Say i have a table items sorted ascending by 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 |
I need to write an SQL query that returns a calculated field called calculated_cost, where the expression is simply quantity * cost
Then I will need to increment calculated_cost for every row and save it to a field called accumulated_cost
However here is the challenge, I am also required to store a field called previous_accumulated_cost where it takes the preceding accumulated_cost and store it as a value.
Note that I also need to calculate these by partitioning based on item and order by date, means i need to reset the accumulated_cost and previous_accumulated_cost when I reach a new item.
Basically i need to generate an output like this.
| 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 |
I have already tried an SQL query like this
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
However this doesn't work, as the number of item entries can keep increasing and I am not sure how to keep referencing back the previous_accumulated_cost
Would appreciate some help. Thanks!
CodePudding user response：
Your hunch to use analytic functions was spot on. I would suggest using this version:
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;
Appreciate that the previous_accumulated_cost is simply the accumulated_cost minus the calculated_cost.