Аналитические функции

SET LINESIZE 32767
SET PAGESIZE 50000
alter session set nls_date_format='DD-MON-YYYY';
alter session set nls_language=AMERICAN;



DROP TABLE EMP;

CREATE TABLE EMP(
    EMPNO NUMBER(4) NOT NULL,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7, 2),
    COMM NUMBER(7, 2),
    DEPTNO NUMBER(2)
);


INSERT INTO EMP VALUES(7369, 'SMITH',  'CLERK',     7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800,  NULL, 20);
INSERT INTO EMP VALUES(7499, 'ALLEN',  'SALESMAN',  7698,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300,  30);
INSERT INTO EMP VALUES(7521, 'WARD',   'SALESMAN',  7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500,  30);
INSERT INTO EMP VALUES(7566, 'JONES',  'MANAGER',   7839,TO_DATE('2-APR-1981',  'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES(7654, 'MARTIN', 'SALESMAN',  7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES(7698, 'BLAKE',  'MANAGER',   7839,TO_DATE('1-MAY-1981',  'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES(7782, 'CLARK',  'MANAGER',   7839,TO_DATE('9-JUN-1981',  'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES(7788, 'SCOTT',  'ANALYST',   7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES(7839, 'KING',   'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES(7844, 'TURNER', 'SALESMAN',  7698,TO_DATE('8-SEP-1981',  'DD-MON-YYYY'), 1500, 0,    30);
INSERT INTO EMP VALUES(7876, 'ADAMS',  'CLERK',     7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES(7900, 'JAMES',  'CLERK',     7698,TO_DATE('3-DEC-1981',  'DD-MON-YYYY'), 950,  NULL, 30);
INSERT INTO EMP VALUES(7902, 'FORD',   'ANALYST',   7566,TO_DATE('3-DEC-1981',  'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES(7934, 'MILLER', 'CLERK',     7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

Вывод сотрудников, номеров отделов и зарплат.

select ename, deptno, sal from emp
order by deptno, ename;


ENAME          DEPTNO        SAL
---------- ---------- ----------
CLARK              10       2450
KING               10       5000
MILLER             10       1300
ADAMS              20       1100
FORD               20       3000
JONES              20       2975
SCOTT              20       3000
SMITH              20        800
ALLEN              30       1600
BLAKE              30       2850
JAMES              30        950
MARTIN             30       1250
TURNER             30       1500
WARD               30       1250

14 rows selected.

Отсортируем результирующее множество по deptno и ename (внутри deptno).

Вычислим кумулятивную сумму зарплат по всему результирующему множеству.

Видим итоговую сумму зарплат 

select ename, deptno, sal,
sum(sal) over
(order by deptno, ename) run_tot
from emp
order by deptno, ename;

ENAME          DEPTNO        SAL    RUN_TOT
---------- ---------- ---------- ----------
CLARK              10       2450       2450
KING               10       5000       7450
MILLER             10       1300       8750
ADAMS              20       1100       9850
FORD               20       3000      12850
JONES              20       2975      15825
SCOTT              20       3000      18825
SMITH              20        800      19625
ALLEN              30       1600      21225
BLAKE              30       2850      24075
JAMES              30        950      25025
MARTIN             30       1250      26275
TURNER             30       1500      27775
WARD               30       1250      29025

14 rows selected.



Разделим группы отступами (пустыми строками)
break on deptno skip 1

select ename, deptno, sal,
sum(sal) over
(order by deptno, ename) run_tot
from emp
order by deptno, ename;


ENAME          DEPTNO        SAL    RUN_TOT
---------- ---------- ---------- ----------
CLARK              10       2450       2450
KING                        5000       7450
MILLER                      1300       8750

ADAMS              20       1100       9850
FORD                        3000      12850
JONES                       2975      15825
SCOTT                       3000      18825
SMITH                        800      19625

ALLEN              30       1600      21225
BLAKE                       2850      24075
JAMES                        950      25025
MARTIN                      1250      26275
TURNER                      1500      27775
WARD                        1250      29025


14 rows selected.



Разобъём все результирующее множество на группы  по полю  DEPTNO
Теперь функция SUM() будет вычислять кумулятивные суммы зарплат отдельно для каждой группы
(в нашем случае для каждого номера отдела)
Видим суммы зарплат по каждому департаменту:

select ename, deptno, sal,
sum(sal) over
(partition by deptno order by ename) dep_tot
from emp
order by deptno, ename;

ENAME          DEPTNO        SAL    DEP_TOT
---------- ---------- ---------- ----------
CLARK              10       2450       2450
KING                        5000       7450
MILLER                      1300       8750

ADAMS              20       1100       1100
FORD                        3000       4100
JONES                       2975       7075
SCOTT                       3000      10075
SMITH                        800      10875

ALLEN              30       1600       1600
BLAKE                       2850       4450
JAMES                        950       5400
MARTIN                      1250       6650
TURNER                      1500       8150
WARD                        1250       9400


14 rows selected.


В следующем запросе укажем конструкцию,
которая задает условие разбиения результирующего множества на группы
по полю DEPTNO.
Для последовательной нумерации строк в каждой группе,
в соответствии с критериями упорядочения, используется функция
ROW_NUMBER()

В результате видим, что SCOTT - четвертый по списку сотрудник
в отделе 20 при упорядочении по фамилии ENAME.


select ename, deptno, sal,
row_number() over
(partition by deptno order by ename) seq
from emp
order by deptno, ename;

ENAME          DEPTNO        SAL        SEQ
---------- ---------- ---------- ----------
CLARK              10       2450          1
KING                        5000          2
MILLER                      1300          3

ADAMS              20       1100          1
FORD                        3000          2
JONES                       2975          3
SCOTT                       3000          4
SMITH                        800          5

ALLEN              30       1600          1
BLAKE                       2850          2
JAMES                        950          3
MARTIN                      1250          4
TURNER                      1500          5
WARD                        1250          6


14 rows selected.



Все в одном запросе:
select ename, deptno, sal,

sum(sal) over
(order by deptno, ename) run_tot,

sum(sal) over
(order by deptno, ename) run_tot,

row_number() over
(partition by deptno order by ename) seq

from emp
order by deptno, ename;


ENAME          DEPTNO        SAL    RUN_TOT    RUN_TOT        SEQ
---------- ---------- ---------- ---------- ---------- ----------
CLARK              10       2450       2450       2450          1
KING                        5000       7450       7450          2
MILLER                      1300       8750       8750          3

ADAMS              20       1100       9850       9850          1
FORD                        3000      12850      12850          2
JONES                       2975      15825      15825          3
SCOTT                       3000      18825      18825          4
SMITH                        800      19625      19625          5

ALLEN              30       1600      21225      21225          1
BLAKE                       2850      24075      24075          2
JAMES                        950      25025      25025          3
MARTIN                      1250      26275      26275          4
TURNER                      1500      27775      27775          5
WARD                        1250      29025      29025          6


14 rows selected.