目录
其他函数-nth_value(expr,n)、ntile(n)
聚合函数
group_concat()
-- 将所有员工的名字合并成一行
select group_concat(emp_name) from emp;
-- 指定分隔符合并
select department,group_concat(emp_name separator ';' ) from emp group by department;
-- 指定排序方式和分隔符
select department,group_concat(emp_name order by salary desc separator ';' ) from emp group by department;
数学函数
字符串函数
日期函数
控制流函数
case_when函数
-- 方式1
select
* ,
case
when payType=1 then '微信支付'
when payType=2 then '支付宝支付'
when payType=3 then '银行卡支付'
else '其他支付方式'
end as payTypeStr
from orders;
-- 方式2
select
* ,
case payType
when 1 then '微信支付'
when 2 then '支付宝支付'
when 3 then '银行卡支付'
else '其他支付方式'
end as payTypeStr
from orders;
窗口函数
序号函数
-- 对每个部门的员工按照薪资排序,并给出排名
select
dname,
ename,
salary,
row_number() over(partition by dname order by salary desc) as rn
from employee;
-- 对每个部门的员工按照薪资排序,并给出排名 rank
select
dname,
ename,
salary,
rank() over(partition by dname order by salary desc) as rn
from employee;
-- 对每个部门的员工按照薪资排序,并给出排名 dense-rank
select
dname,
ename,
salary,
dense_rank() over(partition by dname order by salary desc) as rn
from employee;
--求出每个部门薪资排在前三名的员工- 分组求TOPN
select
*
from
(
select
dname,
ename,
salary,
dense_rank() over(partition by dname order by salary desc) as rn
from employee
)t
where t.rn <= 3
-- 对所有员工进行全局排序(不分组)
-- 不加partition by表示全局排序
select
dname,
ename,
salary,
dense_rank() over( order by salary desc) as rn
from employee;
开窗聚合函数-sum,avg,min,max
select
dname,
ename,
salary,
sum(salary) over(partition by dname order by hiredate) as pv1
from employee;
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid) as pv3
from itcast_t1; -- 如果没有order by排序语句 默认把分组内的所有数据进行sum操作
select
dname,
ename,
salary,
sum(salary) over(partition by dname order by hiredate rows between unbounded preceding and current row) as c1
from employee;
select
dname,
ename,
salary,
sum(salary) over(partition by dname order by hiredate rows between 3 preceding and current row) as c1
from employee;
select
dname,
ename,
salary,
sum(salary) over(partition by dname order by hiredate rows between 3 preceding and 1 following) as c1
from employee;
select
dname,
ename,
salary,
sum(salary) over(partition by dname order by hiredate rows between current row and unbounded following) as c1
from employee;
分布函数-cume_dist()和persent_rank
/*
rn1: 没有partition,所有数据均为1组,总行数为12,
第一行:小于等于3000的行数为3,因此,3/12=0.25
第二行:小于等于4000的行数为5,因此,5/12=0.4166666666666667
rn2: 按照部门分组,dname='研发部'的行数为6,
第一行:研发部小于等于3000的行数为1,因此,1/6=0.16666666666666666
*/
select
dname,
ename,
salary,
cume_dist() over(order by salary) as rn1, -- 没有partition语句 所有的数据位于一组
cume_dist() over(partition by dept order by salary) as rn2
from employee;
/*
rn2:
第一行: (1 - 1) / (6 - 1) = 0
第二行: (1 - 1) / (6 - 1) = 0
第三行: (3 - 1) / (6 - 1) = 0.4
*/
select
dname,
ename,
salary,
rank() over(partition by dname order by salary desc ) as rn,
percent_rank() over(partition by dname order by salary desc ) as rn2
from employee;
前后函数-lag和lead
-- lag的用法
/*
last_1_time: 指定了往上第1行的值,default为'2000-01-01'
第一行,往上1行为null,因此取默认值 '2000-01-01'
第二行,往上1行值为第一行值,2021-11-01
第三行,往上1行值为第二行值,2021-11-02
last_2_time: 指定了往上第2行的值,为指定默认值
第一行,往上2行为null
第二行,往上2行为null
第四行,往上2行为第二行值,2021-11-01
第七行,往上2行为第五行值,2021-11-02
*/
select
dname,
ename,
hiredate,
salary,
lag(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time,
lag(hiredate,2) over(partition by dname order by hiredate) as last_2_time
from employee;
-- lead的用法
select
dname,
ename,
hiredate,
salary,
lead(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time,
lead(hiredate,2) over(partition by dname order by hiredate) as last_2_time
from employee;
头尾函数-first_value和last_value
-- 注意, 如果不指定ORDER BY,则进行排序混乱,会出现错误的结果
select
dname,
ename,
hiredate,
salary,
first_value(salary) over(partition by dname order by hiredate) as first,
last_value(salary) over(partition by dname order by hiredate) as last
from employee;
其他函数-nth_value(expr,n)、ntile(n)
-- 查询每个部门截止目前薪资排在第二和第三的员工信息
select
dname,
ename,
hiredate,
salary,
nth_value(salary,2) over(partition by dname order by hiredate) as second_score,
nth_value(salary,3) over(partition by dname order by hiredate) as third_score
from employee
-- 根据入职日期将每个部门的员工分成3组
select
dname,
ename,
hiredate,
salary,
ntile(3) over(partition by dname order by hiredate ) as rn
from employee;
-- 取出每个部门的第一组员工
select
*
from
(
SELECT
dname,
ename,
hiredate,
salary,
NTILE(3) OVER(PARTITION BY dname ORDER BY hiredate ) AS rn
FROM employee
)t
where t.rn = 1;