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

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

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