In deze zelfstudie leren we over de werking van de PARTITION BY-clausule in SQL en ontdekken we hoe we deze kunnen gebruiken om de gegevens te partitioneren voor een meer gedetailleerde subset.
Syntaxis:
Laten we beginnen met de syntaxis van de PARTITION BY-clausule. De syntaxis kan afhangen van de context waarin u deze gebruikt, maar hier is de algemene syntaxis:
SELECTEER kolom1, kolom2, ...
OVER (PARTITIE DOOR partitie_kolom1, partitie_kolom2, ...)
FROM tabelnaam
De gegeven syntaxis vertegenwoordigt de volgende elementen:
- kolom1, kolom2 – Dit verwijst naar de kolommen die we in de resultatenset willen opnemen.
- PARTITION BY kolommen – Deze clausule definieert hoe we de gegevens willen partitioneren of groeperen.
Voorbeeldgegevens
Laten we een basistabel maken met voorbeeldgegevens om te demonstreren hoe u de PARTITION BY-clausule gebruikt. Laten we voor dit voorbeeld een basistabel maken waarin de productinformatie wordt opgeslagen.
MAAK TAFEL producten (
product_id INT PRIMAIRE SLEUTEL AUTO_INCREMENT,
productnaam VARCHAR( 255 ),
categorie VARCHAR( 255 ),
prijs DECIMAAL( 10 , 2 ),
hoeveelheid INT,
vervaldatum DATUM,
streepjescode BIGINT
);
invoegen
naar binnen
producten (productnaam,
categorie,
prijs,
hoeveelheid,
uiterste houdbaarheidsdatum,
streepjescode)
waarden ( 'Koksmuts 25cm' ,
'bakkerij' ,
24.67 ,
57 ,
'09-09-2023' ,
2854509564204 );
invoegen
naar binnen
producten (productnaam,
categorie,
prijs,
hoeveelheid,
uiterste houdbaarheidsdatum,
streepjescode)
waarden ( 'Kwarteleitjes - uit blik' ,
'voorraadkast' ,
17,99 ,
67 ,
'29-09-2023' ,
1708039594250 );
invoegen
naar binnen
producten (productnaam,
categorie,
prijs,
hoeveelheid,
uiterste houdbaarheidsdatum,
streepjescode)
waarden ( 'Koffie - Egg Nog Capuccino' ,
'bakkerij' ,
92.53 ,
10 ,
'22-09-2023' ,
8704051853058 );
invoegen
naar binnen
producten (productnaam,
categorie,
prijs,
hoeveelheid,
uiterste houdbaarheidsdatum,
streepjescode)
waarden ( 'Peer - Stekelig' ,
'bakkerij' ,
65,29 ,
48 ,
'23-08-2023' ,
5174927442238 );
invoegen
naar binnen
producten (productnaam,
categorie,
prijs,
hoeveelheid,
uiterste houdbaarheidsdatum,
streepjescode)
waarden ( 'Pasta - Engelenhaar' ,
'voorraadkast' ,
48.38 ,
59 ,
'05-08-2023' ,
8008123704782 );
invoegen
naar binnen
producten (productnaam,
categorie,
prijs,
hoeveelheid,
uiterste houdbaarheidsdatum,
streepjescode)
waarden ( 'Wijn - Prosecco Valdobiaddene' ,
'produceren' ,
44.18 ,
3 ,
'2023-03-13' ,
6470981735653 );
invoegen
naar binnen
producten (productnaam,
categorie,
prijs,
hoeveelheid,
uiterste houdbaarheidsdatum,
streepjescode)
waarden ( 'Gebak - Frans Mini Assortiment' ,
'voorraadkast' ,
36.73 ,
52 ,
'29-05-2023' ,
5963886298051 );
invoegen
naar binnen
producten (productnaam,
categorie,
prijs,
hoeveelheid,
uiterste houdbaarheidsdatum,
streepjescode)
waarden ( 'Sinaasappel - Ingeblikt, Mandarijn' ,
'produceren' ,
65,0 ,
1 ,
'20-04-2023' ,
6131761721332 );
invoegen
naar binnen
producten (productnaam,
categorie,
prijs,
hoeveelheid,
uiterste houdbaarheidsdatum,
streepjescode)
waarden ( 'Varkensschouder' ,
'produceren' ,
55,55 ,
73 ,
'2023-05-01' ,
9343592107125 );
invoegen
naar binnen
producten (productnaam,
categorie,
prijs,
hoeveelheid,
uiterste houdbaarheidsdatum,
streepjescode)
waarden ( 'Dc Hikiage Hira Huba' ,
'produceren' ,
56.29 ,
53 ,
'2023-04-14' ,
3354910667072 );
Zodra we de voorbeeldgegevens hebben ingesteld, kunnen we doorgaan en de PARTITION BY-clausule gebruiken.
Basisgebruik
Stel dat we het totaal aantal artikelen voor elke productcategorie in de vorige tabel willen berekenen. We kunnen de PARTITIE DOOR gebruiken om de artikelen in unieke categorieën te verdelen en vervolgens het totaal van de hoeveelheid in elke categorie te bepalen.
Een voorbeeld is als volgt:
SELECTEER
productnaam,
categorie,
hoeveelheid,
SUM(hoeveelheid) OVER (VERDELING PER categorie) AS totaal_items
VAN
producten;
Merk op dat we in het gegeven voorbeeld de gegevens verdelen met behulp van de kolom 'categorie'. Vervolgens gebruiken we de aggregatiefunctie SUM() om het totale aantal items in elke categorie afzonderlijk te bepalen. Het resultaat toont het totaal aantal items in elke categorie.
Met behulp van de PARTITION BY-clausule
Samenvattend: het meest voorkomende gebruik van de PARTITION BY-clausule is in combinatie met de vensterfuncties. De vensterfunctie wordt op elke partitie afzonderlijk toegepast.
Enkele van de algemene vensterfuncties die u kunt gebruiken met PARTITION BY zijn onder meer:
- SUM() – Bereken de som van een kolom binnen elke partitie.
- AVG() – Bereken het gemiddelde van een kolom binnen elke partitie.
- COUNT() – Tel het aantal rijen binnen elke partitie.
- ROW_NUMBER() – Wijs een uniek rijnummer toe aan elke rij binnen elke partitie.
- RANK() – Wijs een rang toe aan elke rij binnen elke partitie.
- DENSE_RANK() – Wijs een dichte rang toe aan elke rij binnen elke partitie.
- NTILE() – Verdeel de gegevens in kwantielen binnen elke partitie.
Dat is het!
Conclusie
In deze zelfstudie hebben we geleerd hoe we met de PARTITION BY-clausule in SQL kunnen werken om de gegevens in verschillende segmenten te verdelen en vervolgens een specifieke bewerking op elk van de resulterende partities afzonderlijk toe te passen.