如何识别数组元素具有相同项的记录?

Question

我有一个表，其中有一些样本数据，如

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

我如何得到这些记录的id，他们有相同的父，相同的子集合，但不同的rollNo.

我只能将它们分组到id和parent，我们如何在gcp中将它们分组到子级?

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

预期输出:

ABC 

Answer 1

如何识别数组元素具有相同项的记录?

由于数组是有序列表，因此在与其他数组比较之前需要先对其排序。

-- sample data
WITH sample_table AS (
SELECT 'ABC' id, '12345' rollNo, 'P123' parent, ['C1', 'C2', 'C3'] children UNION ALL
SELECT 'ABC', '54678', 'P123', ['C3', 'C1', 'C2'] UNION ALL
SELECT 'DEF', '123245', 'P1223', ['C5', 'C6', 'C7'] UNION ALL
SELECT 'DEF', '456278', 'P1223', ['C0', 'C11', 'C22']
)
-- query starts here
SELECT id, parent, (SELECT STRING_AGG(c ORDER BY c) FROM t.children c) sorted
FROM sample_table t
GROUP BY id, parent, sorted HAVING COUNT(1) > 1;
-- query result
+-----+--------+------------+
| id  | parent |   sorted   |
+-----+--------+------------+
| ABC | P123   |  C1,C2,C3  |
+-----+--------+------------+