有意思的SQL(1)

一张表，年year、月month、日day，客诉类type，四个字段，需求：按照月份统计出每个月的客诉数，前提是：每个月的客诉数为当前月及其前两个月的总数，如果只按当前月的总数很好写select year,month,count(1) as kesunum
原问题链接在这里
https://www.modb.pro/issue/14881

with cte1 as 
(
	select 2022 as y,5 as m,23 as d from dual
	union all 
	select 2022 as y,5 as m,22 as d from dual
	union all 
	select 2022 as y,4 as m,22 as d from dual
	union all 
	select 2022 as y,4 as m,10 as d from dual
	union all 
	select 2022 as y,4 as m,9 as d from dual
	union all 
	select 2022 as y,3 as m,1 as d from dual
	union all 
	select 2022 as y,2 as m,1 as d from dual
	union all 
	select 2021 as y,12 as m,1 as d from dual
	union all 
	select 2021 as y,12 as m,2 as d from dual
	union all 
	select 2021 as y,8 as m,7 as d from dual
	union all 
	select 2021 as y,7 as m,7 as d from dual
),
cte2 as
(
	select count(*) as num,y,m,concat(y, right(concat('00',m),2)) as ym 
	from cte1
	group by y,m
	order by y desc,m desc 
),
cte3 as
(
	select *
	 ,last_value(ym) over(order by ym desc rows between CURRENT ROW and 2 following  ) as before2
	 ,last_value(ym) over(order by ym desc rows between CURRENT ROW and 1 following  ) as before1
	from cte2
),
cte4 as
(
	select *,
	CASE when period_diff(ym,before2) <=2 then before2
		 when period_diff(ym,before1) <=2 then before1
	else
		 ym 
	END  as ym2
	from cte3
 )
 select a.y,a.m,t.cnt from cte4 a
 left join LATERAL(select sum(b.num) as cnt from cte4 b  where b.ym>=a.ym2  and b.ym<=a.ym) as t on 1=1

— 2022.05.27 又去学习了一下窗口函数，发现这个有更简单的解法

with cte1 as 
(
	select 2022 as y,5 as m,23 as d from dual
	union all 
	select 2022 as y,5 as m,22 as d from dual
	union all 
	select 2022 as y,4 as m,22 as d from dual
	union all 
	select 2022 as y,4 as m,10 as d from dual
	union all 
	select 2022 as y,4 as m,9 as d from dual
	union all 
	select 2022 as y,3 as m,1 as d from dual
	union all 
	select 2022 as y,2 as m,1 as d from dual
	union all 
	select 2021 as y,12 as m,1 as d from dual
	union all 
	select 2021 as y,12 as m,2 as d from dual
	union all 
	select 2021 as y,8 as m,7 as d from dual
	union all 
	select 2021 as y,7 as m,7 as d from dual
),
cte2 as
(
	select count(*) as num,y,m,str_to_date(concat_ws('-',y, right(concat('00',m),2),'01'),'%Y-%m-%d') as ym 
	from cte1
	group by y,m
	order by y desc,m desc 
)
select *
,sum(num) over(order by ym    range   interval 2 month PRECEDING)
 from cte2
 order by ym desc