I have the following data:
| ID | Date | Total |
|---|---|---|
| A | 2021-09-03 | 0 |
| A | 2021-09-04 | 12 |
| A | 2021-09-05 | 37 |
| A | 2021-09-06 | 40 |
| B | 2021-08-03 | 20 |
| B | 2021-08-04 | 43 |
| B | 2021-08-05 | 75 |
And from this data, I would like to get the following table:
| ID | Date | Total | Daily |
|---|---|---|---|
| A | 2021-09-03 | 0 | 0 |
| A | 2021-09-04 | 12 | 12 |
| A | 2021-09-05 | 37 | 25 |
| A | 2021-09-06 | 40 | 3 |
| B | 2021-08-03 | 20 | 20 |
| B | 2021-08-04 | 43 | 23 |
| B | 2021-08-05 | 75 | 32 |
I tried the following code, but I get a lot of duplicates:
CREATE TABLE table_b AS (
SELECT a.ID, a.Date, a.Total,
a.Total - coalesce(b.Total, 0) AS Daily
FROM table_a a left outer JOIN table_a b
ON (a.ID = b.ID AND b.Date < a.Date));
Thanks in advance:)
CodePudding user response：
SELECT *,
Total - COALESCE(LAG(Total) OVER (PARTITION BY id ORDER BY `date`), 0) Daily
FROM src_table