把 Chrome 更新日志喂给 BigQuery 后踩到的分区坑

线上有一批门店终端，Chrome 版本从 118 到 131 混着跑，大约 4200 台。我需要回答一个很朴素的问题：哪些店还在 120 以下的版本上，最近七天有没有上报过崩溃。数据源是终端每天回传的一份 JSON 快照，单日约 180MB，三十天就是 5GB 出头，本地 jq 加 sqlite 跑到第三天就开始难受，于是挪到了 BigQuery。

建表时我用的是最常见的那套写法：

CREATE TABLE ops.chrome_devices (
  report_date DATE,
  store_id STRING,
  device_id STRING,
  chrome_major INT64,
  crashed BOOL
)
PARTITION BY report_date
CLUSTER BY store_id;

装载用的是 bq load 加 –source_format=NEWLINE_DELIMITED_JSON，字段名和 JSON 对齐。第一版查询直接按日期过滤，扫描量 1.9GB，还能接受。问题出在我为了省事写了一版不带日期条件的「最新状态」视图，想用窗口函数取每台设备最后一条记录：

SELECT device_id, chrome_major
FROM (
  SELECT device_id, chrome_major,
         ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY report_date DESC) rn
  FROM ops.chrome_devices
) WHERE rn = 1;

这一下把整个 5GB 全扫了，而且每天跑一次。分区表不等于分区裁剪，查询里没有对分区列的字面量或参数约束，BigQuery 只能老老实实读全部分区。我后来改成先算出一个截止日期，再把它作为参数塞进去，扫描量掉到 200MB 左右：

DECLARE cutoff DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY);
SELECT device_id, chrome_major
FROM ops.chrome_devices
WHERE report_date >= cutoff
QUALIFY ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY report_date DESC) = 1;

QUALIFY 是这里比较顺手的地方，省掉一层子查询，代价是它只对窗口结果做过滤，前面的 WHERE 仍然必须先收窄分区，否则一样全扫。

另一个坑在 CLUSTER BY。我一开始拿 store_id 做聚簇，以为按门店查会快，结果最常用的过滤其实是 chrome_major，聚簇列和查询模式不匹配，收益基本为零。改成 CLUSTER BY chrome_major, store_id 之后，版本筛选的扫描块明显少了。但要注意，聚簇只影响块级裁剪，不改变分区裁剪的逻辑，两者是叠加而不是替代。

失效的场景也很具体：如果某天装载任务失败，分区里缺了那一天，窗口函数取「最新」会悄悄回退到前一天，不会报错，报表上看不出异常。我现在额外跑一条计数校验，比对预期分区数和实际分区数。另外流式插入进来的记录在分区边界上可能落到 UNPARTITIONED，用批量 load 就没这个问题，但那意味着要接受分钟级的延迟。

你们在用 BigQuery 做这种「取每台设备最新状态」的查询时，是坚持用分区加 QUALIFY，还是干脆落到一张每日全量快照表里？后者存储翻倍，但查询侧几乎不用想裁剪的事。