如何在分区数据中填写缺少的日期和值？ [英] 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屋！