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

Для примера выберем только фамилию и город.

Рисунок 161. Запрос к таблице REGTEST: демонстрация REGEXP_SUBSTR

Воспользовавшись данным методом в сочетании с командой CONNECT BY, можно преобразовать каждую подстроку с разделителями в строку таблицы.

Рисунок 162. Демонстрация REGEXP_SUBSTR и LEVEL

Или более простой пример: найдем количество букв «о» в слове «молоко».

Приведите еще несколько примеров использования регулярных выражений в SQL.

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

Проверка телефона на соответствие заданной маске:

Парсинг и замена XML-выражения:

1. Повторить запросы ЭТОГО шага.

2. Подсчитать количество букв «т» в слове «трактор», используя регулярные выражения.

3. Используя регулярные выражения, выбрать все телефоны из таблицы AUTO, начинающиеся с 911, 915 (REGEXP_LIKE, REGEXP_SUBSTR).

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

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

Аналитические запросы в практическом плане чаще применяются для решения следующих специализированных задач:

• запросы с рейтингами;

• запросы с накопительным итогом;

• запросы для вычисления результата в рамках временного окна;

• запросы для поиска следующих и предыдущих значений после текущей записи в источнике данных по некоторому признаку.

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

Рассмотрим общую структуру аналитических запросов:

Аналитическая функция — одна из аналитических функций, ниже по тексту есть описание данных функций.

• OVER — специальная конструкция, показывающая базе данных, что в запросе используется аналитический SQL;

• PARTITION — партицирование — это логическая модель разделения данных в запросе, некий сегмент данных, объединенных по общему признаку (обычно это одна или несколько колонок запроса);

• ORDER BY — упорядочивание — показывает, как будут отсортированы данные в рамках заданного сегмента (ASC/DESC): порядок, по возрастанию или по убыванию.

В аналитическом SQL наиболее важная составляющая запроса — аналитические функции.

Разберем основные аналитические функции. Всего аналитических функций более 30, но наиболее часто употребляются именно следующие:

Проще всего разобраться с аналитическими функциями и аналитическим SQL на примерах.

Подготовим необходимые данные для демонстрации:

— табельный номер, имя, отдел, зарплата.

У нас есть таблица PERSONA, в которой есть колонки «табельный номер сотрудника», «имя сотрудника», «номер отдела, в котором работает сотрудник» и «оклад сотрудника в долларах».

Запросы списка лидеров

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

Первые три сотрудника с самой высокой зарплатой по отделам (сегмент по отделу).

Данный запрос делит сотрудников на отделы по номеру отдела PARTITION BY OTD, сортирует сотрудников по заплате и с помощью внешнего запроса выводит на экран первых трех сотрудников с самой высокой зарплатой.