这个 SQL 语句能否重构，不使用 RANK/PARTITION？

Question

这个 SQL 语句能否重构，不使用 RANK/PARTITION？

3

我有以下的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

它试图做什么？

按客户名称分组，然后按最近创建的顺序，抓取表中的所有记录。
通过过滤只选择事件类型#2（连接）或#4（断开连接）。
现在，对于每个客户名称，检索最新的记录。

实际上，这是为了获取表中每个唯一用户的最新事件（连接或断开连接）。

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

- Pure.Krome

对于所有回复的人 -> 我真心感到无言以对（但是是好的）。我喜欢有许多不同的方法。这就是为什么我喜欢 Stack Overflow。我会检查它们并看看我想要如何解决这个问题。再次感谢所有回复和 Stack Overflow <3 - Pure.Krome

你之前的问题解决了吗？你问过我是否需要一个模式（schema）来帮助你。如果有模式信息，我仍然很乐意更新我的查询。 - ErikE

抱歉，我在工作要求上时间不够了，只能凑合着用可行的方法完成。真诚道歉。 - Pure.Krome

4个回答

2

在单表扫描、没有窗口函数、单个group by的情况下，如果没有重复日期的问题，与窗口函数相比性能相同，甚至在处理大型查询时性能更好。（更新：我不知道它与TOP 1 WITH TIES / CROSS APPLY方法相比的性能如何。由于它使用了扫描，因此在某些情况下可能会更慢。）

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中的新日期和时间数据类型具有不同的表示形式，无法正确打包以与此方法一起使用。我还没有检查Time数据类型，但对于Date数据类型：

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

实际值为0x37B86E，因此它以相反的字节顺序存储它们（“零”日期是0001-01-01）。

- ErikE

+1 有趣的技巧。我想知道对于大量行，二进制转换是否比反连接执行得更好。 - Andomar

根据我的经验，二进制转换非常节省CPU资源，并且通常比更多的I/O更划算。将值转换为字符串是缓慢而昂贵的，但是二进制转换基本上只是一个内存复制。 - ErikE

我特别想知道这个解决方案与Quassnoi使用递归CTE和CROSS APPLY进行优化的方案相比如何。这个解决方案很好，但仍会导致全表/索引扫描，而Quassnoi的方案最终有点像Oracle的跳过扫描。 - Aaronaught

我收到了这个错误信息：“Msg 8152，Level 16，State 17，Line 1 String or binary data would be truncated”。你需要一张表模式吗？ - Pure.Krome

@Pure.Krome：那会很有帮助。上面的查询与下面的创建表语句一起运行。索引也会很棒。 - ErikE

1

你可以使用独占的左连接：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子句中，您可以指定不得存在后来的行。通过这种方式，您可以过滤每个客户端除最新行以外的所有行。

- Andomar

这真的有效吗？如果有效，那么这是一个很巧妙的技巧，但就速度而言如何呢？在我看来，这好像与每行执行子查询相同，这就是SQL的实现方式吗？ - Hogan

它确实有效并且速度非常快，但可读性较差。如果您注重性能，请参考http://explainextended.com/2009/12/01/sql-server-selecting-records-holding-group-wise-maximum-with-ties/上的“cross apply”解决方案。 - Andomar

@OMG Ponies：那篇文章很有趣，尽管我希望他不要说NOT EXISTS不是连接条件。带有外部引用的NOT EXISTS始终是一种连接。 - ErikE

@Emtucifor：请向作者提出。 - OMG Ponies

大家好。我在原始帖子（在编辑之前）发布后不久就尝试了它，花了1小时30分钟。我没有查看索引和查询计划，但我相信这是我的模式而不是查询+缺乏索引的问题 :) 这是一个有1288095行的表。 - Pure.Krome

显示剩余6条评论

0

给你。可能会更快...不确定。此外，这假设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

注意，我没有测试，可能会有错别字。

- Hogan

请注意，此解决方案不会处理同一人的重复创建日期。 - ErikE

@Emtucifor，我不是在第一行说了吗？ - Hogan

但是客户端名称和创建时间不是唯一的。这就是为什么我最初使用PARTITION关键字的原因。 - Pure.Krome

@ Hogan：我的错误！你做到了。我希望我能删除评论。 - ErikE

现在我明白了。你在8小时前编辑了你的答案，添加了第一行内容，而我在7小时前发表了我的评论，但是我已经打开了这个页面超过一个小时，所以当我评论时它还不在我的版本中。 - ErikE

@Emtucifor：没错，我第一版确实没有关于唯一性的注释。干杯。 - Hogan

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Remus Rusanu · Accepted Answer

另一种方法：选择客户，然后使用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

现在我们来加载一张有 2.4M 行数据的表：

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; set statistics time 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.

在我的笔记本电脑上（已经使用了4年，只有1GB的RAM），从240万条数据中获取数据仅需1.9秒。在模式设计中，仍有很大的改进空间。将ClientName分离到一个规范化表中，并将外键从LogEntries信任到它中将显着减少时间。在EntryTypeId IN（2,4）上正确过滤索引也会有所贡献。我们甚至还没有开始探索并行性可能性。

这是SQL，性能取决于模式设计的绘图板，而不是查询的文本编辑器。