mysql - 获取一组记录的平均值

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 个露营地 (poi) 以及该 poiID 的平均价格、景观、设施、位置/评论数量？

Answer 1

平均函数当然是累加的，但是你可以通过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