SQL 按Year_month分组获取最高记录数对应Week_day的实现

• 第一步创建临时聚合视图的逻辑是合理的，已经完成了「不同Year_month、Week_day组合下的记录数wd_count」的粒度统计
• 第二步提取结果的逻辑存在致命偏差：查询时按Year_month, Week_day两个字段分组，和临时聚合视图的粒度完全一致，此时MAX(wd_count)拿到的就是每个分组自身的计数值，完全没有对同Year_month下的不同Week_day记录数做横向最大值比较，最终返回的结果本质上就是临时聚合视图的全量数据，自然不符合预期

方案1：窗口函数法（推荐，适配绝大多数现代SQL引擎） #

核心逻辑是在聚合后的结果集上，按Year_month分区对wd_count做降序排名，取排名第一的记录即可，同时可灵活兼容同计数并列的业务场景。

-- 第一步：完成基础维度聚合
CREATE OR REPLACE TEMPORARY VIEW Year_month_agg
AS
SELECT
  Year_month,
  Week_day,
  COUNT(*) AS wd_count -- 替换原COUNT(Week_day)，避免Week_day字段为NULL时漏统计，结果更贴近全量业务值
FROM 
    temp_view
GROUP BY
    Year_month,
    Week_day;

-- 第二步：按分区取每个Year_month下计数最高的Week_day
WITH ranked_agg AS (
    SELECT
        Year_month,
        Week_day,
        wd_count,
        -- 可根据业务需求替换排名函数：
        -- 1. RANK()：同计数的Week_day会并列排第一，所有达到最高值的记录都会返回
        -- 2. ROW_NUMBER()：强制每个Year_month只返回1条，同计数时按规则取1个
        RANK() OVER (PARTITION BY Year_month ORDER BY wd_count DESC) AS rk
    FROM Year_month_agg
)
SELECT
    Year_month,
    Week_day,
    wd_count AS max_wd_count
FROM ranked_agg
WHERE rk = 1
ORDER BY Year_month ASC;

方案2：自连接匹配法（适配不支持窗口函数的老旧SQL引擎） #

核心逻辑是先算出每个Year_month对应的最高记录数，再和聚合结果做内连接，匹配出对应最高计数的Week_day。

-- 第一步：基础维度聚合逻辑同前
CREATE OR REPLACE TEMPORARY VIEW Year_month_agg
AS
SELECT
  Year_month,
  Week_day,
  COUNT(*) AS wd_count
FROM 
    temp_view
GROUP BY
    Year_month,
    Week_day;

-- 第二步：先计算每个Year_month的最高计数值，再关联匹配对应Week_day
WITH month_max AS (
    SELECT
        Year_month,
        MAX(wd_count) AS max_wd_count
    FROM Year_month_agg
    GROUP BY Year_month
)
SELECT
    a.Year_month,
    a.Week_day,
    m.max_wd_count
FROM Year_month_agg a
INNER JOIN month_max m
    ON a.Year_month = m.Year_month
    AND a.wd_count = m.max_wd_count
ORDER BY a.Year_month ASC;

• 如果业务上要求同计数并列时每个Year_month仅保留1条记录，可将方案1中的RANK()替换为ROW_NUMBER()，若需要指定并列时的优先排序规则，可在窗口函数的ORDER BY子句中追加次级排序字段（比如同计数时按Week_day的自然顺序取靠前的值）
• 原SQL中COUNT (Week_day)的写法会自动过滤Week_day为NULL的记录，如果需要统计全量业务记录数，替换为COUNT(*)的统计结果会更准确

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