如何使用PostgreSQL命令实现分组求和后的列值比率计算

当然可以只用PostgreSQL命令实现这个需求！我们可以通过**CTE（公共表表达式）**先整理出每个number和type的统计值，再通过条件聚合将同个number的不同type值合并到一行，最后生成需要的比率行并处理各种边界情况（NULL、除零）。

完整查询语句 #

WITH type_totals AS (
    -- 第一步：按projectname、number、type分组统计count的总和
    SELECT
        number,
        type,
        SUM(count) AS count
    FROM myTable
    GROUP BY projectname, number, type
),
number_totals AS (
    -- 第二步：将每个number对应的t1、t2、t3统计值转为单列，方便后续计算
    SELECT
        number,
        MAX(CASE WHEN type = 't1' THEN count END) AS t1_count,
        MAX(CASE WHEN type = 't2' THEN count END) AS t2_count,
        MAX(CASE WHEN type = 't3' THEN count END) AS t3_count
    FROM type_totals
    GROUP BY number
)
-- 第三步：生成t2t1和t3t2的比率行，处理所有边界情况
SELECT
    number,
    't2t1' AS type,
    CASE
        WHEN t2_count IS NULL THEN 'NULL by ' || COALESCE(t1_count::TEXT, 'NULL') || ' is NULL'
        WHEN t1_count = 0 THEN t2_count::TEXT || ' by 0 is INF'
        WHEN t1_count IS NULL THEN t2_count::TEXT || ' by NULL is NULL'
        ELSE t2_count::TEXT || ' by ' || t1_count::TEXT
    END AS ratio
FROM number_totals
UNION ALL
SELECT
    number,
    't3t2' AS type,
    CASE
        WHEN t3_count IS NULL THEN 'NULL by ' || COALESCE(t2_count::TEXT, 'NULL') || ' is NULL'
        WHEN t2_count = 0 THEN t3_count::TEXT || ' by 0 is INF'
        WHEN t2_count IS NULL THEN t3_count::TEXT || ' by NULL is NULL'
        ELSE t3_count::TEXT || ' by ' || t2_count::TEXT
    END AS ratio
FROM number_totals
ORDER BY number, type;

关键部分解释 #

1. type_totals CTE：
   这一步和你原来的查询逻辑一致，按projectname、number、type分组求和，得到每个组合的count总和。这里去掉了DISTINCT ON，因为GROUP BY已经保证每组唯一，不需要额外去重。

2. number_totals CTE：
   使用条件聚合（CASE WHEN + MAX），把同一个number下的t1、t2、t3统计值分别放到t1_count、t2_count、t3_count列中，这样每个number只占一行，方便后续计算比率。

3. 生成比率行：

   • 用UNION ALL合并两个查询结果，分别生成t2t1和t3t2类型的行。
   • 通过CASE WHEN处理所有边界场景：
     • 分子为NULL时，输出NULL by X is NULL格式；
     • 分母为0时，输出X by 0 is INF；
     • 分母为NULL时，输出X by NULL is NULL；
     • 正常情况输出分子 by 分母。

执行结果 #

运行上述查询后，会得到你期望的格式：

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