- Что такое оконная функция
- Виды функций
- Агрегатные оконные функции SQL
- Ранжирующие оконные функции SQL
- Функции смещения
- Аналитические оконные функции SQL
- Операторы SQL
- ROW_NUMBER и ORDER BY
- PARTITION BY и LAG, LEAD и RANK
- LAG
- LEAD
- RANK
- Часто задаваемые вопросы об оконных функциях SQL
- Что представляет собой SQL?
- Зачем изучать SQL?
- Что собой представляет SQL Server?
- Для чего нужны оконные функции SQL?
- Какую литературу стоит изучить по данной теме?
25 рабочих гипотез для увеличения конверсии на 40%
Скачать материалы
Что это? Оконные функции SQL – инструмент, позволяющий упростить и повысить эффективность сложных запросов. Выполняют вычисления для нескольких строк данных, не прибегая к написанию подзапросов или объединению нескольких таблиц.
Где используются? Оконные функции полезны при работе с агрегатами и аналитическими запросами. Они позволяют вычислять текущие итоги, средние значения, рейтинги и другие показатели на основе данных.
Что такое оконная функция
В SQL она представляет собой набор результатов. Это данные, которые возвращаются из запроса. Иными словами, это таблица выполнения кода инструкции select.
Термин состоит из двух слов. Рассмотрим значение каждого из них.
Окно в SQL – это набор строк или наблюдений в таблице или результирующем наборе. В одной таблице может содержаться более одного окна (в зависимости от формулировки запроса). Для его определения используется OVER() предложение.
Функция в SQL применяется для выполнения той или иной операции с данными. Они предопределены. Функции помогают агрегировать информацию, форматировать строки, извлекать даты и так далее. Таким образом, функции Windows представляют собой функции SQL, с помощью которых можно выполнять различные операции с окном (набором записей).
Оконные функции (ОФ) имеют одну очень важную особенность. Они могут использоваться для того, чтобы указать окна, к которым необходимо применить ту или иную функцию. К примеру, пользователь может разделить полный результирующий набор на несколько групп или окон.
Виды функций
Перед тем как изучить синтаксис оконных функций SQL, необходимо рассмотреть их основные группы:
- агрегатные функции;
- ранжирующие функции;
- функции смещения;
- аналитические функции.
В рамках одной инструкции SELECT с одним предложением FROM можно задействовать целый ряд ОФ. Проанализируем каждую группу и выделим их ключевые особенности.
Агрегатные оконные функции SQL
Речь идет о функциях, выполняющих арифметические вычисления на наборе данных и возвращающих итоговый результат.
- SUM – эта оконная функция SQL определяет сумму значений в столбце;
- COUNT – рассчитывает количество значений в столбце (без учета значений NULL);
- AVG – рассчитывает среднее значение в столбце;
- MAX – определяет наибольшее значение в столбце;
- MIN – указывает наименьшее значение в столбце.
Рассмотрим вариант применения агрегирующих оконных функций SQL с инструкцией OVER на конкретном примере:
| 1. | SELECT |
| 2. | Date |
| 3. | , Medium |
| 4. | , Conversions |
| 5. | , SUM(Conversions) OVER(PARTITION BY Date) AS ‘Sum’ |
| 6. | , COUNT(Conversions) OVER(PARTITION BY Date) AS ‘Count’ |
| 7. | , AVG(Conversions) OVER(PARTITION BY Date) AS ‘Avg’ |
| 8. | , MAX(Conversions) OVER(PARTITION BY Date) AS ‘Max’ |
| 9. | , MIN(Conversions) OVER(PARTITION BY Date) AS ‘Min’ |
| 10. | FROM Orders |
Ранжирующие оконные функции SQL
Это те, которые выполняют ранжирование для каждой строки в окне. К примеру, они помогают присвоить порядковый номер строке или сформировать рейтинг. Список оконных функций SQL из этой группы:
- ROW_NUMBER – возвращает номер строки и применяется для нумерации;
- RANK – позволяет увидеть ранг каждой строки (значения уже анализируются и, если обнаруживаются одинаковые, функция возвращает одинаковый ранг с пропуском следующего значения);
- DENSE_RANK – возвращает ранг каждой строки (в отличие от предыдущей функции, DENSE_RANK возвращает ранг для одинаковых значений без пропуска следующего);
- NTILE – определяет, в какую именно группу входит текущая строка (число групп указывается в скобках).
Рассмотрим пример применения SQL-функций ранжирования:
| 1. | SELECT |
| 2. | Date |
| 3. | , Medium |
| 4. | , Conversions |
| 5. | , ROW_NUMBER() OVER(PARTITION BY Date ORDER BY Conversions) AS ‘Row_number’ |
| 6. | , RANK() OVER(PARTITION BY Date ORDER BY Conversions) AS ‘Rank’ |
| 7. | , DENSE_RANK() OVER(PARTITION BY Date ORDER BY Conversions) AS ‘Dense_Rank’ |
| 8. | , NTILE(3) OVER(PARTITION BY Date ORDER BY Conversions) AS ‘Ntile’ |
| 9. | FROM Orders |
Функции смещения
С помощью них пользователь может перемещаться и обращаться к разным строкам в окне (относительно текущей строки). Кроме того, они позволяют обращаться к значениям в начале или в конце окна.
- LAG или LEAD. Первая функция обращается к информации из предыдущей строки окна, а вторая – к сведениям из следующей строки. LAG и LEAD можно применять для сопоставления значений строк. Эти функции имеют три параметра: столбец, значение которого требуется вернуть, число строк для смещения (изначально стоит 1), значение, которое нужно вернуть, если после смещения возвращается значение NULL.
- FIRST_VALUE или LAST_VALUE. Они позволяют получить первое и последнее значение в окне. В качестве параметра используется столбец, значение которого требуется вернуть.
Пример использования этих оконных функций в SQL:
| 1. | SELECT |
| 2. | Date |
| 3. | , Medium |
| 4. | , Conversions |
| 5. | , LAG(Conversions) OVER(PARTITION BY Date ORDER BY Date) AS ‘Lag’ |
| 6. | , LEAD(Conversions) OVER(PARTITION BY Date ORDER BY Date) AS ‘Lead’ |
| 7. | , FIRST_VALUE(Conversions) OVER(PARTITION BY Date ORDER BY Date) AS ‘First_Value’ |
| 8. | , LAST_VALUE(Conversions) OVER(PARTITION BY Date ORDER BY Date) AS ‘Last_Value’ |
| 9. | FROM Orders |
Аналитические оконные функции SQL
Они дают возможность вернуть информацию о распределении данных. Их применяют для проведения статистического анализа.
- CUME_DIST – определяет интегральное распределение (относительное положение) значений в окне.
- PERCENT_RANK – рассчитывает относительный ранг строки в окне.
- PERCENTILE_CONT – указывает процентиль (параметр, который требуется вычислить) на основе постоянного распределения значения столбца.
- PERCENTILE_DISC – рассчитывает определенный процентиль для отсортированных значений в наборе данных.
Стоит учесть, что у функций PERCENTILE_CONT и PERCENTILE_DISC столбец, который будет использоваться для сортировки, обозначается ключевым словом WITHIN GROUP.
Пример SQL-запросов для применения аналитических оконных функций:
| 1. | SELECT |
| 2. | Date |
| 3. | , Medium |
| 4. | , Conversions |
| 5. | , CUME_DIST() OVER(PARTITION BY Date ORDER BY Conversions) AS ‘Cume_Dist’ |
| 6. | , PERCENT_RANK() OVER(PARTITION BY Date ORDER BY Conversions) AS ‘Percent_Rank’ |
| 7. | , PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Conversions) OVER(PARTITION BY Date) AS ‘Percentile_Cont’ |
| 8. | , PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Conversions) OVER(PARTITION BY Date) AS ‘Percentile_Disc’ |
| 9. | FROM Orders |
Операторы SQL
Между оконными функциями и GROUP BY есть большая разница. Первые не снижают число строк. Оконные функции возвращают ровно то количество строк, которые они получили на вход. Еще одно отличие – они могут вычислять скользящие средние и кумулятивные суммы. Кроме того, OVER способен обращаться к другим строкам.
%save-sc0%
Но что если пользователь планирует применять подзапросы или JOIN? Стоит учесть, что JOIN по производительности лучше подзапросов. При этом может показаться, что по данному показателю JOIN и OVER одинаковы. Однако первый оператор является более строгим, чем второй. Плюс ко всему, итоговый объем кода при использовании JOIN будет намного больше.
ROW_NUMBER и ORDER BY
Чтобы создать оконную функцию, необходимо применить оператор OVER. Рассмотрим пример на ROW_NUMBER. При помощи нее можно присвоить номер каждой выбранной записи:
SELECT
athlete,
event,
ROW_NUMBER() OVER() AS row_number
FROM Summer_Medals
ORDER BY row_number ASC;
Таким образом, у каждой пары «спортсмен – вид спорта» появился свой номер. Чтобы обратиться к этим номерам, можно воспользоваться командой row_number.
У пользователя есть возможность объединить ROW_NUMBER с ORDER BY. Это позволит узнать, какой порядок нумерации строк будет применяться. Для наглядности выберем все имеющиеся виды спорта при помощи DISTINCT, а затем пронумеруем их в алфавитном порядке:
SELECT
sport,
ROW_NUMBER() OVER(ORDER BY sport ASC) AS Row_N
FROM (
SELECT DISTINCT sport
FROM Summer_Medals
) AS sports
ORDER BY sport ASC;
PARTITION BY и LAG, LEAD и RANK
С помощью PARTITION BY можно группировать строки по значению определенного столбца. Эта функция помогает выполнить то или иное действие со строкой относительно других строк из той же группы, при условии, что данные делятся на несколько категорий. К примеру, пользователю нужно сопоставить информацию об одном футболисте с остальными футболистами, но не с баскетболистами или тяжелоатлетами.
Стоит отметить, что оператор PARTITION BY работает лишь с оконными функциями типа LAG, LEAD, RANK и тому подобными.
LAG
При ее использовании программа берет строку и возвращает предыдущую. К примеру, стоит задача отыскать все олимпийских чемпионов по теннису (женщин и мужчин по отдельности) с 2004 года. При этом для каждого из спортсменов необходимо указать, кто являлся предыдущим чемпионом.
Пользователю потребуется выполнить несколько действий. Во-первых, нужно сформировать табличное выражение, чтобы сохранить результат запроса «чемпионы по теннису с 2004 года» в качестве временной именованной структуры для последующего анализа. Далее необходимо разделить их по полу и подобрать предыдущего чемпиона. Для этого нужно задействовать функцию LAG:
— Табличное значение выполняет поиск чемпионов и подбирает подходящие столбцы.
WITH Tennis_Gold AS (
SELECT
Athlete,
Gender,
Year,
Country
FROM
Summer_Medals
WHERE
Year >= 2004 AND
Sport = ‘Tennis’ AND
event = ‘Singles’ AND
Medal = ‘Gold’)
— С помощью оконной функции спортсмены разделяются по полу. При этом указывается победитель, данные о котором находятся в предыдущей строке.
SELECT
Athlete as Champion,
Gender,
Year,
LAG(Athlete) OVER (PARTITION BY gender
ORDER BY Year ASC) AS Last_Champion
FROM Tennis_Gold
ORDER BY Gender ASC, Year ASC;
Таким образом, с помощью функции PARTITION BY мы вернули сначала всех мужчин, а затем и всех женщин. Победители 2008 и 2012 годов указываются вместе с предыдущими чемпионами. При этом данные есть лишь за три олимпиады, так что у победителей 2004 года нет предшественников. Следовательно, в этих полях указывается null.
LEAD
Данная функция схожа с LAG. Отличие заключается в том, что вместо предыдущей строки возвращается следующая. Иными словами, пользователь может выяснить, кто именно стал следующим победителем олимпийских игр после определенного спортсмена.
WITH Tennis_Gold AS (
SELECT
Athlete,
Gender,
Year,
Country
FROM
Summer_Medals
WHERE
Year >= 2004 AND
Sport = ‘Tennis’ AND
event = ‘Singles’ AND
Medal = ‘Gold’)
— С помощью оконной функции спортсмены разделяются по полу. После этого указывается победитель из следующей строки.
SELECT
Athlete as Champion,
Gender,
Year,
LEAD(Athlete) OVER (PARTITION BY gender
ORDER BY Year ASC) AS Future_Champion
FROM Tennis_Gold
ORDER BY Gender ASC, Year ASC;
RANK
Эта команда схожа с ROW_NUMBER. Она присваивает одинаковые номера строкам с тождественными значениями. При этом «лишние» номера пропускаются.
Также существует DENSE_RANK, который не пропускает номера. Для лучшего понимания рассмотрим конкретный пример. Выполним ранжирование стран по количеству чемпионатов, в которых они принимали участие. При этом воспользуемся разными операторами:
- Row_number – в этом случае строки будут просто пронумерованы по возрастанию.
- Rank_number – все то же самое, но не будет номера 3, вместо которого две строки разделят номер 2, а после них сразу выставится номер 4.
- Dense_rank – в этом случае все будет выглядеть так же, как и при использовании rank_number, но программа не пропустит номер 3. Все номера будут идти подряд. Однако никто не окажется пятым из пяти.
Теперь приведем пример кода:
— Табличное выражение подберет нужные страны и определит годы.
WITH countries AS (
SELECT
Country,
COUNT(DISTINCT year) AS participated
FROM
Summer_Medals
WHERE
Country in (‘GBR’, ‘DEN’, ‘FRA’, ‘ITA’,’AUT’)
GROUP BY
Country)
— С помощью разных функций проранжируем страны:
SELECT
Country,
participated,
ROW_NUMBER()
OVER(ORDER BY participated DESC) AS Row_Number,
RANK()
OVER(ORDER BY participated DESC) AS Rank_Number,
DENSE_RANK()
OVER(ORDER BY participated DESC) AS Dense_Rank
FROM countries
ORDER BY participated DESC;
Часто задаваемые вопросы об оконных функциях SQL
Что представляет собой SQL?
Это структурированный язык запросов. Он помогает работать с базами данных и получать из них нужные сведения. Пользователь составляет запрос в БД, после чего система выдает требуемый результат.
Вся информация содержится в таблицах. При этом данные структурированы и разложены по строкам и столбцам. Это упрощает процесс управления информацией.
Зачем изучать SQL?
Он представляет собой один из важнейших инструментов для работы с базами данных. С его помощью специалист может найти нужную информацию для последующего анализа и обработки. Этот язык программирования изучается многими аналитиками, финансистами, экономистами, бухгалтерами и товароведами.
Что собой представляет SQL Server?
Это программа, которая применяется для хранения и обработки информации. С ее помощью можно отправлять запросы и получать ответы как локально, так и по сети. В SQL Server применяются оконные функции, рассмотренные выше. Программа открывает сетевой порт, принимает команды пользователя и выдает результат.
Чтобы работать по локальной сети, система управления базой данных должна быть установлена на ПК пользователя. Режим работы зависит от применяемой СУБД.
Для чего нужны оконные функции SQL?
Они помогают пользователям работать как с агрегатными, так и неагрегатными значениями одновременно. Строки не сворачиваются, что очень удобно. Кроме того, оконные функции позволяют снизить нагрузку на систему (к примеру, пользователю не потребуется выполнять самосоединение или декартово произведение).
Какую литературу стоит изучить по данной теме?
Отметим книгу Ицика Бен-Гана «Оконные функции в T-SQL». Автор является экспертом по оптимизации запросов на языке T-SQL. Он приводит массу примеров, которые помогут начинающему специалисту разобраться со всеми разновидностями оконных функций.
Кому будет полезна книга? В первую очередь она подойдет разработчикам, администраторам СУБД, аналитикам данных и специалистам в сфере бизнес-аналитики. Ицик Бен-Ган по большей части пишет об оптимизации ОФ. В книге есть много полезной информации о решениях готовых бизнес-задач с применением современных техник. «Оконные функции в T-SQL» можно изучать для работы с версиями SQL Server вплоть до 2019, а также для Azure SQL Database.
Таким образом, оконные функции SQL представляют собой полезный инструмент, который применяется для анализа информации и работы с результатами запросов. С их помощью можно проводить различные расчеты и агрегацию внутри групп строк. Это увеличивает эффективность обработки больших данных. Кроме того, ОФ облегчают процесс написания сложных запросов. Пользователь может задействовать различные окна и сортировки, чтобы уточнить результаты.
