SQL Server插入缺少的行 [英] SQL Server Interpolate Missing rows

问题描述

我有下表记录每天的值.问题是有时会丢失几天.我想编写一个SQL查询，该查询将:

I have the following table which records a value per day. The problem is that sometimes days are missing. I want to write a SQL query that will:

1. 退回失踪的日子
2. 使用线性插值计算缺失值

因此，请参见以下源表:

So from the following source table:

Date           Value
--------------------
2010/01/10     10
2010/01/11     15
2010/01/13     25
2010/01/16     40

我想返回:

 Date           Value
 --------------------
 2010/01/10     10
 2010/01/11     15
 2010/01/12     20
 2010/01/13     25
 2010/01/14     30
 2010/01/15     35
 2010/01/16     40

任何帮助将不胜感激.

推荐答案

declare @MaxDate date
declare @MinDate date

select @MaxDate = MAX([Date]),
        @MinDate = MIN([Date])
from Dates

declare @MaxValue int
declare @MinValue int

select @MaxValue = [Value] from Dates where [Date] = @MaxDate
select @MinValue = [Value] from Dates where [Date] = @MinDate

declare @diff int
select @diff = DATEDIFF(d, @MinDate, @MaxDate)

declare @increment int
set @increment = (@MaxValue - @MinValue)  / @diff

select @increment

declare @jaggedDates as table
(
    PID INT IDENTITY(1,1) PRIMARY KEY,
    ThisDate date,
    ThisValue int
)

declare @finalDates as table
(
    PID INT IDENTITY(1,1) PRIMARY KEY,
    [Date] date,
    Value int
)

declare @thisDate date
declare @thisValue int
declare @nextDate date
declare @nextValue int

declare @count int
insert @jaggedDates select [Date], [Value] from Dates
select @count = @@ROWCOUNT

declare @thisId int 
set @thisId = 1
declare @entryDiff int
declare @missingDate date
declare @missingValue int

while @thisId <= @count
begin
    select @thisDate = ThisDate,
            @thisValue = ThisValue
    from @jaggedDates
    where PID = @thisId

    insert @finalDates values (@thisDate, @thisValue)

    if @thisId < @count
    begin
        select @nextDate = ThisDate,
            @nextValue = ThisValue
        from @jaggedDates
        where PID = @thisId + 1

        select @entryDiff = DATEDIFF(d, @thisDate, @nextDate)
        if  @entryDiff > 1
        begin
            set @missingDate = @thisDate
            set @missingValue = @thisValue
            while @entryDiff > 1
            begin
                set @missingDate = DATEADD(d, 1, @missingDate)
                set @missingValue = @missingValue + @increment
                insert @finalDates values (@missingDate, @missingValue)
                set @entryDiff = @entryDiff - 1
            end
        end
    end

    set @thisId = @thisId + 1
end

select * from @finalDates

这篇关于SQL Server插入缺少的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！