MySQL：构建计算超时惩罚后聚合用户最高得分的查询

核心思路 #

先关联problem和user_submission表，计算每个提交的调整后得分（应用超时惩罚规则），再按problem_id和uid分组，取每组的最高调整后得分。

具体SQL实现（以MySQL为例） #

SELECT
    problem_id,
    uid,
    MAX(adjusted_score) AS max_score
FROM (
    -- 子查询：计算每个提交的调整后得分
    SELECT
        us.problem_id,
        us.uid,
        us.score * 
        CASE
            -- 未超时或提前提交，得分不变
            WHEN TIMESTAMPDIFF(MINUTE, STR_TO_DATE(p.deadline, '%H:%i'), STR_TO_DATE(us.time, '%H:%i')) <= 0 THEN 1
            -- 超时提交，应用惩罚系数
            ELSE (100 - LEAST(TIMESTAMPDIFF(MINUTE, STR_TO_DATE(p.deadline, '%H:%i'), STR_TO_DATE(us.time, '%H:%i')), 80)) / 100
        END AS adjusted_score
    FROM user_submission us
    INNER JOIN problem p ON us.problem_id = p.id
) AS adjusted_submissions
GROUP BY problem_id, uid
ORDER BY problem_id, uid;

代码说明 #

1. 时间差计算：用STR_TO_DATE将字符串格式的时间转换为可计算的时间类型，再通过TIMESTAMPDIFF(MINUTE, 截止时间, 提交时间)得到超时分钟数（负数表示提前提交）。
2. 惩罚规则应用：
   • 若提交时间早于/等于截止时间，惩罚系数为1，得分保持原值；
   • 若超时，取超时分钟数与80的较小值，计算(100 - min(n,80))/100作为惩罚系数，与原始得分相乘得到调整后得分。
3. 聚合取最大值：外层查询按problem_id和uid分组，用MAX()函数提取每组的最高调整后得分。

其他数据库适配提示 #

如果使用PostgreSQL，时间差计算方式略有不同，可替换为：

EXTRACT(EPOCH FROM (us.time::TIME - p.deadline::TIME)) / 60

来获取分钟级的时间差，其余逻辑保持一致。

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