postgre 通过窗口函数lead或lag计算同环比

最新推荐文章于 2024-07-15 06:52:45 发布

hucbmz

最新推荐文章于 2024-07-15 06:52:45 发布

阅读量1.9k

点赞数 5

文章标签： postgresql

本文链接：https://blog.youkuaiyun.com/hucbmz/article/details/119875101

版权

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