Аналитические функции в Oracle
DENSE_RANK
Функция возвращает ранг строки в упорядоченной группе строк
Aggregate Example
В следующем примере вычисляется рейтинг гипотетического сотрудника с окладом 15 500 долларов США и комиссией 5% в таблице выборки oe.employees:
SELECT DENSE_RANK(15500, .05) WITHIN GROUP
(ORDER BY salary DESC, commission_pct) "Dense Rank"
FROM employees;
Dense Rank
----------
3
Analytic Example
Следующий пример ранжирует сотрудников в типовой схеме кадров в отделе 60 на основе их заработной платы. Одинаковые значения заработной платы получают одинаковый ранг. Однако значения ранга не пропускаются.
SELECT department_id, last_name, salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary) "Dense Rank"
FROM employees WHERE department_id = 60
ORDER BY DENSE_RANK, last_name;
DEPARTMENT_ID LAST_NAME SALARY DENSE_RANK
------------- ------------------------- ---------- ----------
60 Lorentz 4200 1
60 Austin 4800 2
60 Pataballa 4800 2
60 Ernst 6000 3
60 Hunold 9000 4
RANK
Функция возвращает ранг значения в группе значений.
Aggregate Example
В следующем примере вычисляется ранг гипотетического сотрудника в таблице выборки hr.employees с окладом 15 500 долларов США и комиссией 5%:
SELECT RANK(15500, .05) WITHIN GROUP
(ORDER BY salary, commission_pct) "Rank"
FROM employees;
Rank
----------
105
Точно так же следующий запрос возвращает ранг для зарплаты 15 500 долларов среди зарплат сотрудников:
SELECT RANK(15500) WITHIN GROUP
(ORDER BY salary DESC) "Rank of 15500"
FROM employees;
Rank of 15500
--------------
4
Analytic Example
Следующий пример ранжирует сотрудников в типовой схеме кадров в отделе 60 на основе их заработной платы. Одинаковые значения заработной платы получают одинаковый ранг и приводят к непоследовательным рангам.
SELECT department_id, last_name, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary) "Rank"
FROM employees WHERE department_id = 60
ORDER BY RANK, last_name;
DEPARTMENT_ID LAST_NAME SALARY RANK
------------- ------------------------- ---------- ----------
60 Lorentz 4200 1
60 Austin 4800 2
60 Pataballa 4800 2
60 Ernst 6000 4
60 Hunold 9000 5
FIRST_VALUE
Функция возвращает первое значение в упорядоченном наборе значений из аналитического окна.
В следующем примере для каждого сотрудника отдела 90 выбирается имя сотрудника с самой низкой зарплатой:
SELECT employee_id, last_name, salary, hire_date,
FIRST_VALUE(last_name)
OVER (ORDER BY salary ASC ROWS UNBOUNDED PRECEDING) AS fv
FROM (SELECT * FROM employees
WHERE department_id = 90
ORDER BY hire_date);
EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE FV
----------- ------------------------- ---------- --------- -------
102 De Haan 17000 13-JAN-01 De Haan
101 Kochhar 17000 21-SEP-05 De Haan
100 King 24000 17-JUN-03 De Haan
LAST_VALUE
Функция возвращает последнее значение в упорядоченном наборе значений из аналитического окна.
В следующем примере для каждой строки возвращается дата найма сотрудника с самой низкой зарплатой:
SELECT employee_id, last_name, salary, hire_date,
LAST_VALUE(hire_date)
OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING) AS lv
FROM (SELECT * FROM employees
WHERE department_id = 90
ORDER BY hire_date);
EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE LV
----------- ------------------------- ---------- --------- ---------
100 King 24000 17-JUN-03 13-JAN-01
101 Kochhar 17000 21-SEP-05 13-JAN-01
102 De Haan 17000 13-JAN-01 13-JAN-01
NTH_VALUE
Функция возвращает n-ое значение в упорядоченном наборе значений из аналитического окна.
В следующем примере показано минимальное значение amount_sold для второго channel_id в порядке возрастания для каждого prod_id от 13 до 16:
SELECT prod_id, channel_id, MIN(amount_sold),
NTH_VALUE(MIN(amount_sold), 2) OVER (PARTITION BY prod_id ORDER BY channel_id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) nv
FROM sales
WHERE prod_id BETWEEN 13 and 16
GROUP BY prod_id, channel_id;
PROD_ID CHANNEL_ID MIN(AMOUNT_SOLD) NV
---------- ---------- ---------------- ----------
13 2 907.34 906.2
13 3 906.2 906.2
13 4 842.21 906.2
14 2 1015.94 1036.72
14 3 1036.72 1036.72
14 4 935.79 1036.72
15 2 871.19 871.19
15 3 871.19 871.19
15 4 871.19 871.19
16 2 266.84 266.84
16 3 266.84 266.84
16 4 266.84 266.84
16 9 11.99 266.84
13 rows selected.
FIRST and LAST
SELECT empno,
deptno,
sal,
MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS lowest,
MAX(sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS highest
FROM emp
ORDER BY deptno, sal;
EMPNO DEPTNO SAL LOWEST HIGHEST
---------- ---------- ---------- ---------- ----------
7934 10 1300 1300 5000
7782 10 2450 1300 5000
7839 10 5000 1300 5000
7369 20 800 800 3000
7876 20 1100 800 3000
7566 20 2975 800 3000
7788 20 3000 800 3000
7902 20 3000 800 3000
7900 30 950 950 2850
7654 30 1250 950 2850
7521 30 1250 950 2850
7844 30 1500 950 2850
7499 30 1600 950 2850
7698 30 2850 950 2850
LAG
Функция позволяет запрашивать более одной строки в таблице, в то время, не имея присоединенной к себе таблицы. Это возвращает значения из предыдущей строки в таблице.
В следующем примере для каждого продавца в таблице сотрудников показана заработная плата сотрудника, нанятого непосредственно перед этим:
SELECT hire_date, last_name, salary,
LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal
FROM employees
WHERE job_id = 'PU_CLERK'
ORDER BY hire_date;
HIRE_DATE LAST_NAME SALARY PREV_SAL
--------- ------------------------- ---------- ----------
18-MAY-03 Khoo 3100 0
24-JUL-05 Tobias 2800 3100
24-DEC-05 Baida 2900 2800
15-NOV-06 Himuro 2600 2900
10-AUG-07 Colmenares 2500 2600
LEAD
Функция позволяет запрашивать более одной строки в таблице, в то же время, не имея для присоединения к себе таблицы. Возвращает значения из следующей строки в таблице.
В следующем примере для каждого сотрудника отдела 30 в таблице сотрудников указывается дата найма сотрудника, нанятого сразу после:
SELECT hire_date, last_name,
LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "NextHired"
FROM employees
WHERE department_id = 30
ORDER BY hire_date;
HIRE_DATE LAST_NAME Next Hired
--------- ------------------------- ----------
07-DEC-02 Raphaely 18-MAY-03
18-MAY-03 Khoo 24-JUL-05
24-JUL-05 Tobias 24-DEC-05
24-DEC-05 Baida 15-NOV-06
15-NOV-06 Himuro 10-AUG-07
10-AUG-07 Colmenares
LISTAGG
Функция объединяет значения measure_column для каждой группы на основе order_by_clause.
Aggregate Examples
В следующем агрегированном примере с единым набором в таблице hr.employees перечислены все сотрудники отдела 30, отсортированные по дате найма и фамилии:
SELECT LISTAGG(last_name, '; ')
WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
MIN(hire_date) "Earliest"
FROM employees
WHERE department_id = 30;
Emp_list Earliest
------------------------------------------------------------ ---------
Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares 07-DEC-02
Analytic Example
В следующем аналитическом примере для каждого сотрудника, нанятого до 1 сентября 2003 г., показано подразделение сотрудника, дата найма, а также все остальные сотрудники этого отдела, также нанятые до 1 сентября 2003 г.
SELECT department_id "Dept", hire_date "Date", last_name "Name",
LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name)
OVER (PARTITION BY department_id) as "Emp_list"
FROM employees
WHERE hire_date < '01-SEP-2003'
ORDER BY "Dept", "Date", "Name";
Dept Date Name Emp_list
----- --------- --------------- ---------------------------------------------
30 07-DEC-02 Raphaely Raphaely; Khoo
30 18-MAY-03 Khoo Raphaely; Khoo
40 07-JUN-02 Mavris Mavris
50 01-MAY-03 Kaufling Kaufling; Ladwig
50 14-JUL-03 Ladwig Kaufling; Ladwig
70 07-JUN-02 Baer Baer
90 13-JAN-01 De Haan De Haan; King
90 17-JUN-03 King De Haan; King
100 16-AUG-02 Faviet Faviet; Greenberg
100 17-AUG-02 Greenberg Faviet; Greenberg
110 07-JUN-02 Gietz Gietz; Higgins
110 07-JUN-02 Higgins Gietz; Higgins