BigQuery中如何将同位置门店统计结果批量更新到原表对应字段

你可以直接使用BigQuery的UPDATE关联子查询的方式实现目标更新操作，具体SQL如下：

UPDATE `test` t
SET 
  same_location_count = calc.same_location_count,
  same_location_store_id = NULLIF(calc.same_location_store_id, '')
FROM (
  SELECT
    store_id,
    (COUNT(store_id) OVER (PARTITION BY latitude, longitude)) - 1 AS same_location_count,
    REPLACE(STRING_AGG(CONCAT(store_id, ':'), '') OVER (PARTITION BY latitude, longitude), CONCAT(store_id, ':'), '') AS same_location_store_id
  FROM `test`
) calc
WHERE t.store_id = calc.store_id;

注意事项 #

• 字段映射适配：上述SQL已经根据你提供的表示例，将原逻辑里的lat/long/id替换为latitude/longitude/store_id，如果实际表的字段命名有差异，请自行调整对应字段名
• 空值兼容处理：SQL中加入了NULLIF函数，当没有同位置其他门店时，会把计算得到的空字符串转为NULL，和你给出的更新后目标效果完全匹配
• 提前验证建议：你可以先执行下方查询确认更新结果符合预期后，再执行上面的UPDATE操作：

SELECT 
  t.store_id,
  t.same_location_count AS old_count,
  calc.same_location_count AS new_count,
  t.same_location_store_id AS old_ids,
  calc.same_location_store_id AS new_ids
FROM `test` t
JOIN (
  SELECT
    store_id,
    (COUNT(store_id) OVER (PARTITION BY latitude, longitude)) - 1 AS same_location_count,
    REPLACE(STRING_AGG(CONCAT(store_id, ':'), '') OVER (PARTITION BY latitude, longitude), CONCAT(store_id, ':'), '') AS same_location_store_id
  FROM `test`
) calc
ON t.store_id = calc.store_id;

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