1.序号函数
row_number() / rank() / dense_rank() over(partition by 我按照什么分组)order by 我按照什么排序)
表现形式
select
dname,
ename,
salary,
row_number() over(partition by dname order by salary desc) as rn
from employee;
row_number这个意思是我按照dname分组,salary降序排列,一共有dname ename salary 和rn四列查出来,rn组序号123456连续
rank()rn出现相同的salary,不连续,比如1134557
dense_rank()rn出现相同的salary,连续,11223344
我还可以嵌套,如下所示
–求出每个部门薪资排在前三名的员工- 分组求TOPN
--求出每个部门薪资排在前三名的员工- 分组求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 那就是不分组,all就是一个组
2.开窗聚合函数 SUM()、AVG()、MAX()、MIN()、COUNT()
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操作
得到的pv1,是一个累加sum,先按照 dname分组,然后按照序号进行累加,
select
dname,
ename,
salary,
sum(salary) over(partition by dname order by hiredate rows between unbounded preceding and current row) as c1
from employee;
rows between unbounded preceding and current row)这个意思是从开头加到当前行,也是累加的c1
rows between 3 preceding and current row前三行加到当前行
rows between 3 preceding and 1 following 前三行到后一行
rows between current row and unbounded following当前行加到最后
所有的这些,sum()都可以替换,加不到前三行/后一行什么的,按0算
3.cum_dist,percent_rank(),说人话就是,这个函数算,比你低的和和你一样工资的人占当前分组partition by__的占比多少
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;
rn1,是全体一起排,领导和员工不分贵贱一起看薪水
rn2,分部门,牛马部门排一起,总裁排一起,我是牛马,我的工资高低,占我这个部门所有的牛马的排名的前百分之几
另外一个percent_rank()公式,分子分母同时减一,(rank-1) / (rows-1),不常用忘了叭
4.LAG和LEAD前后函数,返回位于当前行的前n行(LAG(expr,n))或后n行(LEAD(expr,n))的expr的值,说人话就是,我要算前一位和后一位同学的工资差多少
应用:
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;
这个2000-1-1号是随便写的,你写9999-9-9都行,拿来占位的,为的就是按日期排入职人员工资,你比你先进来后进来的工资差多少
SELECT
student_name,
exam_date,
score,
LAG(score) OVER (PARTITION BY student_name ORDER BY exam_date) AS previous_score,
score - LAG(score) OVER (PARTITION BY student_name ORDER BY exam_date) AS score_difference
FROM
scores
ORDER BY
student_name, exam_date;
这个是非常直白的排分数了,我比前一名少考了多少分
lead直接替换lag就行我比你多考多少分
5.FIRST_VALUE和LAST_VALUE头尾函数
返回第一个(FIRST_VALUE(expr))或最后一个(LAST_VALUE(expr))expr的值说人话就是元老拿多少钱,我刚招进来的拿多少
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;
这个得写,orderby一定得写,不然会乱,谁知道谁先进来啊
6.NTH_VALUE(expr, n)、
用途:返回窗口中第n个expr的值。expr可以是表达式,也可以是列名
人话就是,第二高薪水是谁?谁是万年老二用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
7.NTILE(n)
用途:将分区中的有序数据分为n个等级,记录等级数
应用场景:将每个部门员工按照入职日期分成3组
分小组了分小组!!!!!我们按照先后顺序分组(orderby date)咱俩前后门进公司分组
select
dname,
ename,
hiredate,
salary,
ntile(3) over(partition by dname order by hiredate ) as rn
``
这意思就是分三组,每组都有一样的编号
```sql
-- 取出每个部门的第一组员工
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;