OracleOracl Over函数

Oracl Over函数

简介

在介绍Over从前, 必须提到开窗函数, 与 聚 合函数一样,
开窗函数也是对行集组举办联谊计算,
可是它不像经常聚合函数那么每组只回去壹个值,
开窗函数可以为每组重返三个值.

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

可见在同一行中而且重返基础行的列和聚合列。

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

Over
关键字表示把函数当成开窗函数而不是聚合函数。SQL标准允许将持有聚合函数用做开窗函数,使用OVERubicon关键字来分别那三种用法。

开窗函数的调用格式为:

函数名(列) 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;
    
    --结果与上述相同, 就不再展示
    

    通过 PA卡宴TITION BY CLASSNUMBELX570 举行分组

  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)
    

    不费吹灰之力察觉, 那种增进格局, 求和艺术是 依据DATENUMBE逍客 一连求和

    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,总计办法是:
    会依据后边的字段, 首先举行分组, 然后再行进行添加:

    如: OLANDDE途乐 BY SALE, 则接近于 SUM(SALE) FROM TABLE GROUP BY SALE,
    对求得的结果集再次展开累加. 而在 DATENUMBEPAJERO累加的时候没有出现那种情形, 是因为 DATENUMBESportage都是唯一值.

    甚至 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
    

    后来再来看 PA瑞虎TITION BY COLUMN_A ORDER BY COLUMN_B, 则是先经过
    COLUMB_A 进行分组, 而后 在 分组的结果集内, 依据 COLUMN_B
    再一次进行添加操作.

  7. 总结

    在 Over() 中, 会依据 Over前的聚合函数控制所急需的操作, 同时根据Partition By 的列举行分组, 对于 分组后的多少, 再次通过 Order By
    后的字段 举办对应的 累加 等操作.

    在上头的案例中说来:

    第壹依据 CLASSNUMBE景逸SUV 班级编号, 对具备数据分组, 分成三组数据:

    --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 名, 随后接下去便是 第陆名, 是的, 你没想错,
在同样的时候, 会将名次并列, 然后跳过下三个名次.

但又会出现一种相比较宽泛的情景:

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

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() 的讲述也大致告一段落.

相关文章