SQL
Funktioner (analytisk)

Introduktion

Syntax

1. FIRST_VALUE (skaläruttryck) ÖVER ([partition_by_clause] order_by_clause [rows_range_clause])
2. LAST_VALUE (scalar_xpress) OVER ([partition_by_clause] order_by_clause [rows_range_clause])
3. LAG (skaläruttryck [, offset] [, standard]) ÖVER ([partition_by_clause] order_by_clause)
4. LEAD (skaläruttryck [, offset], [standard]) ÖVER ([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 (numerisk_bibliotek) INOM GRUPP (BESTÄLLNING AV order_by_uttryck [ASC | DESC]) OVER ([<partition_by_clause>])
8. PERCENTILE_CONT (numerisk_bibliotek) INOM GRUPP (BESTÄLLNING AV order_by_uttryck [ASC | DESC]) OVER ([<partition_by_clause>])

FIRST_VALUE

Du använder FIRST_VALUE funktionen för att bestämma det första värdet i en ordnad resultatuppsättning, som du identifierar med hjälp av ett skalärt uttryck.

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

I det här exemplet används FIRST_VALUE funktionen för att returnera ID för staten eller provinsen med den lägsta skattesatsen. OVER klausulen används för att beställa skattesatserna för att få den lägsta skattesatsen.

LAST_VALUE

Funktionen LAST_VALUE tillhandahåller det sista värdet i en ordnad resultatuppsättning, som du anger med hjälp av ett skalärt uttryck.

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

Detta exempel använder LAST_VALUE funktionen för att returnera det sista värdet för varje rader i de ordnade värdena.

LAG och LED

LAG funktionen tillhandahåller data om rader före den aktuella raden i samma resultatuppsättning. I en SELECT sats kan du till exempel jämföra värden i den aktuella raden med värden i en föregående rad.

Du använder ett skalärt uttryck för att ange värden som ska jämföras. Offsetparametern är antalet rader före den aktuella raden som kommer att användas i jämförelsen. Om du inte anger antalet rader används standardvärdet för en rad.

Standardparametern anger värdet som ska returneras när uttrycket vid offset har ett NULL värde. Om du inte anger ett värde returneras ett värde på NULL .

LEAD funktionen ger data om rader efter den aktuella raden i raduppsättningen. I en SELECT sats kan du till exempel jämföra värden i den aktuella raden med värden i följande rad.

Du anger värden som ska jämföras med hjälp av ett skalärt uttryck. Offsetparametern är antalet rader efter den aktuella raden som kommer att användas i jämförelsen.

Du anger värdet som ska returneras när uttrycket vid offset har ett NULL värde med standardparametern. Om du inte anger dessa parametrar används standardraden för en rad och ett värde på NULL returneras.

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;

Detta exempel använder LEAD- och LAG-funktionerna för att jämföra försäljningsvärdena för varje anställd hittills med de för de anställda som listas ovan och nedan, med poster som beställts baserat på kolumnen BusinessEntityID.

PERCENT_RANK och CUME_DIST

Funktionen PERCENT_RANK beräknar rangordningen för en rad i förhållande till raduppsättningen. Procentandelen baseras på antalet rader i gruppen som har ett lägre värde än den aktuella raden.

Det första värdet i resultatuppsättningen har alltid en procentrankning på noll. Värdet för det högst rankade - eller sista - värdet i uppsättningen är alltid ett.

Funktionen CUME_DIST beräknar den relativa positionen för ett specificerat värde i en grupp värden genom att bestämma procentandelen värden som är mindre än eller lika med det värdet. Detta kallas den kumulativa fördelningen.

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;

I det här exemplet använder du en ORDER klausul för att partitionera - eller gruppera - raderna som hämtats med SELECT uttalandet baserat på anställdas jobbtitlar, och resultaten i varje grupp sorteras utifrån antalet sjukfrånvarotimmar som anställda har använt.

Funktionen PERCENT_RANK rangordnar posterna i varje grupp. För varje post returnerar det procentandelen poster i samma grupp som har lägre värden.

Funktionen CUME_DIST är liknande, förutom att den returnerar procentandelen värden som är mindre än eller lika med det aktuella värdet.

PERCENTILE_DISC och PERCENTILE_CONT

Funktionen PERCENTILE_DISC visar värdet på den första posten där den kumulativa fördelningen är högre än den procentil som du tillhandahåller med parametern numeric_literal .

Värdena grupperas efter rader eller partitioner, som specificeras av WITHIN GROUP klausulen.

Funktionen PERCENTILE_CONT liknar funktionen PERCENTILE_DISC , men returnerar PERCENTILE_DISC för summan av den första matchande posten och nästa post.

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;

För att hitta det exakta värdet från raden som matchar eller överstiger 0,5 percentilen passerar du percentilen som numerisk bokstav i funktionen PERCENTILE_DISC . Den diskreta kolumnen i en resultatset visar värdet på den rad där den kumulativa fördelningen är högre än den angivna percentilen.

För att basera beräkningen på en uppsättning värden använder PERCENTILE_CONT funktionen PERCENTILE_CONT . Kolumnen "Procentile Continuous" i resultaten visar medelvärdet för summan av resultatvärdet och nästa högsta matchningsvärde.

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;