红袖APP付费记录中间表u_wsd.t_od_qidian_

datediff(end_date,start_date)>= 3 

datediff(end_date, start_date)

with....as....用法:

在本例子中相当于建了个名字为base,aa,res_pre的临时表，放在with ... as的括号()里面的sql语句执行后的结果就是临时表的数据，with...as()中as前的那一个就是别名，

with...as()，...as(),...as()多个临时表用逗号连接，后面的查询就可以用它，这样对于大批量的sql语句起到一个优化的作用，而且清楚明了。详细解释和用法可以自行去搜索了解一下

row_number() over(partition by qq_no order by ds) as row_num:

按照qq_no分组，按照日期ds组内分别升序排序得到row_num

date_sub:函数从日期减去指定的时间间隔,例如

date_sub(ds,row_num) as date_rank 

date_add(date_rank,1) as start_date 

datediff() 函数:返回两个日期之间的时间，例如：

datediff(end_date, start_date)

datediff(end_date, start_date)

step01

with base as (
select qq_no, ds,row_number() over(partition by qq_no order by ds) as row_num 
from u_wsd.t_od_qidian_pay_hx 
where ds>='2020-02-01' and ds<'2020-03-01' 
),

aa as (
select qq_no,ds,row_num,date_sub(ds, row_num) as date_rank from base
),

aa表

同一个QQ号下date_rank相同才表示连续，QQ号02的第一个date_rank跟其余三个不一样，所以只有三个连续

res_pre as (
select qq_no,ds,date_add(date_rank,1) as start_date,row_number() over(partition by date_rank order by row_num desc) as rn from aa
) 

res_pre表

step04

select qq_no as qq_no,start_date as start_date,ds as end_date
from res_pre
where rn=1 and datediff(end_date, start_date)>= 3 --连续4天及以上