From: Mario Splivalo <mario.splivalo@megafon.hr>
To: pgsql-sql@postgresql.org
Subject: Get the max viewd product_id for user_id
Date: Fri, 03 Dec 2010 11:53:26 +0100
Message-ID: <4CF8CC26.70206@megafon.hr> (raw)
I have a log-table where I record when some user_id has viewed some
product_id:
CREATE TABLE viewlog (
user_id integer,
product_id integer,
view_timestamp timestamp with time zone
)
Now, I would like to get result that gives me, for each user_id,
product_id of the product he/she viewed the most time, with the number
of views.
The 'issue' is I need this running on postgres 8.0.
I went this way, but for a large number of user_id's, it's quite slow:
CREATE VIEW v_views AS
SELECT user_id, product_id, count(*) as views
FROM viewlog
GROUP BY user_id, product_id
SELECT
DISTINCT user_id,
(SELECT product_id FROM v_views inn WHERE inn.user_id = out.user_id
ORDER BY views DESC LIMIT 1) as product_id,
(SELECT views FROM v_views inn WHERE inn.user_id = out.user_id ORDER BY
views DESC LIMIT 1) as views
FROM
v_views out
Mario
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mario.splivalo@megafon.hr
Subject: Re: Get the max viewd product_id for user_id
In-Reply-To: <4CF8CC26.70206@megafon.hr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox