SELECT x.stat_dt,x.stat_year,x.stat_month,x.ct,
COALESCE(ROUND((ct-hb)/hb::numeric*100,2),0) hb_dt,
COALESCE(ROUND((ct-tb)/tb::numeric*100,2),0) tb_dt,
(CASE WHEN ROUND((ct-hb)/hb::numeric*100,2) is not null THEN
CONCAT(ROUND((ct-hb)/hb::numeric*100,2),'%')
ELSE
'0'
END) hb_info,
(CASE WHEN ROUND((ct-tb)/tb::numeric*100,2) is not null THEN
CONCAT(ROUND((ct-tb)/tb::numeric*100,2),'%')
ELSE
'0'
END) tb_info
from (
with stat_info as (select to_char(stat_date,'yyyy-mm') stat_dt,to_char(stat_date,'yyyy') stat_year,
to_char(stat_date,'mm') stat_month,count(1) ct
from fdc_tmc_yjzx_contract
where community_id = 381022174
group by stat_dt,stat_year,stat_month
order by stat_dt desc)
SELECT t.*,lead(t.ct,1) over(ORDER BY t.stat_dt desc) hb,lead(t.ct,12) over(ORDER BY t.stat_dt desc) tb from stat_info t
) x
LIMIT 12
lag(value any [, offset integer [, default any ]])
返回偏移值,offset integer是偏移值,正数时前值,负数时后值,没有取到值时用default代替,默认偏移量为0, 默认值为null
lead(value any [, offset integer [, default any ]])
返回偏移值,offset integer是偏移值,正数时取后值,负数时取前值,没有取到值时用default代替
注:使用窗口函数lead或lag,进行同环比计算时,需要注意使用的条件必须是连续的。如果有更好的方式,可以在评论区留言,谢谢!