温馨提示:本文翻译自stackoverflow.com,查看原文请点击:sql - How to refer to another field computed in the WHERE clause?
google-bigquery sql

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

发布于 2020-04-05 00:28:24

考虑一个简化的示例:

SELECT
  lengthy_expression AS a,
  g(a) AS b,
  h(a) AS c,
  ...
FROM
  my_table

在这里,lengthy_expression表示一个复杂的表达式,它使用来自多个字段my_table并跨越多行的内容。其结果用于计算另外两个字段bc但是,在标准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因此很难找到一个好名字特别是因为在实践中,我将这种模式重复两次或三次。

有一个更好的方法吗?

查看更多

提问者
Thomas
被浏览
121
Mikhail Berlyant 2020-02-01 03:06

以下是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数组