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