英文:
how to identify records where the array elements have same items?
问题
I have a table which has some sample data like
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
如何获取具有相同父母和相同子集但不同rollNo的记录的id。
我只能在id和parent级别对它们进行分组，如何在GCP中在子级别对它们进行分组？
select id from myTable group by id,parent having count(itemNbr) >1 ;
预期输出:
ABC
英文:
i have a table which has some sample data like
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
how can i get the id of such records where they have same parent with same set of children but different rollNo.
i could only group them till id and parent, how can we group them at children level in gcp?
select id from myTable group by id,parent having count(itemNbr) >1 ;
expected output:
ABC
答案1
得分: 2
你需要识别具有相同项目的数组元素的记录。由于数组是有序列表，你需要首先对其进行排序，然后再进行比较。
-- 示例数据
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']
)
-- 查询从这里开始
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;
-- 查询结果
+-----+--------+------------+
| id | parent | sorted |
+-----+--------+------------+
| ABC | P123 | C1,C2,C3 |
+-----+--------+------------+
只提供了代码部分的翻译。
英文:
>how to identify records where the array elements have same items?
Since an array is an ordered list, you need to sort it first before comparing it with other.
-- 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 |
+-----+--------+------------+
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论