PostgreSQL：在同一查询中使用计算列

Question

我在使用计算列的时候遇到了问题，在SQL中类似的代码如下所示，是否可以在PostgreSQL中重新创建这个计算列？

select cost_1, quantity_1, cost_2, quantity_2, 
      (cost_1 * quantity_1) as total_1,
      (cost_2 * quantity_2) as total_2,
      (calculated total_1 + calculated total_2) as total_3
from data;

在 PostgreSQL 中，类似的代码会返回以下错误：

列 total_1 和 total_2 不存在。

- user1146150

---

可能是重复的问题：PostgreSQL视图：在一个计算字段中引用另一个计算字段 - Andriy M

“works in SQL”？你向我们展示了SQL，然后又说它不起作用。那么你所说的“in SQL”是什么意思？ - user330315

Answer 1

为了能够访问列别名，您需要将SELECT语句包装到派生表中：

select cost1,
       quantity_1,
       cost_2,
       quantity_2
       total_1 + total_2 as total_3
from (
    select cost_1, 
           quantity_1, 
           cost_2, 
           quantity_2, 
           (cost_1 * quantity_1) as total_1,
           (cost_2 * quantity_2) as total_2
    from data
) t

这不会有任何性能惩罚。

（我真的很惊讶你原始的SQL语句竟然在DBMS中运行）

- user330315

---

原文看起来像是SAS的PROC SQL，它允许这样做。 - Andrew

我想在这里像Haskell一样使用let ... = ....定义来定义一个本地计算，而不是子查询。那将会很好。 尽管如此，感谢您的好回答！ - Hartmut Pfarr

使用CTE（公共表达式）和不使用CTE来包装选择语句有什么优点/缺点？谢谢 :) - Davita

在Postgres中，公共表达式（CTE）和派生表的优化方式不同。在大多数情况下（包括本例），这没有区别，因此这取决于个人喜好。并且，并非每个人都习惯使用CTE，这将在答案中引入另一层“复杂性”，我希望避免这种情况。 - user330315

Answer 2

如果您不喜欢用外部查询包装整个查询, 您可以使用LATERAL来计算中间的total_1和total_2：

SELECT cost_1, quantity_1, cost_2, quantity_2, total_1, total_2,
       total_1 + total_2 AS total_3
FROM data
,LATERAL(SELECT cost_1 * quantity_1, cost_2 * quantity_2) AS s1(total_1,total_2);

DBFiddle演示

输出：

╔═════════╦═════════════╦═════════╦═════════════╦══════════╦══════════╦═════════╗
║ cost_1  ║ quantity_1  ║ cost_2  ║ quantity_2  ║ total_1  ║ total_2  ║ total_3 ║
╠═════════╬═════════════╬═════════╬═════════════╬══════════╬══════════╬═════════╣
║      1  ║          2  ║      3  ║          4  ║       2  ║      12  ║      14 ║
║      3  ║          5  ║      7  ║          9  ║      15  ║      63  ║      78 ║
║     10  ║          5  ║     20  ║          2  ║      50  ║      40  ║      90 ║
╚═════════╩═════════════╩═════════╩═════════════╩══════════╩══════════╩═════════╝

- Lukasz Szozda

---

哦，不错。今天我学到了。这种方法对某些情况非常有帮助，包括我正在创建的一个视图，它涉及多层引用之前的计算结果。嵌入重复计算很容易出错，而且子查询的嵌套层数可能会多达9层。使用"LATERAL"版本更加易于理解和维护。 - Bo Jeanes

这里的s1在侧面部分是什么？通过试错，我发现它可以是任意字符串 - 那为什么需要它呢？ - redacted

@RobinNemeth s1 是派生表的别名。您可以在 SELECT 中使用它，比如 SELECT s1.total_1 + s1.total_2 AS total_3。 - Lukasz Szozda

Answer 3

通常情况下，关于SELECT子句，你需要了解以下两点：

• 尽管它写在最前面，但是除ORDER BY子句外，它是在最后被执行的。这就是为什么你不能在任何其他子句（特别是WHERE子句）中使用任何计算字段或别名，除非在ORDER BY子句中。
• SELECT子句中的计算会同时进行，或者至少表现得像是同时进行。因此，你不能将一个计算作为另一个计算的一部分。

所以，简短的答案是你不能这样做，这是有意设计的。

值得注意的例外是Microsoft Access，其中你确实可以在后续列和WHERE子句中使用计��。但是，虽然这很方便，但实际上并不是一个优势：不遵循上述原则会导致效率降低。但对于轻量级数据库，Access就是要用来这么做的。

如果你真的想重复使用计算出的结果，你需要一个单独的查询，可以是子查询或公共表达式。公共表达式更容易使用，因为它们更易于阅读。

编辑

以下是一个例子，说明为什么使用计算列可能会引起混乱。在澳大利亚，我们用厘米来测量身高，但仍有一些地方使用古老的英寸单位（1英寸=2.54厘米）。

SELECT
    id,
    height/2.54 as height, -- cm -> in
    case when height>175 then 'tall' else '' end as comment
FROM people;

这里CASE仍然使用原始的height值。

- Manngo

---

顺便提一下，每当我处理带有物理单位的字段时，我总是在模式中尽可能包含单位，但如果我正在构建一些复杂的查询，例如将 height 别名为 height_cm。这样做不仅更简洁，而且在需要在公制和英制之间进行转换时非常有用。 - Will Ediger

Answer 4

select cost_1, quantity_1, cost_2, quantity_2, 
      cost_1 * quantity_1 as total_1,
      cost_2 * quantity_2 as total_2,
      (cost_1 * quantity_1 + cost_2 * quantity_2) as total_3
from data;

- Sarah

---

不适用于PostgreSQL！ - Suyash Gulati

肯定不行，PostgreSQL里面也不行。 - Idemax

Answer 5

你正在尝试在表达式中使用列别名。如果系统允许您这样做，那只是语法糖。这应该适用于任何SQL方言。

select 
 cost_1
,quantity_1
,cost_2
,quantity_2
,cost_1 * quantity_1 as total_1
,cost_2 * quantity_2 as total_2
,(cost_1 * quantity_1) + (cost_2 * quantity_2) as total_3 

from data;

- tponthieux

---

不适用于Postgres！ - Suyash Gulati