Good candidate query for window syntax?

Поиск
Список
Период
Сортировка
От Ketema Harris
Тема Good candidate query for window syntax?
Дата
Msg-id 716E6AFD-4330-4032-8D92-DE90CEE0EA90@gmail.com
обсуждение исходный текст
Ответы Re: Good candidate query for window syntax?  (Jorge Godoy <jgodoy@gmail.com>)
Re: Good candidate query for window syntax?  (Dimitri Fontaine <dfontaine@hi-media.com>)
Список pgsql-general
Hello,  I have a table defined as:

CREATE TABLE demo AS
(
    id serial PRIMARY KEY,
    start_time timestamp without timezone,
    duration integer
)

A sample data set I am working with is:

      start_time      | duration |       end_time
---------------------+----------+---------------------
 2006-08-28 16:55:11 |       94 | 2006-08-28 16:56:45
 2006-08-28 16:56:00 |       63 | 2006-08-28 16:57:03
 2006-08-28 16:56:02 |       25 | 2006-08-28 16:56:27
 2006-08-28 16:56:20 |       11 | 2006-08-28 16:56:31
 2006-08-28 16:56:20 |       76 | 2006-08-28 16:57:36
 2006-08-28 16:56:29 |       67 | 2006-08-28 16:57:36
 2006-08-28 16:56:45 |       21 | 2006-08-28 16:57:06
 2006-08-28 16:56:50 |       44 | 2006-08-28 16:57:34
 2006-08-28 16:56:50 |       36 | 2006-08-28 16:57:26
 2006-08-28 16:56:53 |       26 | 2006-08-28 16:57:19
 2006-08-28 16:56:57 |       55 | 2006-08-28 16:57:52
 2006-08-28 16:57:28 |        1 | 2006-08-28 16:57:29
 2006-08-28 16:57:42 |       17 | 2006-08-28 16:57:59
 2006-08-28 16:57:46 |       28 | 2006-08-28 16:58:14
 2006-08-28 16:58:25 |       51 | 2006-08-28 16:59:16
 2006-08-28 16:58:31 |       20 | 2006-08-28 16:58:51
 2006-08-28 16:58:35 |       27 | 2006-08-28 16:59:02

generated by the query:
SELECT start_time, duration, to_timestamp((extract(epoch from start_time) + duration))::timestamp as end_time
FROM demo
ORDER BY start_time, duration, 3;

My goal is: To find the maximum number of concurrent rows over an arbitrary interval.  Concurrent is defined as
overlappingin their duration.  Example from the set above: Assume the desired interval is one day.  Rows 1 and 2 are
concurrentbecause row 2's start_time is within the duration of row 1.  If you go through the set the max concurrency is
5(this is a guess cause I did it visually and may have miscounted). I took a scan of how I tried to solve it manually
andattached the image.  I tried using timelines to visualize the start, duration, and end of each row then looked for
wherethey overlapped. 


My desired output set would be:

max_concurrency     |     interval   (in this case grouped by day)
--------------------+-----------------
        5           |   2006-08-28


if the interval for this set were different, say 30 minutes, then I would expect to see something like:
max_concurrency     |     interval
--------------------+--------------------------------------------
        0           |   2006-08-28 00:00:00 - 2006-08-28 00:29:59
        0           |   2006-08-28 00:30:00 - 2006-08-28 00:59:59
        0           |   2006-08-28 01:00:00 - 2006-08-28 01:29:59
            .......continues.....
        0           |   2006-08-28 16:00:00 - 2006-08-28 16:29:59
        5           |   2006-08-28 16:30:00 - 2006-08-28 16:59:59

I think that a query that involves a window could be used to solve this question as the documentation says:
"A window function call represents the application of an aggregate-like function over some portion of the rows selected
bya query...the window function is able to scan all the rows that would be part of the current row's group according to
thegrouping specification...." 
I am hoping that someone with more experience could help devise a way to do this with a query.  Thanks in advance.
Вложения

В списке pgsql-general по дате отправления:

Предыдущее
От: Carlos Henrique Reimer
Дата:
Сообщение: Re: User function canceling VACUUMDB utility
Следующее
От: Jorge Godoy
Дата:
Сообщение: Re: Good candidate query for window syntax?