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 |
+------+-----------+--------+
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;
+-----+-----------+--------+
| 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 |
+-----+-----------+--------+
+-----+----------+--------+
| 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
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
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.
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
