BigQuery按员工入职日期统计每周工时的结果异常排查

导致周工时统计为0的核心原因 #

1. CTE定义后未使用：明明写了new_employees用来获取每个员工最新的入职记录，但最终查询却直接关联原始的employee_created表，导致可能拿到员工旧的入职记录甚至多条重复记录，分组后统计逻辑失效。
2. 日期处理错误：
   • 原created_on的转换方式逻辑混乱，导致DATE(created_on)的日期值与实际入职日期不符，和online_hours的date字段差值不在目标范围内。
   • 若DATE_DIFF参数顺序错误（比如把入职日期放在前面），会得到负数结果，自然不满足BETWEEN的条件，总和为0。
3. 语法错误：
   • SUM(hours_online) AS total_hours末尾缺少逗号，导致后续CASE语句无法被识别。
   • CASE语句前后的**是无效语法，会干扰查询执行。
4. 字段名不匹配：CTEnew_employees里误写date_updated，但原表字段是updated_at，导致CTE逻辑失效。

修正后的SQL #

WITH online_hour AS (
  SELECT 
    employee_id,
    date,
    -- 正确转换时间戳并计算新加坡时区下的工时
    DATE_DIFF(
      DATETIME(TIMESTAMP_SECONDS(out_time), 'Asia/Singapore'),
      DATETIME(TIMESTAMP_SECONDS(in_time), 'Asia/Singapore'),
      HOUR
    ) AS hours_online
  FROM online_hours
),
new_employees AS (
  SELECT
    employee_id,
    -- 直接将入职时间转换为新加坡时区的日期
    DATE(created_on, 'Asia/Singapore') AS created_on,
    ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY updated_at DESC) AS row_num
  FROM employee_created
)
SELECT 
  oh.employee_id,
  ne.created_on,
  SUM(oh.hours_online) AS total_hours,
  -- 统计入职后第1-7天的工时
  SUM(CASE 
        WHEN DATE_DIFF(oh.date, ne.created_on, DAY) BETWEEN 1 AND 7 
        THEN oh.hours_online 
        ELSE 0 
      END) AS total_hours_first_week,
  -- 统计入职后第8-14天的工时
  SUM(CASE 
        WHEN DATE_DIFF(oh.date, ne.created_on, DAY) BETWEEN 8 AND 14 
        THEN oh.hours_online 
        ELSE 0 
      END) AS total_hours_second_week
FROM online_hour oh
INNER JOIN new_employees ne
  ON oh.employee_id = ne.employee_id
-- 只保留每个员工的最新入职记录
WHERE ne.row_num = 1
GROUP BY oh.employee_id, ne.created_on

关键修正说明 #

1. 启用正确的员工数据：关联new_employees并通过row_num=1过滤每个员工的最新入职记录，避免重复数据干扰。
2. 修复时区转换：用DATE(created_on, 'Asia/Singapore')直接转换入职日期，确保和online_hours的date时区一致。
3. 修正语法问题：补上缺失的逗号、删除无效的**符号，保证查询正常执行。
4. 确认日期差逻辑：DATE_DIFF(oh.date, ne.created_on, DAY)计算工作日期与入职日期的间隔天数，确保结果为正且落在目标范围内。

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