Hello.
I try with "With Query".
http://www.postgresql.org/docs/8.4/static/queries-with.html
#We can use "With Queries" > v8.4
#That'll only work if the time values are contiguous, but there's probably a
#similar trick for non-contiguous ,too.
--create data
drop table foo;
create table foo( time int,message text);
insert into foo values(1,'a');
insert into foo values(2,'b');
insert into foo values(3,'b');
insert into foo values(4,'c');
insert into foo values(5,'a');
insert into foo values(6,'c');
insert into foo values(7,'c');
insert into foo values(8,'a');
insert into foo values(9,'a');
insert into foo values(10,'a');
--begin Answer
with recursive r as (
select foo.time,foo.message,1 as dummy from foo
union all
select foo.time,foo.message,r.dummy+1 from foo , r
where foo.time=r.time-1 and foo.message=r.message
)
,rr as (
select foo.time,foo.message,'OLID' as flag from foo
union all
select foo.time,foo.message,'DUP' as flag from foo , rr
where foo.time-1=rr.time-2 and foo.message=rr.message
)
select time min,time+max(dummy)-1 max,message,max(dummy) counts
from r where time not in (select distinct (time+1) times from rr
where flag='DUP') group by time,message order by time;
--result
postgres(# where flag='DUP') group by time,message order by time;
min | max | message | counts
-----+-----+---------+--------
1 | 1 | a | 1
2 | 3 | b | 2
4 | 4 | c | 1
5 | 5 | a | 1
6 | 7 | c | 2
8 | 10 | a | 3
(6 rows)
--end
But I think some one can provide more simple SQL.
Thank you.
On Apr 12, 2010, at 11:31 AM, Scott Marlowe wrote:
On Mon, Apr 12, 2010 at 12:22 PM, A B<gentosaker@xxxxxxxxx> wrote:
Hello!
I have a table (think of it as a table of log messages)
time | message
-----------------------
1 | a
2 | b
3 | b
4 | b
5 | a
the three 'b' are the same message, so I would like to write a query
that would give me a result that is similar to what the unix command
"uniq -c" would give:
first | message | last | count
--------------------------------------
1 | a | 1 | 1
2 | b | 4 | 3<--- here it squeezes
similar consecutive messages into a single row
5 | a | 5 | 1
How do I write such a command?
Pretty straight ahead:
select min(t), message, max(t), count(*) from table group by message.
That was my first though too, but it combines everything not just adjacent messages.
Something like this, maybe
select t1.message, t1.time as first, t2.time as last, t2.time-t1.time+1 as count
from foo as t1, foo as t2
where t1.time<= t2.time and t1.message = t2.message
and not exists
(select * from foo as t3
where (t3.time between t1.time and t2.time and t3.message<> t1.message)
or (t3.time = t2.time + 1 and t3.message = t1.message)
or (t3.time = t1.time - 1 and t3.message = t1.message));
message | first | last | count
---------+-------+------+-------
a | 1 | 1 | 1
b | 2 | 4 | 3
a | 5 | 5 | 1
That'll only work if the time values are contiguous, but there's probably a
similar trick for non-contiguous.
Cheers,
Steve
--
================================================
Kenichiro Tanaka
K.K.Ashisuto
http://www.ashisuto.co.jp/english/index.html
================================================
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
