SQL查找的最小值大于上一行

Question

如何找到大于上一行的最小值？这段代码需要优化以运行超过百万行和业务键，我们最多可以有3-10个timevalue列。我认为它是某种类型的递归CTE。

背景:对许多正在被非正规化的表进行缓慢变化的尺寸处理。并试图找到合适的开始日期。

数据示例：

create table dbo.timetest
(
TimeTestIdentityId int primary key identity(1,1),
businesskey int,
timevalue1 int,
timevalue2 int,
timevalue3 int
)

insert into timetest
values
(5,131,134,137),
(5,131,138,135),
(5,131,140,135),
(5,143,141,145),
(5,149,141,148),
(5,150,141,148),

(6,134,137,140),
(6,134,141,138),
(6,134,143,138),
(6,146,144,148),
(6,152,144,151),
(6,153,144,151)

最终值：

等

行是由TimeValue1命令的。

从多列中选择MIN值

Answer 1

with  
cte1 (businesskey,timevalue1,timevalue2,timevalue3,rn) as (
select businesskey,timevalue1,timevalue2,timevalue3,
       row_number() over (partition by businesskey order by timevalue1,timevalue2,timevalue3) rn
from timetest
),
cte2 (businesskey,timevalue1,timevalue2,timevalue3,rn,timevalue0) as (
select businesskey,timevalue1,timevalue2,timevalue3,rn,timevalue1
from cte1
where rn=1
union all
select t2.businesskey,t2.timevalue1,t2.timevalue2,t2.timevalue3,t2.rn,
       case when t2.timevalue1>t1.timevalue0 then t2.timevalue1 else 2147483647 end
from cte2 t1
inner join cte1 t2 on t1.businesskey=t2.businesskey
          and t1.rn+1=t2.rn
union all
select t2.businesskey,t2.timevalue1,t2.timevalue2,t2.timevalue3,t2.rn,
       case when t2.timevalue2>t1.timevalue0 then t2.timevalue2 else 2147483647 end
from cte2 t1
inner join cte1 t2 on t1.businesskey=t2.businesskey
          and t1.rn+1=t2.rn
union all
select t2.businesskey,t2.timevalue1,t2.timevalue2,t2.timevalue3,t2.rn,
       case when t2.timevalue3>t1.timevalue0 then t2.timevalue3 else 2147483647 end
from cte2 t1
inner join cte1 t2 on t1.businesskey=t2.businesskey
          and t1.rn+1=t2.rn
)
select businesskey,timevalue1,timevalue2,timevalue3,rn,min(timevalue0) timevalue0 
from cte2 
group by businesskey,timevalue1,timevalue2,timevalue3,rn
order by 1,5;

小提琴

输出：

businesskey timevalue1  timevalue2  timevalue3  rn  timevalue0
5           131         134         137         1   131
5           131         138         135         2   135
5           131         140         135         3   140
5           143         141         145         4   141
5           149         141         148         5   148
5           150         141         148         6   150
6           134         137         140         1   134
6           134         141         138         2   138
6           134         143         138         3   143
6           146         144         148         4   144
6           152         144         151         5   151
6           153         144         151         6   153