hive sql 实战案例-求每个用户按月累积交易金额

问题描述

有如下订单表，求每个用户的GMV和按月累积GMV，结果如右表所示。

分析问题

按用户和月份聚合求GMV，累积GMV使用sum() over()窗口函数即可。

准备数据

-- 删除表
drop table if exists orders;
-- 建表
CREATE TABLE orders (
  user_id bigint,
  pay_date string,
  pay_price bigint
);

-- 插入数据
INSERT overwrite table orders  
VALUES
  (101, '2023-01-01',100),
  (101, '2023-02-01',200),
  (101, '2023-02-28',300),
  (101, '2023-03-01',100),
  (102, '2023-01-01',100),
  (102, '2023-03-15',200);  
  
-- 查看数据
select * from orders

实现1：先求GMV，再求累积

-- 1、按照user_id和月份聚合，实现前三列
-- 2、使用sum窗口函数，按照user_id分组，按照月份排序，实现累积列
select user_id
,pay_month
,gmv
,sum(gmv) over(partition by user_id order by pay_month) as `累积`
from (
  -- 先求出各月
  select user_id
  ,substr(pay_date,1,7) as pay_month
  ,sum(pay_price) as gmv
  from orders
  group by user_id,substr(pay_date,1,7)
) t1
order by user_id,pay_month

实现2：窗口函数内聚合

select user_id
,substr(pay_date,1,7) as pay_month
,sum(pay_price) as gmv
,sum(sum(pay_price)) over(partition by user_id order by substr(pay_date,1,7)) as `累积`
from orders
group by user_id,substr(pay_date,1,7)