I’m using PostgreSQL 9.6.
I have a table containing data from my electricity meter such as there is a row for each hour, this is just an example, so there many rows for each month:
| meterid | time | value |
|---|---|---|
| 1 | 2022-07-01 00:00:00 | 548 |
| 1 | 2022-07-01 01:00:00 | 549 |
| 1 | 2022-07-02 12:00:00 | 555 |
| 1 | 2022-08-01 04:00:00 | 650 |
| 1 | 2022-08-14 03:00:00 | 700 |
| 1 | 2022-09-02 14:00:00 | 821 |
I want to show the max meter value for each month (i.e. the value it has at the end of the month) and I want to show how much the value has changed for each month compared to the previous month.
So I want a result like this (this example is based on some other data)
| date | max | diff |
|---|---|---|
| 2023-01-01 | 1 | null |
| 2023-02-01 | 96 | 95 |
| 2023-03-01 | 289 | 193 |
I came up with this query but it contains 4 selects, is there any easier/better way to do this? It uses row_number to assign "1" to the max value for each month and then it uses lag to get the previous months value.
select date, max, max - lag "diff" from (
select date, max, lag(max) over (order by max) from (
select * from (
select date(date_trunc('month', time)), max(value), row_number() over (partition by date(date_trunc('month', time)) order by value desc) rn
from public.electricitymetering where "meterid" = '1'
group by date_trunc('month', time), value
order by date(date_trunc('month', time)) desc ) as result
where rn = 1
) as temp ) as final
>Solution :
Yes, there is a simpler way, based on this example of the lag function:
with monthly_maxes as (
select date(date_trunc('month', time)) as date, max(value) as max,
from public.electricitymetering where "meterid" = '1';
)
select date, max, max - lag(max, 1) OVER (ORDER BY date) diff
from monthly_maxes;