SQL代码简化求助：如何避免重复读取聚合期末库存

嘿，这个需求我刚好有成熟的简化方案，核心就是只计算一次全局聚合值，然后复用它来生成新增记录，彻底避免重复扫描表或者重复计算的问题。下面给你拆解具体思路和代码示例：

核心技巧：用CTE预计算聚合值 #

先通过公共表表达式（CTE）一次性算出所有location的期末库存总和，之后所有需要用到这个值的地方直接引用就行，不用再重复执行SUM操作。

方案1：清晰直观的UNION ALL写法 #

WITH global_total AS (
    -- 只算一次全局期末库存总和
    SELECT SUM(closing_stock) AS total_closing
    FROM your_inventory_table  -- 替换成你的实际表名
)
-- 第一步：取符合条件的原始数据
SELECT location, closing_stock
FROM your_inventory_table
WHERE location > 10
UNION ALL
-- 第二步：生成location=800的记录，复用预计算的总和
SELECT 800 AS location, total_closing AS closing_stock
FROM global_total
UNION ALL
-- 第三步：生成location=810的记录，同样复用总和
SELECT 810 AS location, total_closing AS closing_stock
FROM global_total;

方案2：更紧凑的VALUES子句写法 #

如果觉得写两次UNION ALL有点冗余，可以用VALUES子句一次性生成两个新增的location值，再和预计算的聚合值做关联，代码更简洁：

WITH global_total AS (
    SELECT SUM(closing_stock) AS total_closing
    FROM your_inventory_table
)
SELECT location, closing_stock
FROM your_inventory_table
WHERE location > 10
UNION ALL
-- 用VALUES批量生成新增的location，关联聚合值
SELECT new_loc, total_closing
FROM global_total,
     (VALUES (800), (810)) AS new_locations(new_loc);

为什么这两种方案更好？ #

• 性能优化：CTE里的SUM只执行一次，数据库只需要扫描表一次计算聚合值，后面的引用都是直接用这个结果，避免了重复扫描大表的开销。
• 代码可维护性：聚合逻辑集中在CTE里，后续如果需要调整聚合规则（比如加过滤条件、改SUM为AVG），只需要修改CTE部分，不用改多个地方。
• 避免数据不一致：如果多次计算聚合值，万一中间表数据有变化（比如并发写入），可能导致不同行的聚合值不一致，预计算一次就不会有这个问题。

注意事项 #

如果你的原始数据有额外过滤条件（比如特定日期的期末库存），一定要确保CTE里的SUM和主查询的过滤条件一致。比如原查询是WHERE location > 10 AND stock_date = '2024-06-30'，那CTE里也要加上AND stock_date = '2024-06-30'，不然聚合值会不符合预期。

内容的提问来源于stack exchange，提问作者Raghu