SQL Oracle 中的 OVER 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1092120/
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
OVER clause in Oracle
提问by paweloque
What is the meaning of the OVER clause in Oracle?
Oracle中OVER子句的含义是什么？
回答by Jeffrey Kemp
The OVER clause specifies the partitioning, ordering & window "over which" the analytic function operates.
OVER 子句指定了分析函数运行的分区、排序和窗口。
For example, this calculates a moving average:
例如，这将计算移动平均值：
AVG(amt) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
date amt avg_amt
===== ==== =======
1-Jan 10.0 10.5
2-Jan 11.0 17.0
3-Jan 30.0 17.0
4-Jan 10.0 18.0
5-Jan 14.0 12.0
It operates over a moving window (3 rows wide) over the rows, ordered by date.
它在行上的移动窗口（3 行宽）上运行，按日期排序。
This calculates a running balance:
这将计算运行余额：
SUM(amt) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
date amt sum_amt
===== ==== =======
1-Jan 10.0 10.0
2-Jan 11.0 21.0
3-Jan 30.0 51.0
4-Jan 10.0 61.0
5-Jan 14.0 75.0
It operates over a window that includes the current row and all prior rows.
它在包含当前行和所有先前行的窗口上运行。
This calculates the maximum, separately for each "dept":
这将分别为每个“部门”计算最大值：
MAX(amt) OVER (PARTITION BY dept)
dept amt max_amt
==== ==== =======
ACCT 5.0 7.0
ACCT 7.0 7.0
ACCT 6.0 7.0
MRKT 10.0 11.0
MRKT 11.0 11.0
SLES 2.0 2.0
It operates over a window that includes all rows for a particular dept.
它在一个窗口上运行，该窗口包括特定部门的所有行。
SQL Fiddle: http://sqlfiddle.com/#!4/9eecb7d/122
回答by Quassnoi
You can use it to transform some aggregate functions into analytic:
您可以使用它来将一些聚合函数转换为解析函数：
SELECT MAX(date)
FROM mytable
will return 1row with a single maximum,
将返回1具有单个最大值的行，
SELECT MAX(date) OVER (ORDER BY id)
FROM mytable
will return all rows with a running maximum.
将返回具有运行最大值的所有行。
回答by cletus
It's part of the Oracle analytic functions.
它是Oracle 分析功能的一部分。
