SQL
Functies (analytisch)

Invoering

Syntaxis

1. FIRST_VALUE (scalar_expression) OVER ([partition_by_clause] order_by_clause [rijen_bereik_claus])
2. LAST_VALUE (scalar_expression) OVER ([partition_by_clause] order_by_clause [rijen_bereik_clause])
3. LAG (scalar_expression [, offset] [, standaard]) OVER ([partition_by_clause] order_by_clause)
4. LEAD (scalar_expression [, offset], [standaard]) OVER ([partition_by_clause] order_by_clause)
5. PERCENT_RANK () OVER ([partition_by_clause] order_by_clause)
6. CUME_DIST () OVER ([partition_by_clause] order_by_clause)
7. PERCENTILE_DISC (numeric_literal) BINNEN GROEP (BESTELLEN OP order_by_expression [ASC | DESC]) OVER ([<partition_by_clause>])
8. PERCENTILE_CONT (numeric_literal) BINNEN GROEP (BESTELLEN OP order_by_expression [ASC | DESC]) OVER ([<partition_by_clause>])

FIRST_VALUE

U gebruikt de functie FIRST_VALUE om de eerste waarde in een geordende resultatenset te bepalen, die u identificeert met een scalaire uitdrukking.

SELECT StateProvinceID, Name, TaxRate,
       FIRST_VALUE(StateProvinceID)
        OVER(ORDER BY TaxRate ASC) AS FirstValue
FROM SalesTaxRate;

In dit voorbeeld wordt de functie FIRST_VALUE gebruikt om de ID van de staat of provincie met het laagste belastingtarief te retourneren. De OVER clausule wordt gebruikt om de belastingtarieven te bestellen om het laagste tarief te verkrijgen.

LAST_VALUE

De functie LAST_VALUE biedt de laatste waarde in een geordende resultatenset, die u opgeeft met een scalaire uitdrukking.

SELECT TerritoryID, StartDate, BusinessentityID,
       LAST_VALUE(BusinessentityID) 
        OVER(ORDER BY TerritoryID) AS LastValue
FROM SalesTerritoryHistory;

In dit voorbeeld wordt de functie LAST_VALUE gebruikt om de laatste waarde voor elke rijenet in de geordende waarden te retourneren.

LAG en LEIDEN

De LAG functie biedt gegevens over rijen vóór de huidige rij in dezelfde resultatenset. In een SELECT instructie kunt u bijvoorbeeld waarden in de huidige rij vergelijken met waarden in een vorige rij.

U gebruikt een scalaire uitdrukking om de waarden op te geven die moeten worden vergeleken. De offset-parameter is het aantal rijen vóór de huidige rij die in de vergelijking worden gebruikt. Als u het aantal rijen niet opgeeft, wordt de standaardwaarde van één rij gebruikt.

De standaardparameter geeft de waarde aan die moet worden geretourneerd wanneer de uitdrukking bij offset een NULL waarde heeft. Als u geen waarde opgeeft, wordt de waarde NULL geretourneerd.

De functie LEAD biedt gegevens over rijen na de huidige rij in de rijverzameling. In een SELECT instructie kunt u bijvoorbeeld waarden in de huidige rij vergelijken met waarden in de volgende rij.

U geeft de waarden op die moeten worden vergeleken met behulp van een scalaire uitdrukking. De offset-parameter is het aantal rijen na de huidige rij dat in de vergelijking wordt gebruikt.

U geeft de waarde op die moet worden geretourneerd wanneer de uitdrukking bij offset een NULL waarde heeft met de standaardparameter. Als u deze parameters niet opgeeft, wordt de standaardwaarde van één rij gebruikt en wordt de waarde NULL geretourneerd.

SELECT BusinessEntityID, SalesYTD,
       LEAD(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lead value",
       LAG(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lag value"
FROM SalesPerson;

In dit voorbeeld worden de functies LEAD en LAG gebruikt om de verkoopwaarden voor elke medewerker tot nu toe te vergelijken met die van de hierboven en hieronder vermelde medewerkers, met records die zijn geordend op basis van de BusinessEntityID-kolom.

PERCENT_RANK en CUME_DIST

De functie PERCENT_RANK berekent de rangorde van een rij ten opzichte van de ingestelde rij. Het percentage is gebaseerd op het aantal rijen in de groep die een lagere waarde hebben dan de huidige rij.

De eerste waarde in de resultatenset heeft altijd een percentage van nul. De waarde voor de hoogste - of laatste - waarde in de set is altijd één.

De functie CUME_DIST berekent de relatieve positie van een opgegeven waarde in een groep waarden, door het percentage waarden kleiner dan of gelijk aan die waarde te bepalen. Dit wordt de cumulatieve verdeling genoemd.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
PERCENT_RANK() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
       AS "Percent Rank",
CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
       AS "Cumulative Distribution"
FROM Employee;

In dit voorbeeld gebruikt u een ORDER clausule om de rijen op te halen die zijn opgehaald met de SELECT instructie op basis van de functiebenamingen van werknemers, waarbij de resultaten in elke groep zijn gesorteerd op basis van het aantal uren ziekteverlof dat werknemers hebben gebruikt.

De functie PERCENT_RANK rangschikt de items binnen elke groep. Voor elk item retourneert het het percentage items in dezelfde groep met lagere waarden.

De functie CUME_DIST is vergelijkbaar, behalve dat het percentage waarden kleiner dan of gelijk aan de huidige waarde wordt geretourneerd.

PERCENTILE_DISC en PERCENTILE_CONT

De functie PERCENTILE_DISC geeft de waarde weer van het eerste item waarbij de cumulatieve verdeling hoger is dan het percentiel dat u opgeeft met de parameter numeric_literal .

De waarden zijn gegroepeerd per rijen of partitie, zoals gespecificeerd door de clausule WITHIN GROUP .

De functie PERCENTILE_CONT is vergelijkbaar met de functie PERCENTILE_DISC , maar retourneert het gemiddelde van de som van het eerste overeenkomende item en het volgende item.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
       CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
       AS "Cumulative Distribution",
       PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours)
          OVER(PARTITION BY JobTitle) AS "Percentile Discreet"
FROM Employee;

Om de exacte waarde uit de rij te vinden die overeenkomt met of groter is dan het 0,5-percentiel, geeft u het percentiel door als het numerieke PERCENTILE_DISC in de functie PERCENTILE_DISC . De kolom Percentiel discreet in een resultatenset geeft de waarde weer van de rij waarbij de cumulatieve verdeling hoger is dan het opgegeven percentiel.

Om de berekening op een reeks waarden te baseren, gebruikt u de functie PERCENTILE_CONT . De kolom "Percentiel continu" in de resultaten bevat de gemiddelde waarde van de som van de resultaatwaarde en de op één na hoogste overeenkomende waarde.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
       CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
       AS "Cumulative Distribution",
       PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours) 
          OVER(PARTITION BY JobTitle) AS "Percentile Discreet",
       PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY SickLeaveHours) 
          OVER(PARTITION BY JobTitle) AS "Percentile Continuous"
FROM Employee;