среда, 16 октября 2013 г.

RANK, DENSE_RANK, FIRST и LAST в Oracle 11g

Функции 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;

1 комментарий: