Oracle разбить выборку на части
Можно бесконечно долго «воротить нос» от использования SQL для Data Preparation, отдавая лавры змеиному языку, но нельзя не признавать факт, что чаще мы используем и еще долго будем использовать SQL для работы с данными, в том числе и очень объемными.
Более того, считаем, что на текущий момент SQL окажется под рукой сотрудника с большей вероятностью, чем Python, и поможет быстро решить аналитическую задачку с приоритетом «-1».
Предложение OVER помогает «открыть окно», т.е. определить строки, с которым будет работать та или иная функция.
Предложение partion BY не является обязательным, но дополняет OVER и показывает, как именно мы разделяем строки, к которым будет применена функция.
ORDER BY определит порядок обработки строк.
В одном select может быть больше одного OVER, эта прекрасная особенность упростит выполнение аналитической задачи в дальнейшем.
Итак, оконные функции делятся на:
- Агрегатные функции
- Ранжирующие функции
- Функции смещения
- Аналитические функции
Собственно, те же, что и обычные, только встроенные в конструкцию с OVER
SUM/ AVG / COUNT/ MIN/ MAX
Для наглядности работы данных функций воспользуемся базовым набором данных (T)
Найти максимальную задолженность в каждом банке.
Для чего тут оконные функции? Можно же просто написать:
В данном контексте, действительно, применение оконных функций нецелесообразно, но, когда речь заходит о задаче:
Собрать дэшборд, в котором содержится информация о максимальной задолженности в каждом банке, а также средний размер процентной ставки в каждом банке в зависимости от сегмента, плюс еще количество договоров всего всем банкам (в голове рисуются множественные джойны из подзапросов и как-то сразу тяжело на душе). Однако, как я говорил выше, в одном select можно использовать много OVER, а также еще один прекрасный факт: набор строк в окне, связывается с текущей строкой, а не с группой агрегированных. Таким образом:
SELECT TB, ID_CLIENT, ID_DOG, OSZ, PROCENT_RATE, RATING, SEGMENT , MAX(OSZ) OVER (PARTITION BY TB) 'Максимальная задолженность в разбивке по банкам' , AVG(PROCENT_RATE) OVER (PARTITION BY TB, SEGMENT) 'Средняя процентная ставка в разрезе банка и сегмента' , COUNT(ID_DOG) OVER () 'Всего договоров во всех банках' FROM T
На примере AVG(procent_RATE) OVER (partition BY TB, segment) подробнее:
- Мы применяем AVG – агрегатную функцию по подсчету среднего значения к столбцу procent_RATE.
- Затем предложением OVER определяем, что будем работать с некоторым набором строк. По умолчанию, если указать OVER() с пустыми строками, то этот набор строк равен всей таблице.
- Предложением partition BY выделяем разделы в наборе строк по заданному условию, в нашем случае, в разбивке на Территориальные банки и Сегмент.
- В итоге, к каждой строке базовой таблицы применится функция по подсчету среднего из набора строк, разбитых на разделы (по Территориальным Банкам и Сегменту).
Другой тип оконных функций, надо признать, мой любимый и был использован для решения многих задач. Функции ранжирования для каждой строки в разделе возвращают значение рангов или рейтингов. Все ведь любят рейтинги, правда…?
Базовый набор данных: банки, отделы и количество ревизий.
Сами ранжирующие функции:
ROW_number – нумерует строки в результирующем наборе.
RANK -присваивает ранг для каждой строки, если найдутся одинаковые значения, то следующий ранг присваивается с пропуском.
DENSE_RANK -присваивает ранг для каждой строки, если найдутся одинаковые значения, то следующий ранг присваивается без пропуска.
NTILE – помогает разделить результирующий набор на группы.
Для понимания написанного, проранжируем таблицу по убыванию количества ревизий:
SELECT * , ROW_NUMBER() OVER(ORDER BY count_revisions desc) , Rank() OVER(ORDER BY count_revisions desc) , DENSE_RANK() OVER(ORDER BY count_revisions desc) , NTILE(3) OVER(ORDER BY count_revisions desc) FROM Table_Rev
ROW_number – пронумеровал столбцы в порядке убывания количества ревизий.
RANK – проранжировал отделы во всех банках в порядке убывания количества ревизий, но как только встретились одинаковые значения (количество ревизий 95), функция присвоила им ранг 4, а следующее значение получило ранг 6.
DENSE_RANK – аналогично RANK, но как только встретились одинаковые значения, следующее значение получило ранг 5.
NTILE – функция помогла разбить таблицу на 3 группы (указал в аргументе). Так как в таблице 18 значений, в каждую группу попало по 6.
Найти второй отдел во всех банках по количеству ревизий.
Можно, конечно, воспользоваться чем-то вроде:
SELECT MAX(count_revisions) ms FROM Table_Rev WHERE count_revisions!=(SELECT MAX(count_revisions) FROM Table_Rev)
Но если речь идет не про второй отдел, а про трети? .. уже сложнее. Действительно, никто не списывает со счетов offset, но в этой статье говорится об оконных функциях, так почему бы не написать так:
With T_R as ( SELECT * , DENSE_RANK() OVER(ORDER BY count_revisions desc) ds FROM Table_Rev ) SELECT * FROM T_R WHERE ds=3
Как и во всех других типах функций, здесь можно выделять разделы с помощью partitionby. Например, найти отдел в каждом банке, с меньшим количеством проведенных ревизий, для этого разделяем на секции по территориальным банкам, сортируем по возрастанию:
With T_R as ( SELECT * , DENSE_RANK() OVER(PARTITION BY tb ORDER BY count_revisions) ds FROM Table_Rev ) SELECT tb,dep,count_revisions FROM T_R WHERE ds=1
Оконные функции смещения помогут нам, когда необходимо обратиться к строке в наборе данных из окна, относительно текущей строки с некоторым смещением. Проще говоря, узнать, какое значение (событие/ дата) идет после/до текущей строки. Похоже на отличную штуку в предобработке лога данных.
Привет, Хабр! В компании, где я работаю, часто проходят (за мат извините) митапы. На одном из них выступал мой коллега с докладом об оконных функциях и группировках Oracle. Эта тема показалась мне стоящей того, чтобы сделать о ней пост.
С самого начала хотелось бы уточнить, что в данном случае Oracle представлен как собирательный язык SQL. Группировки и методы их применения подходят ко всему семейству SQL (который понимается здесь как структурированный язык запросов) и применимы ко всем запросам с поправками на синтаксис каждого языка.
Всю необходимую информацию я постараюсь кратко и доступно объяснить в двух частях. Пост скорее будет полезен начинающим разработчикам. Кому интересно — добро пожаловать под кат.
Часть 2: Оконные функции
Оконные функции появились ещё в Microsoft SQL Server 2005. Они осуществляют вычисления в заданном диапазоне строк внутри предложения Select. Если говорить кратко, то “окно” — это набор строк, в рамках которого происходит вычисление. “Окно” позволяет уменьшить данные и более качественно их обработать. Такая функция позволяет разбивать весь набор данных на окна.
Оконные функции обладают огромным преимуществом. Нет необходимости формировать набор данных для расчетов, что позволяет сохранить все строки набора с их уникальными ID. Результат работы оконных функций добавляется к результатирующей выборке в еще одно поле.
SELECT column_name(s)
Агрегирующая функция (столбец для вычислений)
OVER ([PARTITION BY столбец для группировки]
FROM table_name
[ORDER BY столбец для сортировки]
[ROWS или RANGE выражение для ограничения строк в пределах группы])
OVER PARTITION BY — это свойство для задания размеров окна. Здесь можно указывать дополнительную информацию, давать служебные команды, например добавить номер строки. Синтаксис оконной функции вписывается прямо в выборку столбцов.
Давайте рассмотрим все на примере: в нашу таблицу добавился еще один отдел, теперь в таблице 15 строк. Мы попытаемся вывести работников, их з/п, а также максимальную з/п организации.
В первом поле мы берем имя, во втором — зарплату. Дальше мы применяем оконную функцию over(). Используем её для получения максимальной зарплаты по всей организации, так как не указаны размеры “окна”. Over() с пустыми скобками применяется для всей выборки. Поэтому везде максимальная зарплата — 10 000. Результат действия оконной функции добавляется к каждой строчке.
Если убрать из четвертой строки запроса упоминание оконной функции, т.е. остается только max (salary), то запрос не сработает. Максимальную зарплату просто не удалось бы посчитать. Так как данные обрабатывались бы построчно, и на момент вызова max (salary) было бы только одно число текущей строки, т.е. текущего работника. Вот тут и можно заметить преимущество оконной функции. В момент вызова она работает со всем окном и со всеми доступными данными.
Давайте рассмотрим еще один пример, где нужно вывести максимальную з/п каждого отдела:
Фактически мы задаем рамки для “окна”, разбивая его на отделы. В качестве ранжирующего примера мы указываем department. У нас есть три отдела: dev, qa и sales.
“Окно” находит максимальную зарплату для каждого отдела. В результате выборки мы видим, что оно нашло максимальную зарплату сначала для dev, затем для qa, потом для sales. Как уже упоминалось выше, результат оконной функции записывается в результат выборки каждой строки.
В предыдущем примере в скобках после over не было указано. Здесь мы использовали PARTITION BY, которое позволило задать размеры нашего окна. Здесь можно указывать какую-то доп информацию, передавать служебные команды, например, номер строки.
выборка констант
Для выбора констант может указываться любой источник. Однако, если мы хотим просто
подсчитать значение какого-то выражения, то указывать источник имеющий сотню тысяч записей затратно по ресурсам. Поэтому многие СУБД позволяют выбрать константы без указания источника. Oracle не поддерживает синтаксис SELECT без FROM, а для выбора констант используется специальная системная таблица dual.
уникальные записи
Записи выборки считаются одинаковыми, если значения соответствующих полей одинаковы. Поэтому для демонстрации distinct на нашей таблице нужно исключить первичный ключ (поле id) из выборки.
Конструкция ORDER BY позволяет последовательно отсортировать сразу по нескольким столбцам. Столбцы, по которым происходит сортировка, желательно проиндексировать.
Агрегатные функции, группировка данных
Для группировки данных в запросе select используется конструкция group by,
в которой должны быть перечислены те же столбцы, что и после select. Ниже приведен
пример вывода данных по группам для таблицы bills.
Сами по себе группы редко используются, и предыдущий пример выборки можно заменить
сортировкой. Другое дело, если необходимо воспользоваться одной из групповых функций,
называемых агрегатными:
- avg([DISTINCT|ALL] column) — среднее значение по указанному столбцу;
- count(*|[DISTINCT|ALL] соlumn) — количество элементов в выборке
или в группе определяемой указанным столбцом; - sum([DISTINCT | ALL] соlumn) — сумма значений указанного столбца;
- max(соlumn) — максимальное значение в столбце;
- min(соlumn) — минимальное значение в столбце.
Ключевое слово DISTINCT позволяет игнорировать повторные значения в столбце, ALL
обрабатывает все значения в столбце (по умолчанию), * позволяет включить в обработку поля с null значением.
В MySQL между именем функции и скобкой не должно быть пробелов.
Ниже приведен пример использования агрегатных функций в качестве выбираемых данных. Если
агрегатная функция используется в выборке без group by, то она применяется ко всем записям
выборки, иначе для каждой группы в отдельности. И в любом случае в перечислении select нельзя
смешивать групповые столбцы с не групповыми.
Агрегатные функции можно использовать в выражениях условия в конструкции having для
отбора группы.
Oracle
В Oracle для нумерации записей введен псевдостолбец rownum.
Заключение
SQL не так прост, как кажется на первый взгляд. Все описанное выше — это базовые возможности оконных функций. С их помощью можно “упростить” наши запросы. Но в них скрыто намного больше потенциала: есть служебные операторы (например ROWS или RANGE), которые можно комбинировать, добавляя больше функциональности запросам.
Сразу хочется отметить, что данная статья написана исключительно для людей, начинающих свой путь в изучении SQL и оконных функций. Здесь могут быть не разобраны сложные применения функций и могут не использоваться сложные формулировки определений - все написано максимально простым языком для базового понимания.
P.S. Если автор что-то не разобрал и не написал, значит он посчитал это не обязательным в рамках этой статьи)))
Для примеров будем использовать небольшую таблицу, которая показывает оценки учеников по разным предметам. В БД табличка выглядит следующим образом
SQL часто используется для вычислений в данных различных метрик или агрегаций значений по измерениям. Помимо функций агрегации для этого широко используются оконные функции.
Оконная функция в SQL - функция, которая работает с выделенным набором строк (окном, партицией) и выполняет вычисление для этого набора строк в отдельном столбце.
Партиции (окна из набора строк) - это набор строк, указанный для оконной функции по одному из столбцов или группе столбцов таблицы. Партиции для каждой оконной функции в запросе могут быть разделены по различным колонкам таблицы.
В чем заключается главное отличие оконных функций от функций агрегации с группировкой?
При использовании агрегирующих функций предложение GROUP BY сокращает количество строк в запросе с помощью их группировки.
При использовании оконных функций количество строк в запросе не уменьшается по сравнении с исходной таблицей.
Порядок расчета оконных функций в SQL запросе
Сначала выполняется команда выборки таблиц, их объединения и возможные подзапросы под командой FROM.
Далее выполняются условия фильтрации WHERE, группировки GROUP BY и возможная фильтрация c HAVING
Только потом применяется команда выборки столбцов SELECT и расчет оконных функций под выборкой.
После этого идет условие сортировки ORDER BY, где тоже можно указать столбец расчета оконной функции для сортировки.
Здесь важно уточнить, что партиции или окна оконных функций создаются после разделения таблицы на группы с помощью команды GROUP BY, если эта команда используется в запросе.
Добавление итогов в SQL
Еще раз рассмотрим таблицу bills созданную в пункте об агрегатных функциях.
Предположим мы хотим вывести все суммы, а в конце выборки добавить итоговую сумму.
Наиболее универсальным способом является объединение двух запросов.
Для решения подобных задач в стандарте введена конструкция ROLLUP генерирующая
дополнительную строку. Если в определении столбца агрегатная функция не используется,
то соответствующее поле в этой строке заполняется значением null. В противном случае
заполняется значением выражения столбца, причем агрегатная функция выполняется ко
всем записям основной выборки.
А теперь предположим мы хотим вывести все суммы с итогами по каждой группе и в конце выборки
общий итог. Ниже приведен пример с использованием объединений. Чтобы общий итог был точно в
конце выборки, задаем в поле d максимальню дату. В Oracle и Postgre можно оставить значение
null.
Подобную задачу можно решить с помощью стандартной конструкции CUBE, если она уже
реализована в СУБД. Куб генерирует не только общий итог, но и все возможные под итоги.
Ниже приведен пример использования куба. Для упрощения кода пустые значения не заменяются.
выборка по группам
И напоследок пару примеров группировки данных.
выборка по столбцам таблиц
Если источники данных указаны, то кроме констант можно выбирать столбцы или строить выражения с их использованием. Столбец определяется как имя_источника.имя_столбца. Если источник данных один, то его имя можно опустить. Для выбора всех столбцов всех источников используется символ *. Аналогично можно выбрать все столбцы указанного источника: имя_источника.*. Ниже приведен пример выборки всех записей нашей таблицы.
Синтаксис оконных функций
Синтаксис оконных функций вне зависимости от их класса будет так или иначе состоять из идентичных команд.
Оконные функции можно прописывать как под командой SELECT, так и в отдельном ключевом слове WINDOW, где окну дается алиас (псевдоним), к которому можно обращаться в SELECT выборке.
выборка по условию
Конструкция WHERE позволяет ограничить множество выбираемых записей. Ниже приведено несколько примеров.
Часть 1: предложения Order by, Group by, Having
Здесь мы поговорим о сортировке — Order by, группировке — Group by, фильтрации — Having и о плане запроса. Но обо всем по-порядку.
Order by
Оператор Order by выполняет сортировку выходных значений, т.е. сортирует извлекаемое значение по определенному столбцу. Сортировку также можно применять по псевдониму столбца, который определяется с помощью оператора.
Преимущество Order by в том, что его можно применять и к числовым, и к строковым столбцам. Строковые столбцы обычно сортируются по алфавиту.
Сортировка по возрастанию применяется по умолчанию. Если хотите отсортировать столбцы по убыванию — используйте дополнительный оператор DESC.
SELECT column1, column2, … (указывает на название)
FROM table_name
ORDER BY column1, column2… ASC|DESC;
Давайте все рассмотрим на примерах:
В первой таблице мы получаем все данные и сортируем их по возрастанию по столбцу ID.
Во второй мы также получаем все данные. Сортируем по столбцу ID по убыванию, используя ключевое слово DESC.
В третьей таблице используется несколько полей для сортировки. Сначала идет сортировка по отделу. При равенстве первого оператора для полей с одинаковым отделом применяется второе условие сортировки; в нашем случае — это зарплата.
Все довольно просто. Мы можем задать более одного условия сортировки, что позволяет более грамотно сортировать выходные списки.
Group by
В SQL оператор Group by собирает данные, полученные из базы данных в определенных группах. Группировка разделяет все данные на логические наборы, что дает возможность выполнять статистические вычисления отдельно в каждой группе.
Этот оператор используется для объединения результатов выборки по одному или нескольким столбцам. После группировки будет только одна запись для каждого значения, использованного в столбце.
С использованием оператора SQL Group by тесно связано использование агрегатных функций и оператор SQL Having. Агрегатная функция в SQL — это функция, возвращающая какое-либо одно значение по набору значений столбца. Например: COUNT(), MIN(), MAX(), AVG(), SUM()
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
Group by стоит после условного оператора WHERE в запросе SELECT. По желанию можно использовать ORDER BY, чтобы отсортировать выходные значения.
Итак, опираясь на таблицу из предыдущего примера, нам нужно найти максимальную зарплату сотрудников каждого отдела. В итоговой выборке должно получиться название отдела и максимальная зарплата.
Решение 1 (без использования группировки):
Решение 2 (с использованием группировки):
В первом примере решаем задачу без использования группировки, но с использованием подселекта, т.е. в один селект вкладываем второй. Во втором решении используем группировку.
Второй пример вышел короче и читабельнее, хотя выполняет такие же функции, что и первый.
Как у нас работает Group by: сначала разбивает два отдела на группы qa и dev. Потом для каждого из них ищет максимальную зарплату.
Having
Having это инструмент фильтрации. Он указывает на результат выполнения агрегатных функций. Предложение Having используется в SQL там, где нельзя применить WHERE.
Если предложение WHERE определяет предикат для фильтрации строк, то Having используется после группировки для определения логичного предиката, фильтрующего группу по значениям агрегатных функций. Предложение необходимо для проверки значений, полученных при помощи агрегатных функций из групп строк.
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
Сначала мы выводим отделы со средней зарплатой больше 4000. Затем выводим максимальную зарплату с применением фильтрации.
Решение 1 (без использования GROUP BY и HAVING):
Решение 2 (с использованием GROUP BY и HAVING):
В первом примере используется два подселекта: один для нахождения максимальной зарплаты, другой для фильтрации средней зарплаты. Второй пример, опять же, вышел намного проще и лаконичнее.
PostgreSQL
В PostgreSQL для этих целей можно выделить последовательность и сбрасывать ее перед новой
выборкой.
синонимы (алиасы)
При выборе данных можно назначать временные синонимы источникам данных и используемым столбцам. А в некоторых случаях необходимо. Например, когда источник подзапрос соединяется с другим источником, именование подзапроса обязательно. Другой пример, это объединение нескольких выборок, имена столбцов которых должны совпадать. Ключевое слово AS как правило необязательно, а в Oracle разрешено только для столбцов.
Нумерация записей
В стандарт SQL2003 уже добавлена функция row_number(), если она еще не реализована
в вашей версии БД, используйте следующие методы.
План запроса
Нередко бывают ситуации, когда запрос работает долго, потребляя значительные ресурсы памяти и дисков. Чтобы понять, почему запрос работает долго и неэффективно, мы можем посмотреть план запроса.
План запроса — это предполагаемый план выполнения запроса, т.е. как СУБД будет его выполнять. СУБД распишет все операции, которые будут выполняться в рамках подзапроса. Проанализировав все, мы сможем понять, где в запросе слабые места и с помощью плана запроса сможем оптимизировать их.
Исполнение любого SQL предложения в Oracle извлекает так называемый “план исполнения”. Этот план исполнения запроса является описанием того, как Oracle будет осуществлять выборку данных, согласно исполняемому SQL предложению. План представляет собой дерево, которое содержит порядок шагов и связь между ними.
К средствам, позволяющим получить предполагаемый план выполнения запроса, относятся Toad, SQL Navigator, PL/SQL Developer и др. Они выдают ряд показателей ресурсоемкости запроса, среди которых основными являются: cost — стоимость выполнения и cardinality (или rows) — кардинальность (или количество строк).
Чем больше значение этих показателей, тем менее эффективен запрос.
Ниже можно увидеть анализ плана запроса. В первом решении используется подселект, во втором — группировка. Обратите внимание, что в первом решении обработано 22 строки, во втором — 15.
Анализ плана запроса:
Ещё один анализ плана запроса, в котором применяется два подселекта:
Этот пример приведен как вариант нерационального использования средств SQL и я не рекомендую вам его использовать в своих запросах.
Все перечисленные выше функции упростят вам жизнь при написании запросов и повысят качество и читабельность вашего кода.
Операции над выборками
Так как выборка по сути является множеством, то и доступные операции над ними
соответствующие:
- UNION — объединение, в конечной выборке записи из обоих запросов;
- INTERSECT — пересечение, в конечной выборке записи входящие в оба запроса;
- EXCEPT — исключение, в конечной выборке записи входящие только в первый запрос.
Запросы участвующие в таких операциях должны следовать нескольким условиям.
Иметь одинаковое число столбцов, соответствующие столбцы должны быть одного типа.
Тип данных столбца должен быть простым, т.е. не разрешаются типы подобные blob.
MySQL 5 поддерживает только UNION, в Oracle EXCEPT для других целей,
а для исключения используется MINUS.
По умолчанию в результирующую выборку попадают только уникальные записи.
Для включения всех записей используется ключевое слово ALL после имени операции.
Например, в следующем примере будет две записи со значением 2.
MySQL
В MySQL для этого надо воспользоваться переменной. Чтобы увидеть результат следующего
примера в MySQLQueryBrowser, необходимо начать транзакцию (на панели кнопка после слова
Transaction). Далее выполняем приведенные в примере команды и затем завершаем транзакцию
(соседняя кнопка с галочкой).
Соединения (Join)
Этот раздел написан на основе материалов сайта Javenue.
Ключевое слово join в SQL используется при построении select выражений. Инструкция Join позволяет объединить колонки из нескольких таблиц в одну. Объединение происходит временное и целостность таблиц не нарушается. Существует три типа join-выражений:
- inner join;
- outer join;
- cross join;
В свою очередь, outer join может быть left, right и full (слово outer обычно опускается).
В качестве примера (DBMS Oracle) создадим две простые таблицы и сконструируем для них SQL-выражения с использованием join .
В первой таблице будет хранится ID пользователя и его nick-name, а во второй — ID ресурса, имя ресурса и ID пользователя, который может этот ресурс администрировать.
Содержимое таблиц пусть будет таким:
Конструкция join выглядит так:
. join_type join table_name on condition .
Где join_type — тип join-выражения, table_name — имя таблицы, которая присоединяется к результату, condition — условие объединения таблиц.
Кострукция join располагается сразу после select-выражения. Можно использовать несколько таких конструкций подряд для объединения соответствующего кол-ва таблиц. Логичнее всего использовать join в том случае, когда таблица имеет внешний ключ ( foreign key ).
Inner join необходим для получения только тех строк, для которых существует соответствие записей главной таблицы и присоединяемой. Иными словами условие condition должно выполняться всегда. Пример:
Результат будет таким:
В случае с left join из главной таблицы будут выбраны все записи, даже если в присоединяемой таблице нет совпадений, то есть условие condition не учитывает присоединяемую (правую) таблицу. Пример:
Результат выполнения запроса:
Результат показывает все ресурсы и их администраторов, вне зависимотсти от того есть они или нет.
Right join отображает все строки удовлетворяющие правой части условия condition , даже если они не имеют соответствия в главной (левой) таблице:
А результат будет следующим:
Результирующая таблица показывает ресурсы и их администраторов. Если адмнистратор не задействован, эта запись тоже будет отображена. Такое может случиться, например, если ресурс был удален.
Full outer join (ключевое слово outer можно опустить) необходим для отображения всех возможных комбинаций строк из нескольких таблиц. Иными словами, это объединение результатов left и right join .
А результат будет таким:
Некоторые СУБД не поддерживают такую функциональность (например, MySQL), в таких случаях обычно используют объединение двух запросов:
Наконец, cross join. Этот тип join еще называют декартовым произведением (на английском — cartesian product). Настоятельно рекомендую использовать его с умом, так как время выполнения запроса с увеличением числа таблиц и строк в них растет нелинейно. Вот пример запроса, который аналогичен cross join :
Классы Оконных функций
Множество оконных функций можно разделять на 3 класса:
Функции смещения (Value)
Агрегирующие:
Можно применять любую из агрегирующих функций - SUM, AVG, COUNT, MIN, MAX
Ранжирующие:
В ранжирующих функция под ключевым словом OVER обязательным идет указание условия ORDER BY, по которому будет происходить сортировка ранжирования.
ROW_NUMBER() - функция вычисляет последовательность ранг (порядковый номер) строк внутри партиции, НЕЗАВИСИМО от того, есть ли в строках повторяющиеся значения или нет.
RANK() - функция вычисляет ранг каждой строки внутри партиции. Если есть повторяющиеся значения, функция возвращает одинаковый ранг для таких строчек, пропуская при этом следующий числовой ранг.
DENSE_RANK() - то же самое что и RANK, только в случае одинаковых значений DENSE_RANK не пропускает следующий числовой ранг, а идет последовательно.
Про NULL в случае ранжирования:
Для SQL пустые NULL значения будут определяться одинаковым рангом
Функции смещения:
Это функции, которые позволяют перемещаясь по выделенной партиции таблицы обращаться к предыдущему значению строки или крайним значениям строк в партиции.
LAG() - функция, возвращающая предыдущее значение столбца по порядку сортировки.
LEAD() - функция, возвращающая следующее значение столбца по порядку сортировки.
На простом примере видно, как можно в одной строке получить текущую оценку, предыдущую и следующую оценки Пети в четвертях.
FIRST_VALUE()/LAST_VALUE() - функции возвращающие первое или последнее значение столбца в указанной партиции. В качестве аргумента указывает столбец, значение которого нужно вернуть. В оконной функции под словом OVER обязательное указание ORDER BY условия.
В следующей версии статьи разберем отдельно такое понятие как фрейм окна функции или window frame и рассмотрим на простых примерах как он используется.
Я работаю PL/SQL разработчиком. Есть задача собирать некоторые данные для метрик, чтобы отслеживать загрузку систем. Есть некоторая функция, которая вызывается с параметром, состоящим из списка ID.
Задача заключается в следующем. Нужно разбить такую строку на элементы и записать их в целочисленную коллекцию.
Приступим.
Для начала нужны данные для работы. Напишем функцию, которая генерирует строку с числами, разделенными запятой. В функцию будем передавать целочисленный аргумент N – количество чисел в последовательности.
Мудрить не будем, последовательность сделаем с типом VARCHAR2, а не CLOB. Далее объясню, почему именно VARCHAR2.
Код функции для генерации последовательности:
Вернёмся к нашей задаче.
Первое, что приходит на ум, это сделать цикл по строке, с уменьшением длинны строки на каждой итерации. Так как по условию задачи результат нужно поместить в коллекцию, то создадим соответствующую коллекцию.
Результат:
0
1
2
…
421
422
423
…
Функция createNumber() принимает аргумент v_N = 1000. В функции createNumber() можно видеть обработку переполнения переменной v_str. Нехитрым подсчётом можно выяснить, что 4000 байт хватит для 1021 чисел. Наша 1000 без проблем влезает в этот размер.
Как видно, результат тот, который нужен был. Строка разделена.
Пусть даже в Oracle нет встроенной функции split(), как например в Java или Python, но данный вариант меня не устраивает, так как я считаю, что слишком много кода написано для такой простой задачи как разбиение строки.
На данном этапе я задумался, а можно ли разбить строку только средствами SQL? Я имею ввиду не классический SQL, а тот SQL, который предлагает Oracle.
Я вспомнил про конструкцию для построения иерархических запросов CONNECT BY.
Необязательный оператор START WITH говорит Oracle с чего начинать цикл, т.е. какая строка будет корневой. Условие может быть практически любым. Условие после CONNECT BY нужно указать обязательно. Тут надо сказать Oracle, как долго продолжать цикл.
Видно, что единственно важное условие для построения иерархического запроса – это оператор CONNECT BY, остальное «нанизывается» по мере надобности.
Также у этой конструкции есть псевдостолбец level, который возвращает уровень вложенности на текущей итерации.
На первый взгляд может показаться, что данная конструкция для разбиения строки не подходит. Это не совсем так. Если правильно задать условие, то рекурсивный обход можно превратить в циклический, как в циклах while или for.
Прежде чем писать запрос, обдумаем алгоритм обхода строки. Нужно, начиная от начала строки, отрезать некоторое количество символов, до символа разделителя. Выше я писал про псевдостолбец level. Мы его будем использовать, как номер текущей итерации.
Получается что-то такое:
Но если присмотреться, то можно увидеть, что данный алгоритм не сработает на самой первой итерации, так как третий аргумент функции INSTR() не может равняться 0.
Поэтому добавим небольшое условие с помощью функции DECODE().
Теперь самая первая итерации будет отрабатывать корректно.
Пора бы применить конструкцию CONNECT BY. Плюс вынесем нашу строку наверх.
Я уже писал, что при правильном условии конструкция CONNECT BY сможет вести себя подобно циклу. Условие выполняется до тех пор, пока функция INSTR() может найти n-ую позицию символа разделителя, где n – это номер текущей итерации, а как мы помним за номер итерации отвечает псевдостолбец level.
Вроде бы задача решена? Нет.
Код может и работает, но его читаемость нулевая. Я уже думал вернуться к варианту с циклом, но придумал как улучшить вариант с CONNECT BY.
В Oracle есть такое мощное средство, как регулярные выражения. Конкретно функции regexp_instr() и regexp_substr().
regexp_instr(исходная_строка, шаблон[, начальная_позиция [, вхождение ] ]) — функция возвращает позицию символа, находящегося в начале или конце соответствия для шаблона, так же как и ее аналог INSTR().
regexp_substr(исходная_строка, шаблон[, позиция [, вхождение ]]) — функция возвращает подстроку, которая соответствует шаблону.
Перепишем запрос, используя регулярные выражения:
Код читается намного лучше, чем в предыдущем примере. Такой вариант меня устраивает.
В конце было бы логичным привести сравнения времени выполнения разбора строки для трёх вариантов. Выше я обещал объяснить, почему вместо типа CLOB будем использовать тип VARCHAR2. Это нужно как раз для сравнения времени выполнения. Так как Oracle обрабатывает тип CLOB по-другому, чем VARCHAR2, что может исказить результаты.
Выбор данных выполняется командой SELECT.
Ниже приведен примерный список используемых ею конструкций и ключевых слов, полный же список зависит от реализации СУБД:
- AS — определяет временный синоним источника данных или столбца;
- FROM — указывает источники данных как таблицы, представления, другие выборки. По необходимости здесь можно указать соединение источников — каким образом запись одного источника сопоставляется с записью другого;
- WHERE — позволяет указать условия по которым нужно производить отбор данных. Если хоть одно из перечисленных условий не выполняется, запись не попадает в выборку. Здесь также можно указать соединение источников;
- ORDER BY — позволяет отсортировать выборку по указанным полям;
- ASC, DESC — задают направление сортировки;
- GROUP BY — позволяет разбить выборку на группы по указанному полю. Все записи, имеющие одно и то же значение в указанном поле, будут принадлежать одной группе;
- HAVING — позволяет задать условие включения группы в выборку. Набор возможных условий как у WHERE плюс возможность использования агрегатных функций;
- FOR UPDATE — позволяет заблокировать выбранные данные для изменения;
- DISTINCT — позволяет включить в выборку только уникальные записи. Конечно это замедляет запрос, но бывает необходимо при использовании агрегатных функций.
Это наверно самая сложная команда, поэтому ее изучение лучше разбить на несколько частей. Здесь рассмотрим эту команду в общем, а соединения, группировку данных и подобное более детально чуть позже. Пусть имеется следующая таблица с указанными данными.
Читайте также: