В таблице одна строка. Как извлечь из нее 10 строк? Как сформировать последовательность? Календарь? Побочные "плюшки" иерархического SQL запроса в Oracle.
Таблица dual
Если нам нужно получить SQL запросом текущую дату, то в Oracle мы пишем:
select sysdate from dual
;
31.03.19
В некоторых диалектах SQL для скаляра (текущая дата - скаляр) можно записать без from dual, но Oracle требует полноты минимальной конструкции выражения для чтения данных:
select <список столбцов> from <список таблиц>
Служебная таблица dual как раз и наличествует в системе для формализации выборок "из ниоткуда". Она состоит из одного столбца и одной строки, столбец называется dummy, а в единственной ячейке находится строка из одного символа 'X':
В рамках "обычного" SQL мы можем получить из таблицы - предположим, из простой и понятной таблицы dual - много столбцов, но не более строк, чем в ней содержится:
Конструкция connect by
Но если мы добавим в запрос волшебную конструкцию connect by, то начнутся чудеса:
На самом деле это несколько побочный результат основной идеи, состоящей в восстановлении иерархии данных; возможно, он станет более наглядным, если эту иерархию явно показать псевдографикой:
Календарь как иерархия
Вот, например, календарь на текущий месяц. Берем текущую дату sysdate, обрезаем ее "по месяц" 'month', прибавляем значение иерархического псевдостолбца level, вычитаем единицу, поскольку level начнется с единицы, восстанавливаем иерархию до тех пор, пока результирующее значение даты trunc(sysdate, 'month') + level - 1 не более последнего дня месяца текущей даты last_day(sysdate). Простое и понятное SQL выражение заключило в себе достаточно сложный алгоритм, автоматически работающий с различной (28, 29, 30, 31) продолжительностью месяцев:
Немного поработав с преобразованиями и сравнениями (число дней в неделе постоянно), мы можем получить табель-календарь одним SQL запросом. Этот своего рода "спортивный результат" оказывается весьма полезным для визуализации учетно-отчетной информации:
Самым загадочным в приведенном примере является взятие max() с группировкой по номеру недели to_char(d, 'iw'). Этим достигается "конденсация" изначально разреженной матрицы после транспонирования, выполненного при помощи decode().
А конструкция 1 + trunc(d) - trunc(d, 'iw') - это номер дня в неделе при условии, что первым днем считается понедельник. Эта конструкция работает независимо от NLS-установок и будет давать одинаковый результат на серверах в России, Германии, Великобритании и Америке.
Классический иерархический запрос
Основное же применение иерархических запросов - это исследование и восстановление иерархии, представленной в данных парой (ключ, ключ_родителя), например, каталога товаров магазина:
Иерархические запросы - это мощный инструмент Oracle SQL, позволяющий не только анализировать содержащиеся в данных естественные иерархии, но и строить в составных SQL запросах последовательности. Это особенно удобно для построения графиков редких событий: иерархическим запросом строится ряд дат от минимальной до максимальной без пропусков, а с ним внешним соединением соединяются серии фактических данных. И много-много других интересных применений.