DENSE_RANK + OVER (ORDER BY ...) - Gruplar ve Her Gruba Sırayla Numara Verir

Giriş

Açıklaması şöyle

In SQL, DENSE_RANK() is a window function that assigns a unique rank to each row within a partition, with no gaps in the ranking values. When used in conjunction with the OVER (ORDER BY ...) clause, it allows you to define the order in which the rows are ranked.

Here's an explanation of each component:

DENSE_RANK(): It calculates the rank of each row within a partition. The rank value represents the position of a row relative to the other rows in the same partition. Unlike RANK(), DENSE_RANK() does not leave gaps in the ranking sequence, meaning rows with equal values are assigned the same rank, and the next rank is incremented by 1.

OVER (ORDER BY ...): This clause defines the ordering of rows within the partition for the ranking calculation. You specify one or more columns by which you want to order the rows. The rows will be ranked based on the specified order.

Kısaca  DENSE_RANK + OVER (ORDER BY ...) ile belirtilen sütuna göre sıralama yapılır. Aynı değere sahip satırlara aynı gruptadır. Her gruba 1'den başlayan bir sayı verilir.

Örnek - SQL query to fetch a second-highest salary

Elimizde şöyle bir tablo olsun, en düşük ikinci maaşa sahip çalışanı bulmak isteyelim

+------+-----------+--------+
| # id | emp_name  | salary |
+------+-----------+--------+
|    1 | anil      |   1000 |
|    2 | ankit     |   1500 |
|    3 | bhavna    |   1100 |
|    4 | christian |   1400 |
|    5 | daniel    |   1400 |
|    6 | hardik    |   1500 |
|    7 | isha      |   1100 |
|    8 | ruchi     |   1000 |
+------+-----------+--------+

Şöyle yaparız

select * from (
  select dense_rank() over (order by salary)  as sno,
  emp_name,salary
from employee ) X where sno = 2;

// To write even better, we can use the CTE feature of SQL. Below is the final query.
with temp as 
(select dense_rank() over (order by salary)  as sno,
  emp_name,salary
  from employee
)
select * from temp where sno=2;

Where kısmı olmasaydı çıktı şöyleydi

+-----+-----------+--------+
| sno | emp_name  | salary |
+-----+-----------+--------+
|   1 | anil      |   1000 |
|   1 | ruchi     |   1000 |
|   2 | bhavna    |   1100 |
|   2 | isha      |   1100 |
|   3 | christian |   1400 |
|   3 | daniel    |   1400 |
|   4 | ankit     |   1500 |
|   4 | hardik    |   1500 |
+-----+-----------+--------+

Where ile çıktı şöyle

+-----+----------+--------+
| sno | emp_name | salary |
+-----+----------+--------+
|   2 | bhavna   |   1100 |
|   2 | isha     |   1100 |
+-----+----------+--------+

Örnek - DENSE_RANK() vs RANK()

Elimizde şöyle bir sorgu olsun. 2020 yılına ait siparişler çekiliyor ve her satıra bir sayı veriliyor. Burada RANK() kullanılıyor

SELECT name, city, RANK() OVER(ORDER BY orderdate DESC) AS RANK FROM ORDERS
WHERE YEAR(orderdate) = 2020

Çıktı olarak şunu alırız. Rank sütunu sırayla artmıyor. 

name city RANK

David Atlanta 1

Mike     Irving 2

Tom         Austin 3

Mick         Dallas 3

Sam         Dallas 5

Sorguyu şöyle yaparız

SELECT name, city, DENSE_RANK() OVER(ORDER BY orderdate DESC) AS RANK FROM ORDERS
WHERE YEAR(orderdate) = 2020

Çıktı olarak şunu alırız. Bu sefer RANK sırası doğrudur

name city RANK

David Atlanta 1

Mike     Irving 2

Tom         Austin 3

Mick         Dallas 4

Sam         Dallas 5

Örnek

Soru şöyle

Find the customer with the highest daily total order cost between 2019-02-01 to 2019-05-01. If customer had more than one order on a certain day, sum the order costs on daily basis. Output customer's first name, total cost of their items, and the date.

For simplicity, you can assume that every first name in the dataset is unique.

Şöyle yaparız

SELECT first_name,
  total_order_cost,
  order_date FROM (
    SELECT first_name,
	  SUM(total_order_cost) AS 
	    total_order_cost,
	  order_date,
	  DENSE_RANK() OVER(ORDER BY SUM
	    (total_order_cost) DESC) AS
		_order_rank
	FROM customers AS c
	JOIN orders AS o
	  ON c.id = o.cust_id
	WHERE order_date BETWEEN '2019-02-01'
	  AND '2019-05-01'
	GROUP BY c.id, order_date
   ) AS orders_ranked
 WHERE order_rank = 1