BigQuery按周统计在职与离职员工数需求及问题

你的核心问题是没构建出覆盖所有统计周期的周序列，只拿员工的雇佣周作为统计周，没法追踪员工在后续周的在职状态。下面是完整的实现步骤和SQL：

步骤说明 #

1. 日期类型转换：先把字符串格式的hire_date和term_date转成DATE类型，用DATE_TRUNC按周日起始的周做截断（WEEK(SUNDAY)参数）。
2. 生成统计周序列：从最早的雇佣周开始，到最晚的离职周（如果没有离职数据就用当前周），生成每周一行的时间序列，确保每个统计周期都被覆盖。
3. 关联员工数据：判断每个员工在哪些周处于在职状态（雇佣周 ≤ 统计周，且离职周 > 统计周 或 未离职）；同时标记员工的离职周，用于统计当周离职人数。
4. 聚合统计：按周分组，计算在职员工数和当周离职员工数。

完整SQL代码 #

WITH converted_dates AS (
  -- 转换日期格式，处理null的离职日期为当前日期（方便后续判断在职状态）
  SELECT
    department,
    job_specialist,
    score,
    cat_1,
    DATE(hire_date) AS hire_date,
    -- 未离职的员工，term_date设为当前日期（不会影响在职判断，且避免null）
    IFNULL(DATE(term_date), CURRENT_DATE()) AS term_date
  FROM `course_dataset.employee_roster`
),
date_range AS (
  -- 生成需要统计的所有周（从最早雇佣周到最晚离职周）
  SELECT
    DATE_TRUNC(week_date, WEEK(SUNDAY)) AS stat_week
  FROM UNNEST(
    GENERATE_DATE_ARRAY(
      (SELECT MIN(DATE_TRUNC(hire_date, WEEK(SUNDAY))) FROM converted_dates),
      (SELECT MAX(DATE_TRUNC(term_date, WEEK(SUNDAY))) FROM converted_dates),
      INTERVAL 1 WEEK
    )
  ) AS week_date
),
employee_week_status AS (
  -- 关联员工和统计周，标记在职状态和是否当周离职
  SELECT
    dr.stat_week,
    cd.department,
    cd.job_specialist,
    cd.score,
    cd.cat_1,
    -- 判断是否在职：统计周在雇佣周之后，且在离职周之前
    IF(
      dr.stat_week >= DATE_TRUNC(cd.hire_date, WEEK(SUNDAY))
      AND dr.stat_week < DATE_TRUNC(cd.term_date, WEEK(SUNDAY)),
      1,
      0
    ) AS is_active,
    -- 判断是否当周离职：统计周等于离职周，且真实离职日期不是当前日期（排除未离职的）
    IF(
      dr.stat_week = DATE_TRUNC(cd.term_date, WEEK(SUNDAY))
      AND cd.term_date != CURRENT_DATE(),
      1,
      0
    ) AS is_terminated
  FROM date_range dr
  CROSS JOIN converted_dates cd
)
-- 按周聚合统计
SELECT
  stat_week,
  department,
  job_specialist,
  score,
  cat_1,
  SUM(is_active) AS active_employee_count,
  SUM(is_terminated) AS terminated_employee_count
FROM employee_week_status
GROUP BY stat_week, department, job_specialist, score, cat_1
ORDER BY stat_week ASC;

原代码问题说明 #

• 原代码里Most_Recent_Hire_Date和this_week是未定义字段，直接运行会报错；
• 用hire_date作为统计周的思路错误，员工在职是持续状态，需要覆盖从雇佣到离职的所有周；
• GROUP BY包含hire_date、term_date这类明细字段，无法实现按周聚合统计的目的。

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