Oracl Over函数

时间:2023-03-09 19:32:59
Oracl Over函数

Oracl Over函数

简介

在介绍Over之前, 必须提到开窗函数, 与 聚 合函数一样, 开窗函数也是对行集组进行聚合计算, 但是它不像普通聚合函数那样每组只返回一个值, 开窗函数可以为每组返回多个值.

开窗函数用于为行定义一个窗口(这里的窗口是指运算将要操作的行的集合),它对一组值进行操作,不需要使用GROUP BY子句对数据进行分组,



能够在同一行中同时返回基础行的列和聚合列。

在 Oracle中则被称为分析函数,而在DB2中则被称为OLAP函数。

Over 关键字表示把函数当成开窗函数而不是聚合函数。SQL标准允许将所有聚合函数用做开窗函数,使用OVER 关键字来区分这两种用法。

开窗函数的调用格式为:

函数名(列) OVER(选项)

案例及解释

创建一张简单的表, 同时在表内插入15条数据:

create table classInfo(
studentName varchar2(20),
classNumber number(2),
score number(2)
); INSERT INTO CLASSINFO (STUDENTNAME, CLASSNUMBER, SCORE) VALUES ('1', '1', '11');
INSERT INTO CLASSINFO (STUDENTNAME, CLASSNUMBER, SCORE) VALUES ('2', '1', '22');
INSERT INTO CLASSINFO (STUDENTNAME, CLASSNUMBER, SCORE) VALUES ('3', '1', '33');
INSERT INTO CLASSINFO (STUDENTNAME, CLASSNUMBER, SCORE) VALUES ('4', '1', '44');
INSERT INTO CLASSINFO (STUDENTNAME, CLASSNUMBER, SCORE) VALUES ('5', '1', '55');
INSERT INTO CLASSINFO (STUDENTNAME, CLASSNUMBER, SCORE) VALUES ('6', '2', '1');
INSERT INTO CLASSINFO (STUDENTNAME, CLASSNUMBER, SCORE) VALUES ('7', '2', '2');
INSERT INTO CLASSINFO (STUDENTNAME, CLASSNUMBER, SCORE) VALUES ('8', '2', '3');
INSERT INTO CLASSINFO (STUDENTNAME, CLASSNUMBER, SCORE) VALUES ('9', '2', '4');
INSERT INTO CLASSINFO (STUDENTNAME, CLASSNUMBER, SCORE) VALUES ('10', '2', '5');
INSERT INTO CLASSINFO (STUDENTNAME, CLASSNUMBER, SCORE) VALUES ('11', '3', '10');
INSERT INTO CLASSINFO (STUDENTNAME, CLASSNUMBER, SCORE) VALUES ('12', '3', '20');
INSERT INTO CLASSINFO (STUDENTNAME, CLASSNUMBER, SCORE) VALUES ('13', '3', '30');
INSERT INTO CLASSINFO (STUDENTNAME, CLASSNUMBER, SCORE) VALUES ('14', '3', '40');
INSERT INTO CLASSINFO (STUDENTNAME, CLASSNUMBER, SCORE) VALUES ('15', '3', '50');

从这里分别观察其特性:

  1. 常规查询, 不同班级分数的总和

     SELECT CLASSNUMBER, SUM(SCORE) FROM CLASSINFO GROUP BY CLASSNUMBER;
    
         --结果如下
    1 165
    2 15
    3 150

    这是我们常常会用到的分组函数, 不同班级的总分.

    但如果我们还想看到每个人的 name 和 分数呢?不难想出如下代码:

     SELECT STUDENTNAME, SCORE, A.CLASSNUMBER CLASSNUMBER, TOTALSCORE FROM CLASSINFO A
    LEFT OUTER JOIN
    (SELECT CLASSNUMBER, SUM(SCORE) TOTALSCORE FROM CLASSINFO GROUP BY CLASSNUMBER) B
    ON A.CLASSNUMBER = B.CLASSNUMBER
    ORDER BY CLASSNUMBER; --结果如下
    1 11 1 165
    2 22 1 165
    3 33 1 165
    4 44 1 165
    5 55 1 165
    6 1 2 15
    7 2 2 15
    8 3 2 15
    9 4 2 15
    10 5 2 15
    11 10 3 150
    12 20 3 150
    13 30 3 150
    14 40 3 150
  2. 使用Over之后:

     SELECT STUDENTNAME, SCORE, CLASSNUMBER, SUM(SCORE) OVER(PARTITION BY CLASSNUMBER ) TOTALSCORE
    FROM CLASSINFO ORDER BY CLASSNUMBER; --结果与上述相同, 就不再展示

    通过 PARTITION BY CLASSNUMBER 进行分组

  3. 让我们尝试下面几种用法, 就不在这张表上做操作:

     create table overDemo(
    dateNumber number(2),
    sale number(5)
    );
    INSERT INTO OVERDEMO (DATENUMBER, SALE) VALUES ('1', '10');
    INSERT INTO OVERDEMO (DATENUMBER, SALE) VALUES ('2', '10');
    INSERT INTO OVERDEMO (DATENUMBER, SALE) VALUES ('3', '20');
    INSERT INTO OVERDEMO (DATENUMBER, SALE) VALUES ('4', '20');
    INSERT INTO OVERDEMO (DATENUMBER, SALE) VALUES ('5', '30');
    INSERT INTO OVERDEMO (DATENUMBER, SALE) VALUES ('6', '30');
    INSERT INTO OVERDEMO (DATENUMBER, SALE) VALUES ('7', '40');
    INSERT INTO OVERDEMO (DATENUMBER, SALE) VALUES ('8', '40');
    INSERT INTO OVERDEMO (DATENUMBER, SALE) VALUES ('9', '50');
    INSERT INTO OVERDEMO (DATENUMBER, SALE) VALUES ('10', '50'); SELECT * FROM OVER DEMO; --表中数据如下
    DATENUMBER SALE
    1 10
    2 10
    3 20
    4 20
    5 30
    6 30
    7 40
    8 40
    9 50
    10 50 SELECT DATENUMBER, SUM(SALE)
    OVER (ORDER BY DATENUMBER )
    TATLESALE FROM OVERDEMO ORDER BY DATENUMBER; --结果如下
    1 10 -- sale(1)
    2 20 -- sale(1) + sale(2)
    3 40 -- sale(1) + sale(2) + sale(3)
    4 60 --...
    5 90 --...
    6 120 --...
    7 160 --...
    8 200 --...
    9 250 --...
    10 300 --sale(1) + sale(2) + ... + sale(10)

    不难发现, 这种累加方式, 求和方式是 根据DATENUMBER 连续求和

     SELECT DATENUMBER, SUM(SALE)
    OVER (ORDER BY DATENUMBER
    DESC )
    TATLESALE FROM OVERDEMO ORDER BY DATENUMBER; --结果如下 1 300
    2 290
    3 280
    4 260
    5 240
    6 210
    7 180
    8 140
    9 100
    10 50 SELECT DATENUMBER, SUM(SALE)
    OVER ()
    TATLESALE FROM OVERDEMO ORDER BY DATENUMBER; --结果如下 1 300
    2 300
    3 300
    4 300
    5 300
    6 300
    7 300
    8 300
    9 300 SELECT DATENUMBER, SUM(SALE)
    OVER (ORDER BY SALE)
    TATLESALE FROM OVERDEMO ORDER BY DATENUMBER; --结果如下 1 20
    2 20
    3 60
    4 60
    5 120
    6 120
    7 200
    8 200
    9 300
    10 300
  4. 小结

    SUM() Over(ORDER BY COLUMN_NAME) 连续累加

    SUM() Over(ORDER BY COLUMN_NAME DESC) 倒序累加

    SUM() Over() 全部求和

  5. 注意

    但需要注意的一个地方是: 在这里使用 Order By,计算方式是: 会根据后面的字段, 首先进行分组, 然后再度进行累加:

    如: ORDER BY SALE, 则类似于 SUM(SALE) FROM TABLE GROUP BY SALE, 对求得的结果集再度进行累加. 而在 DATENUMBER 累加的时候没有出现这种现象, 是因为 DATENUMBER都是唯一值.

    甚至 OVER(ORDER BY COLUMN_A, COLUMN_B), 也是先根据

    SUM(COLUMN_AIM) FROM TABLE GROUP BY COLUMN_A, COLUMN_B, 对求得的结果集进行累加.

  6. PARTITION BY

     SELECT STUDENTNAME, SCORE, CLASSNUMBER, SUM(SCORE)
    OVER(PARTITION BY CLASSNUMBER ORDER BY STUDENTNAME) TOTALSCORE
    FROM CLASSINFO ORDER BY CLASSNUMBER; --结果 1 11 1 11
    2 22 1 33
    3 33 1 66
    4 44 1 110
    5 55 1 165
    10 5 2 5
    6 1 2 6
    7 2 2 8
    8 3 2 11
    9 4 2 15
    11 10 3 10
    12 20 3 30
    13 30 3 60
    14 40 3 100
    15 50 3 150

    而后再来看 PARTITION BY COLUMN_A ORDER BY COLUMN_B, 则是先通过 COLUMB_A 进行分组, 而后 在 分组的结果集内, 根据 COLUMN_B 再度进行累加操作.

  7. 总结

    在 Over() 中, 会根据 Over前的聚合函数决定所需要的操作, 同时根据 Partition By 的列进行分组, 对于 分组后的数据, 再度通过 Order By 后的字段 进行相应的 累加 等操作.

    在上面的案例中说来:

    首先根据 CLASSNUMBER 班级编号, 对所有数据分组, 分成三组数据:

     --1
    S C SCORE
    1 1 11
    2 1 22
    3 1 33
    4 1 44
    5 1 55
    --2
    6 2 1
    7 2 2
    8 2 3
    9 2 4
    10 2 5
    --3
    11 3 10
    12 3 20
    13 3 30
    14 3 40
    15 3 50

    而后分别根据三组数据中的 STUDENTNAME 进行分组, 分组结果与上面相同,不同的是在 当前的每一组内 又分成 5 组数据, 而后在 每一组内, 根据新分出来的 5 组数据进行 累加 求和.

    如果将 Order By StudentName 改为 ClassNumber, 可以猜想结果:

    按照刚才的理论来说: 仍然是分成上面三组数据, 在第二次分组中又有所区别, 三组中每一组内的 ClassNumber都相同, 因此 归结为一组数据.在组内累加求和 也仅仅进行一次 累加结果分别为 165 15 150. 因此最终的结果不难猜想:

         1   11  1   165
    2 22 1 165
    3 33 1 165
    4 44 1 165
    5 55 1 165
    10 5 2 15
    6 1 2 15
    7 2 2 15
    8 3 2 15
    9 4 2 15
    11 10 3 150
    12 20 3 150
    13 30 3 150
    14 40 3 150
    15 50 3 150

    自己测试了一遍, 结果一致.

注意

以上理论仅仅是根据结果自己猜想, 方便理解. 并没有官方文档的 说明来支撑.

Rank( )

需要再提到的一点是:

比较常用到的另一个地方是 Rank() Over();

它的功能也比较简单, 排序, 做名次的时候会用到.

--在此之前先对之前的 CLASSINFO 表数据略做修改

UPDATE CLASSINFO SET SCORE = '11' WHERE STUDENTNAME = '6';
UPDATE CLASSINFO SET SCORE = '22' WHERE STUDENTNAME = '7';
UPDATE CLASSINFO SET SCORE = '33' WHERE STUDENTNAME = '8'; SELECT STUDENTNAME, SCORE, CLASSNUMBER,
RANK() OVER(ORDER BY SCORE) AS 名次 FROM CLASSINFO; --结果
9 4 2 1
10 5 2 2
11 10 3 3
1 11 1 4
6 11 2 4
12 20 3 6
7 22 2 7
2 22 1 7
13 30 3 9
3 33 1 10
8 33 2 10
14 40 3 12
4 44 1 13
15 50 3 14
5 55 1 15

名次的结果还是比较的清晰明了, 但在这里需要注意的一个地方是, 在查询的时候, 出现了两个 第 4 名, 随后接下来便是 第6名, 是的, 你没想错, 在相同的时候, 会将名次并列, 然后跳过下一个名次.

但又会出现一种比较常见的情况:

考试的时候, 总分相同者, 以数学论名次, 再相同, 语文...这样的一套规则

SELECT STUDENTNAME, SCORE, CLASSNUMBER,
RANK() OVER(ORDER BY SCORE, STUDENTNAME) AS 名次 FROM CLASSINFO; --结果
9 4 2 1
10 5 2 2
11 10 3 3
1 11 1 4
6 11 2 5
12 20 3 6
2 22 1 7
7 22 2 8
13 30 3 9
3 33 1 10
8 33 2 11
14 40 3 12
4 44 1 13
15 50 3 14
5 55 1 15

如上, 在order之后, 当相同时以所给的 STUDENTNAME进行排序, 虽然你想说规则不合理, 但 我是规则的制定者.

又有老师提出要求拉, 我仅仅向看我们班的名次该怎么办呢?

SELECT STUDENTNAME, SCORE, CLASSNUMBER,
RANK() OVER(PARTITION BY CLASSNUMBER ORDER BY SCORE) AS 名次 FROM CLASSINFO; --结果 1 11 1 1
2 22 1 2
3 33 1 3
4 44 1 4
5 55 1 5
9 4 2 1
10 5 2 2
6 11 2 3
7 22 2 4
8 33 2 5
11 10 3 1
12 20 3 2
13 30 3 3
14 40 3 4
15 50 3 5

相信看过我对 Over的解释, 理解这段也不算难. 就不再赘述.

DENSE_RANK( )

需求是无尽的, 如果主任又说, 我们学校排名, 并列第二名, 下一个孩子就该是第三名, 虽然只有四个人, 但好歹让 家长开心下.

SELECT STUDENTNAME, SCORE, CLASSNUMBER,
DENSE_RANK() OVER(ORDER BY SCORE) AS 名次 FROM CLASSINFO; --结果
9 4 2 1
10 5 2 2
11 10 3 3
1 11 1 4
6 11 2 4
12 20 3 5
7 22 2 6
2 22 1 6
13 30 3 7
3 33 1 8
8 33 2 8
14 40 3 9
4 44 1 10
15 50 3 11
5 55 1 12

这样查询下来, 一共15 个人, 我们家孩子 考了个 12 名, 还算不错, 不是倒一. 家长得多开心呀.

在 DENSE_RANK() 中, 在 Rank() 中提到的规则 同样适用.

到这里, Over() 的讲述也差不多告一段落.