RANK-, AGGREGATE- ja OFFSET-funktioiden käyttäminen

  • 6 minuuttia

Ikkunatoiminnoissa voit käyttää koostefunktioita, kuten SUM, MIN ja MAX, suorittaaksesi joukon rivejä, jotka on määritetty OVER-lauseessa ja sen argumenteissa.

Ikkunafunktiot voidaan luokitella seuraavasti:

  • Koostefunktiot. Kuten SUM, AVG ja COUNT, jotka toimivat ikkunassa ja palauttavat skalaariarvon.
  • Sijoitusfunktiot. Esimerkiksi RANK, ROW_NUMBER ja NTILE. Sijoitusfunktiot edellyttävät lajittelujärjestystä ja palauttavat sijoitusarvon osion kullekin riville.
  • Analytiikkafunktiot. esimerkiksi CUME_DIST, PERCENTILE_CONT tai PERCENTILE_DISC. Analytiikkafunktiot laskevat osion arvojen jakauman.
  • Siirtymäfunktiot. Esimerkiksi VIIVE, LIIDI ja LAST_VALUE. Siirtymäfunktiot palauttavat arvoja muilta riveiltä suhteessa nykyisen rivin sijaintiin.

Koostefunktiot

Koostefunktiot palauttavat asioiden kokonaismäärät, keskiarvot tai määrät. Koostefunktiot suorittavat laskutoimituksen ja palauttavat yksittäisen arvon. COUNT(*)-arvoa lukuun ottamatta koostefunktiot eivät laske NULL-arvoja.

Katso seuraavaa koodia, joka soveltaa joitakin yleisiä koostefunktioita tuotetaulukon tuotteiden hintoihin:

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

Tämä palauttaa sarakkeen nimeltä WeightByColor, joka sisältää kaikkien samanväristen tuotteiden kokonaispainon, joka näytetään alla olevassa osittaisessa tulosjoukossa.

Näyttökuva, joka näyttää OVER- ja PARTITION BY Color -lauseen tulokset.

Sijoitusfunktiot

Sijoitusfunktiot määrittävät numeron kullekin riville sen mukaan, mikä on sen sijainti määrittämässäsi järjestyksessä. Järjestys määritetään käyttämällä ORDER BY -lausetta.

Harkitse seuraavaa koodia, joka soveltaa kaikkia neljää luokittelufunktiota tuotteet-taulukon tuotteisiin.

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

Tämä palauttaa kullekin funktiolle sarakkeen, jossa on asianmukainen sijoitusluku.

Näyttökuva, joka näyttää sijoitusfunktioiden tulokset.

Analytiikkafunktiot

Analytiikkafunktiot laskevat riviryhmän perusteella arvon. Analytiikkafunktioiden avulla lasketaan liukuvat keskiarvot, juoksevat summat ja ylimmät N -tulokset. Näitä funktioita ovat esimerkiksi seuraavat:

  • CUME_DIST
  • FIRST_VALUE
  • PERCENT_RANK
  • PERCENTILE_CONT
  • PERCENTIL_DISC

OFFSET-funktiot

Siirtymäfunktioiden avulla voit palauttaa tulosjoukon seuraavia tai aikaisempia rivejä.

Siirtymäfunktiot toimivat sijainnissa, joka on joko suhteessa nykyiseen riviin tai suhteessa ikkunan kehyksen alku- tai loppureunaan. Siirtymäfunktiot ovat seuraavat:

  • LAG ja LEAD – toimivat siirtymällä nykyiseen riviin ja edellyttävät ORDER BY -lausetta.
  • FIRST_VALUE ja LAST_VALUE - toimivat siirtymällä ikkunan kehyksestä. Lag-funktion syntaksi näytetään alla. LEAD-funktio toimii samalla tavalla.
LAG (scalar_expression [,offset] [,default])  
    OVER ( [ partition_by_clause ] order_by_clause )

Seuraavassa koodiesimerkissä LEAD-siirtymäfunktio palauttaa seuraavan vuoden budjettiarvon:

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

LAST_VALUE syntaksi näytetään alla. FIRST_VALUE toimii samalla tavalla.

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

Syntaksi on samantapainen kuin LAG ja LEAD, ja siihen on lisätty rivit/alue-lause.