sql - 需要公式先前值的公式 (SQL Server 2017)
问题描述
我需要一个在前一行中引用自身的方程(如 excel 运行方程)。
我正在尝试对将持续到未来的每一天的库存加权平均成本进行计算。我有每日库存、开始记录时库存的原始成本、新库存(很少发生)以及新库存的价值。我的问题是,一旦我计算了第一个库存,我如何在下次库存进入时自己参考该加权值?
我试图模仿加权平均成本的 Excel 方程:
=((A3*B3)+(C3*D2))/(A3+C3)
A3:进货库存
B3:进货存货成本
C3:当前库存
D2:以前的加权平均库存成本
D1:一些预先确定的库存起始成本
我的 SQL 目前对于其他事情来说是庞大且令人讨厌的,但我会尝试整合。
INSERT INTO #inv_temp_tbl (date_inventory,product_type,curr_qoh,qty_receive,cost_receive)
VALUES ('2019/01/01','shirt',758,NULL,NULL)
,('2019/01/02','shirt',755,NULL,NULL)
,('2019/01/03','shirt',720,240,2.05)
,('2019/01/03','shirt',720,380,2.05)
,('2019/01/04','shirt',1220,NULL,NULL)
,('2019/01/05','shirt',1201,NULL,NULL)
,('2019/01/06','shirt',1005,500,3.00)
,('2019/01/07','shirt',1450,NULL,NULL)
--Note: can have multiple ads on a single day although price will never be different for these same day adds, and the next day usually has to account for the new inventory and sold products
WITH cte1 AS (
SELECT date_inventory
,product_type
,cost_receive
,ROW_NUMBER() OVER (PARTITION BY product_type ORDER BY date_inventory) AS rn1
FROM #inv_temp_tbl
WHERE cost_receive IS NOT NULL
)
,cte2 AS (
SELECT date_inventory
,product_type
,FIRST_VALUE(cost_receive) OVER (PARTITION BY product_type ORDER BY date_inventory, product_type) AS cost_receive_curr_day
)
SELECT date_inventory
,product_type
,curr_qoh
,SUM(qty_receive)
,MAX(cost_receive)
,CASE WHEN row_group = 0 THEN originial_cost ELSE "some equation" END AS weight_avg_cost
FROM #inv_temp_tbl temp
LEFT JOIN cte2 ON cte2.date = temp.date
AND cte2.product_type = temp.product_type
LEFT JOIN (SELECT product_type
,cost_receive
FROM cte1
WHERE rn1 = 1) as cte1_a ON cte1_a.product_type = temp.product_type
我认为这就是解决我的问题所需的全部内容,如果需要更多信息,请告诉我(这是我在 stackoverflow 上寻求帮助的第一篇文章)。在过去的两天里,我一直被困在这个问题上,我觉得我错过了一些愚蠢的明显解决方案(我的大脑也在它上面变成了果冻),但我似乎找不到它。
** 编辑:我添加了数据并意识到我应该添加第一个 CTE 来解释第二个 cte,因为这就是我获得库存初始值的方式(不是一个好方法,但商定了公司的起始值)。
** edit2:修复了 cte1 连接和一个愚蠢的错误
解决方案
推荐阅读
- python - 使用二阶 ODE 在 Python 中实现 3/8 Runge Kutta
- sql - SQL查询按值总和降序
- docker - 在 docker 容器内启动 cron 服务
- excel - Excel Power Pivot / Power Query 和数据透视表中每种类型每季度的里程碑
- google-apps-script - 未检索到使用 Google Apps 脚本更新的文件说明
- apache-spark - 从另一行填充缺失值
- python - 根据发布请求删除数据库中的对象
- arrays - 使用 Swift 过滤嵌套数组
- javascript - 单击导航链接后无法关闭全屏菜单
- css - 当屏幕变小时,避免在带有图标的按钮中换行