SQL: использовать предложение WHERE в OVER()?

Как я могу использовать предложение WHERE для фильтрации в предложении OVER?

то есть. из следующих данных

LoanID | Principal | Tenor | AmortizingPrincipal 
----------------------------------------
1         20000       1       5000
1         20000       2       5000
1         20000       3       5000
1         20000       4       5000

Мне нужен четвертый виртуальный столбец с Балансом Принципала в каждом Тензоре, как показано ниже:

LoanID | Principal | Tenor | AmortizingPrincipal | BalancePrinicpal 
-----------------------------------------------------------
1        20000       1       5000                  20000  
1        20000       2       5000                  15000  
1        20000       3       5000                  10000 
1        20000       4       5000                  5000

Что-то вроде этого:

SELECT 
    BalancePrincipal = Principal - SUM(AmortizingPrincipal) OVER(PARTITION BY LoanID WHERE Tenor < this row tenor)

UPDATE:

Следующий запрос дает мне желаемый результат:

SELECT 
    L1.*    
    ,BalancePrincipal = AL1.Principal - ISNULL(Cumulative.AmortizingSum,0) 
FROM
    Loan L1
CROSS APPLY 
    (
        SELECT 
            AmortizingSum = SUM(AmortizingPrincipal)
        FROM 
            Loan L2
        WHERE 
            L1.LoanID = L2.LoanID
            AND 
            L1.Tenor > L2.Tenor 
    ) Cumulative

Можно ли улучшить?

Ответы

Ответ 1

Если вы используете SQL Server 2012, вы должны указать ROWS/RANGE в OVER:

Далее ограничивает строки в разделе, задавая начальную и конечную точки в разделе. Это делается путем указания диапазона строк относительно текущей строки либо путем логической ассоциации, либо физической ассоциации. Физическая ассоциация достигается с помощью предложения ROWS.

Другие системы баз данных могут иметь схожие функции. Эта функция является новой в версии SQL Server версии 2012 года.

Ответ 2

Для опубликованного примера он не похож на фильтр:

SELECT LoanID, Principal, Tenor, AmortizingPrincipal
      ,SUM(AmortizingPrincipal) OVER(PARTITION BY LoanID ORDER BY Tenor Desc) AS BalancePrincipal
  FROM loan
  ORDER BY LoanID, Principal, Tenor

UPDATE:

Кажется, что в Sql Server 2008 нет предложения windowing? Я даже не думал, что вы можете создать аналитическую функцию без окошка. Вышеупомянутый sql был запущен на Oracle и Postgres без проблем. По умолчанию оговорка окна - НЕОБХОДИМАЯ ПРЕДОСТАВЛЯЮЩАЯСЯ И ТЕКУЩАЯ РУКА (от - до). Но вы можете изменить порядок и перейти от CURRENT ROW к UNBOUNDED FOLLOWING.

UPDATE2:

Итак, я озадачился: какое значение имел бы (кумулятивный) SUM в аналитической функции, если вы не можете упорядочить строки внутри раздела? Есть ли неявный порядок? Я могу изменить окно (ниже) и получить тот же результат, но должен предоставить ORDER BY (в Oracle и Postgres). Я не вижу, как аналитический SUM будет иметь какое-либо значение без ORDER BY.

SELECT LoanID, Principal, Tenor, AmortizingPrincipal
      ,SUM(AmortizingPrincipal) OVER(PARTITION BY LoanID ORDER BY tenor
                                     RANGE BETWEEN CURRENT ROW
                                       AND UNBOUNDED FOLLOWING) AS BalancePrincipal
  FROM loan
  ORDER BY LoanID, Principal, Tenor