当前位置:网站首页>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
边栏推荐
- ansible batch install zabbix-agent
- arcgis填坑_4
- 损失函数——负对数似然
- numpy和tensor增加或删除一个维度
- 从mask-rcnn到shp
- WiFi Deauth 攻击演示分析 // mdk4 // aireplay-ng// Kali // wireshark //
- 华为防火墙会话 session table
- Raspberry Pi set static IP address
- 淘宝sku API 接口(PHP示例)
- CLUSTER DAY04 (Block Storage Use Cases, Distributed File Systems, Object Storage)
猜你喜欢
随机推荐
TOP2两数相加
FusionCompute8.0.0实验(0)CNA及VRM安装(2280v2)
HCIA实验
一个小时快速熟悉MySQL基本用法
亚马逊API接口大全
拼多多API接口(附上我的可用API)
命令输出给变量
arcgis填坑_4
京东商品详情API调用实例讲解
radix-4 FFT 原理和C语言代码实现
局域网文件传输
iptables nat
The ramdisk practice 1: the root file system integrated into the kernel
淘宝sku API 接口(PHP示例)
HCIP 重发布/路由策略实验
华为防火墙-4-安全策略
抖音API接口
iptables的状态
HCIP OSPF/MGRE Comprehensive Experiment
空间点模式方法_一阶效应和二阶效应