如何优化SQL SELECT语句中重复的SUM聚合函数计算

你给出的原SQL代码如下：

select sum(order_qty) as totalOrderQty, sum(stock_qty) as totalStockQty, sum(order_qty) - sum(stock_qty) as totalSubtract from inventory

首先明确结论：
大部分主流数据库（MySQL、PostgreSQL、Oracle、SQL Server等）的查询优化器已经会自动识别重复的同参数聚合函数，只会实际执行一次SUM计算，重复出现的位置直接复用结果，不会额外消耗性能。如果要从代码层面显式消除聚合函数的重复编写，也可以通过子查询或者CTE实现。

显式合并聚合运算的实现写法 #

写法1：子查询实现（兼容所有SQL数据库） #

select 
  totalOrderQty,
  totalStockQty,
  totalOrderQty - totalStockQty as totalSubtract
from (
  select 
    sum(order_qty) as totalOrderQty,
    sum(stock_qty) as totalStockQty
  from inventory
) as agg_temp

写法2：CTE实现（适配支持公共表表达式的数据库，如MySQL8.0+、PostgreSQL、Oracle等） #

with inventory_agg as (
  select 
    sum(order_qty) as totalOrderQty,
    sum(stock_qty) as totalStockQty
  from inventory
)
select 
  totalOrderQty,
  totalStockQty,
  totalOrderQty - totalStockQty as totalSubtract
from inventory_agg

上述两种写法和原SQL执行效率几乎无差异，核心作用是减少代码冗余，当聚合逻辑复杂、重复调用次数多的时候可以明显降低维护成本。

内容的提问来源于stack exchange，提问作者HyoD.k