关于sql:SQL数据分析实战好用的窗口函数

8次阅读

共计 4946 个字符，预计需要花费 13 分钟才能阅读完成。

明天分享 SQL 的窗口函数根底。

目录：

窗口函数是什么
排序函数
散布函数
前后函数
首尾函数
聚合函数

窗口函数 ，也叫OLAP 函数（Online Anallytical Processing，联机剖析解决），能够对数据库数据进行实时剖析解决。

mysql从 8.0 版本开始反对窗口函数了，明天咱们就是以 mysql 为例来介绍这个窗口函数的。

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

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

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

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

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

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

后果比照如下：

差别比照

能够看到这三者的作用如下：

函数	阐明
ROW_NUMBER	为表中的每一行调配一个序号，能够指定分组（也能够不指定）及排序字段(间断且不反复)
DENSE_RANK	依据排序字段为每个分组中的每一行调配一个序号。排名值雷同时，序号雷同，序号中没有间隙（1,1,1,2,3 这种）
RANK	依据排序字段为每个分组中的每一行调配一个序号。排名值雷同时，序号雷同，但序号中存在间隙（1,1,1,4,5 这种）

咱们要 获取各科目排名第一 的学生及得分，就能够再加个条件判断即可，须要留神这里用到了子查问。

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 整除，所以数据不肯定齐全均匀），而后将不同桶号的数据再调配。

散布函数有两个 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 分及以上。

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

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

SELECT
 *,
 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 名了，这里就不再赘述。

查问指定字段第一或最初的数据 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)

在窗口中每条记录动静地利用聚合函数（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(得分)

以上就是本次的根底介绍，日常工作的的实际操作应该会更加简单，不过抽丝剥茧咱们总会发现简单都是由很多根底拼接而成，打好根底就能够变得很强！

以上就是本次分享的所有内容，如果你感觉文章还不错，欢送关注公众号：Python 编程学习圈，每日干货分享，发送“J”还可支付大量学习材料。或是返回编程学习网，理解更多编程技术常识。

正文完

sql

发表至： sql

2022-06-24

0

关于sql:CQ-Tech-解析-CloudQuery-审计分析功能

关于sql:免费下载-冬季实战营第三期MySQL数据库进阶实战

关于sql:SQL-SELECT-查询语句-用法详解

关于sql:SQL-saveOrUpdate

关于ftp:CentOS79搭建FTP服务

关于sql:SQL数据分析实战好用的窗口函数

1. 窗口函数是什么

2. 排序函数

3. 散布函数

4. 前后函数

5. 首尾函数

6. 聚合函数

站内搜索