Представление SQL Server: как добавить недостающие строки с помощью интерполяции

Столкновение с проблемой.

Мне определили таблицу для содержания значений ежедневной казначейской кривой доходности.

Это - довольно простая таблица, используемая для исторического поиска значений.

Существуют notibly некоторые разрывы в таблице в год 4, 6, 8, 9, 11-19 и 21-29.

Формула довольно проста в этом вычислить год 4 это 0.5*Year3Value + 0.5*Year5Value.

Проблема состоит в том, как я могу записать a VIEW это может возвратить недостающие годы?

Я мог, вероятно, сделать это в хранимой процедуре, но конечным результатом должно быть представление.

7
задан duplode 21 July 2019 в 03:33
поделиться

4 ответа

Принимая предположение Том Х. что вам действительно нужна линейная интерполяция, и тот факт, что отсутствуют не только годы, но и месяцы, вам необходимо основывать каждый расчет на МЕСЯЦЕ, а не ГОДЕ.

Для приведенного ниже кода я предполагаю, что у вас есть 2 таблицы (одна из которых может быть вычислена как часть представления):

  • Доходность: содержит реальные данные и сохраняется PeriodM в количестве- месяц, а не название. Если вы сохраните там PeriodName , вам просто нужно будет присоединиться к таблице:
  • Период (может быть вычислен в представлении, как показано) : сохраняет название периода и количество месяцев, которые он представляет

Следующий код должен работать (вам просто нужно создать представление на его основе):

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'
6
ответ дан 7 December 2019 в 05:30
поделиться

Вы можете попробовать unpivot, чтобы получить годы и значения в списке.

Затем объедините это с недостающими годами выберите YearNo , (выберите YearValue, где YearNo = YearNo-1) * 0.5 + (выберите YearValue, где YearNo = YearNo + 1) * 0.5 AS YearValue из unpivotedlist где YearNo in (наш отсутствующий список лет)

Затем снова разверните его, чтобы получить нужный формат и вставить его в представление?

1
ответ дан 7 December 2019 в 05:30
поделиться

Я собираюсь сделать предположение, что вы хотите, чтобы кривая плавно двигалась между двумя годами, если есть разрыв, поэтому, если не хватает более одного года, вы не хотите просто усреднить два ближайших года. Вот что я, вероятно, использовал бы:

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 вместо LEFT OUTER JOINs на YC_BOT2 и YC_TOP2, если хотите это сделать. Некоторых этот метод сбивает с толку.

1
ответ дан 7 December 2019 в 05:30
поделиться
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

Для отсутствующих лет в этом запросе используется среднее значение ближайших найденных лет.

0
ответ дан 7 December 2019 в 05:30
поделиться
Другие вопросы по тегам:

Похожие вопросы: