BigQuery统计数据表连续无摄入天数 数据恢复后自动移出清单

核心思路是不需要单独维护无数据天数的累计计数表，直接基于原始摄入日志实时计算即可，逻辑分三步：

• 按table_id、dataset_ID维度分组，提取每张表最近一次成功写入数据的日期
• 过滤掉最近一次摄入日期等于当前日期的表，这类表当天已经有数据写入，自动从统计清单中排除
• 对剩余的表，直接计算当前日期和最近一次摄入日期的差值，就是连续无数据摄入的天数。新出现无数据的表会自动被纳入统计，已有无数据记录的表天数会自动按日期差累加，完全匹配需求。

WITH table_latest_ingestion AS (
    SELECT
        table_id,
        dataset_ID,
        MAX(DATE(ingestion_time)) AS last_ingest_date
    FROM `list_of_tables`
    WHERE ingestion_time IS NOT NULL
    GROUP BY table_id, dataset_ID
)
SELECT
    table_id AS `Table ID`,
    DATE_DIFF(CURRENT_DATE(), last_ingest_date, DAY) AS `Days without data ingested`
FROM table_latest_ingestion
WHERE last_ingest_date < CURRENT_DATE()
ORDER BY days_without_ingestion DESC;

对照给出的示例场景：

1. 首次统计时，Product表最近摄入日期距当日3天、User表距当日4天，输出结果和第一份示例表完全一致
2. 次日运行查询时：
   • Product表当日有新数据写入，last_ingest_date更新为当前日期，被WHERE条件过滤，自动从结果中移除
   • User表无新写入，last_ingest_date不变，和当前日期的差值自动变为5天
   • Building表前一日有写入、当日无写入，last_ingest_date为前一日，计算得到无数据天数为1
     最终输出和第二份示例表完全匹配。

注意：如果ingestion_time字段本身已经是DATE类型，可以去掉DATE()转换函数直接取MAX值即可。

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