如何在PostgreSQL中用CASE语句/crosstab函数正确透视表格数据

问题核心梳理 #

你用CASE语句时每行仅单个年份有值、其余为NULL，是因为没做聚合处理——原表每一行对应单个艺术家的添加记录，CASE只是提取对应年份的字段，但未将同分组（比如播放列表）的同年份艺术家合并。用crosstab未得到预期结果，大概率是没处理聚合或未指定全所有年份维度。

假设原表结构 #

假设你的表名为playlist_artists，字段如下：

• playlist_id: 播放列表ID（以此分组，每个列表对应一行结果）
• artist_name: 艺术家名称
• add_year: 添加年份（2018-2022）

如果你的分组维度不是播放列表，替换为实际字段即可。

方案1：聚合+CASE实现（无NULL，直接合并艺术家） #

用STRING_AGG把同分组同年份的艺术家拼成字符串，无艺术家的年份显示空字符串（而非NULL）：

SELECT
  playlist_id,
  STRING_AGG(CASE WHEN add_year = 2018 THEN artist_name END, ', ') AS "2018",
  STRING_AGG(CASE WHEN add_year = 2019 THEN artist_name END, ', ') AS "2019",
  STRING_AGG(CASE WHEN add_year = 2020 THEN artist_name END, ', ') AS "2020",
  STRING_AGG(CASE WHEN add_year = 2021 THEN artist_name END, ', ') AS "2021",
  STRING_AGG(CASE WHEN add_year = 2022 THEN artist_name END, ', ') AS "2022"
FROM playlist_artists
GROUP BY playlist_id
ORDER BY playlist_id;

若需以数组格式存储，将STRING_AGG替换为ARRAY_AGG即可。

方案2：crosstab正确透视实现 #

首先确保安装tablefunc扩展（PostgreSQL默认未预装）：

CREATE EXTENSION IF NOT EXISTS tablefunc;

使用crosstab时，先通过子查询聚合生成三列数据（分组字段、年份、艺术家列表），再指定所有需透视的年份：

-- 自动将NULL转为空字符串的版本
SELECT
  playlist_id,
  COALESCE("2018", '') AS "2018",
  COALESCE("2019", '') AS "2019",
  COALESCE("2020", '') AS "2020",
  COALESCE("2021", '') AS "2021",
  COALESCE("2022", '') AS "2022"
FROM crosstab(
  -- 源查询：聚合每个播放列表+年份的艺术家
  $$
    SELECT
      playlist_id,
      add_year::text,
      STRING_AGG(artist_name, ', ') AS artists
    FROM playlist_artists
    WHERE add_year BETWEEN 2018 AND 2022
    GROUP BY playlist_id, add_year
    ORDER BY playlist_id, add_year
  $$,
  -- 指定所有需透视的年份，避免遗漏
  $$ SELECT unnest(ARRAY['2018','2019','2020','2021','2022']) $$
) AS ct(
  playlist_id INT,
  "2018" TEXT,
  "2019" TEXT,
  "2020" TEXT,
  "2021" TEXT,
  "2022" TEXT
);

若需求为按艺术家分组 #

如果想让每个艺术家占一行，展示其在各年份是否被添加到播放列表，将分组字段改为artist_name，用MAX取唯一值：

SELECT
  artist_name,
  COALESCE(MAX(CASE WHEN add_year = 2018 THEN artist_name END), '') AS "2018",
  COALESCE(MAX(CASE WHEN add_year = 2019 THEN artist_name END), '') AS "2019",
  COALESCE(MAX(CASE WHEN add_year = 2020 THEN artist_name END), '') AS "2020",
  COALESCE(MAX(CASE WHEN add_year = 2021 THEN artist_name END), '') AS "2021",
  COALESCE(MAX(CASE WHEN add_year = 2022 THEN artist_name END), '') AS "2022"
FROM playlist_artists
GROUP BY artist_name
ORDER BY artist_name;

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