Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Get max value for a month and the difference to the previous month

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading