RANK-funktionen i SQL Server er en slags Ranking-funktion. Denne funktion vil tildel nummeret til hver række inden for partitionen af et output . Den tildeler rangen til hver række som én plus den foregående rækkerangering. Når RANK-funktionen finder to værdier, der er identiske inden for den samme partition, tildeler den dem det samme rangnummer. Derudover vil det næste nummer i ranglisten være den forrige rang plus dubletnumre. Derfor tildeler denne funktion ikke altid rækkefølgen af rækker i fortløbende rækkefølge.
RANK-funktionen er også en underdel af vinduesfunktioner. Følgende punkter skal huskes, når du bruger denne funktion :
- Det fungerer altid med OVER()-sætningen.
- Den tildeler en rang til hver række baseret på ORDER BY-sætningen.
- Den tildeler en rang til hver række i fortløbende rækkefølge.
- Den tildeler altid en rang til rækker, startende med en for hver ny partition.
BEMÆRK: Rank tildeler midlertidige værdier for rækker i partitionen, når forespørgslen udføres.
SQL Server giver følgende rangfunktioner :
- RANG()
- ROW_NUMBER()
- DENSE_RANK()
- NTILE()
Lad os lære hver rangfunktion i detaljer. Først vil vi oprette en tabel til demonstration af alle disse funktioner. Følgende udsagn opretter en tabel med navnet rank_demo med tre kolonner:
CREATE TABLE rank_demo ( first_name VARCHAR(60), last_name VARCHAR(40), city VARCHAR(30) );
Dernæst vil vi indsætte nogle rækker i denne tabel som nedenfor:
INSERT INTO rank_demo (first_name, last_name, city) VALUES ('Luisa', 'Evans', 'Texas'), ('Paul', 'Ward', 'Alaska'), ('Peter', 'Bennett', 'California'), ('Carlos', 'Patterson', 'New York'), ('Rose', 'Huges', 'Florida'), ('Marielia', 'Simmons', 'Texas'), ('Antonio', 'Butler', 'New York'), ('Diego', 'Cox', 'California');
Vi kan verificere tabellen ved hjælp af SELECT-sætningen. Det viser nedenstående output:
RANK() Funktion
Denne funktion bruges til at bestemme rangeringen for hver række i resultatsættet. Følgende syntaks illustrerer brugen af en RANK-funktion i SQL Server:
SELECT column_name RANK() OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'my_rank' FROM table_name;
I denne syntaks,
- Det OVER-klausul indstiller partitioneringen og rækkefølgen af et resultat, før den tilknyttede vinduesfunktion anvendes.
- Det PARTITION BY klausul deler output produceret af FROM-sætningen i partitionen. Derefter anvendes funktionen på hver partition og re-initialiseret, når divisionsgrænsen krydser partitioner. Hvis vi ikke har defineret denne klausul, vil funktionen behandle alle rækker som en enkelt partition.
- Det BESTIL EFTER er en påkrævet klausul, der bestemmer rækkefølgen af rækkerne på en faldende eller stigende måde baseret på et eller flere kolonnenavne, før funktionen anvendes.
Eksempel
Lad os se, hvordan RANK()-funktionen fungerer i SQL Server. Nedenstående erklæring vil bruge rangfunktionen til at tildele nummerering for hver række:
java sort arraylist
SELECT first_name, last_name, city, RANK () OVER (ORDER BY city) AS Rank_No FROM rank_demo;
Da vi ikke har brugt PARTITION BY klausul , behandlede funktionen hele resultatet som en enkelt partition. Udførelse af sætningen vil vise nedenstående output:
I dette output kan vi se, at nogle af rækkerne får samme rang, fordi de har samme værdi i by kolonne . Og det næste nummer i ranglisten vil være dets tidligere rangering plus et antal duplikerede tal.
Følgende udsagn er et andet eksempel hvor vi skal bruge en partition by-klausul, der vil opdele rækkerne baseret på by kolonne og tildel en rangering til hver række i en partition. Rækkefølgen af output er baseret på fornavn :
SELECT first_name, last_name, city, RANK () OVER (PARTITION BY city ORDER BY first_name) AS Rank_No FROM rank_demo;
Det vil vise nedenstående output:
ROW_NUMBER() Funktion
Denne funktion bruges til returnere det unikke sekventielle nummer for hver række inden for dens partition. Rækkenummereringen begynder ved én og stiger med én, indtil partitionens samlede antal rækker er nået. Det vil returnere de forskellige rækker for rækken med lignende værdier, der gør den forskellig fra RANK()-funktionen.
Nedenstående syntaks illustrerer brugen af en ROW_NUMBER() funktion i SQL Server:
SELECT column_name ROW_NUMBER() OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'rank_name' FROM table_name;
Eksempel
Udfør følgende forespørgsel for at tildele et sekvensnummer til hver række:
SELECT first_name, last_name, city, ROW_NUMBER() OVER(ORDER BY city) AS my_rank FROM rank_demo;
Det vil tildele rangeringen for tabellen i henhold til deres by . Her kan vi se, at den tildeler forskellige rækker til rækken, som har de samme byværdier.
Hvis vi ændrer sorteringsrækkefølgen fra stigende til faldende med ORDER BY-klausulen vil denne funktion også ændre RANK tilsvarende. Se nedenstående erklæring:
SELECT first_name, last_name, city, ROW_NUMBER() OVER(ORDER BY city DESC) AS my_rank FROM rank_demo;
Her er resultatet:
DENSE_RANK() Funktion
Denne funktion tildeler en unik rang for hver række i en partition i henhold til den angivne kolonneværdi uden huller. Den angiver altid rangering i fortløbende rækkefølge. Hvis vi får en dobbeltværdi , vil denne funktion tildele den samme rang, og den næste rang er det næste fortløbende nummer. Denne karakteristik adskiller DENSE_RANK()-funktionen fra RANK()-funktionen.
Nedenstående syntaks illustrerer brugen af en DENSE_RANK() funktion i SQL Server:
SELECT column_name DENSE_RANK() OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'rank_name' FROM table_name;
Eksempel
Følgende forespørgsel bruger funktionen DENSE_RANK() til at tildele et rangnummer for hver række:
tostring metode
SELECT first_name, last_name, city, DENSE_RANK() OVER(ORDER BY city) AS my_rank FROM rank_demo;
Det vil returnere nedenstående output, hvor vi kan se, at de duplikerede værdier har samme rang, og den følgende rang vil være det næste sekventielle nummer.
Det er endnu et eksempel på DENSE_RANK()-funktionen ved at bruge PARTITION BY-sætningen. Denne klausul opdeler rækkerne baseret på bykolonnen, og rækkefølgen af et resultatsæt er baseret på fornavnet:
SELECT first_name, last_name, city, DENSE_RANK() OVER(PARTITION BY city ORDER BY first_name) AS my_rank FROM rank_demo;
Vi får nedenstående output, fordi ikke to navne er ens. I dette tilfælde svarer outputtet til RANK()-funktionen.
Lad os opdatere navnet med følgende forespørgsel:
Update rank_demo set first_name = 'Diego' WHERE city = 'California'
Udfør nu den samme forespørgsel igen. Vi vil se, at denne tabel fik det samme navn Californien by . Derfor er rang også den samme for begge navne.
NTILE(N) Funktion
Denne funktion bruges til fordele rækker af en ordnet partition i et foruddefineret antal (N) af omtrent lige store grupper. Hver rækkegruppe får sin rangering baseret på den definerede betingelse og begynder at nummerere fra én gruppe. Den tildeler en spand nummer for hver række i en gruppe, der repræsenterer den gruppe, den tilhører.
Følgende syntaks illustrerer brugen af en NTILE()-funktion i SQL Server:
SELECT column_name NTILE(N) OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'my_rank' FROM table_name;
Eksempel
Følgende forespørgsel bruger funktionen NTILE() til at tildele et rangnummer for hver række:
SELECT first_name, last_name, city, NTILE(3) OVER(ORDER BY city) AS my_rank FROM rank_demo;
Den angivne tabel har otte poster . Derfor er NTILE(3) fortæller, at resultatsættet skal have en gruppe på tre plader . Udførelse af sætningen vil vise nedenstående output:
Denne artikel vil forklare alle de RANK-funktioner, der bruges i SQL Server med forskellige eksempler. Det viser også de vigtigste forskelle mellem disse funktioner. Disse funktioner er meget nyttige for udviklere til at udforske og administrere deres data godt.