SQL统计用户有效打卡天数及日均工时计算问题

问题分析 #

原查询的核心错误在于用DATE_DIFF计算固定日期区间的间隔，得到的是整月天数（2023-04-30减2023-04-01为29天），这和用户实际打卡天数完全无关。此外还存在字段笔误（total_timeo）、分组逻辑混乱、条件判断错误（用天数数值和日期区间对比）等问题。

需求对应方案 #

要实现每日工时统计、实际打卡天数、日均工时三个目标，可分两步处理：

• 第一步：按用户和日期分组，计算每位用户每天的工时总和
• 第二步：基于每日统计结果，按用户汇总出总工时、打卡天数、日均工时

修正后的SQL语句 #

-- 第一步：统计每位用户每日有效工时
WITH daily_hours AS (
    SELECT
        name,
        date,
        SUM(logged_time_(h)) AS daily_logged_hours
    FROM `data.table1`
    WHERE date BETWEEN '2023-04-01' AND '2023-04-30'
    GROUP BY name, date
    HAVING daily_logged_hours > 0 -- 过滤工时为0的无效记录
)
-- 第二步：汇总用户的核心统计数据
SELECT
    name,
    COUNT(*) AS actual_punch_days, -- 每一行对应一天有效打卡，直接计数即为实际天数
    SUM(daily_logged_hours) AS total_logged_hours,
    ROUND(SUM(daily_logged_hours)/COUNT(*), 2) AS average_daily_hours -- 保留两位小数优化可读性
FROM daily_hours
GROUP BY name;

关键说明 #

• 用CTE（WITH子句）拆分逻辑，让查询结构更清晰
• actual_punch_days通过COUNT(*)计算，因为子查询中每一行都是用户的一天有效打卡记录
• 日均工时通过总工时除以实际打卡天数得到，用ROUND控制小数位数
• 过滤工时为0的记录，确保统计的是真正有打卡的天数

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