方案二实现如下:
-- 级联累加求和场景
/*
login.txt
A,2021-01,5
A,2021-01,15
B,2021-01,5
A,2021-01,8
B,2021-01,25
A,2021-01,5
A,2021-02,4
A,2021-02,6
B,2021-02,10
B,2021-02,5
A,2021-03,7
B,2021-03,9
A,2021-03,11
B,2021-03,6
*/
create table money
(
user_id string,
month string,
money int
) row format delimited fields terminated by ',';
load data local inpath '/root/hivedata/money.txt' overwrite into table money;
select *
from money;
-- 先查询每个用户每个月的消费总金额
select user_id,
month,
sum(money) month_money
from money
group by user_id, month;
-- 再累计每个用户到当前月的消费总金额
with tmp_money as (
select user_id,
month,
sum(money) month_money
from money
group by user_id, month
)
select user_id,
month,
month_money,
sum(month_money) over (partition by user_id order by month
-- rows between unbounded preceding and current row
) as sum_month_money
from tmp_money;