代码之家 › 专栏 › 技术社区 › Pure.Krome

能否将此SQL语句重构为不使用列组/分区?

refactoring tsql sql-server sql

Pure.Krome · 技术社区 · 14 年前

我有下面的SQL语句,它工作得非常好。我希望看到如何重构它,这样就不需要使用等级/分区…如果可能的话。

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

它想做什么?

获取表中的所有记录,按客户机名称分组,然后按最近创建的记录排序。
仅按事件类型2(连接)或4(断开连接)筛选此项。
现在,对于每个客户机名称,检索最新的记录。

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

我确实喜欢等级划分,但我希望看看不使用它是否可行。

4 回复 | 直到 14 年前

Remus Rusanu 14 年前

还有另一个变化:选择客户机,然后使用交叉应用(..顶部(1)按……订购)以获取相关条目。

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

让我们用大约2.4米的行加载表:

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

我们什么时候和IO打交道 set statistics io on; set statistics time on; 在温暖的缓存里?

(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分离到一个规范化的表中,其中包含一个受信任的外键和它的日志条目,这将大大减少时间。(2,4)中entrytypeid的正确筛选索引也会有贡献。我们甚至没有开始探索并行性的可能性。

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

ErikE Russ Cam 14 年前

单表扫描、无窗口功能、单分组方式、重复日期没有问题、窗口功能的性能相同,甚至比实际大型查询的性能更好。(更新:我不知道与使用ties/cross-apply方法的前1名相比,它的表现如何。因为它使用扫描,所以在某些情况下可能会变慢。)

SELECT
   LogEntryID = Convert(int, Substring(Packed, 9, 4)),
   FileID = Convert(int, Substring(Packed, 13, 4)),
   CreatedOn = Convert(datetime, Substring(Packed, 1, 8)),
   EventTypeID = Convert(int, Substring(Packed, 17, 4))
FROM
   (
      SELECT
         Packed = Max(
            Convert(binary(8), CreatedOn)
            + Convert(binary(4), LogEntryID)
            + Convert(binary(4), FileID)
            + Convert(binary(4), EventTypeID)
         )
      FROM LogEntries
      WHERE EventTypeID IN (2,4)
      GROUP BY ClientName
   ) X

如果有人想看到这一点,这里有一些创建脚本:

USE tempdb
CREATE TABLE LogEntries (
   LogEntryID int not null identity(1,1),
   FileID int,
   CreatedOn datetime,
   EventTypeID int,
   ClientName varchar(30)
)

INSERT LogEntries VALUES (1, GetDate()-20, 2, 'bob')
INSERT LogEntries VALUES (1, GetDate()-19, 3, 'bob')
INSERT LogEntries VALUES (1, GetDate()-18, 4, 'bob')
INSERT LogEntries VALUES (1, GetDate()-17, 3, 'bob')
INSERT LogEntries VALUES (1, GetDate()-19.5, 2, 'anna')
INSERT LogEntries VALUES (1, GetDate()-18.5, 3, 'anna')
INSERT LogEntries VALUES (1, GetDate()-17.5, 4, 'anna')
INSERT LogEntries VALUES (1, GetDate()-16.5, 3, 'anna')

请注意,此方法利用给定数据类型的内部字节表示,这些数据类型的顺序与类型的值相同。像float或decimal这样的压缩数据类型将不起作用:这些类型首先需要转换为合适的类型,例如int、bigint或character。

此外,SQL 2008中的新日期和时间数据类型具有不同的表示形式,这些表示形式无法正确打包以用于此方法。我还没有检查时间数据类型,但是对于日期数据类型:

DECLARE @d date
SET @d ='99990101'
SELECT Convert(binary(3), @d) -- 0x6EB837

实际值是0x37B86E,所以它以相反的字节顺序存储它们(“零”日期是0001-01-01)。

Andomar 14 年前

你可以用专用的 left join :

select     cur.*
from       LogEntries cur
left join  LogEntries next
on         next.ClientName = cur.ClientName
           and next.EventTypeId in (2,4)
           and next.CreatedOn > cur.CreatedOn               
where      next.ClientName is null
           and cur.EventTypeId in (2,4)

这将自行联接表,并在 on 条件。在 where 子句,指定不存在后行。这样,除了每个客户机的最新行之外,您可以过滤掉所有的行。

Hogan 14 年前

干得好。可能更快…不确定。此外,这假定clientname+createdon是唯一的。

;WITH MostRecent AS
(
   SELECT ClientName, Max(CreatedOn) AS CreatedOn
   FROM LogEntries
   WHERE EventTypeID IN (2,4)
   GROUP BY ClientName
)
SELECT LogEntryId, FileId, CreatedOn, EventTypeId
FROM LogEntries L
INNER JOIN MostRecent R ON L.ClientName = R.ClientName AND L.CreatedOn = R.CreatedON

注意,我没有测试可能有打字错误。