Анатомия оконных функций SQL

Для того, чтобы понять данные предприятия; вы должны запросить его много. Когда я говорю «много», я имею в виду именно это. Работа с незнакомыми грудами данных часто утомительна, и всегда полезно уделить некоторое время изучению и пониманию самих данных. Хорошо иметь базовые навыки поиска данных, но знание аналитических функций для извлечения полезной информации из ваших данных — это вишенка на торте, и это тоже весело!

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

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

Как обычно, для демонстрации мы будем использовать образец базы данных MySQL classicmodels , в которой хранятся бизнес-данные продавца автомобилей. Ниже приведена диаграмма ER для справки,

Прежде всего, что такое оконная функция?

Определение оконной функции в учебнике:

Оконная функция выполняет вычисления для набора строк таблицы, которые так или иначе связаны с текущей строкой.

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

Какая необходимость? Почему оконная функция? В чем отстает агрегатная функция?

Вот примеры данных из таблицы PRODUCTS , для демонстрационных целей я ограничил их PRODUCTLINES-Planes , Ships и Trains .

--sample data from table PRODUCTS.
SELECT
    *
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                

                    
                    
                
            

--total quantity in stock for each productline
SELECT 
    PRODUCTLINE,
    SUM(QUANTITYINSTOCK) AS TOTAL_QUNATITY
FROM 
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains')
GROUP BY PRODUCTLINE;

      
                

                    
                    
                
            

Давайте изменим требование сейчас,

1. Отображение количества каждого продукта в ЛИНИИ ПРОДУКТОВ вместе с общим количеством на складе для этой конкретной ЛИНИИ ПРОДУКТОВ .
2. Сгруппируйте набор результатов по PRODUCTLINE.

--sum() as a window function
SELECT 
    PRODUCTNAME,
    PRODUCTLINE,
    QUANTITYINSTOCK,
    SUM(QUANTITYINSTOCK) OVER (PARTITION BY PRODUCTLINE) AS TOTAL_QUANTITY
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                

                    
                    
                
            

Типы оконных функций

Честно говоря, официальной классификации оконных функций не существует, но в зависимости от использования мы можем кратко разделить их на три категории:

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

Общий синтаксис оконной функции:

Прежде чем мы углубимся в это; давайте сначала поймем значение каждого пункта в нем,

Предложение OVER()

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

Продолжая приведенный выше пример,

--empty OVER() clause
SELECT 
    PRODUCTNAME,
    PRODUCTLINE,
    QUANTITYINSTOCK,
    SUM(QUANTITYINSTOCK) OVER () AS TOTAL_QUANTITY
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                

                    
                    
                
            

РАЗДЕЛ ПО ПУНКТУ

PARTITION BY используется с предложением OVER . Он делит набор результатов запроса на разделы или сегменты на основе указанного пользователем выражения, а затем функция окна применяется к каждому разделу или сегменту.

Это необязательно, поэтому, если вы не укажете предложение PARTITION BY , функция будет рассматривать все строки как один раздел. Именно то, что мы сделали в приведенном выше примере, мы просто указали пустое предложение OVER() без предложения PARTITION BY , и, следовательно, общее количество было рассчитано для всех PRODUCTLINE s.

Что произойдет, если мы укажем один,

--OVER() with PARTITION BY
SELECT 
    PRODUCTNAME,
    PRODUCTLINE,
    QUANTITYINSTOCK,
    SUM(QUANTITYINSTOCK) OVER (PARTITION BY PRODUCTLINE) AS TOTAL_QUANTITY
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                

                    
                    
                
            

А как обстоят дела с предложениями PARTITION BY и GROUP BY ? Они похожи или разные?

ГРУППА ПО Предложение,

• Он группирует несколько строк в сводные строки на основе одного или нескольких столбцов/выражений (возвращая 1 строку для каждой группы). Проще говоря, это уменьшает количество строк в вашем наборе результатов.
• Он используется вместе с агрегатными функциями, такими как SUM(), AVG(), MAX() и т. д.
• Он помещается после предложения WHERE , но перед предложениями HAVING, ORDER BY . Общий синтаксис,

• Он используется с предложением OVER() в оконной функции. Он делит набор результатов запроса на разделы, а затем к каждому разделу применяется оконная функция.
• PARTITION BY похож на GROUP BY , так как объединяет результат на основе выражения; однако основное отличие состоит в том, что это не уменьшит количество строк результирующего набора.
• Это необязательно, поэтому, если вы не укажете предложение PARTITION BY , функция будет рассматривать все строки как один раздел.
• Общий синтаксис,

ЗАКАЗАТЬ

Он используется для сортировки набора результатов в порядке возрастания или убывания в каждом разделе набора результатов. По умолчанию в порядке возрастания.

Предложение ROWS/RANGE

Теперь мы уже знаем, что ключевой особенностью оконной функции является создание окна или раздела результирующего набора с помощью предложения PARTITION BY , а затем выполнение вычислений для каждого раздела. Что, если мы в дальнейшем захотим создать подмножества внутри этих разделов? Вау! раздел внутри раздела? Да, именно поэтому у нас есть предложение FRAME .

Предложение FRAME дополнительно определяет подмножество текущего раздела. Он использует ROW или RANGE для определения начальной и конечной точек этого подмножества. Для этого требуется предложение ORDER BY .

Фреймы определяются относительно текущей строки, что просто означает, что вы берете местоположение текущей строки в качестве базовой точки и с помощью этой ссылки определяете свой фрейм в разделе.

• ROWS — определяет начало и конец кадра, указывая количество строк, которые предшествуют или следуют за текущей строкой.
• RANGE — В отличие от ROWS , RANGE указывает диапазон значений по сравнению со значением текущей строки для определения кадра в разделе.

{СТРОКИ | ДИАПАЗОН} МЕЖДУ <frame_starting> И <frame_end>

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

• UNBOUNDED PRECEDING — указывает все строки (начиная с первой строки) перед текущей строкой в ​​разделе.
• N ПРЕДШЕСТВУЮЩАЯ — указывает количество строк N перед вашей текущей строкой в ​​разделе.
• UNBOUNDED FOLLOWING — указывает все строки после текущей строки (вплоть до самой последней строки) в разделе.
• M FOLLOWING — это указывает количество строк «M» ниже вашей текущей строки в разделе.

SELECT 
    PRODUCTNAME,
    PRODUCTLINE,
    QUANTITYINSTOCK,
    SUM(QUANTITYINSTOCK) OVER (PARTITION BY PRODUCTLINE ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS TOTAL
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                

                    
                    
                
            

Вот несколько примеров предложений FRAME ,

• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — это означает, что рассмотрим кадр от первой строки раздела до последней строки раздела.
• РЯДЫ МЕЖДУ НЕОГРАНИЧЕННЫМИ ПРЕДЫДУЩИМИ И 4 ПОСЛЕДУЮЩИМИ - Это означает, что рассмотрим кадр от первой строки раздела до 4 строки после текущей строки.
• РЯДЫ МЕЖДУ 4 ПРЕДШЕСТВУЮЩИМИ И 1 ПРЕДШЕСТВУЮЩИМ — кадром будут предыдущие 4 ряда до 1 ряда перед текущей строкой.

{ROWS/RANGE} МЕЖДУ ПРЕДЫДУЩЕЙ И ТЕКУЩЕЙ СТРОКАМИ НЕОГРАНИЧЕННЫМИ

Это означает, что кадр рассматривается как все строки, начиная с строки номер один и заканчивая текущей строкой в ​​разделе.

Без предложения ORDER BY кадр по умолчанию выглядит так:

{СТРОКИ/ДИАПАЗОН} МЕЖДУ НЕОГРАНИЧЕННЫМИ ПРЕДЫДУЩИМИ И НЕОГРАНИЧЕННЫМИ СЛЕДУЮЩИМИ

Это просто означает весь раздел.

Определение псевдонима окна,

Если в вашем запросе есть более одной оконной функции, использующей одно и то же окно, вы можете использовать псевдоним окна.

--finding out minimum and maximum MSRP for each productline
SELECT
    PRODUCTNAME,
    PRODUCTLINE,
    MSRP,
    MIN(MSRP) OVER(PARTITION BY PRODUCTLINE) AS MIN_MSRP,
    MAX(MSRP) OVER(PARTITION BY PRODUCTLINE) AS MAX_MSRP
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

--using window alias
SELECT
    PRODUCTNAME,
    PRODUCTLINE,
    MSRP,
    MIN(MSRP) OVER MSRP_WINDOW AS MIN_MSRP,
    MAX(MSRP) OVER MSRP_WINDOW AS MAX_MSRP
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains')
WINDOW MSRP_WINDOW AS (PARTITION BY PRODUCTLINE);

Во время выполнения запроса оконные функции выполняются над набором результатов,

• После предложений JOIN , WHERE , GROUP BY и HAVING и
• Перед предложением ORDER BY LIMIT и SELECT DISTINCT .

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

• Памятка по оконным функциям SQL
• Рамочная оговорка
• HackerRank или LeetCode для решения базовых/средних/продвинутых задач SQL.