SQL Server使用聚合函数选取随机（或第一个）值

Question

SQL Server使用聚合函数选取随机（或第一个）值

sql-serversql-server-2008randomaggregationsql-execution-plan

4

如何让SQL Server在聚合时返回第一个值（任意一个，我不在乎它是什么，只需要速度快）？

例如，假设我有以下数据：

ID      Group
1       A
2       A
3       A
4       B
5       B

我需要获取每个组中的任意一个ID。我可以按照以下方式完成这个任务：

Select 
max(id)
,group 
from Table 
group by group

返回

ID      Group
3       A
5       B

这样做虽然可行，但我认为请求SQL Server计算最高ID似乎有些愚蠢，因为它只需要选择遇到的第一个ID。

谢谢

另外，这些字段已经建立索引了，所以可能并没有什么区别吧？

- Karl

1个回答

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

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

- Martin Smith · Accepted Answer

有一个未记录的聚合叫做ANY，它不是有效的语法，但可能会出现在您的执行计划中。然而，这并没有提供任何性能优势。

假设以下表格和索引结构

CREATE TABLE T
(
id int identity primary key,
[group] char(1) 
)

CREATE NONCLUSTERED INDEX ix ON T([group])

INSERT INTO T
SELECT TOP 1000000 CHAR( 65 + ROW_NUMBER() OVER (ORDER BY @@SPID) % 3)
FROM sys.all_objects o1, sys.all_objects o2, sys.all_objects o3

我已经填充了示例数据，以便每个组中有许多行。

您的原始查询。

SELECT MAX(id),
       [group]
FROM   T
GROUP  BY [group]

提供了表 'T'。扫描计数1，逻辑读取1367和计划

  |--Stream Aggregate(GROUP BY:([[T].[group]) DEFINE:([Expr1003]=MAX([[T].[id])))
       |--Index Scan(OBJECT:([[T].[ix]), ORDERED FORWARD)

重写以获取ANY聚合...

;WITH cte AS
(
SELECT *,
        ROW_NUMBER() OVER (PARTITION BY [group] ORDER BY [group] ) AS RN
FROM T)
SELECT id,
       [group]
FROM    cte     
WHERE RN=1

给出 表 'T'。扫描次数 1，逻辑读取 1367 和计划

  |--Stream Aggregate(GROUP BY:([[T].[group]) DEFINE:([[T].[id]=ANY([[T].[id])))
       |--Index Scan(OBJECT:([[T].[ix]), ORDERED FORWARD)

尽管可能SQL Server在找到第一个值后就停止处理该组并跳至下一个，但它并没有这样做。它仍然处理所有行，逻辑读取量相同。

对于此特定示例中的大量组，更有效的版本将是递归CTE。

WITH    RecursiveCTE
AS      (
        SELECT TOP 1 id, [group]
        FROM T
        ORDER BY [group]
        UNION   ALL
        SELECT  R.id, R.[group]
        FROM    (
                SELECT  T.*,
                        rn = ROW_NUMBER() OVER (ORDER BY (SELECT 0))
                FROM    T
                JOIN    RecursiveCTE R
                        ON  R.[group] < T.[group]
                ) R
        WHERE   R.rn = 1
        )
SELECT  *
FROM    RecursiveCTE
OPTION  (MAXRECURSION 0);

这给出了

Table 'Worktable'. Scan count 2, logical reads 19
Table 'T'. Scan count 4, logical reads 12

逻辑读取量要少得多，因为它检索每个组的第一行，然后查找下一个组，而不是读取大量不对最终结果有贡献的记录。