MySQL - 如何查询涉及先前计算值的表结果

Question

MySQL - 如何查询涉及先前计算值的表结果

3

假设我有一个按照日期升序排列的表格items

date	item	quantity	cost
2022-12-01	Pencil	12	10.00
2022-12-02	Pencil	10	10.00
2022-12-04	Pencil	5	10.00
2022-12-06	Eraser	10	4.00
2022-12-10	Eraser	50	4.00
2022-12-15	Eraser	25	4.00

我需要编写一个SQL查询，返回一个名为calculated_cost的计算字段，其中表达式简单地是quantity*cost

然后我将需要为每一行增加calculated_cost并将其保存到一个名为accumulated_cost的字段中

然而这里的挑战是，我还需要存储一个名为previous_accumulated_cost的字段，它取前面的accumulated_cost并将其存储为一个值。

请注意，我还需要根据item进行分区并按date排序来计算这些内容，这意味着当我到达新的项目时，我需要重置accumulated_cost和previous_accumulated_cost

基本上我需要生成这样的输出。

date	item	quantity	cost	calculated_cost	accumulated_cost	previous_accumulated_cost
2022-12-01	Pencil	12	10.00	120.00	120.00	0.00
2022-12-02	Pencil	10	10.00	100.00	220.00	120.00
2022-12-04	Pencil	5	10.00	50.00	270.00	220.00
2022-12-06	Eraser	10	4.00	40.00	40.00	0.00
2022-12-10	Eraser	50	4.00	200.00	240.00	40.00
2022-12-15	Eraser	25	4.00	100.00	340.00	240.00

我已经尝试过像这样的SQL查询

SELECT *,
   (i.quantity * i.cost) AS calculated_cost,
   SUM(i.quantity * i.cost) OVER (PARTITION BY i.item ORDER BY i.date) AS accumulated_cost,
   IFNULL(LAG(i2.accumulated_cost) OVER (PARTITION BY i.item ORDER BY i.date), 0) AS previous_accumulated_cost
FROM items i
LEFT JOIN (
   SELECT item, SUM(quantity * cost) OVER (PARTITION BY item ORDER BY date) AS accumulated_cost
   FROM items 
) i2 ON i.item = i2.item

然而，这个方法是行不通的，因为条目数量可能会持续增加，我也不确定如何保持对previous_accumulated_cost的参考。

希望能得到一些帮助。谢谢！

- lyracarat03

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Tim Biegeleisen · Accepted Answer

你认为使用分析函数是正确的。我建议使用以下版本：

SELECT
    quantity * cost AS calculated_cost,
    SUM(quantity * cost) OVER (PARTITION BY item ORDER BY date) AS accumulated_cost,
    SUM(quantity * cost) OVER (PARTITION BY item ORDER BY date) - (quantity * cost) AS previous_accumulated_cost
FROM items
ORDER BY item, date;

感谢您理解，previous_accumulated_cost只是accumulated_cost减去calculated_cost的结果。