sql-server - 有效地为集合中的每个类别选择顶行

Question

我需要从已知集合中为每个类别选择顶行(有点类似于 this question )。问题是，如何在大量行上使此查询高效。

例如，让我们创建一个表，将温度记录存储在多个位置。

CREATE TABLE #t (
    placeId int,
    ts datetime,
    temp int,
    PRIMARY KEY (ts, placeId)
)

-- insert some sample data

SET NOCOUNT ON

DECLARE @n int, @ts datetime
SELECT @n = 1000, @ts = '2000-01-01'

WHILE (@n>0) BEGIN
    INSERT INTO #t VALUES (@n % 10, @ts, @n % 37)
    IF (@n % 10 = 0) SET @ts = DATEADD(hour, 1, @ts)
    SET @n = @n - 1
END

现在我需要获取每个位置 1、2、3 的最新录音。

这种方式很有效，但不能很好地扩展(而且看起来很脏)。

SELECT * FROM (
    SELECT TOP 1 placeId, temp
    FROM #t 
    WHERE placeId = 1
    ORDER BY ts DESC
) t1
UNION ALL
SELECT * FROM (
    SELECT TOP 1 placeId, temp
    FROM #t 
    WHERE placeId = 2
    ORDER BY ts DESC
) t2
UNION ALL
SELECT * FROM (
    SELECT TOP 1 placeId, temp
    FROM #t 
    WHERE placeId = 3
    ORDER BY ts DESC
) t3

以下看起来更好，但工作效率低得多(根据优化器，30% vs 70%)。

SELECT placeId, ts, temp FROM (
    SELECT placeId, ts, temp, ROW_NUMBER() OVER (PARTITION BY placeId ORDER BY ts DESC) rownum
    FROM #t
    WHERE placeId IN (1, 2, 3)
) t
WHERE rownum = 1

问题是，在后面的查询执行计划中，对#t 执行了聚集索引扫描，然后检索、排序、编号和过滤 300 行，只留下 3 行。对于前一个查询，取一行 3 次。

有没有办法在没有大量联合的情况下有效地执行查询？

Answer 1

我加载了 100,000 行(这仍然不足以减慢速度)，尝试了老式的方法:

select t.*
 from #t t
  inner join (select placeId, max(ts) ts
               from #t
               where placeId in (1,2,3)
               group by placeId) xx
   on xx.placeId = t.placeId
    and xx.ts = t.ts

并得到了大致相同的结果。

然后我颠倒了索引中列的顺序，以

CREATE TABLE #t ( 
    placeId int, 
    ts datetime, 
    temp int, 
    PRIMARY KEY (placeId, ts) 
) 

并且，在所有查询中，获得更少的页面读取和索引查找而不是扫描。

如果优化是你的目标并且你可以修改索引，我会修改主键，或者添加一个覆盖索引。