阅读之前看这里👉：博主是正在学习数据分析的一员，博客记录的是在学习过程中一些总结，也希望和大家一起进步，在记录之时，未免存在很多疏漏和不全，如有问题，还请私聊博主指正。
博客地址：天阑之蓝的博客，学习过程中不免有困难和迷茫，希望大家都能在这学习的过程中肯定自己，超越自己，最终创造自己。
MYSQL常见面试题之——窗口函数知识总结
复习知识点：窗口函数
一、窗口函数：
窗口函数也称为OLAP函数，OLAP 是OnLine Analytical Processing 的简称，意思是对数据库数据
进行实时分析处理。例如，市场分析、创建财务报表、创建计划等日常性商务工作。窗口函数就是为了实现OLAP 而添加的标准SQL 功能。
1.窗口函数的基本用法：
<窗口函数> OVER ([PARTITION BY <列清单>]
ORDER BY <排序用列清单>)
over关键字用来指定函数执行的窗口范围,若后面括号中什么都不写,则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算；如果不为空,则支持以下4中语法来设置窗口。
①window_name:给窗口指定一个别名。如果SQL中涉及的窗口较多,采用别名可以看起来更清晰易读
②partition by子句：窗口按照哪些字段进行分组,窗口函数在不同的分组上分别执行
③order by子句：按照哪些字段进行排序,窗口函数将按照排序后的记录顺序进行编号
④frame子句：frame是当前分区的一个子集,子句用来定义子集的规则,通常用来作为滑动窗口使用
2.窗口函数与普通聚合函数的区别：
①聚合函数是将多条记录聚合为一条；窗口函数是每条记录都会执行，有几条记录执行完还是几条。
窗口函数兼具之前我们学过的GROUP BY 子句的分组功能以及ORDER BY 子句的排序功能。但是，PARTITION BY子句并不具备GROUP BY 子句的汇总功能。因此，使用RANK 函数并不会减少原表中 记录的行数，结果中仍然包含8 行数据。
窗口函数兼具分组和排序两种功能。
②聚合函数也可以用于窗口函数。
原因就在于窗口函数的执行顺序（逻辑上的）是在FROM，JOIN，WHERE，GROUP BY，HAVING之后，在ORDER BY，LIMIT，SELECT DISTINCT之前。它执行时GROUP BY的聚合过程已经完成了，所以不会再产生数据聚合。
注:窗口函数是在where之后执行的，所以如果where子句需要用窗口函数作为条件，需要多一层查询，在子查询外面进行,例如:求30天内后一天比前一天平均时间差
select user_id,avg(diff)
from
(
select user_id,lead(log_time)over(partition by user_id order by log_time) - log_time as diff
from user_log
)t
where datediff(now(),t.log_time)<=30
group by user_id
作为窗口函数使用的聚合函数
所有的聚合函数都能用作窗口函数，其语法和专用窗口函数完全相同。但大家可能对所能得到的结果还没有一个直观的印象，所以我们还是通过具体的示例来学习。
SELECT product_id, product_name, sale_price,
SUM (sale_price) OVER (ORDER BY product_id) AS current_sum
FROM Product;
我们得到的并不仅仅是合计值，而是按照ORDER BY 子句指定的product_id 的升序进行排列，计算出商品编号“小于自己”的商品的销售单价的合计值。因此，计算该合计值的逻辑就像金字塔堆积那样，一行一行逐渐添加计算对象。在按照时间序列的顺序，计算各个时间的销售额总额等的时候，通常都会使用这种称为累计的统计方法。使用其他聚合函数时的操作逻辑也和本例相同。例如，使用AVG 来代替SELECT 语句中的SUM:
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id) AS current_avg
FROM Product;
从结果中我们可以看到，current_avg 的计算方法确实是计算平均值的方法，但作为统计对象的却只是“排在自己之上”的记录。像这样以“自身记录（当前记录）”作为基准进行统计，就是将聚合函数当作窗口函数使用时的最大特征。
计算移动平均
窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。其实其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架。
例：指定“最靠近的3行”作为汇总对象
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id
ROWS 2 PRECEDING) AS moving_avg
FROM Product;
- 指定框架（汇总范围）
我们将上述结果与之前的结果进行比较，可以发现商品编号为“0004”的“菜刀”以下的记录和窗口函数的计算结果并不相同。这是因为我们指定了框架，将汇总对象限定为了“最靠近的3 行”。
这里我们使用了**ROWS（“行”）和PRECEDING（“之前”）**两个关键字，将框架指定为“截止到之前~ 行”，因此“ROWS 2 PRECEDING”就是将框架指定为“截止到之前2 行”，也就是将作为汇总对象的记录限
定为如下的“最靠近的3 行”。
● 自身（当前记录）
● 之前1行的记录
● 之前2行的记录
也就是说，由于框架是根据当前记录来确定的，因此和固定的窗口不同，其范围会随着当前记录的变化而变化。
这样的统计方法称为移动平均（moving average）。由于这种方法在希望实时把握“最近状态”时非常方便，因此常常会应用在对股市趋势的实时跟踪当中。
使用关键字FOLLOWING（“之后”）替换PRECEDING，就可以指定“截止到之后~ 行”作为框架了。
将当前记录的前后行作为汇总对象
如果希望将当前记录的前后行作为汇总对象时，就可以同时使PRECEDING（“之前”）和FOLLOWING（“之后”）关键字来实现。
例：将当前记录的前后行作为汇总对象
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM Product;
在上述代码中，我们通过指定框架，将“1 PRECEDING”（之前1 行）和“1 FOLLOWING”（之后1 行）的区间作为汇总对象。具体来说，就是将如下3 行作为汇总对象来进行计算：
3.（面试考点）序号函数:row_number(),rank(),dense_rank()的区别
ROW_NUMBER():顺序排序——1、2、3
RANK():并列排序，跳过重复序号——1、1、3
DENSE_RANK():并列排序，不跳过重复序号——1、1、2
详见：MySQL数据库知识要点归纳和总结中窗口排序函数
4.分布函数:percent_rank(),cume_dist()
percent_rank():
每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数
--给窗口指定别名：WINDOW w AS (PARTITION BY stu_id ORDER BY score) rows = 5
mysql> SELECT
-> RANK() OVER w AS rk,
-> PERCENT_RANK() OVER w AS prk,
-> stu_id, lesson_id, score
-> FROM t_score
-> WHERE stu_id = 1
-> WINDOW w AS (PARTITION BY stu_id ORDER BY score)
-> ;
+----+------+--------+-----------+-------+
| rk | prk | stu_id | lesson_id | score |
+----+------+--------+-----------+-------+
| 1 | 0 | 1 | L003 | 79 |
| 2 | 0.25 | 1 | L002 | 86 |
| 3 | 0.5 | 1 | L004 | 88 |
| 4 | 0.75 | 1 | L005 | 98 |
| 4 | 0.75 | 1 | L001 | 98 |
+----+------+--------+-----------+-------+
cume_dist():
分组内小于、等于当前rank值的行数 / 分组内总行数 eg:查询小于等于当前成绩（score）的比例
--cd1：没有分区，则所有数据均为一组，总行数为8
--cd2：按照lesson_id分成了两组，行数各为4
mysql> SELECT stu_id, lesson_id, score,
-> CUME_DIST() OVER (ORDER BY score) AS cd1,
-> CUME_DIST() OVER (PARTITION BY lesson_id ORDER BY score) AS cd2
-> FROM t_score
-> WHERE lesson_id IN ('L001','L002')
-> ;
+--------+-----------+-------+-------+------+
| stu_id | lesson_id | score | cd1 | cd2 |
+--------+-----------+-------+-------+------+
| 2 | L001 | 84 | 0.125 | 0.25 |
| 1 | L001 | 98 | 0.75 | 0.5 |
| 4 | L001 | 99 | 0.875 | 0.75 |
| 3 | L001 | 100 | 1 | 1 |
| 1 | L002 | 86 | 0.25 | 0.25 |
| 4 | L002 | 88 | 0.375 | 0.5 |
| 2 | L002 | 90 | 0.5 | 0.75 |
| 3 | L002 | 91 | 0.625 | 1 |
+--------+-----------+-------+-------+------+
5.前后函数:lag(expr,n),lead(expr,n)
用途：返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值
应用场景：查询前1名同学的成绩和当前同学成绩的差值
mysql> SELECT stu_id, lesson_id, score, pre_score,
-> score-pre_score AS diff
-> FROM(
-> SELECT stu_id, lesson_id, score,
-> LAG(score,1) OVER w AS pre_score
-> FROM t_score
-> WHERE lesson_id IN ('L001','L002')
-> WINDOW w AS (PARTITION BY lesson_id ORDER BY score)) t
-> ;
+--------+-----------+-------+-----------+------+
| stu_id | lesson_id | score | pre_score | diff |
+--------+-----------+-------+-----------+------+
| 2 | L001 | 84 | NULL | NULL |
| 1 | L001 | 98 | 84 | 14 |
| 4 | L001 | 99 | 98 | 1 |
| 3 | L001 | 100 | 99 | 1 |
| 1 | L002 | 86 | NULL | NULL |
| 4 | L002 | 88 | 86 | 2 |
| 2 | L002 | 90 | 88 | 2 |
| 3 | L002 | 91 | 90 | 1 |
+--------+-----------+-------+-----------+------+
6.头尾函数：FIRST_VALUE(expr),LAST_VALUE(expr)
用途：返回第一个（FIRST_VALUE(expr)）或最后一个（LAST_VALUE(expr)）expr的值
应用场景：截止到当前成绩，按照日期排序查询第1个和最后1个同学的分数
mysql> SELECT stu_id, lesson_id, score, create_time,
-> FIRST_VALUE(score) OVER w AS first_score,
-> LAST_VALUE(score) OVER w AS last_score
-> FROM t_score
-> WHERE lesson_id IN ('L001','L002')
-> WINDOW w AS (PARTITION BY lesson_id ORDER BY create_time)
-> ;
+--------+-----------+-------+-------------+-------------+------------+
| stu_id | lesson_id | score | create_time | first_score | last_score |
+--------+-----------+-------+-------------+-------------+------------+
| 3 | L001 | 100 | 2018-08-07 | 100 | 100 |
| 1 | L001 | 98 | 2018-08-08 | 100 | 98 |
| 2 | L001 | 84 | 2018-08-09 | 100 | 99 |
| 4 | L001 | 99 | 2018-08-09 | 100 | 99 |
| 3 | L002 | 91 | 2018-08-07 | 91 | 91 |
| 1 | L002 | 86 | 2018-08-08 | 91 | 86 |
| 2 | L002 | 90 | 2018-08-09 | 91 | 90 |
| 4 | L002 | 88 | 2018-08-10 | 91 | 88 |
+--------+-----------+-------+-------------+-------------+------------+
二、面试题
1.用户行为分析
表1——用户行为表tracking_log，大概字段有（user_id‘用户编号’,opr_id‘操作编号’,log_time‘操作时间’）如下所示：
| user_id | opr_id | log_time |
|---|---|---|
| 001 | A | 2020-01-01 12:01:44 |
| 001 | B | 2020-01-01 12:02:44 |
| 002 | C | 2020-02-01 11:01:44 |
| 002 | A | 2020-02-03 12:02:44 |
问题：
1.统计每天符合以下条件的用户数：A操作之后是B操作，AB操作必须相邻
分析：
（1）统计每天，所以需要按天分组统计求和
（2）A操作之后是B，且AB操作必须相邻，那就涉及一个前后问题，所以想到用窗口函数中的lag()或lead()。
解答：
select date,count(*)
from(
select user_id
from(
select user_id,convert(log_time,date) date,opr_id f,lag(opr_id,1) over(partition by user_id,convert(log_time,date) order by log_time) l
from tracking_log
) a
where f='A' and l='B'
) b
group by date;
2.统计用户行为序列为A-B-D的用户数,其中:A-B之间可以有任何其他浏览记录(如C,E等),B-D之间除了C记录可以有任何其他浏览记录(如A,E等)
select count(*)
from(
select user_id,group_concat(opr_id) ubp
from tracking_log
group by user_id
) a
where ubp like '%A%B%D%' and ubp not like '%A%B%C%D%'
2.学生成绩分析
表：Enrollments
±--------------±--------+
| Column Name | Type |
±--------------±--------+
| student_id | int |
| course_id | int |
| grade | int |
±--------------±--------+
(student_id, course_id) 是该表的主键。
| student_id | course_id | grade |
|---|---|---|
| 001 | 1 | 60 |
| 001 | 2 | 75 |
| 002 | 2 | 33 |
| 002 | 3 | 97 |
1.查询每位学生获得的最高成绩和它所对应的科目，若科目成绩并列，取 course_id 最小的一门。查询结果需按 student_id 增序进行排序。
分析：因为需要最高成绩和所对应的科目，所以可采用窗口函数排序分组取第一个
按每位学生的成绩排名
SELECT student_id,course_id,grade
RANK_NUMBR() OVER (PARTITION BY student_id order by grade DESC) as Rank
FROM Enrollments
ORDER BY Rank, course_id
取其中最高的成绩
SELECT student_id,course_id,grade
FROM (SELECT student_id,course_id,grade
RANK_NUMBR() OVER (PARTITION BY student_id order by grade DESC) as Rank
FROM Enrollments
ORDER BY Rank, course_id) as A
where A.Rank = 1
order by student_id
解法2：IN 解法
先取最大成绩
SELECT student_id,max(grade)
FROM Enrollments
GROUP BY student_id
然后取成绩在最大成绩之中的学生的最小课程号的课程
select student_id,min(course_id)
from Enrollments
where (student_id,grade) in (
select student_id,max(grade)
from Enrollments
group by student_id)
group by student_id
order by student_id;
2.查询每一科目成绩最高和最低分数的学生,输出course_id,student_id,score
我们可以按科目查找成绩最高的同学和最低分的同学，然后利用union连接起来
select c_id,s_id
from(
select *,row_number() over(partition by c_id order by s_score desc) r
from score
) a
where r=1
union
select c_id,s_id
from(
select *,row_number() over(partition by c_id order by s_score) r
from score
) a
where r=1;
解法2：case-when
select c_id,
max(case when r1=1 then s_id else null end) '最高分学生',
max(case when r2=1 then s_id else null end) '最低分学生'
from(
select *,row_number() over(partition by c_id order by s_score desc) r1,
row_number() over(partition by c_id order by s_score) r2
from score
) a
group by c_id；
读到这里的同学，相信大家对于窗口函数有了深刻的认识和掌握了吧。
————————————————————————————————————————————————
博主码字不易，大家关注点个赞转发再走呗 ，您的三连是对我创作的最大支持^ - ^
参考：《SQL基础教程》
扫一扫
1829
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
