Funcția RANK din SQL Server este un fel de funcție de clasare. Această funcție va atribuiți numărul fiecărui rând din partiția unei ieșiri . Acesta atribuie rangul fiecărui rând ca unul plus rangul rândului anterior. Când funcția RANK găsește două valori care sunt identice în cadrul aceleiași partiții, le atribuie același număr de rang. În plus, următorul număr din clasament va fi rangul anterior plus numerele duplicat. Prin urmare, această funcție nu atribuie întotdeauna clasarea rândurilor în ordine consecutivă.
Funcția RANK este, de asemenea, o sub-parte a funcțiilor ferestrei. Următoarele puncte trebuie reținute atunci când utilizați această funcție :
- Funcționează întotdeauna cu clauza OVER().
- Acesta atribuie un rang fiecărui rând pe baza clauzei ORDER BY.
- Acesta atribuie un rang fiecărui rând în ordine consecutivă.
- Întotdeauna atribuie un rang rândurilor, începând cu unul pentru fiecare partiție nouă.
NOTĂ: Rank atribuie valori temporare pentru rândurile din cadrul partiției atunci când interogarea este executată.
SQL Server oferă următoarele funcții de rang :
- RANG()
- ROW_NUMBER()
- DENSE_RANK()
- NTILE()
Să învățăm fiecare funcție de rang în detaliu. Mai întâi, vom crea un tabel pentru demonstrarea tuturor acestor funcții. Următoarele instrucțiuni creează un tabel numit rank_demo cu trei coloane:
CREATE TABLE rank_demo ( first_name VARCHAR(60), last_name VARCHAR(40), city VARCHAR(30) );
În continuare, vom insera câteva rânduri în acest tabel, după cum urmează:
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');
Putem verifica tabelul folosind instrucțiunea SELECT. Afișează rezultatul de mai jos:
Funcția RANK().
Această funcție este utilizată pentru a determina rangul pentru fiecare rând din setul de rezultate. Următoarea sintaxă ilustrează utilizarea unei funcții RANK în SQL Server:
instanță de java
SELECT column_name RANK() OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'my_rank' FROM table_name;
În această sintaxă,
- The clauza OVER setează partiționarea și ordonarea unui rezultat înainte ca funcția de fereastră asociată să fie aplicată.
- The clauza PARTITION BY împarte rezultatul produs de clauza FROM în partiție. Apoi funcția este aplicată fiecărei partiții și reinițializată atunci când granița diviziei traversează partițiile. Dacă nu am definit această clauză, funcția va trata toate rândurile ca o singură partiție.
- The COMANDA PENTRU este o clauză obligatorie care determină ordinea rândurilor în mod descendent sau ascendent pe baza unuia sau mai multor nume de coloane înainte ca funcția să fie aplicată.
Exemplu
Să vedem cum funcționează funcția RANK() în SQL Server. Declarația de mai jos va folosi funcția de rang pentru a atribui numerotarea fiecărui rând:
SELECT first_name, last_name, city, RANK () OVER (ORDER BY city) AS Rank_No FROM rank_demo;
Din moment ce nu am folosit clauza PARTITION BY , funcția a tratat întregul rezultat ca o singură partiție. Executarea instrucțiunii va afișa rezultatul de mai jos:
În această ieșire, putem vedea că unele dintre rânduri au același rang, deoarece au aceeași valoare în coloana orașului . Iar următorul număr din clasament va fi rangul anterior plus un număr de numere duplicate.
Următoarea afirmație este alt exemplu unde vom folosi o partiție prin clauză care va împărți rândurile pe baza oraș coloană și atribuiți un clasament fiecărui rând dintr-o partiție. Ordinea ieșirii se bazează pe Nume :
SELECT first_name, last_name, city, RANK () OVER (PARTITION BY city ORDER BY first_name) AS Rank_No FROM rank_demo;
Va afișa rezultatul de mai jos:
ROW_NUMBER() Funcție
Această funcție este folosită pentru returnează numărul secvenţial unic pentru fiecare rând în cadrul compartimentării sale. Numerotarea rândurilor începe de la unu și crește cu unu până când se atinge numărul total de rânduri al partiției. Va returna diferitele ranguri pentru rândul având valori similare care îl fac diferit de funcția RANK().
Sintaxa de mai jos ilustrează utilizarea unei funcții ROW_NUMBER() în SQL Server:
SELECT column_name ROW_NUMBER() OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'rank_name' FROM table_name;
Exemplu
Executați următoarea interogare pentru a atribui un număr de secvență pentru fiecare rând:
SELECT first_name, last_name, city, ROW_NUMBER() OVER(ORDER BY city) AS my_rank FROM rank_demo;
Acesta va atribui clasamentul pentru tabel conform lor oraș . Aici putem vedea că atribuie ranguri diferite pentru rândul care are aceleași valori de oraș.
Dacă schimbăm ordinea de sortare de la urcând până la coborând cu clauza ORDER BY, această funcție va modifica și RANK în mod corespunzător. Vezi declarația de mai jos:
SELECT first_name, last_name, city, ROW_NUMBER() OVER(ORDER BY city DESC) AS my_rank FROM rank_demo;
Iată rezultatul:
Funcția DENSE_RANK().
Această funcție atribuie un rang unic pentru fiecare rând dintr-o partiție conform valorii specificate a coloanei, fără goluri. Specifică întotdeauna clasarea în ordine consecutivă. Dacă primim un valoare duplicat , această funcție îi va atribui același rang, iar următorul rang fiind următorul număr secvenţial. Această caracteristică diferă funcția DENSE_RANK() de funcția RANK().
Sintaxa de mai jos ilustrează utilizarea unei funcții DENSE_RANK() în SQL Server:
SELECT column_name DENSE_RANK() OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'rank_name' FROM table_name;
Exemplu
Următoarea interogare folosește funcția DENSE_RANK() pentru a atribui un număr de rang pentru fiecare rând:
SELECT first_name, last_name, city, DENSE_RANK() OVER(ORDER BY city) AS my_rank FROM rank_demo;
Va returna rezultatul de mai jos, unde putem vedea că valorile duplicate au același rang, iar următorul rang va fi următorul număr secvenţial.
Este un alt exemplu de funcție DENSE_RANK() prin utilizarea clauzei PARTITION BY. Această clauză va împărți rândurile în funcție de coloana orașului, iar ordinea unui set de rezultate se bazează pe first_name:
SELECT first_name, last_name, city, DENSE_RANK() OVER(PARTITION BY city ORDER BY first_name) AS my_rank FROM rank_demo;
Vom obține rezultatul de mai jos, deoarece nu există două nume identice. În acest caz, rezultatul este similar cu funcția RANK().
Să actualizăm numele cu următoarea interogare:
Update rank_demo set first_name = 'Diego' WHERE city = 'California'
Acum, executați din nou aceeași interogare. Vom vedea că acest tabel a primit același nume Orașul California . Prin urmare, rangul este același pentru ambele nume.
Funcția NTILE(N).
Această funcție este folosită pentru distribuiți rândurile unei partiții ordonate într-un număr predefinit (N) din grupuri aproximativ egale. Fiecare grup de rânduri își primește rangul în funcție de condiția definită și începe numerotarea dintr-un grup. Se atribuie a numărul găleții pentru fiecare rând dintr-un grup reprezentând grupul căruia îi aparține.
Următoarea sintaxă ilustrează utilizarea unei funcții NTILE() în SQL Server:
SELECT column_name NTILE(N) OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'my_rank' FROM table_name;
Exemplu
Următoarea interogare folosește funcția NTILE() pentru a atribui un număr de rang pentru fiecare rând:
SELECT first_name, last_name, city, NTILE(3) OVER(ORDER BY city) AS my_rank FROM rank_demo;
Tabelul specificat are opt înregistrări . De aceea NTILE(3) spune că setul de rezultate trebuie să aibă a grup de trei înregistrări . Executarea instrucțiunii va afișa rezultatul de mai jos:
Acest articol va explica toate funcțiile RANK utilizate în SQL Server cu diverse exemple. De asemenea, arată principalele diferențe dintre aceste funcții. Aceste funcții sunt foarte utile pentru dezvoltatori pentru a-și explora și gestiona bine datele.