Введение в оконные функции SQL

Очень мощная функция, которую вы любите ненавидеть (но должны знать)

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

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

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

Окно в ваши данные

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

Вы когда-нибудь хотели просмотреть результаты своего запроса, чтобы получить рейтинг, список лучших x или что-то подобное? Были ли у вас какие-либо аналитические проекты, в которых вы хотели подготовить данные как раз для инструмента визуализации, но сочли это почти невозможным или настолько сложным, что оно того не стоило?

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

Вот некоторые из оконных функций, которые мы собираемся рассмотреть:

• OVER
• COUNT()
• SUM()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LEAD()
• LAG()

Более легкий

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

 SELECT COUNT(*) OVER() As NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Вышеупомянутое, как и многие оконные функции, также может быть написано более привычным, не оконным способом, что в этом простом примере не так уж и плохо:

 SELECT (SELECT COUNT(*) FROM Employee) as NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Но теперь предположим, что мы хотим показать количество сотрудников, которые начали работу в том же месяце, что и сотрудник в строке. Нам нужно будет сузить или ограничить счет только этим месяцем для каждой строки. Как это делается? Мы используем предложение окна PARTITION , например:

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started),YEAR(date_started)) As NumPerMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname FROM Employee ORDER BY date_started;

Разделы позволяют фильтровать окно по разделам по определенному значению или значениям. Каждую секцию часто называют оконной рамой .

Чтобы продолжить, предположим, что мы не только хотим узнать, сколько сотрудников приступили к работе в том же месяце, но и хотим показать, в каком порядке они приступили к работе в этом месяце. Для этого мы можем использовать знакомое предложение ORDER BY . Однако внутри оконной функции ORDER BY действует несколько иначе, чем в конце запроса.

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started), YEAR(date_started) ORDER BY date_started) As NumThisMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

В этом случае ORDER BY изменяет окно таким образом, чтобы оно переходило от начала раздела (в данном случае это месяц и год начала работы сотрудника) к текущей строке. Таким образом, счет перезапускается в каждом разделе.

Ранжируйте это

Оконные функции могут быть очень полезны для целей ранжирования. Ранее мы видели, что использование функции агрегирования COUNT позволило нам увидеть, в каком порядке сотрудники присоединялись к компании. Мы также могли бы использовать функции ранжирования окон, такие как ROW_NUMBER() , RANK() и DENSE_RANK() .

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

 SELECT ROW_NUMBER() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As StartingRank, RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As EmployeeRank, DENSE_RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As DenseRank, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Вы можете увидеть различия. ROW_NUMBER() дает последовательный подсчет внутри данного раздела (но при отсутствии раздела он проходит через все строки). RANK() дает ранг каждой строки на основе предложения ORDER BY . Он показывает ничьи, а затем пропускает следующий рейтинг. DENSE_RANK также показывает совпадения, но затем переходит к следующему последовательному значению, как если бы совпадений не было.

Другие функции ранжирования включают в себя:

• CUME_DIST — вычисляет относительный ранг текущей строки в разделе.
• NTILE — разделяет строки для каждого раздела окна как можно равномернее.
• PERCENT_RANK — ранг текущей строки в процентах.

Обратите также внимание на то, что в этом примере вы можете иметь несколько функций Window в одном запросе, и в каждом из них может быть разный раздел и порядок!

Ряды, диапазоны и рамки, о боже!

Чтобы дополнительно определить или ограничить рамку окна в предложении OVER() , вы можете использовать ROWS и RANGE . С помощью предложения ROWS вы можете указать строки, включенные в ваш раздел, как предшествующие или следующие за текущей строкой.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

В этом примере рамка окна переходит от первой строки к текущей строке минус 1, и размер окна продолжает увеличиваться для каждой строки.

Диапазон работает немного по-другому, и мы можем получить другой результат.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

Диапазон будет включать те строки в рамке окна, которые имеют те же значения ORDER BY , что и текущая строка. Таким образом, возможно, что вы можете получить дубликаты с помощью RANGE , если ORDER BY не уникален.

Некоторые описывают ROWS как физический оператор, а RANGE — как логический оператор. В любом случае значения по умолчанию для ROWS и RANGE всегда UNBOUNDED PRECEDING AND CURRENT ROW .

Что еще?

Большинство стандартных агрегатных функций работают с оконными функциями. Мы уже видели COUNT в примерах. Другие включают SUM , AVG , MIN , MAX и т. д.

С помощью оконных функций вы также можете получить доступ как к предыдущим записям, так и к последующим записям, используя LAG и LEAD , а также FIRST_VALUE и LAST_VALUE . Например, предположим, что вы хотите отобразить в каждой строке данные о продажах за текущий месяц и разницу между цифрами продаж за последний месяц. Вы можете сделать что-то вроде этого:

 SELECT id, OrderMonth, OrderYear, product, sales, sales - LAG(sales,1) OVER (PARTITION BY product ORDER BY OrderYear, OrderMonth) As sales_change FROM sales_products WHERE sale_year = 2019;

По сути, оконные функции SQL очень эффективны

Хотя это краткое введение в оконные функции SQL, надеюсь, вам будет интересно увидеть все, на что они способны. Мы узнали, что оконные функции выполняют вычисления, аналогичные функциям агрегации, но с дополнительным преимуществом, заключающимся в том, что они имеют доступ к данным в отдельных строках, что делает их весьма мощными. Они всегда содержат предложение OVER и могут содержать PARTITION BY , ORDER BY и множество агрегирующих ( SUM , COUNT и т. д.) и других позиционных функций ( LEAD , LAG ). Мы также узнали о оконных фреймах и о том, как они инкапсулируют разделы данных.

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

Если вы как разработчик SQL заинтересованы в настройке производительности базы данных SQL, ознакомьтесь с разделом Настройка производительности базы данных SQL для разработчиков .

Удачного оконного дела!

Для получения дополнительной информации о конкретных реализациях см.

• Документация по оконным функциям PostgreSQL для реализации PostgreSQL.
• SELECT - OVER Clause (Transact-SQL) docs от Microsoft.
• Оконные функции в SQL Server для отличного обзора реализаций SQL Server и его части 2.