-1

I have a table with 15min resolution in every 24 hour and looking to extract an hour with highest value which is sum of the highest four 15 minutes

I can find greatest value, but need to have four consecutive 15 minutes which give the highest hour in 24 hours

here is a sample data enter image description here

Four results from 19:30 to 20:15 will result in a maximum value and the one hour consist of these four 15 minutes provide max hour value

Updated 4/4/2023 added Result for each consecutive rows and at the end get the max

select
item
,max(sum)
from(
    select
    ,result+lead(result),1,0) over (order by item,time)
    +lead(result),2,0) over (order by item,time)
    +lead(result),3,0) over (order by item,time) as sum
    from db
)
where 1=1
and time >= current_date -2 and time < current_date -1
and item like ('item1')
group by 1

Above code gives me the max value looking for

Need help with

  • Now I want to show other columns with the same max value
Amir Ahmadi
  • 1
  • 4
  • we can't help you. in order for us to help you, you need to show the data model, the code that you say you can do and be clear about what you need to do that you can't do now. – Hogan Apr 02 '23 at 16:16
  • I think with the edit it's clear now. How many records do you expect to have to run this query on? This can definitely be done but efficiency could be dicey. Also how is `Time` stored? Is it a `Time` datatype? (I hope!) – Emperor Eto Apr 02 '23 at 17:52

1 Answers1

0

Thanks Hogan I was not able to make the sample query work; the query might work on range of days and for different items. here I filtered one item and only one day to check the logic. My idea was to add - like what I did in excel (earlier edit) - and find the max original Date is like this enter image description here

Amir Ahmadi
  • 1
  • 4