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
(
select
source,
time,
case
when
lag(source) over (order by time) != 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) != source
or lead(source) over (order by time) is null
then 1 else 0
end as is_last
from
p
) 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(at)kandalaya(dot)org || GPG:
http://otheronepercent.blogspot.com || http://kandalaya.org || CC68
It is the mind that moves || http://schizoid.in || D17F