(2022.04.11 Mon)

窗口函数

窗口函数是作用于数据关系中的一系列记录的SQL函数，这些记录称为窗口(window/window frame)。窗口是被查询的行(row)，比如当前行之前的所有行，当前行到后面的三行，之类。

窗口函数功能上类似于聚合函数(aggregate function)，都是用于计算一组记录的统计结果。不同于聚合函数，窗口函数不会聚合行数据(collapse row)，而保留每行数据的完整信息。

窗口函数原则上只能使用在SELECT字句中。

基本的调用格式

<window_function> OVER 
([PARTITION BY <group_column>] 
 ORDER BY <order_column> 
 [ROWS BETWEEN <m> PRECEDING AND <n> FOLLOWING])

• window_function：指定的窗口函数
• OVER：用于定义/指定窗口内的数据记录/行，如果不指定后面跟着的字句，如PARTITION/ORDER/ROWS，即OVER()，则窗口函数作用于整个窗口
• PARTITION BY部分：可选，定义窗口内的组(partition)，根据指定的字段<group_column>对数据进行分组，不指定PARTITION就不分组
• group_column：用于分组的列
• order_column：用于排序的列
• ROWS/RANGE BETWEEN部分：用于指定窗口范围，可选
• m：用户指定的当前行前面的行数，可写为UNBOUNDED
• n：用户指定的当前行后面的行数，也可写为UNBOUNDED。

典型问题

窗口函数经常用于解决的问题包括“既分组又排序”，top N问题(找出一个组织内部的top n员工)，最值问题等。

分类

窗口函数分为四类

• 聚合函数aggregate function
• 排序函数ranking function
• 分析函数analytic function
• 分布函数distribution function

聚合函数

包括AVG, COUNT, MAX, MIN和SUM。需要传递变量名作为参数。

计算某个公司的若干员工的总工资，关系表示为salary(eid, ename, department, gender, salary)。

select *,
sum(salary) over(order by salary) ss
from test_data.salary;

返回结果为

SUM配order by用于计算累加和。

计算各部门的最高工资，注意这里就不需要加order by部分。一旦加入则各自计算每个人的而非每个部门。

select *,
max(salary) over(partition by department) ss
from test_data.salary;

计算平均工资，按性别分类。注意这里将使用order by指令，会返回前n个员工的平均工资，而非某个性别全部员工的平均工资。

select *,
avg(salary) over(partition by gender order by salary) ss
from test_data.salary;

(2022.04.12 Tues)
计算所有学生的各科滑动平均成绩，也就是先做科目排名，再计算前后各1个人累计三个人的平均成绩，保留两位小数。该方法也可用于计算公司员工业绩排名和平均业绩。关系score_board(stu_id, stu_name, subjects, stu_score)。

SELECT *,
ROUND(AVG(score) OVER(PARTITION BY subjects 
ORDER BY score DESC ROWS 
BETWEEN 1 PRECEDING AND 1 FOLLOWING), 2) AS avg_score
FROM score_board;

如果不需要分partition，则删掉PARTITION BY语句。如果只需要向前或向后计算滑动平均，则删掉BETWEEN和AND部分。如果计算某一个科目所有人的平均成绩，可将PRECEDING和FOLLOWING前面的数字参数改成UNBOUNDED，或者直接删掉ROWS开始的部分，得到的就是科目的平均成绩。

SELECT *,
ROUND(AVG(score) OVER(PARTITION BY subjects 
ORDER BY score DESC ROWS 
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 2) AS avg_score
FROM score_board;

SELECT *,
ROUND(AVG(score) OVER(PARTITION BY subjects 
ORDER BY score DESC), 2) AS avg_score
FROM score_board;

有关系score_board(stu_id, stu_name, subjects, stu_score)，找出成绩低于该科目平均成绩的同学信息，之后开除。
找出：

select distinct(stu_id)
from 
(select *,
avg(score) OVER(Partition by subjects) as avg_score
from score_board) a
where score < a.avg_score;

开除：

DELETE FROM score_board where stu_id IN
(
select distinct(stu_id)
from 
(select *,
avg(score) OVER(Partition by subjects) as avg_score
from score_board) a
where score < a.avg_score;
);

也可通过子查询方式找出符合条件的结果

select *
from score_board a
where score < (select avg(score) from score_board b 
where a.subjects = b.subjects);

排序函数

包括rank, row_number, dense_rank。这三个函数都用于排序，但在出现并列的情况下，排序结果不同。排序函数在使用时不需要传递参数，即rank()/row_number()/dense_rank()。

• rank: 如果排序中有并列情况，如三个并列第一，则下一位将是第四，即1,1,1,4
• row_number: 即便出现并列的情况，返回的仍然是所在行的数字，按自然数顺序排列，1,2,3,4之类
• dense_rank: 出现并列的情况，如三个并列第一，则下一位是第二，即1,1,1,2。

有关系score_board(stu_id, stu_name, subjects, stu_score)，找出每科成绩最高的信息。

SELECT * FROM
(SELECT *,
RANK() OVER(PARTITION BY subjects ORDER BY stu_score DESC) AS ranking
FROM score_board) a
WHERE a.ranking = 1;

分析函数

包括lead, lag, first_value，用于获取当前窗口数据中的其他行。lead用于返回当前行后面的值，lag返回当前行前面的值。调用方式是lead/lag(col, n, default_value)，其中的n是指定的行数，比如n=1，则返回当前行前面/后面1行的值，default_value指如果返回是Null则赋予default_value。first_value仅需要提供字段名，即first_value(col)。返回最大/最小/最低/最高的值问题都可以考虑用FIRST_VALUE来解决，注意配合ORDER BY的DESC/ASC。
下面是lead的调用方式和结果。

SELECT *, 
LEAD(score,1) OVER(PARTITION BY stu_name ORDER BY score desc) AS sr
FROM test_data.stu_score;

返回结果如下

lag的调用结果，返回的是当前subject的score值的前一个值，如果前一个值为Null(即当前值即第一个值)，则返回default_value，本例中为-1

SELECT *,
lag(score, 1, -1) over(partition by subjects order by score desc) as lag_score
from score_board;

分布函数

包括PERCENT_RANK和CUME_DIST，分别用于获得分位点排序(percentile ranking)和累计分布(cumulative distribution)。

窗口函数在SQL执行序列中的位置

1. FROM / JOINS
2. WHERE
3. GROUP BY
4. Aggregate Functions
5. HAVING
6. Window Functions
7. SELECT
8. DISTINCT
9. UNION / INTERSECT / EXCEPT
10. ORDER BY
11. OFFSET
12. LIMIT / FETCH / TOP
    一般来说窗口函数不会出现在WHERE语句中。

Reference

1 https冒号//learnsql点com/blog/sql-window-functions-interview-questions/
2 https冒号//www点educba点com/sql-window-functions/
3 https冒号//zhuanlan点zhihu点com/p/390381181
4 https冒号//zhuanlan点zhihu点com/p/92654574