BigQuery SQL IF如果重复记录 [英] BigQuery SQL IF over repeated record

问题描述

假设我有一个类似于BigQuery文档中提到的模式：
$ b $ pre $ 最后修改的模式总计行总数字节过期
----------------- ------------------------------- ---- ------------ ------------- ------------
9月27日10:01 ：类型：字符串4 794
| - 全名：字符串（必填）
| - 年龄：整数
| - 性别：字符串
+ - phoneNumber：record
| | - areaCode：integer
| | - 数字：整数
+ - 儿童：记录（重复）
| | - name：string
| | - 性别：字符串
| | - 年龄：整数
+ - citiesLived：记录（重复）
| | - place：string
| + - yearsLived：整数（重复）


假设我们有fullNames：John，josh，harry

citiesLived：newyork，chicago，seattle

如何迭代citiesLived并使用条件计数。例如，我想要计算有多少用户拥有fullName = John的人都住在城市里.Lived.place = newyork和citiesLived.place = chicago，但没有住在citiesLived.place = seattle。

感谢，
John

解决方案

您可以使用OMIT IF关键字。 （这是没有记录，我会提交一个错误，以确保它有记录）

  SELECT COUNT（*）FROM（
 SELECT全名，
 IF（citiesLived.place =='newyork'，1,0）as ny，
 IF（citiesLived.place =='chicago'，1,0）as chi 
 FROM（FLATTEN（name_table，citiesLived））
 OMIT RECORD IF citiesLived.place ='seattle'）
 WHERE fullname ='John'
 AND ny == 1 
 AND chi == 1 
  

Assume I have a schema like the one mentioned in BigQuery docs:

Last modified                 Schema                 Total Rows   Total Bytes   Expiration
 ----------------- ----------------------------------- ------------ ------------- ------------
  27 Sep 10:01:06   |- kind: string                     4            794
                    |- fullName: string (required)
                    |- age: integer
                    |- gender: string
                    +- phoneNumber: record
                    |  |- areaCode: integer
                    |  |- number: integer
                    +- children: record (repeated)
                    |  |- name: string
                    |  |- gender: string
                    |  |- age: integer
                    +- citiesLived: record (repeated)
                    |  |- place: string
                    |  +- yearsLived: integer (repeated)

Assume we have fullNames : John, josh, harry

citiesLived : newyork, chicago, seattle

How do I iterate over citiesLived and count by using conditionals. For example, I'd like to count how many users with fullName = John have lived in both citiesLived.place = newyork and citiesLived.place = chicago, but haven't lived in citiesLived.place = seattle.

Thanks, John

解决方案

You can use the OMIT IF keyword. (this is undocumented, I'll file a bug to make sure it gets documented)

SELECT COUNT(*) FROM (
  SELECT fullname, 
    IF (citiesLived.place == 'newyork', 1, 0) as ny,
    IF (citiesLived.place == 'chicago', 1, 0) as chi
  FROM (FLATTEN(name_table, citiesLived))
  OMIT RECORD IF citiesLived.place = 'seattle') 
WHERE fullname = 'John' 
  AND ny == 1 
  AND chi == 1

这篇关于BigQuery SQL IF如果重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！