如何实现F1赛事预测与结果匹配的积分计算及存储？

1. 创建积分存储表 #

首先需要专门的表存储用户预测积分，参考设计如下（可根据数据库类型调整字段类型）：

CREATE TABLE user_prediction_scores (
    score_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL, -- 关联用户表（假设你有用户表）
    race_event_id INT NOT NULL, -- 关联赛事表，区分不同赛事
    pole_position_points INT DEFAULT 0,
    champion_points INT DEFAULT 0,
    runner_up_points INT DEFAULT 0,
    third_place_points INT DEFAULT 0,
    fastest_lap_points INT DEFAULT 0,
    total_points INT DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (race_event_id) REFERENCES races(race_id), -- 假设赛事表为races
    UNIQUE KEY unique_user_race (user_id, race_event_id) -- 避免同一用户同一赛事重复存储积分
);

2. 计算并插入/更新积分 #

结合你已有的CASE计算逻辑，用INSERT INTO ... SELECT将结果写入积分表。若需支持赛事结果更新后重新计算积分，可使用数据库的UPSERT语法：

MySQL 示例 #

INSERT INTO user_prediction_scores (
    user_id, race_event_id,
    pole_position_points, champion_points, runner_up_points, third_place_points, fastest_lap_points, total_points
)
SELECT
    r.user_id, r.race_id,
    CASE WHEN r.pole_prediction_driver_id = res.pole_driver_id THEN 5 ELSE 0 END AS pole_points,
    CASE WHEN r.champion_prediction_driver_id = res.champion_driver_id THEN 10 ELSE 0 END AS champ_points,
    CASE WHEN r.runner_up_prediction_driver_id = res.runner_up_driver_id THEN 8 ELSE 0 END AS runner_up_points,
    CASE WHEN r.third_place_prediction_driver_id = res.third_place_driver_id THEN 6 ELSE 0 END AS third_points,
    CASE WHEN r.fastest_lap_prediction_driver_id = res.fastest_lap_driver_id THEN 3 ELSE 0 END AS fastest_lap_points,
    -- 计算总积分
    (CASE WHEN r.pole_prediction_driver_id = res.pole_driver_id THEN 5 ELSE 0 END) +
    (CASE WHEN r.champion_prediction_driver_id = res.champion_driver_id THEN 10 ELSE 0 END) +
    (CASE WHEN r.runner_up_prediction_driver_id = res.runner_up_driver_id THEN 8 ELSE 0 END) +
    (CASE WHEN r.third_place_prediction_driver_id = res.third_place_driver_id THEN 6 ELSE 0 END) +
    (CASE WHEN r.fastest_lap_prediction_driver_id = res.fastest_lap_driver_id THEN 3 ELSE 0 END) AS total_points
FROM race r -- 你的用户预测表
JOIN result res ON r.race_id = res.race_id -- 关联对应赛事实际结果
ON DUPLICATE KEY UPDATE
    pole_position_points = VALUES(pole_position_points),
    champion_points = VALUES(champion_points),
    runner_up_points = VALUES(runner_up_points),
    third_place_points = VALUES(third_place_points),
    fastest_lap_points = VALUES(fastest_lap_points),
    total_points = VALUES(total_points);

PostgreSQL 示例（UPSERT语法） #

INSERT INTO user_prediction_scores (
    user_id, race_event_id,
    pole_position_points, champion_points, runner_up_points, third_place_points, fastest_lap_points, total_points
)
SELECT
    r.user_id, r.race_id,
    CASE WHEN r.pole_prediction_driver_id = res.pole_driver_id THEN 5 ELSE 0 END,
    CASE WHEN r.champion_prediction_driver_id = res.champion_driver_id THEN 10 ELSE 0 END,
    CASE WHEN r.runner_up_prediction_driver_id = res.runner_up_driver_id THEN 8 ELSE 0 END,
    CASE WHEN r.third_place_prediction_driver_id = res.third_place_driver_id THEN 6 ELSE 0 END,
    CASE WHEN r.fastest_lap_prediction_driver_id = res.fastest_lap_driver_id THEN 3 ELSE 0 END,
    (CASE WHEN r.pole_prediction_driver_id = res.pole_driver_id THEN 5 ELSE 0 END) +
    (CASE WHEN r.champion_prediction_driver_id = res.champion_driver_id THEN 10 ELSE 0 END) +
    (CASE WHEN r.runner_up_prediction_driver_id = res.runner_up_driver_id THEN 8 ELSE 0 END) +
    (CASE WHEN r.third_place_prediction_driver_id = res.third_place_driver_id THEN 6 ELSE 0 END) +
    (CASE WHEN r.fastest_lap_prediction_driver_id = res.fastest_lap_driver_id THEN 3 ELSE 0 END)
FROM race r
JOIN result res ON r.race_id = res.race_id
ON CONFLICT (user_id, race_event_id) DO UPDATE SET
    pole_position_points = EXCLUDED.pole_position_points,
    champion_points = EXCLUDED.champion_points,
    runner_up_points = EXCLUDED.runner_up_points,
    third_place_points = EXCLUDED.third_place_points,
    fastest_lap_points = EXCLUDED.fastest_lap_points,
    total_points = EXCLUDED.total_points;

3. 生成用户排名 #

基于积分表，用窗口函数计算排名：

总排名（所有赛事累计） #

SELECT
    u.username,
    SUM(ups.total_points) AS overall_total_points,
    RANK() OVER (ORDER BY SUM(ups.total_points) DESC) AS user_rank
FROM user_prediction_scores ups
JOIN users u ON ups.user_id = u.user_id
GROUP BY u.user_id, u.username
ORDER BY user_rank;

单赛事排名 #

SELECT
    u.username,
    ups.total_points,
    RANK() OVER (ORDER BY ups.total_points DESC) AS race_rank
FROM user_prediction_scores ups
JOIN users u ON ups.user_id = u.user_id
WHERE ups.race_event_id = 123 -- 替换为目标赛事ID
ORDER BY race_rank;

注意事项 #

• 确保race表包含user_id字段并关联用户表，若之前没有需补充。
• 积分规则（如5分、10分）可根据需求调整。
• 可将积分计算逻辑封装为存储过程，方便赛事结束后自动执行。

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