如何在BigQuery中按ID统计指定日期前的空值月份数

问题背景 #

给定如下结构的数据集（示例）：

需要编写BigQuery查询，按每个ID统计指定日期前的空值月份数量。示例中统一截止日期为2023-08-01，后续需支持每个ID使用不同的截止日期。

期望输出：

解决方案 #

方案1：统一截止日期 #

如果所有ID使用同一个截止日期，直接过滤日期范围后统计空值：

WITH sample_data AS (
  -- 替换为你的实际表名
  SELECT * FROM UNNEST([
    STRUCT(111 AS ID, DATE('2023-01-01') AS month, NULL AS value),
    STRUCT(111 AS ID, DATE('2023-02-01') AS month, NULL AS value),
    STRUCT(111 AS ID, DATE('2023-03-01') AS month, NULL AS value),
    STRUCT(111 AS ID, DATE('2023-04-01') AS month, 100 AS value),
    STRUCT(111 AS ID, DATE('2023-05-01') AS month, 200 AS value),
    STRUCT(111 AS ID, DATE('2023-06-01') AS month, 300 AS value),
    STRUCT(111 AS ID, DATE('2023-07-01') AS month, 400 AS value),
    STRUCT(111 AS ID, DATE('2023-08-01') AS month, 500 AS value),
    STRUCT(222 AS ID, DATE('2023-01-01') AS month, NULL AS value),
    STRUCT(222 AS ID, DATE('2023-02-01') AS month, NULL AS value),
    STRUCT(222 AS ID, DATE('2023-03-01') AS month, NULL AS value),
    STRUCT(222 AS ID, DATE('2023-04-01') AS month, NULL AS value),
    STRUCT(222 AS ID, DATE('2023-05-01') AS month, NULL AS value),
    STRUCT(222 AS ID, DATE('2023-06-01') AS month, 150 AS value),
    STRUCT(222 AS ID, DATE('2023-07-01') AS month, 250 AS value),
    STRUCT(222 AS ID, DATE('2023-08-01') AS month, 350 AS value)
  ])
)
SELECT
  ID,
  COUNTIF(value IS NULL AND month < DATE('2023-08-01')) AS null_months_before_target
FROM sample_data
GROUP BY ID
ORDER BY ID;

方案2：支持每个ID不同截止日期 #

如果每个ID有独立的截止日期，先准备包含ID和对应截止日期的配置表，再关联原数据统计：

WITH sample_data AS (
  -- 替换为你的实际数据表
  SELECT * FROM UNNEST([
    STRUCT(111 AS ID, DATE('2023-01-01') AS month, NULL AS value),
    STRUCT(111 AS ID, DATE('2023-02-01') AS month, NULL AS value),
    STRUCT(111 AS ID, DATE('2023-03-01') AS month, NULL AS value),
    STRUCT(111 AS ID, DATE('2023-04-01') AS month, 100 AS value),
    STRUCT(111 AS ID, DATE('2023-05-01') AS month, 200 AS value),
    STRUCT(111 AS ID, DATE('2023-06-01') AS month, 300 AS value),
    STRUCT(111 AS ID, DATE('2023-07-01') AS month, 400 AS value),
    STRUCT(111 AS ID, DATE('2023-08-01') AS month, 500 AS value),
    STRUCT(222 AS ID, DATE('2023-01-01') AS month, NULL AS value),
    STRUCT(222 AS ID, DATE('2023-02-01') AS month, NULL AS value),
    STRUCT(222 AS ID, DATE('2023-03-01') AS month, NULL AS value),
    STRUCT(222 AS ID, DATE('2023-04-01') AS month, NULL AS value),
    STRUCT(222 AS ID, DATE('2023-05-01') AS month, NULL AS value),
    STRUCT(222 AS ID, DATE('2023-06-01') AS month, 150 AS value),
    STRUCT(222 AS ID, DATE('2023-07-01') AS month, 250 AS value),
    STRUCT(222 AS ID, DATE('2023-08-01') AS month, 350 AS value)
  ]),
  id_target_dates AS (
    -- 替换为你的实际截止日期配置表，每个ID对应一个截止日期
    SELECT * FROM UNNEST([
      STRUCT(111 AS ID, DATE('2023-08-01') AS target_date),
      STRUCT(222 AS ID, DATE('2023-07-01') AS target_date) -- 示例：ID222的截止日期改为2023-07-01
    ])
)
SELECT
  sd.ID,
  COUNTIF(sd.value IS NULL AND sd.month < itd.target_date) AS null_months_before_target
FROM sample_data sd
JOIN id_target_dates itd ON sd.ID = itd.ID
GROUP BY sd.ID
ORDER BY sd.ID;

关键说明 #

• 使用COUNTIF()函数直接筛选满足条件的空值记录，比先过滤再COUNT()更简洁。
• 针对不同ID的截止日期，通过关联配置表的方式实现，扩展性强，后续只需更新id_target_dates部分即可。
• 如果你的month字段是字符串类型，需要先用DATE(month)转换为日期类型再进行比较。

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