Reputation: 17964
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
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