PostgreSQL中将GROUP BY结果转为列展示的实现方法

针对你的需求，PostgreSQL提供了三种主流方案实现行转列，下面结合你的原查询详细说明：

方案1：条件聚合（无需额外扩展，推荐固定年份场景） #

这是最通用的实现方式，通过CASE WHEN结合聚合函数直接生成横向列，语法简单且无需依赖任何扩展。首先优化原查询的连接写法（替换隐式连接为显式JOIN，更易读且符合SQL标准），再添加条件聚合逻辑：

SELECT 
    s.id AS societe,
    typecombustible,
    -- 按实际存在的年份生成对应列，按需替换
    SUM(CASE WHEN EXTRACT(YEAR FROM p.datedebut) = 2020 THEN quantiteconsommee ELSE 0 END) AS "2020",
    SUM(CASE WHEN EXTRACT(YEAR FROM p.datedebut) = 2021 THEN quantiteconsommee ELSE 0 END) AS "2021",
    SUM(CASE WHEN EXTRACT(YEAR FROM p.datedebut) = 2022 THEN quantiteconsommee ELSE 0 END) AS "2022"
FROM sch_consomind.consommationcombustible cc
JOIN sch_referentiel.unite u ON cc.unite = u.id
JOIN sch_referentiel.societe s ON u.societe_id = s.id
JOIN sch_referentiel.periode p ON cc.periode = p.id
GROUP BY s.id, typecombustible
ORDER BY s.id, typecombustible;

优缺点 #

• 优点：语法直观、无需额外配置、性能稳定
• 缺点：若年份动态变化，需手动更新列定义

方案2：使用crosstab函数（需安装tablefunc扩展） #

PostgreSQL通过tablefunc扩展提供了crosstab函数，专门用于行转列操作，适合需要规范化行转列的场景。

步骤1：启用tablefunc扩展 #

仅需执行一次以下语句安装扩展：

CREATE EXTENSION IF NOT EXISTS tablefunc;

步骤2：编写crosstab查询 #

crosstab需要两个参数：生成纵向结构的源数据查询、指定横向列的定义查询。

SELECT *
FROM crosstab(
    -- 源数据查询：生成societe、typecombustible、年份、消耗量总和的纵向数据
    $$
    SELECT 
        s.id AS societe,
        typecombustible,
        EXTRACT(YEAR FROM p.datedebut)::TEXT AS yearrr,
        SUM(quantiteconsommee) AS somme
    FROM sch_consomind.consommationcombustible cc
    JOIN sch_referentiel.unite u ON cc.unite = u.id
    JOIN sch_referentiel.societe s ON u.societe_id = s.id
    JOIN sch_referentiel.periode p ON cc.periode = p.id
    GROUP BY s.id, typecombustible, yearrr
    ORDER BY s.id, typecombustible, yearrr
    $$,
    -- 列定义查询：获取所有存在的年份，作为横向列
    $$
    SELECT DISTINCT EXTRACT(YEAR FROM p.datedebut)::TEXT
    FROM sch_referentiel.periode p
    JOIN sch_consomind.consommationcombustible cc ON cc.periode = p.id
    ORDER BY 1
    $$
) AS ct(
    -- 定义结果表的列，前两列为分组列，后续年份列需与列定义查询结果顺序一致
    societe INT,
    typecombustible VARCHAR,
    "2020" NUMERIC,
    "2021" NUMERIC,
    "2022" NUMERIC
);

方案3：动态SQL（适合年份动态变化的场景） #

如果数据中的年份不确定且随时变化，可以用PL/pgSQL编写动态SQL，自动生成对应的横向列：

DO $$
DECLARE
    year_columns TEXT;
    query TEXT;
BEGIN
    -- 自动拼接所有年份对应的CASE聚合语句
    SELECT 
        STRING_AGG(
            'SUM(CASE WHEN EXTRACT(YEAR FROM p.datedebut) = ' || yearrr || ' THEN quantiteconsommee ELSE 0 END) AS "' || yearrr || '"',
            ', '
        ) INTO year_columns
    FROM (
        SELECT DISTINCT EXTRACT(YEAR FROM p.datedebut)::INT AS yearrr
        FROM sch_referentiel.periode p
        JOIN sch_consomind.consommationcombustible cc ON cc.periode = p.id
        ORDER BY yearrr
    ) AS years;

    -- 生成完整的查询语句
    query := '
    SELECT 
        s.id AS societe,
        typecombustible,
        ' || year_columns || '
    FROM sch_consomind.consommationcombustible cc
    JOIN sch_referentiel.unite u ON cc.unite = u.id
    JOIN sch_referentiel.societe s ON u.societe_id = s.id
    JOIN sch_referentiel.periode p ON cc.periode = p.id
    GROUP BY s.id, typecombustible
    ORDER BY s.id, typecombustible;
    ';

    -- 执行动态生成的查询
    EXECUTE query;
END $$;

这个脚本会自动扫描数据中存在的所有年份，生成对应的横向列，无需手动维护年份列表。

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