需求
- 给定用户 ID，统计用户第一次消费后30天内的总购买数量；
创建表
-
创建日历表 user_sales
create table user_sales( user_id integer not null primary key, sell_day date not null unique, amount integer not null );
写入测试数据
- 写入数据；
-- 生成测试数据 insert into user_sales values(1, '2021-01-01', 100); insert into user_sales values(2, '2021-01-15', 100); insert into user_sales values(2, '2021-01-10', 200); insert into user_sales values(1, '2021-01-01', 300); insert into user_sales values(1, '2021-01-20', 100); insert into user_sales values(1, '2021-02-10', 200); insert into user_sales values(2, '2021-02-01', 100); insert into user_sales values(1, '2021-01-05', 100); insert into user_sales values(3, '2021-01-20', 300); insert into user_sales values(1, '2021-02-01', 300); insert into user_sales values(3, '2021-02-21', 100);
SQL
SELECT
s.user_id,s.first_day AS 第一次消费时间,sum(user_sales.amount) AS 30天消费
FROM
(
SELECT
user_id,min(sell_day) AS first_day
FROM
user_sales
GROUP BY
user_id
) AS s
JOIN
user_sales
ON
(
s.user_id=user_sales.user_id
AND
user_sales.sell_day BETWEEN s.first_day AND s.first_day + INTERVAL 30 DAY
)
GROUP BY
s.user_id,s.first_day;
-
得到结果
