sql - 如何引用在 WHERE 子句中计算的另一个字段？

Question

考虑一个简化的例子:

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 找到了一个好名字。很难。特别是因为在实践中，我会重复这种模式两三遍。

有一个更好的方法吗？

Answer 1

下面是 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   

注意:如果 lengthy_expression 的结果本身就是一个 ARRAY - 您需要将其包含在 struct 中，因为 BigQuery 不支持数组数组