第六章：

6.函數(shù)

作用：提高代碼重用性和隱藏實(shí)現(xiàn)細(xì)節(jié)

分類：

• 聚合函數(shù)

• 數(shù)學(xué)函數(shù)

• 字符串函數(shù)

• 日期函數(shù)

• 控制流函數(shù)

• 窗口函數(shù)

6.1 聚合函數(shù)

• group_concat()

作用：根據(jù)group by指定的列進(jìn)行分組，并用分隔符分隔，將同一個(gè)分組中的值連接起來，返回一個(gè)字符串結(jié)果，實(shí)現(xiàn)行的合并

語法：group_concat ([distinct] 字段名 [order by 排序字段 asc/desc] [separator ‘分隔符’] )

separator為一個(gè)字符串值，默認(rèn)為逗號(hào)

6.2 數(shù)學(xué)函數(shù)

函數(shù)名	功能
ABS(x)	返回x的絕對(duì)值
CEIL(x)	返回大于或等于x的最小整數(shù)
FLOOR(x)	返回小于或等于x的最大整數(shù)
GREATEST(expr1, expr2, expr3, …)	返回列表中的最大值
LEAST(expr1, expr2, expr3, …)	返回列表中的最小值

函數(shù)名	功能
MAX(expression)	返回字段expression中的最大值
MIN(expression)	返回字段expression中的最小值
MOD(x,y)	返回x除以y后的余數(shù)
PI()	返回圓周率 3.141593
POW(x,y)	返回x的y次方

函數(shù)名	功能
RAND()	返回0到1的隨機(jī)值
ROUND(x)	返回離x最近的整數(shù)，遵循四舍五入規(guī)則
ROUND(x,y)	返回指定位數(shù)的小數(shù)，遵循四舍五入規(guī)則
TRUNCATE(x,y)	返回?cái)?shù)值x保留到小數(shù)點(diǎn)后y位的值，不遵循四舍五入規(guī)則

6.3 字符串函數(shù)

函數(shù)名	功能
CHAR_LENGTH(s)	返回字符串s的字符數(shù)
CHARACTER_LENGTH(s)	返回字符串s的字符數(shù)
CONCAT(s1,s2,s3,…)	s1，s2等多個(gè)字符串合并成一個(gè)字符串
CONCAT_WS(s1,s2,s3,…)	同CONCAT函數(shù)，每個(gè)字符串之間加上x,x可以是分隔符
FIELD(s,s1,s2,s3,…)	返回第一個(gè)字符串s在字符串列表(s1,s2,…)中的位置

函數(shù)名	功能
LTRIM(s)	去除字符串s開始處的空格
MID(s,n,len)	從字符串s的n位置開始截取長度為len的子字符串，同SUBSTRING函數(shù)
POSITION(s1 IN s)	從字符串s中獲取s1的開始位置
REPLACE(s,s1,s2)	字符串s2替代字符串s中的字符串s1
REVERSE(s)	字符串s的順序反過來

函數(shù)名	功能
RIGHT(s,n)	返回字符串s的后n個(gè)字符
RTRIM(s)	去除字符串s結(jié)尾處的空格
STRCMP(s1,s2)	比較s1和s2，若相等返回0，s1>s2返回1，s1<s2返回-1
SUBSTR(s,start,len)	從字符串s的start位置開始截取長度為len的子字符串
SUBTRING(s,start,len)	從字符串s的start位置開始截取長度為len的子字符串

函數(shù)名	功能
TRIM(s)	去除字符串s開始和結(jié)尾處的空格
UCASE(s)	字符串轉(zhuǎn)換為大寫
UPPER(s)	字符串轉(zhuǎn)換為大寫
LCASE(s)	字符串轉(zhuǎn)換為小寫
LOWER(s)	字符串轉(zhuǎn)換為小寫

6.4 日期函數(shù)

函數(shù)名	功能
UNIX_TIMESTAMP()	返回從1970-01-01 00:00:00到當(dāng)前毫秒值
UNIX_TIMESTAMP(DATE_STRING)	將制定日期轉(zhuǎn)為毫秒值時(shí)間戳
FROM_UNIXTIME(BIGINT UNIXTIME[, STRING FORMAT])	將毫秒值時(shí)間戳轉(zhuǎn)為指定格式日期
CURDATE()	返回當(dāng)前日期
CURRENT_DATE()	返回當(dāng)前日期

函數(shù)名	功能
TIMEDIFF(time1, time2)	計(jì)算時(shí)間差值
DATE_FORMAT(d, f)	按表達(dá)式f的要求顯示日期d
STR_TO_DATE(string, format_mask)	將字符串轉(zhuǎn)為日期
DATE_SUB(date, INTERVAL expr type)	函數(shù)從日期減去指定的時(shí)間間隔

6.4.1 日期格式

描述	描述
%a	縮寫星期名
%b	縮寫月名
%c	月，數(shù)值
%D	帶有英文前綴的月中的天
%d	月的天，數(shù)值(00-31)
%e	月的天，數(shù)值(0-31)
%f	微秒
%H	小時(shí)(00-23)
%h	小時(shí)(01-12)
%I	小時(shí)(01-12)
%i	分鐘，數(shù)值(00-59)
%j	年的天(001-366)
%k	小時(shí)(0-23)
%l	小時(shí)(1-12)
%M	月名
%m	月，數(shù)值(00-12)
%p	AM 或 PM
%r	時(shí)間，12-小時(shí)(hh:mm:ss AM 或PM)
%S	秒(00-59)
%s	秒(00-59)
%T	時(shí)間，24-小時(shí)(hh:mm:ss)
%U	周(00-53)星期日是一周的第一天
%u	周(00-53)星期一是一周的第一天
%V	周(01-53)星期日是一周的第一天，與%X使用
%v	周(01-53)星期一是一周的第一天，與%x使用
%W	星期名
%w	周的天(0=星期日, 6=星期六)
%X	年，其中的星期日是周的第一天，4位，與%V使用
%x	年，其中的星期一是周的第一天，4位，與%v使用
%Y	年，4位
%y	年，2位

6.5 控制流函數(shù)

6.5.1 if邏輯判斷語句

格式	含義
IF(expr, v1, v2)	若表達(dá)式expr成立，返回結(jié)果v2，否則返回結(jié)果v2
IFNULL(v1, v2)	若v1的值不為NULL，返回v1，否則返回v2
ISNULL(expression)	判斷表達(dá)式是否為NULL
NULLIF(expr1, expr2)	若字符串expr1與expr2字符串相等返回NULL，否則返回expr1

6.5.2 case when語句

格式：

CASE expression

WHEN conditon1 THEN result1WHEN conditon2 THEN result2....WHEN conditonN THEN resultNELSE result

END

含義：CASE表示函數(shù)開始，END表示函數(shù)結(jié)束。若condition1成立，返回result1，condition2成立，返回result2,當(dāng)全部不成立返回result，而當(dāng)有一個(gè)成立后，后面將不再執(zhí)行

6.6 窗口函數(shù)

窗口函數(shù)又被稱為開窗函數(shù)。

非聚合窗口函數(shù)對(duì)于聚合函數(shù)來說，聚合函數(shù)是一組數(shù)據(jù)計(jì)算后返回單個(gè)值，非聚合函數(shù)一次指揮處理一行數(shù)據(jù)。窗口聚合函數(shù)在行記錄上計(jì)算某個(gè)字段的結(jié)果時(shí)，可將窗口范圍內(nèi)的數(shù)據(jù)輸入到聚合函數(shù)中，并不改變行數(shù)。

語法：

windows_function (expr) OVER(PARTITION BY ...    ORDER BY ...frame_clause
)

• windows_function：窗口函數(shù)名

• expr：參數(shù)

• OVER：包含三個(gè)選項(xiàng)

1. 分區(qū)(PARTITION BY)：用于將數(shù)據(jù)行拆分成多個(gè)分區(qū)。若省略PARTITION BY，所有數(shù)據(jù)作為一個(gè)組進(jìn)行計(jì)算

2. 排序(ORDER BY)：用于指定分區(qū)內(nèi)的排序方式

3. 窗口大小(frame_clause)：用于在當(dāng)前分區(qū)內(nèi)指定一個(gè)計(jì)算窗口

6.6.1 序號(hào)函數(shù)

• ROW_NUMBER()

• RANK()

• DENSE_RANK()

作用：實(shí)現(xiàn)分組排序，并添加序號(hào)

語法：

row_number() | rank() | dense_rank() over(partition by ...order by ...
)

create table emp(dname varchar(20),ename varchar(20),eid varchar(20),sal double
);insert into emp values('技術(shù)部', '101', 'xiaoming', '3500');
insert into emp values('技術(shù)部', '101', 'xiaowang', '4000');
insert into emp values('技術(shù)部', '101', 'xiaoli', '3600');insert into emp values('運(yùn)營部', '102', 'xiaohua', '3000');
insert into emp values('運(yùn)營部', '102', 'xiaohong', '3100');
insert into emp values('運(yùn)營部', '102', 'xiaolu', '3300');-- 對(duì)每個(gè)部門員工按薪資排序，給出排名
select dname,ename,sal,row_number() over(partition by dname order by sal desc) as rn1,rank() over(partition by dname order by sal desc) as rn2,dense_rank() over(partition by dname order by sal desc) as rn3
from emp;-- 求出每個(gè)部門薪水排前兩名的員工，分組求TOPN
select * from (select dname,ename,sal, dense_rank() over(partition by dname order by sal desc) as rn from emp)t where t.rn <= 2;-- 對(duì)所有員工進(jìn)行全局排序 
select dname,ename,sal,dense_rank() over(partition by dname order by sal desc) as rn from emp;

6.6.2 開窗聚合函數(shù)

SUM, AVG, MIN, MAX

• 在窗口中每條記錄動(dòng)態(tài)地應(yīng)用聚合函數(shù)，可動(dòng)態(tài)計(jì)算在指定地窗口內(nèi)的各種聚合函數(shù)值

create table emp(dname varchar(20),ename varchar(20),eid varchar(20),sal double
);insert into emp values('技術(shù)部', '101', 'xiaoming', '3500');
insert into emp values('技術(shù)部', '101', 'xiaowang', '4000');
insert into emp values('技術(shù)部', '101', 'xiaoli', '3600');insert into emp values('運(yùn)營部', '102', 'xiaohua', '3000');
insert into emp values('運(yùn)營部', '102', 'xiaohong', '3100');
insert into emp values('運(yùn)營部', '102', 'xiaolu', '3300');-- 對(duì)每個(gè)部門員工按薪資排序，給出排名
select dname,ename,sal,row_number() over(partition by dname order by sal desc) as rn1,rank() over(partition by dname order by sal desc) as rn2,dense_rank() over(partition by dname order by sal desc) as rn3
from emp;-- 求出每個(gè)部門薪水排前兩名的員工，分組求TOPN
select * from (select dname,ename,sal, dense_rank() over(partition by dname order by sal desc) as rn from emp)t where t.rn <= 2;-- 對(duì)所有員工進(jìn)行全局排序 
select dname,ename,sal,dense_rank() over(partition by dname order by sal desc) as rn from emp;-- 若沒有ORDER BY排序語句， 默認(rèn)把分組內(nèi)所有數(shù)據(jù)進(jìn)行sum操作
select dname,ename,sal,sum(sal) over(partition by dname order by sal desc) as p1 from emp;
select dname,ename,sal,sum(sal) over(partition by dname ) as p2 from emp;
select dname,ename,sal,sum(sal) over(partition by dname rows between 3 preceding and current row) as p3 from emp;
select dname,ename,sal,sum(sal) over(partition by dname rows between 3 preceding and 1 following) as p4 from emp;
select dname,ename,sal,sum(sal) over(partition by dname rows between current row and unbounded following) as p5 from emp;
select dname,ename,sal,max(sal) over(partition by dname order by sal desc) as p5 from emp;

6.6.3 分布函數(shù)

CUME_DIST 和 PERECENT_RANK

• CUME_DIST：用于分組內(nèi)小于、等于當(dāng)前rank值的行數(shù)/分組內(nèi)總行數(shù)，應(yīng)用于查詢小于等于當(dāng)前薪資(sal)比例

create table emp(dname varchar(20),ename varchar(20),eid varchar(20),sal double
);insert into emp values('技術(shù)部', '101', 'xiaoming', '3500');
insert into emp values('技術(shù)部', '101', 'xiaowang', '4000');
insert into emp values('技術(shù)部', '101', 'xiaoli', '3600');
insert into emp values('技術(shù)部', '101', 'xiaoni', '3500');
insert into emp values('技術(shù)部', '101', 'xiaona', '3400');
insert into emp values('技術(shù)部', '101', 'xiaone', '3300');
insert into emp values('技術(shù)部', '101', 'xiaonl', '3200');insert into emp values('運(yùn)營部', '102', 'xiaohua', '3000');
insert into emp values('運(yùn)營部', '102', 'xiaohong', '3100');
insert into emp values('運(yùn)營部', '102', 'xiaolu', '3300');
insert into emp values('運(yùn)營部', '102', 'xiaolo', '3800');
insert into emp values('運(yùn)營部', '102', 'xiaola', '3700');
insert into emp values('運(yùn)營部', '102', 'xiaole', '3600');
insert into emp values('運(yùn)營部', '102', 'xiaolt', '3000');select dname,ename,sal, cume_dist() over(order by sal) as rn1, -- 沒有partition語句 所有數(shù)位于一組cume_dist() over(partition by ename order by sal) as rn2 
from emp;

• PERECENT_RANK：用于每行按照公式(rank-1) / (rows-1)進(jìn)行計(jì)算，rank為rank()函數(shù)產(chǎn)生的序號(hào)，rows為當(dāng)前窗口的記錄總行數(shù)。不常用。

select dname,ename,sal,rank() over(partition by dname order by sal desc) as rn,percent_rank() over(partition by dname order by sal desc) as rn2from emp;

6.6.4 前后函數(shù)

LAG和LEAD

• 用于返回位于當(dāng)前行的前n行(LAG(expr, n)或后n行(LEAD(expr, n)的expr值，應(yīng)用于查詢前1名同學(xué)的成績和當(dāng)前同學(xué)成績的差值

 -- LAG select dname,ename,sal,lag(sal,1,3500) over(partition by dname order by sal ) as last_1_sal,lag(sal,2) over(partition by dname order by sal ) as last_2_sal
from emp;-- LEAD select dname,ename,sal,lead(sal,1,3500) over(partition by dname order by sal ) as last_1_sal,lead(sal,2) over(partition by dname order by sal ) as last_2_sal
from emp;

6.6.5 頭尾函數(shù)

FIRST_VALUE和LAST_VALUE

• 用于返回第一個(gè)(FIRST_VALUE)或最后一個(gè)(LAST_VALUE)的expr值。應(yīng)用于截止到目前，按入職編號(hào)排序查詢第一個(gè)入職和最后一個(gè)入職的員工薪資

select dname, ename, sal, eid, -- 若不指定order by 將會(huì)排序混亂，出現(xiàn)錯(cuò)誤的結(jié)果first_value(sal) over(partition by dname order by eid) as first,last_value(sal) over(partition by dname order by eid) as last 
from emp;

6.6.6 其他函數(shù)

NTH_VALUE(expr, n)和NTILE(n)

• 用于返回窗口中第n個(gè)expr值。應(yīng)用于截止到當(dāng)前薪資，顯示每個(gè)員工中薪資排第2或3的薪資

-- NTH_VALUE()
-- 查詢各部門截止目前薪資排第2或3的員工個(gè)人信息
select dname, ename, sal, eid, -- 若不指定order by 將會(huì)排序混亂，出現(xiàn)錯(cuò)誤的結(jié)果nth_value(sal,2) over(partition by dname order by eid) as second_sal,nth_value(sal,3) over(partition by dname order by eid) as thrid_sal 
from emp;-- NTILE()
-- 根據(jù)入職編號(hào)將各部門員工分3組
select dname, ename, sal, eid, -- 若不指定order by 將會(huì)排序混亂，出現(xiàn)錯(cuò)誤的結(jié)果ntile(3) over(partition by dname order by eid) as rn
from emp;-- 取出各部門的第一組員工
select * from (select dname, ename, sal, eid, ntile(3) over(partition by dname order by eid) as rn from emp)t 
where t.rn = 1;

6.7 練習(xí)

-- 查詢各部門平均薪水最高的部門名
select a.deptno,a.dname,a.location,avg_sal
fromdept a,(select *from (select *,rank() over(order by avg_sal desc ) rn from(select deptno,avg(sal) avg_sal from emp group by deptno)t1)t2where rn = 1)t3 where a.deptno = t3.deptno;--  查詢員工比所屬領(lǐng)導(dǎo)薪資高的員工個(gè)人信息
create view test_view asselect a.ename ename,a.sal esal,b.ename mgrname,b.sal mgrsal, a.deptno fromemp a,emp b where a.mgr = b.deptnoand a.sal > b.sal;select * from dept a join test_view b on a.deptno = b.deptno;