如何在BigQuery中将列与数组进行比较? [英] How to compare column with arrays in BigQuery?

问题描述

我在BigQuery中有两个表.

I have two tables in BigQuery.

表 a 将值保存在数组中.表 b 的每个值都有一行如果至少一个值不在表 a 中具有相同ID

Table a keeps values in arrays. Table b has row for each value I want to have all values from table b by id if at least one value not in array from table a by the same id

WITH a as (SELECT 1 as id, ['123', 'abc', '456', 'qaz', 'uqw'] as value
UNION ALL SELECT 2, ['123', 'wer', 'thg', '10', '200']
UNION ALL SELECT 3, null),
b as (SELECT 1 as id, '123' as value
UNION ALL SELECT 1, 'abc'
UNION ALL SELECT 1, '456'
UNION ALL SELECT 1, 'qaz'
UNION ALL SELECT 1, 'uqw'
UNION ALL SELECT 2, '123'
UNION ALL SELECT 2, 'wer'
UNION ALL SELECT 2, '10'
UNION ALL SELECT 3, '200'
UNION ALL SELECT 3, 'thg'
)

制作它的最好方法是什么?

What is the best way to make it?

推荐答案

下面是BigQuery标准SQL

Below is for BigQuery Standard SQL

#standardSQL
SELECT * EXCEPT(flag)
FROM (
  SELECT b.*, COUNTIF(b.value IS NULL) OVER(PARTITION BY id) flag
  FROM `project.dataset.table_a` a, a.value 
  LEFT JOIN `project.dataset.table_b` b
  USING(id, value)
)
WHERE flag > 0
AND NOT value IS NULL    

如果要应用于您的问题的样本数据-输出为

If to apply to sample data from your question - output is

Row id  value    
1   2   123  
2   2   wer  
3   2   10     

如果按照您的逻辑-id = 3也应该在输出中-在下面使用

In case if by your logic - id=3 should also be in output - use below

#standardSQL
SELECT * EXCEPT(flag)
FROM (
  SELECT b.*, COUNTIF(b.value IS NULL) OVER(PARTITION BY id) flag
  FROM `project.dataset.table_a` a LEFT JOIN a.value 
  FULL OUTER JOIN `project.dataset.table_b` b
  USING(id, value)
)
WHERE flag > 0
AND NOT value IS NULL   

在这种情况下，输出为

Row id  value    
1   2   123  
2   2   wer  
3   2   10   
4   3   200  
5   3   thg  

这篇关于如何在BigQuery中将列与数组进行比较?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！