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
相关产品推荐
相关产品推荐

