考虑一个简化的示例:
SELECT
lengthy_expression AS a,
g(a) AS b,
h(a) AS c,
...
FROM
my_table
在这里,lengthy_expression
表示一个复杂的表达式,它使用来自多个字段my_table
并跨越多行的内容。其结果用于计算另外两个字段b
和c
。但是,在标准SQL中不允许上述操作,因为该WHERE
子句中的表达式不允许引用该WHERE
子句中另一个表达式的结果(为什么不超出我的范围)。
天真的选择是重复lengthy_expression
,但这是我明确要避免的。
一种选择是使用子查询:
SELECT
a,
g(a) AS b,
h(a) AS c,
...
FROM (
SELECT
lengthy_expression AS a,
...
FROM
my_table
)
但是正如您所看到的,我现在需要重复...
外部查询所需的其他字段。
也许有联接的子查询呢?
SELECT
a,
g(a) AS b,
h(a) AS c,
...
FROM
my_table
INNER JOIN (
SELECT
lengthy_expression AS a
FROM
my_table
) USING id
它可以工作,但是现在有一个(可能是昂贵的)联接,除了保持查询可读性外,它没有任何作用。而且它甚至不能很好地达到该目的,因为它们lengthy_expression
被藏在使用位置之下,而且人类读者必须跳遍各处以了解发生了什么。
一种替代方法是使用CTE:
WITH
my_table_with_a AS (
SELECT
*,
lengthy_expression AS a
FROM
my_table
)
SELECT
*,
g(a) AS b,
h(a) AS c,
...
FROM
my_table_with_a
至少现在,阅读顺序或多或少地与操作发生的逻辑顺序相匹配,但这非常冗长,my_table_with_a
因此很难找到一个好名字。特别是因为在实践中,我将这种模式重复两次或三次。
有一个更好的方法吗?
以下是BigQuery标准SQL
#standardSQL
SELECT
a,
g(a) AS b,
h(a) AS c,
FROM `project.dataset.my_table`,
UNNEST([lengthy_expression]) a
以下是上述方法的极为简化的示例
#standardSQL
WITH `project.dataset.my_table` AS (
SELECT 1 x, 2 y, 3 z UNION ALL
SELECT 4, 5, 6
)
SELECT
a,
a / 2 AS b,
2 * a AS c
FROM `project.dataset.my_table`,
UNNEST([x + y + z]) a
WHERE a > 10
结果
Row a b c
1 15 7.5 30
注意:如果result of lengthy_expression
本身是一个数组-您需要将其封装到struct中,因为BigQuery不支持array数组
哈,这是一个非常有趣的技巧,谢谢!是否知道它的性能比子查询或CTE方法更好/更差?
我预计不会对性能造成任何影响-但显然冗长且易于维护