Hive窗口函数应用：级联累加求和场景

-- 级联累加求和场景
/*
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;