sql窗口函数中的取值函数

窗口函数 over(parttion by 字段1 order by 字段2)

序号函数

函数名	函数功能
row_number()	连续取值，且值不重复
rank()	如果有相同的值，它们将获得相同的排名，下一个值的排名将跳过之前的排名。（会出现缺失的排名）
dense_rank()	如果有相同的值，它们将获得相同的排名，下一个值的排名不跳过之前的排名。（不会出现缺失的排名）

总结：
如果有两行的值是相同的，那么

• ROW_NUMBER()会给它们分配不同的行号，
• RANK()会给它们分配相同的排名并跳过下一个排名，
• DENSE_RANK()会给它们分配相同的排名并不跳过下一个排名

取值

常见取值函数有：

函数名	函数功能
lag(expr, n)	返回当前行的往前第n行的值
lead(expr, n)	返回当前行的往后第n行的值

对于滑动窗口的范围指定，有两种方式，基于行和基于范围，具体区别如下：

• ROWS: 表示按照行的范围进行定义框架，根据order by子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）。常用：rows n perceding表示从当前行到前n行（一共n+1行）

使用rows BETWEEN frame_start AND frame_end语法来表示行范围，frame_start和frame_end可以支持如下关键字，来确定不同的动态行记录：

1. CURRENT ROW 边界是当前行，一般和其他范围关键字一起使用

2. UNBOUNDED PRECEDING 边界是分区中的第一行

3. UNBOUNDED FOLLOWING 边界是分区中的最后一行

4. expr PRECEDING  边界是当前行减去expr的值

5. expr FOLLOWING  边界是当前行加上expr的值

rows BETWEEN 1 PRECEDING AND 1 FOLLOWING -- 窗口范围是当前行、前一行、后一行一共三行记录。

rows  UNBOUNDED FOLLOWING -- 窗口范围是当前行到分区中的最后一行。

rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ## 窗口范围是当前分区中所有行，等同于不写。

• RANGE：表示按照值的范围进行定义框架，根据order by子句排序后，指定当前行对应值的范围取值，行数不固定，只要行值在范围内，对应行都包含在内。适用于对日期、时间、数值排序分组

和基于行类似，但有些范围不是直接可以用行数来表示的，比如希望窗口范围是一周前的订单开始，截止到当前行，则无法使用rows来直接表示，此时就可以使用范围来表示窗口：

-- 取特定日期区间	说明
range interval 7-1 day preceding	-- 最近7天的值
range between interval 1 day preceding and interval 1 day following	-- 前后一天和当天的值

函数名	函数功能
range()	基于当前行的值的。例如，RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW定义的窗口包含从分区的开始到当前行（包括当前行）的所有行。
rows()	基于当前行的位置的。例如，ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING定义的窗口包含前一行、当前行和后一行。