Re: Need some help with a query (uniq -c)

On Apr 12, 2010, at 11:31 AM, Scott Marlowe wrote:

> On Mon, Apr 12, 2010 at 12:22 PM, A B <gentosaker(at)gmail(dot)com> 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