Максим Чалышев - Oracle SQL. 100 шагов от новичка до профессионала

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

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

С помощью KEEP FIRST/LAST реализована возможность выбрать первое (последнее) значение в отсортированном наборе внутри группы.

Проще понять это на примере.

Создадим таблицу курсов валют.

Заполним таблицу тестовыми данными.

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

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

Минимальное и максимальное значения курса за наибольшую дату.

Получилось довольно сложно и громоздко, но результат правильный.

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

Немного подробнее о том, что мы здесь написали.

Функция SUM, MAX, MIN находит сумму, максимальное и минимальное значения из курсов валют за последнюю и первую дату в группе 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.

Конструкцию можно использовать совместно с аналитическими функциями.

Использование KEEP FIRST LAST вместе с аналитической функцией.

Более подробно про использование аналитических функций можно посмотреть на предыдущих двух шагах.

Можно ли конструкцию KEEP заменить простыми запросами?

Да, можно построить запрос с использованием DECODE, CASE.

Можно ли применить конструкцию KEEP совместно с аналитическими функциями?

Да, с аналитическими функциями можно использовать конструкцию FIRST LAST.

1. Для автомобилей из таблицы AUTO из нашей учебной схемы найдите цвет авто с максимальной или минимальной датой выпуска, используйте KEEP FIRST LAST.

2. Для городов CITY из нашей учебной схемы найдите название города с максимальным количеством населения, используйте KEEP FIRST LAST.

Для повышения наглядности SQL и читаемости запросов SELECT, для удобства разработки начиная с версии 9 в SQL диалекта ORACLE добавлен специальный оператор WITH.

Оператор WITH позволяет заранее формировать внутренний подзапрос и далее обращаться к данному подзапросу по синониму в основном запросе.

Очень часто используется совместно c XML Type и XML SEQ, а также в сложных запросах с множеством реляционных отношений.

Здесь t1 — псевдоним запроса, к которому идет обращение в основной команде SELECT.

Примеры

Простой оператор WITH:

Рисунок 178. Запрос к CITY: использование WITH

Сложное обращение

Рисунок 179. Запрос к AUTO1: использование WITH

Два запроса из WITH

Рисунок 180. Запрос к ALL_OBJECTS: использование WITH

В этом запросе используется системное представление ALL_OBJECTS, с которым мы познакомимся в следующих шагах.

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

Обратите внимание, что колонка owner является составной.

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

Второй пример:

Можно ли использовать конструкцию WITH с XML?

Да, конструкция WITH с XML работает с разбором XML-документов. Пример подобного запроса мы разберем далее, когда будем изучать работу с XML.

Можно ли использовать конструкцию WITH с CONNECT BY в иерархических запросах?