SQL之窗口函数

目录

一：窗口函数语法

1：over():里面什么也不填例：over里面没有都不填，表示对每一项进行sum

2：over():参数填入partition by

3：over():参数填入Order by

4：over():参数填入"滑动窗口"

#案例1：移动平均值

#案例2：累加求和

​二：窗口函数

排名窗口函数：

        row_number()：

        rank()：

        dense_rank()：

​累积分布函数

        cume_dist：累积分布函数

#案例1：查询所有员工按照月薪排名的累计分布情况。

数据划分函数

        ntile(n份数)：

#案例1：按薪资分成10份

---

窗口函数：用于分析
对比：
        聚合函数：多条输入，一条输出。

        窗口函数：多条输入，多条输出。

一：窗口函数语法

函数(args)  over(【partition by expression】【order by expression ASC】】 【滑动窗口】 )

        以上【】都是选填

1：over():里面什么也不填
例：over里面没有都不填，表示对每一项进行sum

select *,sum(salary) over()  from employee

效果：

​

编辑

2：over():参数填入partition by

             partition by表示定义分区。如果定义了分区，就会针对每个分区进行分析。

select username,dept_id,salary,sum(salary) over(partition by dept_id)  from employee

       效果：（能看的出，计算出的sum并不是全部的sum，而是分组的sum）

3：over():参数填入Order by

     Order by表示组内排序。 并且sum是一个累加的效果。

select username,dept_id,salary,sum(salary) over(partition by dept_id order by salary)  from employee

  效果：组内排序。 并且sum是一个累加的效果。

4：over():参数填入"滑动窗口"

• 滑动窗口由关键字：rows 或 ranges 指定

​

rows [ between { start }  and   {end} ]：通过指定行数来定义窗口的大小。（如果不写between，只写一位，就表示写的这位到当前行）

        current Row：表示当前行

        {N行}   preceding：前n行，包括当前行

        {N行}   following：后n行

        unbounded  preceding：开头到当前行

        unbounded  following：当前行到结尾

#案例1：移动平均值

select salary, avg(salary) over(rows between 1 preceding and 1 following);     # 取每三行的平均值

select salary, sum(salary) over(rows 2 prceding )    # 取当前行与前两行的均值

#案例2：累加求和

select product,ym,amount,sum(amount) over(partition by product order by ym rows between unbounded preceding and current row) as row
from sales_monthly

​

​二：窗口函数

排名窗口函数：

排名函数：通常和OVER()子句一起用，（MySQL8.0才支持）

        row_number()：

                序号不重复，序号连续（如1，2，3）

        rank()：

                序号可以重复，序号不连续（如1，2，2，4）（如果两个人分数一样，那么第三个人的名次就会跳跃）

        dense_rank()：

                序号可以重复，序号连续（如1，2，2，3）（不会跳跃）

 排名窗口函数不支持动态窗口的大小，是以整个分区进行排名的

​累积分布函数

        cume_dist：累积分布函数

        cume_dist：返回当前行在当前分区内的累计分布，也就是排名在当前行之前(包含当前行)的所有数据所占的比例。

#案例1：查询所有员工按照月薪排名的累计分布情况。

#这里over子句没有分区，所以cume_dist()会将所有员工作为一个整体进行分析

select 姓名，月薪，cume_dist() over(order by salary) as "累计分布" 

from  employee

效果：

​

数据划分函数

        ntile(n份数)：

              将分区的数据划分为N等份。

#案例1：按薪资分成10份

select 姓名,部门id，salary，

       ntile(10)  over(order by salary)

from emplyee

分区排序题：牛客（题解 | #窗口函数 重点：查询每天刷题最多的前二用户#_牛客网 (nowcoder.com)）

 select

             date,

             user_id,

             pass_count

 from

       (  

         select

             date,

             user_id,

             pass_count,

             row_number() over (

                 partition by

                     date

                 order by

                     pass_count desc

             ) as row_num

         from

             questions_pass_record

       )  as t

  where

    row_num <= 2;

同比环比问题：计算每个月业绩的环比增长率（(6月-5月)/5月）

取值窗口函数：

• lag(字段, n行数)：按的是orderby顺序的前n行的数据
• lead(字段, n行数)：取后n的行

select *,

     100*(amount-lag(amount,1) over(partition by product order by ym))/

     lag(amount,1) over(partition by product order by ym) d

 from  sales_monthly

​