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.

OLTP (online transaction preocessing) vs. BI (business intelligence)

De la inceput, sistemele relationale de baze de date au fost utilizate pentru a stoca informatii primare pentru afacere precum comenzile sau facturile utilizand procesarea bazata pe tranzactii. Aceasta orientare pe datele afacerii are avantaje si dezavantaje. Un avantaj este acela ca performanta scazuta a primelor sisteme relationale de baze de date s-a imbunatatit substantial astfel incat, in zilele noastre, multe sisteme relationale de baze de date pot executa zeci de tranzactii pe secunda (bineinteles daca si componentele hardware permit acest lucru). Pe de alta parte, orientarea spre afaceri a sistemelor tranzactionale au prevenit aparitia altor sisteme naturale de baze de date pentru a se analiza si a umple nevoia de informatii din datele existente intro companie sau department.

OLTP (online transaction processing)

Dupa cum am spus deja, performanta este principala problema a sistemelor bazate pe procesarea tranzactionala. Un exemplu tipic al aplicarii acestor sisteme este retragerea de fonduri de la un bancomat (ATM). Catva dintre principalele proprietati ale sistemelor OLTP sunt:

1.       Tranzactii scurte

2.       Multi utilizatori (sute sau chiar mii)

3.       Operatii continue de citire si scriere bazate pe un numar redus de randuri

4.       Date de dimensiuni medii sunt stocate in baza de date

Performanta bazelor de date va creste daca tranzactiile sunt scurte. Motivul este acela ca tranzactiile folosesc lock-uri pentru a preveni posibilul efect negativ  al problemelor de acces concurential. Daca tranzactiile sunt lungi, numarul de lock-uri si durata lor cresc, scazand performanta accesului la date si a performantei pentru alte tranzactii.

Sistemele OLTP mari au ,de obicei, multi utilizatori care acceseaza sistemul simultan. Un exemplu tipic este cel al unui sistem de rezervare a biletelor pentru o companie aeriana, care trebuie sa proceseze zeci de cereri ,aproape imediat, pentru bilete de calatorie intro tara sau pe intreg cuprinsul planetei. In acest tip de sistem, utilizatorii isi doresc ca asteptarile lor legate de timp sa fie satisfacute – sistemul sa raspunda cat mai repede si sa fie disponibil 24 de ore pe zi timp de 7 zile pe saptamana.

Utilizatorii sitemelor OLTP executa instructiuni DML (data manipulation language) in mod continu realizand operatii de citire si scriere in acelasi timp. Pentru ca datele unui asemenea sistem se schimba des se poate spune ca sistemul este foarte dinamic. In mod normal, aceste operatii (sau rezultatul lor) implica o cantitate mica de date, altfel, daca sistemul aceseaza multe randuri va fi nevoie sa fie accesate una sau mai multe tabele din baza de date (ceea ce poate produce lock-uri conducand spre scaderea performantelor).

In utlimii ani, cantitatea de date stocate intro baza de date operationala (baza de date gestionata de un sistem OLTP) a crescut rapid. Astazi, multe baze de date pot stoca pana la cateva zeci sau chiar sute de GB de date. Si dupa cum vom vedea, aceasta cantitate este mica in comparatie cu depozitele de date (data warehouse).

Sistemele de Inteligenta Artificiala (business inteligence)

Inteligenta artificiala (BI – business inteligence) este procesul de integrare a cantitatilor uriase de date intrun singur spatiu de stocare in care utilizatorii sa poata rula interogari si rapoarte pentru a analiza datele existente. Cu alte cuvinte, scopul BI este de a pastra datele care pot fi accesate de utilizatorii care iau decizii pe baza analizelor. Aceste sisteme sunt adesea numite analitice sau informative, pentru ca accesand datele, utilizatorii pot obtine informatii pe baza carora pot lua decizii mai bune.

Scopul sistemelor BI este diferit de scopul sistemelor OLTP. Spre exemplu urmatoarea intrebare este o interogare pentru sistemele  BI: „Care este cea mai bine vanduta categorie de produse pentru fiecare regiune in semestrul al 3-lea din anul 2008 ?”. De aceea, un sistem BI are proprietati diferite fata de cele listate la sistemele OLTP (prezentate mai sus). Cateva dintre cele mai importante proprietati ale sistemelor BI sunt urmatoarele:

1.       Operatiunile de incarcare (load) se bazeaza pe un numar mare de randuri

2.       Numar mic de utilizatori

3.       Cantitati mari de date sunt stocate in baza de date

In afara de incarcarea cu date realizata la intervale regulate (de obicei, zilnic), sistemele BI sunt de obicei, sisteme deschise doar pentru citire (de aceea natura natura datelor intrun asemenea sistem este statica). Datele sunt aduse din surse diferite, curatate (se asigura consistenta) si incarcate intro baza de date numita depozit de date (data warehouse sau data mart). Datele curatate raman, de obicei, nemodificate dar ele pot fi modificate dupa procesul de curatare si inainte de incarcare. Pentru ca sistemele BI sunt utilizate pentru a obtine informatii, numarul utilizatorilor simultani este foarte mic in comparatie cu numarul utilizatorilor care acceseaza simultan un sistem OLTP. De obicei, utilizatorii unui sistem BI genereaza rapoarte care afiseaza diferiti indicatori cu privire la starea financiara a unei intreprinderi (sau departament), sau executa interogari complexe pentru a compara datele.

O alta deosebire intre sistemele OLAP si BI este aceea legata de disponibilitate, in timp ce un sistem OLTP poate fi accesat non – stop, un sistem BI poate fi accesat numai dupa ce acesta este incarcat cu date.

In timp ce un sistem OLTP stocheaza numai datele curente, un sistem BI trebuie sa tina cont si de modificarile istorice (spuneam mai devreme ca un sistem BI realizeaza comparatii intre date, ei bine acest lucru se poate realiza pe perioade diferite de timp). Din acest motiv, cantitatea de date stocata intr-un depozit de date este foarte mare.