SQL数据分析实战：好用的窗口函数

大家好，我是才哥。

感觉这个春节假期在除夕过完之后吧，时间就过的非常快了，余额已经明显不足了。嗯，是开始可以学习起来了!

目录：

• 1. 窗口函数是什么
• 2. 排序函数
• 3. 分布函数
• 4. 前后函数
• 5. 首尾函数
• 6. 聚合函数

1. 窗口函数是什么

窗口函数，也叫OLAP函数(online Anallytical Processing，联机分析处理)，可以对数据库数据进行实时分析处理。

mysql从8.0版本开始支持窗口函数了，今天我们就是以mysql为例来介绍这个窗口函数的。

窗口其实是指一个记录集合，而窗口函数则是在满足某些条件的记录集合上执行指定的函数方法。在日常工作中比较常见的例子比如求学生的单科成绩排名、求前三名等等之类的。

窗口函数的基本语法如下：

<窗口函数> OVER (PARTITION BY <用于分组的列名> ORDER BY <用于排序的列名>)

像一些聚合函数如 SUM()、AVG()、COUNT()、MAX()与MIN()等等，以及专用的窗口函数RANK()、DENSE_RANK()与ROW_NUMBER()等等。

2. 排序函数

就是进行排序操作，显示排名

RANK()、DENSE_RANK()与ROW_NUMBER()

我们先创建数据表如下：

DROP TABLE
IF
 EXISTS 成绩单;
CREATE TABLE 成绩单 ( 学号 VARCHAR ( 8 ), 姓名 VARCHAR ( 8 ), 科目 VARCHAR ( 8 ), 得分 INT ) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO 成绩单
VALUES
 ('1000', '小明', '语文' ,112 ),
 ('1000', '小明', '数学' ,120 ),
 ('1000', '小明', '英语' ,92 ),
 ('1001', '云朵', '语文' ,112 ), 
 ('1001', '云朵', '数学' ,118 ),
 ('1001', '云朵', '英语' ,99 ), 
 ('1002', '库里', '语文' ,101 ),
 ('1002', '库里', '数学' ,111 ),
 ('1002', '库里', '英语' ,90 ),
 ('1003', '才子', '语文' ,112 ), 
 ('1003', '才子', '数学' ,120 ),
 ('1003', '才子', '英语' ,112 ), 
 ('1004', '小华', '语文' ,112 ),
 ('1004', '小华', '数学' ,112 ),
 ('1004', '小华', '英语' ,112 ),
 ('1005', '强森', '语文' ,92 ), 
 ('1005', '强森', '数学' ,120 ),
 ('1005', '强森', '英语' ,92 );

这是一张成绩表，分别是学号、姓名、科目与得分。

成绩表

面对上面这份数据，我们要求各科目学生们得分排名，就可以用到排序函数。

比如RANK()

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

这个操作是按照科目进行分组，然后按照得分进行排序(DESC是由大到小)。

结果如下：

RANK()

可以看到，对于同样得分而言，RANK()下的名次是同样的，而且名次中存在间隙(不一定连续)。

我们来看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
 成绩单

结果对比如下：

差异对比

可以看到这三者的作用如下：

我们要获取各科目排名第一的学生及得分，就可以再加个条件判断即可，需要注意这里用到了子查询。

SELECT
 * 
FROM
 ( SELECT *, DENSE_RANK() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC ) AS DENSE_RANK_排名 FROM 成绩单 ) a 
WHERE
 DENSE_RANK_排名 = 1;

查询结果如下：

DENSE_RANK_排名第一

另外还有个NTILE(n)将分区中的有序数据分为n个等级，记录等级数

比如按照学号分区得分排序进行分2个等级

SELECT
 *,
 NTILE(2) OVER ( PARTITION BY 学号 ORDER BY 得分 DESC ) AS NTILE_
FROM
 成绩单

查询结果如下：

NTILE(2)

NTILE(n)在数据分析中应用较多，比如由于数据量大，需要将数据平均分配到n个并行的进程分别计算，此时就可以用NTILE(n)对数据进行分组(由于记录数不一定被n整除，所以数据不一定完全平均)，然后将不同桶号的数据再分配。

3. 分布函数

分布函数有两个PERCENT_RANK()和CUME_DIST()

**PERCENT_RANK()**的用途是每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数。

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

查询结果如下：

PERCENT_RANK()

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
 成绩单

查询结果如下：

CUME_DIST()

可以看到，数学科目中有0.5也就是50%的朋友得分120，超过66.66%的学生成绩在118分及以上。

4. 前后函数

查询当前行指定字段往前后N行数据，LAG() 和 LEAD()

前N行LAG(expr[,N[,default]])，比如我们看各科目同学每个人往前3名的同学得分。

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

查询结果如下：

LAG(得分, 3)

可以看到，各科目前三行都是NULL空值，这是因为前三行不存在它们往前3行的值。rank 4的前3是rank 1，对应得分是120。

这个可以用于进行一些诸如环比的情况，在这里我们可以计算当前同学与前1名同学得分差值，操作如下：

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

查询结果如下：

LEAD(expr[,N[,default]])就是往后N名了，这里就不再赘述。

5. 首尾函数

查询指定字段第一或最后的数据FIRST_VALUE(expr)和LAST_VALUE(expr)

查询各科目得分第1的分值

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

查询结果如下：

FIRST_VALUE(得分)

我们可以计算各个同学与第1名的差距(上面前后函数部分介绍了和前1名的差距)：

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
  成绩单 
 ) a

查询结果如下：

LAST_VALUE(expr)就是最后1名了，这里不再赘述。

另外还有NTH_VALUE(expr, n)查询指定字段有序行的第n的值

比如查询排名第4的数据

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

查询结果如下：

NTH_VALUE(得分,4)

6. 聚合函数

在窗口中每条记录动态地应用聚合函数(SUM()、AVG()、MAX()、MIN()、COUNT())，可以动态计算在指定的窗口内的各种聚合函数值。

所以，这里我们构造一个带有时间字段的数据表。

DROP TABLE
IF
 EXISTS 语文成绩单;
CREATE TABLE 语文成绩单 ( 学号 VARCHAR ( 8 ), 姓名 VARCHAR ( 8 ), 时间  DATE, 得分 INT ) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO 语文成绩单
VALUES
 ('1000', '小明', '2022-01-02' ,102 ),
 ('1001', '云朵', '2022-01-04' ,112 ), 
 ('1002', '库里', '2022-01-07' ,101 ),
 ('1003', '才子', '2022-01-07' ,118 ),  
 ('1004', '小华', '2022-01-08' ,112 ),
 ('1005', '强森', '2022-01-09' ,92 );

这是一张语文成绩表，分别是学号、姓名、时间与得分。

语文成绩表

比如，我们要查询在截止每个时间语文最高分，可以这样操作：

SELECT
 *,
 MAX(得分) OVER ( ORDER BY 时间 ) AS MAX_ 
FROM
 语文成绩单

查询结果如下：

MAX(得分)

以上就是本次的基础介绍，日常工作的的实际操作应该会更加复杂，不过抽丝剥茧我们总会发现复杂都是由很多基础拼接而成，打好基础就可以变得很强!