Keep dense rank first order by

Keep dense rank first order by

Периодические заметки о программировании

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

RANK, DENSE_RANK, FIRST и LAST в Oracle 11g

Функции RANK , DENSE_RANK , FIRST и LAST в СУБД в Oracle 11g могут использоваться как агрегатные и как аналитические.

Все эти функции имеют дело с ранжированием значений в некоторой выборке. По сути, все значения выборки упорядочиваются и им присваиваются номера таким образом, что первое значение получает номер 1, одинаковые значения получают одинаковые номера, последующие отличающиеся значения получают номера, большие предыдущих. Например,

RANK и DENSE_RANK

Синтаксис функций RANK and DENSE_RANK следующий:

Отранжируем даты создания пользователей Oracle с помощью аналитической функции:

Агрегатная функция позволяет для данного значения узнать, каков (был бы) ранг этого значения в некоторой выборке:

Посмотрите на результат предыдущего (аналитического) запроса и соотнесите с ним последний результат. Got it? Если бы у нас был пользователь, созданный 2012-01-01, его ранг в выборке был бы 13.

Или оценим, на каком месте, по количеству таблиц, среди схем в этой БД окажется схема со 100 таблицами:

Теперь используем аналитические RANK и DENSE_RANK в одном запросе, чтобы увидеть разницу между ними:

Разница между RANK и DENSE_RANK в том, что последняя не делает пропусков в рангах (о чем говорит слово dense). Пример с агрегатными функциями RANK и DENSE_RANK в одном запросе:

Используя ранжирование в подзапросе, можно выбрать строки с N первыми (последними) значениями выражений, по которым выполняется ранжирование:

Это не то же самое, что выбрать первые N строк из подзапроса с ORDER BY .

Названия функций FIRST и LAST говорят о том, что эти функции имеют дело с первым и последним результатами ранжирования. Синтаксис этих функций следующий:

Для исследования функций FIRST и LAST создадим и наполним таблицу:

В следующем запросе агрегатные функции MAX и MIN работают на подмножествах строк, у которых ранг salary наименьший ( FIRST ) и наибольший ( LAST ). Таким образом, мы получаем максимальный и минимальный возраст работников с наименьшей зарплатой, и максимальный и минимальный возраст работников с наибольшей зарплатой:

Комментарии под соответствующими частями запроса ниже иллюстрируют логическую последовательность работы FIRST :

Логика работы запросов с FIRST и LAST подразумевает сортировку (ранжирование), фильтрацию по наименьшему или наибольшему рангу и агрегирование.

Максимальный возраст работника с минимальной зарплатой, найденный в предыдущем запросе, можно получить и без использования FIRST . Следующие запросы менее эффективны, чем запрос с функцией FIRST , но отвечают на тот же вопрос, не используя ранжирование:

Читайте также:  Finder показать скрытые файлы

В качестве аналитических функций FIRST и LAST приобретают дополнительную гибкость, позволяя в одном запросе выводить скалярные и агрегированные значения. Например, можно найти для каждого работника максимальную зарплату, получаемую работниками одного с ним возраста:

Проделанные эксперименты дают представление о назначении и потенциале функций ранжирования, но вряд ли исчерпывают их потенциал. Посмотрим, насколько они пригодятся в работе.

добавлено: 02 окт 17
понравилось:0
просмотров: 27541
комментов: 0

Конструкция KEEP FIRST/LAST используется в SQL Oracle для вычисления значения, первой или последней записи в заданной подгруппе, отcортированной по некоторому признаку
она так же позволяет найти результат агрегатной функции по сгруппированным данным, если таких значений несколько

Проще понять это на примере
создадим таблицу курсов валют

заполним таблицу тестовыми данными
за каждый день в таблице может быть несколько курсов заданной валюты

— обратите внимание, за каждую дату может быть несколько разных курсов одной и той же валюты
напишем запрос, который бы выбирал минимальное и максимальное значение курса каждой валюты за наибольшую дату
и за наименьшую , наиболее отдаленную во времени, дату

chf 88,11 88,11 88,33 89,33 88,11 177,66
eur 65,11 65,12 68,11 68,11 130,23 68,11
usd 55,11 56,11 57,11 57,11 111,22 57,11

— а теперь используем KEEP FIRST, KEEP LAST, посмотрите, насколько улучшился наш запрос

chf 88,11 88,11 88,33 89,33 88,11 177,66
eur 65,11 65,12 68,11 68,11 130,23 68,11
usd 55,11 56,11 57,11 57,11 111,22 57,11

немного подробнее о том , что мы здесь написали
— функция sum, max, min находит сумму максимальное и минимальное значение из курсов валют за поcледнюю и первую дата в группе ticker
— в конструкции KEEP FIRST , KEEP LAST мы можем использовать следующие агрегатные функции
MIN, MAX, SUM, AVG, COUNT, VARIANCE, STDDEV

— конструкция KEEP (DENSE_RANK LAST ORDER BY pdate) означает , что мы осуществляем работу с последними LAST значениями, с сортировкой по полю PDATE, в свою очередь, DENSE_RANK FIRST ORDER BY pdate вернет агрегатной функции max(price) первые значения price отсортированные по PDATE

Читайте также:  8800 Arte не заряжается

— и еще плюс , использование KEEP FIRST LAST вместе с аналитической функцией

— разумеется, здесь так же можно использовать и другие агрегатные функции
MIN, MAX, SUM, AVG, COUNT, VARIANCE, STDDEV

usd 55,11 55,11 56,11 57,11 57,11
usd 56,11 55,11 56,11 57,11 57,11
usd 57,11 55,11 56,11 57,11 57,11
usd 57,11 55,11 56,11 57,11 57,11
usd 57,11 55,11 56,11 57,11 57,11
usd 57,22 55,11 56,11 57,11 57,11
usd 58,11 55,11 56,11 57,11 57,11
eur 65,11 65,11 65,12 68,11 68,11
eur 65,12 65,11 65,12 68,11 68,11
eur 67,11 65,11 65,12 68,11 68,11
eur 68,11 65,11 65,12 68,11 68,11
chf 88,11 88,11 88,11 89,33 88,33
chf 88,33 88,11 88,11 89,33 88,33
chf 89,33 88,11 88,11 89,33 88,33

Эти функции, как и функция ROW_NUMBER() , тоже нумеруют строки, но делают это несколько отличным способом. Это отличие проявляется в том, что строки, которые имеют одинаковые значения в столбцах, по которым выполняется упорядочивание, получают одинаковые номера (ранги). Например, значения (отсортированные по возрастанию)

1
5
6
6
6

получат такие номера:

1 1
5 2
6 3
6 3
6 3

Возникает вопрос, с какого номера продолжится нумерация, если, скажем, в последовательности чисел появится 7 и т.д.? Здесь есть два варианта:

1) с номера 4, т.к. это следующий номер по порядку;

2) с номера 6, т.к. следующая строка будет шестая по счету.

Такая "неоднозначность" и привела к появлению двух функций вместо одной — RANK и DENSE_RANK, первая из которых продолжит нумерацию с 6, а вторая (плотная) — с 4.

Рассмотрим несколько примеров. Начнем с демонстрации отличия в поведении функций RANK и ROW_NUMBER:

Здесь в двух последних столбцах выводятся значения сравниваемых функций при упорядочивании строк по столбцу type:

code model color type price num rnk
2 1433 y Jet 270,00 1 1
3 1434 y Jet 290,00 2 1
1 1276 n Laser 400,00 3 3
6 1288 n Laser 400,00 4 3
4 1401 n Matrix 150,00 5 5
5 1408 n Matrix 270,00 6 5

Как и следовало ожидать, ROW_NUMBER пронумеровывает весь набор строк, возвращаемых запросом. Функция RANK, как оказалось, работает по второму из рассмотренных выше варианту, т.е. следующим номером после строк с одинаковым рангом будет номер строки.

А теперь сравним "плотный" и "неплотный" ранги:

code model color type price rnk rnk_dense
2 1433 y Jet 270,00 1 1
3 1434 y Jet 290,00 1 1
1 1276 n Laser 400,00 3 2
6 1288 n Laser 400,00 3 2
4 1401 n Matrix 150,00 5 3
5 1408 n Matrix 270,00 5 3
Читайте также:  Normal dot где находится windows 10

Следует также обратить вниманию на порядок, в котором выводятся строки результирующего набора. Поскольку оператор SELECT в нашем примере не имеет предложения ORDER BY, а для вычисления рангов используется одинаковое упорядочивание по столбцу type, то и результат выводится в том же порядке. В целях оптимизации, если вам не нужно какое-либо другое упорядочение результирующего набора, используйте этот факт, чтобы не выполнять лишние сортировки, которые ухудшают производительность запроса.

Как и для функции ROW_NUMBER, в предложении OVER может использоваться конструкция PARTITION BY, разбивающая весь набор строк, возвращаемых запросом, на группы, к которым затем применяется соответствующая функция.

code model color type price rnk
2 1433 y Jet 270,00 1
3 1434 y Jet 290,00 2
1 1276 n Laser 400,00 1
6 1288 n Laser 400,00 1
4 1401 n Matrix 150,00 1
5 1408 n Matrix 270,00 2

А вот как можно выбрать самые дешевые модели в каждой категории:

model color type price
1433 y Jet 270,00
1276 n Laser 400,00
1288 n Laser 400,00
1401 n Matrix 150,00

Запрос можно было бы написать короче, если бы функцию RANK можно было бы применять в предложении WHERE, т.к. само значение ранга нам не требуется. Однако это запрещено (как и для других ранжирующих функций), по крайней мере, в SQL Server.

Наконец, рассмотрим еще один пример.

Пример. Найти производителей, которые производят более 2-х моделей PC.

Эта задача имеет традиционное решение через агрегатные функции:

Однако эту задачу можно решить и с помощью функции RANK. Идея состоит в следующем: ранжировать модели каждого производителя по уникальному ключу и выбрать только тех производителей, модели которых достигают ранга 3:

И в одном, и в другом случае, естественно, мы получим один и тот же результат:

maker
E

Еще раз повторю: упорядочивание в последнем случае должно быть выполнено по уникальной комбинации столбцов, т.к., в противном случае, моделей может быть больше трех, а ранг меньше (например, 1, 2, 2. ). В нашем случае данное условие выполнено, т.к. упорядочивание выполняется по столбцу model, который является первичным ключом в таблице Product.

Кстати, планы выполнения этих запросов демонстрируют одинаковые стоимости наиболее расходных операций – сканирования таблицы и сортировку (которая я первом случае присутствует неявно и вызвана операцией группировки).

Ссылка на основную публикацию
Adblock detector