软件下载吧文章资讯

分类分类

PostgreSQL完成按月累加的操作

2024-02-09 10:40作者:下载吧

背景

统计某个指标,指标按照月进行累加,注意需要按省份和年份进行分组。

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;

展开全部

相关文章

说两句网友评论
    我要跟贴
    取消