pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGet the max viewd product_id for user_id
6+ messages / 2 participants
[nested] [flat]
* Get the max viewd product_id for user_id
@ 2010-12-03 10:53 Mario Splivalo <mario.splivalo@megafon.hr>
2010-12-03 11:40 ` Re: Get the max viewd product_id for user_id Jayadevan M <Jayadevan.Maymala@ibsplc.com>
0 siblings, 1 reply; 6+ messages in thread
From: Mario Splivalo @ 2010-12-03 10:53 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Get the max viewd product_id for user_id
2010-12-03 10:53 Get the max viewd product_id for user_id Mario Splivalo <mario.splivalo@megafon.hr>
@ 2010-12-03 11:40 ` Jayadevan M <Jayadevan.Maymala@ibsplc.com>
2010-12-05 16:48 ` Re: Get the max viewd product_id for user_id Mario Splivalo <mario.splivalo@megafon.hr>
2010-12-05 16:57 ` Re: Get the max viewd product_id for user_id Mario Splivalo <mario.splivalo@megafon.hr>
0 siblings, 2 replies; 6+ messages in thread
From: Jayadevan M @ 2010-12-03 11:40 UTC (permalink / raw)
To: Mario Splivalo <mario.splivalo@megafon.hr>; +Cc: pgsql-sql; pgsql-sql-owner@postgresql.org
Hello,
> 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
>
Does this work faster?
select x.user_id,y.product_id,x.count from
(select user_id, max(count ) as count from (select user_id,product_id,
count(*) as count from viewlog group by user_id,product_id) as x group by
user_id
) as x inner join
(select user_id,product_id, count(*) as count1 from viewlog group by
user_id,product_id ) as y
on x.user_id=y.user_id and x.count=y.count1
Regards,
Jayadevan
DISCLAIMER:
"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Get the max viewd product_id for user_id
2010-12-03 10:53 Get the max viewd product_id for user_id Mario Splivalo <mario.splivalo@megafon.hr>
2010-12-03 11:40 ` Re: Get the max viewd product_id for user_id Jayadevan M <Jayadevan.Maymala@ibsplc.com>
@ 2010-12-05 16:48 ` Mario Splivalo <mario.splivalo@megafon.hr>
1 sibling, 0 replies; 6+ messages in thread
From: Mario Splivalo @ 2010-12-05 16:48 UTC (permalink / raw)
To: pgsql-sql
On 12/03/2010 12:40 PM, Jayadevan M wrote:
> Hello,
>
>> 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
>>
> Does this work faster?
> select x.user_id,y.product_id,x.count from
> (select user_id, max(count ) as count from (select user_id,product_id,
> count(*) as count from viewlog group by user_id,product_id) as x group by
> user_id
> ) as x inner join
> (select user_id,product_id, count(*) as count1 from viewlog group by
> user_id,product_id ) as y
> on x.user_id=y.user_id and x.count=y.count1
>
It does, yes. Actually, pretty silly of me not to implement it that way,
thank you.
Since I already have the view, the query now looks like this:
select
x.user_id,
y.product_id,
x.views
from (
select
user_id,
max(views) as views
from
v_views
group by
user_id
) as x
inner join v_views as y
on x.user_id=y.user_id and x.views=y.views
And CTEs would also help here :)
Mario
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Get the max viewd product_id for user_id
2010-12-03 10:53 Get the max viewd product_id for user_id Mario Splivalo <mario.splivalo@megafon.hr>
2010-12-03 11:40 ` Re: Get the max viewd product_id for user_id Jayadevan M <Jayadevan.Maymala@ibsplc.com>
@ 2010-12-05 16:57 ` Mario Splivalo <mario.splivalo@megafon.hr>
2010-12-05 16:59 ` Re: Get the max viewd product_id for user_id Mario Splivalo <mario.splivalo@megafon.hr>
1 sibling, 1 reply; 6+ messages in thread
From: Mario Splivalo @ 2010-12-05 16:57 UTC (permalink / raw)
To: Jayadevan M <Jayadevan.Maymala@ibsplc.com>; +Cc: pgsql-sql
On 12/03/2010 12:40 PM, Jayadevan M wrote:
> Hello,
>
>> 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
>>
> Does this work faster?
> select x.user_id,y.product_id,x.count from
> (select user_id, max(count ) as count from (select user_id,product_id,
> count(*) as count from viewlog group by user_id,product_id) as x group by
> user_id
> ) as x inner join
> (select user_id,product_id, count(*) as count1 from viewlog group by
> user_id,product_id ) as y
> on x.user_id=y.user_id and x.count=y.count1
The issue in both approaches is that if I have two product_ids that are
viewed same number of times and share the first place as most viewed
products by that user, I'll get only one of them (LIMIT 1 OR MAX() can
only return one row :).
I don't see how I can sort this out with elegance in SQL.
Mario
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Get the max viewd product_id for user_id
2010-12-03 10:53 Get the max viewd product_id for user_id Mario Splivalo <mario.splivalo@megafon.hr>
2010-12-03 11:40 ` Re: Get the max viewd product_id for user_id Jayadevan M <Jayadevan.Maymala@ibsplc.com>
2010-12-05 16:57 ` Re: Get the max viewd product_id for user_id Mario Splivalo <mario.splivalo@megafon.hr>
@ 2010-12-05 16:59 ` Mario Splivalo <mario.splivalo@megafon.hr>
2010-12-06 02:55 ` Re: Get the max viewd product_id for user_id Jayadevan M <Jayadevan.Maymala@ibsplc.com>
0 siblings, 1 reply; 6+ messages in thread
From: Mario Splivalo @ 2010-12-05 16:59 UTC (permalink / raw)
To: pgsql-sql
On 12/05/2010 05:57 PM, Mario Splivalo wrote:
> The issue in both approaches is that if I have two product_ids that are
> viewed same number of times and share the first place as most viewed
> products by that user, I'll get only one of them (LIMIT 1 OR MAX() can
> only return one row :).
>
And then, to jump again into my own mouth - your approach, Jayadevan,
correctly gives me both product_id's if they're viewed the same number
of times.
Mario
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Get the max viewd product_id for user_id
2010-12-03 10:53 Get the max viewd product_id for user_id Mario Splivalo <mario.splivalo@megafon.hr>
2010-12-03 11:40 ` Re: Get the max viewd product_id for user_id Jayadevan M <Jayadevan.Maymala@ibsplc.com>
2010-12-05 16:57 ` Re: Get the max viewd product_id for user_id Mario Splivalo <mario.splivalo@megafon.hr>
2010-12-05 16:59 ` Re: Get the max viewd product_id for user_id Mario Splivalo <mario.splivalo@megafon.hr>
@ 2010-12-06 02:55 ` Jayadevan M <Jayadevan.Maymala@ibsplc.com>
0 siblings, 0 replies; 6+ messages in thread
From: Jayadevan M @ 2010-12-06 02:55 UTC (permalink / raw)
To: Mario Splivalo <mario.splivalo@megafon.hr>; +Cc: pgsql-sql; pgsql-sql-owner@postgresql.org
> > The issue in both approaches is that if I have two product_ids that
are
> > viewed same number of times and share the first place as most viewed
> > products by that user, I'll get only one of them (LIMIT 1 OR MAX() can
> > only return one row :).
> >
>
> And then, to jump again into my own mouth - your approach, Jayadevan,
> correctly gives me both product_id's if they're viewed the same number
> of times.
>
Good. It should, since we are joining on count and user_id. I was
surprised to see your mail which said it wouldn't :).
Regards,
Jayadevan
DISCLAIMER:
"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-12-06 02:55 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-03 10:53 Get the max viewd product_id for user_id Mario Splivalo <mario.splivalo@megafon.hr>
2010-12-03 11:40 ` Jayadevan M <Jayadevan.Maymala@ibsplc.com>
2010-12-05 16:48 ` Mario Splivalo <mario.splivalo@megafon.hr>
2010-12-05 16:57 ` Mario Splivalo <mario.splivalo@megafon.hr>
2010-12-05 16:59 ` Mario Splivalo <mario.splivalo@megafon.hr>
2010-12-06 02:55 ` Jayadevan M <Jayadevan.Maymala@ibsplc.com>
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