SQL Server 视图: 如何使用插值方法添加缺失行

Question

SQL Server 视图: 如何使用插值方法添加缺失行

sqlsql-serversql-viewlinear-interpolation

8

遇到了一个问题。

我有一个表格，用于存储每日国库收益率曲线的值yield curve。

这是一个非常简单的表格，用于历史查找值。

在年份4、6、8、9、11-19和21-29中，表格中有一些明显的空缺。

公式非常简单，计算第4年的值为0.5*Year3Value + 0.5*Year5Value。

问题是如何编写一个VIEW来返回缺失的年份？

我可能可以在存储过程中完成，但最终结果需要是一个视图。

- Christopher Klein

当一年缺失时，您希望返回什么？最近一年的简单平均值？ - ahains

好问题！不过标题有误导性，请将其更改为类似“MSSQL视图：如何使用插值添加缺失行”或类似的内容。谢谢。 - van

标题已更改，好建议。 - Christopher Klein

4个回答

1

我猜你想让曲线在两年之间平滑移动，如果有间隔，那么如果缺失超过一年，你不想只取最接近的两年的平均值。这是我可能会使用的方法：

SELECT
     NUM.number AS year,
     COALESCE(YC.val, YC_BOT.val + ((NUM.number - YC_BOT.yr) * ((YC_TOP.val - YC_BOT.val)/(YC_TOP.yr - YC_BOT.yr))))
FROM
     dbo.Numbers NUM
LEFT OUTER JOIN dbo.Yield_Curve YC ON
     YC.yr = NUM.number
LEFT OUTER JOIN dbo.Yield_Curve YC_TOP ON
     YC.yr IS NULL AND       -- Only join if we couldn't find a current year value
     YC_TOP.yr > NUM.number
LEFT OUTER JOIN dbo.Yield_Curve YC_TOP2 ON
     YC_TOP2.yr > NUM.number AND
     YC_TOP2.yr < YC_TOP.yr
LEFT OUTER JOIN dbo.Yield_Curve YC_BOT ON
     YC.yr IS NULL AND       -- Only join if we couldn't find a current year value
     YC_BOT.yr < NUM.number
LEFT OUTER JOIN dbo.Yield_Curve YC_BOT2 ON
     YC_BOT2.yr < NUM.number AND
     YC_BOT2.yr > YC_BOT.yr
WHERE
     YC_TOP2.yr IS NULL AND
     YC_BOT2.yr IS NULL AND
     NUM.number BETWEEN @low_yr AND @high_yr

你可以使用CTE（公用表达式）来重写这段代码，而不是使用数字表（仅包含连续数字的表）。如果你想要这样做，你也可以使用NOT EXISTS或MIN和MAX的子查询来代替对YC_BOT2和YC_TOP2的LEFT OUTER JOIN。有些人可能会觉得这种方法很困惑。

- Tom H

1

您可以尝试使用unpivot将年份和值转换为列表。

然后将其与缺失的年份合并选择 YearNo ，(当YearNo = YearNo-1时选择YearValue) * 0.5 + (当YearNo = YearNo+1时选择YearValue) * 0.5 AS YearValue 从unpivotedlist中选择其中YearNo在（我们缺失的年份列表）

然后再次将其旋转以获得所需的格式，并将其放入视图中？

- BeckyLou

0

WITh cal(year) AS
        (
        SELECT  1 AS current_year
        UNION ALL
        SELECT  year + 1
        FROM    cal
        WHERE   year < 100
        )
SELECT  CASE WHEN yield_year IS NULL THEN
             0.5 *
             (
             SELECT  TOP 1 yield_value
             FROM    yield
             WHERE   yield_year < year
             ORDER BY
                     yield_year DESC
             ) +
             0.5 *
             (
             SELECT  TOP 1 yield_value
             FROM    yield
             WHERE   yield_year > year
             ORDER BY
                     yield_year ASC
             )
         ELSE
             yield_value
         END
FROM     cal
LEFT JOIN
         yield
ON       yield_year = year

对于缺失的年份，此查询会取最接近的年份的平均值。

- Quassnoi

我相信 'cal' CTE 会因为递归限制而崩溃。 - van

@van：好的，我忘记了限制器。已经更正了。 - Quassnoi

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

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

- van · Accepted Answer

根据Tom H.的假设，您真正想要的是线性插值，并且不仅缺少年份，而且还缺少月份，因此您需要基于每个计算月份，而不是年份。

对于下面的代码，我假设您有两个表（其中一个可以作为视图的一部分计算）：

- Yield：包含实际数据并以数字形式存储PeriodM，而不是名称。如果您在那里存储PeriodName，则只需要加入该表即可。 - Period（可以像所示那样在视图中计算）：存储期间名称和它代表的月数

以下代码必须有效（您只需基于它创建一个视图）：

WITH "Period" (PeriodM, PeriodName) AS (
    -- // I would store it as another table basically, but having it as part of the view would do
                SELECT  01, '1 mo'
    UNION ALL   SELECT  02, '2 mo' -- // data not stored
    UNION ALL   SELECT  03, '3 mo'
    UNION ALL   SELECT  06, '6 mo'
    UNION ALL   SELECT  12, '1 yr'
    UNION ALL   SELECT  24, '2 yr'
    UNION ALL   SELECT  36, '3 yr'
    UNION ALL   SELECT  48, '4 yr' -- // data not stored
    UNION ALL   SELECT  60, '5 yr'
    UNION ALL   SELECT  72, '6 yr' -- // data not stored
    UNION ALL   SELECT  84, '7 yr'
    UNION ALL   SELECT  96, '8 yr' -- // data not stored
    UNION ALL   SELECT 108, '9 yr' -- // data not stored
    UNION ALL   SELECT 120, '10 yr'
    -- ... // add more
    UNION ALL   SELECT 240, '20 yr'
    -- ... // add more
    UNION ALL   SELECT 360, '30 yr'
)
, "Yield" (ID, PeriodM, Date, Value) AS (
    -- // ** This is the TABLE your data is stored in **
    -- // 
    -- // value of ID column is not important, but it must be unique (you may have your PK)
    -- // ... it is used for a Tie-Breaker type of JOIN in the view
    -- //
    -- // This is just a test data:
                SELECT 101, 01 /* '1 mo'*/, '2009-05-01', 0.06
    UNION ALL   SELECT 102, 03 /* '3 mo'*/, '2009-05-01', 0.16
    UNION ALL   SELECT 103, 06 /* '6 mo'*/, '2009-05-01', 0.31
    UNION ALL   SELECT 104, 12 /* '1 yr'*/, '2009-05-01', 0.49
    UNION ALL   SELECT 105, 24 /* '2 yr'*/, '2009-05-01', 0.92
    UNION ALL   SELECT 346, 36 /* '3 yr'*/, '2009-05-01', 1.39
    UNION ALL   SELECT 237, 60 /* '5 yr'*/, '2009-05-01', 2.03
    UNION ALL   SELECT 238, 84 /* '7 yr'*/, '2009-05-01', 2.72
    UNION ALL   SELECT 239,120 /*'10 yr'*/, '2009-05-01', 3.21
    UNION ALL   SELECT 240,240 /*'20 yr'*/, '2009-05-01', 4.14
    UNION ALL   SELECT 250,360 /*'30 yr'*/, '2009-05-01', 4.09
)
, "ReportingDate" ("Date") AS (
    -- // this should be a part of the view (or a separate table)
    SELECT DISTINCT Date FROM "Yield"
)

-- // This is the Final VIEW that you want given the data structure as above
SELECT      d.Date, p.PeriodName, --//p.PeriodM,
            CAST(
                COALESCE(y_curr.Value,
                    (   (p.PeriodM - y_prev.PeriodM) * y_prev.Value
                    +   (y_next.PeriodM - p.PeriodM) * y_next.Value
                    ) / (y_next.PeriodM - y_prev.PeriodM)
                ) AS DECIMAL(9,4) -- // TODO: cast to your type if not FLOAT
            )  AS Value
FROM        "Period" p
CROSS JOIN  "ReportingDate" d
LEFT JOIN   "Yield" y_curr
        ON  y_curr.Date = d.Date
        AND y_curr.PeriodM = p.PeriodM
LEFT JOIN   "Yield" y_prev
        ON  y_prev.ID = (SELECT TOP 1 y.ID FROM Yield y WHERE y.Date = d.Date AND y.PeriodM <= p.PeriodM ORDER BY y.PeriodM DESC)
LEFT JOIN   "Yield" y_next
        ON  y_next.ID = (SELECT TOP 1 y.ID FROM Yield y WHERE y.Date = d.Date AND y.PeriodM >= p.PeriodM ORDER BY y.PeriodM ASC)

--//WHERE       d.Date = '2009-05-01'