Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 9CA47B53E84 for ; Fri, 25 May 2012 02:51:20 -0300 (ADT) Received: from etc.kandalaya.org ([83.143.86.98] helo=images.kandalaya.org) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SXnQj-0002wG-E0 for pgsql-sql@postgresql.org; Fri, 25 May 2012 05:51:19 +0000 Received: from mail.linux-delhi.org ([120.56.168.226]) by images.kandalaya.org (8.14.3/8.14.3/Debian-9) with ESMTP id q4P5ot5I029737 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK) for ; Fri, 25 May 2012 11:20:58 +0530 Received: from mail.linux-delhi.org (raju@localhost [127.0.0.1]) by mail.linux-delhi.org (8.14.4/8.14.4/Debian-2) with ESMTP id q4P5okKL025059 for ; Fri, 25 May 2012 11:20:46 +0530 From: "Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=)" Reply-To: raju@linux-delhi.org Organization: Kandalaya To: pgsql-sql@postgresql.org Subject: Re: Flatten table using timestamp and source Date: Fri, 25 May 2012 11:20:45 +0530 User-Agent: KMail/1.13.7 (Linux/3.2.0-2-686-pae; KDE/4.6.5; i686; ; ) References: <73a6b75b83b37093e5e2751fc59c4323@mail.gmail.com> <201205241729.21270.raju@linux-delhi.org> <43a608eb27c08adad0ae99c3409eb1fd@mail.gmail.com> In-Reply-To: <43a608eb27c08adad0ae99c3409eb1fd@mail.gmail.com> MIME-Version: 1.0 Content-Type: Text/Plain; charset="windows-1252" Content-Transfer-Encoding: quoted-printable Message-Id: <201205251120.46272.raju@linux-delhi.org> X-Spam-Status: No, score=-10.8 required=5.0 tests=AWL,BAYES_00, DNS_FROM_RFC_BOGUSMX, LOCAL_FROM_RAJU, RCVD_IN_PBL, RDNS_NONE autolearn=ham version=3.2.5 X-Spam-Checker-Version: SpamAssassin 3.2.5 (2008-06-10) on images.kandalaya.org X-Virus-Scanned: clamav-milter 0.95.2 at images.kandalaya.org X-Virus-Status: Clean X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201205/85 X-Sequence-Number: 36633 On Thursday 24 May 2012, Elrich Marx wrote: > If source changes, in this case from 1 to 2, then etime would be the > last value of stime for source =3D1; So for source 1 it starts at > stime 13:00 and continues till 13:02 (etime). >=20 > This should result in 3 records, because source is 1, then 2, then 1 > again. I hope this explains ? I think I understand. Here's a partially working example -- it doesn't=20 compute the last interval. Probably amenable to some severe=20 optimisation too, but then I don't claim to be an SQL expert :) QUERY =2D---- with first_last as ( select * from ( select source, time, case when lag(source) over (order by time) !=3D source or lag(source) over (order by time) is null then 1 else 0 end as is_first, case when lead(source) over (order by time) !=3D source or lead(source) over (order by time) is null then 1 else 0 end as is_last from p ) foo where is_first !=3D 0 or is_last !=3D 0 ) select t1.source, start_time, end_time from ( select source, time as start_time from first_last where is_first =3D 1 ) t1 join ( select source, time as end_time, is_last from first_last where is_last =3D 1 ) t2 on ( t1.source =3D t2.source and t2.end_time > t1.start_time and t2.end_time <=20 ( select time from first_last where source !=3D t2.source and time > t1.start_time order by time limit 1 ) ) ; DATA SET =2D------- source | time =20 =2D-------+--------------------- 1 | 1970-01-01 05:30:01 1 | 1970-01-01 05:31:01 1 | 1970-01-01 05:32:01 6 | 1970-01-01 05:33:01 6 | 1970-01-01 05:34:01 6 | 1970-01-01 05:35:01 6 | 1970-01-01 05:36:01 6 | 1970-01-01 05:37:01 2 | 1970-01-01 05:38:01 2 | 1970-01-01 05:39:01 2 | 1970-01-01 05:40:01 2 | 1970-01-01 05:41:01 6 | 1970-01-01 05:42:01 6 | 1970-01-01 05:43:01 6 | 1970-01-01 05:44:01 6 | 1970-01-01 05:45:01 6 | 1970-01-01 05:46:01 4 | 1970-01-01 05:47:01 4 | 1970-01-01 05:48:01 4 | 1970-01-01 05:49:01 4 | 1970-01-01 05:50:01 4 | 1970-01-01 05:51:01 0 | 1970-01-01 05:52:01 0 | 1970-01-01 05:53:01 0 | 1970-01-01 05:54:01 0 | 1970-01-01 05:55:01 7 | 1970-01-01 05:56:01 7 | 1970-01-01 05:57:01 7 | 1970-01-01 05:58:01 8 | 1970-01-01 05:59:01 8 | 1970-01-01 06:00:01 8 | 1970-01-01 06:01:01 8 | 1970-01-01 06:02:01 8 | 1970-01-01 06:03:01 1 | 1970-01-01 06:04:01 1 | 1970-01-01 06:05:01 1 | 1970-01-01 06:06:01 1 | 1970-01-01 06:07:01 1 | 1970-01-01 06:08:01 1 | 1970-01-01 06:09:01 1 | 1970-01-01 06:10:01 8 | 1970-01-01 06:11:01 8 | 1970-01-01 06:12:01 8 | 1970-01-01 06:13:01 6 | 1970-01-01 06:14:01 6 | 1970-01-01 06:15:01 6 | 1970-01-01 06:16:01 4 | 1970-01-01 06:17:01 4 | 1970-01-01 06:18:01 9 | 1970-01-01 06:19:01 9 | 1970-01-01 06:20:01 9 | 1970-01-01 06:21:01 9 | 1970-01-01 06:22:01 2 | 1970-01-01 06:23:01 2 | 1970-01-01 06:24:01 2 | 1970-01-01 06:25:01 1 | 1970-01-01 06:26:01 1 | 1970-01-01 06:27:01 1 | 1970-01-01 06:28:01 1 | 1970-01-01 06:29:01 4 | 1970-01-01 06:30:01 4 | 1970-01-01 06:31:01 4 | 1970-01-01 06:32:01 4 | 1970-01-01 06:33:01 4 | 1970-01-01 06:34:01 0 | 1970-01-01 06:35:01 0 | 1970-01-01 06:36:01 0 | 1970-01-01 06:37:01 9 | 1970-01-01 06:38:01 9 | 1970-01-01 06:39:01 9 | 1970-01-01 06:40:01 9 | 1970-01-01 06:41:01 9 | 1970-01-01 06:42:01 1 | 1970-01-01 06:43:01 1 | 1970-01-01 06:44:01 1 | 1970-01-01 06:45:01 8 | 1970-01-01 06:46:01 8 | 1970-01-01 06:47:01 8 | 1970-01-01 06:48:01 8 | 1970-01-01 06:49:01 8 | 1970-01-01 06:50:01 0 | 1970-01-01 06:51:01 0 | 1970-01-01 06:52:01 0 | 1970-01-01 06:53:01 0 | 1970-01-01 06:54:01 0 | 1970-01-01 06:55:01 0 | 1970-01-01 06:56:01 0 | 1970-01-01 06:57:01 2 | 1970-01-01 06:58:01 2 | 1970-01-01 06:59:01 2 | 1970-01-01 07:00:01 2 | 1970-01-01 07:01:01 2 | 1970-01-01 07:02:01 2 | 1970-01-01 07:03:01 2 | 1970-01-01 07:04:01 2 | 1970-01-01 07:05:01 4 | 1970-01-01 07:06:01 4 | 1970-01-01 07:07:01 2 | 1970-01-01 07:08:01 2 | 1970-01-01 07:09:01 2 | 1970-01-01 07:10:01 2 | 1970-01-01 07:11:01 2 | 1970-01-01 07:12:01 7 | 1970-01-01 07:13:01 7 | 1970-01-01 07:14:01 9 | 1970-01-01 07:15:01 9 | 1970-01-01 07:16:01 9 | 1970-01-01 07:17:01 7 | 1970-01-01 07:18:01 7 | 1970-01-01 07:19:01 7 | 1970-01-01 07:20:01 7 | 1970-01-01 07:21:01 RESULT =2D----- source | start_time | end_time =20 =2D-------+---------------------+--------------------- 1 | 1970-01-01 05:30:01 | 1970-01-01 05:32:01 6 | 1970-01-01 05:33:01 | 1970-01-01 05:37:01 2 | 1970-01-01 05:38:01 | 1970-01-01 05:41:01 6 | 1970-01-01 05:42:01 | 1970-01-01 05:46:01 4 | 1970-01-01 05:47:01 | 1970-01-01 05:51:01 0 | 1970-01-01 05:52:01 | 1970-01-01 05:55:01 7 | 1970-01-01 05:56:01 | 1970-01-01 05:58:01 8 | 1970-01-01 05:59:01 | 1970-01-01 06:03:01 1 | 1970-01-01 06:04:01 | 1970-01-01 06:10:01 8 | 1970-01-01 06:11:01 | 1970-01-01 06:13:01 6 | 1970-01-01 06:14:01 | 1970-01-01 06:16:01 4 | 1970-01-01 06:17:01 | 1970-01-01 06:18:01 9 | 1970-01-01 06:19:01 | 1970-01-01 06:22:01 2 | 1970-01-01 06:23:01 | 1970-01-01 06:25:01 1 | 1970-01-01 06:26:01 | 1970-01-01 06:29:01 4 | 1970-01-01 06:30:01 | 1970-01-01 06:34:01 0 | 1970-01-01 06:35:01 | 1970-01-01 06:37:01 9 | 1970-01-01 06:38:01 | 1970-01-01 06:42:01 1 | 1970-01-01 06:43:01 | 1970-01-01 06:45:01 8 | 1970-01-01 06:46:01 | 1970-01-01 06:50:01 0 | 1970-01-01 06:51:01 | 1970-01-01 06:57:01 2 | 1970-01-01 06:58:01 | 1970-01-01 07:05:01 4 | 1970-01-01 07:06:01 | 1970-01-01 07:07:01 2 | 1970-01-01 07:08:01 | 1970-01-01 07:12:01 7 | 1970-01-01 07:13:01 | 1970-01-01 07:14:01 9 | 1970-01-01 07:15:01 | 1970-01-01 07:17:01 Regards, =2D- Raj > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Raj Mathur (??? > ?????) > Sent: 24 May 2012 01:59 PM > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] Flatten table using timestamp and source >=20 > On Thursday 24 May 2012, Elrich Marx wrote: > > I am quite new to Postgres, so please bear with me. > >=20 > > I have a table with data in the following format: > >=20 > > Table name : Time_Source_Table > >=20 > > Source , Stime > > 1, "2012-05-24 13:00:00" > > 1, "2012-05-24 13:01:00" > > 1, "2012-05-24 13:02:00" > > 2, "2012-05-24 13:03:00" > > 2, "2012-05-24 13:04:00" > > 1, "2012-05-24 13:05:00" > > 1, "2012-05-24 13:06:00" > >=20 > > I=92m trying to get to a result that flattens the results based on > > source, to look like this: > >=20 > > Source, Stime, Etime > > 1, "2012-05-24 13:00:00","2012-05-24 13:02:00" > > 2, "2012-05-24 13:03:00","2012-05-24 13:04:00" > > 1, "2012-05-24 13:05:00","2012-05-24 13:06:00" > >=20 > > Where Etime is the last Stime for the same source. >=20 > How do you figure out that the Etime for (1, 13:00:00) is (1, > 13:02:00) and not (1, 13:01:00)? =2D-=20 Raj Mathur || raju@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F