SQL 窗口函数

目录

• SQL窗口函数
  • 基本语法
  • 窗口子句
  • rank()/dense_rank()/row_number() 排名
    • 分组TOPN
  • PERCENT_RANK()/CUME_DIST() 分布函数
  • LEAD(field, num, defaultvalue)/LAG(field, num, defaultvalue) 前后函数
  • FIRST_VALUE(expr)/ LAST_VALUE(expr)/NTH_VALUE(expr,n) 首尾函数
  • NTILE(n) 分区内再分组
  • 聚合函数作为窗口函数

SQL窗口函数

在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：

• 排名问题：每个部门按业绩来排名
• topN问题：找出每个部门排名前N的员工进行奖励
  面对这类需求，就需要使用sql的高级功能窗口函数了。

窗口函数的特点

• 同时具有分组和排序的功能
• 不减少原表的行数

分组排序但并不聚合

窗口函数会把表进行分组但不会合并，只是相同组的会按照排序挨着。
窗口函数是因为partition by分组后的结果称为“窗口”，“范围”的意思。

基本语法

# 写法1
<窗口函数> over (partition by <要分列的组> order by <要排序的列> rows between <数据范围>)
# 写法2
<窗口函数>
over  w
window w as ([partition-by-clause] [order-by-clause] [windowing-clause])

over是关键字，用来指定函数执行的窗口范围，包含三个分析子句：分组(partition by)子句，排序(order by)子句，窗口(rows)子句

窗口子句

窗口(rows)：就是进行函数分析时要处理的数据范围，属于当前分区的一个子集，通常用来作为滑动窗口使用。

当order by后面缺少窗口从句条件， 窗口规范默认是rows between unbounded preceding and current row.
当order by和窗口从句都缺失, 窗口规范默认是 rows between unbounded preceding and unbounded following

[rows | range] between <start expr> and [end expr]

<start expr> is [unbounded preceding | current row | n preceding | n following]
<end expr> is [unbounded following  | current row | n preceding | n following]

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    # 本行、前面三行和下面一行， 总共五行

窗口函数	是否支持windowing-clause
row_number/rank/dense_rank	×
NTH_VALUE	√

窗口函数

• 专用窗口函数，rank, dense_rank, row_number等专用窗口函数。
• 聚合函数，如sum. avg, count, max, min等

rank()/dense_rank()/row_number() 排名

1.没有分区，那么整个表表示一个区。
2.
row_number() 不考虑并列名次的情况，按顺序依次下去1,2,3
rank() 有并列名次的行，会占用下一名次的位置1,1,3
dense_rank() 有并列名次的行，不占用下一名次的位置1,1,2

分组TOPN

问题模板

select *
from (
   select *,
          row_number() over (partition by 要分组的列名
                       order by 要排序的列名 desc,如果相同则比较的列名 desc) as ranking
   from 表名) as a
where ranking <= N

PERCENT_RANK()/CUME_DIST() 分布函数

PERCENT_RANK() 函数是百分比函数，结果=(rank-1)/(rows-1)。rank:使用rank函数产生的序号，rows当前窗口的总记录数

CUME_DIST():查询小于或等于当前记录某列(排序的列)的比例

# 计算 goods 数据表中'女装'类别下商品的PERCENT_RANK()值

SELECT 
RANK() OVER (PARTITION BY category_id ORDER BY price) AS r,
PERCENT_RANK() OVER (PARTITION BY category_id ORDER BY price) AS pr
FROM goods
WHERE category_id = 1;

类似进度条？

LEAD(field, num, defaultvalue)/LAG(field, num, defaultvalue) 前后函数

lead(field, num, defaultvalue):取出当前行后num行的field数据
lag(field, num, defaultvalue):取出当前行前num行的field数据

虽然可以不用排序，但是往往只有在排序的场景下取前面或者后面N行数据才有意义。
这种操作可以代替表的自联接，并且LEAD有更高的效率。

FIRST_VALUE(expr)/ LAST_VALUE(expr)/NTH_VALUE(expr,n) 首尾函数

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

NTH_VALUE(expr,n) [FROM first/last] [RESPECT/IGNORE NULLS] over (...)

说明
1.返回每个分区的第n个expr值,没有则返回null
2.first从头开始数窗口的第n个(默认)，last从后往前数窗口的第n个
3.IGNORE NULLS 忽略空值，RESPECT NULLS 不忽略空值(默认)

# 查找薪水第二高的员工
SELECT
    employee_name,
    salary,
    NTH_VALUE(employee_name, 2) OVER  (
        ORDER BY salary DESC
    ) second_highest_salary
FROM
    basic_pays;

# 注意这里最高的是NULL，从头到第一行(当前行)找不到排名第二的(第二行)
+-------------------+--------+-----------------------+
| employee_name     | salary | second_highest_salary |
+-------------------+--------+-----------------------+
| Larry Bott        |  11798 | NULL                  |
| Gerard Bondur     |  11472 | Gerard Bondur         |
| Pamela Castillo   |  11303 | Gerard Bondur         |
| Barry Jones       |  10586 | Gerard Bondur         |
| George Vanauf     |  10563 | Gerard Bondur         |
| Loui Bondur       |  10449 | Gerard Bondur         |
| Mary Patterson    |   9998 | Gerard Bondur         |
| Steve Patterson   |   9441 | Gerard Bondur         |
| Julie Firrelli    |   9181 | Gerard Bondur         |
| Jeff Firrelli     |   8992 | Gerard Bondur         |
| William Patterson |   8870 | Gerard Bondur         |
| Diane Murphy      |   8435 | Gerard Bondur         |
| Leslie Jennings   |   8113 | Gerard Bondur         |
| Gerard Hernandez  |   6949 | Gerard Bondur         |
| Foon Yue Tseng    |   6660 | Gerard Bondur         |
| Anthony Bow       |   6627 | Gerard Bondur         |
| Leslie Thompson   |   5186 | Gerard Bondur         |
+-------------------+--------+-----------------------+
17 rows in set (0.01 sec)

NTILE(n) 分区内再分组

NTILE(n)函数将分区中的有序数据分为n个桶，记录桶编号

分区内再分组

聚合函数作为窗口函数

函数需要指定聚合的列名

select *,
   sum(成绩) over (order by 学号) as current_sum,
   avg(成绩) over (order by 学号) as current_avg,
   count(成绩) over (order by 学号) as current_count,
   max(成绩) over (order by 学号) as current_max,
   min(成绩) over (order by 学号) as current_min
from 班级表

聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和，若是0005号，则结果是0001号~0005号成绩的求和，以此类推。
比如0005号后面的聚合窗口函数结果是：学号0001~0005五人成绩的总和、平均、计数及最大最小值。
这样使用窗口函数有什么用呢？
聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。