sql - 是否可以将此 Sql 语句重构为不使用 RANK/PARTITION？

Question

我有以下 sql 语句，它工作得很好。我希望看到如何重构它，以便它不需要使用 RANK/PARTITION ... 如果可能的话。

SELECT LogEntryId, FileId, CreatedOn, EventTypeId
FROM (SELECT a.LogEntryId, a.FileId, a.CreatedOn,  a.EventTypeId, 
        RANK() OVER (PARTITION BY ClientName ORDER BY a.CreatedOn DESC) AS MostRecentEventRank
    FROM LogEntries a
    WHERE (a.EventTypeId = 2 or a.EventTypeId = 4)) SubQuery
WHERE MostRecentEventRank = 1

它想做什么？

1. 获取表中的所有记录，按客户名称分组，然后按最近创建的顺序排序。
2. 仅按事件类型 #2(连接)或 #4(断开)过滤。
3. 现在，对于每个客户名称，检索最近的记录。

这实际上是为表中的每个唯一用户获取最近的事件(连接或断开连接)。

我确实喜欢 RANK/PARTITION，但我希望看看是否可以不使用它。

Answer 1

还有另一种变体:选择客户端，然后使用 CROSS APPLY (.. TOP (1) ... ORDER BY ...) 获取相关条目。

SELECT c.ClientName,r.LogEntryId, r.FileId, r.CreatedOn,  r.EventTypeId
FROM (
 SELECT DISTINCT ClientName
 FROM LogEntries
 WHERE EventTypeId IN (2,4)) as c
CROSS APPLY (
   SELECT TOP (1) a.LogEntryId, a.FileId, a.CreatedOn,  a.EventTypeId
   FROM LogEntries as a
   WHERE a.ClientName = c.ClientName
   AND a.EventTypeId IN (2,4)
   ORDER BY a.CreatedOn DESC) as r;

更新

在不知道架构的情况下谈论 T-SQL 查询的性能是无意义的。此查询在根据其需要正确设计的模式上是完美的。由于访问是通过 ClientName 和 CreatedOn，因此即使是简单的模式也需要考虑到这一点:

CREATE TABLE LogEntries (
   LogEntryId int identity(1,1),
   FileID int,
   CreatedOn datetime,
   EventTypeID int,
   ClientName varchar(30)
);

create clustered index cdxLogEntries on LogEntries (
    ClientName, CreatedOn DESC);
go

让我们用大约 240 万行加载表:

declare @i int;
set @i = 0;

while @i < 1000
begin
    insert into LogEntries (FileId, CreatedOn, EventTypeId, ClientName)
    select cast(rand()*100 as int),
        dateadd(minute, -rand()*10000, getdate()),
        cast(rand() * 5 as int),
        'Client' + cast(@i as varchar(10))
        from master..spt_values;
    set @i = @i+1;
end

set statistics io on; 我们得到什么时间和 IO？设置统计时间； 在热缓存上？

(410 row(s) affected)
Table 'LogEntries'. Scan count 411, logical reads 14354, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 1219 ms,  elapsed time = 1932 ms.

1.9 秒从我的笔记本电脑(已使用 4 年，内存为 1Gb)上的 240 万个条目中获取数据。模式设计仍有很大的改进空间。将 ClientName 分离到一个规范化的表中，并使用来自 LogEntries 的可信外键将其显着减少。 EntryTypeId IN (2,4) 上的适当过滤索引也会有所贡献。我们甚至没有开始探索并行性的可能性。

这是 SQL，性能是在架构的绘图板上获得的，而不是在查询的文本编辑器中获得的。