В этом руководстве мы узнаем о работе предложения PARTITION BY в SQL и узнаем, как мы можем использовать его для разделения данных для более детального подмножества.
Синтаксис:
Начнем с синтаксиса предложения PARTITION BY. Синтаксис может зависеть от контекста, в котором вы его используете, но вот общий синтаксис:
ВЫБРАТЬ столбец1, столбец2, ...
НАД (РАЗДЕЛ ПО разделу_столбец1, раздел_столбец2, ...)
ОТ имя_таблицы
Данный синтаксис представляет следующие элементы:
- столбец1, столбец2 — это относится к столбцам, которые мы хотим включить в набор результатов.
- Столбцы PARTITION BY. Этот пункт определяет, как мы хотим разделить или сгруппировать данные.
Образец данных
Давайте создадим базовую таблицу с образцом данных, чтобы продемонстрировать, как использовать предложение PARTITION BY. В этом примере давайте создадим базовую таблицу, в которой будет храниться информация о продукте.
СОЗДАТЬ ТАБЛИЦУ продуктов (
product_id INT ПЕРВИЧНЫЙ КЛЮЧ AUTO_INCREMENT,
имя_продукта VARCHAR( 255 ),
категория ВАРЧАР( 255 ),
цена ДЕСЯТИЧНАЯ( 10 , 2 ),
количество INT,
истечение_даты ДАТА,
штрих-код БИГИНТ
);
вставлять
в
продукты (имя_продукта,
категория,
цена,
количество,
Дата окончания срока,
штрих-код)
ценности ( 'Шляпа шеф-повара 25см' ,
'пекарня' ,
24.67 ,
57 ,
'2023-09-09' ,
2854509564204 );
вставлять
в
продукты (имя_продукта,
категория,
цена,
количество,
Дата окончания срока,
штрих-код)
ценности ( 'Яйца перепелиные - консервированные' ,
'кладовая' ,
17.99 ,
67 ,
'29 сентября 2023 г.' ,
1708039594250 );
вставлять
в
продукты (имя_продукта,
категория,
цена,
количество,
Дата окончания срока,
штрих-код)
ценности ( 'Кофе - Эгг Ног Капучино' ,
'пекарня' ,
92,53 ,
10 ,
'22 сентября 2023 г.' ,
8704051853058 );
вставлять
в
продукты (имя_продукта,
категория,
цена,
количество,
Дата окончания срока,
штрих-код)
ценности ( 'Груша - Колючая' ,
'пекарня' ,
65,29 ,
48 ,
'2023-08-23' ,
5174927442238 );
вставлять
в
продукты (имя_продукта,
категория,
цена,
количество,
Дата окончания срока,
штрих-код)
ценности ( 'Паста - Волосы ангела' ,
'кладовая' ,
48,38 ,
59 ,
'2023-08-05' ,
8008123704782 );
вставлять
в
продукты (имя_продукта,
категория,
цена,
количество,
Дата окончания срока,
штрих-код)
ценности ( 'Вино - Просекко Вальдобьядене' ,
'производить' ,
44,18 ,
3 ,
'2023-03-13' ,
6470981735653 );
вставлять
в
продукты (имя_продукта,
категория,
цена,
количество,
Дата окончания срока,
штрих-код)
ценности ( 'Выпечка - французское мини-ассорти' ,
'кладовая' ,
36,73 ,
52 ,
'2023-05-29' ,
5963886298051 );
вставлять
в
продукты (имя_продукта,
категория,
цена,
количество,
Дата окончания срока,
штрих-код)
ценности ( 'Апельсин - консервированный, Мандарин' ,
'производить' ,
65,0 ,
1 ,
'20 апреля 2023 г.' ,
6131761721332 );
вставлять
в
продукты (имя_продукта,
категория,
цена,
количество,
Дата окончания срока,
штрих-код)
ценности ( 'Свинина - лопатка' ,
'производить' ,
55,55 ,
73 ,
'2023-05-01' ,
9343592107125 );
вставлять
в
продукты (имя_продукта,
категория,
цена,
количество,
Дата окончания срока,
штрих-код)
ценности ( 'Ди-Си Хикиаге Хира Хуба' ,
'производить' ,
56,29 ,
53 ,
'2023-04-14' ,
3354910667072 );
После настройки образца данных мы можем продолжить и использовать предложение PARTITION BY.
Основное использование
Предположим, мы хотим рассчитать общее количество товаров для каждой категории продуктов в предыдущей таблице. Мы можем использовать PARTITION BY, чтобы разделить товары на уникальные категории, а затем определить общее количество в каждой категории.
Пример следующий:
ВЫБИРАТЬ
наименование товара,
категория,
количество,
СУММА (количество) БОЛЬШЕ (РАЗДЕЛЕНИЕ ПО категории) AS total_items
ОТ
продукты;
Обратите внимание, что в данном примере мы разделяем данные, используя столбец «Категория». Затем мы используем агрегатную функцию SUM(), чтобы определить общее количество элементов в каждой категории отдельно. Результат показывает общее количество элементов в каждой категории.
Использование предложения PARTITION BY
Подводя итог, наиболее распространенный вариант использования предложения PARTITION BY — в сочетании с оконными функциями. Функция окна применяется к каждому разделу отдельно.
Некоторые из общих оконных функций, используемых с PARTITION BY, включают следующее:
- SUM() – вычисляет сумму столбца в каждом разделе.
- AVG() – вычисляет среднее значение столбца в каждом разделе.
- COUNT() – подсчитывает количество строк в каждом разделе.
- ROW_NUMBER() — присвойте уникальный номер каждой строке в каждом разделе.
- RANK() – Присвойте ранг каждой строке в каждом разделе.
- DENSE_RANK() – присваивает плотный ранг каждой строке в каждом разделе.
- NTILE() – разделяет данные на квантили внутри каждого раздела.
Вот и все!
Заключение
В этом руководстве мы узнали, как работать с предложением PARTITION BY в SQL для разделения данных на различные сегменты, а затем применять определенную операцию к каждому из полученных разделов отдельно.