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

PostgreSQL: using a calculated column in the same query

提问by user1146150

I am having trouble using a calculated column in postgres. A similar code which works in SQL is given below, is it possible to recreate this in PostgreSQL?

我在 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;

In PostgreSQLa similar code returns the error that:

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

the column total_1 and total_2 do not exist.

列 total_1 和 total_2 不存在。

回答by a_horse_with_no_name

You need to wrap the SELECT statement into a derived table in order to be able to access the column alias:

您需要将 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

There won't be any performance penalty on that.

不会有任何性能损失。

(I'm reallysurprised that your original SQL statement runs at all in a DBMS)

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

回答by Lukasz Szozda

If you don't like wraping entire query with outerquery, you could use LATERALto calculate intermediate total_1and total_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 Demo

DBFiddle 演示

Output:

输出：

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

回答by Manngo

As a rule, there a two things you need to know about the SELECTclause:

通常，您需要了解有关该SELECT条款的两件事：

• Although it is written first, it is evaluated last, with the exception of the ORDER BYclause. This is why you cannot use any calculated fields or aliases in any other clause (particularly the WHEREclause) exceptin the ORDER BYclause.
• Calculations in the SELECTclause are performed in parallel, or at least are handled as if they are. This is why you cannot use one calculation as part of another.

• 尽管它是最先写的，但它是最后计算的，除了ORDER BY子句。这就是为什么您不能在任何其他子句（特别是WHERE子句）中使用任何计算字段或别名的原因，除了在ORDER BY子句中。
• SELECT子句中的计算是并行执行的，或者至少像它们一样处理。这就是您不能将一个计算用作另一个计算的一部分的原因。

So, the short answer is that you can't, and that is by design.

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

The notable exception to this is Microsoft Access, where you can indeed use calculations in subsequent columns and WHEREclauses. However, although that is convenient, it's not actually an advantage: not following the above principals is less efficient. But it's OK for light duty databases, which is what Access is supposed to be used for.

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

If you really want re-use calculated results, you will need a separate query, either in the form of a sub-query or as a Common Table Expression. CTEs are much easier to work with, as they are clearer to read.

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

Edit

编辑

Here is an example why using calculated columns could cause confusion. In Australia we measure height in centimetres, but there still some places which use the ancient inches (1 in = 2.54 cm).

这是一个示例，为什么使用计算列可能会导致混淆。在澳大利亚，我们以厘米为单位测量身高，但仍有一些地方使用古老的英寸（1 英寸 = 2.54 厘米）。

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

Here the CASEstill uses the original heightvalue.

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

回答by Sarah

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;

回答by tponthieux

You're trying to use column aliases in an expression. If a system allows you to do that it's just syntactic sugar. This should work in any SQL dialect.

您正在尝试在表达式中使用列别名。如果一个系统允许你这样做，它只是语法糖。这应该适用于任何 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;