语法介绍
窗口函数语法:
over (partition by
order by
rows/range子句 )
可以放以下两种函数:
1) 专用窗口函数,包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
2) 聚合函数,如sum. avg, count, max, min等
1)专用窗口函数
rank, dense_rank, row_number这三个函数的区别在这篇文章里有详细介绍,简略说就是:
- Rank:有相同名次,名次按实际个数走,会跳数字。
- Dense_rank: 有相同名次,名次不跳数
- Row_number:相同分数按行数排序
分数 | Rank | Dense_Rank | Row_number |
---|---|---|---|
100 | 1 | 1 | 1 |
100 | 1 | 1 | 2 |
90 | 3 | 2 | 3 |
2)聚合函数
这里以sum()
为例子,使用常用的部门员工数据集,介绍聚合函数的不同组合用法
例1: 求各个部门的薪酬总数:
3) 滑动窗口:rows&range用法
[<ROWS or RANGE clause> BETWEEN <Start expr> AND <End expr>]
- ROWS: 表示按照行的范围进行定义框架,根据order by子句排序后,取的前N行及后N行的数据计算(与当前行的值无关,只与排序后的行号相关)。常用:
rows n perceding
表示从当前行到前n行(一共n+1行) - RANGE:表示按照值的范围进行定义框架,根据order by子句排序后,指定当前行对应值的范围取值,行数不固定,只要行值在范围内,对应行都包含在内。适用于对日期、时间、数值排序分组
边界可取值(Start expr & End expr) | 说明 |
---|---|
Current Row | 当前行 |
N preceding | 前 n 行,n 为数字, 比如 2 Preceding 表示前2行 |
unbounded preceding | 开头 |
N following | 后N行,n 为数字, 比如 2 following 表示后2行 |
unbounded following | 结尾 |
range取特定日期区间 | 说明 |
---|---|
range interval 7-1 day preceding | 最近7天的值 |
range between interval 1 day preceding and interval 1 day following | 前后一天和当天的值 |
列2:求按id号累计员工的薪资(rows 用法)
注:如果将这里的rows 换成range 结果是一样的,因为这里使用id号排序,id和行号一致。
例3:求每个员工的薪资情况以及对应±1万元及±5千元薪资范围内的人数 (Range用法)
Range 是根据值来组合排序的,结果中的第一行 Same的工资是60000, 而薪资范围内在50000-70000的人一共有4个,薪资范围内在55000-65000的人只有一个。
参考问题连接
累计问题: 牛客网 SQL159 每个创作者每月的涨粉率及截止当前的总粉丝量
限制条件下的累计问题: 牛客网SQL160 国庆期间每类视频点赞量和转发量
The RANGE Clause in SQL Window Functions: 5 Practical Examples