mysql窗口函数学习笔记，自我理解

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;