递归公共表达式为什么会顺序执行分析函数（ROW_NUMBER）？

Question

递归公共表达式为什么会顺序执行分析函数（ROW_NUMBER）？

sqlsql-servercommon-table-expressionrow-numberrecursive-cte

12

昨天我回答了一个递归CTE的问题，揭示了这些在SQL Server中实现的方式存在的问题（可能也存在于其他RDBMS中？）。基本上，当我尝试对当前递归级别使用ROW_NUMBER时，它会针对当前递归级别的每个行子集运行。我期望这将在真正的SET逻辑中起作用，并针对整个当前递归级别运行。

从这篇MSDN文章看来，我发现的问题是有意为之的：

CTE递归部分中的分析和聚合函数应用于当前递归级别的数据集，而不是CTE的数据集。 像ROW_NUMBER这样的函数仅在由当前递归级别传递给它们的数据子集上运行，而不是在传递给CTE递归部分的整个数据集上运行。有关详细信息，请参见J.在递归CTE中使用分析函数。

在我的挖掘过程中，我找不到任何解释为什么选择以这种方式工作的地方？这更像是集合语言中的过程性方法，因此它与我的SQL思维流程相矛盾，并且在我看来非常令人困惑。 有人知道并/或可以解释为什么递归CTE以程序方式处理递归级别的分析函数吗？

这是帮助可视化的代码：

请注意，每个代码输出中的RowNumber列。

这里是CTE的SQLFiddle（仅显示递归的第二层）

WITH myCTE
AS
(
  SELECT *, ROW_NUMBER() OVER (ORDER BY Score desc) AS RowNumber, 1 AS RecurseLevel
  FROM tblGroups
  WHERE ParentId IS NULL

  UNION ALL

  SELECT tblGroups.*, 
      ROW_NUMBER() OVER (ORDER BY myCTE.RowNumber , tblGroups.Score desc) AS RowNumber, 
      RecurseLevel + 1 AS RecurseLevel
  FROM tblGroups
      JOIN myCTE
          ON myCTE.GroupID = tblGroups.ParentID
 )
SELECT *
FROM myCTE
WHERE RecurseLevel = 2;

这里是第二个SQLFiddle，展示了我希望公共表表达式（CTE）实现的功能（只需要展示第二层级以显示问题）。

WITH myCTE
AS
(
  SELECT *, ROW_NUMBER() OVER (ORDER BY Score desc) AS RowNumber, 1 AS RecurseLevel
  FROM tblGroups
  WHERE ParentId IS NULL
 )
  SELECT tblGroups.*, 
      ROW_NUMBER() OVER (ORDER BY myCTE.RowNumber , tblGroups.Score desc) AS RowNumber, 
      RecurseLevel + 1 AS RecurseLevel
  FROM tblGroups
      JOIN myCTE
          ON myCTE.GroupID = tblGroups.ParentID;

我一直认为SQL递归CTE的运行方式应该更像这个while循环

DECLARE @RecursionLevel INT
SET @RecursionLevel = 0
SELECT *, ROW_NUMBER() OVER (ORDER BY Score desc) AS RowNumber, @RecursionLevel AS recurseLevel
INTO #RecursiveTable
FROM tblGroups
WHERE ParentId IS NULL

WHILE EXISTS( SELECT tblGroups.* FROM tblGroups JOIN #RecursiveTable ON #RecursiveTable.GroupID = tblGroups.ParentID WHERE recurseLevel = @RecursionLevel)
BEGIN

    INSERT INTO #RecursiveTable
    SELECT tblGroups.*, 
        ROW_NUMBER() OVER (ORDER BY #RecursiveTable.RowNumber , tblGroups.Score desc) AS RowNumber, 
        recurseLevel + 1 AS recurseLevel
    FROM tblGroups
        JOIN #RecursiveTable
            ON #RecursiveTable.GroupID = tblGroups.ParentID
    WHERE recurseLevel = @RecursionLevel
    SET @RecursionLevel = @RecursionLevel + 1
END

SELECT * FROM #RecursiveTable ORDER BY RecurseLevel;

- Justin Pihony

所有递归CTE目前都使用相同的基本计划，其中将行添加到充当堆栈的Spool中，然后使用嵌套循环逐行处理该行。与此类似，EXCEPT也存在类似问题如此问题所述。 - Martin Smith

@MartinSmith 是的，我明白这一点，但我的问题是为什么它要这样做，当它可以轻松地将其视为一个基于集合的递归。这是SQL的优势，而不是这种过程化方法。 - Justin Pihony

不知道。猜想更简单或更高效的实现？大多数展示逻辑和物理实现差异的函数都被禁止了。EXCEPT 将加入列表。一个 Connect 项目关于 ROW_NUMBER 表明，他们在2008年也曾这样做过，但为 hierarchyids 的某些用例颠倒了它。 - Martin Smith

@MartinSmith 嗯，我和Paul White的想法不一致，但我理解他们的推理。如果你把你的评论发表为答案，我会接受，因为那正是我想要的... MS的推理。 - Justin Pihony

这似乎是SQL Server中的实现细节。对于PostgreSQL，第一条语句返回您所期望的内容：http://sqlfiddle.com/#!1/4c6ec/1，而Oracle似乎与SQL Server工作方式相同：http://sqlfiddle.com/#!4/4c6ec/13 - user330315

显示剩余2条评论

1个回答

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

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

- Younes · Accepted Answer

分析函数在解析时需要一个已知的结果集。它们依赖于以下、前面或完整的结果集来计算当前值。因此，在包含分析函数的视图上永远不允许合并视图。为什么？因为这会改变结果。

例如：

    Select * from (
      select row_number() over (partition by c1 order by c2) rw, c3 from t) z
    where c3=123

不同于

    select row_number() over (partition by c1 order by c2) rw, c3 from t 
    where c3=123

这两个查询将返回不同的rw值。这就是包含分析函数的子查询在与其他部分合并之前始终被完全解析的原因。

更新：

查看第二个查询：

WITH myCTE
AS
(
  SELECT *, ROW_NUMBER() OVER (ORDER BY Score desc) AS RowNumber, 1 AS RecurseLevel
  FROM tblGroups
  WHERE ParentId IS NULL
 )
  SELECT tblGroups.*, 
      ROW_NUMBER() OVER (ORDER BY myCTE.RowNumber , tblGroups.Score desc) AS RowNumber, 
      RecurseLevel + 1 AS RecurseLevel
  FROM tblGroups
      JOIN myCTE
          ON myCTE.GroupID = tblGroups.ParentID;

它的工作方式与写成以下形式完全相同（具有相同的执行计划和结果）：

SELECT tblGroups.*, 
      ROW_NUMBER() OVER (ORDER BY myCTE.RowNumber , tblGroups.Score desc) AS RowNumber, 
      RecurseLevel + 1 AS RecurseLevel
FROM tblGroups
JOIN (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Score desc) AS RowNumber, 1 AS RecurseLevel
    FROM tblGroups
    WHERE ParentId IS NULL
    )myCTE ON myCTE.GroupID = tblGroups.ParentID;

这个需要被分区以重置行号。

递归查询在while循环中不起作用，它们不是过程化的。在基础上，它们像递归函数一样工作，但根据表、查询和索引，它们可以被优化为以一种或另一种方式运行。

如果我们遵循视图在使用分析函数时不能合并的概念，并查看查询1。它只能以一种方式运行，并且它在嵌套循环中。

WITH myCTE
AS
( /*Cannot be merged*/
  SELECT *, ROW_NUMBER() OVER (ORDER BY Score desc) AS RowNumber, 1 AS RecurseLevel,
  cast(0 as bigint) n
  FROM tblGroups
  WHERE ParentId IS NULL

  UNION ALL

/*Cannot be merged*/
  SELECT tblGroups.*, 
      ROW_NUMBER() OVER (ORDER BY myCTE.RowNumber, tblGroups.Score desc) AS RowNumber,       RecurseLevel + 1 AS RecurseLevel,
  myCTE.RowNumber
  FROM tblGroups
      JOIN myCTE
          ON myCTE.GroupID = tblGroups.ParentID
 )
SELECT *
FROM myCTE;

首先，第一次选择不能合并第二个。运行此查询的唯一方法是在每个级别返回的每个项目中进行嵌套循环，因此需要重置。再次强调，这不是程序性问题，而只是可能执行计划的问题。

希望这回答了你的问题，如果没有，请告诉我：）

y