BigQuery REPEATED字段包含

问题描述

有没有办法检查BigQuery REPEATED字段是否包含特定值？

我正在尝试执行类似操作：

  SELECT CASE WHEN column1 CONTAINS（Test Value）THEN 1 ELSE 0 END FROM testdata 
  

当column1是REPEATED模式的STRING类型的字段时。
我有多个相同性质的REPEATED列，所以不得不将它们全部展平似乎很单调。

解决方案

$ b

  SELECT 
 id_or_whatever_key_column_you_have，
 SUM（CASE 
当column1 CONTAINS（Test Value ）THEN 1 
 ELSE 0 END 
）WITHIN RECORD AS匹配
 FROM testdata 
  

我意识到 - 从您的示例复制/粘贴时，我留下了一些额外的东西，可以在不丢失结果的情况下将其删除 - 因此它不仅会告诉您如果您有匹配或不匹配（真/假），而是让您计数匹配在每条记录中：

  SELECT 
 id_or_whatever_key_column_you_have，
 SUM（column1 CONTAINS（Test Value）） WITHIN RECORD AS相匹配
 FROM testdata 
  

我认为Danny的回答引发了我的想法： o）

Is there a way to check if a BigQuery REPEATED field contains a specific value?

I'm trying to do something like:

SELECT CASE WHEN column1 CONTAINS ("Test Value") THEN 1 ELSE 0 END FROM testdata

when column1 is a field of type STRING of mode REPEATED. I have multiple REPEATED columns of the same nature so having to flatten them all out seems tedious.

解决方案

Try below

SELECT 
  id_or_whatever_key_column_you_have, 
  SUM(CASE 
        WHEN column1 CONTAINS ("Test Value") THEN 1 
        ELSE 0 END
     ) WITHIN RECORD AS matches
FROM testdata

I realised - while copy/paste from your example I left some extra that can be removed without losing result - so it not only tells you if you have matches or not (true/false) but rather gives you count of matches within each record:

SELECT 
  id_or_whatever_key_column_you_have, 
  SUM(column1 CONTAINS ("Test Value")) WITHIN RECORD AS matches
FROM testdata

I think Danny's answer has triggered this in my mind :o)

这篇关于BigQuery REPEATED字段包含的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！