为动态生成的PostgreSQL透视表添加行总计

问题场景 #

已创建cross_table表，结构及数据如下：

CREATE TABLE cross_table (brand varchar(10), gender varchar(10), sales int);

INSERT INTO cross_table (brand, gender, sales) VALUES ('Nike', 'Male', 10);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Nike', 'Male', 20);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Adidas', 'Woman', 20);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Nike', 'Male', 10);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Adidas', 'Woman', 30);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Puma', 'Woman', 40);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Puma', 'Male', 10);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Nike', 'Male', 20);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Puma', 'Woman', 10);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Adidas', 'Woman', 20);

使用以下查询生成以brand为行、gender为列、sales为值的透视表：

with main_query as (    
    SELECT  brand,
            GROUPING(brand) AS "brand_grouping",
            gender,
            GROUPING(gender) AS "gender_grouping",
            sum(sales) AS "sales"
    FROM    cross_table
    GROUP BY ROLLUP (brand, gender)
),  
second_query AS (
    SELECT  brand,
            brand_grouping,
            cast(
                json_object_agg(
                  gender,
                  sales
                  ORDER BY gender DESC
                ) FILTER (WHERE gender_grouping = 0) AS jsonb) "gender",
            SUM(sales)  AS "sales"
    FROM main_query
    GROUP BY (brand, brand_grouping)
)

SELECT  brand,
        gender,
        sales
FROM    second_query
ORDER BY brand_grouping, brand

查询结果中，总计行的gender字段为NULL。需要在未知gender具体取值的情况下，动态为总计行生成包含所有gender键的JSON对象，每个键对应其全局总计值，而非硬编码指定Male/Woman。

解决方案 #

可以通过预先获取所有唯一的gender值及其全局总计，再在处理总计行时将这些值填充为JSON对象。修改后的查询如下：

WITH gender_totals AS (
    -- 预统计每个gender的全局总销量
    SELECT gender, sum(sales) AS total_sales
    FROM cross_table
    GROUP BY gender
),
main_query AS (    
    SELECT  brand,
            GROUPING(brand) AS "brand_grouping",
            gender,
            GROUPING(gender) AS "gender_grouping",
            sum(sales) AS "sales"
    FROM    cross_table
    GROUP BY ROLLUP (brand, gender)
),  
second_query AS (
    SELECT  
        brand,
        brand_grouping,
        -- 分支处理：总计行用预统计的gender全局值生成JSON
        CASE 
            WHEN brand_grouping = 1 THEN (SELECT jsonb_object_agg(gender, total_sales) FROM gender_totals)
            ELSE cast(
                json_object_agg(
                  gender,
                  sales
                  ORDER BY gender DESC
                ) FILTER (WHERE gender_grouping = 0) AS jsonb
            )
        END AS "gender",
        SUM(sales)  AS "sales"
    FROM main_query
    GROUP BY (brand, brand_grouping)
)

SELECT  brand,
        gender,
        sales
FROM    second_query
ORDER BY brand_grouping, brand

原理说明 #

• gender_totals CTE：提前计算每个gender的全局总销量，无论后续新增多少gender取值，都能动态获取对应的总计数据。
• CASE语句分支：
  • 当brand_grouping=1时（即总计行），直接调用gender_totals的统计结果生成包含所有gender键的JSON对象。
  • 非总计行保持原有逻辑，生成对应品牌各gender的销量JSON。

这样就能在不硬编码gender取值的情况下，让总计行的gender字段动态包含所有存在的性别及其全局总销量。

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