mysql 按月累计_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() + dateti