当前位置:网站首页>sql--7天内(含当天)购买次数超过3次(含),且近7天的购买金额超过1000的用户
sql--7天内(含当天)购买次数超过3次(含),且近7天的购买金额超过1000的用户
2022-08-11 05:36:00 【吃再多糖也不长胖】
背景
有一张用户购买记录表.现在我们需要找出所有的特殊用户.特殊用户的定义如下:
在当前购买时间的近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
边栏推荐
猜你喜欢
HCIP MPLS/BGP Comprehensive Experiment
SECURITY DAY04 (Prometheus server, Prometheus monitored terminal, Grafana, monitoring database)
Memory debugging tools Electric Fence
获取拼多多商品信息操作详情
HCIP WPN experiment
arcgis填坑_1
unable to extend table xxx by 1024 in tablespace xxxx
HCIP MGRE\OSPF综合实验
OA项目之待开会议&历史会议&所有会议
pytorch调整模型学习率
随机推荐
抖音API接口大全
淘宝商品详情API接口
【LeetCode】1036. 逃离大迷宫(思路+题解)压缩矩阵+BFS
MySQL之CRUD
Sturges规则
HCIP实验(pap、chap、HDLC、MGRE、RIP)
ETCD containerized to build a cluster
查看内核版本和发行版版本
LabelEncoder和LabelBinarizer的区别
导航定位中的坐标系
HCIA experiment
华为防火墙-6
WiFi Deauth 攻击演示分析 // mdk4 // aireplay-ng// Kali // wireshark //
HCIP BGP neighbor building, federation, and aggregation experiments
deepin v20.6+cuda+cudnn+anaconda(miniconda)
快速了解集成学习
Monte Carlo
八股文之redis
华为防火墙会话 session table
矩阵分析——矩阵分解