Situatie
Una dintre functiile nedreptatite din sql server (in sensul ca este foarte putin cunoscuta si folosita) este NTILE().
Daca primim o cerinta de la departamentele de business in care ni se cere sa impartim salariatii in 3 echipe in fuctie de topul salariilor (fara sa tinem cont de departamente), am putea sa realizam acest lucru folosind tabele temporare sau un cursor.
O varianta mult mai eleganta si mai simpla este insa functia NTILE().
Aceasta functie primeste si ea doi parametrii:
– primul este un intreg si reprezinta numarul de partitionari dorite (ca sa fie mai clar reprezinta in cate echipe sa imparta numarul de angajati din tabela test_angajati)
– al doilea parametru este o coloana (sau mai multe) din tabela si reprezinta coloana (sau coloanele) de care sa tina cont cand face aceasta partitionare.
Pentru a fi foarte clar, in scriptul de mai jos am creat o tabela in care am inserat valori:
drop table test_angajati
create table test_angajati
(
id int identity (1,1),
nume varchar(max),
departament varchar(max),
varsta int,
salariu int
)
insert into test_angajati
values (‘Georgescu Mihai’, ‘IT’,24, 2400)
insert into test_angajati
values (‘Popescu Ionel’, ‘Contabilitate’,70, 7000)
insert into test_angajati
values (‘Popescu Mihai’, ‘Distributie’,45, 4500)
insert into test_angajati
values (‘Marinescu Elena’, ‘Contabilitate’,56, 6900)
insert into test_angajati
values (‘Calimanescu Geo’, ‘IT’,24, 2400)
insert into test_angajati
values (‘Eminescu Mihai’, ‘IT’,37, 3800)
insert into test_angajati
values (‘Ionescu Bogdan’, ‘Distributie’,38, 3800)
insert into test_angajati
values (‘Ionescu Emil’, ‘Contabilitate’,70, 7000)
insert into test_angajati
values (‘Ionescu Costin’, ‘Contabilitate’,69, 6900)
insert into test_angajati
values (‘Ionescu Gogu’, ‘Contabilitate’,20, 4300)
select * from test_angajati
id nume departament varsta salariu
1 Georgescu Mihai IT 24 2400
2 Popescu Ionel Contabilitate 70 7000
3 Popescu Mihai Distributie 45 4500
4 Marinescu Elena Contabilitate 56 6900
5 Calimanescu Georgeta IT 24 2400
6 Eminescu Mihai IT 37 3800
7 Ionescu Bogdan Distributie 38 3800
8 Ionescu Emil Contabilitate 70 7000
9 Ionescu Costin Contabilitate 69 6900
10 Ionescu Gogu Contabilitate 20 4300
Solutie
Acum rulam urmatorul select:
SELECT departament, nume, id, varsta, salariu,
NTILE(3) OVER(ORDER BY salariu desc ) AS Echipa
FROM test_angajati
order by salariu desc
Obtinem rezultatul de mai jos:
departament nume id varsta salariu Echipa
Contabilitate Popescu Ionel 2 70 7000 1
Contabilitate Ionescu Emil 8 70 7000 1
Contabilitate Ionescu Costin 9 69 6900 1
Contabilitate Marinescu Elena 4 56 6900 1
Distributie Popescu Mihai 3 45 4500 2
Contabilitate Ionescu Gogu 10 20 4300 2
IT Eminescu Mihai 6 37 3800 2
Distributie Ionescu Bogdan 7 38 3800 3
IT Calimanescu Geo 5 24 2400 3
IT Georgescu Mihai 1 24 2400 3
Observam ca angajatii au fost impartiti in 3 echipe, in functie de topul salariilor, fara sa tina cont de departament. Fiindca sunt doar 10 angajati, prima echipa a fost alcatuita din 4 angajati, iar restul din cate 3.
Functia incearca sa pastreze o proportie echilibrata a echipelor, in cazul in care numarul de angajati nu se imparte exact la primul parametru al functiei.
Sa intram putin mai in detaliu si sa presupunem ca o alta cerinta este ca, in cazul in care doi salariati cad in echipe diferite desi au acelasi salariu, cel care are varsta mai mare (se presupune ca are mai multa experienta) sa fie in echipa cu salarii mai mari.
Putem rezolva aceasta cerinta simplu, adaugand la selectul de mai sus, la functie, in ORDER BY, coloana varsta, sortata desc
SELECT departament, nume, id, varsta, salariu,
NTILE(3) OVER(ORDER BY salariu desc, varsta desc ) AS Echipa
FROM test_angajati
order by salariu desc
Rezultatul obtinut este:
departament nume id varsta salariu Echipa
Contabilitate Popescu Ionel 2 70 7000 1
Contabilitate Ionescu Emil 8 70 7000 1
Contabilitate Ionescu Costin 9 69 6900 1
Contabilitate Marinescu Elena 4 56 6900 1
Distributie Popescu Mihai 3 45 4500 2
Contabilitate Ionescu Gogu 10 20 4300 2
Distributie Ionescu Bogdan 7 38 3800 2
IT Eminescu Mihai 6 37 3800 3
IT Georgescu Mihai 1 24 2400 3
IT Calimanescu Geo 5 24 2400 3
Observam ca, spre deosebire de rezultatul anterior, acum angajatul Ionescu Bogdan, care inainte facea parte din echipa 3, face parte din echipa 2, pentru ca, desi are acelasi salariu ca Eminescu Mihai, are varsta mai mare.
Sa mergem mai departe cu analiza si sa vedem ce se intampla daca cerinta de business e sa impartim angajatii in 4 echipe tinand cont de salariu:
SELECT departament, nume, id, varsta, salariu,
NTILE(4) OVER(ORDER BY salariu desc ) AS Echipa
FROM test_angajati
order by salariu desc
Observam ca functia a impartit angajatii in doua echipe de cate 3 si doua echipe de cate 2 angajati, aceasta fiind cea mai buna proportie pentru a pastra echipele echilibrate ca si numar.
Acum, pentru a complica si a aprofunda exemplul anterior, sa presupunem ca departamentul de business doreste ca, in cazul in care doi angajati cu acelasi salariu cad in echipe diferite, sa nu se mai tina cont de varsta, ci angajatul cel mai vechi in firma (adica cu id-ul mai mic) sa faca parte din echipa cu salarii mai mari
Acest lucru se obtine cu selectul de mai jos:
SELECT departament, nume, id, varsta, salariu,
NTILE(4) OVER(ORDER BY salariu desc, id asc ) AS Echipa
FROM test_angajati
order by salariu desc
departament nume id varsta salariu Echipa
Contabilitate Popescu Ionel 2 70 7000 1
Contabilitate Ionescu Emil 8 70 7000 1
Contabilitate Marinescu Elena 4 56 6900 1
Contabilitate Ionescu Costin 9 69 6900 2
Distributie Popescu Mihai 3 45 4500 2
Contabilitate Ionescu Gogu 10 20 4300 2
IT Eminescu Mihai 6 37 3800 3
Distributie Ionescu Bogdan 7 38 3800 3
IT Georgescu Mihai 1 24 2400 4
IT Calimanescu Geo 5 24 2400 4
Se observa ca acum in prima echipa a intrat Marinescu Elena, care are id-ul mai mic (adica este mai veche in firma) decat Ionescu Costin (indiferent ca el are acelasi salariu ca si ea si ca are varsta mai mare). Cu acest exemplu am vrut sa arat ca functia este foarte flexibila si ca, in cadrul functiei, in ORDER BY putem sa folosim oricate coloane vrem si, important, acestea pot fi ordonate diferit.
Leave A Comment?