Re: Good candidate query for window syntax?

Ok I have been flailing at trying to understand both syntax and concepts...I
think I am moving forward as I have a query that returns a result...its just
the wrong result....

SELECT count(*) OVER w as max_concurrency,
start_time::date as "interval"
FROM demo
GROUP BY start_time::date,
case
when
(
(start_time, to_timestamp((extract(epoch from start_time)
+ duration))::timestamp)
OVERLAPS
(start_time, to_timestamp((extract(epoch from start_time)
+ duration))::timestamp)
) = TRUE
then 1
end
WINDOW w AS
(
PARTITION BY
start_time::date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

The results are :

max_concurrency | interval
-----------------+--------------------------
1 | 2006-08-28
1 | 2010-09-09
1 | 2010-09-10
(3 rows)

The count is returning the count of the date, not if a rows interval
overlaps another rows. Also I recognize that I really want the max count
over the given interval. Any thoughts would be appreciated

On Fri, Sep 10, 2010 at 9:40 AM, Ketema <ketema(at)gmail(dot)com> wrote:

> On Sep 10, 9:08 am, jgo(dot)(dot)(dot)(at)gmail(dot)com (Jorge Godoy) wrote:
> > Have you checked the OVERLAPS operator in the documentation?
> >
> > http://www.postgresql.org/docs/8.4/interactive/functions-datetime.html
> >
> > --
> > Jorge Godoy <jgo(dot)(dot)(dot)(at)gmail(dot)com>
> >
> >
> >
> > On Fri, Sep 10, 2010 at 10:03, Ketema Harris <ket(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> > > 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 overlapping in their duration.
> Example
> > > from the set above: Assume the desired interval is one day. Rows 1 and
> 2
> > > are concurrent because 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 and attached the image. I tried using timelines to
> > > visualize the start, duration, and end of each row then looked for
> where
> > > they 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 by a query...the window
> > > function is able to scan all the rows that would be part of the current
> > > row's group according to the grouping specification...."
> > > I am hoping that someone with more experience could help devise a way
> to do
> > > this with a query. Thanks in advance.
> >
> > > --
> > > Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
> > > To make changes to your subscription:
> > >http://www.postgresql.org/mailpref/pgsql-general
>
> I have just reviewed and thanks for reminding me that exists. I can
> see that this could be useful, but I'm not quite putting it all
> together. The overlaps operator takes two sets of start and end times
> and tells you if they overlap, i need this comparison done over an
> entire set......I started writing the following:
>
> SELECT count(case when (start_time, end_time) overlaps ? is true then
> 1) as max_concurrency OVER w,
> calldate::date as "interval"
> OVER (PARTITION BY start_time::date ORDER BY start_time, end_time
> desc) AS w
> ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
> FROM cdr;
>
> What would go on the right side of overlaps ?
>
> I really have no idea if that is the right direction....
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>