SQL窗⼝函数轻松解决连续N天购买⽤户问题
⼀、了解数据
订单表表名：ord
字段：name1：客户编号，orderdate：订购⽇期，amount：购买数量
1、出有连续三天购物的⽤户
2、计算每位客户相邻两次购物的时间差
1、 出有连续三天购物的⽤户
⽅法⼀：
连续三天购物，就是同⼀个⽤户相邻的订单⽇期都相差1天，⽐如A001⽤户2019/10/4、2019/10/5、2019/10/6就是连续三天购物。
可以通过⽇期将表⾃连接起来，如果以客户编号相同和⽇期隔⼀天为连接条件将表⾃连接，存在隔⼀天的订单才能连接，不存在即不能连接，就能到连续两天购物的⽤户；同理再⾃连接隔两天的，到连续三天购物的⽤户。
隔⼀天即是：同⼀个客户第⼆次购买时间与第⼀次购买时间的时间差为1天
求⽇期差，有两个函数；
datediff（）函数
timestampdiff（）函数
datediff（）函数：只可以求相差天数
timestampdiff（）函数：可以求相差秒数/分钟数/⼩时数/天数/⽉数/年数
因为题⽬只要求天数，所以⽤datediff（）函数就可以了。
如果要求除天数以外的时间就要⽤timestampdiff函数。
连续2天购物的客户编号
SELECT*FROM ord a JOIN ord b
on a.name1=b.name1 and derdate)=1；#连接条件
ord(a)和ord(b)通过编号相同且相邻⽇期差1天连接起来得到连续两天的购物⽤户。（看相同颜⾊的字段）连续3天购物的客户编号
SELECT*FROM ord a
diff函数
JOIN ord b on a.name1=b.name1 and derdate)=1
JOIN ord c on c.name1=b.name1 and derdate)=1;
题⽬要求的显⽰结果是⽤户，只显⽰⽤户id就可以了
SELECT  a.name1 FROM ord a
JOIN ord b on a.name1=b.name1 and derdate)=1
JOIN ord c on c.name1=b.name1 and derdate)=1;
⽅法⼆：窗⼝函数
题⽬要求：出连续三天购物的⽤户
思路：连续三天购物，那就是相邻的第3次购物的时间与第1次购物的时间差为2天。
⽤窗⼝函数 lead（），往后数两⾏，得到第3次购物的订购时间；
第3次订购时间 – 第1次订购时间=2，则为连续3天购物。
这样做的前提是：
1） 客户1天内购买多次都算1次；
2） 订购时间列的格式为“年/⽉/⽇”
我们的数据符合条件
窗⼝函数 lead（）讲解
取值原理：以A001号客户为例
取值原理：取不到的为空值。
最后计算出相差天数，相差天数为2就是我们要的客户，即A001号客户符合连续三天购买的条件。取出第3次订购时间：date3
SELECT*,LEAD(orderdate,2)over(PARTITION by name1 ORDER BY orderdate)date3 FROM ord;
计算相差天数 ：date3-orderdate
SELECT*,
DATEDIFF(LEAD(orderdate,2)over(PARTITION by name1 ORDER BY orderdate),orderdate)相差天数
FROM ord;
把相差天数等于2 的筛选出来
SELECT * FROM
(SELECT *,LEAD(orderdate,2)over(PARTITION by name1 ORDER BY orderdate)date3 FROM ord) a WHERE DATEDIFF(date1,orderdate)=2;
得到结果
窗⼝函数⽐⾃连接的优点是代码简单，
尤其是如果求连续10天购物的⽤户⽤⾃连接就得连接9次⽽且容易出错，
但⽤窗⼝函数代码量不会增加， 结果准确只需要把相差天数改成9就可以了。
通过上⾯的学习你们应该掌握了吧？试⼀试吧！
练⼀练
2、计算每位客户相邻两次购物的时间差
SELECT name1,orderdate, DATEDIFF(date1,orderdate)时间差
FROM(SELECT*,LEAD(orderdate,1)over(PARTITION by name1 ORDER BY orderdate)date1 FROM ord)a;
SELECT name1,orderdate,
DATEDIFF(LEAD(orderdate,1)over(PARTITION by name1 ORDER BY orderdate),orderdate)时间差
FROM ord;
喜欢记得点赞哦！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。