时间戳

首页 > 解决方案 > 如何引用 WHERE 子句中计算的另一个字段？

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

问题描述

考虑一个简化的例子：

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

有一个更好的方法吗？

标签： sqlgoogle-bigquery

解决方案

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

推荐阅读