I have a table:
id date val
1 10.08.2022 10
1 12.08.2022 11
1 08.08.2022 15
1 16.08.2022 9
2 02.07.2022 2
2 01.07.2022 4
2 30.07.2022 7
I want to create two new columns last_v and max_v which are equal to last val for each id by date and maximum val per id. So desired output is:
id date val last_v max_v
1 10.08.2022 10 9 15
1 12.08.2022 11 9 15
1 08.08.2022 15 9 15
1 16.08.2022 9 9 15
2 02.07.2022 2 2 7
2 01.07.2022 4 2 7
2 30.06.2022 7 2 7
How could I do that?
You can use window functions!
select t.*,
first_value(val) over(partition by id order by dt desc) last_val,
max(val) over(partition by id) max_val
from mytable t
| id | dt | val | last_val | max_val |
|---|---|---|---|---|
| 1 | 2022-08-08 | 15 | 9 | 15 |
| 1 | 2022-08-10 | 10 | 9 | 15 |
| 1 | 2022-08-12 | 11 | 9 | 15 |
| 1 | 2022-08-16 | 9 | 9 | 15 |
| 2 | 2022-06-30 | 7 | 2 | 7 |
| 2 | 2022-07-01 | 4 | 2 | 7 |
| 2 | 2022-07-02 | 2 | 2 | 7 |