GCP中如何筛选同Parent、同Children集合但不同RollNo的记录ID

需求说明 #

需要从表中筛选出满足以下条件的id：

• 拥有相同的parent值
• children数组的元素集合完全一致（不考虑数组内元素的顺序）
• 对应不同的rollNo值

样例数据表 #

id                      rollNo         parent             children(array)   
ABC                     12345          P123                [C1, C2, C3]
ABC                     54678          P123                [C3, C1, C2]
DEF                     123245         P1223               [C5, C6, C7]
DEF                     546278         P1223               [C0, C11, C12]

当前SQL语句 #

select id from myTable group by id,parent having count(itemNbr) >1 ; 

预期输出 #

ABC

解决方案 #

在GCP BigQuery中，数组的顺序会直接影响分组结果，因此需要先将children数组转换为不依赖元素顺序的统一标识，再进行分组筛选。具体实现SQL如下：

WITH processed_data AS (
  SELECT
    id,
    rollNo,
    parent,
    -- 将children数组排序后拼接为字符串，消除元素顺序差异
    ARRAY_TO_STRING(ARRAY(SELECT elem FROM UNNEST(children) AS elem ORDER BY elem), ',') AS normalized_children
  FROM myTable
)
SELECT DISTINCT id
FROM processed_data
GROUP BY parent, normalized_children, id
-- 筛选分组内存在多个不同rollNo的记录
HAVING COUNT(DISTINCT rollNo) > 1;

逻辑说明 #

• UNNEST(children)将数组展开为单行元素，ORDER BY elem对元素排序后重新组合为有序数组，最后通过ARRAY_TO_STRING转为字符串——这样不管原数组元素顺序如何，只要集合相同，得到的字符串就一致
• 分组时结合parent和normalized_children，确保只聚合满足parent相同、children集合相同的记录
• COUNT(DISTINCT rollNo) > 1筛选出同一id下对应不同rollNo的情况，完全匹配需求

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