Re: Flatten table using timestamp and source - Mailing list pgsql-sql
| From | Raj Mathur (राज माथुर) |
|---|---|
| Subject | Re: Flatten table using timestamp and source |
| Date | |
| Msg-id | 201205261113.36822.raju@linux-delhi.org Whole thread Raw |
| In response to | Re: Flatten table using timestamp and source ("Raj Mathur (राज माथुर)" <raju@linux-delhi.org>) |
| List | pgsql-sql |
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