MySQL:查找最后 x 条记录的每个条目的平均值

Question

我想弄清楚如何获取每位销售人员在过去 100 次评分中的平均评分(如果他们目前在职)并且平均评分低于 3(满分 5)。

我有下表(省略了查询中不需要的信息):

users
id    name     employed
-----------------------
1     John     1
2     Sue      1
3     Bob      0
...

sales
id     users_id
------------------
100    3
101    2
102    3
103    1
...

ratings
sales_id   rating
-----------------
100        4
101        5
102        5
103        2
...

我当前的查询搜索了所有内容并返回了所有订单的平均值，但我希望它只获取最近的 100 个评分(如果销售人员没有售出那么多商品，则更少)，仍然不包括任何不再受雇或最近 100 个订单的评级大于 3。这是当前查询:

SELECT u.name, avg(r.rating) as avg_rating, count(r.rating)
FROM users AS u
JOIN sales AS s ON s.users_id = u.id
JOIN ratings AS r ON r.sales_id = s.id
WHERE u.employed = 1
GROUP BY u.id
HAVING avg_rating <= 3;

任何帮助都会很棒!谢谢! :D

Answer 1

您可以使用我的 sql 变量来跟踪评分的数量，以便您只能获得最近的 100 个评分，按 sales_id 排序以便获得最近的评分。

SQL FIDDLE DEMO

SELECT T.name, avg(T.rating) as avg_rating, count(T.rating)
FROM
(
  SELECT u.name, r.rating, @num := if (@name = name, @num+1, 1) as rn,
       @name:= name as var_name
  FROM users AS u
  JOIN sales AS s ON s.users_id = u.id
  JOIN ratings AS r ON r.sales_id = s.id
  AND u.employed = 1
  JOIN ( select @name :='' , @num :=1) var
  order by sales_id desc
)T
where T.rn <=100
GROUP BY T.name
HAVING avg_rating <= 3