BigQuery查询：找出连续2个月未更新数据的用户

针对你的需求，这里提供两种实用的BigQuery SQL方案，分别适用于不同场景：

方案一：检查用户活动记录间的间隔（快速排查） #

这种方法专注于用户已有记录的月份之间是否存在≥2个月的空缺，适合快速定位用户的活动中断情况。

步骤分解 #

1. 去重用户的记录月份：同一用户同一月份的多条记录仅保留一个月份标记
2. 计算相邻记录月份的间隔：用LAG函数获取上一次有记录的月份，计算间隔
3. 筛选存在间隔≥2个月的用户

完整SQL代码 #

WITH user_months AS (
  -- 提取每个用户的唯一记录月份
  SELECT
    ID,
    DATE_TRUNC(Date, MONTH) AS record_month
  FROM
    `your-project.your-dataset.your-table` -- 替换为你的表路径
  GROUP BY
    ID, record_month
),
user_month_gaps AS (
  -- 计算每个用户相邻记录月份的间隔
  SELECT
    ID,
    record_month,
    LAG(record_month) OVER (PARTITION BY ID ORDER BY record_month) AS prev_month,
    DATE_DIFF(record_month, LAG(record_month) OVER (PARTITION BY ID ORDER BY record_month), MONTH) AS month_gap
  FROM
    user_months
)
-- 筛选存在连续2个月及以上空缺的用户
SELECT DISTINCT
  ID
FROM
  user_month_gaps
WHERE
  month_gap >= 2;

方案二：检查全表时间范围内的连续空缺（全面排查） #

如果需要检查整个表的时间跨度内（从表中最早记录月份到最晚记录月份），用户是否存在连续两个月无任何数据的情况，可使用以下方案：

步骤分解 #

1. 生成表覆盖的所有月份：从表中最早到最晚的月份生成连续序列
2. 交叉连接用户与所有月份：确保每个用户对应所有可能的月份
3. 标记用户每月是否有记录：左连接原表数据，标记有/无记录
4. 分组统计连续无记录的月份：用窗口函数分组连续的空缺月份，统计长度≥2的组

完整SQL代码 #

WITH all_months AS (
  -- 生成表中覆盖的所有连续月份
  SELECT
    month
  FROM
    UNNEST(GENERATE_DATE_ARRAY(
      (SELECT DATE_TRUNC(MIN(Date), MONTH) FROM `your-project.your-dataset.your-table`),
      (SELECT DATE_TRUNC(MAX(Date), MONTH) FROM `your-project.your-dataset.your-table`),
      INTERVAL 1 MONTH
    )) AS month
),
user_all_months AS (
  -- 每个用户与所有月份交叉连接
  SELECT
    u.ID,
    am.month
  FROM
    (SELECT DISTINCT ID FROM `your-project.your-dataset.your-table`) u
  CROSS JOIN
    all_months am
),
user_records AS (
  -- 提取用户的唯一记录月份
  SELECT
    ID,
    DATE_TRUNC(Date, MONTH) AS record_month
  FROM
    `your-project.your-dataset.your-table`
  GROUP BY
    ID, record_month
),
user_month_status AS (
  -- 标记用户每月是否有记录
  SELECT
    uam.ID,
    uam.month,
    CASE WHEN ur.record_month IS NOT NULL THEN 1 ELSE 0 END AS has_record
  FROM
    user_all_months uam
  LEFT JOIN
    user_records ur ON uam.ID = ur.ID AND uam.month = ur.record_month
),
user_consecutive_gaps AS (
  -- 分组连续的无记录月份
  SELECT
    ID,
    month,
    has_record,
    SUM(has_record) OVER (PARTITION BY ID ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS gap_group
  FROM
    user_month_status
)
-- 筛选存在连续2个月及以上无记录的用户
SELECT DISTINCT
  ID
FROM
  user_consecutive_gaps
WHERE
  has_record = 0
GROUP BY
  ID, gap_group
HAVING
  COUNT(*) >= 2;

说明 #

• 方案一效率更高，适合大数据量场景，仅关注用户自身活动的中断；
• 方案二更全面，能覆盖表时间范围内所有可能的连续空缺，但计算量相对较大。

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