如何无需迭代编写SQL 用PIVOT实现话题标签用户计数表行列转换

你要实现的是典型的行转列（宽表转换）需求，以下是两种可行的单条SQL写法：

方案1：使用PIVOT函数（适配支持PIVOT语法的数据库，如Spark SQL、PostgreSQL 11+、MySQL 8.0+、Oracle等） #

你之前用PIVOT没得到预期结果，大概率是没有处理空值、或者没有给转换后的字段指定别名，正确写法如下：

SELECT 
  user_id AS `user`,
  COALESCE(hashtag_123, 0) AS hashtag_123,
  COALESCE(hashtag_245, 0) AS hashtag_245
FROM hashtags_users_grouped
PIVOT (
  -- 聚合函数，同一用户同个hashtag有多条记录时用SUM求和，单条记录用MAX也可
  SUM(`count`) 
  -- 指定要转成列的hashtag_id，同时指定别名
  FOR hashtag_id IN (123 AS hashtag_123, 245 AS hashtag_245)
) AS pivot_result
ORDER BY `user`;

方案2：条件聚合写法（全数据库兼容） #

如果你的数据库不支持PIVOT语法，或者想要更灵活的逻辑控制，可以用CASE+聚合函数的通用写法，不需要额外处理空值：

SELECT
  user_id AS `user`,
  SUM(CASE WHEN hashtag_id = 123 THEN `count` ELSE 0 END) AS hashtag_123,
  SUM(CASE WHEN hashtag_id = 245 THEN `count` ELSE 0 END) AS hashtag_245
FROM hashtags_users_grouped
GROUP BY user_id
ORDER BY `user`;

补充说明 #

如果你的hashtag_id是动态变化的，不能提前枚举所有值，那么静态单条SQL无法实现需求，需要使用动态SQL拼接，或者你提到的PySpark遍历生成列的方案。

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