无需排序就能获得组ID的有效方法

Question

假设我有一个像这样的非规范化表:

CREATE TABLE Persons
(
    Id           int identity primary key,
    FirstName    nvarchar(100),
    CountryName  nvarchar(100)
)
INSERT INTO Persons
VALUES ('Mark',    'Germany'),
       ('Chris',   'France'),
       ('Grace',   'Italy'),
       ('Antonio', 'Italy'),
       ('Francis', 'France'),
       ('Amanda',  'Italy');

我需要构造一个查询，该查询返回每个人的姓名和他们所在国家的唯一ID。id不必是连续的;更重要的是，它们不必是的任意顺序。实现这一目标的最有效方法是什么?

最简单的解决方案似乎是DENSE_RANK:

SELECT FirstName, 
       CountryName, 
       DENSE_RANK() OVER (ORDER BY CountryName) AS CountryId
FROM Persons
-- FirstName  CountryName  CountryId
-- Chris      France       1
-- Francis    France       1
-- Mark       Germany      2
-- Amanda     Italy        3
-- Grace      Italy        3
-- Antonio    Italy        3

但是，这会导致对CountryName列进行排序，这是一个浪费性能的肥猪。我想出了这个替代方案，它使用ROW_NUMBER和众所周知的抑制其排序的技巧:

SELECT P.FirstName, 
       P.CountryName,
       C.CountryId
FROM Persons P
    JOIN (
        SELECT CountryName, 
               ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS CountryId
        FROM Persons
        GROUP BY CountryName
    ) C
    ON C.CountryName = P.CountryName
-- FirstName  CountryName  CountryId
-- Mark       Germany      2
-- Chris      France       1
-- Grace      Italy        3
-- Antonio    Italy        3
-- Francis    France       1
-- Amanda     Italy        3

我是否正确地假设第二个查询在一般情况下会表现得更好(不仅仅是在我的人造数据集上)?是否存在可能产生差异的因素(例如CountryName指数)?有没有更优雅的表达方式?

Answer 1

为什么你认为聚合会比窗口函数更便宜呢?我问这个问题，是因为我在这两方面都有一些经验，对这件事没有什么强烈的看法。如果按下，我猜窗口函数更快，因为它不需要聚合所有数据，然后再将结果加入。

这两个查询将有非常不同的执行路径。要想知道哪个性能更好，正确的方法是尝试一下。在您的环境中对足够大的数据样本运行这两个查询。

顺便说一下，我认为没有一个正确的答案，因为性能取决于几个因素:

• 哪些列被索引?
• 数据有多大?内存大小合适吗?
• 有多少不同的国家?

如果您关心性能，并且只想要一个唯一的数字，您可以考虑使用checksum()代替。这确实有碰撞的危险。对于200个左右的国家来说，这种风险非常非常小。另外，你可以对它进行测试，如果它确实发生了，你可以做些什么。查询将是:

SELECT FirstName, CountryName, CheckSum(CountryName) AS CountryId
FROM Persons;

Answer 2

第二个查询很可能会避免排序，因为它将使用散列匹配聚合来构建内部查询，然后使用散列匹配连接将ID映射到实际记录。

这实际上并不排序，但必须扫描原始表两次。

我是否正确地假设第二个查询在一般情况下会表现得更好(不仅仅是在我的人造数据集上)?

不一定。如果您在CountryName上创建了聚集索引，那么排序将不是问题，所有事情都将在一次传递中完成。

有更优雅的表达方式吗?

一个"正确"的计划应该是一次完成哈希和哈希查找。

读取每条记录时，必须与哈希表进行匹配。如果匹配，将返回存储的ID;如果未命中，则将新国家添加到哈希表中，并为其分配新的ID，并返回新分配的ID。

但是我想不出一个方法让SQL Server在单个查询中使用这样的计划。

更新:

如果你有很多记录，很少的国家，最重要的是，CountryName上有一个非聚集索引，你可以模拟松散扫描来建立一个国家列表:

DECLARE  @country TABLE
         (
         id INT NOT NULL IDENTITY PRIMARY KEY,
         countryName VARCHAR(MAX)
         )
;
WITH    country AS
        (
        SELECT  TOP 1
                countryName
        FROM    persons
        ORDER BY
                countryName
        UNION ALL
        SELECT  (
                SELECT  countryName
                FROM    (
                        SELECT  countryName,
                                ROW_NUMBER() OVER (ORDER BY countryName) rn
                        FROM    persons
                        WHERE   countryName > country.countryName
                        ) q
                WHERE   rn = 1
                )
        FROM    country
        WHERE   countryName IS NOT NULL
        )
INSERT
INTO    @country (countryName)
SELECT  countryName
FROM    country
WHERE   countryName IS NOT NULL
OPTION  (MAXRECURSION 0)
SELECT  p.firstName, c.id
FROM    persons p
JOIN    @country c
ON      c.countryName = p.countryName

Answer 3

group by use also sort operator在后台(group是基于'排序和比较'，就像c#中的Icomparable)