Window 函数

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;