MySQL/Hive 常用窗口函数详解及相关面试题

目录

一、窗口函数概述：

1.窗口函数的分类

2.窗口函数与普通聚合函数的区别：

二、窗口函数的基本用法

1.基本语法

2.设置窗口的方法

1）window_name

2) partition by 子句

3）order by子句

4）rows 指定窗口大小

3.开窗函数中加order by 和 不加 order by的区别

三、窗口函数用法举例

1.序号函数：row_number() / rank() / dense_rank()（面试重点）

2.分布函数：percent_rank() / cume_dist()

3.前后函数 lag(expr,n,defval)、lead(expr,n,defval)（面试重点）

4.头尾函数：FIRST_VALUE(expr),LAST_VALUE(expr)

5.聚合函数+窗口函数联合使用

四、面试题

1.用户行为分析

2.学生成绩分析

3.其他面试题

---

本文是本人在学习过程中的一些总结，全文比较长，记录了窗口函数的基本用法，各种函数的组合使用实例以及相关的面试题等，如果您耐心看完，相信一定会有所收获。

若有不足之处，欢迎评论指教，一起交流学习~

一、窗口函数概述：

窗口函数也称为OLAP函数，OLAP 是OnLine Analytical Processing 的简称，意思是对数据库数据进行实时分析处理。例如，市场分析、创建财务报表、创建计划等日常性商务工作。窗口函数就是为了实现OLAP 而添加的标准SQL 功能。

1.窗口函数的分类

按照功能划分：

• 序号函数：row_number() / rank() / dense_rank()
• 分布函数：percent_rank() / cume_dist()
• 前后函数：lag() / lead()
• 头尾函数：first_val() / last_val()
• 聚合函数+窗口函数联合：
  • 求和 sum() over()
  • 求最大/小 max()/min() over()
  • 求平均 avg() over()
• 其他函数：nth_value() / nfile()

如上，窗口函数的用法多种多样，不仅有专门的的窗口函数，还可以与聚合函数配合使用。

2.窗口函数与普通聚合函数的区别：

聚合函数是将多条记录聚合为一条；窗口函数是每条记录都会执行，有几条记录执行完还是几条。

窗口函数兼具GROUP BY 子句的分组功能以及ORDER BY 子句的排序功能。但是，PARTITION BY 子句并不具备 GROUP BY 子句的汇总功能。

举例：若原表中有id一样的10行数据，使用GROUP BY，返回的结果是将多条记录聚合成一条；而使用 rank() 等窗口函数并不会减少原表中 记录的行数，结果中仍然包含 10 行数据。

窗口函数兼具分组和排序两种功能。

二、窗口函数的基本用法

如有基础数据：

drop table if exists exam_record;
CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:02', 84),
(1006, 9001, '2021-09-01 12:11:01', '2021-09-01 12:31:01', 89),
(1006, 9002, '2021-09-06 10:01:01', '2021-09-06 10:21:01', 81),
(1005, 9002, '2021-09-05 10:01:01', '2021-09-05 10:21:01', 81),
(1005, 9001, '2021-09-05 10:31:01', '2021-09-05 10:51:01', 81),
(1004, 9002, '2021-09-05 10:01:01', '2021-09-05 10:21:01', 71),
(1004, 9001, '2021-09-05 10:31:01', '2021-09-05 10:51:01', 91),
(1004, 9002, '2021-09-05 10:01:01', '2021-09-05 10:21:01', 80),
(1004, 9001, '2021-09-05 10:31:01', '2021-09-05 10:51:01', 80);

select uid,score from exam_record;

uid	score
1006	84
1006	89
1006	81
1005	81
1005	81
1004	71
1004	91
1004	80
1004	80

1.基本语法

<窗口函数> OVER ([PARTITION BY <列名清单>] ORDER BY <排序列名清单> [rows between 开始位置 and 结束位置])

其中：

<窗口函数>：指需要使用的分析函数，如row_number()、sum()等。

over() : 用来指定函数执行的窗口范围，这个数据窗口大小可能会随着行的变化而变化；

如果括号中什么都不写，则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算。如：

select 
    uid,
    score,
    sum(score) over() as sum_score
from exam_record 

结果：

uid	score	sum_score
1006	84	738
1006	89	738
1006	81	738
1005	81	738
1005	81	738
1004	71	738
1004	91	738
1004	80	738
1004	80	738

sum(score) over() as sum_score 会聚合所有的数据，将结果接到每一行的后面(窗口函数不会改变结果原表行数)。

2.设置窗口的方法

如果不为空,则支持以下4中语法来设置窗口。

1）window_name

给窗口指定一个别名。如果SQL中涉及的窗口较多,采用别名可以看起来更清晰易读，如：

--给窗口指定别名：WINDOW my_window_name AS (PARTITION BY uid ORDER BY score)
SELECT
    uid,
    score,
    rank() OVER my_window_name AS rk_num,
    row_number() OVER my_window_name AS row_num
FROM exam_record
WINDOW my_window_name AS (PARTITION BY uid ORDER BY score)

2) partition by 子句

窗口按照哪些字段进行分组,窗口函数在不同的分组上分别执行，如：

实例1：

SELECT
    uid,
    score,
    sum(score) OVER(PARTITION BY uid) AS sum_score
FROM exam_record

结果：

uid	score	sum_score
1004	71	322
1004	91	322
1004	80	322
1004	80	322
1005	81	162
1005	81	162
1006	84	254
1006	89	254
1006	81	254

sum(score) OVER(PARTITION BY uid) AS sum_score 会按照 uid 分组，分别求和，展示在每个分组的末尾。

如果我想看某个uid有多少行记录，并标明序号该如何实现？使用序号函数row_number()请看：

SELECT
    uid,
    score,
    row_number() OVER(PARTITION BY uid) AS row_num
FROM exam_record

uid	score	row_num
1004	71	1
1004	91	2
1004	80	3
1004	80	4
1005	81	1
1005	81	2
1006	84	1
1006	89	2
1006	81	3

可以看到，row_number()按照uid分组并从上到下按照顺序标号。我们看到1004中的score是无序的，如果想按照score降序排名应该怎么做呢？(实际场景：成绩排名)

可以结合 order by 子句实现

3）order by子句

按照哪些字段进行排序,窗口函数将按照排序后的记录顺序进行编号，如：

SELECT
    uid,
    score,
    row_number() OVER(PARTITION BY uid ORDER BY score desc) AS row_num
FROM exam_record

uid	score	row_num
1004	91	1
1004	80	2
1004	80	3
1004	71	4
1005	81	1
1005	81	2
1006	89	1
1006	84	2
1006	81	3

这样就实现了每个uid内的分数降序排名，order by 后面可以跟多个列名，大家可以试一试。

当order by 与聚合类函数连用时，特别需要注意理解，如下面几个例子：

先看前面的例子，单独使用 partition by uid

SELECT
    uid,
    score,
    sum(score) OVER(PARTITION BY uid) AS sum_score
FROM exam_record

结果：

uid	score	sum_score
1004	71	322
1004	91	322
1004	80	322
1004	80	322
1005	81	162
1005	81	162
1006	84	254
1006	89	254
1006	81	254

单独使用order by uid

SELECT
    uid,
    score,
    sum(score) OVER(ORDER BY uid) AS sum_score
FROM exam_record

结果：

uid	score	sum_score
1004	71	322
1004	91	322
1004	80	322
1004	80	322
1005	81	484
1005	81	484
1006	84	738
1006	89	738
1006	81	738

注意观察uid 从1004->1005的变化，两条SQL的结果有什么不同：

• partition by 按照uid分组，分别对score求和，”接到每一行的末尾“
  • 分组内求和，分组间相互独立。
• order by 按照uid排序，对”序号“相同的元素进行求和，不同”序号“的数累加求和
  • 如果将”序号“认为是分组的话，个人理解这是一个分组求和并累加的过程
  • 即分组内求和，分组间累加。

再看，order by score 的例子

SELECT
    uid,
    score,
    sum(score) OVER(ORDER BY score) AS sum_score
FROM exam_record

结果：

uid	score	sum_score
1004	71	71
1004	80	231
1004	80	231
1006	81	474
1005	81	474
1005	81	474
1006	84	558
1006	89	647
1004	91	738

总结一下：

如果使⽤环境为hive，over()开窗函数前分排序函数和聚合函数两种。

当为排序函数，如row_number(),rank()等时，over中的order by只起到窗⼝内排序作⽤。

当为聚合函数，如max，min，count等时，over中的order by不仅起到窗⼝内排序，还起到窗⼝内从当前⾏到之前所有⾏的聚合（多了⼀个范围）。

4）rows 指定窗口大小

a.先看个例子，按照顺序，求score的平均值：

SELECT
    uid,
    score,
    avg(score) OVER(ORDER BY score desc) AS avg_score
FROM exam_record

注意score相同的部分：

uid	score	avg_score	解释
1004	91	91.0000	91/1
1006	89	90.0000	(91+89)/2
1006	84	88.0000	(91+89+84)/3
1006	81	84.5000	(91+89+84+81+81+81)/6
1005	81	84.5000	(91+89+84+81+81+81)/6
1005	81	84.5000	(91+89+84+81+81+81)/6
1004	80	83.3750	(91+89+84+81+81+81+80+80)/8
1004	80	83.3750	(91+89+84+81+81+81+80+80)/8
1004	71	82.0000	(91+89+84+81+81+81+80+80+71)/9

如果想要sql先按照score降序排列，每一行计算从第一行到当前行的score平均值，该怎么计算呢？——想办法做一个不重复的key

实现：

SELECT
    uid,
    score,
    row_score,
    avg(score) OVER(ORDER BY row_score) AS avg_score
FROM (
    SELECT
        uid,
        score,
        row_number() OVER(ORDER BY score desc) AS row_score
    FROM exam_record
) res

uid	score	row_score	avg_score	解释
1004	91	1	91.0000	91/1
1006	89	2	90.0000	(91+89)/2
1006	84	3	88.0000	(91+89+84)/3
1006	81	4	86.2500	(91+89+84+81)/4
1005	81	5	85.2000	(91+89+84+81+81)/5
1005	81	6	84.5000	(91+89+84+81+81+81)/6
1004	80	7	83.8571	(91+89+84+81+81+81+80)/7
1004	80	8	83.3750	(91+89+84+81+81+81+80+80)/8
1004	71	9	82.0000	(91+89+84+81+81+81+80+80+71)/9

现在改下需求，希望求"我与前两名的平均值"应该怎么实现呢？

分析一下，"我与前两名"指的是当前行以及前两行总共三行数据求平均，也就是说，我们需要限定窗口的范围或者窗口大小。

b.引入窗口框架

指定窗口大小，又称为窗口框架。框架是对窗口进行进一步分区，框架有两种范围限定方式：

一种是使用 ROWS 子句，通过指定当前行之前或之后的固定数目的行来限制分区中的行数。

另一种是使用 RANGE 子句，按照排列序列的当前值，根据相同值来确定分区中的行数。

语法：

ORDER BY 字段名 RANGE|ROWS 边界规则0 | [BETWEEN 边界规则1 AND 边界规则2]

RANGE | ROWS的区别是什么？

RANGE表示按照值的范围进行范围的定义，而ROWS表示按照行的范围进行范围的定义；边界规则的可取值见下表：

可取值	说明	示例
CURRENT ROW	当前行
n PRECEDING	往前 n 行数据，单独使用包含当前行	2 PRECEDING
UNBOUNDED PRECEDING	一直到第一条记录，单独使用包含当前行
n FOLLOWING	往后 n 行数据，单独使用包含当前行	3 FOLLOWING
UNBOUNDED FOLLOWING	一直到最后一条记录，单独使用包含当前行

需要注意：

• 当使用框架时，必须要有 order by 子句，如果仅指定了order by 子句而未指定框架，那么默认框架将采用 RANGE UNBOUNDED PRECEDING AND CURRENT ROW（表示当前行以及一直到第一行的数据）。
• 如果窗口函数没有指定 order by 子句，也就不存在 ROWS/RANGE 窗口的计算。
• PS: RANGE 只支持使用 UNBOUNDED 和 CURRENT ROW 窗口框架分隔符。

OK，回到前面的需求：求"我与前两名的平均值"。因为要"我与前两名"，所以我们会用到规则 2 PRECEDING

之前2行的记录
之前1行的记录
自身（当前记录）

SELECT
    uid,
    score,
    avg(score) OVER(ORDER BY score desc rows 2 PRECEDING) AS avg_score
FROM exam_record

uid	score	avg_score	解释
1004	91	91.0000	(91)/1
1006	89	90.0000	(91+89)/2
1006	84	88.0000	(91+89+84)/3
1006	81	84.6667	(89+84+81)/3
1005	81	82.0000	(84+81+81)/3
1005	81	81.0000	(81+81+81)/3
1004	80	80.6667	(81+81+80)/3
1004	80	80.3333	(81+80+80)/3
1004	71	77.0000	(80+80+71)/3

如果要求当前行及前后一行呢？

之前1行的记录
自身（当前记录）
之后1行的记录

SELECT
    uid,
    score,
    avg(score) OVER(ORDER BY score desc rows between 1 PRECEDING and 1 FOLLOWING) AS avg_score
FROM exam_record

 结果略~

其他组合举例：

1.第一行到当前行
ORDER BY score desc rows UNBOUNDED PRECEDING

2.第一行到前一行(不含当前行)
ORDER BY score desc rows between UNBOUNDED PRECEDING and 1 PRECEDING 

3.第一行到后一行(包含当前行)
ORDER BY score desc rows between UNBOUNDED PRECEDING and 1 FOLLOWING

4.当前行到最后一行
ORDER BY score desc rows between CURRENT ROW and UNBOUNDED FOLLOWING 
注意，这种写法是错误的
ORDER BY score desc rows UNBOUNDED FOLLOWING -- 错误示范

5.前一行到最后一行(包含当前行)
ORDER BY score desc rows between 1 PRECEDING and UNBOUNDED FOLLOWING

6.后一行到最后一行(不含当前行)
ORDER BY score desc rows between 1 FOLLOWING and UNBOUNDED FOLLOWING

7.前一行到后一行(包含当前行) 
ORDER BY score desc rows between 1 PRECEDING and 1 FOLLOWING 

3.开窗函数中加order by 和 不加 order by的区别

如果使⽤环境为hive，over()开窗函数前分排序函数和聚合函数两种。

当为排序函数，如row_number(),rank()等时，over中的order by只起到窗⼝内排序作⽤。

当为聚合函数，如max，min，count等时，over中的order by不仅起到窗⼝内排序，还起到窗⼝内从当前⾏到之前所有⾏的聚合（多了⼀个范围）。

如：

-- sql ①
select id, dept, salary, min(salary) over(partition by dept) min_sal from dept;  

-- sql ②
select id, dept, salary, min(salary) over(partition by dept order by id) min_sal from dept; 

上⾯①②中的min_salary字段的值会不⼀样，原因是②中使⽤order by后，等同于 min(salary) over(partition by dept order by userid range between unbounded preceding and current row )，当然可以在order by后使⽤框架⼦句，即rows，range等，如果没有写框架⼦句，就默认在窗⼝范围中当前⾏到之前所有⾏的数据进⾏统计。

再看个例子：

# 表数据为：exam_record(uid,exam_id,start_time,end_time,score)
# (1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 100),
# (1001, 9001, '2021-09-02 09:01:01', '2021-09-01 09:31:00', 100),
# (1001, 9001, '2021-09-03 09:01:01', '2021-09-01 09:31:00', 100),
# (1002, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 100),
# (1002, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 100),
# (1002, 9001, '2021-09-02 09:01:01', '2021-09-01 09:31:00', 100); 
-- 执行下面的sql
select 
    uid,
    exam_id,
    start_time,
    sum(score) over(partition by uid) as one,
    sum(score) over(partition by uid order by start_time) as two
from exam_record

得到结果：

uid	exam_id	start_time	one	two
1001	9001	2021-09-01 09:01:01	300	100
1001	9001	2021-09-02 09:01:01	300	200
1001	9001	2021-09-03 09:01:01	300	300
1002	9001	2021-09-01 09:01:01	300	200
1002	9001	2021-09-01 09:01:01	300	200
1002	9001	2021-09-02 09:01:01	300	300

需要注意表中标注的部分

三、窗口函数用法举例

1.序号函数：row_number() / rank() / dense_rank()（面试重点）

三者区别：

• RANK(): 并列排序，跳过重复序号——1、1、3
• ROW_NUMBER(): 顺序排序——1、2、3
• DENSE_RANK(): 并列排序，不跳过重复序号——1、1、2

--给窗口指定别名：WINDOW my_window_name AS (PARTITION BY uid ORDER BY score)
SELECT
    uid,
    score,
    rank() OVER my_window_name AS rk_num,
    row_number() OVER my_window_name AS row_num
FROM exam_record
WINDOW my_window_name AS (PARTITION BY uid ORDER BY score)

uid	score	rank_num	row_number_num	dense_rank_num
1004	91	1	1	1
1004	80	2	2	2
1004	80	2	3	2
1004	71	4	4	3
1005	81	1	1	1
1005	81	1	2	1
1006	89	1	1	1
1006	84	2	2	2
1006	81	3	3	3

我们来探索一下，如果不使用窗口函数，如何实现分数排序呢？（使用自连接的方法）

-- 相当于 rank()
SELECT 
    P1.uid,
    P1.score,
    (SELECT 
        COUNT(P2.score)
    FROM exam_record P2 
    WHERE P2.score > P1.score) + 1 AS rank_1
FROM exam_record P1
ORDER BY rank_1;

uid	score	rank_1
1004	91	1
1006	89	2
1006	84	3
1006	81	4
1005	81	4
1005	81	4
1004	80	7
1004	80	7
1004	71	9

这里1234447..，如果想要7改为5呢，不跳过位次。相当于DENSE_RANK 函数。 

只需要改 COUNT(P2.score) 为 COUNT(distinct P2.score) 即可。

2.分布函数：percent_rank() / cume_dist()

1）percent_rank():

percent_rank()函数将某个数值在数据集中的排位作为数据集的百分比值返回，此处的百分比值的范围为 0 到 1。 此函数可用于计算值在数据集内的相对位置。如班级成绩为例，返回的百分数30%表示某个分数排在班级总分排名的前30%。

每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数。

SELECT
    uid,
    score,
    rank() OVER my_window_name AS rank_num,
    PERCENT_RANK() OVER my_window_name AS prk
FROM exam_record
WINDOW my_window_name AS (ORDER BY score desc)

uid	score	rank_num	prk
1004	91	1	0.00
1006	89	2	0.12
1006	84	3	0.25
1006	81	4	0.38
1005	81	4	0.38
1005	81	4	0.38
1004	80	7	0.75
1004	80	7	0.75
1004	71	9	1.00

2）cume_dist():

如果按升序排列，则统计：小于等于当前值的行数/总行数。

如果是降序排列，则统计：大于等于当前值的行数/总行数。

如:查询小于等于当前成绩（score）的比例。

SELECT
    uid,
    score,
    rank() OVER my_window_name AS rank_num,
    cume_dist() OVER my_window_name AS cume_dist_num
FROM exam_record
WINDOW my_window_name AS (ORDER BY score asc)

uid	score	rank_num	cume_dist_num
1004	71	1	0.11
1004	80	2	0.33
1004	80	2	0.33
1006	81	4	0.67
1005	81	4	0.67
1005	81	4	0.67
1006	84	7	0.78
1006	89	8	0.89
1004	91	9	1.00

3.前后函数 lag(expr,n,defval)、lead(expr,n,defval)（面试重点）

Lag()和Lead()分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。

在实际应用当中，若要用到取今天和昨天的某字段差值时，Lag()和Lead()函数的应用就显得尤为重要。当然，这种操作可以用表的自连接实现，但是LAG()和LEAD()与left join、rightjoin等自连接相比，效率更高，SQL更简洁。下面我就对这两个函数做一个简单的介绍。

函数语法如下：

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

其中

• exp_str 是字段名
• Offset 是偏移量，即是上1个或上N个的值，假设当前行在表中排在第5行，offset 为3，则表示我们所要找的数据行就是表中的第2行（即5-3=2）。
• Defval 默认值，当两个函数取 上N 或者 下N 个值，当在表中从当前行位置向前数N行已经超出了表的范围时，lag() 函数将defval这个参数值作为函数的返回值，若没有指定默认值，则返回NULL，那么在数学运算中，总要给一个默认值才不会出错。

用途：

• 返回位于当前行的前n行的expr的值：LAG(expr,n)
• 返回位于当前行的后n行的expr的值：LEAD(expr,n)

举例：查询前1名同学及后一名同学的成绩和当前同学成绩的差值(只排分数，不按uid分组)

先将前一名和后一名的分数与当前行的分数放在一起：

SELECT
    uid,
    score,
    LAG(score,1,0) OVER my_window_name AS `前一名分数`,
    LEAD(score,1,0) OVER my_window_name AS `后一名分数`
FROM exam_record
WINDOW my_window_name AS (ORDER BY score desc)

uid	score	前一名分数	后一名分数
1004	91	0	89
1006	89	91	84
1006	84	89	81
1006	81	84	81
1005	81	81	81
1005	81	81	80
1004	80	81	80
1004	80	80	71
1004	71	80	0

再做diff：

SELECT
    uid,
    score,
    score - `前一名分数` AS `与前一名分差`,
    score - `后一名分数` AS `与后一名分差`
FROM (
    SELECT
        uid,
        score,
        LAG(score,1,0) OVER my_window_name AS `前一名分数`,
        LEAD(score,1,0) OVER my_window_name AS `后一名分数`
    FROM exam_record
    WINDOW my_window_name AS (ORDER BY score desc)
) res

uid	score	与前一名分差	与后一名分差
1004	91	91	2
1006	89	-2	5
1006	84	-5	3
1006	81	-3	0
1005	81	0	0
1005	81	0	1
1004	80	-1	0
1004	80	0	9
1004	71	-9	71

4.头尾函数：FIRST_VALUE(expr),LAST_VALUE(expr)

用途：

• 返回第一个expr的值：FIRST_VALUE(expr)
• 返回最后一个expr的值：LAST_VALUE(expr)

应用场景：截止到当前成绩，按照分数排序查询第1个和最后1个的分数

    SELECT
        uid,
        score,
        FIRST_VALUE(score) OVER my_window_name AS `第一行分数`,
        LAST_VALUE(score) OVER my_window_name AS `最后一行分数`
    FROM exam_record
    WINDOW my_window_name AS (ORDER BY score desc)

uid	score	第一行分数	最后一行分数
1004	91	91	91
1006	89	91	89
1006	84	91	84
1006	81	91	81
1005	81	91	81
1005	81	91	81
1004	80	91	80
1004	80	91	80
1004	71	91	71

5.聚合函数+窗口函数联合使用

聚合函数也可以用于窗口函数。

原因就在于窗口函数的执行顺序（逻辑上的）是在FROM，JOIN，WHERE，GROUP BY，HAVING之后，在ORDER BY，LIMIT，SELECT DISTINCT之前。它执行时GROUP BY的聚合过程已经完成了，所以不会再产生数据聚合。

注:窗口函数是在where之后执行的，所以如果where子句需要用窗口函数作为条件，需要多一层查询，在子查询外面进行

前面基本用法中已经有部分举例，如：

    SELECT
        uid,
        score,
        sum(score) OVER my_window_name AS sum_score,
        max(score) OVER my_window_name AS max_score,
        min(score) OVER my_window_name AS min_score,
        avg(score) OVER my_window_name AS avg_score
    FROM exam_record
    WINDOW my_window_name AS (ORDER BY score desc)

uid	score	sum_score	max_score	min_score	avg_score
1004	91	91	91	91	91.0000
1006	89	180	91	89	90.0000
1006	84	264	91	84	88.0000
1006	81	507	91	81	84.5000
1005	81	507	91	81	84.5000
1005	81	507	91	81	84.5000
1004	80	667	91	80	83.3750
1004	80	667	91	80	83.3750
1004	71	738	91	71	82.0000

mysql> SELECT 
    ->     stu_id, 
    ->     lesson_id, 
    ->     score, 
    ->     create_time,
    ->     FIRST_VALUE(score) OVER w AS first_score, -- 按照lesson_id分区，create_time升序，取第一个score值
    ->     LAST_VALUE(score) OVER w AS last_score -- 按照lesson_id分区，create_time升序，取最后一个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()。

-- 使用 lead() 实现 
select 
    dt,
    count(1) as res_cnt
from (
    select
        user_id,
        date_format(log_time,"%Y%m%d") as dt,
        opr_id as curr_opr, -- 当前操作
        lead(opr_id,1) over(partition by user_id,date_format(log_time,"%Y%m%d") order by log_time) as next_opr -- 获取 下一个操作
    from tracking_log
) res 
where curr_opr = "A" and next_opr="B" 
group by dt

---------------------------------

-- 使用 lag() 实现 
select 
    dt,
    count(1) as res_cnt
from (
    select
        user_id,
        date_format(log_time,"%Y%m%d") as dt,
        opr_id as curr_opr, -- 当前操作
        lag(opr_id,1) over(partition by user_id,date_format(log_time,"%Y%m%d") order by log_time) as before_opr -- 获取 前一个操作
    from tracking_log
) res 
where before_opr = "A" and curr_opr="B" 
group by dt

2）统计用户行为序列为A-B-D的用户数,其中:A-B之间可以有任何其他浏览记录(如C,E等),B-D之间除了C记录可以有任何其他浏览记录(如A,E等)

select 
    count(*)
from(
    select 
        user_id,
        group_concat(opr_id) ubp -- 先按照用户分组，将组内的opr_id拼接起来
    from tracking_log
    group by user_id
) a
where ubp like '%A%B%D%' and ubp not like '%A%B%C%D%'

2.学生成绩分析

表：Enrollments (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
from (
	select
		student_id,
		course_id,
		grade,
		row_number() over(partition by student_id order by grade desc,course_id asc) as rank_num 
	from Enrollments 
) res 
where rank_num = 1 
order by student_id 

解法2：IN 解法

取成绩在最大成绩之中的学生的最小课程号的课程

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;

3.其他面试题

其他面试题，可以看我另外一篇文章，也是干货满满：Hive SQL 五大经典面试题