SQL重要应用：窗口函数

已于 2024-09-04 14:30:42 修改
阅读量1.8k 收藏 22
点赞数 46
文章标签： sql
于 2024-07-31 15:54:26 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_44002014/article/details/140800827
版权

一、窗口函数概念

窗口函数，也叫联机分析处理(Online Anallytical Processing, OLAP)函数，可以对数据库数据进行实时的分析处理。窗口函数中的“窗口”，是指一个记录的集合，基于此，窗口函数则是在满足某些条件的记录集合上执行的指定的函数方法。

二、窗口函数的主要应用场景

窗口函数的使用场景主要可以分为两大类：

（一）简化

  • 对某些分组场景的SQL进行简化，以提升效率；
  • 用于简化子查询中的一些复杂条件；

（二）一些常用的数据分析场景

在一些涉及到数据分析的业务场景中，窗口函数可以在不减少原表行数的情况下进行分组排序等计算，具体的应用场景有：

  • 组内比较问题
  • 组内topN问题
  • 累计求和问题
  • 连续登录、活跃N天问题
  • 连续出现N次问题

关于上述的场景问题，详细的例子和解法可以参考这篇文章（个人认为这篇文章给的例子和解法通俗易懂，还贴出了每一步的运行结果，清晰易懂，非常值得参考）。

三、窗口函数语法及分类

（一）窗口函数基本语法

窗口函数的基本语法如下所示

窗口函数名([参数]) OVER(
    [PARTITION BY <分组列>]
    [ORDER BY <排序列 ASC/DESC>]
    [ROWS BETWEEN 开始行 AND 结束行]
)
  • 可以使用的 [窗口函数] 类别较多，详见本章第（二）节；
  • [PARTITION BY] 子句用于指定分组列，且可依据多个列进行分组；
  • [ORDER BY] 子句用于指定排序列，且可依据多个列进行排序，默认升序(ASC)；
  • [ROWS BETWEEN ... AND ...] 子句用于指定窗口的范围，详见本章第（三）节；

【注意】运用窗口函数生成的新的列名，不能在WHERE、HAVING、GROUP BY、ORDER BY等子句中直接使用，需要把运用窗口函数的查询作为一张子表，从该表中查询出窗口函数生成的新列名才能用于上述字句中。

（二）窗口函数分类

【注】该部分的例子来自优快云@G皮T，原帖链接。

1. 排序函数

常用的排序窗口函数有RANK()、DENSE_RANK()与ROW_NUMBER()。此外，NTILE()也可以视为特殊的排序函数。对这些函数的详细说明如下所示：

函数说明
RANK根据排序字段为每个分组中的每一行分配一个序号，排序值相同时，序号相同，但是序号值不连续。例如：1、1、1、4、5；
DENSE_RANK根据排序字段为每个分组中的每一行分配一个序号，排序值相同时，序号相同，且序号值连续。例如：1、1、1、2、3；
ROW_NUMBER为表中的每一行分配一个序号，可以指定分组（也可以不指定）及排序字段（必须指定），分配的序号值连续且不重复，一定为1、2、3、4、5、...；
NITLE(n)将窗口中的有序数据分为n个等级，且记录等级数。常用于将数据分成N份（并选取其中的第n份进行展示）的任务中。如果窗口中的数据不能被N整除，则分配不会平均，但每一份中的个数差别不会超过1；

RANK()、DENSE_RANK()与ROW_NUMBER()的区别如下所示

SELECT *,
       RANK() OVER (PARTITION BY 科目 ORDER BY 得分 DESC) AS RANK_排名,
       DENSE_RANK() OVER (PARTITION BY 科目 ORDER BY 得分 DESC) AS DENSE_RANK_排名,
       ROW_NUMBER() OVER (PARTITION BY 科目 ORDER BY 得分 DESC) AS ROW_NUMBER_排名
FROM 成绩单;

可以得到如下结果

【注】对以上每一个排序窗口函数的功能，都可以用[group by]子句和[order by]子句来配合实现。但是，使用窗口函数不会减少原表中的行数，使得可以在同一行中展现多种排序方式的结果，这是使用[group by]子句和[order by]子句无法做到的。

NITLE(n)函数的一个示例如下所示

-- 按照学号分区、得分排序分成2个等级，便于查看每一名学生在所有科目得分上的高低分级情况
SELECT *,
       NTILE(2) OVER (PARTITION BY 学号 ORDER BY 得分 DESC) AS NTILE_
FROM 成绩单;

可以得到如下结果

2. 聚合函数

【注】该部分的例子来自优快云@CoderSharry，原帖链接。

所有聚合函数均可以用在窗口函数中，例如求和SUM()、求平均值AVG()、求数量COUNT()、求最大值和求最小值MIN()。聚合窗口函数与普通聚合函数的函数名一致、功能也一致。从使用的角度来讲，除了使用语法不同之外，聚合窗口函数使得数据的分析和获取更加简便。示例如下：

【例】要求在sales表每行最后一列都加上这一行的[产品类别category]的平均[销售收入revenue]，并且以category顺序排序，即如下图所示：

若使用普通聚合函数，代码如下

SELECT t1.*, t2.avg_revenue
FROM sales t1
LEFT JOIN(
    SELECT category, AVG(revenue) AS avg_revenue
    FROM sales
    GROUP BY category
) t2 ON t1.category = t2.category
ORDER BY t1.category;

需实证，或许可以用如下的更简单的方法

SELECT *, AVG(revenue) AS avg_revenue
FROM sales
GROUP BY category
ORDER BY category;

使用聚合窗口函数则可以简化上述代码，具体如下

SELECT sales.*, 
       AVG(revenue) OVER (PARTITION BY category) AS avg_revenue
FROM sales;

3. 前后函数

前后函数用于查询当前行指定字段的前(LAG())后(LEAD())N行的数据，主要应用于将某个时间段与给定指标的前一个时间段进行比较，例如：

  • 获得每年销售额与上一年销售额之间的差值；
  • 获得每月注册/转换/网站访问次数的增量；
  • 按月比较用户流失率；

LAG()函数的具体语法为

LAG(字段名,N,DEFAULT) OVER(......);

其中，N为向上选取的行数，DEFAULT为默认值，表示若向上选取N行后的值为NULL的时候，取DEFAULT默认值代替，若不指定默认值，则为NULL；

LEAD()函数的具体语法与LAG()相同；

【例】想要查看各科目同学每人往前3名的同学得分，可以用如下的解法

SELECT *,
       RANK() OVER (PARTITION BY 科目 ORDER BY 得分 DESC) AS RANK_排名,
       LAG(得分, 3) OVER (PARTITION BY 科目 ORDER BY 得分 DESC) AS LAG_
FROM 成绩单;

可以得到如下结果：

可以看到，各科目前三行都是空值NULL，这是因为排名前三的不存在更前面3位的值。从rank4开始则有对应值，例如rank4的前3是rank1，对应得分是120。

4. 首尾函数

首(FIRST_VALUE(字段名))尾(LAST_VALUE(字段名))函数用于查询指定字段的第一或最后的数值。例如，若要查询各科目得分第一的分值，则

SELECT *,
       RANK() OVER (PARTITION BY 科目 ORDER BY 得分 DESC) AS RANK_排名,
       FIRST_VALUE(得分) OVER (PARTITION BY 科目 ORDER BY 得分 DESC) AS FIRST_VALUE_得分
FROM 成绩单;

可以得到如下结果

基于此，还可以计算各个同学与第一名的得分差距，例如

SELECT *, FIRST_VALUE_得分 - 得分
FROM(
    SELECT *,
           RANK() OVER (PARTITION BY 科目 ORDER BY 得分 DESC) AS RANK_排名,
           FIRST_VALUE(得分) OVER (PARTITION BY 科目 ORDER BY 得分 DESC) AS FIRST_VALUE_得分
    FROM 成绩单
);

可以得到如下结果

除了查询第一和最后一个数值外，也可以用NTH_VALUE(字段名,N)来返回窗口中指定字段的第N个值。例如，若要查询得分排名（以rank()计）第4的数据，则

SELECT *,
       RANK() OVER (PARTITION BY 科目 ORDER BY 得分 DESC) AS RANK_排名,
       NTH_VALUE(得分,4) OVER (PARTITION BY 科目 ORDER BY 得分 DESC) AS NTH_VALUE_得分
FROM 成绩单;

可以得到如下结果

5. 分布函数

SQL窗口函数中的分布函数有两个，PERCENT_RANK()和CUME_DIST()。

  • PERCENT_RANK()：先对每一行用RANK()进行排序产生序号（RANK所用的排序条件由PERCENT_RANK的排序条件(ORDER BY 字段名)来决定），再遵照公式(rank-1)/(rows-1)进行计算。其中rows为当前分组的总行数；

例如：查看该行得分在该科目得分分布中的百分位数（以小数形式展示）：

SELECT *,
       RANK() OVER (PARTITION BY 科目 ORDER BY 得分 DESC) AS RANK_排名,
       PERCENT_RANK() OVER (PARTITION BY 科目 ORDER BY 得分 DESC) AS PERCENT_RANK_
FROM 成绩单;

可以得到如下结果

  • CUME_DIST()：可用于查询当前分组内小于、等于当前行的rank()值的行数与分组内总行数的比值；

例如，若要查询小于等于当前成绩的比例：

SELECT *,
       RANK() OVER (PARTITION BY 科目 ORDER BY 得分 DESC) AS RANK_排名,
       CUME_DIST() OVER (PARTITION BY 科目 ORDER BY 得分 DESC) AS CUME_DIST_
FROM 成绩单;

可以得到如下结果

可以看到，数学科目中有50%（3/6）的学生得分大于等于120分，有66.66%（4/6）的学生得分大于等于118分，其余同理。

（三）窗口函数的“窗口”

窗口函数之所以称为“窗口”，是因为其可以选择特定的窗口范围。而窗口函数对窗口范围的选择是用ROWS BETWEEN [...] AND [...] 或RANGE BEWTEEN [...] AND [...]来实现的。

  • ROWS ......表示基于行数进行选择，例如从起始行到当前行等；
  • RANGE ......表示基于当前行的数值进行的选择，例如筛选比当前行的值小且小的范围在10以内的行等，行数不固定，只以值来决定；

而[...]中的可以使用的参数如下所示：

  • PRECEDING：往前或比当前值小；
  • FOLLOWING：往后或比当前值大；
  • CURRENT ROW：当前行或值；
  • UNBOUNDED：起点（一般结合PRECEDING、FOLLOWING使用），例如UNBOUNDED PRECEDING表示该窗口最前面的行（起点），UNBOUNDED FOLLOWING表示该窗口最后面的行（终点）。

对“窗口”的选择示例：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（表示从起点到当前行）

ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING（表示往前2行到往后1行）

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW（表示往前2行到当前行）

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING（表示当前行到终点）

RANGE BETWEEN 10 PRECEDING ANG CURRENT ROW（表示当前行到当前行值-10的范围内的数据）

RANGE BETWEEN CURRENT ROW AND 10 FOLLOWING（表示当前行到当前行值+10的范围内的数据）

RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING（表示所有值大于等于当前行的数据）

RANGE BETWEEN UNBOUNDED FOLLOWING AND CURRENT ROW（表示所有值小于等于当前行的数据）

对于“窗口”范围的图示如下所示

（图源：知乎@waynaqua，该作者也是从网上引用的该图片，且未给出具体的引用出处）

（四）窗口简化

MySQL8中的Named Windows是指可以使用WINDOW子句定义并命名窗口，在OVER子句中只需要通过定义的窗口名来引用即可。使用该方法的好处是可以避免在多个OVER子句中重复定义相同的窗口。

例如，下面的查询使用了三个相同的窗口：

SELECT val,
       ROW_NUMBER() OVER (ORDER BY val) AS row_number,
       RANK() OVER (ORDER BY val) AS rank,
       DENSE_RANK() OVER (ORDER BY val) AS dense_rank
FROM numbers;

若使用Named Windows来简化，可以为：

SELECT val,
       ROW_NUMBER() OVER w AS row_number,
       RANK() OVER w as rank,
       DENSE_RANK() OVER w AS dense_rank
FROM numbers
WINDOW w AS (ORDER BY val);

【注】

  1. 在一个SELECT中，可以定义多个windows以简化代码；
  2. 一个命名窗口的定义本身也可以是另一个窗口名的开头，即可以实现窗口之间的引用，但是要注意不能形成循环；
SELECT val,
       SUM(val) OVER w1 AS sum_w1,
       SUM(val) OVER w2 AS sum_w2,
       SUM(val) OVER w3 AS sum_w3
FROM numbers
WINDOW
       w1 AS (ORDER BY val),
       w2 AS (w1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
       w3 AS (w2 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
legendHY
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值