本文介绍了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字段包含的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-19 22:57