PostgreSQL中关联文本数组与JSONB，统计用户完成游戏数及击杀数

核心思路 #

1. 展开用户游戏数组：将users表中games字段的text数组拆分为单行记录，并转换为integer类型，匹配finished_games的game_id。
2. 计算单游戏总击杀：解析finished_games中playbook的gameplay数组，提取每回合kills值并求和，得到单个已完成游戏的总击杀数。
3. 关联并汇总用户数据：通过左连接关联用户游戏和已完成游戏数据，按用户分组统计总击杀数和参与的已完成游戏数量，确保无已完成游戏的用户返回0值。

最终SQL查询 #

WITH user_games AS (
    -- 展开用户参与的所有游戏ID，转换为integer类型
    SELECT
        u.uid,
        (unnest(u.games))::integer AS game_id
    FROM users u
), game_kills AS (
    -- 计算每个已完成游戏的总击杀数
    SELECT
        fg.game_id,
        SUM((jsonb_array_elements(fg.playbook->'gameplay')->>'kills')::integer) AS total_kills
    FROM finished_games fg
    GROUP BY fg.game_id
)
-- 汇总用户的已完成游戏数据
SELECT
    ug.uid,
    COALESCE(SUM(gk.total_kills), 0) AS finished_kills,
    COUNT(DISTINCT gk.game_id) AS games_participated
FROM user_games ug
LEFT JOIN game_kills gk ON ug.game_id = gk.game_id
GROUP BY ug.uid
ORDER BY ug.uid;

关键细节说明 #

• unnest(u.games)：将用户的游戏数组拆分为单行，每个游戏ID对应一条记录；::integer转换类型是为了匹配finished_games的game_id（integer类型）。
• jsonb_array_elements(fg.playbook->'gameplay')：展开playbook中存储回合数据的gameplay数组，逐个提取每回合的kills值并转换为integer后求和，得到单游戏总击杀。
• LEFT JOIN + COALESCE：确保所有用户都被包含在结果中，若用户无已完成游戏，finished_kills会被替换为0而非NULL。
• COUNT(DISTINCT gk.game_id)：避免用户games数组中重复的游戏ID导致计数错误，确保每个已完成游戏只被统计一次。

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