SQL：在OVER()中使用WHERE子句？

Question

我怎样可以在 OVER 子句中使用 WHERE 子句进行筛选？

例如，从以下数据中：

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

我需要第四个虚拟列，其中每个 Tenor 的 Balance Principal 如下所示：

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's tenor) 

更新：

下面的查询语句给出了我想要的结果:

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

是否可以更好？

- Sreerag

---

你使用哪个数据库？你是想要获取累加总和吗？ - Chetter Hummin

请查看在SqlServer中计算运行总数。被接受的答案对几种计算运行总和的方法进行了一些基准测试。 - Nikola Markovinović

余额本金不就是本金减去((期限-1)*摊销本金)吗？ - Jodrell

@jodrell 根据给定的样本数据，计算是正确的，但我确实需要累计总额。 BalancePrincipal 是 Principal 减去之前十期的摊销本金总和。 以防摊销本金不是相同的数字，这个方法就不适用了。 - Sreerag

Answer 1

如果您使用的是SQL Server 2012，您需要在OVER中指定ROWS/RANGE，以进一步限制分区内的行：

通过指定分区内的起始点和结束点来进一步限制行。这可以通过逻辑关联或物理关联的方式指定与当前行相对应的一系列行。使用ROWS子句可以实现物理关联。

其他数据库系统可能也有类似的功能。这个功能是SQL Server 2012版本中的新功能。

- Damien_The_Unbeliever

---

一个可能有用的链接 http://sqlandme.com/2011/08/17/sql-server-denali-over-rows-range/ - Chetter Hummin

那可能是我认为我在这里需要的一个。我们如何在SQL 2008中达到相同的目标？ - Sreerag

Answer 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

更新：

似乎 Sql Server 2008 没有窗口从句？我甚至不认为你可以创建一个没有窗口从句的分析函数。上述的 SQL 在 Oracle 和 Postgres 上运行没有问题。默认情况下，窗口子句是 UNBOUNDED PRECEDING AND CURRENT ROW（从-到）。但是，你可以改变顺序，从 CURRENT ROW 到 UNBOUNDED FOLLOWING。

更新2：

所以我感到困惑：如果无法对分区内的行进行排序，那么（累计）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

- Glenn

---

考虑到加法是可交换的，SUM和ORDER BY的组合意味着什么？ - Damien_The_Unbeliever

默认情况下，SUM在UNBOUNDED PRECEDING和CURRENT ROW之间。因此，按降序排序的行会留下一个从最高到最低的累计总和，这似乎是真正想要的。 - Glenn

@Glenn：这会在“order”附近产生语法错误。 FYI：我正在使用SQL 2008。 - Sreerag

SQL Server 2008 R2及之前版本仅具有非常有限的窗口函数实现。仅自SQL Server 2012起才支持“总计”运行。 - user330315

@Glenn：这是PostgreSQL、Oracle和DB2的核心功能 ;) - user330315