sql - 在嵌套子查询中访问聚合

Question

我有一个包含 order_id 和 extended_price 列的 order_lines 表。我想知道扩展价格总和高于所有订单扩展价格总和平均值的订单。这是我得到的查询:

SELECT order_id, SUM(extended_price) AS "sumtotal"
FROM order_lines e
GROUP BY order_id
HAVING SUM(extended_price) > 
  (SELECT AVG(c.sumtotal) AS "avgtotal"
  FROM
    (SELECT order_id, SUM(extended_price) AS "sumtotal"
    FROM order_lines
    GROUP BY order_id) c
  )
ORDER BY sumtotal       

正如我们所见，我有一个子查询 c 来获取 sumtotal，它用于计算 avgtotal。但我正在运行与主查询相同的查询，以再次计算 sumtotal 并与 avgtotal 进行比较。有没有更好的方法来仅使用标准 SQL 功能来做到这一点。我正在使用 PostgreSQL。

Answer 1

一种方法是在子查询中的聚合函数上运行窗口函数:

SELECT order_id, sumtotal
FROM  (
   SELECT order_id
        , SUM(extended_price) AS sumtotal
        , AVG(SUM(extended_price)) OVER () AS avgtotal
   FROM   order_lines
   GROUP  BY order_id
   ) sub
WHERE  sumtotal > avgtotal;

应该会更快。

或者使用 CTE 来避免重复评估。不过，它会为实现临时结果增加一些成本。

WITH cte AS (
   SELECT order_id, SUM(extended_price) AS sumtotal
   FROM   order_lines
   GROUP  BY order_id
   )
SELECT order_id, sumtotal
FROM   cte
WHERE  sumtotal > (SELECT avg(sumtotal) FROM cte);

为了清晰起见，您可能会使用另一个 CTE 来计算平均值，但子查询通常成本更低。

相关:

• Postgres window function and group by exception
• Best way to get result count before LIMIT was applied
• PostgreSQL reusing computation result in select query