Thread: Get the max viewd product_id for user_id
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 ASSELECT user_id, product_id, count(*) as viewsFROM viewlogGROUP BY user_id, product_id SELECTDISTINCT 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 FROMv_views out Mario
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."
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: selectx.user_id,y.product_id,x.views from (select user_id, max(views) as viewsfrom v_viewsgroup by user_id ) as xinner join v_views as yon x.user_id=y.user_id and x.views=y.views And CTEs would also help here :) Mario
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
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
> > 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."