PostgreSQL GROUPING SETS

Samenvatting: in deze tutorial leert u over het grouping set concept en hoe u de PostgreSQL GROUPING SETS clausule kunt gebruiken om meerdere grouping sets in een query te genereren.

Een voorbeeldtabel

Laten we beginnen met het maken van een nieuwe tabel genaamdsales voor de demonstratie.

Code language: SQL (Structured Query Language) (sql)

In de tabel sales wordt het aantal verkochte producten per merk en segment opgeslagen.

Inleiding tot PostgreSQL GROUPING SETS

Een groeperingsset is een verzameling kolommen die u groepeert met behulp van de GROUP BY-clausule.

Een groeperingsset wordt aangeduid met een door komma’s gescheiden lijst met kolommen die tussen haakjes worden geplaatst:

De volgende query gebruikt bijvoorbeeld de GROUP BY-clausule om het aantal verkochte producten per merk en segment terug te geven. Met andere woorden, er wordt een groep merk en segment gedefinieerd die wordt aangeduid met (brand, segement)

Code language: SQL (Structured Query Language) (sql)

Met de volgende query wordt het aantal producten gevonden dat door een merk is verkocht. Er wordt een groeperingsset gedefinieerd (brand):

Code language: SQL (Structured Query Language) (sql)

Met de volgende query wordt het aantal producten gevonden dat per segment wordt verkocht. Hiermee wordt een groepset gedefinieerd (segment):

Code language: SQL (Structured Query Language) (sql)

Met de volgende query wordt het aantal verkochte producten voor alle merken en segmenten gevonden. Er wordt een lege groeperingsverzameling gedefinieerd, die wordt aangeduid met ().

Code language: SQL (Structured Query Language) (sql)

Voorstel dat u alle groeperingsverzamelingen met één enkele query wilt ophalen. Om dit te bereiken, kunt u UNION ALL gebruiken om alle bovenstaande query’s te combineren.

Omdat UNION ALL vereist dat alle resultaatreeksen hetzelfde aantal kolommen met compatibele gegevenstypen hebben, moet u de query’s aanpassen door NULL aan de selectielijst van elk toe te voegen, zoals hieronder getoond:

Code language: SQL (Structured Query Language) (sql)

Deze query genereert één resultaatreeks met de aggregaten voor alle groeperingsreeksen.

Ondanks dat de bovenstaande query werkt zoals u verwachtte, heeft deze twee grote problemen.

• Ten eerste is deze nogal lang.
• Ten tweede heeft deze een performance probleem omdat PostgreSQL de sales tabel voor elke query apart moet scannen.

Om het efficiënter te maken, biedt PostgreSQL de GROUPING SETS clausule die de subclausule is van de GROUP BY clausule.

Met de GROUPING SETS kunt u meerdere grouping sets definiëren in dezelfde query.

De algemene syntaxis van de GROUPING SETS is als volgt:

Code language: SQL (Structured Query Language) (sql)

In deze syntaxis hebben we vier groeperingsreeksen (c1,c2), (c1), (c2), en ().

Om deze syntaxis op het bovenstaande voorbeeld toe te passen, kunt u in plaats van de UNION ALL-clausule de GROUPING SETS-clausule gebruiken, en wel als volgt:

Code language: SQL (Structured Query Language) (sql)

Deze query is veel korter en leesbaarder. Bovendien zal PostgreSQL het aantal keren dat het de sales tabel scant optimaliseren en niet meerdere keren scannen.

Grouping functie

De GROUPING() functie accepteert een argument dat een kolomnaam of een expressie kan zijn:

De column_name of expression moet overeenkomen met degene die in de GROUP BY clausule is gespecificeerd.

De GROUPING()-functie retourneert bit 0 als het argument lid is van de huidige groeperingsset en 1 anders.

Zie het volgende voorbeeld:

Code language: SQL (Structured Query Language) (sql)

Zoals in de schermafbeelding te zien is, wanneer de waarde in de grouping_brand 0 is, toont de kolom sum het subtotaal van de brand.

Wanneer de waarde in de grouping_segment nul is, toont de somkolom het subtotaal van de segment.

U kunt de functie GROUPING() in de HAVING-clausule gebruiken om het subtotaal van elk merk als volgt te vinden:

In deze tutorial hebt u geleerd hoe u de PostgreSQL GROUPING SETS kunt gebruiken om meerdere groeperingsreeksen te genereren.