OracleOracl 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() 的描述也差不多告一截落.

相关文章