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

Запуск проблемы.

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

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

В таблице на 4 , 6 , 8 , 9 , 11-19 и 21-29 наблюдаются некоторые пробелы.

Формула довольно проста в том, что для расчета 4 это 0.5*Year3Value + 0.5*Year5Value .

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

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

Принимая предположение Тома Х. , что вы действительно хотите, это линейная интерполяция и тот факт, что не только годы, но и месяцы отсутствуют, вам нужно основывать каждый расчет на MONTH, а не в YEAR.

Для кода ниже я предполагаю, что у вас есть 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' 

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

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

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

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

 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 вместо таблицы Numbers (только таблица последовательных номеров). Вы также можете использовать NOT EXISTS или подзапросы с MIN и MAX вместо LEFT OUTER JOINs на YC_BOT2 и YC_TOP2, если вы хотите это сделать. Некоторые люди считают этот метод запутанным.

 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 

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

  • SQL Server. Исключение записей из представления, если два определенных поля равны нулю.
  • Запрос C # SQL Server VIEW
  • Как написать триггер INSTEAD OF INSERT в представлении с несколькими таблицами, который работает с идентификаторами?
  • Entity Framework, просмотр и вместо триггера вставки. Невозможно вставить строку в представление
  • Добавьте столбец Identity в представление в SQL Server 2008
  • SQL Server 2008 R2 и план выполнения в индексированном виде
  • Вместо триггера для обновления вида несколькими таблицами
  • Повысить производительность выборки данных из View
  • Вставка в представление возвращает 2 строки
  • При просмотре
  • Изменение имени / соединения SQL Change
  • Давайте будем гением компьютера.