sql - 在同一查询中多次使用计算列

Question

我有这个反复出现的问题，我想多次使用我在选择查询中计算的值。这是一个例子

SELECT
  complicated_function(x) as foo,
  another_complicated_function(y) as bar,
  complicated_function(x)/another_complicated_function(y) as foo_bar_rate
FROM my_table;

编写此查询的最简单方法是什么？理想情况下，我想写

SELECT
  complicated_function(x) as foo
  another_complicated_function(y) as bar,
  foo/bar as foo_bar_rate
FROM my_table;

这个问题不是关于正在计算的具体值，而是关于如何以更简单、更易于维护的方式编写此查询。

Answer 1

你可以使用WITH

WITH computed_functions as (
  SELECT
    complicated_function(x) as foo,
    another_complicated_function(y) as bar,
  FROM my_table;
)
SELECT foo, bar, foo/bar as foo_bar_rate FROM computed_functions;

然而，这仍然很笨重。如果要选择更多列，则需要将其添加到 WITH 查询和主查询中。