复杂的SQL SELECT可在单个列上计算评级

问题描述

它可能被视为的重复项，但是我的问题有点复杂。我有一个包含以下字段的MySQL表：

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

显示ip访问产品的次数。我终于想生成一个像这样的php数组：

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, ...
  ),
  .
  .
  .
);

评级等于某个ip中单个产品的访问次数除以所有产品访问次数的最大值例如：

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

---

我有性能方面的考虑，我想避免嵌套循环中的SQL查询。我认为上述SQL查询是错误的，因为它没有显示出预期的结果。

---

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.

推荐答案

该想法是使用子查询以计算总和，然后进行计算并将值放入一个逗号分隔的列中，您可以将其转换为php中的数组：

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;

编辑：

SQL中的表为本质上是无序的，并且SQL中的排序不稳定（这意味着未保留原始顺序）。您可以在 group_concat（）语句中指定顺序。例如，以下将按 id 排序结果：

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

您可以使列表更复杂，以在其中包含 id ：

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

这篇关于复杂的SQL SELECT可在单个列上计算评级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！