PostgreSQL - 使用先前从同一查询计算的值

发布于 2024-10-18 13:14:27 字数 287 浏览 1 评论 0原文

我有以下 sql 查询：

SELECT (SELECT ...) AS X, (SELECT ...) AS Y from my_table

“X”很难计算，它用作计算 Y 的输入。但是，如果我尝试在计算 Y 的查询中引用 X，甚至在主查询中引用 X，我会得到以下结果错误消息：

Error: column "X" does not exist

计算后是否有任何方法可以引用 X？我真的不想计算两次，因为这看起来效率很低。

原文

I have the following sql query:

SELECT (SELECT ...) AS X, (SELECT ...) AS Y from my_table

'X' is quite hard to compute, and it's used as an input for computing Y. However, if I try to reference X within the query that computes Y or even within the main query I get the following error message:

Error: column "X" does not exist

Is there any way to reference X once it is computed? I really don't want to compute it twice, as this seems very inefficient.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

策马西风 2024-10-25 13:14:27

PostgreSQL 通常非常聪明，因为不必对同一件事进行两次计算。因此像这样的查询

SELECT (SELECT hard_to_compute FROM whatever) AS X,
       (SELECT hard_to_compute FROM whatever)*2 AS Y 
FROM my_table

应该只需要运行两个子查询一次。但是，如果查询是这样的

SELECT (SELECT hard_to_compute FROM whatever) AS X,
       (SELECT hard_to_compute*2 FROM whatever) AS Y 
FROM my_table

，那么优化器可能更难看到两个查询中的相同内容。

处理这个问题的另一种方法是创建一个 STABLE 函数来计算 X。只要输入相同，STABLE 函数总是在单个语句中返回相同的结果，因此 Postgres 知道它只需要运行一次。请参阅http://www.postgresql.org/docs/9.0/interactive/ xfunc-volatility.html。

PostgreSQL is generally pretty clever in not having to compute the same thing twice. So a query like

SELECT (SELECT hard_to_compute FROM whatever) AS X,
       (SELECT hard_to_compute FROM whatever)*2 AS Y 
FROM my_table

should only need to run the two subqueries once. However if the query is like

SELECT (SELECT hard_to_compute FROM whatever) AS X,
       (SELECT hard_to_compute*2 FROM whatever) AS Y 
FROM my_table

then that might be harder for the optimiser to see what is the same in the two queries.

Another way you can handle this is to create a STABLE function to calculate X. As long as the input is the same, STABLE functions always return the same result within a single statement, so Postgres knows it only needs to run it once. See http://www.postgresql.org/docs/9.0/interactive/xfunc-volatility.html.

回复收藏 0 原文

深空失忆 2024-10-25 13:14:27

子选择可能会为您做到这一点，例如：

select *, hard_to_compute * 2 as using_hard_to_compute \
from (select *, (i * i) as hard_to_compute from foo) bar;

 i | hard_to_compute | using_hard_to_compute 
---+-----------------+-----------------------
 1 |               1 |                     2
 2 |               4 |                     8
 3 |               9 |                    18

当然，i * i 并不难计算。我只是假装这是为了这个例子。

A subselect might do it for you, e.g.:

select *, hard_to_compute * 2 as using_hard_to_compute \
from (select *, (i * i) as hard_to_compute from foo) bar;

 i | hard_to_compute | using_hard_to_compute 
---+-----------------+-----------------------
 1 |               1 |                     2
 2 |               4 |                     8
 3 |               9 |                    18

Of course, i * i isn't really hard to compute. I'm just pretending it is for this example.

回复收藏 0 原文