loading..
Русский    English
16:35
листать

Функции RANK() и DENSE_RANK() стр. 2

Пример использования DENSE_RANK

Часто встречается задача нахождения N-го по величине значения из набора значений некоторого столбца таблицы, например:

Найти второе по величине значение цены в таблице PC.

Давайте выведем отсортированный список значений цены из таблицы PC для контроля, добавив столбцы со значениями ранжирующих функций:

Консоль
Выполнить
  1. SELECT  price, DENSE_RANK() OVER(ORDER BY price DESC) dense_rnk, 
  2. RANK() OVER(ORDER BY price DESC) rnk,
  3. ROW_NUMBER() OVER(ORDER BY price DESC) rn
  4. FROM PC ORDER BY price DESC;

price    dense_rnk    rnk    rn
980,00    1   1    1
970,00    2   2    2
950,00    3   3    3
850,00    4   4   4
850,00    4   4   5
850,00    4   4   6
600,00    5   7   7
600,00    5   7   8
400,00    6   9    9
350,00    7   10    10
350,00    7   10   11
350,00    7   10   12

В рамках стандарта SQL-92 эту задачу можно решить следующим образом:

Консоль
Выполнить
  1. SELECT MAX(price) "2nd_price"  FROM PC 
  2. WHERE price < (SELECT MAX(price) FROM PC);

2nd_price
970,00

Т.е. мы находим значение максимума среди всех значений, меньших максимального. А если нам потребуется найти значение третьей по величине цены? Можно поступить аналогично:

Консоль
Выполнить
  1. SELECT MAX(price) "3rd_price" FROM PC WHERE price <
  2. (
  3. SELECT MAX(price) FROM PC 
  4. WHERE price < (SELECT MAX(price) FROM PC)
  5. );

3rd_price
950,00

А как найти N-е значение цены? Следуя используемой логике, мы можем добавлять новые "матрешки" к уже существующим вплоть до N-ой. Это решение никак не назовешь универсальным.

Для решения подобных задач хорошо подходит функция DENSE_RANK. Например, исходную задачу с помощью этой функции можно решить так:

Консоль
Выполнить
  1. SELECT DISTINCT price FROM(
  2. SELECT DENSE_RANK() OVER(ORDER BY price DESC) rnk, price FROM PC
  3. ) X WHERE rnk=2;

А чтобы найти любую другую порядковую цену (например, третью), достаточно поменять константу в условиях отбора:

Консоль
Выполнить
  1. SELECT DISTINCT price FROM(
  2. SELECT DENSE_RANK() OVER(ORDER BY price DESC) rnk, price FROM PC
  3. ) X WHERE rnk=3;

Следует отметить, что использование DENSE_RANK, а не RANK, обусловлено тем, что в случае наличия одинаковых цен, значения, возвращаемые функцией RANK, будут иметь пропуски (рассмотрите задачу нахождения пятой по величине цены). Если же ставить задачу нахождения неуникального N-го значения, то можно использовать функцию ROW_NUMBER (например, третий человек в шеренге по росту). А если значения в таблице уникальны, то решение с помощью любой из этих функций даст один и тот же результат.

Bookmark and Share
Страницы: 1 2
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INFORMATION_SCHEMA INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker Больше тэгов
Учебник обновлялся
месяц назад
https://exchangesumo.com/obmen/PRUSD-GNO-sort
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.