假设我有一个按date升序排序的表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 |
我已经try 过这样的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
希望能得到一些帮助.谢谢!