PostgreSQL基于关联数据创建透视表，分析高频用户标签相关性

嘿，先帮你揪出原查询里的一个小错误：INNER JOIN tags ON user_tags.tag_id = tags.id 里的user_tags应该是tag_users，不然会报表不存在的错误～

接下来针对你要的「user_id | 标签1 | 标签2 | ...」的0/1矩阵需求，分两种常用场景给你具体实现方案，覆盖主流数据库：

一、标签数量固定且少的情况 #

如果你的标签列表是确定的（比如就几个核心标签），直接用静态SQL就能搞定，写法简单易维护。

MySQL 写法 #

SELECT
  u.id AS user_id,
  MAX(CASE WHEN t.title = '数据分析' THEN 1 ELSE 0 END) AS `数据分析`,
  MAX(CASE WHEN t.title = '前端开发' THEN 1 ELSE 0 END) AS `前端开发`,
  MAX(CASE WHEN t.title = '后端架构' THEN 1 ELSE 0 END) AS `后端架构`
FROM users u
INNER JOIN tag_users tu ON tu.user_id = u.id
INNER JOIN tags t ON tu.tag_id = t.id
GROUP BY u.id;

核心逻辑：用CASE判断用户是否拥有当前标签，有就返回1，否则0；再通过MAX聚合（因为一个用户对应多条标签记录，聚合后保留该用户是否持有标签的状态），最后按用户ID分组。

SQL Server 写法（用PIVOT语法更简洁） #

SELECT user_id, [数据分析], [前端开发], [后端架构]
FROM (
  SELECT u.id AS user_id, t.title
  FROM users u
  INNER JOIN tag_users tu ON tu.user_id = u.id
  INNER JOIN tags t ON tu.tag_id = t.id
) AS SourceTable
PIVOT (
  COUNT(title) -- 有标签则计数为1，无则显示0
  FOR title IN ([数据分析], [前端开发], [后端架构])
) AS PivotTable;

二、标签数量不确定/经常新增的情况 #

如果标签是动态变化的，静态SQL就不适用了，得用动态SQL自动生成所有标签列。

MySQL 动态SQL实现 #

-- 第一步：自动生成所有标签的列定义
SELECT GROUP_CONCAT(
  DISTINCT CONCAT(
    'MAX(CASE WHEN t.title = ''', t.title, ''' THEN 1 ELSE 0 END) AS `', t.title, '`'
  )
) INTO @pivot_cols
FROM tags;

-- 第二步：拼接完整SQL并执行
SET @pivot_sql = CONCAT(
  'SELECT u.id AS user_id, ', @pivot_cols, ' 
   FROM users u
   LEFT JOIN tag_users tu ON tu.user_id = u.id
   LEFT JOIN tags t ON tu.tag_id = t.id
   GROUP BY u.id;'
);

PREPARE stmt FROM @pivot_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

这里用LEFT JOIN是为了包含那些没有任何标签的用户（所有标签列显示0），如果只需要有标签的用户，换回INNER JOIN就行。

PostgreSQL 动态SQL实现 #

DO $$
DECLARE
  pivot_cols text;
BEGIN
  -- 生成标签列的SQL片段
  SELECT string_agg(DISTINCT format('MAX(CASE WHEN t.title = %L THEN 1 ELSE 0 END) AS %I', t.title, t.title), ', ')
  INTO pivot_cols
  FROM tags;

  -- 执行动态生成的透视表SQL
  EXECUTE format('
    SELECT u.id AS user_id, %s
    FROM users u
    LEFT JOIN tag_users tu ON tu.user_id = u.id
    LEFT JOIN tags t ON tu.tag_id = t.id
    GROUP BY u.id;
  ', pivot_cols);
END $$;

三、结合高频用户做相关性分析 #

如果你要聚焦高频用户（比如按标签数量排名前N的用户），可以先筛选出高频用户ID，再嵌套到透视表查询里。示例（MySQL）：

SELECT
  u.id AS user_id,
  MAX(CASE WHEN t.title = '数据分析' THEN 1 ELSE 0 END) AS `数据分析`,
  MAX(CASE WHEN t.title = '前端开发' THEN 1 ELSE 0 END) AS `前端开发`,
  MAX(CASE WHEN t.title = '后端架构' THEN 1 ELSE 0 END) AS `后端架构`
FROM users u
INNER JOIN tag_users tu ON tu.user_id = u.id
INNER JOIN tags t ON tu.tag_id = t.id
-- 筛选标签数量前100的高频用户
WHERE u.id IN (
  SELECT id
  FROM users
  INNER JOIN tag_users ON tag_users.user_id = users.id
  GROUP BY id
  ORDER BY COUNT(tag_id) DESC
  LIMIT 100
)
GROUP BY u.id;

拿到这个0/1矩阵后，你就可以用统计工具（比如Python的Pandas、R）来计算标签和高频用户的相关性啦～

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