我的处境是每台机器都有两次计数, p
以及 r
. p
应始终大于或等于 r
但由于技术滞后和聚合周期短,情况并非总是如此,因为 r
经常计数-但不总是-显示前一时期的数据。因为滞后的长度不是恒定的,所以无法确切地知道是哪个周期 r
值属于。因此,我不能简单地移动所有的 r
在时间上统一向后计数,因为这可能会在以前没有的地方产生其他差异。
这种情况无法改变,我必须按原样处理数据。
在下面的示例中,您可以看到 p
在机器上计算短暂的“暂停” 1
在机器上速度明显减慢 2
,但是 r
count继续返回大于的值 p
在“暂停”之前的短暂时间:
-- Dummy data
declare @t table(d date,m int,p int,r int);
insert into @t values(getdate()-9,1,100,10),(getdate()-8,1,90 ,10),(getdate()-7,1,70 ,10),(getdate()-6,1,70 ,10),(getdate()-5,1,80 ,10),(getdate()-4,1,50 ,10),(getdate()-3,1,10 ,10),(getdate()-2,1,0 ,10),(getdate()-1,1,0 ,10),(getdate()+0,1,0 ,10),(getdate()+1,1,0 ,0),(getdate()+2,1,0 ,0),(getdate()+3,1,40 ,0),(getdate()+4,1,50 ,0),(getdate()+5,1,80 ,10),(getdate()-9,2,1100,100),(getdate()-8,2,190 ,100),(getdate()-7,2,170 ,100),(getdate()-6,2,170 ,100),(getdate()-5,2,180 ,100),(getdate()-4,2,150 ,100),(getdate()-3,2,110 ,100),(getdate()-2,2,10 ,100),(getdate()-1,2,10 ,100),(getdate()+0,2,10 ,100),(getdate()+1,2,10 ,0),(getdate()+2,2,10 ,0),(getdate()+3,2,140 ,0),(getdate()+4,2,150 ,0),(getdate()+5,2,180 ,100);
select * from @t order by m,d;
-- Output
+------------+---+------+-----+
| d | m | p | r |
+------------+---+------+-----+
| 2020-05-27 | 1 | 100 | 10 |
| 2020-05-28 | 1 | 90 | 10 |
| 2020-05-29 | 1 | 70 | 10 |
| 2020-05-30 | 1 | 70 | 10 |
| 2020-05-31 | 1 | 80 | 10 |
| 2020-06-01 | 1 | 50 | 10 |
| 2020-06-02 | 1 | 10 | 10 |
| 2020-06-03 | 1 | 0 | 10 |
| 2020-06-04 | 1 | 0 | 10 |
| 2020-06-05 | 1 | 0 | 10 |
| 2020-06-06 | 1 | 0 | 0 |
| 2020-06-07 | 1 | 0 | 0 |
| 2020-06-08 | 1 | 40 | 0 |
| 2020-06-09 | 1 | 50 | 0 |
| 2020-06-10 | 1 | 80 | 10 |
| 2020-05-27 | 2 | 1100 | 100 |
| 2020-05-28 | 2 | 190 | 100 |
| 2020-05-29 | 2 | 170 | 100 |
| 2020-05-30 | 2 | 170 | 100 |
| 2020-05-31 | 2 | 180 | 100 |
| 2020-06-01 | 2 | 150 | 100 |
| 2020-06-02 | 2 | 110 | 100 |
| 2020-06-03 | 2 | 10 | 100 |
| 2020-06-04 | 2 | 10 | 100 |
| 2020-06-05 | 2 | 10 | 100 |
| 2020-06-06 | 2 | 10 | 0 |
| 2020-06-07 | 2 | 10 | 0 |
| 2020-06-08 | 2 | 140 | 0 |
| 2020-06-09 | 2 | 150 | 0 |
| 2020-06-10 | 2 | 180 | 100 |
+------------+---+------+-----+
我需要能够明智地调整这些 r
在某种程度上倒计时,这样就可以将它们添加到前面的行中,使每一行保持一致 p
大于或等于相应 r
价值观。
在上面的例子中 m = 1
,输出可能类似于以下任何一种 r
计数;我不在乎调整的蔓延,只在乎这个 p
>= r
对于每一行和每一次调整,只能在时间上向后移动:
+------------+---+------+------+------+------+
| d | m | p | r1 | r2 | r3 |
+------------+---+------+------+------+------+
| 2020-05-27 | 1 | 100 | 10 | 10 | 10 |
| 2020-05-28 | 1 | 90 | 10 | 10 | 10 |
| 2020-05-29 | 1 | 70 | 10 | 15 | 10 |
| 2020-05-30 | 1 | 70 | 20 | 20 | 10 |) Note how the original 30 r counts
| 2020-05-31 | 1 | 80 | 20 | 20 | 10 |} that didn't follow the rule
| 2020-06-01 | 1 | 50 | 20 | 15 | 40 |) have been moved back in time
| 2020-06-02 | 1 | 10 | 10 | 10 | 10 |
| 2020-06-03 | 1 | 0 | 0 | 0 | 0 |
| 2020-06-04 | 1 | 0 | 0 | 0 | 0 |
| 2020-06-05 | 1 | 0 | 0 | 0 | 0 |
| 2020-06-06 | 1 | 0 | 0 | 0 | 0 |
| 2020-06-07 | 1 | 0 | 0 | 0 | 0 |
| 2020-06-08 | 1 | 40 | 0 | 0 | 0 |
| 2020-06-09 | 1 | 50 | 0 | 0 | 0 |
| 2020-06-10 | 1 | 80 | 10 | 10 | 10 |
+------------+---+------+------+------+------+
我试过用窗口函数和 rows between
但我不知道如何识别 r
需要重新分配给以前期间的值,以及确定 p
分配给的值。如果我有任何进展,我会加在下面,但所有的帮助是非常感谢。
尝试1
我所管理的最接近的是下面的代码,它对上面的代码有效,但是当您更改 p = 50
价值低于 40
当我只想向后时,也会在时间上前后调整:
with t as(
select row_number() over (partition by m order by d) as rn
,(row_number() over (partition by m order by d)-1) / 5 as gn
,*
from @t
where m = 1
)
select *
,case when p > r
then r + (sum(case when p < r then r else 0 end) over (partition by gn) / sum(case when p > r then 1 else 0 end) over (partition by gn))
else case when p = r
then r
else 0
end
end as r_adj
from t;
尝试2
这更近了,但仍在时间上向前和向后调整:
with t as(
select row_number() over (partition by m order by d) as rn
,(row_number() over (partition by m order by d)-1) / 10 as gn
,(row_number() over (partition by m order by d)+4) / 10 as gn2
,*
from @t
where m = 1
)
,r1 as(
select *
,case when p > r
then r + (sum(case when p < r then r - p else 0 end) over (partition by gn) / sum(case when p > r then 1. else 0. end) over (partition by gn))
else case when p = r
then r
else 0
end
end as r_adj
from t
)
select d
,m
,p
,r
,case when p > r_adj
then r_adj + (sum(case when p < r_adj then r_adj - p else 0 end) over (partition by gn2) / sum(case when p > r_adj then 1. else 0. end) over (partition by gn2))
else case when p = r_adj
then r_adj
else r_adj - (r_adj - p)
end
end as r_new
from r1
order by rn
;
3条答案
按热度按时间zzoitvuj1#
一种方法使用
apply
:这是一把小提琴。
db2dz4w82#
你也许可以把它整理一下,但希望详细的内容能帮助你理解正在发生的事情。
输出:
uqcuzwp83#
通过使用下面的脚本,我们成功地使它工作在一个可以接受的非常小的误差范围内。
它的工作原理是检查前面的行,看是否有多余的
r
如果是,则将其添加到当前行。然后对这个的输出运行相同的过程,有效地将任何多余的内容向后移动两行。这涵盖了现实世界数据中的大多数情况。最后,它会检查是否有多余的内容,并找到最近一行的时间戳
p
超过某个阈值的值,将多余的值移到。如果没有带的行p > r
事先(例如,接收到的第一个数据周期会将此问题排除在外),它会在将来获取最近的一个数据周期。