CODEWITHSUNDEEP
phpmysqlarraysselect
Peyman Mohamadpour
Peyman Mohamadpour

Reputation: 17964

Complex SQL SELECT to compute rating on a single column

It may be considered a duplicate for this, but my question is somehow more complicated. I have a MySQL table with these fields:

ip | product_id | visits

which shows how many times an ip has visited a product. I finally want to generate a php array like this:

product_rating_for_ip = array(
  ip=>array(
    product_id=>rating, product_id=>rating, ...
  ),
  ip=>array(
    product_id=>rating, product_id=>rating, ...
  ),
  .
  .
  .
);

The rating is equal to the visits for a single product from some ip divided by MAX of all product visits by that ip .for example:

product_rating_for_ip = array(
  "78.125.15.92"=>array(
    1=>0.8,2=>0.2,3=>1.0
  ),
  "163.56.75.112"=>array(
    1=>0.1,2=>0.3,3=>0.5,8=>0.1,12=>1.0
  ),
  .
  .
  .
);

What I have done :

SELECT ip, id, visits, MAX(visits) FROM visit GROUP BY ip

I have performance considerations and I want to avoid SQL queries in nested loops. I think the above SQL query is incorrect as it does not show the expected results in action.

Upvotes: 0

Views: 183

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

The idea is to use a subquery to calculate the sum, then do the calculation and put the values into a single comma-delimited column, which you can transform into an array in php:

select v.ip, group_concat(v.visits / iv.maxvisits) as ratings
from visit v join
     (SELECT ip, id, visits, max(visits) as maxvisits
      FROM visit
      GROUP BY ip
     ) iv
     on v.ip = iv.ip
group by v.ip;

EDIT:

Tables in SQL are inherently unordered and sorting in SQL is not stable (meaning the original order is not preserved). You can specify an ordering in the group_concat() statement. For instance, the following would order the results by id:

select v.ip, group_concat(v.visits / iv.maxvisits order by id) as ratings
from visit v join
     (SELECT ip, id, visits, max(visits) as maxvisits
      FROM visit
      GROUP BY ip
     ) iv
     on v.ip = iv.ip
group by v.ip;

And this would order by the highest rating first:

select v.ip, group_concat(v.visits / iv.maxvisits order by v.visits desc) as ratings

You can make the list more complex to include the id in it as well:

select v.ip,
      group_concat(concat(v.id, ':', v.visits / iv.maxvisits)) as ratings

Upvotes: 1

Related Questions