Ranking functions.
SQL Server ka kater funksionet rankimi:
RowNumber
RANK
DENSE_RANK
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
VIEWS Schemabinding
create view shitjet with schemabinding
as
select Nr,OrderId,OrderDate,Firstname,Lastname,CompanyName,Amount 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 dbo.Customers INNER JOIN
dbo.Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN
dbo.Employees ON Orders.EmployeeID = Employees.EmployeeID INNER JOIN
dbo.[Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.OrderID, Orders.OrderDate, Employees.FirstName, Employees.LastName,
Customers.CompanyName
) t
create view andrewsales with schemabinding
as
select Nr,OrderId,OrderDate,Firstname,Lastname,
CompanyName,Amount from
shitjet
where FirstName='Andrew'