Het is met name handig als u de aggregaties of rangschikkingen voor groepen rijen moet berekenen zonder de hele resultatenset daadwerkelijk samen te vouwen.
Neem deel aan deze tutorial waarin we alles leren over wat u moet weten om aan de slag te gaan met de OVER-clausule.
Vereisten:
Voordat we ingaan op de functionaliteit en werking van de OVER-clausule, moet u ervoor zorgen dat u de basisbeginselen van SQL achter de rug heeft. Daarnaast gaan wij ervan uit dat u toegang heeft tot een database waarmee u uw kennis kunt testen.
In ons geval gebruiken we de MySQL-database met de Sakila-voorbeelddatabase. Zorg ervoor dat u voldoende rechten heeft en dat uw database-engine de vensterfuncties ondersteunt.
Syntaxis:
Zoals we eerder vermeldden, gebruiken we in de meeste gevallen voornamelijk de OVER-clausule in combinatie met de vensterfuncties.
Als zodanig kunnen we de syntaxis van de clausule als volgt uitdrukken:
[PARTITIE DOOR partitie_expressie, ...]
[ORDER OP sort_expression [ASC | BESCHRIJVING], ...]
[framespecificatie]
)
In de gegeven syntaxis kunnen we elke component als volgt opsplitsen:
-
– Het verwijst naar de vensterfunctie die we willen toepassen op een specifiek venster met rijen zoals SUM(), AVG(), ROW_NUMBER(), RANK, etc. - Expressie – Dit specificeert een kolom of expressie waarop de vensterfunctie wordt toegepast.
- PARTITION BY – Dit is een optionele clausule die de resultaatset in partities verdeelt, waarbij elke partitie een afzonderlijke eenheid is waarop de functie wordt toegepast. Rijen binnen dezelfde partitie delen dezelfde waarden in de opgegeven kolommen.
- ORDER BY – Dit specificeert de volgorde waarin de rijen in elke partitie worden verwerkt.
- frame_specification – Dit is een optionele clausule die het frame van rijen binnen de partitie definieert. Algemene framespecificaties zijn RIJEN TUSSEN
EN of RANGE TUSSEN EN
Laten we, nu dat uit de weg is, enkele praktische voorbeelden bekijken van hoe u het kunt gebruiken.
Voorbeeld:
Laten we demonstreren hoe u de clausule kunt gebruiken met behulp van de voorbeelddatabase van Sakila. Beschouw een voorbeeld waarbij we de totale inkomsten voor elke filmcategorie moeten bepalen.
We kunnen de somvensterfunctie gebruiken met de OVER-clausule en een aantal join-instructies, zoals weergegeven in het volgende voorbeeld:
SELECTEERcategorie.naam AS categorienaam,
film.titel AS film_titel,
film.huur_tarief,
SUM(betalingsbedrag) OVER (VERDELING OP categorie.naam) AS totale_omzet
VAN
film
MEEDOEN
film_categorie AAN
film.film_id = film_categorie.film_id
MEEDOEN
categorie AAN
film_category.category_id = categorie.categorie_id
MEEDOEN
inventaris AAN
film.film_id = inventaris.film_id
MEEDOEN
verhuur AAN
inventaris.inventaris_id = huur.inventaris_id
MEEDOEN
betaling AAN
verhuur.rental_id = betaling.rental_id
BESTEL DOOR
categorie naam,
film titel;
In de gegeven zoekopdracht beginnen we met het selecteren van de filmtitel en het huurtarief, en gebruiken we de expressie sum (betaling.bedrag over partitie op categorie.naam) om de som van elke categoriepartitie te bepalen op basis van de categorienaam.
We moeten de PARTITION BY-clausule gebruiken om ervoor te zorgen dat de berekening van de som bij elke unieke categorie opnieuw begint.
De resulterende uitvoer is als volgt:
Daar heb je het!
Conclusie
In dit voorbeeld hebben we de basisbeginselen van het werken met de OVER-clausule in SQL onderzocht. Dit is geen basisclausule en vereist voorafgaande bekendheid met andere SQL-functies.