计算团队成员动态占比及多数派判定的SQL实现

针对players_in_teams事件表的团队命名需求——当某姓名球员占比≥60%时以该姓名命名团队，否则无名称，同时要覆盖4种场景且避免自连接适配千万级数据，可采用以下基于窗口函数的方案：

核心思路 #

通过窗口函数的累计聚合，实时计算每个事件发生后：

1. 各姓名在团队中的当前人数
2. 团队的当前总人数
   然后对每个时间点的姓名占比排序，提取最高占比的记录并判断是否达标。

实现代码 #

假设表结构为 team_id, event_time, player_name, event_type（event_type取值join/leave）：

WITH running_counts AS (
    SELECT
        team_id,
        event_time,
        player_name,
        event_type,
        -- 累计计算当前姓名在团队中的人数（加入+1，退出-1）
        SUM(CASE WHEN event_type = 'join' THEN 1 ELSE -1 END) OVER (
            PARTITION BY team_id, player_name
            ORDER BY event_time
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS current_name_count,
        -- 累计计算团队当前总人数
        SUM(CASE WHEN event_type = 'join' THEN 1 ELSE -1 END) OVER (
            PARTITION BY team_id
            ORDER BY event_time
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS current_team_size
    FROM players_in_teams
),
ranked_counts AS (
    SELECT
        *,
        -- 按占比降序排名，占比相同时可按姓名排序（按需调整）
        RANK() OVER (
            PARTITION BY team_id, event_time
            ORDER BY (current_name_count::FLOAT / current_team_size) DESC, player_name
        ) AS rank
    FROM running_counts
    -- 过滤掉已退出团队的姓名（当前人数为0的记录）
    WHERE current_name_count > 0
)
SELECT
    team_id,
    event_time,
    -- 判断最高占比是否达标，生成团队名称
    CASE
        WHEN (current_name_count::FLOAT / current_team_size) >= 0.6 THEN player_name
        ELSE NULL
    END AS team_name,
    current_team_size,
    current_name_count AS top_name_count
FROM ranked_counts
WHERE rank = 1;

方案优势与场景覆盖 #

• 无自连接，性能高效：全程依赖窗口函数计算，避免了千万级数据下自连接的性能瓶颈
• 覆盖所有4种场景：
  • 场景1：球员加入后自身占比超60% → 该姓名的累计占比达标，会被选为top记录
  • 场景2：球员退出后自身占比仍≥60% → 退出后人数减少但占比仍达标，保持top位置
  • 场景3：新球员加入拉低其他姓名占比至60%以下 → 团队总人数增加，原top姓名占比下降，排名被挤掉，若新top占比不达标则团队名称为NULL
  • 场景4：球员退出后其他姓名占比达标 → 团队总人数减少，其他姓名占比上升，若≥60%则成为新的团队名称

注意事项 #

• 确保event_time的唯一性或有序性，若同一时间点有多个事件，建议添加event_id作为排序辅助字段（ORDER BY event_time, event_id）
• 处理团队总人数为0的情况（如最后一个成员退出），此时团队名称自然为NULL，可通过WHERE条件过滤或CASE分支单独处理
• 占比计算时注意数据类型转换：PostgreSQL用::FLOAT，MySQL用CAST(current_name_count AS DECIMAL)/current_team_size，避免整数除法导致的错误

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