Re: Flatten table using timestamp and source

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