SQL每日一题(20210818)取到每段连续日期的起始日期，终止日期，持续天数以及起始日期距上一期终止日期间隔的天数

SQL每日一题(20211126)

WITH cte as (SELECT *, last_value(rq) over (partition by (day(rq) - id)) nq FROM t0818),
     temp as (SELECT min(rq) rq, nq FROM cte GROUP BY nq)
SELECT rq, nq,lag (nq, 1, rq) over () a, datediff(nq, rq)+1 a, datediff(rq, lag (nq, 1, rq) over ()) b FROM temp

with a as(select min(rq) mi,max(rq) mx,count(1) c,date_format(rq,'%d')-id z from t0818 group by z)

select mi,mx,c,date_format(mi,'%d') b ,lag(date_format(mx,'%d'),1,1) over() a,
date_format(mi,'%d')-lag(date_format(mx,'%d'),1,1) over() s
from a