Re: Grouping logs by ip and time

Hi Alex,

in PG9 you can use a query like this:

with a as ( select a.*, rank() over (partition by a.ip,a.date order by
a.log_type, a.time) from log_table a )
select a.*, b.*, b.time-a.time from a join a b on a.ip=b.ip and
a.date=b.date and a.rank+1=b.rank

this orders entry by time grouped by ip and date and selects entries
with there successors. In older versions this is not so easy. It should
work like this:
create temp sequence s;
create temp table a as select a.*, nextval('s') as rank from ( select
a.* from log_table a order by a.ip, a.date, a.time) a;
select a.*, b.*, b.time-a.time from a a join a b on a.ip=b.ip and
a.date=b.date and a.rank+1=b.rank;

Thomas

Am 08.11.2011 18:59, schrieb Alex Thurlow:
> Hello all,
> I have a table which stores action logs from users. It looks
> something like this:
> log_type text,
> date date,
> "time" time without time zone,
> ip inet
>
> The log type can be action1, action2, action3, action4, or action5. I
> know that each user session will have a max of one of each log and it
> will always start with action1. It may not have every action though.
> I also know that each session will take no longer than one minute.
>
> What I'd like to do is be able to group these logs by sessions based
> on the IP and the time range so I can figure out the time taken
> between each action.
>
> I know how to script it, although it's very slow. I was hoping there
> was some way to do this in SQL. I'm running Postgresql 8.3.7 on this
> machine right now, but if there's something that would make this
> easier and doesn't exist there yet, I would be willing to upgrade.
>
> Thanks,
> Alex
>