RANK, AGGREGATE és OFF függvények használata Standard kiadás T függvények

  • 6 perc

Az ablakműveletekben az olyan összesítő függvények, mint a SZUM, a MIN és a MAX függvények használhatók az OVER záradék és argumentumai által meghatározott sorok halmazán való működéshez.

Az ablakfüggvények a következőképpen kategorizálhatók:

  • Összesítő függvények. Ilyen például a SZUM, az AVG és a DARAB, amelyek egy ablakban működnek, és skaláris értéket ad vissza.
  • Rangsorolási függvények. Például RANG, ROW_NUMBER és NTILE. A rangsorolási függvények rendezési sorrendet igényelnek, és egy rangsorolási értéket ad vissza a partíció minden sorához.
  • Elemzési függvények. Például CUME_DIST, PERCENTILE_CONT vagy PERCENTILE_DISC. Az elemzési függvények kiszámítják a partíció értékeinek eloszlását.
  • Eltolási függvények. Például LAG, LEAD és LAST_VALUE. Az eltolásfüggvények az aktuális sor helyzetéhez viszonyított értékeket ad vissza más sorokból.

Aggregátumfüggvények

Az összesítő függvények összegeket, átlagokat vagy dolgok számát ad vissza. Az összesítő függvények számítást végeznek, és egyetlen értéket adnak vissza. A DARAB(*) kivételével az összesítő függvények nem számolják a NULL értékeket.

Vegye figyelembe a következő kódot, amely néhány általános összesítő függvényt alkalmaz a termékek árára a termékek táblájában:

SELECT Name, ProductNumber, Color, SUM(Weight) 
OVER(PARTITION BY Color) AS WeightByColor
FROM SalesLT.Product
ORDER BY ProductNumber;

Ez egy WeightByColor nevű oszlopot ad vissza, amely az összes ugyanolyan színű termék teljes súlyát tartalmazza, mint az alábbi részleges eredményhalmazban.

A screenshot showing results from the OVER and PARTITION BY Color clause.

Rangsorolási függvények

A rangsorolási függvények számokat rendelnek az egyes sorokhoz a megadott sorrendben elfoglalt helyüktől függően. A rendelés az ORDER BY záradékkal van megadva.

Vegye figyelembe az alábbi kódot, amely mind a négy rangsorolási függvényt alkalmazza a termékek táblában lévő termékekre.

SELECT productid, name, listprice 
    ,ROW_NUMBER() OVER (ORDER BY productid) AS "Row Number"  
    ,RANK() OVER (ORDER BY listprice) AS PriceRank  
    ,DENSE_RANK() OVER (ORDER BY listprice) AS "Dense Rank"  
    ,NTILE(4) OVER (ORDER BY listprice) AS Quartile  
FROM SalesLT.Product

Ez egy oszlopot ad vissza az egyes függvényekhez a megfelelő rangsorszámmal.

A screenshot showing results from ranking functions.

Elemzési függvények

Az elemzési függvények egy sorcsoport alapján kiszámítanak egy értéket. Az elemzési függvények a mozgó átlagok, a futó összegek és a top-N eredmények kiszámítására szolgálnak. Ezek a függvények a következők:

  • CUME_DIST
  • FIRST_VALUE
  • PERCENT_RANK
  • PERCENTILE_CONT
  • PERCENTIL_DISC

KI Standard kiadás T függvények

Az eltolásfüggvények lehetővé teszik egy eredményhalmaz későbbi vagy korábbi sorainak visszaadását.

Az eltolásfüggvények az aktuális sorhoz képest vagy az ablakkeret kezdő vagy záró határához viszonyítva működnek. Az eltolási függvények a következők:

  • LAG és LEAD – az aktuális sor eltolásán kell dolgoznia, és meg kell követelnie az ORDER BY záradékot.
  • FIRST_VALUE és LAST_VALUE - az ablakkeret eltolásán működnek. A LAG függvény szintaxisa alább látható. A LEAD függvény ugyanúgy működik.
LAG (scalar_expression [,offset] [,default])  
    OVER ( [ partition_by_clause ] order_by_clause )

A következő kód példában az ÉRDEKLŐDŐ eltolás függvény a következő év költségvetési értékét adja vissza:

SELECT [Year], Budget, LEAD(Budget, 1, 0) OVER (ORDER BY [Year]) AS 'Next'
    FROM dbo.Budget
    ORDER BY [Year];

A LAST_VALUE szintaxisa alább látható. FIRST_VALUE ugyanúgy működik.

LAST_VALUE ( [ scalar_expression ] )  
OVER ( [ partition_by_clause ] order_by_clause rows_range_clause )

A szintaxis hasonló a LAG és az ÉRDEKLŐDŐ függvényhez, a sorok/tartomány záradék hozzáadásával.