扫码加入数据分析学习群

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年,连续7天及以上无订单的用户ID,对应起止时间,持续天数

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

## 1，构造一个左右相邻订单表，左侧为同一客户上一个订单号，右侧为同一客户下一个订单号，

## 当同一客户，左右订单日期不在连续日期(同一日或者前后日）时，说明连续日期中断了，相差7天，就是中断7天，

## 就是连续7天无订单所以需要给同一用户的订单排序号

## 2，如何构造左右相邻订单表，借助ROWBUNBE对各个用户的订单排序，然后排序结果自连接，限定条件：左右客户id相等，右侧序号比左侧大1。

## 3, 在2的基础就可以找出索要所需要的记录,左右日期差值大于2就是出现了中断

SELECT Ac_id as 客户ID, Aorder_date+1 起始日期 ,Border_date -1 结束日期, timestampdiff(DAY, Aorder_date ,Border_date) -1 中断持续天数

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;

输出结果截图: