Чек-листы по В2В маркетингу увеличивающие продажи на 40%
Icon PDF 7,4 МБ
Скачать бесплатно

Оконные функции SQL: что это и зачем они нужны

23.10.2023
1610
Время чтения:
Обновлено: 23.10.2023
оконные функции SQL

Что это? Оконные функции SQL – инструмент, позволяющий упростить и повысить эффективность сложных запросов. Выполняют вычисления для нескольких строк данных, не прибегая к написанию подзапросов или объединению нескольких таблиц.

Где используются? Оконные функции полезны при работе с агрегатами и аналитическими запросами. Они позволяют вычислять текущие итоги, средние значения, рейтинги и другие показатели на основе данных.

Что такое оконная функция

В SQL она представляет собой набор результатов. Это данные, которые возвращаются из запроса. Иными словами, это таблица выполнения кода инструкции select.

Термин состоит из двух слов. Рассмотрим значение каждого из них.

Окно в SQL – это набор строк или наблюдений в таблице или результирующем наборе. В одной таблице может содержаться более одного окна (в зависимости от формулировки запроса). Для его определения используется OVER() предложение.

Функция в SQL применяется для выполнения той или иной операции с данными. Они предопределены. Функции помогают агрегировать информацию, форматировать строки, извлекать даты и так далее. Таким образом, функции Windows представляют собой функции SQL, с помощью которых можно выполнять различные операции с окном (набором записей).

что такое оконная функция

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

Виды функций

Перед тем как изучить синтаксис оконных функций SQL, необходимо рассмотреть их основные группы:

  • агрегатные функции;
  • ранжирующие функции;
  • функции смещения;
  • аналитические функции.

В рамках одной инструкции SELECT с одним предложением FROM можно задействовать целый ряд ОФ. Проанализируем каждую группу и выделим их ключевые особенности.

Агрегатные оконные функции SQL

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

  • SUM – эта оконная функция SQL определяет сумму значений в столбце;
  • COUNT – рассчитывает количество значений в столбце (без учета значений NULL);
  • AVG – рассчитывает среднее значение в столбце;
  • MAX – определяет наибольшее значение в столбце;
  • MIN – указывает наименьшее значение в столбце.
До 26 апреля
23 A/B теста для B2B, которые повысили конверсию сайта в 4 раза
    Дополнительно:
  • 10 критических ошибок В2В маркетинга
  • 5 полезных инструментов для В2В
Background image Background image

Рассмотрим вариант применения агрегирующих оконных функций 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

Пример 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;

ROW_NUMBER и ORDER BY

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 Server

Чтобы работать по локальной сети, система управления базой данных должна быть установлена на ПК пользователя. Режим работы зависит от применяемой СУБД.

Для чего нужны оконные функции SQL?

Они помогают пользователям работать как с агрегатными, так и неагрегатными значениями одновременно. Строки не сворачиваются, что очень удобно. Кроме того, оконные функции позволяют снизить нагрузку на систему (к примеру, пользователю не потребуется выполнять самосоединение или декартово произведение).

Какую литературу стоит изучить по данной теме?

Отметим книгу Ицика Бен-Гана «Оконные функции в T-SQL». Автор является экспертом по оптимизации запросов на языке T-SQL. Он приводит массу примеров, которые помогут начинающему специалисту разобраться со всеми разновидностями оконных функций.

Кому будет полезна книга? В первую очередь она подойдет разработчикам, администраторам СУБД, аналитикам данных и специалистам в сфере бизнес-аналитики. Ицик Бен-Ган по большей части пишет об оптимизации ОФ. В книге есть много полезной информации о решениях готовых бизнес-задач с применением современных техник. «Оконные функции в T-SQL» можно изучать для работы с версиями SQL Server вплоть до 2019, а также для Azure SQL Database.

какую литературу стоит изучить по данной теме

Таким образом, оконные функции SQL представляют собой полезный инструмент, который применяется для анализа информации и работы с результатами запросов. С их помощью можно проводить различные расчеты и агрегацию внутри групп строк. Это увеличивает эффективность обработки больших данных. Кроме того, ОФ облегчают процесс написания сложных запросов. Пользователь может задействовать различные окна и сортировки, чтобы уточнить результаты.

Оцените статью
Рейтинг:
( голосов )
Поделиться статьей
ТОП-8 кейсов для роста лидов на 30%
Скачать материалы