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)twhere 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 , Bxuahofrom( 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)twhere 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)t1GROUP 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_infoWHERE      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_infoWHERE      order_date BETWEEN "20170101" AND "20171231";#没有 GROUP BY t.c_id,t.order_date; #则全部显示出来,无法通过日期序号差来找出同一客户的不同连续日期段

输出结果截图: