PostgreSQL表转置：将propertyId对应聚合列转为新列

问题描述 #

需要将包含userId、时间窗口、propertyId以及count/sum/avg/max聚合列的表进行转置，把每个propertyId对应的聚合列转为以其为前缀的新列（如a_count、a_sum），最终每个userId+时间窗口对应一行，包含所有propertyId的聚合值。且propertyId的值可能动态变化，新列数量会随propertyId的数量自动调整（n个不同值对应4n个聚合列）。

原表示例 #

目标表结构示例 #

解决方案 #

1. 静态场景（已知所有propertyId） #

如果propertyId的值固定，直接用条件聚合即可实现：

SELECT
  userId,
  "时间窗口",
  -- 处理propertyId='a'的聚合列
  MAX(CASE WHEN propertyId = 'a' THEN count END) AS a_count,
  MAX(CASE WHEN propertyId = 'a' THEN sum END) AS a_sum,
  MAX(CASE WHEN propertyId = 'a' THEN avg END) AS a_avg,
  MAX(CASE WHEN propertyId = 'a' THEN max END) AS a_max,
  -- 处理propertyId='b'的聚合列
  MAX(CASE WHEN propertyId = 'b' THEN count END) AS b_count,
  MAX(CASE WHEN propertyId = 'b' THEN sum END) AS b_sum,
  MAX(CASE WHEN propertyId = 'b' THEN avg END) AS b_avg,
  MAX(CASE WHEN propertyId = 'b' THEN max END) AS b_max
FROM your_table_name
GROUP BY userId, "时间窗口"
ORDER BY userId, "时间窗口";

2. 动态场景（propertyId动态变化） #

当propertyId的值不确定且会动态变化时，需要用动态SQL自动生成查询语句：

完整动态SQL脚本

DO $$
DECLARE
  dynamic_sql TEXT;
  columns TEXT;
BEGIN
  -- 生成所有propertyId对应的聚合列片段
  SELECT string_agg(
    format(
      'MAX(CASE WHEN propertyId = %L THEN count END) AS %I_count,
       MAX(CASE WHEN propertyId = %L THEN sum END) AS %I_sum,
       MAX(CASE WHEN propertyId = %L THEN avg END) AS %I_avg,
       MAX(CASE WHEN propertyId = %L THEN max END) AS %I_max',
      p.propertyId, p.propertyId,
      p.propertyId, p.propertyId,
      p.propertyId, p.propertyId,
      p.propertyId, p.propertyId
    ),
    ', '
  ) INTO columns
  FROM (SELECT DISTINCT propertyId FROM your_table_name) p;

  -- 拼接完整查询语句
  dynamic_sql := format(
    'SELECT userId, "时间窗口", %s
     FROM your_table_name
     GROUP BY userId, "时间窗口"
     ORDER BY userId, "时间窗口";',
    columns
  );

  -- 执行动态SQL
  EXECUTE dynamic_sql;
END $$;

关键说明

• 替换your_table_name为实际表名；
• %L用于转义字符串值，%I用于处理标识符（自动转义特殊字符、大写字母等）；
• 若需要将结果保存为新表，可将EXECUTE中的语句改为CREATE TABLE new_table_name AS ...。

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