如何从Timestamp提取周数据，查询各地点年度最高周工时员工（SQL 8.0）

实现步骤 #

1. 出入记录配对，计算单次在岗时长 #

Gate_Logs表的每条In记录对应后续最近的一条Out记录，用LEAD()窗口函数就能快速匹配同员工的下一条打卡记录：

• 按Employee ID分组，按Timestamp升序排序，取每条记录的下一条时间戳作为出门时间
• 过滤掉状态为Out的首条记录、只有In没有对应Out的无效记录

2. 提取周维度，统计单周累计工时 #

MySQL 8.0自带YEARWEEK()函数可以直接提取时间所属的周：

• 语法为YEARWEEK(日期, 起始日规则)，如果需要周一是一周的第一天，第二个参数填1，周日为第一天填0，自动区分跨年周
• 按Employee ID、YEARWEEK(Timestamp)分组，求和单次在岗时长得到单周总工时，同时过滤Timestamp在过去一年的记录

3. 关联员工表，按地点取最高工时员工 #

用RANK()窗口函数按地点分组排序，取排名第一的记录即可，并列最高会同时返回。

完整代码示例 #

WITH 
-- 第一步：配对出入记录
log_pairs AS (
    SELECT 
        `Employee ID`,
        `Timestamp` AS in_time,
        LEAD(`Timestamp`) OVER (PARTITION BY `Employee ID` ORDER BY `Timestamp`) AS out_time,
        Status AS in_status
    FROM Gate_Logs
    -- 先过滤过去一年的记录，减少计算量
    WHERE `Timestamp` >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
),
-- 第二步：计算单次在岗时长，按周聚合
week_work_hours AS (
    SELECT 
        `Employee ID`,
        -- 提取周数，这里用周一作为周起始，可自行调整第二个参数
        YEARWEEK(in_time, 1) AS work_week,
        -- 时长按小时统计，保留1位小数，可自行调整精度
        ROUND(SUM(TIMESTAMPDIFF(MINUTE, in_time, out_time)/60), 1) AS total_week_hours
    FROM log_pairs
    -- 过滤有效的出入配对：当前是In状态，下一条是Out状态，且出入时间差不为空
    WHERE in_status = 'In' AND out_time IS NOT NULL
    GROUP BY `Employee ID`, work_week
),
-- 第三步：关联员工表，按地点排名
location_rank AS (
    SELECT 
        e.*,
        w.work_week,
        w.total_week_hours,
        -- 按地点分组，周工时倒序排名
        RANK() OVER (PARTITION BY e.`Reporting Location` ORDER BY w.total_week_hours DESC) AS rk
    FROM week_work_hours w
    LEFT JOIN Employee e ON w.`Employee ID` = e.`Employee ID`
)
-- 取每个地点排名第一的记录
SELECT * 
FROM location_rank 
WHERE rk = 1;

可选调整说明 #

• 如果要排除周工时异常值（比如单日打卡超过24小时的错误记录），可以在week_work_hours的聚合后加HAVING total_week_hours <= 168过滤
• 如果不需要保留并列最高，把RANK()换成ROW_NUMBER()即可
• 周起始规则可以根据业务需求调整YEARWEEK的第二个参数

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