Search Postgresql Archives

Re: Given N, finding the interval of N hours with max(sum(..))

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I have found an initial solution. For an interval N of 3 hours the query can be:

select distinct poi,first_value(start_date) OVER w as start_date,first_value(end_date) OVER w as end_date,first_value(the_sum) OVER w from

(select poi,t1.ts as start_date,
       t1.ts + '3 hour'::interval as end_date,
       (select sum(apcpsfc)
       from event_forecast_data t2
       where t2.ts >= t1.ts
               and t2.ts <= t1.ts + '3 hour'::interval and poi = t1.poi
        group by poi
               ) as the_sum
from event_forecast_data t1
where ts > '2010-06-18 00:00:00' and ts + interval '3 hours' <= '2010-06-19 00:00:00'

group by poi,ts) as stats

    WINDOW w AS (partition by poi order by the_sum desc)


order by poi,start_date

where initially I get the sum for all intervals of 3 hours of the day and after with the window functions I get the row with the higher value ...

2010/6/17 stefano bonnin <stefano.bonnin@xxxxxxxxx>
Hi all,
I'm trying to make a query that, given N and a date, gives me the interval of N hours with the max(sum(...)). In others words, given the following table:

2010-06-16 00:00:00 | 0
2010-06-16 01:00:00 | 2
2010-06-16 02:00:00 | 1
2010-06-16 03:00:00 | 5
2010-06-16 04:00:00 | 7
2010-06-16 05:00:00 | 9
2010-06-16 06:00:00 | 2
2010-06-16 07:00:00 | 0
2010-06-16 08:00:00 | 0
2010-06-16 09:00:00 | 0
2010-06-16 10:00:00 | 1
2010-06-16 11:00:00 | 2
2010-06-16 12:00:00 | 1
2010-06-16 13:00:00 | 1
2010-06-16 14:00:00 | 1
2010-06-16 15:00:00 | 1
2010-06-16 16:00:00 | 1
2010-06-16 17:00:00 | 1
2010-06-16 18:00:00 | 1
2010-06-16 19:00:00 | 1
2010-06-16 20:00:00 | 1
2010-06-16 21:00:00 | 0
2010-06-16 22:00:00 | 3
2010-06-16 23:00:00 | 1

With N = 3 and date = '2010-06-16' for example ... I'd like that the "query" gives me as response: the interval from 3AM and 5AM.
Any solutions and/or hints?

Thanks in advance.
stefano


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux