Функции RANK, DENSE_RANK, FIRST и LAST в СУБД в Oracle 11g могут использоваться как агрегатные и как аналитические.
Все эти функции имеют дело с ранжированием значений в некоторой выборке. По сути, все значения выборки упорядочиваются и им присваиваются номера таким образом, что первое значение получает номер 1, одинаковые значения получают одинаковые номера, последующие отличающиеся значения получают номера, большие предыдущих. Например,
значение ранг
13 1
15 2
15 2
21 3
22 4
RANK и DENSE_RANK
Синтаксис функций RANK and DENSE_RANK следующий:
-- as aggregate functions
RANK(const1, ... constn ) WITHIN GROUP (ORDER BY expr1, ... exprn )
DENSE_RANK(const1, ... constn ) WITHIN GROUP (ORDER BY expr1, ... exprn )
-- as analytic functions
RANK() OVER ( [ query_partition_clause] ORDER BY clause )
DENSE_RANK() OVER ( [ query_partition_clause] ORDER BY clause )
Отранжируем даты создания пользователей Oracle с помощью аналитической функции:
SQL> ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> SELECT created, rank() OVER (ORDER BY created) FROM all_users;
CREATED RANK()OVER(ORDERBYCREATED)
------------------- --------------------------
2011-08-27 08:20:52 1
2011-08-27 08:20:52 1
2011-08-27 08:20:54 3
2011-08-27 08:36:19 4
2011-08-27 08:37:13 5
2011-08-27 08:37:13 5
2011-08-27 08:40:49 7
2011-08-27 08:41:10 8
2011-08-27 08:55:19 9
2011-08-27 08:56:16 10
2011-08-27 08:56:17 11
2011-08-27 08:56:17 11
2013-02-12 20:41:30 13
2013-06-15 21:04:06 14
14 rows selected.
Агрегатная функция позволяет для данного значения узнать, каков (был бы) ранг этого значения в некоторой выборке:
SQL> SELECT rank(date '2012-01-01') WITHIN GROUP (ORDER BY created) FROM all_users;
RANK(DATE'2012-01-01')WITHINGR
------------------------------
13
Посмотрите на результат предыдущего (аналитического) запроса и соотнесите с ним последний результат. Got it? Если бы у нас был пользователь, созданный 2012-01-01, его ранг в выборке был бы 13.
Или оценим, на каком месте, по количеству таблиц, среди схем в этой БД окажется схема со 100 таблицами:
SQL> SELECT
rank(100) WITHIN GROUP (ORDER BY howmany) || ' of ' || count(howmany),
min(howmany),
max(howmany)
FROM (SELECT count(*) howmany FROM all_tables group by owner);
RANK(100)WITHINGROUP(ORDERBYHO MIN(HOWMANY) MAX(HOWMANY)
------------------------------ ------------ ------------
8 of 11 1 937
Теперь используем аналитические RANK и DENSE_RANK в одном запросе, чтобы увидеть разницу между ними:
SQL> SELECT created,
RANK() OVER (ORDER BY created) rnk,
dense_rank() OVER (ORDER BY created) dense_rnk
FROM all_users;
CREATED RNK DENSE_RNK
------------------- ---------- ----------
2011-08-27 08:20:52 1 1
2011-08-27 08:20:52 1 1
2011-08-27 08:20:54 3 2
2011-08-27 08:36:19 4 3
2011-08-27 08:37:13 5 4
2011-08-27 08:37:13 5 4
2011-08-27 08:40:49 7 5
2011-08-27 08:41:10 8 6
2011-08-27 08:55:19 9 7
2011-08-27 08:56:16 10 8
2011-08-27 08:56:17 11 9
2011-08-27 08:56:17 11 9
2013-02-12 20:41:30 13 10
2013-06-15 21:04:06 14 11
14 rows selected.
Разница между RANK и DENSE_RANK в том, что последняя не делает пропусков в рангах (о чем говорит слово dense). Пример с агрегатными функциями RANK и DENSE_RANK в одном запросе:
SQL> SELECT rank(DATE '1970-01-01') WITHIN GROUP (ORDER BY created) long_ago,
rank(DATE '2012-01-01') WITHIN GROUP (ORDER BY created) rnk,
dense_rank(DATE '2012-01-01') WITHIN GROUP (ORDER BY created) dense_rnk
FROM all_users;
LONG_AGO RNK DENSE_RNK
---------- ---------- ----------
1 13 10
Используя ранжирование в подзапросе, можно выбрать строки с N первыми (последними) значениями выражений, по которым выполняется ранжирование:
SQL> SELECT * FROM (
SELECT username, created, dense_rank() OVER (ORDER BY created) r FROM all_users)
WHERE r = 1;
USERNAME CREATED R
------------------------------ ------------------- ----------
SYSTEM 2011-08-27 08:20:52 1
SYS 2011-08-27 08:20:52 1
SQL> SELECT * FROM (
SELECT username, created, dense_rank() OVER (ORDER BY created DESC) r FROM all_users)
WHERE r <= 3;
USERNAME CREATED R
------------------------------ ------------------- ----------
AY 2013-06-15 21:04:06 1
APEX_040200 2013-02-12 20:41:30 2
APEX_040000 2011-08-27 08:56:17 3
APEX_PUBLIC_USER 2011-08-27 08:56:17 3
Это не то же самое, что выбрать первые N строк из подзапроса с ORDER BY.
FIRST и LAST
Названия функций FIRST и LAST говорят о том, что эти функции имеют дело с первым и последним результатами ранжирования. Синтаксис этих функций следующий:
-- as aggregate functions
AggrF(value) KEEP (DENSE_RANK FIRST|LAST ORDER BY expr1)
-- as analytic functions
AggrF(value) KEEP (DENSE_RANK FIRST|LAST ORDER BY expr1) OVER (PARTITION BY expr2)
Для исследования функций FIRST и LAST создадим и наполним таблицу:
SQL> CREATE TABLE folk (name VARCHAR2(50), salary NUMBER, age NUMBER);
Table created
SQL> INSERT INTO folk VALUES ('Пупкин', 50000, 32);
1 row inserted
SQL> INSERT INTO folk VALUES ('Шапкин', 50000, 35);
1 row inserted
SQL> INSERT INTO folk VALUES ('Машкин', 45000, 47);
1 row inserted
SQL> INSERT INTO folk VALUES ('Булкин', 35000, 32);
1 row inserted
SQL> INSERT INTO folk VALUES ('Банкин', 35000, 23);
1 row inserted
SQL> COMMIT;
Commit complete
В следующем запросе агрегатные функции MAX и MIN работают на подмножествах строк, у которых ранг salary наименьший (FIRST) и наибольший (LAST). Таким образом, мы получаем максимальный и минимальный возраст работников с наименьшей зарплатой, и максимальный и минимальный возраст работников с наибольшей зарплатой:
SQL> SELECT max(age) max_salary,
max(age) KEEP (DENSE_RANK FIRST ORDER BY salary) max_age_min_salary,
min(age) KEEP (DENSE_RANK FIRST ORDER BY salary) min_age_min_salary,
max(age) KEEP (DENSE_RANK LAST ORDER BY salary) max_age_max_salary,
min(age) KEEP (DENSE_RANK LAST ORDER BY salary) min_age_max_salary
FROM folk;
MAX_SALARY MAX_AGE_MIN_SALARY MIN_AGE_MIN_SALARY MAX_AGE_MAX_SALARY MIN_AGE_MAX_SALARY
---------- ------------------ ------------------ ------------------ ------------------
47 32 23 35 32
Комментарии под соответствующими частями запроса ниже иллюстрируют логическую последовательность работы FIRST:
SQL> SELECT max(age) KEEP (DENSE_RANK FIRST ORDER BY salary) FROM folk;
-- 1) найти строки с минимальным рангом по salary
-- 2) найти max(age) на строках, найденных на шаге 1
MAX(AGE)KEEP(DENSE_RANKFIRSTOR
------------------------------
32
Логика работы запросов с FIRST и LAST подразумевает сортировку (ранжирование), фильтрацию по наименьшему или наибольшему рангу и агрегирование.
Максимальный возраст работника с минимальной зарплатой, найденный в предыдущем запросе, можно получить и без использования FIRST. Следующие запросы менее эффективны, чем запрос с функцией FIRST, но отвечают на тот же вопрос, не используя ранжирование:
SQL> SELECT max(age) FROM folk WHERE salary = (SELECT min(salary) FROM folk);
MAX(AGE)
----------
32
SQL> SELECT max(age) FROM folk, (SELECT min(salary) m FROM folk) m where m.m = salary;
MAX(AGE)
----------
32
SQL> SELECT max(DECODE(salary, m.m, age, NULL)) FROM folk, (SELECT min(salary) m FROM folk) m;
MAX(DECODE(SALARY,M.M,AGE,NULL
------------------------------
32
В качестве аналитических функций FIRST и LAST приобретают дополнительную гибкость, позволяя в одном запросе выводить скалярные и агрегированные значения. Например, можно найти для каждого работника максимальную зарплату, получаемую работниками одного с ним возраста:
SQL> SELECT name, age, salary,
max(salary) KEEP (DENSE_RANK FIRST ORDER BY age) OVER (PARTITION BY age) max_salary_by_age
FROM folk;
NAME AGE SALARY MAX_SALARY_BY_AGE
-------------------- ---------- ---------- -----------------
Банкин 23 35000 35000
Пупкин 32 50000 50000
Булкин 32 35000 50000
Шапкин 35 50000 50000
Машкин 47 45000 45000
Проделанные эксперименты дают представление о назначении и потенциале функций ранжирования, но вряд ли исчерпывают их потенциал. Посмотрим, насколько они пригодятся в работе.
В заключение, убираю мусор:
SQL> DROP TABLE folk;
Спасибо!
ОтветитьУдалить