Det är särskilt användbart när du behöver beräkna aggregeringarna eller rankningarna för grupper av rader utan att faktiskt kollapsa hela resultatuppsättningen.
Följ med oss i den här handledningen när vi lär oss allt om det finns att veta för att få dig att börja arbeta med OVER-klausulen.
Krav:
Innan vi dyker in i funktionaliteten och hur OVER-klausulen fungerar, se till att du har grunderna i SQL ur vägen. Vi förutsätter också att du har tillgång till en databas som du kan använda för att testa dina kunskaper.
I vårt fall kommer vi att använda MySQL-databasen med Sakila-exempeldatabasen. Se bara till att du har tillräckliga behörigheter och att din databasmotor stöder fönsterfunktionerna.
Syntax:
Som vi nämnde tidigare använder vi i de flesta fall huvudsakligen OVER-satsen i kombination med fönsterfunktionerna.
Som sådan kan vi uttrycka syntaxen för satsen enligt följande:
[PARTITION BY partition_expression, ...]
[ORDER BY sort_expression [ASC | DESC], ...]
[frame_specification]
)
I den givna syntaxen kan vi dela upp varje komponent enligt följande:
-
– Det hänvisar till fönsterfunktionen som vi vill tillämpa över ett specifikt fönster med rader som SUM(), AVG(), ROW_NUMBER(), RANK, etc. - Uttryck – Detta anger en kolumn eller ett uttryck för vilket fönsterfunktionen används.
- PARTITION BY – Detta är en valfri klausul som delar upp resultatuppsättningen i partitioner där varje partition är som en separat enhet där funktionen tillämpas. Rader inom samma partition delar samma värden i de angivna kolumnerna.
- ORDER BY – Detta anger i vilken ordning raderna i varje partition bearbetas.
- frame_specification – Detta är en valfri klausul som definierar ramen för rader inom partitionen. Vanliga ramspecifikationer inkluderar RADER MELLAN
OCH eller RANGE MELLAN OCH
Med det ur vägen, låt oss utforska några praktiska exempel på hur man använder det.
Exempel:
Låt oss visa hur man använder klausulen med hjälp av Sakila-exempeldatabasen. Tänk på ett exempel där vi måste bestämma den totala intäkten för varje filmkategori.
Vi kan använda sum window-funktionen med OVER-satsen och ett gäng join-satser som visas i följande exempel:
VÄLJkategori.namn AS kategorinamn,
film.title AS film_title,
film.rental_rate,
SUM(betalning.belopp) ÖVER (DELNING EFTER kategori.namn) SOM total_revenue
FRÅN
filma
ANSLUTA SIG
filmkategori PÅ
film.film_id = film_category.film_id
ANSLUTA SIG
kategori PÅ
film_category.category_id = category.category_id
ANSLUTA SIG
inventering PÅ
film.film_id = inventory.film_id
ANSLUTA SIG
hyra PÅ
inventory.inventory_id = rental.inventory_id
ANSLUTA SIG
betalning PÅ
rental.rental_id = betalning.rental_id
SORTERA EFTER
kategori namn,
film.title;
I den givna frågan börjar vi med att välja filmtitel, hyrespris och använda uttrycket summa (betalning.belopp över partition efter kategori.namn) för att bestämma summan av varje kategoripartition med kategorinamnet.
Vi måste använda PARTITION BY-satsen för att säkerställa att beräkningen av summan startar om vid varje unik kategori.
Resultatet är som följer:
Där har du det!
Slutsats
I det här exemplet utforskade vi grunderna för att arbeta med OVER-satsen i SQL. Detta är inte en grundläggande klausul och kräver tidigare bekantskap med andra SQL-funktioner.