如何在单个PostgreSQL查询中展示多时间段的发行比率？

可以通过条件聚合结合CTE（公共表达式）来实现，一次性计算出多个时间段的比率，无需使用LEFT JOIN，同时提升查询效率：

完整查询语句 #

WITH period_totals AS (
    -- 预先计算各时间段的全网络总发行数
    SELECT
        COUNT(*) FILTER (WHERE issued_at >= date_trunc('year', current_date)) AS total_ytd,
        COUNT(*) FILTER (WHERE issued_at >= current_date - INTERVAL '1 month') AS total_1mo,
        COUNT(*) FILTER (WHERE issued_at >= current_date - INTERVAL '2 months') AS total_2mo
    FROM issuances_extended
    WHERE status = 'completed'
)
SELECT
    IR.network,
    -- 计算年初至今比率，NULLIF避免除以0报错
    COUNT(*) FILTER (WHERE issued_at >= date_trunc('year', current_date)) / (NULLIF(pt.total_ytd, 0) * 1.0) AS issuance_ratio_ytd,
    -- 计算过去1个月比率
    COUNT(*) FILTER (WHERE issued_at >= current_date - INTERVAL '1 month') / (NULLIF(pt.total_1mo, 0) * 1.0) AS issuance_ratio_1mo,
    -- 计算过去2个月比率
    COUNT(*) FILTER (WHERE issued_at >= current_date - INTERVAL '2 months') / (NULLIF(pt.total_2mo, 0) * 1.0) AS issuance_ratio_2mo
FROM issuances_extended IR
-- 关联预计算的总发行数（CTE仅一行，CROSS JOIN无性能问题）
CROSS JOIN period_totals pt
WHERE IR.status = 'completed'
GROUP BY IR.network, pt.total_ytd, pt.total_1mo, pt.total_2mo
ORDER BY IR.network;

方案说明 #

1. CTE预计算总发行数：通过period_totals一次性算出所有时间段的全网络总完成发行数，避免原查询中每个分组重复执行子查询的低效问题。
2. 条件聚合统计单网络数量：使用COUNT(*) FILTER (WHERE ...)语法，在同一个GROUP BY中统计单个网络在不同时间段的发行数量。
3. 避免除以0错误：用NULLIF(total, 0)处理某时间段无发行的情况，此时比率会返回NULL而非报错。
4. 无需LEFT JOIN：通过CROSS JOIN关联仅一行的CTE，逻辑简洁且性能稳定。

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