SQL Server中的AVG和COUNT

Question

我有一个评分系统，任何人都可以对其他人进行评价。每个人可以被多个人评判。为了计算平均值，我只想包含最新的值。

这在 SQL 中是否可行？

· Person 1 对 Person 2 进行了 5 分评价于 2011 年 1 月 2 日 <- 被忽略，因为有更新的评分 · Person 1 对 Person 2 进行了 2 分评价于 2011 年 1 月 3 日 · Person 2 对 Person 1 进行了 6 分评价于 2011 年 1 月 2 日 <- 同样被忽略 · Person 2 对 Person 1 进行了 3 分评价于 2011 年 1 月 3 日 · Person 3 对 Person 1 进行了 5 分评价于 2011 年 1 月 5 日

结果：

· Person 2 的平均值是 2。 · Person 1 的平均值是 4。

表格可能看起来像这样：评估者、被评估者、评分、日期。

敬礼，

Michael

- user1101780

---

你的表格是否也有一个代理合成主键？或者是自增的身份整数？如果是这样的话，查询会更加容易和高效。 - Ian Nelson

Answer 1

我可以做到。

假设您的表结构如下所示：

CREATE TABLE [dbo].[Ratings](
    [Evaluator] varchar(10),
    [Evaluatee] varchar(10),
    [Rating] int,
    [Date] datetime
);

以及像这样的值：

INSERT INTO Ratings
    SELECT 'Person 1', 'Person 2', 5, '2011-02-01' UNION
    SELECT 'Person 1', 'Person 2', 2, '2011-03-01' UNION
    SELECT 'Person 2', 'Person 1', 6, '2011-02-01' UNION
    SELECT 'Person 2', 'Person 1', 3, '2011-03-01' UNION
    SELECT 'Person 3', 'Person 1', 5, '2011-05-01'

那么Person 1的平均评分为：

SELECT AVG(Rating) FROM Ratings r1
    WHERE Evaluatee='Person 1' and not exists
    (SELECT 1 FROM Ratings r2
        WHERE r1.Evaluatee = r2.Evaluatee AND
            r1.evaluator=r2.evaluator AND
            r1.date < r2.date)

结果：

4

或者对所有被评估者进行分组，按被评估者分组：

SELECT Evaluatee, AVG(Rating) FROM Ratings r1
    WHERE not exists
    (SELECT 1 FROM Ratings r2
        WHERE r1.Evaluatee = r2.Evaluatee AND
            r1.evaluator = r2.evaluator AND
            r1.date < r2.date)
    GROUP BY Evaluatee

结果：

Person 1    4
Person 2    2

这可能看起来含有一个隐含的假设，即不存在相同日期的条目；但实际上这不是问题：如果这样的条目存在，那么你无法决定哪个是后创建的；你只能在它们之间随机选择。就像这里展示的一样，它们都被包括并平均计算 - 这可能是你能得到的最好解决方案，针对这种边界情况（尽管它略微偏向了那个人，给了他两票）。

为了避免这个问题，你可以简单地将日期作为主键或唯一索引的一部分 - 在这里明显的主键选择是列（评估者、被评估者、日期）。

- codeling

---

+1 - 我非常喜欢这种方法，出于尊重我会删除我的回答 :) - Ian Nelson

Answer 2

declare @T table
(
  evaluator int, 
  evaluatee int, 
  rating int,
  ratedate date
)

insert into @T values
(1, 2, 5, '20110102'),
(1, 2, 2, '20110103'),
(2, 1, 6, '20110102'),
(2, 1, 3, '20110103'),
(3, 1, 5, '20110105')

select evaluatee,
       avg(rating) as avgrating 
from (       
      select evaluatee,
             rating,
             row_number() over(partition by evaluatee, evaluator 
                               order  by ratedate desc) as rn
      from @T
     ) as T
where T.rn = 1
group by evaluatee

结果：

evaluatee   avgrating
----------- -----------
1           4
2           2

- Mikael Eriksson

Answer 3

这是可以做到的，但有时会非常困难 - SQL 不是为了比较行而设计的，只能比较列。我强烈建议您保留一个仅包含最新数据的附加表，并将其余部分存储在归档表中。

如果您必须以这种方式操作，那么我需要完整的表结构才能尝试编写此查询。特别是我需要知道哪些是唯一索引。

- Ariel