扫码加入数据分析学习群

create database ccc;

use ccc;

create table order_info(

c_id varchar(10),

c_name varchar(10),

city_id varchar(10),

city_name varchar(10),

order_id varchar(10),

order_date date

);

insert into order_info values

('111','王**','32','厦门市','12233',"2017-07-01"),

('111','王**','32','厦门市','12234',"2017-07-02"),

('111','王**','32','厦门市','12235',"2017-07-03"),

('111','王**','32','厦门市','12236',"2017-07-03"),

('111','王**','32','厦门市','12237',"2017-7-3"),

('111','王**','32','厦门市','12238',"2017-7-4"),

('111','王**','32','厦门市','12239',"2017-7-5"),

('111','王**','32','厦门市','12240',"2017-7-6"),

('111','王**','32','厦门市','12241',"2017-7-7"),

('111','王**','32','厦门市','12242',"2017-7-8"),

('111','王**','32','厦门市','12243',"2017-7-9"),

('111','王**','32','厦门市','12244',"2017-7-10"),

('111','王**','32','厦门市','12245',"2017-7-11"),

('111','王**','32','厦门市','12246',"2017-7-12"),

('202','林**','32','厦门市','32247',"2017-7-1"),

('202','林**','32','厦门市','32248',"2017-7-2"),

('202','林**','32','厦门市','32249',"2017-7-3"),

('202','林**','32','厦门市','32250',"2017-7-4"),

('202','林**','32','厦门市','32251',"2017-7-5"),

('202','林**','32','厦门市','32252',"2017-7-6"),

('202','林**','32','厦门市','32253',"2017-7-20"),

('203','刘**','32','厦门市','32254',"2017-7-10"),

('203','刘**','32','厦门市','32255',"2017-7-11");

#问题02, 找出2017年,连续7天及以上有订单的用户ID,对应起止时间,持续天数

#问题02, 找出2017年,连续7天及以上有订单的用户ID,对应起止时间,持续天数 
## 1，构造一个左右相邻订单表，左侧为同一客户上一个订单号，右侧为同一客户下一个订单号，
##   当同一客户，左右订单日期在连续日期(同一日或者前后日）时，是符合要求的,左右订单日期不在连续日期(同一日或者前后日）时,就是中断了
##  剔除掉中断的记录后,便可以计算出我们要的结果
## 2, 如何构造左右相邻订单表，借助ROWBUNBE对各个用户的订单排序，然后排序结果自连接，限定条件：左右客户id相等，右侧序号比左侧大1。
## 3, 在2的基础上剔除掉中断的记录后,where timestampdiff(DAY, Aorder_date ,Border_date)<=1
## 4,
SELECT *
from
(
 select a.c_id  Ac_id, a.order_id Aorder_id ,a.order_date Aorder_date ,a.xuhao Axuaho,
        b.c_id  Bc_id, b.order_id Border_id, b.order_date Border_date ,b.xuhao Bxuaho
 from 
 (select c_id,order_id,order_date ,ROW_NUMBER() OVER(PARTITION BY c_id order by order_date ) as xuhao
 from order_info WHERE    order_date BETWEEN '20170101' AND '20171231' GROUP BY c_id, order_date ) as a,
 (select c_id,order_id,order_date ,ROW_NUMBER() OVER(PARTITION BY c_id order by order_date ) as xuhao
 from order_info WHERE    order_date BETWEEN '20170101' AND '20171231'  GROUP BY c_id, order_date ) as b
 where a.c_id=b.c_id 
 and a.xuhao+1=b.xuhao
 and a.order_date < b.order_date
)t
where timestampdiff(DAY, Aorder_date ,Border_date)<=1;

## 4, 在同一客户同一天的订单只保留一个的情况下(加上 GROUP BY c_id, order_date),然后可通过日期序号差来找出同一客户的不同连续日期段
SELECT    Ac_id,  Aorder_id , Aorder_date , Axuaho, Aorder_date- Axuaho 日期序号差,
          Bc_id,  Border_id,  Border_date , Bxuaho
from
(
 select a.c_id  Ac_id, a.order_id Aorder_id ,a.order_date Aorder_date ,a.xuhao Axuaho,
        b.c_id  Bc_id, b.order_id Border_id, b.order_date Border_date ,b.xuhao Bxuaho
 from 
 (select c_id,order_id,order_date ,ROW_NUMBER() OVER(PARTITION BY c_id order by order_date ) as xuhao
 from order_info WHERE    order_date BETWEEN '20170101' AND '20171231' GROUP BY c_id, order_date ) as a,
 (select c_id,order_id,order_date ,ROW_NUMBER() OVER(PARTITION BY c_id order by order_date ) as xuhao
 from order_info WHERE    order_date BETWEEN '20170101' AND '20171231'  GROUP BY c_id, order_date ) as b
 where a.c_id=b.c_id 
 and a.xuhao+1=b.xuhao
 and a.order_date < b.order_date
)t
where timestampdiff(DAY, Aorder_date ,Border_date)<=1;

## 在4的基础上取出不同的c_id的订单持续起止日期和持续天数
SELECT Ac_id c_id, min(Aorder_date) 开始日期,max(Border_date) 结束日期 ,count(日期序号差)+1 持续天数
FROM 
(
   Select    Ac_id,  Aorder_id , Aorder_date , Axuaho, Aorder_date- Axuaho 日期序号差,
          Bc_id,  Border_id,  Border_date , Bxuaho
   from
   (
   select a.c_id  Ac_id, a.order_id Aorder_id ,a.order_date Aorder_date ,a.xuhao Axuaho,
          b.c_id  Bc_id, b.order_id Border_id, b.order_date Border_date ,b.xuhao Bxuaho
   from 
   (select c_id,order_id,order_date ,ROW_NUMBER() OVER(PARTITION BY c_id order by order_date ) as xuhao
   from order_info WHERE    order_date BETWEEN '20170101' AND '20171231' GROUP BY c_id, order_date ) as a,
   (select c_id,order_id,order_date ,ROW_NUMBER() OVER(PARTITION BY c_id order by order_date ) as xuhao
   from order_info WHERE    order_date BETWEEN '20170101' AND '20171231'  GROUP BY c_id, order_date ) as b
   where a.c_id=b.c_id 
   and a.xuhao+1=b.xuhao
   and a.order_date < b.order_date
  )t
  where timestampdiff(DAY, Aorder_date ,Border_date)<=1
)t1
GROUP BY Ac_id,日期序号差;

####************************************************************************************
#通过日期序号差来找出同一客户的不同连续日期段
#先使用group by 按日期分组,每个日期只显示一次,一行一个日期,然后row_numer排序号
Select c_id ,row_number() over(partition by c_id order by order_date) 行序号, order_date,
	   order_date - ( row_number() over(partition by c_id order by order_date) ) 日期序号差
From    order_info
WHERE      order_date BETWEEN "20170101" AND "20171231"
GROUP BY   c_id, order_date; 
#GROUP BY  c_id, order_date 分组去重显示

#对比，没有 GROUP BY t.c_id,t.order_date:
Select c_id ,row_number() over(partition by c_id order by order_date) 行序号, order_date,
	   order_date - ( row_number() over(partition by c_id order by order_date) ) 日期序号差
From    order_info
WHERE      order_date BETWEEN "20170101" AND "20171231";
#没有 GROUP BY t.c_id,t.order_date; #则全部显示出来,无法通过日期序号差来找出同一客户的不同连续日期段

输出结果截图: