我正在编写一些代码,以在7天的滚动窗口中寻找最小值。
首先,我要找到每天的最小值并将其插入到临时表中#minimumvalue这是要考虑到存在多个值的天数。
我正在通过使用找到rolling_min值min(Value) over(partition by ID order by Date_Value rows between 6 preceding and current row
,这就是我得到的
ID | Date_Value | Value | rolling_min
1234 1-1-2018 12:15 pm 2.95 1.54
1234 1-1-2018 3:30 pm 1.54 1.54
1234 1-2-2018 10:45 am 1.57 1.54
1234 1-3-2018 2:15 pm 2.02 1.54
1234 1-4-2018 4:00 pm 2.80 1.54
1234 1-5-2018 NULL 1.54
1234 1-6-2018 NULL 1.54
1234 1-7-2018 NULL 1.56
1234 1-8-2018 NULL 1.57
前两行发生在同一天,因此rolling_min以1.54开始。实际上,它应该从2.95开始,因为这是当前的最小值。
从那里,我想找到最小增加了1.85的第一个实例。我尝试了一个案例陈述CASE WHEN Value >= 1.85 * MIN(Value) OVER(PARTITION BY ID ORDER BY Date_Value ROWS BETWEEN 6 PRECEDING and CURRENT ROW THEN 1 ELSE 0 END AS Increase YN
这是结果
ID | Date_Value | Value | rolling_min | Increase YN
1234 1-1-2018 12:15 pm 2.95 1.54 1
1234 1-1-2018 3:30 pm 1.54 1.54 0
1234 1-2-2018 10:45 am 1.57 1.54 0
1234 1-3-2018 2:15 pm 2.02 1.54 0
1234 1-4-2018 4:00 pm 2.80 1.54 1
1234 1-5-2018 NULL 1.54 0
1234 1-6-2018 NULL 1.54 0
1234 1-7-2018 NULL 1.57 0
1234 1-8-2018 NULL 1.57 0
第一行显示1,因为它是1.85 * 1.54的最小值,但之后是1.54,所以这并不是我真正想要的。我怎样才能避免这种情况,而只在增加的地方返回1?
在此先感谢您的帮助:)
我认为您正在尝试首次发现任何数据值比最近6天的最小值都高出85%。我希望那是对的。
这是我的代码:
create table data
(ID int not null,
Date_Value datetime not null,
Value decimal(5,2)
)
go
truncate table data
go
insert into data
select 1234, '2018-01-01 12:15:00', 2.95
union all select 1234, '2018-01-01 12:15:00', 1.54
union all select 1234, '2018-01-02 10:45:00', 1.57
union all select 1234, '2018-01-03 14:15:00', 2.02
union all select 1234, '2018-01-04 16:00:00', 2.80
union all select 1234, '2018-01-05', null
union all select 1234, '2018-01-06', null
union all select 1234, '2018-01-07', null
union all select 1234, '2018-01-08', null
union all select 1234, '2018-01-09 09:00:00', 3.4
union all select 1234, '2018-01-09 10:00:00', 5.4
union all select 1234, '2018-01-09 11:00:00', 8.4
go
insert into data
select 9999, '2018-01-01 12:15:00', 2.95
union all select 9999, '2018-01-01 12:15:00', 1.54
union all select 9999, '2018-01-02 10:45:00', 1.57
union all select 9999, '2018-01-03 14:15:00', 2.02
union all select 9999, '2018-01-04 16:00:00', 2.80
union all select 9999, '2018-01-05', null
union all select 9999, '2018-01-06', null
union all select 9999, '2018-01-07', null
union all select 9999, '2018-01-08', null
union all select 9999, '2018-01-09 09:00:00', 3.4
union all select 9999, '2018-01-09 10:00:00', 5.4
union all select 9999, '2018-01-09 11:00:00', 8.4
go
;with ByDayMin as
(
select ID, Date_Value = cast(Date_Value as date), Value=min(Value)
from data
group by ID, cast(Date_Value as date)
),
IncBig as (
select ID, Date_Value,
Value,
sixDayMin = MIN(Value) OVER(PARTITION BY ID ORDER BY Date_Value ROWS BETWEEN 6 PRECEDING and CURRENT ROW)
from ByDayMin
),Calcs as
(
select d.*,
i.SixDayMin,
ValueIncreaseRatioLast6Days = d.Value / SixDayMin,
Prior = LAG(d.Date_Value, 1,null) OVER(PARTITION BY d.ID ORDER BY d.Date_Value)
from data d
join ByDayMin b
on b.ID = d.ID
and b.Date_Value = cast(d.Date_Value as date)
join IncBig i
on i.ID = d.ID
and i.Date_Value = b.Date_Value
)
select c.ID, c.Date_Value, c.Value, c.SixDayMin, ValueIncreaseRatioLast6Days,
Increase_YN=case when c.ValueIncreaseRatioLast6Days >= 1.85
and not exists (select 1 from Calcs c2
where c2.ID = c.ID
and c2.ValueIncreaseRatioLast6Days >= 1.85
and c2.Date_Value < c.Date_Value
and Prior is not Null
)
and Prior is not Null
then 1
else 0
end
from Calcs c
order by c.ID, c.Date_Value
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句