1

Current sql code will sum up all the max island sizes into one. I'm trying to only find what the max island size is.

 select deviceid, grp -- min(readdate) as mn, max(readdate) as mx
       from (  select deviceid, readdate, sum(case when prev_rd >= readdate - interval '1 hour' then 1 else 0 end) over (partition by deviceid order by readdate rows between unbounded preceding and current row) as grp
               from (
                    select deviceid, readdate, lag(readdate) over (partition by deviceid order by readdate) as prev_rd
                    from metersWithExcess
                    order by readdate)
            ) tt
            group by deviceid, readdate, grp
           -- having mx > mn + 1 * interval '1 hour'

Is there a way to return back the deviceid with it's max size? I am trying to find the longest continuous hourly readings

the data looks like this:

    deviceid    readdate
91428   2018-08-27 18:04:42
91428   2018-08-27 17:04:42
91428   2018-08-27 16:04:42
91428   2018-08-27 12:04:42
91428   2018-08-27 11:04:42
91428   2018-08-26 20:04:42
91428   2018-08-26 17:04:40
91428   2018-08-26 16:04:40
91428   2018-08-26 15:04:40
91428   2018-08-26 12:04:40
91489   2018-08-26 06:00:49
91493   2018-08-27 06:58:44
91511   2018-08-27 12:57:01
91511   2018-08-27 10:57:01
91511   2018-08-27 09:57:01
91511   2018-08-26 23:57:01
91511   2018-08-26 22:57:01
91511   2018-08-26 20:57:01
91511   2018-08-26 13:56:59
91511   2018-08-26 12:56:59
91511   2018-08-26 10:56:59
91547   2018-08-26 16:59:07
91547   2018-08-26 15:59:07
91547   2018-08-26 14:59:07
91547   2018-08-26 11:59:07
91547   2018-08-26 04:59:07
91582   2018-08-27 12:01:45
91582   2018-08-26 19:01:45

in this example: 91428 would have a max size of 3 The other devices would be similar in that they will contain the max number of continuous hours

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.