说我有一个表items按date排序
| 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
感谢一些帮助。谢谢！
分析解答
您的HUNCH使用分析功能是现实的。我建议使用此版本：
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。