SQL：在 OVER() 中使用 WHERE 子句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10027192/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: use WHERE clause in OVER()?
提问by Sreerag
How can I use WHEREclause to filter in the OVERclause?
如何使用WHERE子句过滤OVER子句？
i.e. from the following data
即来自以下数据
LoanID | Principal | Tenor | AmortizingPrincipal
----------------------------------------
1 20000 1 5000
1 20000 2 5000
1 20000 3 5000
1 20000 4 5000
I need a fourth virtual column with the Balance Principal in each Tenor like the following:
我需要在每个 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
Something like this:
像这样的东西：
SELECT
BalancePrincipal = Principal - SUM(AmortizingPrincipal) OVER(PARTITION BY LoanID WHERE Tenor < this row's tenor)
UPDATE:
更新：
The following query gives me the desired result:
以下查询为我提供了所需的结果：
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
Can it be bettered?
可以改善吗？
回答by Damien_The_Unbeliever
If you're using SQL Server 2012, you'd be looking to specify ROWS/RANGEin your OVER:
如果您使用SQL Server 2012中，你会寻找到指定ROWS/RANGE在你的OVER：
Further limits the rows within the partition by specifying start and end points within the partition. This is done by specifying a range of rows with respect to the current row either by logical association or physical association. Physical association is achieved by using the ROWS clause.
通过指定分区内的起点和终点，进一步限制分区内的行。这是通过通过逻辑关联或物理关联指定与当前行相关的行范围来完成的。物理关联是通过使用 ROWS 子句实现的。
Other database systems may have similar features. This feature is new in the 2012 version of SQL Server.
其他数据库系统可能具有类似的功能。此功能是 SQL Server 2012 版中的新增功能。
回答by Glenn
For the sample posted, it doesn't look like a filter is needed:
对于发布的示例，看起来不需要过滤器：
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:
更新：
Seems Sql Server 2008 does not have the windowing clause? I didn't even think you could create an analytic function without a windowing clause. The above sql was run on Oracle and Postgres without issue. By default the window clause is UNBOUNDED PRECEDING AND CURRENT ROW (from - to). But you could change the order and go from CURRENT ROW to UNBOUNDED FOLLOWING.
似乎Sql Server 2008 没有窗口子句？我什至不认为你可以创建一个没有窗口子句的分析函数。上面的 sql 在 Oracle 和 Postgres 上运行没有问题。默认情况下，window 子句是 UNBOUNDED PRECEDING AND CURRENT ROW（从 - 到）。但是您可以更改顺序并从 CURRENT ROW 转到 UNBOUNDED FOLLOWING。
UPDATE2:
更新2：
So I puzzled: what meaning would a (cumulative) SUM have in an analytic function if you are unable to order the rows within the partition? Is there an implicit ordering? I can change the window (below) and get the same result, but must provide the ORDER BY (in Oracle and Postgres). I can't see how the analytic SUM would have any meaning without the ORDER BY.
所以我感到困惑：如果您无法对分区内的行进行排序，那么（累积）SUM 在分析函数中有何意义？是否有隐式排序？我可以更改窗口（如下）并获得相同的结果，但必须提供 ORDER BY（在 Oracle 和 Postgres 中）。如果没有 ORDER BY，我看不出解析 SUM 会有什么意义。
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
