BigQuery:为什么从临时表查询花费的时间是整个数据集的单个聚合的 20+ 倍?

Question

表dataset.transactions包含 ~5.000.000 条记录。

1( 此查询需要 ~ 3 秒：

SELECT *
FROM dataset.transactions
WHERE customer = 'c1' AND year = 2017

2( 此查询大约需要 10 秒：

SELECT
salesrep_id AS id,
date AS lastUsedForFplDate,
fpl AS individual_fpl,
ANY_VALUE(salesrep_name) AS salesrep_name,
ANY_VALUE(customer) AS customer
FROM dataset.transactions VT1
WHERE date = (
SELECT
MAX(date)
FROM dataset.transactions VT2
WHERE
VT1.salesrep_id = VT2.salesrep_id
)
GROUP BY
salesrep_id,
date,
fpl

3(而这个需要超过200秒(尝试几次，每次在200秒后取消(：

WITH transactions AS (
SELECT *
FROM dataset.transactions
WHERE customer = 'c1' AND year = 2017
)
SELECT
salesrep_id AS id,
date AS lastUsedForFplDate,
fpl AS individual_fpl,
ANY_VALUE(salesrep_name) AS salesrep_name,
ANY_VALUE(customer) AS customer
FROM transactions VT1
WHERE date = (
SELECT
MAX(date)
FROM transactions VT2
WHERE
VT1.salesrep_id = VT2.salesrep_id
)
GROUP BY
salesrep_id,
date,
fpl

第三个查询由上述两个查询组成，但 #2 使用 #1 的输出作为源。

为什么需要这么多时间？

Answer 1

只是不要让这个问题得不到回答。第三个查询使用自连接，这是一种反模式，正如 Elliott 所评论的那样。

使用自联接的结果是，它可能会使输出行数加倍。输出数据的这种增加可能会导致性能不佳。

建议使用窗口(分析(函数来减少查询生成的额外字节数，而不是使用自联接。

分析函数的简化语法如下：

analytic_function_name ( [ argument_list ] )
OVER (
[ PARTITION BY partition_expression_list ]
[ ORDER BY expression [{ ASC | DESC }] [, ...] ]
[ window_frame_clause ]
)