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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
