Re: Flatten table using timestamp and source

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
(
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
)
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
)
)
;

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-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
------
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

Regards,

-- Raj

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Raj Mathur (???
> ?????)
> Sent: 24 May 2012 01:59 PM
> To: pgsql-sql(at)postgresql(dot)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(at)kandalaya(dot)org || GPG:
http://otheronepercent.blogspot.com || http://kandalaya.org || CC68
It is the mind that moves || http://schizoid.in || D17F