De OVER-clausule is toegevoegd aan SQL Server “ver terug” in SQL Server 2005, en is uitgebreid in SQL Server 2012. Het wordt voornamelijk gebruikt met de “Vensterfuncties”; de enige uitzondering is de sequentiefunctie VOLGENDE WAARDE VOOR. De OVER-clausule wordt gebruikt om te bepalen welke rijen uit de query op de functie worden toegepast, in welke volgorde ze door die functie worden geëvalueerd en wanneer de berekeningen van de functie opnieuw moeten starten. Omdat het wordt gebruikt in combinatie met andere functies, en dit artikel gaat specifiek over de OVER-clausule, zal er alleen over deze functies worden gesproken aangezien het betrekking heeft op de OVER-clausule in de gegeven voorbeelden.

De syntaxis van de OVER-clausule is:

<function> OVER ( )

Als we naar de syntaxis kijken, lijkt het erop dat alle subclausules optioneel zijn. In feite bepaalt elke functie die de OVER-clausule kan gebruiken welke van de subclausules zijn toegestaan en welke vereist zijn. Afhankelijk van de functie die wordt gebruikt, kan de OVER-clausule zelf optioneel zijn. Aan het einde van dit artikel staat een diagram dat laat zien welke functies welke delen van de OVER-clausule toestaan / vereisen.

De PARTITION BY-clausule wordt gebruikt om de resultatenset van de query in gegevenssubsets te verdelen, of partities. Als de PARTITION BY-clausule niet wordt gebruikt, is de volledige resultatenset van de query de partitie die zal worden gebruikt. De vensterfunctie die wordt gebruikt, wordt op elke partitie afzonderlijk toegepast en de berekening die de functie uitvoert, wordt voor elke partitie opnieuw gestart. U definieert een reeks waarden die de partitie (s) bepalen waarin de query moet worden onderverdeeld. Deze waarden kunnen kolommen, scalaire functies, scalaire subquery’s of variabelen zijn.

Laten we bijvoorbeeld de volgende query eens bekijken:

SELECT COUNT(*)FROM .sys.indexes;

Deze query retourneert de volgende resultatenset:

Dit is gewoon het aantal rijen dat door de query wordt geretourneerd – in dit geval het aantal indexen in de msdb-database. Laten we nu de OVER-clausule aan deze zoekopdracht toevoegen:

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

De verkorte resultaten zijn:

Deze query retourneert de object_id en index_id voor elke index, en het totale aantal indexen in de resultatenset. Omdat er geen PARTITION BY-component werd gebruikt, werd de volledige resultaatset behandeld als een enkele partitie. Het is nu tijd om de PARTITION BY-clausule toe te voegen en te zien hoe dit de resultaten verandert:

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

De verkorte resultaten zijn:

Deze query retourneert een rij voor elke index, maar nu specificeert de query een PARTITION BY-clausule van de object_id-kolom, dus de count-functie retourneert het aantal indexen op dat specifieke object_id. De ORDER BY-component bepaalt de volgorde waarin de rijen worden geëvalueerd door de functie. Dit zal binnenkort worden aangetoond. De clausule ROWS of RANGE bepaalt de subset van rijen binnen de partitie die op de functie moeten worden toegepast. Als u ROWS of RANGE gebruikt, specificeert u het begin- en eindpunt van het venster. De toegestane waarden zijn:

Er zijn twee syntaxis voor het specificeren van het venster:

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

Als alleen het beginframe is opgegeven, is het standaard eindframe CURRENT ROW.

Het sleutelwoord UNBOUNDED specificeert het begin van de partitie (voor VOORAFGAANDE), of het einde van de partitie (voor VOLGENDE). CURRENT ROW geeft aan dat de huidige rij het begin van het venster of het einde van het venster is, afhankelijk van de positie van het raamkozijn waarin het wordt gebruikt. ‘N’ geeft een aantal rijen aan voorafgaand aan de huidige rij (voor VOORAFGAANDE ), of na de huidige rij (voor FOLLOWING) om te gebruiken voor het raamkozijn.

Dit zijn geldige raamspecificaties:

-- 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

Als u de clausule ROWS of RANGE wilt gebruiken, moet u ook de clausule ORDER BY specificeren. Omgekeerd, als u de clausule ORDER BY gebruikt en u geeft geen clausule ROWS of RANGE op, dan moet de standaardwaarde RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW wordt gebruikt.

Om de ORDER BY- en ROWS- of RANGE-clausules te demonstreren, maken we enkele testgegevens: twee accounts, vier datums per account en een bedrag voor elke datum. De query toont beide clausules die op verschillende manieren worden gebruikt:

Deze query retourneert de volgende resultatenset:

De ” RowNbr ”kolom is usin g de COUNT-functie om te retourneren hoeveel rijen er in de partitie zijn. De partitie is geordend op TranDate, en we specificeren een raamkozijn van alle rijen vanaf het begin van de partitie tot en met de huidige rij. Voor de eerste rij is er maar één rij in het raamkozijn, dus de waarde “1” wordt geretourneerd Voor de tweede rij zijn er nu twee rijen in het raamkozijn dus de waarde “2” wordt geretourneerd. En zo verder door de rest van de rijen in dit account.

Aangezien de PARTITION BY-clausule het account specificeert, worden de functieberekeningen gereset wanneer het account verandert, wat kan worden gezien door de rijen voor het tweede account in de resultatenset te onderzoeken. Dit is een voorbeeld van een ‘lopende’ aggregatie, waarbij de aggregatie voortbouwt op eerdere berekeningen. Een voorbeeld van wanneer u dit zou gebruiken is om het saldo van uw bankrekening na elke transactie te berekenen (ook wel bekend als een lopend totaal).

De “DateCount” -kolom voert een telling uit van het aantal rijen, gepartitioneerd op datum. In dit voorbeeld heeft elk van de rekeningen een transactie op elk van dezelfde vier datums, dus elke datum heeft twee transacties (één voor elke rekening). Dit resulteert in de waarde ‘2’ die wordt geretourneerd voor elke rij. Dit is vergelijkbaar met het uitvoeren van een telling waarbij GROUP BY voor de datum wordt gebruikt. Het verschil is dat het totaal wordt geretourneerd voor elke rij in plaats van slechts één keer voor elke datum. Een voorbeeld van wanneer u deze methode zou gebruiken, zou zijn om een “Rij X van Y” weer te geven, of om een percentage van de huidige rij tot het totaal te berekenen.

De kolom “Last2Count” voert een telling van de rijen uit binnen de partitie, voor de huidige rij en de ene rij die er onmiddellijk aan voorafgaat. Voor de eerste rij in elke account, aangezien er geen rijen aan voorafgaan, wordt de waarde “1” geretourneerd. Voor de resterende rijen in elk account wordt de waarde ‘2’ geretourneerd. Dit is een voorbeeld van een ‘bewegende’ of ‘glijdende’ aggregatie. Een voorbeeld van wanneer u deze methode zou gebruiken, is om een bonus te berekenen op basis van de verkoop van de afgelopen twee maanden.

Op dit punt heb ik alleen de ROWS-clausule getoond. De RANGE-clausule werkt op een vergelijkbare manier, maar in plaats van de rijen op een positionele manier te behandelen, behandelt deze de waarden die door die rij worden geretourneerd. Omdat het niet positioneel is, kunnen de clausules N PRECEDING / FOLLOWING niet worden gebruikt. Laten we het verschil tussen ROWS en RANGE snel bekijken door beide in dezelfde query te gebruiken. Hier hebben we een lijst met mensen (laten we ze DBA’s noemen) en hun uurtarieven. Houd er rekening mee dat de rijen met RowID’s 4 & 5 en 12 & 13 hebben hetzelfde tarief. De zoekopdracht zal de tarieven twee keer optellen, één keer met ROWS en de andere met RANGE:

Deze zoekopdracht levert de volgende resultatenset op:

In zowel de SumByRows- als de SumByRange-kolom is de OVER-clausule identiek met uitzondering van de ROWS / RANGE-clausule. Merk ook op dat aangezien het eindbereik niet is opgegeven, de standaard het gebruik van CURRENT ROW is. Aangezien we het salaris vanaf het begin van het ingestelde resultaat tot en met de huidige rij optellen, berekenen we echt een lopend totaal van de kolom Salaris. In de SumByRows-kolom wordt de waarde berekend met behulp van de ROWS-clausule, en we kunnen zien dat de som van de huidige rij het salaris van de huidige rij is plus het totaal van de vorige rij. De clausule RANGE werkt echter op basis van de waarde van de kolom Salaris, dus alle rijen met hetzelfde of een lager salaris worden opgeteld. Dit resulteert erin dat de SumByRange-waarde dezelfde waarde heeft voor alle rijen met hetzelfde Salaris.

Een belangrijke opmerking: de ORDER BY-clausule in de OVER-clausule bepaalt alleen de volgorde waarin de rijen in de partitie worden gebruikt door de vensterfunctie. Het heeft geen invloed op de volgorde van de uiteindelijke resultatenset. Zonder een ORDER BY-clausule op de query zelf, is de volgorde van de rijen niet gegarandeerd. Mogelijk merkt u dat uw vraag wordt geretourneerd in de volgorde van de laatst opgegeven OVER-clausule – dit komt door de manier waarop dit momenteel is geïmplementeerd in SQL Server. Als het SQL Server-team bij Microsoft de manier waarop het werkt verandert, is het mogelijk dat het uw resultaten niet langer rangschikt op de manier die u momenteel observeert. Als u een specifieke volgorde voor de resultatenset nodig heeft, moet u een ORDER BY-clausule tegen de query zelf opgeven.

Ten slotte is hier een diagram van de verschillende functies die de OVER-clausule kunnen gebruiken, evenals welke delen van de clausule zijn toegestaan / vereist / optioneel.

R-vereist, O-optioneel, X-niet toegestaan