В този урок ще научим за работата на клаузата PARTITION BY в SQL и ще открием как можем да я използваме за разделяне на данните за по-подробно подмножество.
Синтаксис:
Нека започнем със синтаксиса на клаузата PARTITION BY. Синтаксисът може да зависи от контекста, в който го използвате, но ето общият синтаксис:
ИЗБЕРЕТЕ колона1, колона2, ...
НАД (ДЯЛ ПО partition_column1, partition_column2, ...)
ОТ име_таблица
Даденият синтаксис представлява следните елементи:
- колона1, колона2 – Това се отнася за колоните, които искаме да включим в набора от резултати.
- PARTITION BY колони – Тази клауза определя как искаме да разделим или групираме данните.
Примерни данни
Нека създадем основна таблица с примерни данни, за да демонстрираме как да използваме клаузата PARTITION BY. За този пример нека създадем основна таблица, която съхранява информацията за продукта.
CREATE TABLE продукти (
product_id INT PRIMARY KEY AUTO_INCREMENT,
име на продукт VARCHAR( 255 ),
категория VARCHAR( 255 ),
цена DECIMAL( 10 , 2 ),
количество INT,
дата на изтичане DATE,
баркод BIGINT
);
вмъкнете
в
продукти (име_на_продукт,
категория,
цена,
количество,
срок на годност,
баркод)
стойности ( 'Шапка за готвач 25см' ,
'пекарна' ,
24,67 ,
57 ,
„2023-09-09“ ,
2854509564204 );
вмъкнете
в
продукти (име_на_продукт,
категория,
цена,
количество,
срок на годност,
баркод)
стойности ( „Пъдпъдъчи яйца – консерва“ ,
'килер' ,
17,99 ,
67 ,
„29 септември 2023 г.“ ,
1708039594250 );
вмъкнете
в
продукти (име_на_продукт,
категория,
цена,
количество,
срок на годност,
баркод)
стойности ( „Кафе - Egg Nog Capuccino“ ,
'пекарна' ,
92,53 ,
10 ,
„22 септември 2023 г.“ ,
8704051853058 );
вмъкнете
в
продукти (име_на_продукт,
категория,
цена,
количество,
срок на годност,
баркод)
стойности ( 'Круша - Бодлива' ,
'пекарна' ,
65,29 ,
48 ,
„23 август 2023 г.“ ,
5174927442238 );
вмъкнете
в
продукти (име_на_продукт,
категория,
цена,
количество,
срок на годност,
баркод)
стойности ( „Паста – ангелска коса“ ,
'килер' ,
48,38 ,
59 ,
'2023-08-05' ,
8008123704782 );
вмъкнете
в
продукти (име_на_продукт,
категория,
цена,
количество,
срок на годност,
баркод)
стойности ( „Вино – Просеко Валдобиадене“ ,
'произвеждам' ,
44.18 ,
3 ,
„13 март 2023 г.“ ,
6470981735653 );
вмъкнете
в
продукти (име_на_продукт,
категория,
цена,
количество,
срок на годност,
баркод)
стойности ( 'Сладкиши - френски мини асорти' ,
'килер' ,
36,73 ,
52 ,
'2023-05-29' ,
5963886298051 );
вмъкнете
в
продукти (име_на_продукт,
категория,
цена,
количество,
срок на годност,
баркод)
стойности ( „Портокал – консерва, мандарина“ ,
'произвеждам' ,
65,0 ,
1 ,
'20.04.2023' ,
6131761721332 );
вмъкнете
в
продукти (име_на_продукт,
категория,
цена,
количество,
срок на годност,
баркод)
стойности ( 'Свинско рамо' ,
'произвеждам' ,
55,55 ,
73 ,
'2023-05-01' ,
9343592107125 );
вмъкнете
в
продукти (име_на_продукт,
категория,
цена,
количество,
срок на годност,
баркод)
стойности ( „Dc Hikiage Hira Huba“ ,
'произвеждам' ,
56.29 ,
53 ,
„14.04.2023“ ,
3354910667072 );
След като имаме настройката на примерните данни, можем да продължим и да използваме клаузата PARTITION BY.
Основна употреба
Да предположим, че искаме да изчислим общите артикули за всяка продуктова категория в предишната таблица. Можем да използваме PARTITION BY, за да разделим артикулите в уникални категории и след това да определим общото количество във всяка категория.
Пример е следният:
ИЗБЕРЕТЕ
Име на продукта,
категория,
количество,
SUM(количество) НАД (ДЯЛ ПО категория) КАТО total_items
ОТ
продукти;
Забележете, че в дадения пример ние разделяме данните с помощта на колоната „категория“. След това използваме обобщената функция SUM(), за да определим общите елементи във всяка категория поотделно. Резултатът показва общия брой елементи във всяка категория.
Използване на клаузата PARTITION BY
За да обобщим, най-честият случай на използване на клаузата PARTITION BY е във връзка с функциите на прозореца. Функцията прозорец се прилага към всеки дял поотделно.
Някои от обичайните функции на прозореца, които да използвате с PARTITION BY, включват следното:
- SUM() – Изчислете сумата на колона във всеки дял.
- AVG() – Изчислете средната стойност на колона във всеки дял.
- COUNT() – Преброяване на броя на редовете във всеки дял.
- ROW_NUMBER() – Присвояване на уникален номер на ред на всеки ред във всеки дял.
- RANK() – Присвояване на ранг на всеки ред във всеки дял.
- DENSE_RANK() – Присвояване на плътен ранг на всеки ред във всеки дял.
- NTILE() – Разделете данните на квантили във всеки дял.
Това е!
Заключение
В този урок научихме как да работим с клаузата PARTITION BY в SQL, за да разделим данните на различни сегменти и след това да приложим специфична операция към всеки от получените дялове поотделно.