From: "Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?=
	=?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=)" <raju@linux-delhi.org>
Reply-To: raju@linux-delhi.org
Organization: Kandalaya
To: pgsql-sql@postgresql.org
Subject: Re: Flatten table using timestamp and source
Date: Sat, 26 May 2012 08:26:54 +0530
User-Agent: KMail/1.13.7 (Linux/3.2.0-2-686-pae; KDE/4.6.5; i686; ; )
References: <73a6b75b83b37093e5e2751fc59c4323@mail.gmail.com>
	<43a608eb27c08adad0ae99c3409eb1fd@mail.gmail.com>
	<201205251120.46272.raju@linux-delhi.org>
In-Reply-To: <201205251120.46272.raju@linux-delhi.org>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Message-Id: <201205260826.54985.raju@linux-delhi.org>

On Friday 25 May 2012, Raj Mathur (=E0=A4=B0=E0=A4=BE=E0=A4=9C =E0=A4=AE=E0=
=A4=BE=E0=A4=A5=E0=A5=81=E0=A4=B0) 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 =3D1; So for source 1 it starts
> > at stime 13:00 and continues till 13:02 (etime).
> >=20
> > This should result in 3 records, because source is 1, then 2, then
> > 1 again. I hope this explains ?
>=20
> 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
=2D----
with
  first_last as
  (
    select * from
    (
      select
	source,
	time,
	case
	  when
	    lag(source) over (order by time) !=3D 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) !=3D source
	    or lead(source) over (order by time) is null
	  then 1 else 0
	end as is_last
      from
	p
    ) foo
    where
      is_first !=3D 0 or is_last !=3D 0
  )
select
  t1.source,
  start_time,
  end_time
from
  (
    select
      source,
      time as start_time
    from
      first_last
    where
      is_first =3D 1
  ) t1
  join
  (
    select
      source,
      time as end_time,
      is_last
    from
      first_last
    where
      is_last =3D 1
  ) t2
  on
  (
    t1.source =3D t2.source
    and t2.end_time > t1.start_time
    and
    (
      (
	t2.end_time <=20
	(
	  select
	    time
	  from
	    first_last
	  where
	    source !=3D t2.source
	    and time > t1.start_time
	  order by
	    time
	  limit
	    1
	)
      )
      or
      (
	t1.start_time =3D=20
	(
	  select
	    time
	  from
	    first_last
	  where
	    is_first =3D 1
	  order by
	    time desc
	  limit
	    1
	)
	and t2.end_time =3D
	(
	  select
	    time
	  from
	    first_last
	  where
	    is_last =3D 1
	  order by
	    time desc
	  limit
	    1
	)
      )
    )
  )
;

RESULT (with same data set as before)
=2D-----
 source |     start_time      |      end_time      =20
=2D-------+---------------------+---------------------
      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

Would be interested in seeing how to shorten and/or optimise this query.

Regards,

=2D- Raj
=2D-=20
Raj Mathur                          || raju@kandalaya.org   || GPG:
http://otheronepercent.blogspot.com || http://kandalaya.org || CC68
It is the mind that moves           || http://schizoid.in   || D17F