基于用户维度过滤早期记录的MySQL查询优化求助

场景与表结构 #

使用MySQL搭配MariaDB，涉及游戏结果报表表results_daily，结构如下：

• id：每条报表的唯一整数ID，主键
• user_id：每位玩家的整数ID
• day：每日谜题的标识整数
• result：得分整数
• submitted：报表提交时间戳

注：每日谜题在当地时间午夜切换，同一提交时间可能对应不同日期。

需求 #

计算每日的平均得分，但仅保留用户首次获得>5分之后的所有记录，排除用户首次达标前的所有记录，以此剔除纯新手和游戏水平异常低下的玩家。

当前进展 #

已能通过以下查询获取每位用户首次取得合格得分（result>5）的记录：

SELECT id, user_id, MIN(submitted), result FROM `results_daily` WHERE result > 5 GROUP BY user_id

（记为output1）
但无法将此结果作为筛选条件，实现“仅保留output1中存在的用户，且其每日报表提交时间晚于该用户在output1中的提交时间”的过滤逻辑，进而计算平均值。

补充：已实现的函数方案 #

已通过自定义函数实现需求，但不确定是否为最高效、最符合SQL规范的方式，希望找到无需函数的更优方案。当前查询如下：

SELECT day, 
       AVG(result) average_score,
       COUNT(*) number_of_plays,
       COUNT(DISTINCT user_id) number_of_non_n00b_players
  FROM results_daily
 WHERE user_id NOT IN (
             SELECT user_id 
               FROM results_daily
                WHERE submitted < GetEureka(user_id)
              GROUP BY user_id )           
 GROUP BY day; 

自定义函数GetEureka()定义：

DECLARE eureka TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

SELECT
    MIN(submitted) INTO eureka
FROM
    results_daily
WHERE
    user_id = user
    AND
    result >= 5
GROUP BY
    user_id;
RETURN eureka;

无函数的最优实现方案 #

方案一：关联子查询（兼容低版本数据库） #

先通过子查询获取每个用户首次达标（result>5）的时间，再通过JOIN关联主表，筛选出达标后的记录进行聚合：

SELECT 
    day,
    AVG(result) AS average_score,
    COUNT(*) AS number_of_plays,
    COUNT(DISTINCT user_id) AS number_of_non_n00b_players
FROM results_daily r
INNER JOIN (
    -- 子查询：获取每个用户首次取得>5分的提交时间
    SELECT user_id, MIN(submitted) AS first_qualified_time
    FROM results_daily
    WHERE result > 5
    GROUP BY user_id
) qualified_users ON r.user_id = qualified_users.user_id
WHERE r.submitted >= qualified_users.first_qualified_time
GROUP BY day;

方案二：窗口函数（逻辑更简洁，适合高版本数据库） #

如果使用MySQL 8.0+或MariaDB 10.2+，可以用窗口函数直接标记每个用户的首次达标时间，再过滤聚合：

WITH user_score_info AS (
    SELECT 
        *,
        -- 计算每个用户首次取得>5分的提交时间
        MIN(CASE WHEN result > 5 THEN submitted END) OVER (PARTITION BY user_id) AS first_qualified_time
    FROM results_daily
)
SELECT 
    day,
    AVG(result) AS average_score,
    COUNT(*) AS number_of_plays,
    COUNT(DISTINCT user_id) AS number_of_non_n00b_players
FROM user_score_info
WHERE 
    first_qualified_time IS NOT NULL  -- 排除从未达标过的用户
    AND submitted >= first_qualified_time
GROUP BY day;

方案说明 #

• 方案一兼容性强，适合不支持窗口函数的旧版本数据库；
• 方案二逻辑更直观，代码更简洁，大表场景下性能更优（建议给user_id和submitted字段建立联合索引）。

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