ROWS窗口函数中的行选择器 rows between [n|unbounded preceding]|[n|unbounded following]|[current row] and [n|unbounded preceding]|[n|unbounded following]|[current row]
参数解释：
n行数
unbounded 不限行数
preceding 在前N行
following 在后N行
current row 当前行正在执行查询的那一行
应用场景：
1）查询当月销售额和近三个月的销售额
2）查询当月销售额和今年年初到当月的销售额
示例： 年份 月份 销售额 \t分割
2018 01 1000 2018 02 1000 2018 03 3000 2018 04 3000 2018 05 5000 2018 06 5000 2018 07 1000 2018 08 1000 2018 09 3000 2018 10 3000 2018 11 5000 2018 12 5000 2019 01 1000 2019 02 2000 2019 03 3000 2019 04 4000 2019 05 5000 2019 06 6000
建表导入数据
CREATE TABLE sale_table( y string, m string, sales int ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t‘;
load data local inpath ‘/home/suniu/d1/file7‘ overwrite into table sale_table;
-- 查询当月销售额和近三个月的销售额 SELECT y, m, sales, sum(sales) OVER(order by y,m ROWS BETWEEN 2 preceding AND current row) AS s1 FROM sale_table;
---------------------------------------------------------------------------------------------------------
-- 查询当月销售额和今年年初到当月的销售额 SELECT y, m, sales, sum(sales) OVER(PARTITION BY y order by m asc ROWS between unbounded preceding and current row) AS s2 FROM sale_table;