标准的sql查询以获得与另一个表（Google BigQuery）匹配的记录字段 [英] standard sql query to get matching record field with another table (Google BigQuery)

问题描述

我有两张桌子
$ b $ 1 $ table $ $ c $ main $

  id phone.type phone.id 
 ========================== ==== 
 | 1 | android | adkfjagp | 
 | | android | asdfasdf | 
 | | iphone | akfj2341 | 
 | | iphone | ada93519 | 
 ------------------------------ 
  

我还有另外一个存储一堆Android手机ID的表，如

2）table android

  ============== 
 | adkfjagp | 
 | ... | 
 -------------- 
  

有没有方式我可以得到table main中的所有行，其中该行包含一个类型为android和id的记录，该记录也位于android中。

解决方案

以下应该使它


$ b

  #standardSQL 
 SELECT m。* 
 FROM main AS m 
 CROSS JOIN（SELECT ARRAY_AGG（id）AS ids FROM android）as a 
 WHERE（
 SELECT COUNT（1）
 FROM UNNEST（phone）AS phone 
 WHERE phone.type ='android'
 AND phone.id IN UNNEST（a.ids）
）> 0 
  

您可以使用以下虚拟数据进行测试
#standardSQL
WITH AS（
SELECT
1 AS id，
[STRUCT< type STRING，id STRING>
（'android'，'adkfjagp'），
（'android'，'asdfasdf'），
（'iphone'，'akfj2341'），
（'iphone'，'ada93519'）
] AS phone UNION ALL
SELECT
2 AS id，
[STRUCT< STRING，id STRING>
'android'，'adkfjagp1'），
（'android'，'bbbbbbbb1'），
（'android'，'akfj2341'），
（'iphone'，'ada93519'）
] AS phone
），
android AS（
SELECT'adkfjagp'AS id UNION ALL
SELECT'bbbbbbbb'
）
SELECT m $ * b $ b FROM main m
CROSS JOIN（SELECT ARRAY_AGG（id）AS ids FROM android）as a
WHERE（
SELECT COUNT（1）
FROM UNNES T（phone）AS phone
WHERE phone.type ='android'
AND phone.id IN UNNEST（a.ids）
）> 0


I have two tables

1) table main

   id    phone.type  phone.id
==============================
|   1   | android | adkfjagp |
|       | android | asdfasdf |
|       | iphone  | akfj2341 |
|       | iphone  | ada93519 |
------------------------------

and I have another table which stores a bunch of android phone ids like this

2) table android

==============
|  adkfjagp  |
|   ...      |
--------------

Is there a way I can get all rows in table main where the row contains a record with type android and id that is in also table android.

解决方案

below should make it

#standardSQL
SELECT m.*
FROM main AS m
CROSS JOIN (SELECT ARRAY_AGG(id) AS ids  FROM android) AS a
WHERE  (
  SELECT COUNT(1) 
  FROM UNNEST(phone) AS phone 
  WHERE phone.type = 'android' 
  AND phone.id IN UNNEST(a.ids)
) > 0

you can test it with below dummy data

#standardSQL
WITH main AS (
  SELECT 
    1 AS id, 
    [STRUCT<type STRING, id STRING>
      ('android', 'adkfjagp'),
      ('android', 'asdfasdf'),
      ('iphone', 'akfj2341'),
      ('iphone', 'ada93519')
     ] AS phone UNION ALL
  SELECT 
    2 AS id, 
    [STRUCT<type STRING, id STRING>
      ('android', 'adkfjagp1'),
      ('android', 'bbbbbbbb1'),
      ('android', 'akfj2341'),
      ('iphone', 'ada93519')
     ] AS phone
),
android AS (
  SELECT 'adkfjagp' AS id UNION ALL
  SELECT 'bbbbbbbb' 
)
SELECT m.*
FROM main AS m
CROSS JOIN (SELECT ARRAY_AGG(id) AS ids  FROM android) AS a
WHERE  (
  SELECT COUNT(1) 
  FROM UNNEST(phone) AS phone 
  WHERE phone.type = 'android' 
  AND phone.id IN UNNEST(a.ids)
) > 0

这篇关于标准的sql查询以获得与另一个表（Google BigQuery）匹配的记录字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！