语法介绍

窗口函数语法:

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:相同分数按行数排序
分数RankDense_RankRow_number
100111
100112
90323

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