PostgreSQL中使用crosstab实现动态列转置的技术咨询

原表结构与数据 #

CREATE TABLE ProductSales
(    
    Productname varchar(50),
    Year int,
    Sales int
);
 
INSERT INTO ProductSales 
values ('A', 2017, 100),
       ('A', 2018, 150),
       ('A', 2019, 300),
       ('A', 2020, 500),
       ('A', 2021, 450),
       ('A', 2022, 675),
       ('B', 2017, null),
       ('B', 2019, 1120),
       ('B', 2020, 750),
       ('B', 2021, 1500),
       ('B', 2022, 1980);

原表数据：

目标结果 #

解决方案 #

PostgreSQL完全可以实现该需求，以下提供两种可行方法：

方法1：使用条件聚合（无需扩展，简单直观） #

无需安装任何扩展，直接通过CASE WHEN结合聚合函数实现转置，自动处理缺失年份的null值：

SELECT
  Productname,
  MAX(CASE WHEN Year = 2017 THEN Sales END) AS "2017",
  MAX(CASE WHEN Year = 2018 THEN Sales END) AS "2018",
  MAX(CASE WHEN Year = 2019 THEN Sales END) AS "2019",
  MAX(CASE WHEN Year = 2020 THEN Sales END) AS "2020",
  MAX(CASE WHEN Year = 2021 THEN Sales END) AS "2021",
  MAX(CASE WHEN Year = 2022 THEN Sales END) AS "2022"
FROM ProductSales
GROUP BY Productname
ORDER BY Productname;

方法2：使用crosstab函数（需tablefunc扩展） #

如果偏好使用crosstab，需先启用tablefunc扩展：

CREATE EXTENSION IF NOT EXISTS tablefunc;

结合年份表的实现（推荐，适配动态年份）

若存在单独的年份表（示例中的Years表），可先关联补全所有年份数据，确保转置后包含所有目标年份列：

SELECT * FROM crosstab(
  -- 生成包含所有产品+所有年份的数据集
  'SELECT ps.Productname, y.Year, ps.Sales
   FROM Years y
   LEFT JOIN ProductSales ps 
     ON y.Year = ps.Year AND ps.Productname = ps.Productname
   ORDER BY 1, 2',
  -- 指定转置后的列对应的年份
  'SELECT DISTINCT Year FROM Years ORDER BY Year'
) AS ct(
  Productname varchar(50),
  "2017" int, "2018" int, "2019" int,
  "2020" int, "2021" int, "2022" int
);

无年份表的实现

如果没有单独的年份表，可通过generate_series生成年份范围：

SELECT * FROM crosstab(
  'SELECT ps.Productname, y.Year, ps.Sales
   FROM (SELECT generate_series(2017, 2022) AS Year) y
   LEFT JOIN ProductSales ps 
     ON y.Year = ps.Year AND ps.Productname = ps.Productname
   ORDER BY 1, 2',
  'SELECT generate_series(2017, 2022) ORDER BY 1'
) AS ct(
  Productname varchar(50),
  "2017" int, "2018" int, "2019" int,
  "2020" int, "2021" int, "2022" int
);

注：之前使用crosstab失败大概率是因为未启用扩展，或未补全缺失年份导致数据集不完整。

关于单独年份表的作用 #

单独的年份表确实有帮助：

1. 适配动态年份：当年份新增或变化时，无需修改SQL语句中的硬编码年份值，只需维护年份表即可。
2. 确保数据完整性：通过左关联年份表，可自动补全产品缺失的年份记录，避免转置后丢失列。

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