当前位置:网站首页>sql--Users who have purchased more than 3 times (inclusive) within 7 days (including the current day), and the purchase amount in the past 7 days exceeds 1,000
sql--Users who have purchased more than 3 times (inclusive) within 7 days (including the current day), and the purchase amount in the past 7 days exceeds 1,000
2022-08-11 07:13:00 【Eating too much sugar will not gain weight】
背景
有一张用户购买记录表.现在我们需要找出所有的特殊用户.特殊用户的定义如下:
在当前购买时间的近7天内(含当天)购买次数超过3次(含),且近7The purchase amount of the day exceeds1000的用户即为特殊用户.
数据
--创建测试表
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
边栏推荐
猜你喜欢
随机推荐
HCIP MPLS/BGP综合实验
Open Set Domain Adaptation 开集领域适应
HCIP MPLS/BGP Comprehensive Experiment
华为防火墙-4-安全策略
会议OA项目之我的会议
How Xshell connects to a virtual machine
iptables nat
【LeetCode】1036. 逃离大迷宫(思路+题解)压缩矩阵+BFS
防火墙-0-管理地址
Map Reduce
导航定位中的坐标系
OA项目之我的审批(查询&会议签字)
Top20括号匹配
unable to extend table xxx by 1024 in tablespace xxxx
My meeting of the OA project (meeting seating & review)
淘宝API接口参考
《Generative Adversarial Networks》
一个小时快速熟悉MySQL基本用法
uboot sets the default bootdelay
SATA、SAS、SSD三种硬盘存储性能数据