Functii de rang (RANK functions)

O data cu SQL Server 2005, Microsoft a introdus o serie de noi functionalitati. Aceste noi functionalitati usureaza munca unui administrator sau dezvoltator in scrierea codului T-SQL si a intretinerii bazelor de date.  In continuare vom discuta despre funtiile de rang. Functiile de rang intorc o valoare rang pentru fiecare rand dintr-un set de date. Aceste functii sunt nondeterministice. Functie de functia utilizata, mai multe randuri pot avea aceeasi valoare sau valori indivituale.

Functiile de rang permite inumararea secventiala a setului de date. Pentru a exemplifica rezultatul executiei acestor functii, se considera tabela si inregistrarile de mai jos:

CREATE TABLE Persoane(
            Nume VARCHAR(50),
            Varsta INT,
            SEX CHAR(1)
)

INSERT INTO Persoane VALUES (‘Ion’,53,’M')
INSERT INTO Persoane VALUES (‘Vasile’,45,’M')
INSERT INTO Persoane VALUES (‘Gheorghe’,89,’M')
INSERT INTO Persoane VALUES (‘Maria’,21,’F')
INSERT INTO Persoane VALUES (‘Stefan’,46,’M')
INSERT INTO Persoane VALUES (‘Adriana’,69,’F')
INSERT INTO Persoane VALUES (‘Mircea’,56,’M')
INSERT INTO Persoane VALUES (‘Mihai’,2,’M')
INSERT INTO Persoane VALUES (‘Daniela’,46,’F')
INSERT INTO Persoane VALUES (‘Cristina’,25,’F')
INSERT INTO Persoane VALUES (‘Andreea’,14,’F')

Transact – SQL furnizeaza urmatoarele functii: RANK(), DENSE_RANK(), NTILE(), ROW_NUMBER()

RANK

Uneori se doreste ca doua sau mai multe randuri care au aceasi clauza de ordonare (order by) ca aibe acelasi rang. Sintaxa generala este:

RANK ( )  OVER ( [ <partition_by_clause> ] <order_by_clause> )

Unde:
[ <partition_by_clause> ] – reprezinta coloana sau coloanele dupa care se creaza grupari in setul de date
<order_by_clause> – reprezinta coloana sau coloanele dupa care se realizeaza ordonarea pentru stabilirea valorilor rang in cadrul partitiei.

Functia RANK inumara secvential valorile din clauza ORDER BY. Cand doua sau mai multe randuri au aceeasi valoare in ORDER BY, primesc acelasi rang. Chiar si in acest caz, valoarea de rang este incrementata iar cand o noua valoare este determinate in ORDER BY, rangul determinat va fi mai mare cu 1 decat numarul de randuri aflat inaintea randului current. In exemplul urmator, s-a aplicat functia RANK peste coloana Varsta.

SELECT      RANK() OVER(ORDER BY Varsta) as RankNumber,
            Nume,
            Varsta
FROM Persoane

Se poate observa ca acolo unde varsta este aceeasi, si valoarea rang este identical. Atunci cand se intalneste o noua varsta, rangul devine numarul de randuri de dinaintea randului current incrementat cu 1. Cu alte cuvinte, Andreea are rangul 3 pentru ca inaintea ei sunt alte 2 persoane care avand aceeasi varsta, au primit acelasi rang.

SELECT      RANK() OVER(PARTITION BY Sex ORDER BY Varsta) as RankNumber,
            Nume,
            Varsta,
            Sex
FROM Persoane

In exemplul 2, setul de date a fost grupat dupa sex iar apoi s-a atribuit valoarea de rang (individual pentru fiecare partitie a setului de date).

 DENSE_RANK

Aceasta functie este similara funtiei RANK cu exceptia faptului ca valorile rang sunt in ordine, nu lipseste niciuna. Cu alte cuvinte, o valoare rang este ori valoarea rang a randului precedent ori valoarea rang a randului precedent incrementat cu 1. Aceste valori sunt in ordine, fara valori “lipsa”.

SELECT      DENSE_RANK() OVER(ORDER BY Varsta) as RankNumber,
            Nume,
            Varsta
FROM Persoane

NTILE

Aceasta functie este similara celorlalte si imparte un set de date in subgrupuri.

SELECT      NTILE(3) OVER(ORDER BY Varsta) as RankNumber,
            Nume,
            Varsta
FROM Persoane

In exemplul de mai sus, setul de date a fost impartit in 3 subgrupuri (3 este valoarea parametrului functiei NTILE). Folosirea acestei functii va avea ca effect divizarea setului de date in subgrupuri de date cu acelasi rang.

ROW_NUMBER

Aceasta functie intoarce o valoare rang data de numarul randului. Fiecare rand din setul de date va fi cu 1 mai mare decat precedentul rand si cu 1 mai mic decat urmatorul rand. Primul rand din setul de date va avea valoarea 1. Acest rang se poate aplica sip e partitii de date ale setului de date (aduca pe grupuri de date).

SELECT      ROW_NUMBER() OVER(ORDER BY Varsta) as RankNumber,
            Nume,
            Varsta
FROM Persoane

In exemplul de mai sus, numerotarea randurilor s-a realizat dupa coloana Varsta. Daca vom dori afisarea setului de date in alta ordine, adica adaugarea clauzei Order by, in acest caz, numai afisarea o sa se realizeze dupa noua ordine, numerotarea randurilor se va realiza dupa clauza order by a functiei ROW_NUMBER.

SELECT      ROW_NUMBER() OVER(ORDER BY Varsta) as RankNumber,
            Nume,
            Varsta
FROM Persoane
ORDER BY NUME

Acum se poate observa ca afisarea s-a realizat dupa noua ordine dar valorile rang sunt aceleasi.