如何在BigQuery中生成含entry_switches列的员工扩展表

需求说明 #

基于现有表my-project.my_dataset.employee_entries创建新表employees_extended：

• 保留原表employee_id、entry_datetime、entry_flag、salary所有列
• 新增entry_switches列：按employee_id分区，按entry_datetime chronological排序后，统计当前行过去2小时内entry_flag的连续切换次数（连续相同值的变更不计入）

原表结构与测试数据 #

CREATE OR REPLACE TABLE `my-project.my_dataset.employee_entries`  (
  employee_id STRING,
  entry_datetime DATETIME,
  entry_flag STRING,
  salary FLOAT64
);

INSERT INTO `my-project.my_dataset.employee_entries`  
(employee_id, entry_datetime, entry_flag, salary) VALUES
('1234', '2023-07-15 08:00:00', '1', 50000),
('1234', '2023-07-15 09:00:00', '2', 50000),
('1234', '2023-07-15 10:00:00', '0', 50000),
('1234', '2023-07-15 11:00:00', '0', 50000),
('1234', '2023-07-15 12:00:00', '1', 50000),
('1234', '2023-07-15 13:00:00', '3', 50000),
('5678', '2023-07-15 08:30:00', '2', 75000),
('5678', '2023-07-15 09:30:00', '2', 75000),
('5678', '2023-07-15 10:30:00', '3', 75000),
('5678', '2023-07-15 11:30:00', '4', 75000),
('5678', '2023-07-15 12:30:00', '4', 75000),
('5678', '2023-07-15 13:30:00', '0', 75000);

解决方案SQL #

原SQL的核心问题是未限制2小时的时间窗口，且切换次数统计逻辑需要针对时间窗口内的记录调整。以下是完整实现代码：

WITH entry_with_prev AS (
  -- 为每条记录获取上一条的entry_flag，并标记是否发生切换
  SELECT
    *,
    LAG(entry_flag) OVER (PARTITION BY employee_id ORDER BY entry_datetime) AS prev_flag,
    -- 标记当前行与上一行是否发生切换（首次记录无切换）
    CASE
      WHEN LAG(entry_flag) OVER (PARTITION BY employee_id ORDER BY entry_datetime) IS NULL THEN 0
      WHEN entry_flag != LAG(entry_flag) OVER (PARTITION BY employee_id ORDER BY entry_datetime) THEN 1
      ELSE 0
    END AS is_switch
  FROM `my-project.my_dataset.employee_entries`
),
window_switch_counts AS (
  -- 计算每个时间点过去2小时内的切换次数
  SELECT
    *,
    SUM(is_switch) OVER (
      PARTITION BY employee_id
      ORDER BY entry_datetime
      RANGE BETWEEN INTERVAL 2 HOUR PRECEDING AND CURRENT ROW
    ) AS entry_switches
  FROM entry_with_prev
)
-- 创建目标表
CREATE OR REPLACE TABLE `my-project.my_dataset.employees_extended` AS
SELECT
  employee_id,
  entry_datetime,
  entry_flag,
  salary,
  entry_switches
FROM window_switch_counts
ORDER BY employee_id, entry_datetime;

逻辑说明 #

1. entry_with_prev CTE：通过LAG()函数获取每条记录的上一条entry_flag，并判断当前行与上一行是否发生切换（生成is_switch标记，1表示切换，0表示未切换）。
2. window_switch_counts CTE：使用RANGE BETWEEN INTERVAL 2 HOUR PRECEDING AND CURRENT ROW定义2小时的滑动时间窗口，对窗口内的is_switch求和，得到当前行的entry_switches值。
3. 创建目标表：筛选所需字段，生成最终的employees_extended表。

验证结果示例 #

以员工1234为例：

• 2023-07-15 08:00:00：无前置记录，entry_switches=0
• 2023-07-15 09:00:00：过去2小时内仅一次切换（1→2），entry_switches=1
• 2023-07-15 10:00:00：过去2小时内有两次切换（1→2，2→0），entry_switches=2
• 2023-07-15 11:00:00：过去2小时内，08:00的记录超出窗口，窗口内切换为2→0，entry_switches=1
• 2023-07-15 12:00:00：过去2小时内，09:00的记录超出窗口，窗口内切换为0→1，entry_switches=1
• 2023-07-15 13:00:00：过去2小时内切换为0→1、1→3，entry_switches=2

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