Geoff Caplan <geoff@variosoft.com> writes:
> Hi folks,
>
> Sorry to ask a newbie SQL question but I'm struggling...
There's no efficient way to write this in standard SQL. However Postgres has
an extension DISTINCT ON that would do it:
select url,count(*)
from (select distinct on (session_id)
url
from clickstream
order by session_id,sequence_num desc
)
group by url
This isn't going to be a superfast query. It has to sort all the clickstream
records by session and sequence, take just the last one, then probably sort
those again.
You could maybe make it faster by having an index on <session_id,sequence_num>
and doing order by "session_id desc, sequence_num desc". And giving this
session a larger than normal sort_mem would give it a better chance of being
able to use hash_agg for the count.
--
greg