如何查询数据库中用户的排名，但只考虑每个用户的最新条目？

Question

假设我有一个名为“Scrape”的数据库表，其设置可能如下：

UserID (int)   
UserName (varchar)  
Wins (int)   
Losses (int)  
ScrapeDate (datetime)

我试图根据用户的赢/输比对他们进行排名。但是，每周我都会抓取有关用户的新数据，并在抓取表中添加另一个条目。

如何查询按赢/输排序的用户列表，但只考虑最近的条目 (ScrapeDate)？

另外，您认为人们会访问该网站并且刮擦可能正在完成中这很重要吗？

例如，我可以：

1 - Bob - Wins: 320 - Losses: 110 - ScrapeDate: 7/8/09  
1 - Bob - Wins: 360 - Losses: 122 - ScrapeDate: 7/17/09  
2 - Frank - Wins: 115 - Losses: 20 - ScrapeDate: 7/8/09  

其中，这表示到目前为止仅更新了 Bob 的抓取，并且正在更新 Frank 但尚未插入。您也将如何处理这种情况？

所以，我的问题是：

1. 您如何处理仅查询每个用户最近的抓取以确定排名
2. 您是否认为数据库可能处于更新状态（特别是如果抓取可能需要长达 1 天的时间）完成），并且并非所有用户都已完全更新，这还重要吗？如果是这样，你会如何处理？

谢谢您，也感谢您对我的相关问题的答复：

从网页中抓取大量统计信息时，我应该多久将收集到的结果插入到数据库中？

Answer 1

这就是我所说的“每组最大的”问题。它每周在 StackOverflow 上出现几次。

我使用外连接技术解决了此类问题：

SELECT s1.*, s1.wins / s1.losses AS win_loss_ratio
FROM Scrape s1
LEFT OUTER JOIN Scrape s2
  ON (s1.username = s2.username AND s1.ScrapeDate < s2.ScrapeDate)
WHERE s2.username IS NULL
ORDER BY win_loss_ratio DESC;

这将为每个用户名仅返回一行 - ScrapeDate 列中具有最大值的行。这就是外连接的用途，尝试将s1与具有相同用户名和更大日期的其他行s2进行匹配。如果没有这样的行，则外连接将为 s2 的所有列返回 NULL，然后我们就知道 s1 对应于给定用户名具有最大日期的行。

当您正在进行部分完成的抓取时，这也应该有效。

这种技术不一定像其他答案给出的 CTE 和 RANKING 解决方案那么快。您应该两者都尝试一下，看看哪种更适合您。我更喜欢我的解决方案的原因是它适用于任何 SQL 风格。

Answer 2

尝试如下操作：

1. 为每个用户选择用户 ID 和上次条目的最大日期。
2. 根据以上查询结果对记录进行选择和排序以获得排名。

这应该可行，但取决于您的数据库大小。

DECLARE 
    @last_entries TABLE(id int, dte datetime)

-- insert date (dte) of last entry for each user (id)
INSERT INTO
    @last_entries (id, dte)
SELECT
    UserID,
    MAX(ScrapeDate)
FROM
    Scrape WITH (NOLOCK)
GROUP BY
    UserID

-- select ranking
SELECT
    -- optionally you can use RANK OVER() function to get rank value
    UserName,
    Wins,
    Losses
FROM
    @last_entries
    JOIN
        Scraps WITH (NOLOCK)
    ON
        UserID = id
        AND ScrapeDate = dte
ORDER BY
    Winds,
    Losses

我没有测试此代码，因此它无法在首次运行时编译。

Answer 3

您问题第一部分的答案取决于您使用的 SQL Server 版本 - SQL 2005+ 提供 排名函数使这种查询比 SQL 2000 及之前的版本更简单。如果您指出您正在使用哪个平台，我将更详细地更新此内容。

我怀疑处理第 2 部分的最清晰方法是显示最新完整抓取练习的统计数据，否则您不会显示时间一致的排名（尽管，如果您的数据收集练习需要 24 小时，则存在一定的自由度）已经）。

为了简化这一过程，您可以创建一个表来保存有关每个抓取操作的元数据，为每个操作提供一个 ID、开始日期和完成日期（至少），并显示与最新完整抓取相关的记录。为了使这更容易，您可以从数据收集表中删除“抓取日期”，并将其替换为将每个数据行链接到抓取表中的行的外键。

编辑

以下代码说明了如何根据用户的最新分数对用户进行排名，而不管它们是否具有时间一致性：

create table #scrape
(userName varchar(20)
,wins int
,losses int
,scrapeDate datetime
)

INSERT #scrape
      select 'Alice',100,200,'20090101'
union select 'Alice',120,210,'20090201'
union select 'Bob'  ,200,200,'20090101'
union select 'Clara',300,100,'20090101'
union select 'Clara',300,210,'20090201'
union select 'Dave' ,100,10 ,'20090101'


;with latestScrapeCTE
AS
(
        SELECT *
               ,ROW_NUMBER() OVER (PARTITION BY userName
                                   ORDER BY scrapeDate desc
                                  ) AS rn
               ,wins + losses AS totalPlayed
               ,wins - losses as winDiff
        from #scrape
)
SELECT userName
       ,wins
       ,losses
       ,scrapeDate
       ,winDiff
       ,totalPlayed
       ,RANK() OVER (ORDER BY winDiff desc
                              ,totalPlayed desc
                    ) as rankPos
FROM latestScrapeCTE
WHERE rn = 1
ORDER BY rankPos

编辑2

使用元数据表进行选择的说明最新完整的抓取：

create table #scrape_run
(runID int identity
,startDate datetime
,completedDate datetime
)

create table #scrape
(userName varchar(20)
,wins int
,losses int
,scrapeRunID int
)


INSERT #scrape_run
select '20090101', '20090102'
union select '20090201', null --null completion date indicates that the scrape is not complete

INSERT #scrape
      select 'Alice',100,200,1
union select 'Alice',120,210,2
union select 'Bob'  ,200,200,1
union select 'Clara',300,100,1
union select 'Clara',300,210,2
union select 'Dave' ,100,10 ,1


;with latestScrapeCTE
AS
(
        SELECT TOP 1 runID
                     ,startDate
        FROM #scrape_run
        WHERE completedDate IS NOT NULL
)
SELECT userName
       ,wins
       ,losses
       ,startDate     AS scrapeDate
       ,wins - losses AS winDiff
       ,wins + losses AS totalPlayed
       ,RANK() OVER (ORDER BY (wins - losses)  desc
                              ,(wins + losses) desc
                    ) as rankPos
FROM #scrape
JOIN latestScrapeCTE
ON   runID = scrapeRunID
ORDER BY rankPos