PostgreSQL多表连接下近12个月统计优化：新增Current Month列的实现方案咨询

问题描述 #

我的多表连接查询可以正常运行，但输出结果不符合预期。我希望新增一列名为"Current Month"的字段，需求逻辑是：若查看5月的报表，则统计区间为去年5月至今年4月（共12个月）并将对应数据分配到各月份列，同时把今年5月的数据单独统计到"Current Month"列，最终输出总计13列统计数据。我觉得窗口查询可能能解决，但不确定具体实现方式。

当前查询代码 #

select c.name, 
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'January' THEN 1 END) as "January",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'February' THEN 1 END) as "February",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'March' THEN 1 END) as "March",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'April' THEN 1 END) as "April",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'May' THEN 1 END) as "May",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'June' THEN 1 END) as "June",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'July' THEN 1 END) as "July",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'August' THEN 1 END) as "August",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'September' THEN 1 END) as "September",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'October' THEN 1 END) as "October",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'November' THEN 1 END) as "November",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'December' THEN 1 END) as "December"
from analytics_outbox mo 
inner join analytics_outbox_recipient mor on mor.analytics_outbox_id = mo.id 
inner join customer c on c.id = mo.customer_id 
group by c.name

当前输出结果 #

name |january|february|march |april |may |june|july|august|september|october|november|december|
----------------------------------+-------+--------+------+-------+-------+----+----+------+---------+-------+--------+--------+
ABC |       |        | 1     | 2     |     |     |     |      |         |       |        |        |
DEF | 11    | 24     | 34    | 32    | 19  |     |     |      |         |       |        |        |
GEH | 9     | 3      | 7     | 18    | 22  |     |     |      |         |       |        |        |
IJK |       |        |       | 1     |     |     |     |      |         |       |        |        |

你的需求不需要用到窗口查询，核心是明确统计区间的边界，并区分"过去12个月"和"当前报表月"的数据。下面提供两种实现方式，你可以根据自己的习惯选择：

方式一：直接修改原查询（直观易理解） #

通过定义报表月份参数，给原有的月份统计条件加上时间范围限制，同时新增Current Month列：

-- 定义报表目标月份，可手动替换为指定月份，比如TO_DATE('2024-05-01', 'YYYY-MM-DD')
WITH report_params AS (
    SELECT TRUNC(SYSDATE, 'MONTH') AS report_month FROM dual
)
SELECT 
    c.name, 
    -- 统计"去年报表月至今年报表月前一个月"的各月份数据
    SUM(case WHEN mor.sent_at BETWEEN ADD_MONTHS(rp.report_month, -12) AND ADD_MONTHS(rp.report_month, -1)
             AND RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'January' THEN 1 END) as "January",
    SUM(case WHEN mor.sent_at BETWEEN ADD_MONTHS(rp.report_month, -12) AND ADD_MONTHS(rp.report_month, -1)
             AND RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'February' THEN 1 END) as "February",
    SUM(case WHEN mor.sent_at BETWEEN ADD_MONTHS(rp.report_month, -12) AND ADD_MONTHS(rp.report_month, -1)
             AND RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'March' THEN 1 END) as "March",
    SUM(case WHEN mor.sent_at BETWEEN ADD_MONTHS(rp.report_month, -12) AND ADD_MONTHS(rp.report_month, -1)
             AND RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'April' THEN 1 END) as "April",
    SUM(case WHEN mor.sent_at BETWEEN ADD_MONTHS(rp.report_month, -12) AND ADD_MONTHS(rp.report_month, -1)
             AND RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'May' THEN 1 END) as "May",
    SUM(case WHEN mor.sent_at BETWEEN ADD_MONTHS(rp.report_month, -12) AND ADD_MONTHS(rp.report_month, -1)
             AND RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'June' THEN 1 END) as "June",
    SUM(case WHEN mor.sent_at BETWEEN ADD_MONTHS(rp.report_month, -12) AND ADD_MONTHS(rp.report_month, -1)
             AND RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'July' THEN 1 END) as "July",
    SUM(case WHEN mor.sent_at BETWEEN ADD_MONTHS(rp.report_month, -12) AND ADD_MONTHS(rp.report_month, -1)
             AND RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'August' THEN 1 END) as "August",
    SUM(case WHEN mor.sent_at BETWEEN ADD_MONTHS(rp.report_month, -12) AND ADD_MONTHS(rp.report_month, -1)
             AND RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'September' THEN 1 END) as "September",
    SUM(case WHEN mor.sent_at BETWEEN ADD_MONTHS(rp.report_month, -12) AND ADD_MONTHS(rp.report_month, -1)
             AND RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'October' THEN 1 END) as "October",
    SUM(case WHEN mor.sent_at BETWEEN ADD_MONTHS(rp.report_month, -12) AND ADD_MONTHS(rp.report_month, -1)
             AND RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'November' THEN 1 END) as "November",
    SUM(case WHEN mor.sent_at BETWEEN ADD_MONTHS(rp.report_month, -12) AND ADD_MONTHS(rp.report_month, -1)
             AND RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'December' THEN 1 END) as "December",
    -- 新增Current Month列，统计报表当月的数据
    SUM(case WHEN TRUNC(mor.sent_at, 'MONTH') = rp.report_month THEN 1 END) as "Current Month"
from analytics_outbox mo 
inner join analytics_outbox_recipient mor on mor.analytics_outbox_id = mo.id 
inner join customer c on c.id = mo.customer_id
cross join report_params rp
group by c.name

逻辑说明： #

1. report_params CTE用来定义报表的目标月份：TRUNC(SYSDATE, 'MONTH')会自动获取当前系统月份，也可以手动指定（比如TO_DATE('2024-05-01', 'YYYY-MM-DD')生成5月报表）。
2. 原有月份的统计条件新增了时间范围：BETWEEN ADD_MONTHS(rp.report_month, -12) AND ADD_MONTHS(rp.report_month, -1)，也就是报表月份往前推12个月到前一个月（比如5月报表就统计去年5月到今年4月）。
3. Current Month列直接匹配数据的月份等于报表月份，单独统计该月的数量。

方式二：先过滤再统计（更简洁易维护） #

先通过CTE过滤出需要的时间段数据，再进行统计，避免重复写时间范围：

WITH report_params AS (
    SELECT 
        TRUNC(SYSDATE, 'MONTH') AS report_month,
        ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), -12) AS period_start, -- 去年报表月
        ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), -1) AS period_end -- 今年报表月前一个月
    FROM dual
),
filtered_data AS (
    SELECT 
        c.name,
        RTRIM(TO_CHAR(mor.sent_at , 'Month')) AS month_name,
        -- 标记是否为当前报表月的数据
        CASE WHEN TRUNC(mor.sent_at, 'MONTH') = rp.report_month THEN 1 ELSE 0 END AS is_current_month
    FROM analytics_outbox mo 
    INNER JOIN analytics_outbox_recipient mor ON mor.analytics_outbox_id = mo.id 
    INNER JOIN customer c ON c.id = mo.customer_id
    CROSS JOIN report_params rp
    -- 过滤出过去12个月 + 当前报表月的数据
    WHERE mor.sent_at BETWEEN rp.period_start AND rp.report_month
)
SELECT 
    name,
    SUM(case WHEN month_name = 'January' THEN 1 END) as "January",
    SUM(case WHEN month_name = 'February' THEN 1 END) as "February",
    SUM(case WHEN month_name = 'March' THEN 1 END) as "March",
    SUM(case WHEN month_name = 'April' THEN 1 END) as "April",
    SUM(case WHEN month_name = 'May' THEN 1 END) as "May",
    SUM(case WHEN month_name = 'June' THEN 1 END) as "June",
    SUM(case WHEN month_name = 'July' THEN 1 END) as "July",
    SUM(case WHEN month_name = 'August' THEN 1 END) as "August",
    SUM(case WHEN month_name = 'September' THEN 1 END) as "September",
    SUM(case WHEN month_name = 'October' THEN 1 END) as "October",
    SUM(case WHEN month_name = 'November' THEN 1 END) as "November",
    SUM(case WHEN month_name = 'December' THEN 1 END) as "December",
    SUM(is_current_month) as "Current Month"
FROM filtered_data
GROUP BY name

逻辑说明： #

1. report_params定义了统计的关键时间节点，filtered_data先把需要的所有数据筛选出来，同时标记哪些是当前报表月的数据。
2. 最后一步统计时，直接用标记字段求和得到Current Month的数据，其他月份统计也不用重复写时间范围，代码更简洁，后续修改也更方便。

内容的提问来源于stack exchange，提问作者A l w a y s S u n n y