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");

問題01,找出2017年,連續(xù)7天及以上無訂單的用戶ID,對應(yīng)起止時間,持續(xù)天數(shù)

#問題01, 找出2017年,連續(xù)7天及以上無訂單的用戶ID,對應(yīng)起止時間,持續(xù)天數(shù)

## 1，構(gòu)造一個左右相鄰訂單表，左側(cè)為同一客戶上一個訂單號，右側(cè)為同一客戶下一個訂單號，

## 當(dāng)同一客戶，左右訂單日期不在連續(xù)日期(同一日或者前后日）時，說明連續(xù)日期中斷了，相差7天，就是中斷7天，

## 就是連續(xù)7天無訂單所以需要給同一用戶的訂單排序號

## 2，如何構(gòu)造左右相鄰訂單表，借助ROWBUNBE對各個用戶的訂單排序，然后排序結(jié)果自連接，限定條件：左右客戶id相等，右側(cè)序號比左側(cè)大1。

## 3, 在2的基礎(chǔ)就可以找出索要所需要的記錄,左右日期差值大于2就是出現(xiàn)了中斷

SELECT Ac_id as 客戶ID, Aorder_date+1 起始日期 ,Border_date -1 結(jié)束日期, timestampdiff(DAY, Aorder_date ,Border_date) -1 中斷持續(xù)天數(shù)

from

(

select a.c_id Ac_id, a.order_id Aorder_id ,a.order_date Aorder_date , b.c_id Bc_id, b.order_id Border_id, b.order_date Border_date

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') 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') 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)>=7;

輸出結(jié)果截圖: