You need to enable JavaScript to run this app. PostgreSQL中基于LAG函数实现时序连续库存计算的查询问题
优惠活动
大模型
产品
解决方案
定价
更多

PostgreSQL中基于LAG函数实现时序连续库存计算的查询问题

核心问题原因

原查询存在三个无法得到正确结果的硬伤:

  • 同层级SELECT别名自引用无效:SQL执行逻辑中,窗口函数的计算早于SELECT列表的别名赋值阶段,lag(ending_stock, 1)中的ending_stock是当前查询临时生成的别名,并非表内真实字段,无法被窗口函数直接识别取值,会触发字段不存在的报错。
  • 窗口分区维度错误:仅按origin做分区的逻辑,会将同一出口国对应不同目的国的贸易链路数据归为同一窗口,不符合“按origin+desti组成的独立贸易链路计算”的要求。
  • 未识别计算逻辑的可化简性:逐期结转的库存值本质是累计销量的线性推导,不需要依赖逐行自引用时序值即可实现,可避开循环依赖的问题。
正确实现方案

逻辑推导

按照库存结转规则逐期展开计算关系:

同贸易链路按时间排序的首期记录:beginning_stock = 0,ending_stock = 0 - 当期销量q
后续每一期:beginning_stock = 上一期ending_stock,ending_stock = 当期beginning_stock - 当期q

展开后可得到无循环依赖的通用公式:

  • 任意期的ending_stock = -(截止当期的累计销量总和)
  • 任意期的beginning_stock = -(截止上一期的累计销量总和),首期无前置记录时累计值为null,转换为0即可满足初始值要求。

实现代码

CREATE OR REPLACE VIEW market_research.v_tonnage_stock AS
SELECT
    origin,
    desti,
    yr,
    mt,
    q,
    -- 期初库存:取截止上一期的累计销量负值,首期无前置记录时置0
    COALESCE(
        - SUM(q) OVER (
            PARTITION BY origin, desti
            ORDER BY yr, mt
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ),
        0
    ) AS beginning_stock,
    -- 期末库存:取截止当期的累计销量负值
    - SUM(q) OVER (
        PARTITION BY origin, desti
        ORDER BY yr, mt
    ) AS ending_stock
FROM market_research.test_tonnage;

效果验证

上述代码对示例数据的计算结果完全匹配预期:

  • toto-coucou链路:2019年1月期初0、期末-1.4;2月期初-1.4、期末-3.9;3月期初-3.9、期末-5.1
  • tata-yoyo链路:2018年11月期初0、期末-5.4;12月期初-5.4、期末-10.9;2019年1月期初-10.9、期末-16.1

内容的提问来源于stack exchange,提问作者Max

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.28 10:36:04