如何在SQL中使用动态函数来获得snake模式？
如何在SQL中使用动态函数来获得snake模式？
提问于 2019-06-26 02:50:54
我正在尝试在数据集上做出时态决策。让我用一个简单的表格来解释这个问题：
Time Var1 Var2 Var3 Var4 Var5 Var6 Total
0:00 1.69 3.27 4.80 2.14 0.70 2.14
0:05 2.73 2.73 1.60 1.20 0.46 2.14
0:10 5.45 2.69 4.62 1.15 1.03 4.29 16.28
0:15 2.07 4.74 2.14 1.50 0.43 2.37
0:20 1.71 4.62 1.79 1.29 0.73 2.37
0:25 1.88 3.60 4.00 2.09 0.56 2.25
0:30 5.22 8.57 1.54 2.20 0.48 1.13 14.13
0:35 5.00 5.63 2.93 1.32 1.03 2.05
0:40 4.29 5.29 2.55 1.14 0.38 1.48对于第一个合计中显示的结果，路径将是下一个：
Time Var1 Var2 Var3 Var4 Var5 Var6 Total
0:00 1.69 3.27 4.80
0:05 1.20
0:10 1.03 4.29 16.28我试图找到每个时间段的路径和总数。
我的表格是垂直制作的，而不是像示例中所示的水平制作。到目前为止，我已经使用以下命令获得了所有变量的cumulative_addition：
sum(value) over(partition by variable, time order by variable) as cumulative_addition但我不知道这是不是我应该迈出的第一步。我还考虑过在查询中组合超前和滞后函数，但在尝试增加时间段时遇到了问题：
Time Variable Value Cumulative_addition
0:00 Var1 1.69 1.69
0:00 Var2 3.27 4.96
0:00 Var3 4.80 9.76
0:00 Var4 2.14 11.90
0:00 Var5 0.70 12.60
0:00 Var6 2.14 14.74
0:05 Var1 2.73 17.47
0:05 Var2 2.73 20.20
0:05 Var3 1.60 21.80
0:05 Var4 1.20 23.00所以在9.76中，我应该做9.76-5=4.76，跳到0:05，在var4中不断加法，直到我再次达到5。
你认为我能用窗口函数解决这个问题吗？
预先感谢您的帮助
回答 3
Stack Overflow用户
回答已采纳
发布于 2019-06-26 04:40:31
这个答案为变量提供了一个循环，并将它们加载到表中：
create table #t
(
[Time] time
,[Variable] varchar(10)
, [Value] numeric(5,2)
--, [Cumulative_addition] as numeric(5,2)
)
insert into #t
values
('0:00' ,'Var1' ,1.69) --1.69)
,('0:00' ,'Var2' ,3.27) -- 4.96
,('0:00' ,'Var3' ,4.80 ) --9.76
,('0:00' ,'Var4' ,2.14 ) --11.90
,('0:00' ,'Var5' ,0.70 ) --12.60
,('0:00' ,'Var6' ,2.14 )--14.74
,('0:05' ,'Var1' ,2.73) --17.47
,('0:05' ,'Var2' ,2.73 ) --20.20
,('0:05' ,'Var3' ,1.60 ) --21.80
,('0:05' ,'Var4' ,1.20 ) --23.00
declare @v as numeric(7,4)
declare @total numeric(7,4) = 0
declare @calc numeric(7,4) = 0
declare @time time ='0:00'
declare @i int = 1
create table #answers (variable int, [Time] time, Value numeric(7,4))
while(@i<=6)
begin
select @v=[Value]
from #t where time = @time and [Variable] = 'Var' + cast(@i as varchar(1))
set @calc=@calc+@v
set @total = @total+@v
insert into #answers
values(@i,@time,@v)
if @calc>=5
Begin
set @time = dateadd(mi,5,@time)
set @calc = @calc-5
End
set @i=@i+1
set @v=null
end
select *
from #answers
drop table #t,#answers结果：
variable Time Value
1 00:00:00.0000000 1.6900
2 00:00:00.0000000 3.2700
3 00:00:00.0000000 4.8000
4 00:05:00.0000000 1.2000
5 00:10:00.0000000 NULL
6 00:10:00.0000000 NULLStack Overflow用户
发布于 2019-06-26 04:07:14
我想出了一个解决方案，但它敲击钉子，并没有那么优雅。
基本上，你一次加载一个变量，并检查总数是否超过5，如果超过5，则增加5分钟的时间，然后从计算中减去5。
create table #t
(
[Time] time
,[Variable] varchar(10)
, [Value] numeric(5,2)
--, [Cumulative_addition] as numeric(5,2)
)
insert into #t
values
('0:00' ,'Var1' ,1.69) --1.69)
,('0:00' ,'Var2' ,3.27) -- 4.96
,('0:00' ,'Var3' ,4.80 ) --9.76
,('0:00' ,'Var4' ,2.14 ) --11.90
,('0:00' ,'Var5' ,0.70 ) --12.60
,('0:00' ,'Var6' ,2.14 )--14.74
,('0:05' ,'Var1' ,2.73) --17.47
,('0:05' ,'Var2' ,2.73 ) --20.20
,('0:05' ,'Var3' ,1.60 ) --21.80
,('0:05' ,'Var4' ,1.20 ) --23.00
declare @var1 numeric(7,4)
declare @var2 numeric(7,4)
declare @var3 numeric(7,4)
declare @var4 numeric(7,4)
declare @var5 numeric(7,4)
declare @var6 numeric(7,4)
declare @total numeric(7,4) = 0
declare @calc numeric(7,4) = 0
declare @time time ='0:00'
select @var1 = [Value] from #t where time = @time and [Variable] = 'Var1'
set @calc=@calc+@var1
set @total = @total+@var1
if @calc>=5
Begin
set @time = dateadd(mi,5,@time)
set @calc = @calc-5
End
select @var2 = [Value] from #t where time = @time and [Variable] = 'Var2'
set @calc=@calc+@var2
set @total = @total+@var2
select 2,@calc
if @calc>=5.00
Begin
set @time = dateadd(mi,5,@time)
set @calc = @calc-5
End
select @var3 = [Value] from #t where time = @time and [Variable] = 'Var3'
set @calc=@calc+@var3
set @total = @total+@var3
if @calc>=5
Begin
set @time = dateadd(mi,5,@time)
set @calc = @calc-5
End
select @var4 = [Value] from #t where time = @time and [Variable] = 'Var4'
set @calc=@calc+@var4
set @total = @total+@var4
if @calc>=5
Begin
set @time = dateadd(mi,5,@time)
set @calc = @calc-5
End
select @var5 = [Value] from #t where time = @time and [Variable] = 'Var5'
set @calc=@calc+@var5
set @total = @total+@var5
if @calc>=5
Begin
set @time = dateadd(mi,5,@time)
set @calc = @calc-5
End
select @var6 = [Value] from #t where time = @time and [Variable] = 'Var6'
set @calc=@calc+@var6
set @total = @total+@var6
select var1=@var1,var2=@var2,var3=@var3,var4=@var4,var5=@var5,var6=@var6,total=@total
select * from #t
drop table #t结果(由于数据有限)：
var1 var2 var3 var4 var5 var6 total
1.6900 3.2700 4.8000 1.2000 NULL NULL NULLStack Overflow用户
发布于 2019-06-26 21:08:12
这是一个基于集合的蛇形和脚本，它可以比基于循环的脚本运行得更快，因为蛇可以并行移动。玩得开心。
-- Mock table to visualize groups and subgroups
create table #t(
[Time] time
,Var1 decimal(5,2)
,Var2 decimal(5,2)
,Var3 decimal(5,2)
,Var4 decimal(5,2)
,Var5 decimal(5,2)
,Var6 decimal(5,2)
)
insert #t([Time], Var1, Var2, Var3, Var4, Var5, Var6)
values
-- group 1
('0:00', 1.69, 3.27, 4.80, 2.14, 0.70, 2.14)
,('0:05', 2.73, 2.73, 1.60, 1.20, 0.46, 2.14)
,('0:10', 5.45, 2.69, 4.62, 1.15, 1.03, 4.29)
-- group 2
,('0:15', 2.07, 4.74, 2.14, 1.50, 0.43, 2.37)
,('0:20', 1.71, 4.62, 1.79, 1.29, 0.73, 2.37)
,('0:25', 1.88, 3.60, 4.00, 2.09, 0.56, 2.25)
-- group 3
,('0:30', 5.22, 8.57, 1.54, 2.20, 0.48, 1.13)
,('0:35', 5.00, 5.63, 2.93, 1.32, 1.03, 2.05)
,('0:40', 4.29, 5.29, 5.55, 1.14, 0.38, 1.48); -- this snake will hit the bottom.
-- Task parameters
declare @sumLimit decimal(5,2) = 5.0;
declare @grpStep int = 15; -- minutes
declare @subgrpStep int = 5; -- minutes
declare @nvars int = 6;
-- This is how the real table looks like
with realTable as(
select [Time], n, val
from #t
cross apply( values (1, Var1), (2, Var2), (3, Var3), (4, Var4), (5, Var5), (6, Var6)) a (n, val )
)
-- How data are grouped, 3 levels tgrp + tsubgrp + n
, grp as(
select [Time], datediff(MINUTE, '00:00', [Time]) / @grpStep tgrp
, datediff(MINUTE, '00:00', [Time]) % @grpStep tsubgrp
, n, val
from realTable
)
-- Snakes are moving
, snake as (
select [Time], tgrp, tsubgrp, n, val
, s = val % @sumLimit
-- should the snake move down?
, step = case when val > @sumLimit then @subgrpStep else 0 end
from grp
where tsubgrp = 0 and n = 1
union all
select grp.[Time], snake.tgrp, grp.tsubgrp, grp.n, grp.val
, s = cast((s + grp.val) % @sumLimit as decimal(5,2))
, step = case when s + grp.val > @sumLimit then @subgrpStep else 0 end
from grp
join snake on snake.tgrp = grp.tgrp
and grp.n = snake.n + 1 -- always move right
and grp.tsubgrp = snake.tsubgrp + snake.step -- and down when needed
where grp.n <= @nvars
and case when s > @sumLimit then snake.tsubgrp + @subgrpStep else snake.tsubgrp end <= @grpStep
)
-- select * from snake order by tgrp, tsubgrp, n; /*
select min([Time]) gstart, max([Time]) gend, sum(val) [sum]
from snake
group by tgrp
order by tgrp;
-- */页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56760349
复制相关文章
相似问题