Thread: Flatten table using timestamp and source
<div class="WordSection1"><p class="MsoNormal">Good day.<p class="MsoNormal"> <p class="MsoNormal">I am quite new to Postgres,so please bear with me.<p class="MsoNormal"> <p class="MsoNormal">I have a table with data in the following format:<pclass="MsoNormal"> <p class="MsoNormal">Table name : Time_Source_Table<p class="MsoNormal">Source , Stime<p class="MsoNormal">1,"2012-05-24 13:00:00"<p class="MsoNormal">1, "2012-05-24 13:01:00"<p class="MsoNormal">1, "2012-05-2413:02:00"<p class="MsoNormal">2, "2012-05-24 13:03:00"<p class="MsoNormal">2, "2012-05-24 13:04:00"<p class="MsoNormal">1,"2012-05-24 13:05:00"<p class="MsoNormal">1, "2012-05-24 13:06:00"<p class="MsoNormal"> <p class="MsoNormal">I’m trying to get to a result that flattens the results based on source, to look like this:<p class="MsoNormal">Source,Stime, Etime<p class="MsoNormal">1, "2012-05-24 13:00:00","2012-05-24 13:02:00"<p class="MsoNormal">2,"2012-05-24 13:03:00","2012-05-24 13:04:00"<p class="MsoNormal">1, "2012-05-24 13:05:00","2012-05-2413:06:00"<p class="MsoNormal"> <p class="MsoNormal"> Where Etime is the last Stime for the same source.<pclass="MsoNormal"> <p class="MsoNormal">Any suggestions would be much appreciated.<p class="MsoNormal"> <p class="MsoNormal">Regards<pclass="MsoNormal"> El</div>
On Thursday 24 May 2012, Elrich Marx wrote: > I am quite new to Postgres, so please bear with me. > > I have a table with data in the following format: > > Table name : Time_Source_Table > > 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" > > I’m trying to get to a result that flattens the results based on > source, to look like this: > > 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" > > Where Etime is the last Stime for the same source. How do you figure out that the Etime for (1, 13:00:00) is (1, 13:02:00) and not (1, 13:01:00)? Regards, -- Raj -- Raj Mathur || raju@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F
HI Raj If source changes, in this case from 1 to 2, then etime would be the last value of stime for source =1; So for source 1 it starts at stime 13:00 and continues till 13:02 (etime). This should result in 3 records, because source is 1, then 2, then 1 again. I hope this explains ? -----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 On Thursday 24 May 2012, Elrich Marx wrote: > I am quite new to Postgres, so please bear with me. > > I have a table with data in the following format: > > Table name : Time_Source_Table > > 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" > > I’m trying to get to a result that flattens the results based on > source, to look like this: > > 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" > > Where Etime is the last Stime for the same source. How do you figure out that the Etime for (1, 13:00:00) is (1, 13:02:00) and not (1, 13:01:00)? Regards, -- Raj -- Raj Mathur || raju@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
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 =1; So for source 1 it starts at > stime 13:00 and continues till 13:02 (etime). > > 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 compute the last interval. Probably amenable to some severe optimisation too, but then I don't claim to be an SQL expert :) QUERY ----- with first_last as ( select * from ( selectsource,time,case when lag(source) over (order by time) != source or lag(source) over (order by time) is null then 1 else 0end as is_first,case when lead(source) over (order by time)!= source or lead(source) over (order by time) is null then 1 else 0end as is_last fromp ) foo where is_first != 0 or is_last != 0 ) select t1.source, start_time, end_time from ( select source, time as start_time from first_last where is_first = 1 ) t1 join ( select source, time as end_time, is_last from first_last where is_last = 1 ) t2 on ( t1.source = t2.source and t2.end_time > t1.start_time and t2.end_time < ( select timefrom first_lastwhere source!= t2.source and time > t1.start_timeorder by timelimit 1 ) ) ; DATA SET --------source | time --------+--------------------- 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-0105: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-0105: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-0105: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-0105: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-0105: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-0105: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-0106: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-0106: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-0106: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-0106: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-0106: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-0106: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-0106: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-0106: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-0106: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-0106: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-0106: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-0106: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-0106: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-0106: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-0106: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-0107: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-0107: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-0107: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-0107: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-0107: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-0107:21:01 RESULT ------source | start_time | end_time --------+---------------------+--------------------- 1 | 1970-01-01 05:30:01 | 1970-01-01 05:32:01 6 | 1970-01-0105: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-0106: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-0106: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-0107: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, -- 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 > > On Thursday 24 May 2012, Elrich Marx wrote: > > I am quite new to Postgres, so please bear with me. > > > > I have a table with data in the following format: > > > > Table name : Time_Source_Table > > > > 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" > > > > I’m trying to get to a result that flattens the results based on > > source, to look like this: > > > > 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" > > > > Where Etime is the last Stime for the same source. > > How do you figure out that the Etime for (1, 13:00:00) is (1, > 13:02:00) and not (1, 13:01:00)? -- Raj Mathur || raju@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F
On Friday 25 May 2012, Raj Mathur (राज माथुर) wrote: > 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 =1; So for source 1 it starts > > at stime 13:00 and continues till 13:02 (etime). > > > > 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 compute the last interval. Probably amenable to some severe > optimisation too, but then I don't claim to be an SQL expert :) With the last interval computation: QUERY ----- with first_last as ( select * from ( selectsource,time,case when lag(source) over (order by time) != source or lag(source) over (order by time) is null then 1 else 0end as is_first,case when lead(source) over (order by time)!= source or lead(source) over (order by time) is null then 1 else 0end as is_last fromp ) foo where is_first != 0 or is_last != 0 ) select t1.source, start_time, end_time from ( select source, time as start_time from first_last where is_first = 1 ) t1 join ( select source, time as end_time, is_last from first_last where is_last = 1 ) t2 on ( t1.source = t2.source and t2.end_time > t1.start_time and ( (t2.end_time < ( select time from first_last where source!= t2.source and time > t1.start_time order by time limit 1) ) or (t1.start_time = ( select time from first_last where is_first = 1 order by time desc limit 1)and t2.end_time =( select time from first_last where is_last = 1 order by time desc limit 1) ) ) ) ; RESULT (with same data set as before) ------source | start_time | end_time --------+---------------------+--------------------- 1 | 1970-01-01 05:30:01 | 1970-01-01 05:32:01 6 | 1970-01-0105: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-0106: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-0106: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-0107: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 7 | 1970-01-01 07:18:01 | 1970-01-01 07:21:01 Would be interested in seeing how to shorten and/or optimise this query. Regards, -- Raj -- Raj Mathur || raju@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F
On Saturday 26 May 2012, Raj Mathur (राज माथुर) wrote: > On Friday 25 May 2012, Raj Mathur (राज माथुर) wrote: > > 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 =1; So for source 1 it starts > > > at stime 13:00 and continues till 13:02 (etime). > > > > > > 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 compute the last interval. Probably amenable to some > > severe optimisation too, but then I don't claim to be an SQL > > expert :) > > With the last interval computation: Wokeh, much better solution (IMNSHO). Results are the same as earlier, probably still amenable to optimisation and simplification. Incidentally, thanks for handing out the problem! It was a good brain- teaser (and also a good opportunity to figure out window functions, which I hadn't worked with earlier). QUERY ----- -- -- Compute rows that are the first or the last in an interval. -- with first_last as ( select * from ( selectsource,time,case when lag(source) over (order by time) != source or lag(source) over (order by time) is null then 1 else 0end as is_first,case when lead(source) over (order by time)!= source or lead(source) over (order by time) is null then 1 else 0end as is_last fromp ) foo where is_first != 0 or is_last != 0 ) -- -- Main query -- select source, start_time, end_time from ( -- Get each row and the time from the next one select source, time as start_time, lead(time) over(order by time)as end_time, is_first from first_last ) bar -- Discard rows generated by the is_last row in the inner query where is_first = 1; ; > RESULT (with same data set as before) > ------ > source | start_time | end_time > --------+---------------------+--------------------- > 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 > 7 | 1970-01-01 07:18:01 | 1970-01-01 07:21:01 Regards, -- Raj -- Raj Mathur || raju@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F