PostgreSQL完成按月累加_pgsql的一段时间的累加值-CSDN博客

本文链接：https://blog.csdn.net/qq_31156277/article/details/103873829

本文介绍在PostgreSQL中实现月度累计统计的方法，包括使用自关联子查询和窗口函数两种方式，适用于按省份和年份分组的场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

PostgreSQL完成按月累加

背景

统计某个指标，指标按照月进行累加，注意需要按省份和年份进行分组。
在这里插入图片描述

方法一、使用自关联

-- with 按月统计得到中间结果
WITH yms AS (SELECT regionid,SUM(getnum) AS getnum,SUM(dealnum) AS dealnum,to_char(qndate,'yyyy-MM') AS yearmonth
FROM t_queuenumber
GROUP BY regionid,to_char(qndate,'yyyy-MM')
ORDER BY regionid,yearmonth)

-- 查用子查询解决。
SELECT s1.regionid,s1.yearmonth, getnum,dealnum,
(SELECT SUM(getnum) FROM yms  s2 WHERE s2.regionid = s1.regionid AND s2.yearmonth <= s1.yearmonth  AND SUBSTRING(s1.yearmonth,0,5) = SUBSTRING(s2.yearmonth,0,5) ) AS getaccumulatednum,
(SELECT SUM(dealnum) FROM yms  s2 WHERE s2.regionid = s1.regionid AND s2.yearmonth <= s1.yearmonth  AND  SUBSTRING(s1.yearmonth,0,5) = SUBSTRING(s2.yearmonth,0,5) ) AS accumulatednum
FROM yms s1;

查询的结果如下：
在这里插入图片描述

方法二、使用窗口函数

更多关于窗口函数的用法，可以参考以前的文章。窗口函数十分适合这样的场景：

	WITH yms AS (SELECT regionid,SUM(getnum) AS getnum,SUM(dealnum) AS dealnum,to_char(qndate,'yyyy-MM') AS yearmonth
	FROM t_queuenumber
	GROUP BY regionid,to_char(qndate,'yyyy-MM')
	ORDER BY regionid,yearmonth)
	-- 窗口函数的使用
	SELECT regionid,yearmonth,
	SUM(getnum) OVER(PARTITION BY regionid,SUBSTRING(yearmonth,0,5) ORDER BY yearmonth) AS getaccumulatednum,
	SUM(dealnum) OVER(PARTITION BY regionid ,SUBSTRING(yearmonth,0,5) ORDER BY yearmonth)  AS dealaccumulatednum
	FROM yms;