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

Question

我在使用 postgres 中的计算列时遇到问题。下面给出了在 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不存在。

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 中运行）

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 ║
╚═════════╩═════════════╩═════════╩═════════════╩══════════╩══════════╩═════════╝

Answer 3

一般来说，关于 SELECT 子句，您需要了解两件事：

• 虽然它是first编写的，但它是last计算的， ORDER BY 子句除外。这就是为什么您不能在任何其他子句（特别是 WHERE 子句）中使用任何计算字段或别名，ORDER BY 子句中的除外。
• SELECT 子句中的计算是并行执行的，或者至少按照并行方式进行处理。这就是为什么您不能将一种计算用作另一种计算的一部分。

所以，简短的回答是你不能，这是设计使然的。

值得注意的例外是 Microsoft Access，您确实可以在后续列和 WHERE 子句中使用计算。然而，虽然这很方便，但实际上并不是一个优势：不遵循上述原则效率较低。但对于轻型数据库来说这是可以的，这正是 Access 应该用于的用途。

如果您确实想要重用计算结果，则需要一个单独的查询，可以是子查询的形式，也可以是公共表表达式的形式。 CTE 更容易使用，因为它们更易于阅读。

编辑

以下是使用计算列可能导致混乱的示例。在澳大利亚，我们以厘米为单位测量身高，但仍有一些地方使用古老的英寸（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值。

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;

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;