TSQL - SQL Server填充值与前一个填充日期的值
问题描述:
我想在我的事实表中达到棘手的事情。 我的客户希望用事实填表中的每个空值填充上一个填充日期的值。TSQL - SQL Server填充值与前一个填充日期的值
它变得复杂了,当我需要consinder,它应该只更新值,如果尺寸的情况下是一样的。
这是预期的结果:
我们正在使用微软的Analysis Services所以也许这也可以用MDX和LastNonEmpty做,但现在它会FINDE有更新工作事实表的声明。 (通过使用实际的事实表交叉连接所有可能的日组合来生成具有0值的事实表)。
谢谢你们!
答
你可以用apply
做到这一点:
select t.dim1, t.dim2, t.dimdate, t2.value
from t outer apply
(select top 1 t2.*
from t t2
where t2.dim1 = t.dim1 and t2.dim2 = t.dim2 and
t2.dimdate <= t.dimdate and t2.value <> 0
order by t2.dimdate desc
) t2;
SQL确实提供的功能做你想要使用的是什么窗口功能。 LAG()
上的IGNORE NULLS
选项。不幸的是,SQL Server没有实现这个选项。
你可以用窗口函数做到这一点,但它是有点更麻烦:
select t.dim1, t.dim2, t.dimdate,
max(value) over (partition by dim1, dim2, effectivedimdate) as value
from (select t.*,
(case when value = 0
then max(case when value <> 0 then dimdate end) over (partition by dim1, dim2 order by dimdate)
else dimdate
end) as effective_dimdate
from t
) t;
的max()
扫描获取最近dimdate
具有非零值。然后使用另一个max()
扫描将该值“传播”到所有值上。
注意:这假定您要应用的值总是大于零。该代码很容易修改来处理这个问题,但额外的case
表达式使逻辑复杂化。
其中之一可以很容易地变成更新。第二个特别容易:
with toupdate as (
select t.*,
max(value) over (partition by dim1, dim2, effectivedimdate) as new_value
from (select t.*,
(case when value = 0
then max(case when value <> 0 then dimdate end) over (partition by dim1, dim2 order by dimdate)
else dimdate
end) as effective_dimdate
from t
) t
)
update toupdate
set value = newvalue
where value = 0;