单条SQL实现表查询、移动平均计算及新记录插入

核心需求 #

• 查询匹配指定维度参数、Date字段为最大值的历史台账记录
• 存在历史记录时，结合历史数据与待新增记录数据，计算累计数量、移动平均单价
• 携带计算结果插入新记录，全流程通过单条SQL完成

原有代码问题 #

• 未使用INSERT ... SELECT标准语法，直接拼接查询和插入语句无法执行
• 最新记录筛选条件错误：需求要求取Date字段最大值，原有代码取的是CreatedAt最大值
• 未处理首次入库无历史记录的边界场景，查不到历史数据时会导致插入失败
• 缺失移动平均单价、库存金额的计算逻辑，插入字段值不完整

实现方案 #

移动加权平均采用存货核算标准公式：

新累计数量 = 历史累计数量 + 本次出入库数量
新库存总价值 = 历史累计数量 * 历史移动平均价 + 本次入库数量 * 本次单价
新移动平均价 = 新库存总价值 / 新累计数量

以下SQL适配当前使用的SAP HANA XSA/CAP环境（表名格式符合该环境规范），通过左关联处理无历史记录的场景，单条语句完成全流程：

INSERT INTO "minute.db.data::tables.MaterialLedger" (${sColumns})
SELECT
    -- 按${sColumns}的列顺序依次传入值、计算字段即可，以下为字段逻辑参考
    ${oEntity.ValidFrom},
    TO_UTCTIMESTAMP('9999-12-31 23:59:59'), -- 台账当前生效记录的ValidTo默认用9999-12-31，可按需替换
    ${iCreatedById},
    CURRENT_UTCTIMESTAMP,
    ${iChangedById},
    ${iCustomerId},
    ${oEntity.StorageLocationId},
    ${oEntity.Date},
    ${oEntity.ProductId},
    ${oEntity.Quantity},
    ${oEntity.UnitPrice},
    ${oEntity.UnitId},
    ${oEntity.CurrencyId},
    -- 计算本次记录的发生额
    ${oEntity.Quantity} * ${oEntity.UnitPrice} AS "Value",
    -- 计算累计数量，无历史记录时直接取本次数量
    COALESCE(history."QuantitySum", 0) + ${oEntity.Quantity} AS "QuantitySum",
    -- 计算移动平均价，无历史记录时直接取本次单价
    CASE
        WHEN COALESCE(history."QuantitySum", 0) = 0 THEN ${oEntity.UnitPrice}
        ELSE (COALESCE(history."QuantitySum", 0) * COALESCE(history."AverageUnitPrice", 0) + ${oEntity.Quantity} * ${oEntity.UnitPrice})
             / (COALESCE(history."QuantitySum", 0) + ${oEntity.Quantity})
    END AS "AverageUnitPrice"
FROM DUMMY -- HANA系统虚拟表，用于生成单行待插入数据
LEFT JOIN (
    -- 子查询：拉取指定维度下Date最大的最新历史记录
    SELECT "QuantitySum", "AverageUnitPrice"
    FROM "minute.db.data::tables.MaterialLedger"
    WHERE ("CustomerId", "StorageLocationId", "ProductId", "CurrencyId", "ReservedProjectPhase", "ReservedOrgUnit", "Date") IN (
        SELECT
            "CustomerId", "StorageLocationId", "ProductId", "CurrencyId", "ReservedProjectPhase", "ReservedOrgUnit", MAX("Date")
        FROM "minute.db.data::tables.MaterialLedger"
        WHERE "CustomerId" = ${iCustomerId}
          AND "StorageLocationId" = ${oEntity.StorageLocationId}
          AND "ProductId" = ${oEntity.ProductId}
          AND "CurrencyId" = ${oEntity.CurrencyId}
          AND "ReservedProjectPhase" = ${oEntity.ReservedProjectPhase}
          AND "ReservedOrgUnit" = ${oEntity.ReservedOrgUnit}
        GROUP BY "CustomerId", "StorageLocationId", "ProductId", "CurrencyId", "ReservedProjectPhase", "ReservedOrgUnit"
    )
) history ON 1=1;

注意事项 #

• 使用动态列名${sColumns}时，必须保证SELECT子句返回的字段顺序、数据类型与${sColumns}定义的列完全一致，否则会报字段不匹配错误
• 若业务上确实需要以CreatedAt而非业务日期Date判定最新记录，把MAX("Date")替换为MAX("CreatedAt")即可，逻辑不变
• 若出库场景允许数量为负，需要额外加判断避免累计数量为0时出现除零错误

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