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年,連續(xù)7天及以上有訂單的用戶ID,對(duì)應(yīng)起止時(shí)間,持續(xù)天數(shù)

#問題02, 找出2017年,連續(xù)7天及以上有訂單的用戶ID,對(duì)應(yīng)起止時(shí)間,持續(xù)天數(shù) 
## 1，構(gòu)造一個(gè)左右相鄰訂單表，左側(cè)為同一客戶上一個(gè)訂單號(hào)，右側(cè)為同一客戶下一個(gè)訂單號(hào)，
##   當(dāng)同一客戶，左右訂單日期在連續(xù)日期(同一日或者前后日）時(shí)，是符合要求的,左右訂單日期不在連續(xù)日期(同一日或者前后日）時(shí),就是中斷了
##  剔除掉中斷的記錄后,便可以計(jì)算出我們要的結(jié)果
## 2, 如何構(gòu)造左右相鄰訂單表，借助ROWBUNBE對(duì)各個(gè)用戶的訂單排序，然后排序結(jié)果自連接，限定條件：左右客戶id相等，右側(cè)序號(hào)比左側(cè)大1。
## 3, 在2的基礎(chǔ)上剔除掉中斷的記錄后,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, 在同一客戶同一天的訂單只保留一個(gè)的情況下(加上 GROUP BY c_id, order_date),然后可通過日期序號(hào)差來找出同一客戶的不同連續(xù)日期段
SELECT    Ac_id,  Aorder_id , Aorder_date , Axuaho, Aorder_date- Axuaho 日期序號(hào)差,
          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的基礎(chǔ)上取出不同的c_id的訂單持續(xù)起止日期和持續(xù)天數(shù)
SELECT Ac_id c_id, min(Aorder_date) 開始日期,max(Border_date) 結(jié)束日期 ,count(日期序號(hào)差)+1 持續(xù)天數(shù)
FROM 
(
   Select    Ac_id,  Aorder_id , Aorder_date , Axuaho, Aorder_date- Axuaho 日期序號(hào)差,
          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,日期序號(hào)差;

####************************************************************************************
#通過日期序號(hào)差來找出同一客戶的不同連續(xù)日期段
#先使用group by 按日期分組,每個(gè)日期只顯示一次,一行一個(gè)日期,然后row_numer排序號(hào)
Select c_id ,row_number() over(partition by c_id order by order_date) 行序號(hào), order_date,
	   order_date - ( row_number() over(partition by c_id order by order_date) ) 日期序號(hào)差
From    order_info
WHERE      order_date BETWEEN "20170101" AND "20171231"
GROUP BY   c_id, order_date; 
#GROUP BY  c_id, order_date 分組去重顯示

#對(duì)比，沒有 GROUP BY t.c_id,t.order_date:
Select c_id ,row_number() over(partition by c_id order by order_date) 行序號(hào), order_date,
	   order_date - ( row_number() over(partition by c_id order by order_date) ) 日期序號(hào)差
From    order_info
WHERE      order_date BETWEEN "20170101" AND "20171231";
#沒有 GROUP BY t.c_id,t.order_date; #則全部顯示出來,無法通過日期序號(hào)差來找出同一客戶的不同連續(xù)日期段

輸出結(jié)果截圖: