Oracle SQL：按weekOfTheYear分组获取出现次数最多的ID值

我有一条SELECT查询返回如下结果：

需要得到每个weekOfTheYear唯一出现，且对应mostRepeatedID为该周内出现次数最多的取值，结果如下：

通用方法（支持窗口函数的数据库：PostgreSQL、MySQL 8+、SQL Server等） #

通过分组统计次数结合窗口函数排序筛选，SQL代码如下：

WITH count_per_week AS (
    SELECT 
        weekOfTheYear,
        mostRepeatedID,
        COUNT(*) AS occurrence_count
    FROM your_table_name  -- 替换为你的表名或原查询语句
    GROUP BY weekOfTheYear, mostRepeatedID
),
ranked_counts AS (
    SELECT 
        weekOfTheYear,
        mostRepeatedID,
        RANK() OVER (PARTITION BY weekOfTheYear ORDER BY occurrence_count DESC) AS rank_num
    FROM count_per_week
)
SELECT weekOfTheYear, mostRepeatedID
FROM ranked_counts
WHERE rank_num = 1;

逻辑说明：

1. count_per_week：按周和ID分组，统计每个ID在对应周的出现次数
2. ranked_counts：用RANK()窗口函数按周分组，对每个周内的ID按出现次数降序排名
3. 最后筛选排名为1的记录，即为该周出现次数最多的ID

处理并列最多的情况 #

如果某一周存在多个ID出现次数相同且均为最高，RANK()会返回多条记录。若需强制返回唯一结果，可改用ROW_NUMBER()，并补充排序规则（比如按ID升序）：

WITH count_per_week AS (
    SELECT 
        weekOfTheYear,
        mostRepeatedID,
        COUNT(*) AS occurrence_count
    FROM your_table_name
    GROUP BY weekOfTheYear, mostRepeatedID
),
ranked_counts AS (
    SELECT 
        weekOfTheYear,
        mostRepeatedID,
        ROW_NUMBER() OVER (PARTITION BY weekOfTheYear ORDER BY occurrence_count DESC, mostRepeatedID ASC) AS row_num
    FROM count_per_week
)
SELECT weekOfTheYear, mostRepeatedID
FROM ranked_counts
WHERE row_num = 1;

内容的提问来源于stack exchange，提问作者Alejandro Hernández