Chapter 04 - Ranking Functions



Funksionet e Rankimit

Ranking functions.
SQL Server ka kater funksionet rankimi:

  1. RowNumber
  2. RANK
  3. DENSE_RANK
  4. NTILE
Funksionet e rankimit na krijojne mundesi te numerojme rreshtat e nje rezultati, duke shtuar nje kollone te ngjashme me numrin rendor ne bashkesine e rreshtave te rezultatit.

Funksioni ROW_NUMBER()

Sintaksa e funksionit ROW_NUMBER eshte

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

Funksioni  gjeneron nje numer rendor per rreshtat e rezultatit te nje komande.
Numri rendor nis nga numri nje dhe rritet me nga nje duke u bazuar ne klauzolen e renditjes <order_by_clause>.
Nepermjet klauzoles se renditjes te funksionit ROW_NUMBER percaktojme se si do te renditen rreshtat dhe mbi bazen e kesaj renditje do te vendoset numri rendor.
Klauzola e particionimit eshte opsionale 

SELECT TOP 2 * FROM (
SELECT ROW_NUMBER() OVER (Order By Birthdate DESC) as 'Nr', 
EmployeeID,FirstName,LastName,BirthDate
FROM Employees
) EMP WHERE nR>7


Ushtrim - Te afishojme pese porosite e dyta me vleren me te madhe financiare si me poshte

 Nr OrderId Orderdate Firstname Lastname CompanyName Amount
 6 .. .. .. .. .. 100,000
 7  ..  ..  ..  .. .. 90,000
 8  ..  ..  ..  ..  .. 85,000
 9  ..  ..  ..  ..  .. 75,000
 10  ..  ..  ..  ..  .. 43,000


Zgjidhje


select top 5 * from 
(
SELECT     ROW_NUMBER() 
OVER ( 
ORDER BY SUM([Order Details].UnitPrice* [Order Details].Quantity) 
DESC) as Nr ,
Orders.OrderID, Orders.OrderDate, Employees.FirstName, Employees.LastName, 
Customers.CompanyName, 
SUM([Order Details].UnitPrice* [Order Details].Quantity) as Amount
FROM         Customers INNER JOIN
                      Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN
                      Employees ON Orders.EmployeeID = Employees.EmployeeID INNER JOIN
                      [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY      Orders.OrderID, Orders.OrderDate, Employees.FirstName, Employees.LastName, 
Customers.CompanyName
)  shitje
where shitje.Nr>5



Ushtrim - Te afishohen shitjet(numri i porosive) e punonjesve sipas shtetit. 
Te afishohen vetem 5 rreshtat e dyte ne renditjen sipas numrin te porosive

 Nr    Shteti Emri Mbiemri Shitje
 6    100
 7    23
8    22
 9    22
 10    14


Zgjidhje

select top 5 * from 
(
SELECT    ROW_NUMBER() over (order by count(Orders.OrderID) DESC) as nr,
Orders.ShipCountry, Employees.FirstName, Employees.LastName, count(Orders.OrderID) AS shitje
FROM         Orders INNER JOIN
                      Employees ON Orders.EmployeeID = Employees.EmployeeID
                      
group by  Orders.ShipCountry, Employees.FirstName, Employees.LastName  
) as shitje
where shitje.nr>5                   


Ushtrime te tjera


SELECT ROW_NUMBER() OVER (ORDER by OrderId,ProductID) 'Nr',*
FROM [Order Details]


SELECT ROW_NUMBER() OVER (PARTITION BY ProductId ORDER by OrderId,ProductID) 'Nr',*
FROM [Order Details]

-- Orders with row number ordered by date with employee name
SELECT Row_Number() over (order by Orderdate asc) 'Nr',OrderID,OrderDate,
TitleOfCourtesy+' '+FirstName+' '+LastName 'Employee', CustomerID,ShipCountry
 FROM Orders,Employees
where 
Orders.EmployeeID=Employees.EmployeeID
and
OrderDate IS NOT NULL


-- Orders with row number ordered by ShipCountry with employee name
SELECT Row_Number() over (order by ShipCountry asc) 'Nr',OrderID,OrderDate,
TitleOfCourtesy+' '+FirstName+' '+LastName 'Employee', CustomerID,ShipCountry
 FROM Orders,Employees
where 
Orders.EmployeeID=Employees.EmployeeID
and
ShipCountry IS NOT NULL

-- PARTITION CLAUSE
-- Orders with row number ordered by ShipCountry with employee name , paritioned by shicpountry
SELECT Row_Number() over (PARTITION BY ShipCountry order by ShipCountry asc) 'Nr',OrderID,OrderDate,
TitleOfCourtesy+' '+FirstName+' '+LastName 'Employee', CustomerID,ShipCountry
 FROM Orders,Employees
where 
Orders.EmployeeID=Employees.EmployeeID
and
ShipCountry IS NOT NULL


-- PARTITION CLAUSE
-- Orders with row number ordered by Orderdate with employee name
-- partitioned by shipcountry
SELECT Row_Number() over (PARTITION BY ShipCountry order by Orderdate asc) 'Nr',OrderID,OrderDate,
TitleOfCourtesy+' '+FirstName+' '+LastName 'Employee', CustomerID,ShipCountry
 FROM Orders,Employees
where 
Orders.EmployeeID=Employees.EmployeeID
and
OrderDate IS NOT NULL
and 
ShipCountry IS NOT NULL


PAGING EXAMPLE
-- paging through row_number and top
--ROWS 10 TO 15
SELECT TOP 5  * FROM 
(
SELECT Row_Number() over (order by Orderdate asc) 'Nr',OrderID,OrderDate,
TitleOfCourtesy+' '+FirstName+' '+LastName 'Employee', CustomerID,ShipCountry
 FROM Orders,Employees
where 
Orders.EmployeeID=Employees.EmployeeID
and
OrderDate IS NOT NULL
) porosite
WHERE Nr>10

-- paging through row_number and top
--ROWS 16-20
SELECT TOP 5  * FROM 
(
SELECT Row_Number() over (order by Orderdate asc) 'Nr',OrderID,OrderDate,
TitleOfCourtesy+' '+FirstName+' '+LastName 'Employee', CustomerID,ShipCountry
 FROM Orders,Employees
where 
Orders.EmployeeID=Employees.EmployeeID
and
OrderDate IS NOT NULL
) porosite
WHERE Nr>15

Funksioni RANK 

-- Nese ne kollonen e rankimit nuk ka vlera qe perseriten
-- atehere RANK ka te njejtin efekt si ROW_NUMBER

-- Per shembull komandat me poshte prodhojne te njejtin rezultat:

SELECT ROW_NUMBER() OVER (ORDER BY OrderId) AS 'Nr',*
FROM Orders

SELECT RANK() OVER (ORDER BY OrderId) AS 'Nr',*
FROM Orders


-- pasi ne tabelen orders nuk ka vlera qe perseriten ne kollonen OrderID
-- Kollona OrderId sherben si celes primar i tabeles
-- Ndersa komandat me poshte kane efekte te ndryshem

SELECT ROW_NUMBER() OVER (ORDER by OrderId) 'Nr',*
FROM [Order Details]


SELECT RANK() OVER (ORDER by OrderId) 'Nr',*
FROM [Order Details]

-- gjithashtu edhe komandat me poshte kane efekt te ndryshem
SELECT ROW_NUMBER() OVER ( PARTITION BY ShipCountry ORDER by Orders.OrderId) 'Nr',
Orders.OrderID,ProductID,UnitPrice,Quantity,ShipCountry,OrderDate
FROM [Order Details] INNER JOIN Orders
ON [Order Details].OrderID=Orders.OrderID

dhe

SELECT RANK() OVER ( PARTITION BY ShipCountry ORDER by Orders.OrderId) 'Nr',
Orders.OrderID,ProductID,UnitPrice,Quantity,ShipCountry,OrderDate
FROM [Order Details] INNER JOIN Orders
ON [Order Details].OrderID=Orders.OrderID


Funksioni DENSE_RANK


Nese nuk duam boshlleqe ne sekuence mund te perdorim DENSE RANK
-- Vini re efektin e komandave te meposhtme

SELECT ROW_NUMBER() OVER (ORDER by OrderId) 'Nr',*
FROM [Order Details]

SELECT RANK() OVER (ORDER by OrderId) 'Nr',*
FROM [Order Details]

SELECT DENSE_RANK() OVER (ORDER by OrderId) 'Nr',*
FROM [Order Details]

-- E njejta gje ndodh edhe kur kemi particionim te rezultateve

SELECT ROW_NUMBER() OVER ( PARTITION BY ShipCountry ORDER by Orders.OrderId) 'Nr',
Orders.OrderID,ProductID,UnitPrice,Quantity,ShipCountry,OrderDate
FROM [Order Details] INNER JOIN Orders
ON [Order Details].OrderID=Orders.OrderID

SELECT RANK() OVER ( PARTITION BY ShipCountry ORDER by Orders.OrderId) 'Nr',
Orders.OrderID,ProductID,UnitPrice,Quantity,ShipCountry,OrderDate
FROM [Order Details] INNER JOIN Orders
ON [Order Details].OrderID=Orders.OrderID

SELECT DENSE_RANK() OVER ( PARTITION BY ShipCountry ORDER by Orders.OrderId) 'Nr',
Orders.OrderID,ProductID,UnitPrice,Quantity,ShipCountry,OrderDate
FROM [Order Details] INNER JOIN Orders
ON [Order Details].OrderID=Orders.OrderID

Funksioni NTILE

-- perdoret per te ndare nej bashkesi rreshtash ne disa grupe me 
-- numer te barabarte rreshtash
-- per shembull


SELECT NTILE(4) OVER (ORDER by OrderId) 'Nr',*
FROM [Order Details]



--  shembull 
SELECT NTILE(4) OVER 
(Partition by EmployeeId ORDER by OrderId),* 
FROM Orders



--  funksionet e rankimit mund te koekzistojne  brenda te njejtes komande
--Nese kjo gje ka kuptim dhe eshte e nevojshme
SELECT NTILE(10) OVER 
( ORDER by OrderId) 'ntile',ROW_NUMBER() over (order by orderid) 'NR',
RANK() over (order by orderid) 'DR',
DENSE_RANK() over (order by orderid) 'DR',* 
FROM Orders