I have a sales table as this:
| date | Item | net_sale |
|---|---|---|
| 2023/01/02 | Milk | 500 |
| 2023/01/03 | Milk | 700 |
| 2023/01/04 | Milk | 600 |
| 2023/01/05 | Milk | 300 |
| 2023/01/06 | Milk | 1100 |
| 2023/01/09 | Milk | 900 |
| 2023/01/10 | Milk | 1000 |
| 2023/01/11 | Milk | 800 |
I want a join query, which shows net change in sales compare to previous day, considering weekend is closed so no data. (e.g. 7th and 8th in table)
My desired output should be like this:
| date | Item | net_sale | change |
|---|---|---|---|
| 2023/01/02 | Milk | 500 | NULL |
| 2023/01/03 | Milk | 700 | 200 |
| 2023/01/04 | Milk | 600 | -100 |
| 2023/01/05 | Milk | 300 | -300 |
| 2023/01/06 | Milk | 1100 | 800 |
| 2023/01/09 | Milk | 900 | -200 |
| 2023/01/10 | Milk | 1000 | 100 |
| 2023/01/11 | Milk | 800 | -200 |
CodePudding user response：
You can use the LAG window function to get the previous "net_sale" value and subtract it from your current value.
SELECT *,
net_sale - LAG(net_sale) OVER(PARTITION BY Item ORDER BY date) AS change
FROM tab
Check the demo here.