如何编写高效SQL查询员工每周各天最频繁的到岗起始时间

前提假设 #

假设你的员工到岗数据表名为employee_attendance，核心字段如下：

• emp_id：员工ID
• attend_date：到岗日期
• start_time：到岗起始时间

最高效SQL实现（支持MySQL8.0+/PostgreSQL/SQL Server等支持窗口函数的数据库） #

该方案仅需一次全表扫描（加索引后可跳过全表扫描），性能远高于子查询关联写法：

WITH attend_freq AS (
    SELECT
        -- 提取周几标识，可根据数据库类型替换对应函数：
        -- MySQL：DAYOFWEEK(attend_date) 返回1=周日、2=周一...7=周六；WEEKDAY(attend_date)返回0=周一...6=周日
        -- PostgreSQL：EXTRACT(DOW FROM attend_date) 返回0=周日...6=周六
        -- SQL Server：DATEPART(WEEKDAY, attend_date) 返回1=周日...7=周六
        DAYOFWEEK(attend_date) AS week_day_num,
        start_time,
        COUNT(*) AS occur_times
    FROM employee_attendance
    -- 可按需添加时间过滤条件缩小扫描范围，进一步提升性能
    -- WHERE attend_date >= '2024-01-01'
    GROUP BY week_day_num, start_time
),
rank_result AS (
    SELECT
        week_day_num,
        start_time,
        occur_times,
        -- 同周几内按出现频次倒序排名，RANK()会保留并列结果，如需去并列可换成ROW_NUMBER()
        RANK() OVER(PARTITION BY week_day_num ORDER BY occur_times DESC) AS freq_rank
    FROM attend_freq
)
SELECT
    CASE week_day_num
        WHEN 1 THEN '周日'
        WHEN 2 THEN '周一'
        WHEN 3 THEN '周二'
        WHEN 4 THEN '周三'
        WHEN 5 THEN '周四'
        WHEN 6 THEN '周五'
        WHEN 7 THEN '周六'
    END AS 星期,
    start_time AS 最常见到岗起始时间,
    occur_times AS 出现次数
FROM rank_result
WHERE freq_rank = 1
ORDER BY week_day_num;

性能优化建议 #

• 给attend_date、start_time建立联合索引，可避免全表扫描，千万级数据量下也可以秒出结果
• 如无特殊需求，尽量添加时间范围过滤条件，减少扫描的数据量

老版本数据库兼容写法（不支持窗口函数/CTE） #

SELECT
    CASE t1.week_day_num
        WHEN 1 THEN '周日'
        WHEN 2 THEN '周一'
        WHEN 3 THEN '周二'
        WHEN 4 THEN '周三'
        WHEN 5 THEN '周四'
        WHEN 6 THEN '周五'
        WHEN 7 THEN '周六'
    END AS 星期,
    t1.start_time AS 最常见到岗起始时间,
    t1.occur_times AS 出现次数
FROM (
    SELECT
        DAYOFWEEK(attend_date) AS week_day_num,
        start_time,
        COUNT(*) AS occur_times
    FROM employee_attendance
    GROUP BY week_day_num, start_time
) t1
WHERE t1.occur_times = (
    SELECT MAX(occur_times)
    FROM (
        SELECT
            DAYOFWEEK(attend_date) AS week_day_num,
            COUNT(*) AS occur_times
        FROM employee_attendance
        GROUP BY week_day_num, start_time
    ) t2
    WHERE t2.week_day_num = t1.week_day_num
)
ORDER BY t1.week_day_num;

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