如何在BigQuery中计算活动间隔时间Time_since_activity_min列？

云上老周

2026-7-26

计算BigQuery中Time_since_activity_min列的实现方案

是否可以使用LAG函数？

不能直接用LAG函数。LAG仅能获取当前行的前一行数据，但这里需要匹配最近一次Activity>0的行，该行可能与当前行间隔多行，因此需要借助分组窗口函数来实现。

实现SQL代码

假设你的表名为your_table，Time列为TIME类型，以下是完整的实现代码：

WITH grouped_data AS (
  SELECT
    Name,
    Activity,
    Time,
    -- 生成分组ID：每个Activity>0的行为分组起点，后续0行归属同一分组
    SUM(CASE WHEN Activity > 0 THEN 1 ELSE 0 END) OVER (
      PARTITION BY Name 
      ORDER BY Time 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS group_id
  FROM your_table
)
SELECT
  Name,
  Activity,
  Time,
  CASE
    WHEN Activity > 0 THEN 0
    ELSE TIME_DIFF(Time, FIRST_VALUE(Time) OVER (
      PARTITION BY Name, group_id 
      ORDER BY Time
    ), MINUTE)
  END AS Time_since_activity_min
FROM grouped_data
ORDER BY Name, Time;

代码逻辑说明

分组标记：通过SUM(...) OVER (...)窗口函数，为每个用户（按Name分区）的行按时间排序，每当遇到Activity>0的行就增加分组ID，这样所有连续的Activity=0行会被归到最近一次Activity>0行所在的分组。
获取最近活动时间：在每个分组内，用FIRST_VALUE(Time)获取分组的第一个时间（即最近一次Activity>0的时间）。
计算时间差：用TIME_DIFF函数计算当前时间与最近活动时间的分钟差，Activity>0的行直接返回0。