如何在BigQuery中高效获取指定ID记录的最新重复条目？

表信息 #

• 所有更新以新条目形式插入，表按createdOn字段的月份分区
• fieldsData为通用字段，可包含任意数量键值对
• 每日新增约10000条记录

示例表数据 #

[
  {
    "id":"221212",
    "fieldsData": [
      {
        "key": "someDate",
        "value": "12-12-2022"
      },
      {
        "key": "someString",
        "value": "ABCDEF"
      }
    ],
    "name": "Sample data 1",
    "createdOn":"12-11-2022",
    "insertedDate": "14-11-2022",
    "updatedOn": "14-11-2022"
  },
   {
    "id":"221212",
    "fieldsData": [
      {
        "key": "someDate",
        "value": "12-12-2022"
      },
      {
        "key": "someString",
        "value": "ABCDEF"
      },
      {
        "key": "someMoreString",
        "value": "12qwwe122"
      }
    ],
    "name": "Sample data 1",
    "createdOn":"12-11-2022",
    "insertedDate": "15-11-2022",
    "updatedOn": "15-11-2022"
  }
]

需求 #

获取id=221212的最新条目，并仅提取该最新条目中与同id历史条目重复的fieldsData记录。

当前查询的问题 #

当前使用的查询语句通过UNNEST展开所有记录后做窗口排序，会扫描全表数据，违背分区表的设计初衷：

select * from 
(
SELECT 
id, createdAt, createdBy, fields.key, fields.value,
DENSE_RANK() OVER(PARTITION BY id ORDER BY insertedDate DESC)AS Rank1
FROM `mytableName` , UNNEST(fieldsData) as fields
WHERE createdAt IS NULL or DATE(createdAt) = CURRENT_DATE()
)
where rank1 = 1

优化思路 #

1. 先通过分区过滤缩小范围，定位目标id的最新条目，避免全表扫描
2. 单独提取最新条目的fieldsData，再与该id的历史条目（排除最新）的fieldsData做交集，得到重复记录

优化后的查询语句 #

WITH latest_entry AS (
  SELECT 
    id,
    fieldsData,
    insertedDate
  FROM `mytableName`
  WHERE id = '221212'
    -- 利用分区过滤，根据实际情况调整时间范围，比如只扫描近3个月的分区
    AND createdOn >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)
  ORDER BY insertedDate DESC
  LIMIT 1
),
historical_fields AS (
  SELECT 
    fields.key,
    fields.value
  FROM `mytableName`, UNNEST(fieldsData) AS fields
  WHERE id = '221212'
    AND createdOn >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)
    -- 排除最新条目，只取历史数据
    AND insertedDate < (SELECT insertedDate FROM latest_entry)
)
-- 取最新条目fieldsData与历史fieldsData的交集
SELECT DISTINCT
  le.id,
  f.key,
  f.value
FROM latest_entry le, UNNEST(le.fieldsData) AS f
INNER JOIN historical_fields hf
  ON f.key = hf.key AND f.value = hf.value

关键优化点 #

• 精准定位最新条目：用ORDER BY insertedDate DESC LIMIT 1直接获取目标id的最新记录，无需对所有记录做窗口函数计算
• 分区过滤生效：通过createdOn的时间范围条件，让查询仅扫描指定分区的数据，而非全表
• 减少UNNEST范围：仅对最新条目和必要的历史数据做UNNEST操作，降低数据处理量

额外建议 #

• 若已知目标id对应的createdOn月份，直接指定分区条件（如DATE_TRUNC(createdOn, MONTH) = '2022-11-01'），能进一步减少扫描量
• 针对每日新增10000条的场景，可定期归档历史数据到冷存储，提升查询效率

内容的提问来源于stack exchange，提问作者User-8017771