这篇主要是用举栗子的方式来理解SQL中的窗口函数，加深大家对SQL窗口函数的理解。
样例表
这个样例表是我为了好理解，随便设计的，不符合数据库设计的三范式，请忽略。
标准的聚合函数有avg、count、sum、max和min，接下来分别介绍这些聚合函数的窗口函数形式。
移动平均值的定义：若依次得到测定值（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
影响行数范围的语句在标准的聚合函数中都适用。
窗口 函数 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名。
语法结构：
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
语法结构：
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日每天做新题的人的数量，重点在每天。
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组（1）取出成绩前25%的学生信息
SELECT *,
ntile(4) OVER(ORDER BY grade DESC) AS 'rank'
FROM v_info
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
（2）向后推1个日期
SELECT *,
LEAD(birth,1,'无') OVER(PARTITION BY sex) AS 'lead_1'
FROM v_info
（3） 统计每天符合以下条件的用户数：A操作之后是B操作，AB操作必须相邻。
用户行为表racking_log（user_id,operate_id,log_time）
解题思路：
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：窗口函数和普通函数的区别在于：普通聚合函数结果返回的是一条，将多条记录合成一条，而窗口函数是有几条记录就返回几条。
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。