Oracle中的OVER子句 [英] OVER clause in Oracle

问题描述

Oracle中OVER子句的含义是什么?

What is the meaning of the OVER clause in Oracle?

推荐答案

OVER子句指定分区，排序和排序.解析功能在哪个窗口上运行.

The OVER clause specifies the partitioning, ordering & window "over which" the analytic function operates.

例如，这将计算移动平均值:

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

按日期排序，在行上方的移动窗口(宽3行)上操作.

It operates over a moving window (3 rows wide) over the rows, ordered by date.

这将计算运行余额:

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

SQL Fiddle: http://sqlfiddle.com/#!4/9eecb7d/122

这篇关于Oracle中的OVER子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！