当用于筛选该列的值来自子查询时，如何从 BigQuery 分区表中获得好处?

Question

我有一个这样的查询：

WITH data AS (
SELECT *
FROM `fh-bigquery.wikipedia_v3.pageviews_2018`
WHERE wiki='en'
AND title LIKE 'Goo%'
), min_date_calc AS (
SELECT DATE(MIN(datehour)) FROM data WHERE datehour<'2018-01-05'
)

SELECT title, SUM(views) views
FROM data
WHERE 
DATE(datehour) BETWEEN (SELECT * FROM min_date_calc) AND DATE_ADD((SELECT * FROM min_date_calc), INTERVAL 7 DAY) 
GROUP BY 1 ORDER BY 2 DESC LIMIT 1

此查询要么不运行，要么不使用分区来修剪查询的数据 - 而是扫描整个表。我能在这里做什么？

(问题基于Reddit上的评论(

Answer 1

此查询将仅扫描 2.86GB：

WITH data AS (
SELECT *
FROM `fh-bigquery.wikipedia_v3.pageviews_2018`
WHERE wiki='en'
AND title LIKE 'Goo%'
)
SELECT title, SUM(views) views
FROM data
WHERE 
DATE(datehour) BETWEEN '2018-01-01' AND '2018-01-08' # 2.68 GB 
GROUP BY 1 ORDER BY 2 DESC LIMIT 1

但那是因为我有日期作为常数。同样，像这样的查询也会表现良好：

WITH data AS (
SELECT *
FROM `fh-bigquery.wikipedia_v3.pageviews_2018`
WHERE wiki='en'
AND title LIKE 'Goo%'
), min_date AS (
SELECT DATE('2018-01-01')
)

SELECT title, SUM(views) views
FROM data
WHERE 
DATE(datehour) BETWEEN (SELECT * FROM min_date) AND DATE_ADD((SELECT * FROM min_date), INTERVAL 7 DAY) 
GROUP BY 1 ORDER BY 2 DESC LIMIT 1

这很有效，因为日期已成为常量(即使在函数内也是如此(。

要使用不是常量的值获得这些相同的好处，那么我们可以使用脚本来获得所有好处：

DECLARE min_date DATE;
SET min_date = (
WITH data AS (
SELECT *
FROM `fh-bigquery.wikipedia_v3.pageviews_2018`
WHERE wiki='en'
AND title LIKE 'Goo%'
)
SELECT DATE(MIN(datehour)) FROM data WHERE datehour<'2018-01-04'
);
WITH data AS (
SELECT *
FROM `fh-bigquery.wikipedia_v3.pageviews_2018`
WHERE wiki='en'
AND title LIKE 'Goo%'  
)
SELECT title, SUM(views) views
FROM data
WHERE 
DATE(datehour) BETWEEN min_date AND DATE_ADD(min_date, INTERVAL 7 DAY) # 2.68 GB 
GROUP BY 1 ORDER BY 2 DESC LIMIT 1

现在我们看到两个查询：

• 第一个需要尽可能多的数据来设置min_date的值。
• 第二个也像前一个一样使用 2.86GB - 但现在min_date的值来自动态查询。