如何在分区数据中填写缺少的日期和值? [英] How to fill missing dates and values in partitioned data?
问题描述
如何在分区数据中填写缺少的日期和值?
How to fill missing dates and values in partitioned data?
我遇到了很多麻烦Googling这个大部分帖子似乎都是Oracle数据库,我正在使用Microsoft SQL Server。
I'm having a lot of trouble Googling this as most of the posts seem to feature Oracle databases, and I'm working with Microsoft SQL Server.
我有以下假设表:
name date val
-------------------------------
A 01/01/2014 1.5
A 01/03/2014 2
A 01/06/2014 5
B 01/02/2014 90
B 01/07/2014 10
我想填写差距之间的日期,并复制最近的日期值。此外,我想填写1)回到预设MINDATE(假设是12/29/2013)的日期,2)到当前日期(假设是01/09/2014) - 和2)默认值将为1。
I want to fill in the dates in between the gaps and copy over the value from the most recent following date. In addition, I would like to fill in dates that 1) go back to a pre-set MINDATE (let's say it's 12/29/2013) and 2) go up to the current date (let's say it's 01/09/2014) - and for 2) the default values will be 1.
因此,输出将是:
name date val
-------------------------------
A 12/29/2014 1.5
A 12/30/2014 1.5
A 12/31/2014 1.5
A 01/01/2014 1.5 <- original
A 01/02/2014 2
A 01/03/2014 2 <- original
A 01/04/2014 5
A 01/05/2014 5
A 01/06/2014 5 <- original
A 01/07/2014 1
A 01/08/2014 1
A 01/09/2014 1
B 12/29/2014 90
B 12/30/2014 90
B 12/31/2014 90
B 01/01/2014 90
B 01/02/2014 90 <- original
B 01/03/2014 10
B 01/04/2014 10
B 01/05/2014 10
B 01/06/2014 10
B 01/07/2014 10 <- original
B 01/08/2014 1
B 01/09/2014 1
推荐答案
首先,您需要生成日期。然后可以生成日期和名称的所有组合。最后填写这些值。这是一个使用交叉申请
的示例:
First, you need to generate the dates. Then you can generate all the combinations of date and name. Finally, fill in the values. Here is an example using cross apply
:
with dates as (
select @MINDATE as thedate
union all
select dateadd(day, 1, thedate)
from dates
where dateadd(day, 1, thedate) <= getdate()
)
select thedate, vals.val
from dates cross join
(select distinct name from hypothetical) h cross apply
(select top 1 val
from hypothetical h2
where h2.name = h.name and h2.date <= dates.thedate
order by date desc
) vals;
这篇关于如何在分区数据中填写缺少的日期和值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!