这篇主要是用举栗子的方式来理解SQL中的窗口函数,加深大家对SQL窗口函数的理解。
![](https://pic3.zhimg.com/80/v2-3dbec8a4cf3e415daea22728dd79d7e2_720w.webp)
这个样例表是我为了好理解,随便设计的,不符合数据库设计的三范式,请忽略。
(一)标准聚合函数
标准的聚合函数有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
![](https://pic2.zhimg.com/80/v2-f31a3b957d42cc2e12a9eff7037c2219_720w.webp)
- 对于第一行来说,没有前面两行,所以值就为当前行的值
- 对于第二行来说,前面只有一行,所以三移动平均就为第一行和第二行的平均值
影响行数范围的语句在标准的聚合函数中都适用。
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
![](https://pic1.zhimg.com/80/v2-6bdbccd0af5620e0821ed79a787e70dc_720w.webp)
这个结果说明,成绩大于90分的,有两位同学。
(2)按照课程号进行分组,找出成绩大于等于80分的学生人数
SELECT *,
COUNT(*) OVER(PARTITION BY c_no) AS 'ct'
FROM v_info
WHERE grade>=80
![](https://pic4.zhimg.com/80/v2-1c8c97c50683db9f318f5154b40e4903_720w.webp)
从结果上可以看出,课程号为“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
![](https://pic4.zhimg.com/80/v2-d4af4e0db3143e22cddbe7c2a86b071b_720w.webp)
(2)按照课程号分组,然后根据学号对成绩进行累积求和
SELECT *,
SUM(grade) OVER(PARTITION BY c_no ORDER BY stu_no) AS '累积求和'
FROM v_info
![](https://pic3.zhimg.com/80/v2-70ce3aac17da24ec38e9822d25dab75e_720w.webp)
tips:一定要选择根据学号排序,要不然得出来的是最终的累积求和结果,如下图:
SELECT *,
SUM(grade) OVER(PARTITION BY c_no) AS '累积求和'
FROM v_info
![](https://pic4.zhimg.com/80/v2-de182247a79e7c8c65c9c0349907f06b_720w.webp)
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
![](https://pic2.zhimg.com/80/v2-02dd0f762e05dd12312a7a003a801201_720w.webp)
按照学号进行排序,在累积最大值中,会依次往下找最大值,如果有比当前值大的,就更新,若没有就保持当前;最小值同理。
(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
![](https://pic4.zhimg.com/80/v2-3537cc01e3a060d533fcaed01a5a9c8f_720w.webp)
(3)根据学生号和课程号求成绩的累积最小值
SELECT stu_no,c_no,stu_name,sex,birth,grade,
MIN(grade) OVER(PARTITION BY stu_no,c_no) AS '累积最小值'
FROM v_info
![](https://pic1.zhimg.com/80/v2-7677c6d67903aa499a55260b19fc98b4_720w.webp)
从上图可以看出,对于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
![](https://pic3.zhimg.com/80/v2-a00718c010916677dd937c6bc05e347a_720w.webp)
- 第二步:按照rank筛选出第一组,则得到最终的结果如下:
SELECT a.*
FROM
(SELECT *,
ntile(4) OVER(ORDER BY grade DESC) AS 'rank'
FROM v_info) AS a
WHERE a.rank=1
![](https://pic4.zhimg.com/80/v2-7d0155116da546877b4d966b79294577_720w.webp)
(四)偏移分析窗口函数
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
![](https://pic1.zhimg.com/80/v2-b22f619e82857200a0aea38c99355708_720w.webp)
- 第一条记录,往前推没有,则为0,因为我设置了为0,默认为NULL;
- 第四条记录是在男生组里,所以也相当于第一条记录,所以也为0;
(2)向后推1个日期
SELECT *,
LEAD(birth,1,'无') OVER(PARTITION BY sex) AS 'lead_1'
FROM v_info
![](https://pic2.zhimg.com/80/v2-bf52645b29a0fd7c00aa04dc72dc0689_720w.webp)
- 在女生组里,第三条记录往后推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:窗口函数和普通函数的区别在于:普通聚合函数结果返回的是一条,将多条记录合成一条,而窗口函数是有几条记录就返回几条。
标签:窗口,函数,no,grade,OVER,实例,SELECT,SQL,字段名 From: https://www.cnblogs.com/superstar/p/17297871.html