PostgreSQL多维度数据笛卡尔积分组聚合及列转行需求

方案一：使用MAX(CASE)静态生成变量列 #

适合变量列表固定的场景，通过CASE语句将每个变量转为单独列，同时预生成全量维度笛卡尔积保证所有组合被覆盖。

步骤说明 #

1. 生成全量维度组合：通过CROSS JOIN获取所有年份与空间维度的笛卡尔积，确保无数据的组合也能出现在结果中。
2. 聚合数据：关联所有表，按年份、空间名称、变量分组计算求和，用COALESCE将空值转为0。
3. 转置列：使用MAX(CASE)将每个变量的聚合值转为单独列，再次用COALESCE确保无数据时显示0。

示例SQL #

WITH all_dimensions AS (
    -- 生成所有年份和空间名称的全量组合
    SELECT td.year, sd.name AS spatial_name
    FROM time_dimension td
    CROSS JOIN spatial_dimension sd
),
aggregated_data AS (
    -- 按维度+变量聚合求和，空值转0
    SELECT
        td.year,
        sd.name AS spatial_name,
        v.var_name,
        COALESCE(SUM(dp.value), 0) AS sum_value
    FROM datapoints dp
    JOIN time_dimension td ON dp.td_id = td.td_id
    JOIN spatial_dimension sd ON dp.sd_id = sd.sd_id
    JOIN datapoint_variablevalue dv ON dp.dp_id = dv.dp_id
    JOIN variablevalue vv ON dv.vv_id = vv.vv_id
    JOIN variable v ON vv.var_id = v.var_id
    GROUP BY td.year, sd.name, v.var_name
)
SELECT
    ad.year,
    ad.spatial_name,
    -- 为每个变量单独生成一列，无数据则返回0
    COALESCE(MAX(CASE WHEN var_name = '能耗类型' THEN sum_value END), 0) AS "能耗类型",
    COALESCE(MAX(CASE WHEN var_name = '排放等级' THEN sum_value END), 0) AS "排放等级",
    COALESCE(MAX(CASE WHEN var_name = '设备类型' THEN sum_value END), 0) AS "设备类型"
FROM all_dimensions ad
LEFT JOIN aggregated_data ag 
    ON ad.year = ag.year 
    AND ad.spatial_name = ag.spatial_name
GROUP BY ad.year, ad.spatial_name
ORDER BY ad.year, ad.spatial_name;

方案二：使用crosstab实现动态列转置 #

如果变量列表不固定，可使用PostgreSQL的crosstab函数（依赖tablefunc扩展）实现动态转置，同时保证全量维度覆盖。

前置准备 #

先安装tablefunc扩展：

CREATE EXTENSION IF NOT EXISTS tablefunc;

示例SQL #

WITH all_dimensions AS (
    -- 生成所有年份和空间名称的全量组合
    SELECT td.year, sd.name AS spatial_name
    FROM time_dimension td
    CROSS JOIN spatial_dimension sd
),
aggregated_data AS (
    -- 关联全量维度与数据，聚合求和，空值转0
    SELECT
        ad.year,
        ad.spatial_name,
        COALESCE(v.var_name, '未匹配变量') AS var_name,
        COALESCE(SUM(dp.value), 0) AS sum_value
    FROM all_dimensions ad
    LEFT JOIN datapoints dp 
        ON ad.year = (SELECT year FROM time_dimension WHERE td_id = dp.td_id)
        AND ad.spatial_name = (SELECT name FROM spatial_dimension WHERE sd_id = dp.sd_id)
    LEFT JOIN datapoint_variablevalue dv ON dp.dp_id = dv.dp_id
    LEFT JOIN variablevalue vv ON dv.vv_id = vv.vv_id
    LEFT JOIN variable v ON vv.var_id = v.var_id
    GROUP BY ad.year, ad.spatial_name, v.var_name
)
SELECT *
FROM crosstab(
    -- 源查询：返回行标识、列标识、值
    'SELECT year, spatial_name, var_name, sum_value
     FROM aggregated_data
     ORDER BY 1, 2',
    -- 指定输出列的变量顺序
    'SELECT DISTINCT var_name FROM variable ORDER BY var_name'
) AS ct(
    year INT,
    spatial_name VARCHAR,
    -- 需与变量列表一一对应，动态场景可结合PL/pgSQL生成
    "能耗类型" NUMERIC,
    "排放等级" NUMERIC,
    "设备类型" NUMERIC
);

核心注意点 #

• 全量维度生成是关键：通过CROSS JOIN预先生成所有年份和空间的组合，再左连接数据，确保无数据的组合也被包含。
• COALESCE(SUM(...), 0)确保无匹配数据时求和结果为0，满足需求。
• 若需完全动态生成列名，可结合PL/pgSQL编写动态SQL，自动适配变量列表的变化。

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