背景
有一张用户购买记录表.现在我们需要找出所有的特殊用户.特殊用户的定义如下:
在当前购买时间的近7天内(含当天)购买次数超过3次(含),且近7天的购买金额超过1000的用户即为特殊用户.
数据
--创建测试表
create table aaa001
(
user_id int,
buy_date varchar(20),
amount int
)
;
--插入测试数据(102和104为特殊用户)
insert into aaa001 (user_id,buy_date,amount) values (101,'2021-01-01',1000);
insert into aaa001 (user_id,buy_date,amount) values(101,'2021-01-02',2000);
insert into aaa001 (user_id,buy_date,amount) values(102,'2021-10-01',10);
insert into aaa001 (user_id,buy_date,amount) values(102,'2021-10-02',700);
insert into aaa001 (user_id,buy_date,amount) values(102,'2021-10-07',200);
insert into aaa001 (user_id,buy_date,amount) values(103,'2021-11-07',500);
insert into aaa001 (user_id,buy_date,amount) values(103,'2021-11-08',500);
insert into aaa001 (user_id,buy_date,amount) values(103,'2021-11-20',500);
insert into aaa001 (user_id,buy_date,amount) values(104,'2021-03-01',10);
insert into aaa001 (user_id,buy_date,amount) values(104,'2021-03-05',200);
insert into aaa001 (user_id,buy_date,amount) values(104,'2021-03-09',800);
insert into aaa001 (user_id,buy_date,amount) values(104,'2021-03-09',800);
insert into aaa001 (user_id,buy_date,amount) values(105,'2021-05-01',1);
insert into aaa001 (user_id,buy_date,amount) values(105,'2021-05-10',2);
解决方案
自查询 -
---查询sql
select
*
from aaa001
order by user_id ,buy_date
;
select user_id, sum(cnt) as cnt,sum(money) as money
from
(select t1.user_id,t1.buy_date,t1.money,sum(t2.cnt) as cnt
from
(select user_id,buy_date,count(*) as cnt,sum(amount)as money from aaa001 group by user_id,buy_date)t1
left join
(select user_id,buy_date,count(*) as cnt,sum(amount)as money from aaa001 group by user_id,buy_date)t2
on t1.user_id = t2.user_id
and str_to_date(t2.buy_date, '%Y-%m-%d %H') >str_to_date(t1.buy_date, '%Y-%m-%d %H')
and str_to_date(t2.buy_date, '%Y-%m-%d %H') <= date_add(t1.buy_date,INTERVAL 7 DAY)
group by t1.user_id,t1.buy_date,t1.money)a
group by user_id
having sum(cnt ) >=3 and sum(money) >=1000;
开窗
---sql逻辑
select
distinct user_id
from
(
select
user_id
,buy_date
,count(1) over(PARTITION by user_id order by datediff(buy_date,'2021-01-01') RANGE between 6 PRECEDING and CURRENT row) as cnt
,sum(amount) over(PARTITION by user_id order by datediff(buy_date,'2021-01-01') RANGE between 6 PRECEDING and CURRENT row) as amount
from test.aaa001
)t1
where cnt>=3 and amount>1000