当前位置：文江博客话题详情

Oracle 中的 OVER 子句

发布于 2024-07-26 11:39:08 字数 27 浏览 7 评论 0原文

Oracle中的OVER子句是什么意思？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

花心好男孩 2024-08-02 11:39:08

OVER 子句指定分析函数运行的“分区、排序和窗口”。

示例 #1：计算移动平均值

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 行宽）上运行。

示例 #2：计算运行余额

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

它在包含当前行和所有先前行的窗口上运行。

注意：对于带有指定排序 ORDER 的 OVER 子句的聚合，默认窗口是 UNBOUNDED PRECEDING 到 CURRENT ROW >，因此上面的表达式可以简化为，具有相同的结果：

SUM(amt) OVER (ORDER BY date)

示例#3：计算每个组内的最大值

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

它在包含特定部门的所有行的窗口上进行操作。

SQL小提琴：http://sqlfiddle.com/#!4/9eecb7d/122

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

Example #1: calculate 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.

Example #2: calculate 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.

Note: for an aggregate with an OVER clause specifying a sort ORDER, the default window is UNBOUNDED PRECEDING to CURRENT ROW, so the above expression may be simplified to, with the same result:

SUM(amt) OVER (ORDER BY date)

Example #3: calculate the maximum within each group

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

回复收藏 0 原文

不再让梦枯萎 2024-08-02 11:39:08

您可以使用它将一些聚合函数转换为解析函数：

SELECT  MAX(date)
FROM    mytable

将返回具有单个最大值的 1 行，

SELECT  MAX(date) OVER (ORDER BY id)
FROM    mytable

将返回具有运行最大值的所有行。

You can use it to transform some aggregate functions into analytic:

SELECT  MAX(date)
FROM    mytable

will return 1 row with a single maximum,

SELECT  MAX(date) OVER (ORDER BY id)
FROM    mytable

will return all rows with a running maximum.

回复收藏 0 原文

少女情怀诗 2024-08-02 11:39:08

它是 Oracle 分析函数的一部分。

回复收藏 0 原文

丶情人眼里出诗心の 2024-08-02 11:39:08

使用 OVER 的另一种方法是让 select 中的结果列对另一个“分区”进行操作。

这：

SELECT 
    name, 
    ssn, 
    case 
      when ( count(*) over (partition by ssn) ) > 1      
      then 1
      else 0
    end AS hasDuplicateSsn
FROM table;

对于 ssn 被另一行共享的每一行，在 hasDuplicateSsn 中返回 1。非常适合为不同错误报告等的数据制作“标签”。

Another way to use OVER is to have a result column in your select operate on another "partition", so to say.

This:

SELECT 
    name, 
    ssn, 
    case 
      when ( count(*) over (partition by ssn) ) > 1      
      then 1
      else 0
    end AS hasDuplicateSsn
FROM table;

returns 1 in hasDuplicateSsn for each row whose ssn is shared by another row. Great for making "tags" for data for different error reports and such.

回复收藏 0 原文

~没有更多了~