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;

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

列 total _ 1和 total _ 2不存在。

78467 次浏览
小开

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.

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;
小开

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

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 really surprised that your original SQL statement runs at all in a DBMS)

小开

If you don't like wraping entire query with outerquery, you could use LATERAL to calculate intermediate total_1 and 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

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

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

  • Although it is written first, it is evaluated last, with the exception of the ORDER BY clause. This is why you cannot use any calculated fields or aliases in any other clause (particularly the WHERE clause) except in the ORDER BY clause.
  • Calculations in the SELECT clause 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.

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 WHERE clauses. 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.

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.

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).

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

Here the CASE still uses the original height value.

小开 
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;

提交答案