Oracle窗口函数基础知识学习

PHP中文网 • 2025年2月22日 15:07:58 • 数据库 • 阅读 2

Oracle为这种情况提供了一个子句：rows between … preceding and … following。从字面上猜测它的意思是：在XXX之前和XXX之后

1,测试环境：
sql> create table win_order(
month number(2),
total_sales number);

2,输入数据:
insert into win_order values(1,623141);
insert into win_order values(2,423124);
insert into win_order values(3,323214);
insert into win_order values(4,212314);
insert into win_order values(5,654314);
insert into win_order values(6,122134);
insert into win_order values(7,859234);
insert into win_order values(8,752314);
insert into win_order values(9,365314);
insert into win_order values(10,265314);
insert into win_order values(11,563114);
insert into win_order values(12,595314);

3,测试语句:

我们前面使用了sum(sum(sal)) over (partition by deptno) 来统计每个部门的总额。现在我们要统计的不单是每个部门，而是所有分区，partition by region_id在这里不起作用了。

Oracle为这种情况提供了一个子句：rows between … preceding and … following。从字面上猜测它的意思是：在XXX之前和XXX之后的所有记录，实际情况如何让我们通过示例来验证：

SQL> select month,
sum(total_sales) month_sales,
sum(sum(total_sales))over (order by month
rows between unbounded preceding and unbounded following) total_sale
from win_order group by month;

MONTH MONTH_SALES TOTAL_SALE
—– ———– ———-
1 623141 5758845
2 423124 5758845
3 323214 5758845
4 212314 5758845
5 654314 5758845
6 122134 5758845
7 859234 5758845
8 752314 5758845
9 365314 5758845
10 265314 5758845
11 563114 5758845
12 595314 5758845

12 rows selected

高亮处的代码在这里发挥了关键作用，，它告诉oracle统计从第一条记录开始至最后一条记录的每月销售额。这个统计在记录集形成的过程中执行了12次，这时相当费时的！但至少我们解决了问题。

unbounded preceding and unbouned following的意思针对当前所有记录的前一条、后一条记录，也就是表中的所有记录。那么假如我们直接指定从第一条记录开始直至末尾呢？看看下面的结果：

SQL> select month,
sum(total_sales) month_sales,
sum(sum(total_sales))over (order by month
rows between 1 preceding and unbounded following) total_sale
from win_order group by month ;

MONTH MONTH_SALES TOTAL_SALE
—– ———– ———-
1 623141 5758845
2 423124 5758845
3 323214 5135704
4 212314 4712580
5 654314 4389366
6 122134 4177052
7 859234 3522738
8 752314 3400604
9 365314 2541370
10 265314 1789056
11 563114 1423742
12 595314 1158428

12 rows selected
很明显这个语句错了。实际1在这里不是从第1条记录开始的意思，而是指当前记录的前一条记录。preceding前面的修饰符是告诉窗口函数执行时参考的记录数，如同unbounded就是告诉oracle不管当前记录是第几条，只要前面有多少条记录，都列入统计的范围。