SQL 查询 -AVG 最近两天

Question

我正在尝试获取最后两条记录的平均值并按名称对它们进行分组。

OId     ODate        Value      Name
1      05/01/2011   25          Mike
2      05/01/2011   56      Peter
3      05/01/2011   09      Robert
4      06/01/2011   21          Mike
5      06/01/2011   52      Peter
6      06/01/2011   04      Robert
7      10/01/2011   28          Mike
8      10/01/2011   57      Peter
9      10/01/2011   03      Robert

SELECT Name, avg(Value) as avg_score
FROM (
  SELECT Name, Value FROM S_ORDER BY dessc ODate 60
)
GROUP BY Name;

谢谢。

Answer 1

SELECT tLast.Name, 
  (tLast.Value + COALESCE(tLast2.Value, 0)) / CASE WHEN tLast2.Value IS NULL 1 ELSE 2 END As avg_score
FROM (
    SELECT last1.Name, MAX(last1.MaxID) MaxID, MAX(last2.OId) MaxID2
    FROM (
        SELECT Name, MAX(OId) MaxID
        FROM tableName
        GROUP BY Name
      ) last1 LEFT JOIN tableName last2 ON Name = last1.Name AND OId < last1.MaxID
    GROUP BY last1.Name
  ) rs JOIN tableName tLast ON rs.MaxID = tLast.OId
  LEFT JOIN tableName tLast2 ON rs.MaxID2 = tLast2.OId
ORDER BY tLast.Name

不建议按名称分组，您应该存储指向 user 表的 user_id，而不是存储名称。