Query Ranking dalam MS SQL Server 2005

Ranking atau peringkat, manusia senang membuat ranking. Dalam MS SQL Server 2005 pun tersedia beberapa fungsi untuk menampilkan hasil query dengan nomor ranking. Untuk contoh-contoh query ranking saya menggunakan Entity Relationship berikut :



Saya membuat view berikut untuk menjumlahkan SubTotal dari setiap order ID :

CREATE VIEW [dbo].[viewSumSubTotalPerOrder]
AS
  SELECT orderid,
         SUM(subtotal) AS total
  FROM   dbo.orderitems
  GROUP  BY orderid 

Untuk menampilkan query ranking, kita bisa menggunakan query ini :

SELECT c.name,
       o.dateordered,
       o.orderid,
       vw.total,
       Row_number() OVER (ORDER BY total DESC) AS bestcustomer
FROM   viewsumsubtotalperorder AS vw
       INNER JOIN orders AS o
         ON o.orderid = vw.orderid
       INNER JOIN customers AS c
         ON c.customerid = o.customerid 

Akan menghasilkan :

Name	DateOrdered	orderId	Total	BestCustomer
Kurniawan	00:00.0	11	450000	1
Slamet	00:00.0	6	240000	2
Kurniawan	00:00.0	10	220000	3
Rangky	00:00.0	3	100000	4
Firman	00:00.0	8	60000	5
Rangky	00:00.0	1	45000	6
Rico	00:00.0	2	45000	7
Slamet	00:00.0	5	40000	8
Fajar	00:00.0	7	30000	9
Firman	00:00.0	9	15000	10
Rico	00:00.0	4	2000	11

Yang ini query ranking yang dibagi-bagi berdasarkan nama customernya :

SELECT c.name,
       o.dateordered,
       o.orderid,
       vw.total,
       Row_number() OVER (PARTITION BY c.customerid ORDER BY total DESC) AS bestcustomer
FROM   viewsumsubtotalperorder AS vw
       INNER JOIN orders AS o
         ON o.orderid = vw.orderid
       INNER JOIN customers AS c
         ON c.customerid = o.customerid 

Menghasilkan :

Name	DateOrdered	orderId	Total	BestCustomer
Rico	00:00.0	2	45000	1
Rico	00:00.0	4	2000	2
Rangky	00:00.0	3	100000	1
Rangky	00:00.0	1	45000	2
Fajar	00:00.0	7	30000	1
Slamet	00:00.0	6	240000	1
Slamet	00:00.0	5	40000	2
Firman	00:00.0	8	60000	1
Firman	00:00.0	9	15000	2
Kurniawan	00:00.0	11	450000	1
Kurniawan	00:00.0	10	220000	2

Tapi kurangnya adalah, hasil ranking ini tidak bisa di masukan didalam kondisi "where" ataupun didalam expresi "order by". Untuk melakukan itu kita harus memasukan dulu ketable terpisah atau masukannya keview.
Berikut contoh querynya :

CREATE VIEW [dbo].[viewBestCustomers]
AS
  SELECT c.name,
         o.dateordered,
         o.orderid,
         vw.total,
         Row_number() OVER (ORDER BY total DESC) AS bestcustomer
  FROM   viewsumsubtotalperorder AS vw
         INNER JOIN orders AS o
           ON o.orderid = vw.orderid
         INNER JOIN customers AS c
           ON c.customerid = o.customerid

SELECT name,
       dateordered,
       orderid,
       total,
       bestcustomer
FROM   viewbestcustomers
WHERE  bestcustomer BETWEEN 3 AND 5 

Name	DateOrdered	orderId	Total	BestCustomer
Kurniawan	00:00.0	10	220000	3
Rangky	00:00.0	3	100000	4
Firman	00:00.0	8	60000	5

Semua hasil diatas dihasilkan dengan syntax ROW_NUMBER(), selanjutnya saya akan menampilkan perbedaan dengan menggunakan syntax RANK() dan DENSE_RANK :

Menggunakan RANK() :

SELECT c.name,
       o.dateordered,
       vw.total,
       Rank() OVER (ORDER BY total DESC) AS bestcustomer
FROM   viewsumsubtotalperorder AS vw
       INNER JOIN orders AS o
         ON o.orderid = vw.orderid
       INNER JOIN customers AS c
         ON c.customerid = o.customerid 

Menghasilkan :

Name	DateOrdered	Total	BestCustomer
Kurniawan	00:00.0	450000	1
Slamet	00:00.0	240000	2
Kurniawan	00:00.0	220000	3
Rangky	00:00.0	100000	4
Firman	00:00.0	60000	5
Rangky	00:00.0	45000	6
Rico	00:00.0	45000	6
Slamet	00:00.0	40000	8
Fajar	00:00.0	30000	9
Firman	00:00.0	15000	10
Rico	00:00.0	2000	11

Menggunakan DENSE_RANK :

SELECT c.name,
       o.dateordered,
       vw.total,
       Dense_rank() OVER (ORDER BY total DESC) AS bestcustomer
FROM   viewsumsubtotalperorder AS vw
       INNER JOIN orders AS o
         ON o.orderid = vw.orderid
       INNER JOIN customers AS c
         ON c.customerid = o.customerid 

Menghasilkan :

Name	DateOrdered	Total	BestCustomer
Kurniawan	00:00.0	450000	1
Slamet	00:00.0	240000	2
Kurniawan	00:00.0	220000	3
Rangky	00:00.0	100000	4
Firman	00:00.0	60000	5
Rangky	00:00.0	45000	6
Rico	00:00.0	45000	6
Slamet	00:00.0	40000	7
Fajar	00:00.0	30000	8
Firman	00:00.0	15000	9
Rico	00:00.0	2000	10

Yang terakhir adalah, query untuk membagi hasil ranking berdasarkan berapa grup yang kita mau :

SELECT productid,
       name,
       price,
       Ntile(4) OVER (ORDER BY price DESC) AS quartile
FROM   products 

Menghasilkan :

ProductID	Name	Price	Quartile
3	Table	100000	1
6	Shoes	40000	1
2	Chair	30000	2
5	Bag	20000	2
7	Hat	15000	3
4	Pen	2000	3
1	Book	1500	4
8	Pencil	500	4

Bagi yang mau mencoba-coba, saya sertakan juga data mentahnya disini. File yang saya upload berextensi .doc, ubah saja menjadi .sql.

Semoga tutorial sederhana ini membantu dalam pembuatan report, atau query sederhana yang anda inginkan.