sql - 有效地选择最新答案

Question

SQL fiddle :http://sqlfiddle.com/#!3/9b459/6

我有一个表格，其中包含“您会参加此事件吗？”问题的答案。每个用户可能会响应多次，所有答案都存储在表中。通常我们只对最新的答案感兴趣，我正在尝试为此构建一个有效的查询。我使用的是 SQL Server 2008 R2。

一个事件的表格内容:

Column types: int, int, datetime, bit
Primary key: (EventId, MemberId, Timestamp)

请注意，成员(member) 18 首先回答“否”，后来回答"is"，成员(member) 20 首先回答"is"，后来回答“否”，成员(member) 11 回答“否”，后来又回答“否”。我想过滤掉这些成员的第一个答案。此外，可能有多个答案需要过滤 - 例如，用户可能会回答"is"、"is"、“否”、"is"、“否”、“否”、“否”。

我尝试了一些不同的想法，并在 SQL Server Management Studio 中通过输入所有查询、选择“显示估计执行计划”并以百分比形式比较每个查询的总成本来评估它们。这是评估性能的好方法吗？

到目前为止测试的不同查询:

-----------------------------------------------------------------
-- Subquery to select Answer (does not include Timestamp)
-- Cost: 63 %
-----------------------------------------------------------------
select distinct a.EventId, a.MemberId,
(
  select top 1 Answer
  from    Attendees
  where EventId   = a.EventId
  and   MemberId  = a.MemberId
  order by Timestamp desc
) as Answer
from    Attendees a
where a.EventId = 68

-----------------------------------------------------------------
-- Where with subquery to find max(Timestamp)
-- Cost: 13 %
-----------------------------------------------------------------
select a.EventId, a.MemberId, a.Timestamp, a.Answer
from     Attendees a
where  a.EventId = 68
and    a.Timestamp =
(
  select max(Timestamp)
  from     Attendees
  where  EventId  = a.EventId
  and    MemberId = a.MemberId
)
order by a.TimeStamp;

-----------------------------------------------------------------
-- Group by to find max(Timestamp)
-- Subquery to select Answer matching max(Timestamp)
-- Cost: 23 %
-----------------------------------------------------------------
select a.EventId, a.MemberId, max(a.Timestamp),
(
  select top 1 Answer
  from    Attendees
  where EventId   = a.EventId
  and   MemberId  = a.MemberId
  and   Timestamp = max(a.Timestamp)
) as Answer
from    Attendees a
where a.EventId = 68
group by a.EventId, a.MemberId
order by max(a.TimeStamp);

最好避免对每个成员使用子查询。在上一个查询中，我尝试使用group by，但仍然必须对答案列使用子查询。我真的很想要这样的东西，但这当然不是有效的 SQL:

select a.EventId, a.MemberId, max(a.Timestamp), a.Answer <-- Picked from the line selected by max(a.Timestamp)
from  Attendees a
where a.EventId = 68
group by a.EventId, a.MemberId
order by max(a.TimeStamp);

对于高效查询还有其他想法吗？

<小时/>

编辑:

SQL Fiddle 给我留下了深刻的印象，我现在已经在那里输入了我的实际数据: http://sqlfiddle.com/#!3/9b459/6

Answer 1

SQL Server 2008 支持公共(public)表表达式和窗口函数。

WITH recordsList
AS
(
    SELECT  EventID, MemberID, TimeStamp, Answer,
            ROW_NUMBER() OVER (PARTITION BY EventID, MemberID
                                ORDER BY Timestamp DESC) rn
    FROM    tableName
)
SELECT  EventID, MemberID, TimeStamp, Answer
FROM    recordsList
WHERE   rn = 1