BigQuery中按15分钟间隔校验时间点数据完整性的SQL查询方法

要实现指定时间点的数据更新校验，核心思路是生成所有需要校验的预期时间点，再与现有数据做左连接，判断每个时间点是否有数据，最终标记缺失状态。以下分两种字段类型给出实现方案：

方案1：当timecolumn是字符串类型（如"00:10"） #

WITH expected_times AS (
  -- 生成当天从00:10开始、每15分钟一个的时间点（格式化为字符串）
  SELECT
    FORMAT_TIMESTAMP("%H:%M", ts) AS expected_time
  FROM
    UNNEST(GENERATE_TIMESTAMP_ARRAY(
      TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY) + INTERVAL 10 MINUTE,
      TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY) + INTERVAL 23 HOUR + INTERVAL 55 MINUTE,
      INTERVAL 15 MINUTE
    )) AS ts
),
current_time AS (
  -- 获取当前时间的时分部分，用于过滤未到的时间点
  SELECT FORMAT_TIMESTAMP("%H:%M", CURRENT_TIMESTAMP()) AS now_time
)
SELECT
  et.expected_time AS time,
  COUNT(t.timecolumn) AS cnt,
  -- 标记数据状态：无数据则提示缺失
  CASE WHEN COUNT(t.timecolumn) = 0 THEN '数据缺失' ELSE '正常' END AS status
FROM expected_times et
CROSS JOIN current_time ct
-- 只校验当前时间之前的预期时间点（符合"及时更新"的校验逻辑）
WHERE et.expected_time <= ct.now_time
-- 左连接保留所有预期时间点，即使表中无对应数据
LEFT JOIN `project.dataset.table` t
  ON et.expected_time = t.timecolumn
GROUP BY et.expected_time
ORDER BY et.expected_time;

方案2：当timecolumn是TIME类型 #

如果你的timecolumn是BigQuery的TIME类型（而非字符串），可以用更直接的TIME生成方式：

WITH expected_times AS (
  -- 生成00:10到23:55、每15分钟一个的TIME类型时间点
  SELECT
    TIME_ADD(TIME "00:10", INTERVAL 15 * n MINUTE) AS expected_time
  FROM
    UNNEST(GENERATE_ARRAY(0, 95)) AS n  -- 0到95共96个时间点，覆盖全天
),
current_time AS (
  SELECT CURRENT_TIME() AS now_time
)
SELECT
  et.expected_time AS time,
  COUNT(t.timecolumn) AS cnt,
  CASE WHEN COUNT(t.timecolumn) = 0 THEN '数据缺失' ELSE '正常' END AS status
FROM expected_times et
CROSS JOIN current_time ct
WHERE et.expected_time <= ct.now_time
LEFT JOIN `project.dataset.table` t
  ON et.expected_time = t.timecolumn
GROUP BY et.expected_time
ORDER BY et.expected_time;

逻辑说明 #

1. 生成预期时间点：通过GENERATE_TIMESTAMP_ARRAY或TIME_ADD生成所有符合规则的时间点，保证覆盖从00:10到23:55的15分钟间隔序列。
2. 过滤未到时间点：通过当前时间过滤，只校验已经过的时间点（比如01:05执行时，只校验00:10、00:25、00:40、00:55）。
3. 左连接校验数据：左连接保留所有预期时间点，即使表中无对应数据，COUNT(t.timecolumn)会返回0，再通过CASE标记缺失状态。
4. 结果输出：返回所有需校验的时间点、数据量、状态，一目了然哪些时间点未及时更新。

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