在 CTE 中插入"missing records"，并将日期作为键

Question

我有一个具有以下结构的CTE

ITEM,   DATE,       QUANTITY,   EXTRA
AAAA    01-07-2015  100         20
AAAA    04-07-2015  100         13
AAAA    07-07-2015  100         16
AAAA    09-07-2015  100         23
.
.
.
AAAA    31-07-2015  100         30

基本上，我想做的是让记录中有"缺失的日期"和EXTRA字段，其中包含来自前一个记录的EXTRA(如下所示)

ITEM,   DATE,       QUANTITY,   EXTRA
AAAA    01-07-2015  100         20
AAAA    02-07-2015  0           20
AAAA    03-07-2015  0           20
AAAA    04-07-2015  100         13
AAAA    05-07-2015  0           13
AAAA    06-07-2015  0           13
AAAA    07-07-2015  100         16
AAAA    08-07-2015  0           16
AAAA    09-07-2015  100         23
.
.
.
AAAA    31-07-2015  100         30

我可以使用LAG和包含有效日期的完整列表+ MERGE的临时表的混合手动插入记录。但是，此CTE仅用于一次性检查，然后将其丢弃。有没有更好的办法?

Answer 1

试试这个;

;with tbl as (
    --your data....
),
max_min_val as (
  select max(date) as max_date, min(date) as min_date from tbl
),
all_date AS (
    SELECT min_date as DateColumn 
    from max_min_val
    UNION ALL
    SELECT DATEADD(day,1,DateColumn)
    FROM all_date, max_min_val
    WHERE DATEADD(day,1,DateColumn) <= max_date
)
select 
    coalesce(t.item, (select top 1 tt.item from tbl tt where tt.date < x.DateColumn order by tt.date desc)) as item,
    x.DateColumn as date, 
    coalesce(t.QUANTITY, 0) as QUANTITY,
    coalesce(t.EXTRA, (select top 1 tt.EXTRA from tbl tt where tt.date < x.DateColumn order by tt.date desc)) as EXTRA
from all_date x
left join
tbl t on t.date = x.DateColumn

1. 这里all_date将拥有min and max date in tbl之间的所有date。
2. 左连接all_date将给出所有日期
3. coalesce(t.QUANTITY, 0) as QUANTITY将给出t.QUANTITY如果not null否则0
4. EXTRA和item将根据date给出CTE tbl的前一行数据。

Answer 2

考虑以下内容:

我做了一个CTE代表你的数据(不考虑ITEM，因为它在示例中不相关)。

使用以下两个CTE s，我正在使用recursive query搜索可用data的最小值和最大值之间的缺失日期。

接下来，您需要将正确的data加入到可用的dates中。关键是要从data中获得date最大值的行，只要data.date小于dates.date。

最后，基于这个例子，我的解释是你想继承extra，而不是quantity。因此case -语句在select .

with data as (
    select cast('2015-07-01' as date) date, 100 quantity, 20 extra union all
    select cast('2015-07-04' as date) date, 100 quantity, 13 extra union all
    select cast('2015-07-07' as date) date, 100 quantity, 16 extra union all
    select cast('2015-07-09' as date) date, 100 quantity, 23 extra
)
, maxDate as (
    select MAX(date) date
    from data
)
, dates as (
    select date
    from data
    union all
    select DATEADD(day,1,date) date
    from dates
    where DATEADD(day,1,date) not in (select date from data)
        and DATEADD(day,1,date) < (select date from maxDate)
)
select dates.date
, case dates.date when data.date then data.quantity else 0 end quantity
, data.extra
from dates
join data on data.date = (select max(date) from data where data.date <= dates.date)
order by 1