PostgreSQL如何计算月度滚动审批率？附数据表示例

现有如下数据表：

CREATE TABLE data 
(
    Event_Date date,
    approved int, 
    total int
)

INSERT INTO data (Event_date, approved, total)
VALUES
    ('20190901', 5, 20),
    ('20190902', 6, 30),
    ('20190903', 4, 50),
    ('20190904', 7, 40),
    ('20190905', 8, 50),
    ('20190906', 10, 70),
    ('20190907', 4, 25)

需要计算从所选月份起始日到每日的月度滚动率（公式为累计approved除以累计total），并得到包含累计表达式格式的结果：

无需使用循环，SQL的窗口函数是实现这类累计计算的最优方案——它基于集合操作，性能远高于行处理的循环，代码也更简洁易维护。

通用实现（兼容MySQL 8.0+、SQL Server、PostgreSQL等） #

以下语句会同时生成要求的累计表达式格式和实际比率数值：

SELECT
    Event_Date,
    approved,
    total,
    -- 生成显示用的累计表达式
    CONCAT(
        '(', GROUP_CONCAT(approved ORDER BY Event_Date SEPARATOR '+'), ')/(',
        GROUP_CONCAT(total ORDER BY Event_Date SEPARATOR '+'), ')'
    ) AS `Rolling monthly rate`,
    -- 计算实际滚动率数值（保留4位小数）
    ROUND(
        SUM(approved) OVER (PARTITION BY DATE_FORMAT(Event_Date, '%Y%m') ORDER BY Event_Date) / 
        SUM(total) OVER (PARTITION BY DATE_FORMAT(Event_Date, '%Y%m') ORDER BY Event_Date),
        4
    ) AS `Rolling rate value`
FROM data
ORDER BY Event_Date;

数据库适配说明 #

不同数据库的日期分组函数略有差异，替换DATE_FORMAT(Event_Date, '%Y%m')即可：

• SQL Server：用FORMAT(Event_Date, 'yyyyMM')或DATEPART(YEAR, Event_Date)*100 + DATEPART(MONTH, Event_Date)
• PostgreSQL：用TO_CHAR(Event_Date, 'YYYYMM')

仅需数值比率的简化写法 #

如果不需要显示(5+6)/(20+30)这类表达式，只需要实际比率数值，可使用更简洁的语句：

SELECT
    Event_Date,
    approved,
    total,
    ROUND(
        SUM(approved) OVER (PARTITION BY DATE_FORMAT(Event_Date, '%Y%m') ORDER BY Event_Date) / 
        SUM(total) OVER (PARTITION BY DATE_FORMAT(Event_Date, '%Y%m') ORDER BY Event_Date),
        4
    ) AS `Rolling monthly rate`
FROM data
ORDER BY Event_Date;

内容的提问来源于stack exchange，提问作者Nickname_used