如何用SQL查询每周各工作日访问量最高的Top3时段？

问题背景 #

现有表TableA包含Visits（访问量）、Day（星期）、Hour（时段）三个字段，样例数据如下：

Visits   Day       Hour
83       Thursday  12AM
75       Friday    1AM
124      Tuesday   2AM
0        Sunday    3AM
0        Thursday  4AM
26       Friday    5AM
0        Monday    6AM
7        Friday    7AM
55       Friday    8AM
21       Wednesday 9AM
154      Friday    10AM
425      Thursday  11AM
270      Tuesday   12PM
373      Friday    1PM
0        Friday    2PM
0        Tuesday   3PM
0        Friday    4PM
1774     Tuesday   5PM
509      Friday    6PM
519      Saturday  7PM

需求：按星期日→星期一的顺序，展示每周各工作日平均访问量最高的前3个时段。

你之前尝试的SQL语句存在两个问题：TOP 3会直接取全局前3条，而非每个工作日各自的前3；外层的MAX(Visits)完全多余，子查询已经算出了每个时段的平均访问量。原语句如下：

SELECT TOP 3
    [Day],
    [Hour],
    MAX(Visits)
FROM 
    (  SELECT 
    [Day],
    [Hour],
    AVG(Visits) AS Visits
  FROM TableA
  GROUP BY [Day], [Hour]
    ) z
GROUP BY [Day],[Hour]

正确SQL实现 #

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

利用ROW_NUMBER()窗口函数按工作日分组，对每个工作日内的平均访问量降序排名，再筛选排名≤3的记录，最后按指定星期顺序排序。

WITH AvgVisits AS (
    -- 先计算每个工作日每个时段的平均访问量
    SELECT 
        [Day],
        [Hour],
        AVG(Visits) AS Avg_Visits
    FROM TableA
    GROUP BY [Day], [Hour]
),
RankedVisits AS (
    -- 对每个工作日的时段按平均访问量降序排名
    SELECT 
        [Day],
        [Hour],
        Avg_Visits,
        ROW_NUMBER() OVER (
            PARTITION BY [Day] 
            ORDER BY Avg_Visits DESC
        ) AS RankNum
    FROM AvgVisits
)
-- 筛选每个工作日排名前3的时段，并按星期日到星期一的顺序排序
SELECT 
    [Day],
    [Hour],
    ROUND(Avg_Visits, 2) AS Visits -- 保留两位小数，可按需调整
FROM RankedVisits
WHERE RankNum <= 3
ORDER BY 
    -- 自定义星期排序顺序：星期日→星期一
    CASE [Day]
        WHEN 'Sunday' THEN 1
        WHEN 'Monday' THEN 2
        WHEN 'Tuesday' THEN 3
        WHEN 'Wednesday' THEN 4
        WHEN 'Thursday' THEN 5
        WHEN 'Friday' THEN 6
        WHEN 'Saturday' THEN 7
    END,
    RankNum;

补充说明

• 如果需要让相同平均访问量的时段并列排名（比如两个时段都是第2名，下一个是第4名），可以把ROW_NUMBER()换成RANK()；如果要让并列的时段共享排名（两个第2名后下一个是第3名），则用DENSE_RANK()。

方法2：SQL Server旧版本兼容写法（无窗口函数） #

如果数据库不支持窗口函数，可用关联子查询实现：

SELECT 
    a.[Day],
    a.[Hour],
    ROUND(a.Avg_Visits, 2) AS Visits
FROM (
    SELECT 
        [Day],
        [Hour],
        AVG(Visits) AS Avg_Visits
    FROM TableA
    GROUP BY [Day], [Hour]
) a
WHERE (
    SELECT COUNT(*)
    FROM (
        SELECT 
            [Day],
            AVG(Visits) AS Avg_Visits
        FROM TableA
        GROUP BY [Day], [Hour]
    ) b
    WHERE b.[Day] = a.[Day] AND b.Avg_Visits >= a.Avg_Visits
) <= 3
ORDER BY 
    CASE [Day]
        WHEN 'Sunday' THEN 1
        WHEN 'Monday' THEN 2
        WHEN 'Tuesday' THEN 3
        WHEN 'Wednesday' THEN 4
        WHEN 'Thursday' THEN 5
        WHEN 'Friday' THEN 6
        WHEN 'Saturday' THEN 7
    END,
    a.Avg_Visits DESC;

补充说明

通过关联子查询统计当前时段在同工作日内平均访问量大于等于它的记录数，若数量≤3，就说明该时段是当前工作日的Top3。

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