PostgreSQL技术问询：如何对经关联分组后的结果表执行减法运算

你可以通过**CTE（公共表表达式）**将入库、出库的统计逻辑封装到同一个SQL语句中，直接生成最终的库存物化视图，无需中间临时视图。核心思路是先在CTE中分别完成入库和出库的INNER JOIN+GROUP BY统计，再关联两个统计结果计算库存。

示例SQL（匹配常见出入库场景） #

假设你的基础表结构为：

• produtos：商品表（id, nome）
• entradas：入库表（produto_id, quantidade, 关联其他业务表如采购单）
• saidas：出库表（produto_id, quantidade, 关联其他业务表如销售单）

合并后的物化视图创建语句如下：

CREATE MATERIALIZED VIEW estoque AS
WITH entrada_agregada AS (
    -- 入库统计：按商品分组计算总入库量（包含你的INNER JOIN逻辑）
    SELECT 
        p.id AS produto_id,
        p.nome AS produto_nome,
        COALESCE(SUM(e.quantidade), 0) AS total_entrada
    FROM produtos p
    -- 替换成你原来入库统计的INNER JOIN关联逻辑
    INNER JOIN entradas e ON p.id = e.produto_id
    INNER JOIN pedidos_compra pc ON e.pedido_id = pc.id
    WHERE pc.status = 'concluido' -- 业务过滤条件
    GROUP BY p.id, p.nome
),
saida_agregada AS (
    -- 出库统计：按商品分组计算总出库量（包含你的INNER JOIN逻辑）
    SELECT 
        p.id AS produto_id,
        COALESCE(SUM(s.quantidade), 0) AS total_saida
    FROM produtos p
    -- 替换成你原来出库统计的INNER JOIN关联逻辑
    INNER JOIN saidas s ON p.id = s.produto_id
    INNER JOIN pedidos_venda pv ON s.pedido_id = pv.id
    WHERE pv.status = 'entregue' -- 业务过滤条件
    GROUP BY p.id
)
-- 关联两个统计结果，计算实时库存
SELECT
    ea.produto_id,
    ea.produto_nome,
    ea.total_entrada,
    sa.total_saida,
    (ea.total_entrada - sa.total_saida) AS quantidade_estoque
FROM entrada_agregada ea
-- 根据业务需求选择JOIN类型：INNER JOIN只保留有出入库记录的商品；LEFT JOIN保留所有入库商品（即使无出库）
INNER JOIN saida_agregada sa ON ea.produto_id = sa.produto_id
ORDER BY ea.produto_id;

关键说明 #

1. CTE替代临时视图：entrada_agregada和saida_agregada两个CTE完全替代了你原来的两个物化视图，把统计逻辑内联到主查询中。
2. 处理空值：用COALESCE将SUM可能返回的NULL转为0，避免库存计算出现NULL值。
3. JOIN类型选择：
   • 用INNER JOIN：只保留同时有入库和出库记录的商品；
   • 用LEFT JOIN：保留所有有入库记录的商品（即使没有出库，出库量显示为0）；
   • 用FULL JOIN：保留所有有入库或出库记录的商品（适合需要覆盖全量商品的场景）。
4. 刷新物化视图：如果需要更新库存数据，执行：

   -- 普通刷新（会锁表）
   REFRESH MATERIALIZED VIEW estoque;
   -- 并发刷新（需先给物化视图创建唯一索引）
   REFRESH MATERIALIZED VIEW CONCURRENTLY estoque;
   

适配你的可复现脚本 #

如果你的分步脚本中有特定的表关联或过滤条件，只需将CTE中的INNER JOIN和WHERE部分替换为你原有的逻辑即可，核心结构保持一致。

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