AVG and COUNT in SQL Server

Question

I have a rating system in which anyone can view others. Each person can be evaluated by one person more than once. To calculate averages, I would like to include only the most current values.

Is this possible with SQL?

• Person 1 bets Person 2 from 5 to 1.2.2011 <- is ignored because there is a new person rating 1
• Person 1 bets Person 2 from 2 to 1.3.2011
• Person 2 bets Person 1 from 6 to 1.2.2011 <- also ignored
• Person 2 bets Person 1 from 3 to 1.3.2011
• Person 3 bets Person 1 from 5 to 1.5.2011.

Result:

• The average value for human 2 is 2.
• The average value for a person 1 is 4.

The table may look like this: evaluator, evaluatee, rating, date .

Yours faithfully

Michael

Answer 1

That's quite possible.

Suppose your table structure is as follows:

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

and values ​​like this:

 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' 

Then the average rating for Person 1 is:

 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) 

Result:

 4 

Or for all Evaluatee grouped by Evaluatee:

 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 

Result:

 Person 1 4 Person 2 2 

It may seem that he has an implicit assumption that no records exist with the same date; but this is actually not a problem: if such records can exist, then you cannot decide which one was made later; You can only choose randomly between them. As shown here, they are both included and averaged - this may be the best solution you can get for this borderline case (although it contributes a bit to this person by giving him two votes).

To avoid this problem altogether, you can simply make Date part of the primary key or a unique index - the columns (Evaluator, Evaluatee, Date) are the obvious choice for the primary key.

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 

Result:

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

Answer 3

It can be done, but it can be REALLY harry - SQL was not intended to compare rows, only columns. I highly recommend that you keep an extra table containing only the most recent data, and save the rest in the archive table.

If you have to do it this way, I will need a full table structure to try to write a query for this. In particular, I need to know what unique indexes.