这篇文章主要介绍了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;

后记

可以使用子查询、可以使用窗口函数完成上面业务场景。

补充：PostgreSQL实现按秒按分按时按日按周按月按年统计数据

提取时间（年月日时分秒）：

import datetime
from dateutil.relativedelta import relativedelta
today = str(datetime.datetime.now())
print(today)
print(today[:4], today[:7], today[:10],today[:13])
 
print("************分隔符***************")
 
yesterday = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime("%Y-%m-%d %H:%M:%S")
yesterday2 = (datetime.datetime.now() + datetime.timedelta(days=-2)).strftime("%Y-%m-%d %H:%M:%S")
nextmonths = str(datetime.date.today() - relativedelta(months=-1))[:7]
lastmonths = str(datetime.date.today() - relativedelta(months=+1))[:7]
lastyears = str(datetime.date.today() - relativedelta(years=+1))[:4]
nextyears = str(datetime.date.today() - relativedelta(years=-1))[:4]