Re: Flatten table using timestamp and source - Mailing list pgsql-sql

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