如何修正SQL查询以获取每日合规唯一Hacker及最高提交者信息

嘿，我来帮你搞定这个SQL问题！先明确你的核心需求，再拆解原查询的问题，最后给你修正后的方案：

核心需求回顾 #

你需要完成两个关键统计：

• 统计竞赛首日起，每日至少提交一次的唯一Hacker总数
• 找出每日提交量最多的hacker_id与name，若多人提交量相同则取最小的hacker_id，最终结果按日期排序

原查询的问题分析 #

你的原查询存在两个核心问题：

1. 用count(s.hacker_id) over(partition by s.submission_date)统计的是当日所有提交记录的数量（同一个hacker多次提交会被重复计数），而不是你需要的唯一Hacker总数
2. row_number()的排序逻辑错误：你只是按hacker_id升序取了当日第一个出现的hacker，完全没有考虑“提交量最多”的筛选规则

修正后的SQL查询 #

WITH daily_submissions AS (
    -- 第一步：统计每个hacker每日提交次数，同时计算当日唯一hacker总数
    SELECT 
        submission_date,
        hacker_id,
        COUNT(submission_id) AS daily_submit_count,
        COUNT(DISTINCT hacker_id) OVER(PARTITION BY submission_date) AS daily_unique_hackers
    FROM submissions
    GROUP BY submission_date, hacker_id
),
top_hackers AS (
    -- 第二步：筛选每日提交量最多的hacker，并列时取最小id
    SELECT 
        submission_date,
        hacker_id,
        daily_unique_hackers,
        ROW_NUMBER() OVER(
            PARTITION BY submission_date 
            ORDER BY daily_submit_count DESC, hacker_id ASC
        ) AS rn
    FROM daily_submissions
)
-- 第三步：关联用户表获取名字，按日期排序输出
SELECT 
    th.submission_date,
    th.daily_unique_hackers,
    th.hacker_id,
    h.name
FROM top_hackers th
JOIN hackers h ON th.hacker_id = h.hacker_id
WHERE th.rn = 1
ORDER BY th.submission_date;

逻辑拆解说明 #

1. daily_submissions 公用表表达式（CTE）：

   • 先按submission_date和hacker_id分组，统计每个hacker当日的提交次数daily_submit_count
   • 用窗口函数COUNT(DISTINCT hacker_id)计算当日所有唯一提交Hacker的总数，这正是你需要的每日唯一Hacker数

2. top_hackers CTE：

   • 对每个日期，按daily_submit_count降序（提交多的在前）、hacker_id升序（提交数相同时取小id）排序
   • 用ROW_NUMBER()给每个日期内的hacker标记排名，排名为1的就是当日符合要求的目标hacker

3. 最终查询：

   • 关联hackers表获取hacker的名字，过滤出排名为1的记录，按日期排序后输出，结果完全匹配你的预期

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