pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFlatten table using timestamp and source
6+ messages / 2 participants
[nested] [flat]
* Flatten table using timestamp and source
@ 2012-05-24 09:01 Elrich Marx <elrich.marx@rorotika.com>
2012-05-24 11:59 ` Re: Flatten table using timestamp and source Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
0 siblings, 1 reply; 6+ messages in thread
From: Elrich Marx @ 2012-05-24 09:01 UTC (permalink / raw)
To: pgsql-sql
Good day.
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.
Any suggestions would be much appreciated.
Regards
El
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Flatten table using timestamp and source
2012-05-24 09:01 Flatten table using timestamp and source Elrich Marx <elrich.marx@rorotika.com>
@ 2012-05-24 11:59 ` Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
2012-05-24 12:10 ` Re: Flatten table using timestamp and source Elrich Marx <elrich.marx@rorotika.com>
0 siblings, 1 reply; 6+ messages in thread
From: Raj Mathur (राज माथुर) @ 2012-05-24 11:59 UTC (permalink / raw)
To: pgsql-sql
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)?
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Flatten table using timestamp and source
2012-05-24 09:01 Flatten table using timestamp and source Elrich Marx <elrich.marx@rorotika.com>
2012-05-24 11:59 ` Re: Flatten table using timestamp and source Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
@ 2012-05-24 12:10 ` Elrich Marx <elrich.marx@rorotika.com>
2012-05-25 05:50 ` Re: Flatten table using timestamp and source Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
0 siblings, 1 reply; 6+ messages in thread
From: Elrich Marx @ 2012-05-24 12:10 UTC (permalink / raw)
To: raju@linux-delhi.org; pgsql-sql
HI Raj
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 ?
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Raj Mathur (??? ?????)
Sent: 24 May 2012 01:59 PM
To: pgsql-sql@postgresql.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)?
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes
to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Flatten table using timestamp and source
2012-05-24 09:01 Flatten table using timestamp and source Elrich Marx <elrich.marx@rorotika.com>
2012-05-24 11:59 ` Re: Flatten table using timestamp and source Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
2012-05-24 12:10 ` Re: Flatten table using timestamp and source Elrich Marx <elrich.marx@rorotika.com>
@ 2012-05-25 05:50 ` Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
2012-05-26 02:56 ` Re: Flatten table using timestamp and source Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
0 siblings, 1 reply; 6+ messages in thread
From: Raj Mathur (राज माथुर) @ 2012-05-25 05:50 UTC (permalink / raw)
To: pgsql-sql
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@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Raj Mathur (???
> ?????)
> Sent: 24 May 2012 01:59 PM
> To: pgsql-sql@postgresql.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@kandalaya.org || GPG:
http://otheronepercent.blogspot.com || http://kandalaya.org || CC68
It is the mind that moves || http://schizoid.in || D17F
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Flatten table using timestamp and source
2012-05-24 09:01 Flatten table using timestamp and source Elrich Marx <elrich.marx@rorotika.com>
2012-05-24 11:59 ` Re: Flatten table using timestamp and source Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
2012-05-24 12:10 ` Re: Flatten table using timestamp and source Elrich Marx <elrich.marx@rorotika.com>
2012-05-25 05:50 ` Re: Flatten table using timestamp and source Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
@ 2012-05-26 02:56 ` Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
2012-05-26 05:43 ` Re: Flatten table using timestamp and source Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
0 siblings, 1 reply; 6+ messages in thread
From: Raj Mathur (राज माथुर) @ 2012-05-26 02:56 UTC (permalink / raw)
To: 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
(
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
)
)
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-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,
-- 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Flatten table using timestamp and source
2012-05-24 09:01 Flatten table using timestamp and source Elrich Marx <elrich.marx@rorotika.com>
2012-05-24 11:59 ` Re: Flatten table using timestamp and source Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
2012-05-24 12:10 ` Re: Flatten table using timestamp and source Elrich Marx <elrich.marx@rorotika.com>
2012-05-25 05:50 ` Re: Flatten table using timestamp and source Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
2012-05-26 02:56 ` Re: Flatten table using timestamp and source Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
@ 2012-05-26 05:43 ` Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
0 siblings, 0 replies; 6+ messages in thread
From: Raj Mathur (राज माथुर) @ 2012-05-26 05:43 UTC (permalink / raw)
To: pgsql-sql
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@kandalaya.org || GPG:
http://otheronepercent.blogspot.com || http://kandalaya.org || CC68
It is the mind that moves || http://schizoid.in || D17F
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-05-26 05:43 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-05-24 09:01 Flatten table using timestamp and source Elrich Marx <elrich.marx@rorotika.com>
2012-05-24 11:59 ` Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
2012-05-24 12:10 ` Elrich Marx <elrich.marx@rorotika.com>
2012-05-25 05:50 ` Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
2012-05-26 02:56 ` Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
2012-05-26 05:43 ` Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox