具有排名函数的递归 cte [英] recursive cte with ranking functions

问题描述

如何在递归 cte 中使用排名函数?这是一个简单的例子，展示了我正在尝试做的事情:

How to use ranking functions in recursive cte? Here's simple example showing how I'm trying to do:

with cte as (
  select 1 a, 1 b union all select 1, 2 union all select 2, 3 union all select 2, 4
)
, rcte (a, b, c, d) as (
  select a, b, cast(0 as int), 1 
  from cte
  union all
  select a, b, cast(ROW_NUMBER() over (partition by a order by b) as int), d+1
  from rcte
  where d < 2
)
select * 
from rcte
where d=2
order by a, b

为什么没有排名?请告诉我我的错误

Why there's no ranking? Show me my mistake pls

推荐答案

EDIT

当您阅读有关递归的 CTE 文档时，您会注意到它有一些限制，例如不能使用子查询、分组依据、顶部.这些都涉及多行.从有限的测试，检查执行计划，以及测试这个查询

EDIT

When you read the CTE documentation regarding recursion, you will notice that it has some limits, such as not being able to use subqueries, group-by, top. These all involve multiple rows. From limited testing, and checking the execution plan, as well as testing this query

with cte as (
  select 1 a, 1 b union all select 1, 2 union all select 1, 3 union all select 2, 4
)
, rcte (a, b, c, d) as (
  select a, b, cast(0 as int), 1 
  from cte
  union all
  select r.a, cte.b, cast(ROW_NUMBER() over (order by r.b) as int), r.d+1
  from rcte r inner join cte on cte.a=r.a
  where r.d < 2
)
select * 
from rcte
where d=2
order by a, b

我只能得出结论:

1. Row_Number() 在 CTE 中有效，当其他表被连接以产生多行结果集时
2. 从编号结果可以清楚地看出，CTE 在所有迭代中都是在一行中处理的，逐行而不是多行逐多行，即使它似乎是同时迭代所有行.这将解释为什么递归 CTE 不允许任何适用于多行操作的函数.

虽然我很容易得出这个结论，但显然有人花了更多的时间详细解释一下 仅 17 个月前...

Although I came to this conclusion easily, someone obviously took a lot more time to explain it in excruciating detail only 17 months ago...

换句话说，这是SQL Server 实现递归 CTE 的本质，因此窗口函数不会按照您期望的方式工作.

In other words, this is the nature of SQL Server's implementation of the recursive CTE, so windowing functions will not work the way you expect it to.

<小时>为了他人的利益，输出是:

---

For the benefit of others, the output is:

a           b           c           d
----------- ----------- ----------- -----------
1           1           1           2
1           2           1           2
2           3           1           2
2           4           1           2

而您希望 c 包含 1,2,1,2 而不是 1,1,1,1.这当然看起来像是一个错误，因为没有文档说窗口函数不应该在 CTE 的递归部分工作.

Whereas you are expecting c to contain 1,2,1,2 instead of 1,1,1,1. This certainly seems like it could be a bug, since there is no documentation to say that windowing functions should not work in the recursive part of a CTE.

注意:row_number() 返回 bigint，所以你可以只将锚点(c)转换为 bigint.

Note: row_number() returns bigint, so you can cast just the anchor(c) as bigint.

由于每次迭代都会增加 d，因此您可以在外部执行加窗.

Since each iteration increases d, you could perform the windowing outside.

with cte as (
  select 1 a, 1 b union all select 1, 2 union all select 2, 3 union all select 2, 4
)
, rcte (a, b, d) as (
  select a, b, 1 
  from cte
  union all
  select a, b, d+1
  from rcte
  where d < 2
)
select a,b, ROW_NUMBER() over (partition by a,d order by b) c,d
from rcte
--where d=2
order by d, a, b

---

在回答另一个问题^link，我用递归 CTE 玩了一些.如果在没有最终 ORDER BY 的情况下运行它，您可以看到 SQL Server 如何接近递归.有趣的是，在这种情况下它会向后倒退，然后对每一行进行完整的深度优先递归.

While answering another question^link, I played some more with recursive CTE. If you run it without the final ORDER BY, you can see how SQL Server is approaching the recursion. It is interesting that it goes backwards in this case, then does a full depth-first recursion on each row.

示例表

create table Testdata(SomeID int, OtherID int, Data varchar(max))
insert Testdata select 1, 9, '18,20,22,alpha,beta,gamma,delta'
insert Testdata select 2, 6, ''
insert Testdata select 3, 8, '11,12,.'
insert Testdata select 4, 7, '13,19,20,66,12,232,1232,12312,1312,abc,def'
insert Testdata select 5, 8, '17,19'

递归查询

;with tmp(SomeID, OtherID, DataItem, Data) as (
select SomeID, OtherID, LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from Testdata
union all
select SomeID, OtherID, LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where Data > ''
)
select SomeID, OtherID, DataItem, Data
from tmp
-- order by SomeID

输出显示在第一次迭代中处理的 CTE 锚点，然后无论出于何种原因在处理其他行之前，锚点集中的每一行都被递归完成(深度优先).

The output shows the CTE anchor processed in iteration one, then for whatever reason each row in the anchor set is recursed to completion (depth-first) before processing other rows.

但它确实有其奇怪的用途，如这个答案 显示

Yet it does have its strange uses, as this answer shows

这篇关于具有排名函数的递归 cte的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！