获取一组记录的平均值

Question

我有一个有类别(价格，视图，设施，位置)的营地点评表。以下查询用于按四个类别的平均值显示前20个评论。

SELECT
  ROUND(((R.price + R.location + R.facilities + R.view) / 4), 2) AS avg,
  R.poi_id,
  poi.Name
FROM
  `Reviews` AS `R`
  LEFT JOIN `poi` ON `R`.`poi_id` = `poi`.`ID`
ORDER BY avg DESC
LIMIT 20

然而，我已经意识到查询不能适应同一营地有多个评论的情况。

那么，有没有一个查询会返回排名最高的20个露营地( poiID )，以及该poi的价格、视图、设施、位置/评论数量的平均值？

Answer 1

average函数当然是累积性的，但是你可以自己实现它，方法是对评论分数进行SUM并对它们的数字进行COUNT：

SELECT
  ROUND(((R.price + R.location + R.facilities + R.view) / 4 * cnt), 2) AS avg,
  R.poi_id,
  poi.Name
FROM
  (SELECT SUM(price) AS price, 
          SUM(location) AS location,
          SUM(facilities) AS facilities,
          SUM(view) AS view,
          COUNT(*) AS cnt,
          poi_id
   FROM   Review
   GROUP BY poi_id) AS `R`
  LEFT JOIN `poi` ON `R`.`poi_id` = `poi`.`ID`
ORDER BY avg DESC
LIMIT 20