OVER-klausulen lades till SQL Server ”långt tillbaka” i SQL Server 2005 och utvidgades till i SQL Server 2012. Den används främst med ”Fönsterfunktioner”; enda undantaget är sekvensfunktionen NÄSTA VÄRDE FÖR. OVER-klausulen används för att bestämma vilka rader från frågan som tillämpas på funktionen, i vilken ordning de utvärderas i den funktionen och när funktionens beräkningar ska startas om. Eftersom den används i kombination med andra funktioner, och den här artikeln handlar om specifikt bara OVER-klausulen, kommer dessa funktioner endast att talas om när det gäller OVER-klausulen i de givna exemplen.

Syntaxen för OVER-klausulen är:

<function> OVER ( )

När man tittar på syntaxen verkar det som om alla underklausuler är valfria. Faktum är att varje funktion som kan använda OVER-klausulen avgör vilka av underklausulerna som är tillåtna och vilka som krävs. Beroende på vilken funktion som används kan själva OVER-klausulen vara valfri. Det finns ett diagram i slutet av den här artikeln som visar vilka funktioner som tillåter / kräver vilka delar av OVER-klausulen.

PARTITION BY-klausulen används för att dela resultatuppsättningen från frågan i datamängder, eller partitioner. Om klausulen PARTITION BY inte används är hela resultatuppsättningen från frågan den partition som ska användas. Fönsterfunktionen som används tillämpas på varje partition separat, och den beräkning som funktionen utförs startas om för varje partition. Du definierar en uppsättning värden som bestämmer partitionerna som du vill dela upp frågan i. Dessa värden kan vara kolumner, skalära funktioner, skalära underfrågor eller variabler.

Låt oss till exempel undersöka följande fråga:

SELECT COUNT(*)FROM .sys.indexes;

Denna fråga returnerar följande resultatuppsättning:

Detta är helt enkelt antalet rader som returneras av frågan – i det här fallet, antalet index i msdb-databasen. Låt oss nu lägga till OVER-klausulen i den här frågan:

SELECT object_id, index_id, COUNT(*) OVER ()FROM .sys.indexes;

De förkortade resultaten är:

Denna fråga returnerar objekt_id och index_id för varje index och det totala antalet index i resultatuppsättningen. Eftersom en PARTITION BY-sats inte användes behandlades hela resultatsatsen som en enda partition. Nu är det dags att lägga till PARTITION BY-klausulen och se hur detta ändrar resultaten:

SELECT object_id, index_id, COUNT(*) OVER (PARTITION BY object_id)FROM .sys.indexes;

De förkortade resultaten är:

Denna fråga returnerar en rad för varje index, men nu specificerar frågan en PARTITION BY-sats i kolumnen objekt_id, så räkningsfunktionen returnerar antalet index på just det objektet_id. ORDER BY-klausulen styr ordningen att raderna utvärderas av funktionen. Detta kommer att demonstreras inom kort. ROWS eller RANGE-satsen bestämmer delmängden av rader inom partitionen som ska tillämpas på funktionen. När du använder ROWS eller RANGE anger du början och slutpunkten för fönstret. De tillåtna värdena är:

Det finns två syntaxer för att specificera fönstret:

BETWEEN <beginning frame> AND <ending frame><beginning frame>

Om bara startramen är angiven är standardramen CURRENT ROW.

Det obegränsade nyckelordet anger början på partitionen (för PRECEDING) eller slutet på partitionen (för FÖLJANDE). AKTUELL RAD anger att den aktuella raden är antingen början på fönstret eller slutet på fönstret, beroende på vilken fönsterramposition den används i. ”N” anger ett antal rader antingen före den aktuella raden (för föregående ), eller efter den aktuella raden (för FÖLJANDE) som ska användas för fönsterramen.

Följande är giltiga fönsterspecifikationer:

-- specifies the entire result set from the partitionBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- specifies 5 rows, starting 4 rows prior to the current row through the current row from the partitionBETWEEN 4 PRECEDING AND CURRENT ROW-- specifies all of the rows from the current row to the end of the partitionBETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING-- specifies all of the rows from the start of the partition through the current rowUNBOUNDED PRECEDING

För att kunna använda ROWS- eller RANGE-satsen måste du också ange ORDER BY-satsen. Omvänt, om du använder ORDER BY-satsen och du inte anger en ROWS- eller RANGE-sats, då är standard RANGE MELLAN OBEGRÄNSAD FÖRFARANDE OCH STRÖM ROW används.

För att visa ORDER BY- och ROWS- eller RANGE-satserna, låt oss skapa några testdata: två konton, fyra datum per konto och ett belopp för varje datum. Frågan visar båda dessa klausuler som används på olika sätt:

Denna fråga returnerar följande resultatuppsättning:

The ” RowNbr ”-kolumnen används g COUNT-funktionen för att returnera hur många rader som finns i partitionen. Partitionen beställs av TranDate, och vi anger en fönsterram för alla rader från början av partitionen till den aktuella raden. För den första raden finns det bara en rad i fönsterramen, så värdet ”1” returneras. För den andra raden finns det nu två rader i fönsterramen så att värdet ”2” returneras. Och så vidare genom resten av raderna i det här kontot.

Eftersom PARTITION BY-klausulen specificerar kontot återställs funktionsberäkningarna när kontot ändras, vilket kan ses genom att undersöka raderna för det andra kontot i resultatuppsättningen. Detta är ett exempel på en ”löpande” aggregering, där aggregeringen bygger på tidigare beräkningar. Ett exempel på när du skulle använda detta skulle vara när du beräknar ditt bankkontosaldo efter varje transaktion (annars känd som en löpande summa).

Kolumnen ”DateCount” utför en räkning av hur många rader, uppdelade efter datumet. I det här exemplet har var och en av kontona en transaktion på var och en av samma fyra datum, så varje datum har två transaktioner (en för varje konto). Detta resulterar i att värdet ”2” returneras för varje rad. Detta liknar att utföra en räkning som använder GROUP BY för datumet. Skillnaden är att summan returneras för varje rad istället för bara en gång för varje datum. Ett exempel när du skulle använda den här metoden skulle vara att visa en ”Rad X av Y”, eller att beräkna en procent av den aktuella raden till den totala.

Kolumnen ”Last2Count” utför ett antal rader inom partitionen för den aktuella raden och den ena raden omedelbart före den. För den första raden i varje konto, eftersom det inte finns några rader före den, returneras värdet ”1”. För de återstående raderna i varje konto returneras värdet ”2”. Detta är ett exempel på en ”rörlig” eller ”glidande” aggregering. Ett exempel på när du skulle använda den här metoden skulle vara att beräkna en bonus baserat på senaste två månaders försäljning.

Vid denna tidpunkt har jag bara visat ROWS-klausulen. RANGE-klausulen fungerar på liknande sätt, men istället för att hantera raderna på ett positionellt sätt, hanterar den värden som returneras av den raden. Det beror på att det inte är positionellt att de N FÖREGÅENDE / FÖLJANDE klausulerna inte kan användas. Låt oss ta en snabb titt på skillnaden mellan RADER och RANGE genom att använda dem båda i samma fråga. lista över personer (låt oss kalla dem DBA) och deras timpris. Observera att raderna med RowIDs 4 & 5 och 12 & 13 har samma hastighet. Frågan summerar priserna två gånger, en gång med ROWS och den andra med RANGE:

Denna fråga ger följande resultatuppsättning:

I både SumByRows och SumByRange-kolumnerna är OVER-satsen identisk med undantag för ROWS / RANGE-satsen. Lägg också märke till att eftersom slutintervallet inte specificerades är standard att använda CURRENT ROW. Eftersom vi summerar lönen från början av det resultat som ställts in genom den aktuella raden, beräknar vi verkligen löpande summan av kolumnen Lön. I kolumnen SumByRows beräknas värdet med hjälp av ROWS-satsen, och vi kan se att summan av den aktuella raden är den aktuella radens lön plus den tidigare radens totala. RANGE-klausulen fungerar emellertid utifrån värdet på kolumnen Lön, så den summerar alla rader med samma eller lägre lön. Detta resulterar i att SumByRange-värdet är samma värde för alla rader med samma lön.

En viktig anmärkning: ORDER BY-klausulen i OVER-klausulen styr bara den ordning som raderna i partitionen ska användas av fönsterfunktionen. Det styr inte ordningen på det slutliga resultatuppsättningen. Utan en ORDER BY-sats i själva frågan garanteras inte ordningen på raderna. Du kanske märker att din fråga kanske återvänder i ordningen efter den senast angivna OVER-klausulen – detta beror på det sätt som detta för närvarande implementeras i SQL Server. Om SQL Server-teamet hos Microsoft ändrar hur det fungerar kan det hända att det inte längre beställer dina resultat på det sätt som du för närvarande observerar. Om du behöver en specifik ordning för resultatuppsättningen måste du ange en ORDER BY-sats mot själva frågan.

Slutligen är här ett diagram över de olika funktionerna som kan använda OVER-satsen, liksom vilka delar av klausulen är tillåtna / obligatoriska / valfria.

R-obligatoriskt, O-valfritt, X-ej tillåtet