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

REGEXP_INSTR

REGEXP_INSTR определяет номер первого символа вхождения REGEXP шаблона в строку.

Рисунок 159. Демонстрация REGEXP_INSTR

REGEXP_COUNT

Рисунок 160. Демонстрация REGEXP_COUNT: запрос к таблице REGTEST

На самом деле в REGEXP_LIKE, REGEXP_SUBSTR имеется ряд дополнительных параметров, которые влияют на результат работы данных функций.

Некоторые дополнительные интересные примеры использования REGEXP.

Выбираем подстроку с разделителями, есть строка Зайцев, 01111998, Киев = Зайцев 01111998 Киев

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

Рисунок 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, сортирует сотрудников по заплате и с помощью внешнего запроса выводит на экран первых трех сотрудников с самой высокой зарплатой.

Выполним сначала следующий запрос:

Рисунок 163. Демонстрация работы аналитических функций

Колонка NUM, полученная с помощью аналитической функции ROW_NUMBER () и аналитического подзапроса, возвращает номер строки в наборе данных, разбитом по колонке OTD, отсортированном по SAL.

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

Рисунок 164. Демонстрация работы аналитических функций

Еще один пример: данные делятся по наименованию отдела OTD, а сортировка данных в рамках сегмента по имени — NAME.

По наименованию (PARTITION по отделу), сортировка по NAME.

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

Запросы с накопительным итогом

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

Запросы с накопительным итогом пишутся аналогично запросам, которые мы только что проходили, различаются только функции. В данном случае рассмотрим аналитическую функцию SUM.

Накопительный итог по зарплате.

В данном запросе в колонке Num формируется накопительный итог по зарплате — аналитическая функция SUM (SAL), в рамках отдела с сортировкой по зарплате OVER (PARTITION BY OTD ORDER BY SAL).