HiveQL之Hive开窗函数

题目：有销售表T。样例数据如下，请用sql查出每个员工的年累计销售额，查询结果如表L。

样例表T：

结果表K：

1.准备数据sale.txt

emi,201801,10000
emi,201802,11000
emi,201803,12000
tommy,201801,20000
tommy,201802,21000
tommy,201803,22000

2.建表

use tmp;
create table if not exists tmp.sale
(
name    string  comment '名字',
month   bigint  comment '月份',
sale    int     comment '销售额'
)
comment '销售表'
row format delimited fields terminated by ',' stored as textfile;

3.加载数据

load data local inpath '/home/odsrpdev/dapeng/test/sale.txt' into table tmp.sale;

4.查询语句和结果

select
name,
month,
sale,
sum(sale) over (partition by name order by month) as total
from sale;

emi     201801  10000   10000
emi     201802  11000   21000
emi     201803  12000   33000
tommy   201801  20000   20000
tommy   201802  21000   41000
tommy   201803  22000   63000

select
name,
month,
sale,
lag(sale,1,0) over (partition by name order by month) as total
from sale;

emi     201801  10000   0
emi     201802  11000   10000
emi     201803  12000   11000
tommy   201801  20000   0
tommy   201802  21000   20000
tommy   201803  22000   21000