From: | Ketema Harris <ketema(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Good candidate query for window syntax? |
Date: | 2010-09-10 13:03:34 |
Message-ID: | 716E6AFD-4330-4032-8D92-DE90CEE0EA90@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 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.
Attachment | Content-Type | Size |
---|---|---|
![]() |
image/jpeg | 22.4 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Jorge Godoy | 2010-09-10 13:08:05 | Re: Good candidate query for window syntax? |
Previous Message | Carlos Henrique Reimer | 2010-09-10 10:07:32 | Re: User function canceling VACUUMDB utility |