如何在PostgreSQL中将按category和date_period分组的数据合并为单行

实现方案 #

你需要的是行转列效果，PostgreSQL里直接用条件聚合就能实现，SQL语句如下：

SELECT
  category,
  date_period,
  MAX(CASE WHEN unique_key LIKE '%_all' THEN value END) AS "all",
  MAX(CASE WHEN unique_key LIKE '%_partial' THEN value END) AS partial
FROM 你的表名
GROUP BY category, date_period
ORDER BY category, date_period;

逻辑说明 #

• 先按category、date_period分组，确保同分类同时段的数据归入同一组
• 用CASE语句按unique_key的后缀匹配对应的值，聚合函数用MAX/SUM都可以，因为每个分组里_all、_partial各只有一条数据，聚合只是为了提取唯一的非空值
• 这里给all加双引号是因为all是SQL保留字，不加引号可能会触发语法报错

可选优化 #

如果担心unique_key中间出现_all、_partial字符导致匹配错误，可以用拆分函数精准判断后缀：

SELECT
  category,
  date_period,
  MAX(CASE WHEN SPLIT_PART(unique_key, '_', 2) = 'all' THEN value END) AS "all",
  MAX(CASE WHEN SPLIT_PART(unique_key, '_', 2) = 'partial' THEN value END) AS partial
FROM 你的表名
GROUP BY category, date_period
ORDER BY category, date_period;

如果需要把缺失值转成0，把聚合表达式包一层COALESCE(表达式, 0)即可。

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