Hey Adam,
I tried your sequence method this morning on an unsorted table and for
some reason the order by's aren't working. If I create a sorted view
(client_id, datetime) on the 'all_client_times' table and then use that
view with your sequence method all works fine. The strange thing is
that my table which has about 750K rows only ends up returning 658 rows
with your sequence method using the unsorted table. In fact, when I
tried the same thing with the lagfunc() method you wrote earlier on an
unsorted table the same thing occurs - only returning 658 rows instead
of the 750K. Again, all works well with lagfunc() if I use it on a
sorted view and I remove the order by in the function. This is not too
much of a problem as I can use a sorted view first but I don't
understand why this is happening. Perhaps this is a bug?
As well, I am finding that the lagfunc() is consistently faster than the
sequence method.
cheers,
Willem
Adam Rich wrote:
>>> I'm trying to replicate the use of Oracle's 'lag' and 'over
>>> partition by' analytical functions in my query. I have a table
>>> (all_client_times) such as:
>>> and I would like to create a new view that takes the first table and
>>> calculates the time difference in minutes between each row so that
>>> the result is something like:
>>>
>
> I thought of a another way of doing this. In my tests, it's a little
> faster, too.
>
> DROP SEQUENCE if exists seq1;
> DROP SEQUENCE if exists seq2;
> CREATE TEMPORARY SEQUENCE seq1 CACHE 1000;
> CREATE TEMPORARY SEQUENCE seq2 CACHE 1000;
>
> select a.client_id, b.datetime, a.datetime as previousTime, (b.datetime -
> a.datetime) as difftime from
> (select nextval('seq1') as s, client_id, datetime from all_client_times
> order by client_id, datetime OFFSET 0) as a
> inner join
> (select nextval('seq2') as s, client_id, datetime from all_client_times
> order by client_id, datetime OFFSET 0) as b
> on a.s=(b.s-1) where a.client_id=b.client_id
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>