BigQuery REPEATED 字段包含 [英] BigQuery REPEATED field contains

问题描述

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

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

当 column1 是模式为 REPEATED 的 STRING 类型字段时.我有多个相同性质的 REPEATED 列，因此必须将它们全部展平似乎很乏味.

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.

推荐答案

试试下面的

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

我认为丹尼的回答在我脑海中引发了这一点:o)

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

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