T-SQL分析函数(或类似函数)在递归CTE中的应用。

huangapple go评论67阅读模式
英文:

T-SQL analytic functions (or analogue) in recursive CTE

问题

I have a recursive CTE. I use count (s_) over (partition by c) cc in column cc in the recursive part of that CTE. But I get "1" as the result, but I need to get such a result in the recursive CTE (see Expected result - column cc). Could you give me advice on how to get such a result in the recursive part?

Because I need to use such a result in this recursive CTE next.

I try to explain this task. I need to calculate for (column n) in each iteration:

  1. Calculate count (s_) over (partition by c) as cc
  2. Calculate the minimum from cc (minimum of count (s_) over (partition by c))
  3. Exclude values from column S with the minimum from cc (minimum of count (s_) over (partition by c)) before the next iteration and re-calculate items 1), 2), 3).
create table cor (nb int, s varchar(10), c varchar(10))

insert into cor values (1, 's1', 'c1')
insert into cor values (1, 's2', 'c1')
insert into cor values (1, 's3', 'c2')
insert into cor values (1, 's4', 'c3')
insert into cor values (1, 's5', 'c3')
insert into cor values (1, 's6', 'c4')

create table sr (num int, s varchar(10))

insert into sr values (1, 's1')
insert into sr values (1, 's2')
insert into sr values (1, 's3')
insert into sr values (2, 's1')
insert into sr values (2, 's2')
insert into sr values (2, 's6')
insert into sr values (3, 's1')
insert into sr values (3, 's3')
insert into sr values (3, 's4')
insert into sr values (4, 's1')
insert into sr values (4, 's3')
insert into sr values (4, 's4')
insert into sr values (4, 's6')
with rec as (
    select 1 n, nb, s, c, cast(0 as int) num, cast('' as varchar(50)) s_, 0 nn, 0 cc from cor
    union all
    select r.*, count (s_) over (partition by c) cc
    from (
        select t.*, cast(row_number() over (partition by n, s, c order by t.s_ desc) as int) nn
        from (
            select 1 + n n, cast(sr.num as int) num_, rec.s, rec.c, cast(sr.num as int) num,
            iif(cast(sr.s as varchar(50)) <> rec.s, '1', cast(sr.s as varchar(50))) s_
            from rec, sr where sr.num = rec.n + 1 and sr.num < 5
        ) t
    ) r where nn = 1
)
select * from rec order by n, s

I have such a result

n nb s c num s_ nn cc
2 2 s1 c1 2 s1 1 1
2 2 s2 c1 2 s2 1 1
2 2 s3 c2 2 1 1 1
2 2 s4 c3 2 1 1 1
2 2 s5 c3 2 1 1 1

Expected result

n nb s c num s_ nn cc
2 2 s1 c1 2 s1 1 2
2 2 s2 c1 2 s2 1 2
2 2 s3 c2 2 1 1 1
2 2 s4 c3 2 1 1 2
2 2 s5 c3 2 1 1 2
英文:

I have a recursive CTE. I use count (s_) over (partition by c) cc in column cc in recursive part of that CTE. But I get "1" as result,
but I need to get such result in recursive cte (see Expected result - column cc). Could you give me advise how to get such result in recursive part?

Because I need to use such result in this recursive CTE next.

I try to explain this task. I need to calculate for (column n) in each iteration: 1) calculate count (s_) over (partition by c) as cc 2) calculate min from cc (min of count (s_) over (partition by c)) 3) exclude values from column S with min from cc (min of count (s_) over (partition by c)) before next each iteration and re-calculate items 1) 2) 3)

create table cor (nb int, s varchar(10), c varchar(10))

insert into cor values ( 1 , &#39;s1&#39; , &#39;c1&#39;  )
insert into cor values ( 1, &#39;s2&#39; , &#39;c1&#39;)
insert into cor values ( 1, &#39;s3&#39; , &#39;c2&#39; ) 
insert into cor values ( 1, &#39;s4&#39; , &#39;c3&#39; )
insert into cor values ( 1, &#39;s5&#39; , &#39;c3&#39; )
insert into cor values ( 1, &#39;s6&#39; , &#39;c4&#39;)

create table sr (num int, s varchar(10))


insert into sr values ( 1  , &#39;s1&#39; )
insert into sr values ( 1 , &#39;s2&#39; )
insert into sr values ( 1 , &#39;s3&#39; )
insert into sr values ( 2  , &#39;s1&#39; )
insert into sr values ( 2 , &#39;s2&#39; )
insert into sr values ( 2  , &#39;s6&#39; )
insert into sr values ( 3 , &#39;s1&#39; )
insert into sr values ( 3 , &#39;s3&#39; )
insert into sr values ( 3 , &#39;s4&#39; )
insert into sr values ( 4 , &#39;s1&#39; )
insert into sr values ( 4  , &#39;s3&#39; )
insert into sr values ( 4  , &#39;s4&#39; )
insert into sr values ( 4 , &#39;s6&#39;)
with  rec as (


select  1 n, nb, s , c , cast(0 as int) num, cast(&#39;&#39; as varchar(50)) s_  , 0 nn, 0 cc  from cor 

union all
select r.*, count (s_) over (partition by c) cc
from 
(
	select t.*, cast(row_number()over(partition by n,s, c order by t.s_ desc ) as int)nn
	from(
		select   1+n n,   cast(sr.num as int) num_, rec.s, rec.c, cast(sr.num as int) num,iif(cast(sr.s as varchar(50))&lt;&gt;rec.s,&#39;1&#39;,cast(sr.s as varchar(50)))  s_
				
					from rec, sr where sr.num=rec.n+1  and sr.num&lt;5
		)t
)r where nn=1
)

select * from rec 
order by n,s

I have such result

n s c num s_ nn cc
2 2 s1 c1 2 s1 1 1
2 2 s2 c1 2 s2 1 1
2 2 s3 c2 2 1 1 1
2 2 s4 c3 2 1 1 1
2 2 s5 c3 2 1 1 1

Expected result

n s c num s_ nn cc
2 2 s1 c1 2 s1 1 2
2 2 s2 c1 2 s2 1 2
2 2 s3 c2 2 1 1 1
2 2 s4 c3 2 1 1 2
2 2 s5 c3 2 1 1 2

答案1

得分: 2

count (s_) over (partition by c) cc 在递归中不会看到整个输出,无法正确计数。但是你可以在最终的选择中使用它。

或者你可以添加一个不带递归但带有分析函数的公共表达式(CTE)。

结果是相同的。

英文:

count (s_) over (partition by c) cc in recursion doesn't see whole output and can't count properly. But you can use it in the final select.

with  rec as (
select  1 n, nb, s, c, cast(0 as int) num, cast(&#39;&#39; as varchar(50)) s_  , 0 nn from cor 
union all
select r.*
from 
(
    select t.*, cast(row_number()over(partition by n,s, c order by t.s_ desc ) as int)nn
    from(
        select   1+n n,   cast(sr.num as int) num_, rec.s, rec.c, cast(sr.num as int) num,iif(cast(sr.s as varchar(50))&lt;&gt;rec.s,&#39;1&#39;,cast(sr.s as varchar(50)))  s_
                
                    from rec, sr where sr.num=rec.n+1  and sr.num&lt;5
        )t
)r where nn=1
)

select rec.*, count (c) over (partition by n,c) cc from rec 
order by n,s

T-SQL分析函数(或类似函数)在递归CTE中的应用。

Or you can add one more CTE without recursion but with analytic function.

with  rec as (
select  1 n, nb, s, c, cast(0 as int) num, cast(&#39;&#39; as varchar(50)) s_  , 0 nn from cor 
union all
select r.*
from 
(
    select t.*, cast(row_number()over(partition by n,s, c order by t.s_ desc ) as int)nn
    from(
        select   1+n n,   cast(sr.num as int) num_, rec.s, rec.c, cast(sr.num as int) num,iif(cast(sr.s as varchar(50))&lt;&gt;rec.s,&#39;1&#39;,cast(sr.s as varchar(50)))  s_
                
                    from rec, sr where sr.num=rec.n+1  and sr.num&lt;5
        )t
)r where nn=1
), res as(
select rec.*, count (c) over (partition by n,c) cc from rec 
)

select * from res
order by n,s

The result is the same.

huangapple
  • 本文由 发表于 2023年6月8日 14:33:43
  • 转载请务必保留本文链接:https://go.coder-hub.com/76429167.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定