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;