连接与子查询

Question

我在资源和性能方面比较了Joins和Subquery，答案似乎与平台有关。但似乎没有任何东西从BigQuery的角度来谈论它们。

当我将查询范围扩展到包括100 GB时，我遇到了一个

Query Failed
Error: Resources exceeded during query execution.

我大概有

#standardSQL
SELECT * FROM table t1 WHERE 
(t1.a in (SELECT b FROM anothertable WHERE class='value') 
OR t1.a in (SELECT c FROM table2) )

我想知道在BigQuery中连接是否会更好，特别是如果我扩展到to级的数据。

Answer 1

注意这个查询和下一个查询之间的区别：

1)

#standardSQL
SELECT COUNTIF(author IN (
   SELECT author 
   FROM `fh-bigquery.reddit_comments.2017_01` 

))
FROM `fh-bigquery.reddit_comments.2017_01`

2)

#standardSQL
SELECT COUNTIF(author IN (
   SELECT DISTINCT author 
   FROM `fh-bigquery.reddit_comments.2017_01` 
))
FROM `fh-bigquery.reddit_comments.2017_01`

这是一个愚蠢的查询--两者都应该返回157893170。尽管如此，1)跑了超过8分钟(到目前为止)，而2)跑了36秒。

秘密是什么？在执行IN()时，请确保使用DISTINCT删除重复项-否则将有许多行要连接，而这些行根本不会改变结果。

// TODO(gcp): This could be a BigQuery optimization.

Answer 2

我想知道，你有没有尝试过埃利奥特关于使用EXISTS的建议

类似于：

WITH table1 AS(
SELECT '1' as user, 1 AS id UNION ALL
SELECT '2' AS user, 2 as id UNION ALL
SELECT '3' AS user, 3 as id
),
anothertable AS(
SELECT '1' AS user, 'value' AS class , '4' AS c UNION ALL
SELECT '2' AS user, 'value2' AS class, '2' AS c UNION ALL
SELECT '4' AS user, 'value' AS class, '3' AS c UNION ALL
SELECT '5' AS user, 'value2' AS class, '5' as c
),
table2 AS(
SELECT '4' AS c UNION ALL
SELECT '2' AS c UNION ALL
SELECT '3' AS c UNION ALL
SELECT '5' as c
)

SELECT
  t1.*
FROM table1 t1
WHERE TRUE
AND EXISTS(SELECT 1 FROM anothertable ta WHERE (class = 'value' AND t1.user = ta.user))
OR EXISTS(SELECT 1 FROM table2 t2 WHERE t1.user = t2.c)

它是否超出了资源？