问题描述
有如下订单表,求每个用户的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)