Inleiding tot T-SQL Vensterfuncties

Ik heb de afgelopen zes jaar door de VS gereisd om databaseprofessionals te vertellen over T-SQL Vensterfuncties op SQL zaterdagen en andere evenementen. Ik ben verbaasd over hoe weinig mensen hebben gehoord over deze functies en nog minder die ze gebruiken. Aan het einde van elke presentatie komen een of meer mensen naar voren om te zeggen dat ze wilden dat ze jaren eerder over deze functies hadden geleerd omdat ze voordelig hadden kunnen zijn voor zoveel vragen.,

deze functies zijn bevorderd om de prestaties te verbeteren ten opzichte van andere, meer traditionele methoden. Daar ben ik het gedeeltelijk mee eens. Ze maken veel zoekopdrachten gemakkelijker te schrijven, en soms verbeteren ze de prestaties.

heeft niets te maken met het Windows besturingssysteem

deze functies maken deel uit van de ANSI SQL 2003-standaarden en zijn, in het geval van SQL Server, T-SQL-functies die worden gebruikt om query ‘ s te schrijven. Ze hebben niets te maken met het Windows-besturingssysteem of API-oproepen. Andere databasesystemen, zoals Oracle, hebben deze ook opgenomen als onderdeel van hun eigen SQL-taal.,

Window (ook Window of windowed) functies voeren een berekening uit over een reeks rijen. Ik denk graag aan “kijken door het raam” naar de rijen die worden geretourneerd en het hebben van een laatste kans om een berekening uit te voeren. Het venster wordt gedefinieerd door de over-clausule die bepaalt of de rijen worden verdeeld in kleinere sets en of ze worden geordend. In feite, als je een window functie gebruikt zul je altijd een over clausule gebruiken. De over-clausule maakt ook deel uit van de volgende waarde voor syntaxis die vereist is voor het sequence-object, maar anders wordt het gebruikt met vensterfuncties.,

de over-clausule kan een partitie per optie bevatten. Dit breekt de rijen in kleinere sets. Je denkt misschien dat dit hetzelfde is als GROUP BY, maar dat is het niet. Bij het groeperen wordt één rij per unieke groep geretourneerd. Bij het gebruik van partitie door, alle detail rijen worden geretourneerd, samen met de berekeningen. Als u een venster in uw huis dat is verdeeld in panelen, elk paneel is een venster. Bij het denken over window functies, de volledige set van resultaten is een partitie, maar bij het gebruik van partitie door, elke partitie kan ook worden beschouwd als een venster., Partitie door wordt ondersteund-en optioneel-voor alle windowing functies.

de OVER-clausule kan ook een volgorde per optie bevatten. Dit is onafhankelijk van de volgorde door clausule van de vraag. Sommige functies vereisen volgorde door, en het wordt niet ondersteund door de anderen. Wanneer de volgorde van de rijen belangrijk is bij het toepassen van de berekening, is de volgorde Door vereist.

Vensterfuncties mogen alleen worden gebruikt in de Select en ORDER BY-clausules van een query. Ze worden toegepast na het samenvoegen, filteren of groeperen.,

Ranking functies

de meest gebruikte vensterfuncties, ranking functies, zijn beschikbaar sinds 2005. Dat is wanneer Microsoft introduceerde ROW_NUMBER, RANK, DENSE_RANK, en NTILE. ROW_NUMBER wordt zeer vaak gebruikt, om unieke rijnummers toe te voegen aan een partitie of aan de gehele resultaatset. Het toevoegen van een rij nummer, of een van de andere ranking functies, is meestal niet het doel, maar het is een stap op weg naar de oplossing.

volgorde is vereist in de over-clausule bij gebruik van ROW_NUMBER en de andere functies in deze groep., Dit vertelt de database engine de volgorde waarin de nummers moeten worden toegepast. Als de waarden van de kolommen of expressies die in de volgorde BY worden gebruikt niet uniek zijn, dan zullen RANK en DENSE_RANK de ties behandelen, terwijl ROW_NUMBER niets geeft om ties. NTILE wordt gebruikt om de rijen te verdelen in emmers op basis van de volgorde door.

een voordeel van ROW_NUMBER is de mogelijkheid om niet-unieke rijen om te zetten in unieke rijen. Dit kan bijvoorbeeld worden gebruikt om dubbele rijen te elimineren.

om te laten zien hoe dit werkt, begint u met een tijdelijke tabel met dubbele rijen., De eerste stap is om de tabel te maken en te vullen.

het toevoegen van ROW_NUMBER en partitionering door elke kolom zal de rijnummers voor elke unieke reeks rijen opnieuw opstarten. U kunt de unieke rijen identificeren door die te vinden met een rijnummer gelijk aan één.,

SELECT Col1, Col2,

ROW_NUMBER() OVER(PARTITION BY Col1, Col2 OM DOOR Col1) ALS RowNum

VAN #Duplicaten;

Nu, alles wat je hoeft te doen is het verwijderen van eventuele rijen een rij groter is dan één., Het probleem is dat je geen vensterfuncties kunt toevoegen aan de WHERE-clausule.,

1 2	DELETE #Duplicates WHERE ROW_NUMBER() OVER(PARTITION BY Col1, Col2 ORDER BY Col1) <> 1;

You’ll see this error message:

The way around this problem is to separate the logic using a common table expression (CTE)., U kunt dan de rijen direct uit de CTE verwijderen.

succes! De extra rijen zijn verwijderd en er blijft een unieke reeks rijen over.

om het verschil tussen ROW_NUMBER, RANK en DENSE_RANK te zien, voer je deze query uit:

De volgorde van elke over-clausule is Orderdatum die niet uniek is. Deze klant heeft op 2013-10-24 twee bestellingen geplaatst. ROW_NUMBER ging gewoon door met het toewijzen van nummers en deed niets anders, ook al is er een dubbele datum., Rang toegewezen 6 Aan beide rijen en vervolgens ingehaald naar ROW_NUMBER met een 8 op de volgende rij. DENSE_RANK heeft ook 6 toegewezen aan de twee rijen, maar 7 toegewezen aan de volgende rij.

twee verklaren het verschil, denk aan ROW_NUMBER als positioneel. Rang is zowel positioneel als logisch. Deze twee rijen zijn logisch hetzelfde gerangschikt, maar de volgende rij wordt gerangschikt door de positie in de set. DENSE_RANK rangschikt ze logisch. Order 2013-11-04 is de 7e unieke datum.

de uiteindelijke functie in deze groep wordt NTILE genoemd. Het wijst bucket-nummers toe aan de rijen in plaats van rijnummers of rangen., Hier is een voorbeeld:

NTILE heeft een parameter, in dit geval 4, dat is het aantal emmers dat u wilt zien in de resultaten. De ORDER BY wordt toegepast op de som van de verkopen. De rijen met de laagste 25% worden toegewezen 1, de rijen met de hoogste 25% worden toegewezen 4. Tot slot, de resultaten van NTILE worden vermenigvuldigd met 1000 om te komen met de bonus bedrag. Omdat 14 niet gelijkmatig gedeeld kan worden door 4, gaat er een extra rij in elk van de eerste twee emmers.

Vensteraggregaten

Vensteraggregaten werden ook geïntroduceerd met SQL Server 2005., Deze maken het schrijven van een aantal lastige queries gemakkelijk, maar zal vaak slechter dan oudere technieken presteren. Hiermee kunt u uw favoriete aggregate-functie toevoegen aan een niet-aggregate query. Zeggen, bijvoorbeeld wilt u alle bestellingen van de klant, samen met het subtotaal weer te geven voor elke klant., Door het toevoegen van een BEDRAG met behulp van de OVER-component, doet u dit heel eenvoudig:

SELECTEER Klantnummer, Orderdatum, SalesOrderID, TotalDue,

SUM(TotalDue) OVER(PARTITION BY Klantnummer) ALS Subtotaal

VAN de Omzet.,SalesOrderHeader;

door de partitie toe te voegen wordt voor elke klant een subtotaal berekend. Elke geaggregeerde functie kan worden gebruikt, en volgorde door in de over-clausule wordt niet ondersteund.

Vensteraggregaatverbeteringen in 2012

vanaf 2012 kunt u een volgorde toevoegen aan de over-clausule aan vensteraggregaten om bijvoorbeeld lopende totalen en voortschrijdende gemiddelden te produceren. Tegelijkertijd introduceerde Microsoft het concept van framing. Het toevoegen van een partitie door is als het verdelen van een venster in panelen., Toevoegen framing is als het creëren van een glas-in-lood raam. Elke rij heeft een afzonderlijk venster waar de expressie zal worden toegepast.

met deze verbetering kunt u lopende totalen maken, zelfs zonder de framing-syntaxis toe te voegen., Here is an example that returns a running total by customer:

SELECT CustomerID, OrderDate, SalesOrderID, TotalDue,

SUM(TotalDue) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID)

AS RunningTotal

FROM Sales.,SalesOrderHeader;

het standaard frame, dat wordt gebruikt als een frame niet is gespecificeerd, is het bereik tussen onbegrensde voorafgaande en huidige rij. Helaas zal dit niet zo goed presteren als wanneer u in plaats daarvan dit frame opgeeft: rijen tussen onbegrensde voorafgaande en huidige rij. Het verschil is het woord rijen. Bereik is slechts gedeeltelijk geïmplementeerd op dit moment, en het is bedoeld voor het werken met perioden, terwijl rijen positioneel is., Het frame, rijen tussen onbegrensde voorafgaande en huidige rij, betekent dat het venster bestaat uit de eerste rij van de partitie en alle rijen tot aan de huidige rij. Elke berekening wordt gedaan over een andere reeks rijen. Bij het uitvoeren van de berekening voor rij 4 worden bijvoorbeeld de rijen 1 tot en met 4 gebruikt.

bij het uitvoeren van de berekening voor rij 5 zijn de rijen 1 tot en met 5. Het venster wordt groter als je van de ene rij naar de volgende gaat.,

u kunt ook de syntaxis rijen tussen n voorafgaan en huidige rij of rijen tussen huidige rij en N volgende gebruiken. Dit kan handig zijn voor het berekenen van een drie maanden voortschrijdend gemiddelde, bijvoorbeeld. Het volgende cijfer vertegenwoordigt rijen tussen 2 voorafgaande en huidige rij.

wanneer 5 de huidige rij is, beweegt het venster; het verandert de grootte niet.,

Hier is de lijst met termen die u moet weten bij het schrijven van de framing optie:

Ik geef toe dat deze syntaxis een beetje verwarrend is, maar het gebruik van SQL Prompt maakt het schrijven van de framing optie makkelijker!

Offset functies

ook opgenomen bij de release van SQL Server 2012 zijn vier functies waarmee u waarden uit andere rijen kunt opnemen – zonder een self-join te doen. Microsoft noemt deze ‘analytische functies’, maar ik noem ze altijd’ offset functies ‘ bij het presenteren over dit onderwerp., Met twee van de functies kunt u kolommen of expressies uit een rij voor (LAG) of na (LEAD) de huidige rij trekken. Met de andere twee functies kunt u waarden retourneren uit de eerste rij van de partitie (FIRST_VALUE) of de laatste rij van de partitie (LAST_VALUE). FIRST_VALUE en LAST_VALUE vereisen ook framing, dus zorg ervoor dat het frame bij het gebruik van deze functies. Alle vier de functies vereisen de volgorde van optie van de over-clausule. Dat is logisch, omdat de database-engine de volgorde van de rijen moet weten om erachter te komen welke rij de waarde bevat om terug te keren.,

sommige mensen hebben een favoriete band; sommige mensen hebben een favoriete film. Ik heb een favoriete functie – vertraging. Het is makkelijk te gebruiken (geen frame!) en presteert geweldig., Here is an example:

SELECT CustomerID, OrderDate, SalesOrderID,

LAG(SalesOrderID) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID

) AS PrevOrder

FROM Sales.,SalesOrderHeader

volgorde door CustomerID;

vertraging en lood vereisen een argument – de kolom of expressie die u wilt retourneren. LAG retourneert standaard de waarde uit de vorige rij en LEAD retourneert de waarde uit de volgende rij. U kunt dat wijzigen door een waarde voor de OFFSET parameter op te geven, die standaard 1 is. Merk op dat de eerste rij van de partitie NULL retourneert. Als u de NULLs wilt overschrijven, kunt u een standaardwaarde opgeven., Here is a similar query that goes back two rows and has a default value:

SELECT CustomerID, OrderDate, SalesOrderID,

LAG(SalesOrderID,2,0) OVER(PARTITION BY CustomerID

ORDER BY SalesOrderID) AS Back2Orders

FROM Sales.,SalesOrderHeader;

FIRST_VALUE en LAST_VALUE kunnen worden gebruikt om een waarde te vinden uit de allereerste rij of de allerlaatste rij van de partitie. Zorg ervoor dat u het frame specificeert, niet alleen om prestatieredenen, maar omdat het standaard frame niet werkt zoals u zou verwachten met LAST_VALUE. Het standaardframe, bereik tussen onbegrensde voorafgaande en huidige rij, gaat alleen naar de huidige rij. De laatste rij van de partitie is niet inbegrepen., Om de verwachte resultaten te krijgen, moet u rijen opgeven tussen de huidige rij en onbegrensde volgende wanneer u LAST_VALUE gebruikt., Hier is een voorbeeld van het gebruik van EERSTE_WAARDE:

SELECTEER Klantnummer, Orderdatum, SalesOrderID,

EERSTE_WAARDE(SalesOrderID) OVER(PARTITION BY Klantnummer

OM DOOR SalesOrderID

RIJEN TUSSEN UNBOUNDED VORIGE EN de HUIDIGE RIJ) ALS FirstOrder

VAN de Omzet.,SalesOrderHeader;

statistische functies

Microsoft groepeert deze vier functies – PERCENT_RANK, CUME_DIST, PERCENTILE_DISC, PERCENTILE_CONT – samen met de offset functies roepen alle acht de analytische functies. Omdat ik deze graag onderscheid van de offset functies, noem ik deze statistisch.

PERCENT_RANK en CUME_DIST geven een ranking voor elke rij over een partitie. Ze verschillen lichtjes. PERCENT_RANK geeft het percentage rijen terug dat lager is dan de huidige rij., “Mijn score is hoger dan 90% van de scores.”CUME_DIST, of cumulatieve distributie, geeft de exacte rang. “Mijn score is op 90% van de scores.”Hier is een voorbeeld met behulp van de gemiddelde hoge temperatuur in St.Louis voor elke maand. Merk op dat de rangen werden bepaald door de Fahrenheit temperatuur.

de rijen worden niet bepaald door de relatieve waarden, maar door de posities van de rijen. Merk op dat Maart en November hebben dezelfde gemiddelde hoge temperatuur, dus ze werden gerangschikt hetzelfde.

u vraagt zich misschien af hoe u PERCENT_RANK en CUME_DIST kunt berekenen., Hier zijn de formules:

1 2	PERCENT_RANK = (Score -1)/(aantal rijen -1) REGR_SXX = (Rang)/(aantal rijen)

PERCENTILE_DISC en PERCENTILE_CONT werken in de tegenovergestelde richting. Gegeven een procent rang, vind de waarde op die rang., Ze verschillen in dat PERCENTILE_DISC een waarde retourneert die in de set bestaat, terwijl PERCENTILE_CONT een exacte waarde berekent als geen van de waarden in de set precies op die rang valt. Je kunt PERCENTILE_CONT gebruiken om een mediaan te berekenen door 0.5 op te geven als percentage. Bijvoorbeeld, welke temperatuur staat op 50% in St. Louis?

De functie PERCENTILE_CONT neemt het gemiddelde van de twee waarden die het dichtst bij het midden liggen, 67 en 69, en gemiddelden ze. PERCENTILE_DISC geeft een exacte waarde terug, 67., Merk ook op dat deze twee functies een extra clausule hebben die niet in de andere functies te zien is, binnen de groep, die de volgorde bevat door in plaats van binnen de over-clausule.

samenvatting

Dit artikel is een zeer snel overzicht van T-SQL vensterfuncties. Twee soorten functies werden uitgebracht met SQL Server 2005, de ranking functies en venster aggregaten. Met 2012 hebt u window aggregate verbeterd met framing en de analytische functies. Ik wil de analytische functies scheiden in twee groepen, de offset en statistische functies., Window functies maken veel query ‘ s gemakkelijker te schrijven, en ik geloof dat is het belangrijkste voordeel. In sommige gevallen, De queries zal beter presteren, ook, maar dat is een discussie voor een andere dag.

Ik hoop dat dit artikel u heeft geïnspireerd om meer te leren over deze fantastische functies!

Redgate Hub