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

明天分享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 TABLEIF 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名的同学得分。

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

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 TABLEIF 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”还可支付大量学习材料。或是返回编程学习网，理解更多编程技术常识。