SQL窗口函数：返回多个相同的avg()对性能的影响？

Question

我想从表A中选择一些行，同时也要得到像avg(A.price)和avg(A.distance)这样的聚合函数的结果。

现在，SELECT查询需要花费很长时间，因此我不想运行两个查询分别获取行和平均值。如果那样做，我将运行两次查询来选择适当的行。

但是，在查看PostgreSQL窗口函数文档（http://www.postgresql.org/docs/9.1/static/tutorial-window.html）时，似乎使用窗口函数来返回我想要使用的聚合函数的结果以及返回的行意味着每个返回的行都包含聚合函数的结果。在我的情况下，由于聚合是在主SELECT查询返回的所有行而不是其行的子集上进行的，因此这显得很浪费。

如果返回相同的avg()多次，会有什么性能影响，特别是在我选择A表的子集但对整个子集执行聚合查询的情况下？具体来说，Postgres是否会每次重新计算平均值，还是会以某种方式缓存平均值？

类比一下：如果您查看窗口函数文档，并假装depname对于SELECT查询返回的每一行都是“develop”，并且由于平均值是在返回的所有行中计算得出的，因此每行的平均值相同。那么这个平均值会被计算多少次？

- skyw

---

如果你对此进行基准测试，你肯定会发现PostgreSQL足够聪明，不会为每一行重新计算平均值。你试过了吗？ - PinnyM

我不确定，但我猜测Postgres只有在“frame”不同时才重新计算窗口函数的值。如果该框架被定义为包括结果集中的所有行，则永远不会发生这种情况，因此相同的值将再次输出。 - IMSoP

Answer 1

根据文档的第7.2.4节所述：

当使用多个窗口函数时，所有具有在其窗口定义中语法上等效的PARTITION BY和ORDER BY子句的窗口函数都保证在对数据进行单次遍历时进行评估。

- John Velonis

Answer 2

您可以使用公共表表达式（CTE）来实现您想要的功能。根据Postgres 文档：

WITH查询的一个有用特性是，它们仅在父查询的每次执行中评估一次，即使它们被父查询或兄弟WITH查询多次引用。因此，需要在多个地方使用的昂贵计算可以放置在WITH查询中，以避免冗余工作。另一个可能的应用是防止具有副作用函数的不必要多次评估。但是，这个硬币的另一面是优化器不太能将约束从父查询推入WITH查询中，而是普通子查询更容易做到。WITH查询通常会按照所述进行评估，而不会抑制父查询之后可能丢弃的行。（但是，如上所述，如果对查询的引用仅要求有限数量的行，则评估可能会提前停止。）

您可以使用以下结构来结构化您的最终结果：

with cte as (your basic select goes here)
select *
from cte cross join
     (select averages here
      from cte
     ) const
where < your row filter here>

- Gordon Linoff

---

谢谢，这绝对有效。然而，一个输出等效的窗口函数出于某种原因对我来说更快。了解WITH很好，但我想知道如何确定为什么窗口函数更快。 - skyw

@zach...如果我要猜测为什么窗口函数更快，那可能是因为这个版本保存了所有数据（未经过滤），而窗口函数版本则没有。 - Gordon Linoff