英文:
BigQuery compare two lists
问题
DECLARE list ARRAY
DECLARE list2 ARRAY
SET list1 = ['orange','red','black' ];
SET list2 = ['red','black' ];
作为结果，我想要获得'orange'，因为它是在list2中缺失的值。
有人能帮我吗？
我尝试过这个解决方案，但是太慢了。我的列表实际上包含数百个值，所以我想要比在每个值前添加'as value union all select'更高效的方法。
WITH list1 AS (
SELECT 'orange' AS value UNION ALL
SELECT 'red' AS value UNION ALL
SELECT 'black' AS value
)
,
list2 AS (
SELECT 'red' AS value UNION ALL
SELECT 'black' AS value
)
SELECT value
FROM (
SELECT value, 'list1' AS list_name
FROM list1
UNION ALL
SELECT value, 'list2' AS list_name
FROM list2
)
GROUP BY value
HAVING COUNT(*) = 1
ORDER BY value
英文:
I would like to compare two lists of strings using BigQuery.
DECLARE list ARRAY <String>;
DECLARE list2 ARRAY <String>;
SET list1 = ['orange','red','black' ];
SET list2 = ['red','black' ];
AS a result, i would like to get 'orange' as it is the value missing in list2.
Can anyone help me please ?
I've tried this solution, but it takes too long. My lists actually contain hundreds of values, so I'd like something more efficient than adding 'as value union all select' in front of each value.
WITH list1 AS (
SELECT 'orange' AS value UNION ALL
SELECT 'red' AS value UNION ALL
SELECT 'black' AS value
)
,
list2 AS (
SELECT 'red' AS value UNION ALL
SELECT 'black' AS value
)
SELECT value
FROM (
SELECT value, 'list1' AS list_name
FROM list1
UNION ALL
SELECT value, 'list2' AS list_name
FROM list2
)
GROUP BY value
HAVING COUNT(*) = 1
ORDER BY value
答案1
得分: 1
以下是您提供的代码的中文翻译：
考虑以下（BigQuery标准SQL）
DECLARE list1, list2 ARRAY<string>;
SET list1 = ['orange','red','black' ]; SET list2 = ['red','black' ];
SELECT * FROM (
SELECT DISTINCT el FROM UNNEST(list1) el
UNION ALL
SELECT DISTINCT el FROM UNNEST(list2) el
)
GROUP BY el
HAVING COUNT(*) = 1;
带有输出
还请考虑以下方法
DECLARE list1, list2 ARRAY<string>;
SET list1 = ['orange','red','black', 'green' ]; SET list2 = ['red','black', 'pink' ];
SELECT list, ARRAY_AGG(el) el FROM (
SELECT el, MIN(list) list FROM (
SELECT DISTINCT el, 'list1' list FROM UNNEST(list1) el
UNION ALL
SELECT DISTINCT el, 'list2' FROM UNNEST(list2) el
)
GROUP BY el
HAVING COUNT(*) = 1
)
GROUP BY list;
带有输出
英文:
Consider below (BigQuery Standard SQL)
DECLARE list1, list2 ARRAY<string>;
SET list1 = ['orange','red','black' ]; SET list2 = ['red','black' ];
SELECT * FROM (
SELECT DISTINCT el FROM UNNEST(list1) el
UNION ALL
SELECT DISTINCT el FROM UNNEST(list2) el
)
GROUP BY el
HAVING COUNT(*) = 1;
with output
Also consider below approach
DECLARE list1, list2 ARRAY<string>;
SET list1 = ['orange','red','black', 'green' ]; SET list2 = ['red','black', 'pink' ];
SELECT list, ARRAY_AGG(el) el FROM (
SELECT el, MIN(list) list FROM (
SELECT DISTINCT el, 'list1' list FROM UNNEST(list1) el
UNION ALL
SELECT DISTINCT el, 'list2' FROM UNNEST(list2) el
)
GROUP BY el
HAVING COUNT(*) = 1
)
GROUP BY list
with output
答案2
得分: 0
DECLARE list1, list2 ARRAY
SET list1 = ['orange', 'red', 'black'];
SET list2 = ['red', 'black'];
SELECT *
FROM (
SELECT *
FROM UNNEST(list1) as list1
)
WHERE list1 not in (
SELECT * FROM UNNEST(list2)
)
英文:
A little variation of the previous answer, not sure it will be faster then the previous code, given that your lists contain 100+ values.
DECLARE list1, list2 ARRAY<string>;
SET list1 = ['orange','red','black' ]; SET list2 = ['red','black' ];
SELECT *
FROM (
SELECT *
FROM UNNEST(list1) as list1
)
WHERE list1 not in (
SELECT * FROM UNNEST(list2)
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论