BigQuery多表关联去重：获取指定ID的唯一结果集

我在BigQuery中有三张表及对应数据，想要通过id和指定日期（2023-08-03）编写SQL查询，得到包含id、合并后的existing_stores和missing_stores的唯一行结果集（预期结果如下）。但当前编写的查询因为mic_item_discrepancy表存在重复id行，没能得到正确结果，请求修正。

预期结果集 #

id            existing_stores                             missing_stores
1003812607    "3640,0130,0131,2306,3638,0127,2789,2305"   "3102,2681,2686,2670,2682,3101,2673,2669,3103,2668"

表结构及数据 #

item表 #

id            date
------------------------
1003812607    2023-08-03
1003812607    2023-08-01
1003812607    2023-07-23
1003812607    2023-06-30

item_change_history表 #

createdTime                     docType    id       existing_stores
---------------------------------------------------------------------------------------------
2023-08-03 11:01:10.139617 UTC  Item    1003812607  "3640,0130,0131,2306,3638,0127,2789,2305"
2023-07-01 09:01:10.139617 UTC  Item    1003812607  "3640,0130,0131,2306,3638,0127,2789,2301"

mic_item_discrepancy表 #

ID          MISSING_STORE
-------------------------
1003812607  3102
1003812607  2681
1003812607  2686
1003812607  2670
1003812607  2682
1003812607  3101
1003812607  2673
1003812607  2669
1003812607  3103
1003812607  2668

尝试的查询语句 #

SELECT 
    item.id, ich.id, ich.existing_stores, mid.missing_stores
FROM 
    `hd-merch-prod.merch_item_cache_validation.item` item 
JOIN
    `hd-merch-prod.merch_item_cache.item_change_history` ich 
         ON item.date = "2023-08-03" 
         AND DATE(ich.createdTime) = item.date 
         AND ich.id = item.id  
JOIN
    `hd-merch-prod.merch_item_cache.mic_item_discrepancy` mid 
         ON item.id = mid.id
GROUP BY
    item.id, ich.id, ich.existing_stores, mid.missing_stores;

修正后的查询语句 #

WITH aggregated_missing AS (
    SELECT 
        ID AS id,
        STRING_AGG(MISSING_STORE, ',') AS missing_stores
    FROM `hd-merch-prod.merch_item_cache.mic_item_discrepancy`
    GROUP BY ID
)
SELECT 
    item.id,
    ich.existing_stores,
    am.missing_stores
FROM `hd-merch-prod.merch_item_cache_validation.item` item
JOIN `hd-merch-prod.merch_item_cache.item_change_history` ich
    ON item.id = ich.id
    AND item.date = "2023-08-03"
    AND DATE(ich.createdTime) = item.date
JOIN aggregated_missing am
    ON item.id = am.id
GROUP BY item.id, ich.existing_stores, am.missing_stores;

修正说明 #

原查询的问题在于直接关联三张表时，mic_item_discrepancy表的每行记录都会与前两张表的匹配行生成一条结果，加上分组时包含了mid.missing_stores，最终会生成多条重复id的行，无法得到唯一行的合并结果。

修正思路：

1. 先用CTE（公共表表达式）对mic_item_discrepancy表按id聚合，使用STRING_AGG函数将同一id下的所有MISSING_STORE合并成逗号分隔的字符串，得到每个id对应的完整missing_stores。
2. 从item表筛选指定日期的id，关联item_change_history表中同一天的记录，获取对应的existing_stores。
3. 将前两步的结果按id关联，最后分组确保结果唯一（如果前两步的结果已经是单一行，分组可以省略，但保留更稳妥）。

内容的提问来源于stack exchange，提问作者gcpdev-guy