假设我有一个按照日期
升序排列的表格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
的参考。
希望能得到一些帮助。谢谢!