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 11:13:36 +0530
User-Agent: KMail/1.13.7 (Linux/3.2.0-2-686-pae; KDE/4.6.5; i686; ; )
References: <73a6b75b83b37093e5e2751fc59c4323@mail.gmail.com>
	<201205251120.46272.raju@linux-delhi.org>
	<201205260826.54985.raju@linux-delhi.org>
In-Reply-To: <201205260826.54985.raju@linux-delhi.org>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Message-Id: <201205261113.36822.raju@linux-delhi.org>

On Saturday 26 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 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 :)
>=20
> With the last interval computation:

Wokeh, much better solution (IMNSHO).  Results are the same as earlier,=20
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,=20
which I hadn't worked with earlier).

QUERY
=2D----
=2D-
=2D- Compute rows that are the first or the last in an interval.
=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
  )
=2D-
=2D- Main query
=2D-
select
  source,
  start_time,
  end_time
from
(
=2D- 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
=2D- Discard rows generated by the is_last row in the inner query
where
  is_first =3D 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,

=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