SQL Server 插值缺失行

Question

SQL Server 插值缺失行

5

我有一个记录每天数值的表格。问题在于有时候会缺少一些日期。我想要编写一个SQL查询，实现以下两个功能：

返回缺失的日期
使用线性插值计算缺失的数值

下面是源表格：

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

I want to return:

 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

非常感谢您的帮助。

- SausageFingers

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- GalacticJello · Accepted Answer

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