主页 / dba / 问题 / 318188
Accepted
Rebecca
Asked: 2022-10-14 01:44:07 +0800 CST

为什么我在 STRING_AGG 中的 ORDER BY 并不总是有效？

  • 772

我有一个表，其中包含一个记录 ID、一个组 ID（将 1 个或多个记录链接到一个组中）和每个记录的哈希值。

CREATE TABLE HashTable(
    RecordID VARCHAR(255),
    GroupIdentifier VARCHAR(255),
    Hash VARCHAR (255),
    GroupHashList VARCHAR(4000)
)

（我知道这不是一个高效的表，但对于本示例而言，它只是一个临时表）。

我想为每个组生成一个哈希，所以我认为最简单的方法是连接组中每个记录的哈希。RecordID 是唯一的，但这些记录相关的内容不一定是唯一的，因此哈希可能是重复的。这样做的目的是标记完全重复的组，即一个组是该组中的所有记录都是另一个组中所有记录的重复。如果要将组的所有成员识别为重复组，则 GUI 需要组的所有成员具有相同的哈希值。

我正在使用 STRING_AGG 连接组中记录的各个散列，并按散列对它们进行排序，以确保我得到重复组的相同字符串。我实际上并不关心哈希的顺序是什么，只要每次都相同。当我将它作为 SELECT 查询运行时，它工作正常，我可以看到重复组的相同字符串。当我采用相同的 SELECT 查询并将其放入 UPDATE 查询时，排序似乎丢失了。

SELECT STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC) 
FROM HashTable
GROUP BY [GroupIdentifier]

这给出了结果（例如一对重复组）：

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697

当我将相同的代码放入 UPDATE 查询时，它没有正确排序它们：

UPDATE HashTable
SET GroupHashList = c.HashList
FROM HashTable
INNER JOIN (
    SELECT (STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC)) AS [HashList],
        [GroupIdentifier]
    FROM HashTable
    GROUP BY [GroupIdentifier]) c
ON c.[GroupIdentifier] = HashTable.[GroupIdentifier]

写入表的相同两组的结果：

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;E44256CE7CFCB971EB679BAC25A697;90E749375DF806CB6E3F5CA48FFA38

我错过了什么？

我第一次得到的是

Hash1; Hash2; Hash3
Hash1; Hash2; Hash3

但是当它在 UPDATE 查询中时，我得到

Hash1; Hash2; Hash3
Hash1; Hash3; Hash2

更新查询是按记录 ID 排序的，虽然不知道是不是巧合。（https://dbfiddle.uk/CPG1-z2l）

sql-server

1 个回答

  1. Best Answer

    这似乎是优化器中的一个错误。

    优化器在意识到连接是自连接后，将其转换为窗口聚合。尽管STRING_AGG不能作为窗口聚合使用，但它可以做到这一点。该规则称为GenGbApplySimple，并允许将自联接转换为窗口聚合。到目前为止，这并没有什么特别的问题。

    计划

    粘贴计划

    问题是聚合超过了错误的值。它聚合的是外部值而不是内部值。

    如果你给这两个引用不同的别名，那么仔细检查查询计划就会发现错误。

    STRING_AGG([dbo].[HashTable].[Hash] as [HT1].[Hash],'')
WITHIN GROUP (ORDER BY [HT2].[Hash])

    另一个问题是与该规则一起使用的聚合（例如MIN, MAX, AVG）没有WITHIN GROUP要满足的顺序，因此替换计划没有考虑到它。这似乎STRING_AGG不是为了使用GbApply规则，或者需要进行工作以使其兼容（尊重排序请求）。

    正如您在下面看到的，排序仅按相关列排序GroupIdentifier，而不是按中Hash使用的列排序WITHIN GROUP。

    <OrderBy>
  <OrderByColumn Ascending="1">
    <ColumnReference
      Database="[...]"
      Schema="[dbo]"
      Table="[HashTable]"
      Alias="[HT1]"
      Column="GroupIdentifier">
    </ColumnReference>
  </OrderByColumn>
</OrderBy>

    如果您是sysadmin，则可以使用以下未记录的OPTION.

    OPTION (QUERYRULEOFF GenGbApplySimple)

    作为一种解决方法，防止应用此优化的一个选项是使用分组OUTER APPLY

    UPDATE HT1
SET GroupHashList = C.HashList
OUTPUT inserted.*
FROM HashTable AS HT1
OUTER APPLY
(
    SELECT
        HashList =
            STRING_AGG(HT2.[Hash], ';')
                WITHIN GROUP (ORDER BY HT2.[Hash] ASC)
    FROM HashTable AS HT2
    WHERE HT2.GroupIdentifier = HT1.GroupIdentifier
) C;

    这使您可以使用Stream Aggregate进行非常简单的自加入。

    db<>小提琴

    我强烈建议您将此作为错误提交给Microsoft。

    您也可以留下反馈，但这通常不会导致特定的响应。

    顺便说一句，在编写多表语句时，您应该遵循Conor Cunningham建议的别名规则：UPDATE

    非 ANSI FROM 子句（您在此处使用）具有特定的绑定行为，这些行为可能是您所期望的，也可能不是您所期望的。我建议您首先将 3 个对哈希表的引用命名为不同的别名，然后确保您明确引用了您想要的那个。可能（我猜）它绑定到与您想象的不同的人，并因此为您提供不希望的输出。

    • 22

相关问题