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

В конструкции окна аналитической функции есть возможность оперировать интервалами времени. Приведите пример.

Это возможно с данными типа DATE или TIMESTAMP. Пример интервала за 20 дней назад от даты, соответствующей текущей строке запроса.

Можно ли использовать аналитический SQL, аналитические функции вместе с операциями группировки в запросе?

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

Рисунок 176. Демонстрация работы аналитических функций: запрос к PERSONA, функции LAG, LEAD

Конструкция 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 в иерархических запросах?

Вот пример подобного запроса:

Может ли быть использован WITH совместно с UNION?

Да, например таким образом:

В SQL диалекте ORACLE 12C есть возможность определить функцию или процедуру на языке PL/SQL и использовать с помощью оператора WITH, как обычный SQL.

Специальная инструкция WITH с FUNCTION используется, когда необходимо вернуть данные, преобразованные с помощью сложного нелинейного алгоритма.

Примеры

Вывести на экран марки автомобиля из таблицы AUTO.

Используемая функция REVERSIVE_FNC выводит слово справа налево по буквам.

Рисунок 181. Использование WITH и FUNCTION

Еще пример. Добавить к идентификатору города заданное количество нулей, преобразовать к числу.

Рисунок 182. Использование WITH и FUNCTION