本文介绍了BigQuery SQL IF如果重复记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

假设我有一个类似于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如果重复记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-21 01:20