编写SQL查询按周统计每人时长总和（自定义列）

核心思路分为三步：计算单条记录时长、按用户和周分组求和、将行式周数据转为列式结构（行转列），同时补全无数据周的0值。

MySQL 实现示例 #

SELECT
    ID,
    COALESCE(SUM(CASE WHEN WEEK(startDT, 1) = 1 THEN duration ELSE 0 END), 0) AS W01,
    COALESCE(SUM(CASE WHEN WEEK(startDT, 1) = 2 THEN duration ELSE 0 END), 0) AS W02,
    COALESCE(SUM(CASE WHEN WEEK(startDT, 1) = 3 THEN duration ELSE 0 END), 0) AS W03
FROM (
    SELECT
        ID,
        -- 计算单条记录的时长（转成小时，支持小数）
        TIMESTAMPDIFF(MINUTE, startDT, endDT) / 60.0 AS duration
    FROM your_table_name
) AS duration_per_record
GROUP BY ID
ORDER BY ID;

代码说明 #

1. 子查询duration_per_record：通过分钟差除以60，精确计算每条记录的时长（比如3.5小时这类非整数情况）。
2. 条件聚合：用CASE WHEN筛选对应周的时长并求和，COALESCE确保无数据的周显示为0而非NULL。
3. 周数计算：WEEK(startDT, 1)指定以周一为一周起始，周数从1开始，匹配示例中2024年1月1日为第1周的逻辑。

其他数据库适配 #

PostgreSQL #

SELECT
    ID,
    COALESCE(SUM(CASE WHEN EXTRACT(WEEK FROM startDT) = 1 THEN duration ELSE 0 END), 0) AS W01,
    COALESCE(SUM(CASE WHEN EXTRACT(WEEK FROM startDT) = 2 THEN duration ELSE 0 END), 0) AS W02,
    COALESCE(SUM(CASE WHEN EXTRACT(WEEK FROM startDT) = 3 THEN duration ELSE 0 END), 0) AS W03
FROM (
    SELECT
        ID,
        EXTRACT(EPOCH FROM (endDT - startDT)) / 3600 AS duration
    FROM your_table_name
) AS duration_per_record
GROUP BY ID
ORDER BY ID;

SQL Server #

SELECT
    ID,
    COALESCE(SUM(CASE WHEN DATEPART(WEEK, startDT) = 1 THEN duration ELSE 0 END), 0) AS W01,
    COALESCE(SUM(CASE WHEN DATEPART(WEEK, startDT) = 2 THEN duration ELSE 0 END), 0) AS W02,
    COALESCE(SUM(CASE WHEN DATEPART(WEEK, startDT) = 3 THEN duration ELSE 0 END), 0) AS W03
FROM (
    SELECT
        ID,
        DATEDIFF(MINUTE, startDT, endDT) / 60.0 AS duration
    FROM your_table_name
) AS duration_per_record
GROUP BY ID
ORDER BY ID;

注意事项 #

• 替换your_table_name为实际表名。
• 若需统计更多周数，复制对应CASE WHEN行并修改周数即可。
• 周数计算逻辑可能因数据库地区设置不同调整（比如是否以周日为一周起始），需根据实际需求适配。

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