如何在BigQuery中按日统计总记录及time+id冲突记录数

原始数据 #

需求 #

生成包含以下列的结果表：

• date：截断后的UTC日期
• records：当日总记录数
• records_conflicting_time_id：当日time与id组合不唯一的记录数（同一time+id下的所有重复记录都计入）

解决方案 #

通过窗口函数标记重复组合，再按日期聚合统计：

WITH tagged_data AS (
  SELECT
    DATE(time) AS date,
    -- 计算每条记录所属time+id组合的总条数
    COUNT(*) OVER (PARTITION BY time, id) AS group_count,
    1 AS record_count
  FROM
    `your_project.your_dataset.your_table` -- 替换为你的表路径
)
SELECT
  date,
  SUM(record_count) AS records,
  -- 统计group_count>1的记录总数
  SUM(CASE WHEN group_count > 1 THEN record_count ELSE 0 END) AS records_conflicting_time_id
FROM
  tagged_data
GROUP BY
  date
ORDER BY
  date;

逻辑说明 #

1. CTE标记阶段：

   • 用DATE(time)将UTC时间截断为日期；
   • 窗口函数COUNT(*) OVER (PARTITION BY time, id)统计每个time+id组合的总记录数，标记为group_count；
   • 用1 AS record_count简化后续总记录数的求和计算。

2. 聚合统计阶段：

   • 按date分组，SUM(record_count)得到当日总记录数；
   • 通过CASE WHEN筛选出group_count>1的记录，求和得到当日冲突记录总数。

执行查询后将得到预期输出：

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