526互联

结合实例来分析SQL的窗口函数

发布时间 2023-04-08 08:07:48作者: superstar

这篇主要是用举栗子的方式来理解SQL中的窗口函数，加深大家对SQL窗口函数的理解。

样例表

这个样例表是我为了好理解，随便设计的，不符合数据库设计的三范式，请忽略。

（一）标准聚合函数

标准的聚合函数有avg、count、sum、max和min，接下来分别介绍这些聚合函数的窗口函数形式。

1、移动平均窗口函数

移动平均值的定义：若依次得到测定值（x1,x2,x3,...xn）时，按顺序取一定个数所做的全部算数平均值。例如(x1+x2+x3)/3，(x2+x3+x4)/3，(x3+x4+x5)/3,....就是移动平均值。其中，x可以是日或者月，以上的可以成为3日移动平均，或3月移动平均，常用于股票分析中。

语法结构：

avg(字段名) over(partition by 字段名 order by 字段名 asc/desc rows between A and B )

-- A和B是计算的行数范围

影响行数的范围（限定计算移动平均的范围）：

rows between 2 preceding and current row  # 取当前行和前面两行


rows between unbounded preceding and current row   # 包括本行和之前所有的行


rows between current row and unbounded following  # 包括本行和之后所有的行


rows between 3 preceding and current row   # 包括本行和前面三行


rows between 3 preceding and 1 following   # 从前面三行和下面一行，总共五行

当order by后面缺少窗口从句条件，窗口规范默认是rows between unbounded preceding and current row.
当order by和窗口从句都缺失, 窗口规范默认是 rows between unbounded preceding and unbounded following

以v_info举个例子吧

SELECT *,
       AVG(grade) OVER(ORDER BY stu_no ROWS BETWEEN 2 preceding AND CURRENT ROW) AS '三移动平均'
FROM v_info
  • 对于第一行来说，没有前面两行，所以值就为当前行的值
  • 对于第二行来说，前面只有一行，所以三移动平均就为第一行和第二行的平均值

影响行数范围的语句在标准的聚合函数中都适用。

2、计数（count）窗口函数

窗口 函数 count(*) over() 对于查询返回的每一行，它返回了表中所有行的计数。

语法结构：

count(字段名1) over(partition by 字段名2 order by 字段名3 asc/desc)

（1）查询出成绩在90分以上的人数

SELECT *,
       COUNT(*) OVER() AS 'ct'
FROM v_info
WHERE grade>=90

这个结果说明，成绩大于90分的，有两位同学。

（2）按照课程号进行分组，找出成绩大于等于80分的学生人数

SELECT *,
       COUNT(*) OVER(PARTITION BY c_no) AS 'ct'
FROM v_info
WHERE grade>=80

从结果上可以看出，课程号为“0001”的学生人数有2名；课程号为“0002”的学生人数有2名；课程号为“0003”的学生有3名。

3、累计求和（sum）窗口函数

语法结构：

sum(字段名1) over(partition by 字段名2 order by 字段名3 asc/desc) 

--按照字段1进行累积求和
-- 按照字段2 进行分组
-- 在组内按照字段3进行排序

（1）根据学号排序，对学生的成绩进行累积求和

SELECT *,
       SUM(grade) OVER(ORDER BY stu_no) AS '累积求和'
FROM v_info

（2）按照课程号分组，然后根据学号对成绩进行累积求和

SELECT *,
       SUM(grade) OVER(PARTITION BY c_no ORDER BY stu_no) AS '累积求和'
FROM v_info

tips：一定要选择根据学号排序，要不然得出来的是最终的累积求和结果，如下图：

SELECT *,
       SUM(grade) OVER(PARTITION BY c_no) AS '累积求和'
FROM v_info

4、最大（max）、最小值（min）窗口函数

语法结构：

max(字段名1) over(partition by 字段名2 order by 字段名3 asc/desc) 

min(字段名1) over(partition by 字段名2 order by 字段名3 asc/desc)

（1）求成绩的累积最大值和累积最小值

SELECT *,
       MAX(grade) OVER(ORDER BY stu_no) AS '累积最大值',
       MIN(grade) OVER(ORDER BY stu_no) AS '累积最小值'
FROM v_info

按照学号进行排序，在累积最大值中，会依次往下找最大值，如果有比当前值大的，就更新，若没有就保持当前；最小值同理。

（2）按照课程号进行分组，再求最大、最小值

SELECT *,
       MAX(grade) OVER(PARTITION BY c_no ORDER BY stu_no) AS '累积最大值',
       MIN(grade) OVER(PARTITION BY c_no ORDER BY stu_no) AS '累积最小值'
FROM v_info

（3）根据学生号和课程号求成绩的累积最小值

SELECT stu_no,c_no,stu_name,sex,birth,grade,
	    MIN(grade) OVER(PARTITION BY stu_no,c_no) AS '累积最小值'
FROM v_info

从上图可以看出，对于stu_no,c_no分组，后面没有一样的分组，所以每个stu_no,c_no都是一组，所以累积最小值就是当前的成绩值。

（4）统计2019年10月1日-10月10日每天做新题的人的数量，重点在每天。

  • 这个题的重点是在每天，所以需要求出count(时间）=10的用户ID；
  • 这个题可以使用min() over()窗口函数，先根据每个做题者和试卷号，找出每个做题者的最小日期，这里和前面（3）的解题思路是一样的；
  • 如果每天都做题，那么得到的日期是不一样的，所以count（时间）会等于10；
  • 再对这部分的用户ID进行求和，就可以找出每天都做新题的人了。
SELECT COUNT(a.sno) AS '每天做题的人数'
FROM
    (SELECT sno,
	    s_id,
	    time,
	    MIN(time) OVER(PARTITION BY sno,s_id) AS 'first_time'
	   FROM paper
	   WHERE DATE_FORMAT(time,'%Y-%m-%d') BETWEEN '2019-10-01' AND '2019-10-10') AS a
WHERE a.time=a.first_time
GROUP BY a.sno
HAVING COUNT(DISTINCT a.first_time)=10

（二）排序窗口函数

我在之前就更新过了，这里就不重复写了，感兴趣的可以点链接，去看我之前写的文章。

 

（三）分组排序窗口函数

可以按照销售额的高低、点击次数的高低，以及成绩的高低为对用户和学生进行分组，这里的考点是：取销售额最高的25%的用户（将用户分成4组，取出第一组）、取成绩高的前10%的学生（将学生分成10组，取出第一组）等等。

语法结构：

ntile(n) over(partition by 字段名2 order by 字段名3 asc/desc) 

--n表示要切分的片数，如需要取前25%的用户，则需要分为4组，取前10%的用户，则需要分10组
  • ntile(n)，用于将分组数据按照顺序切分成n片，返回当前切片值
  • ntile不支持rows between的用法
  • 切片如果不均匀，默认增加第一个切片的分布

（1）取出成绩前25%的学生信息

  • 第一步：按照成绩的高低，将学生按照成绩进行切片
SELECT *,
	   ntile(4) OVER(ORDER BY grade DESC) AS 'rank'
FROM v_info
  • 第二步：按照rank筛选出第一组，则得到最终的结果如下：
SELECT a.*
FROM
		(SELECT *,
			  ntile(4) OVER(ORDER BY grade DESC) AS 'rank'
		FROM v_info) AS a
WHERE a.rank=1

（四）偏移分析窗口函数

lag() over()和lead() over()窗口函数，lag和lead分析函数可以在同一次查询中取出同一个字段的前N行数据（lag）和后N行（lead）作为独立的列。

在实际应用当中，若要用到取今天和昨天的某字段的差值时，lag和lead函数的应用就显得尤为重要了。

适用场景：获取用户在某个页面停留的起始与结束时间

语法结构：

lag(exp_str,offset,defval) over(partition by ... order by...)
lead(exp_str,offset,defval) over(partition by ... order by...)

-- exp_str表示字段名称
-- offset偏移量，假设当前行在表中排在第5行，则offset为3，则表示我们所要找的数据行就是表中的第2行（即5-3=2）
-- offset默认为1

（1）向前推1个日期

SELECT *,
       LAG(birth,1,0) OVER(PARTITION BY sex) AS 'lag_1'
FROM v_info
  • 第一条记录，往前推没有，则为0，因为我设置了为0，默认为NULL；
  • 第四条记录是在男生组里，所以也相当于第一条记录，所以也为0；

（2）向后推1个日期

SELECT *,
       LEAD(birth,1,'无') OVER(PARTITION BY sex) AS 'lead_1'
FROM v_info
  • 在女生组里，第三条记录往后推1个日期是没有的，所以为无；
  • 在男生组里，最后一条记录网后也是没有的，所以也为无。

（3） 统计每天符合以下条件的用户数：A操作之后是B操作，AB操作必须相邻。

用户行为表racking_log（user_id,operate_id,log_time）

解题思路：

  • 先根据用户ID和日期，用LEAD()窗口函数向后获取下一步的步骤；
  • AB必须相邻，则表明当前的步骤为A，而下一个步骤为B，即A向下移的步骤是B；
  • “每天”，即根据日期进行分组。
SELECT a.log_date,
       COUNT(DISTINCT a.user_id)
FROM
	(SELECT user_id,
		operate_id,
		DATE_FORMAT(log_time,'%Y-%m-%d') AS log_date,
		LEAD(operate_id,1,NULL) OVER(PARTITION BY user_id,DATE_FORMAT(log_time,'%Y-%m-%d') ORDER BY log_time) AS 'next_operate'
	FROM tracking_log) AS a			 
WHERE a.operate_id=A AND b.next_operate=B
GROUP BY a.log_date

 

（4）现在有某个登录表，找出连续登录7天以上的用户（看SQL面试题一）

 

tips：窗口函数和普通函数的区别在于：普通聚合函数结果返回的是一条，将多条记录合成一条，而窗口函数是有几条记录就返回几条。