Статьи

Функции ранжирования: ROW_NUMBER (), RANK () и DENSE_RANK ()

Июль 21, 2017

Одной из лучших функций в SQL являются оконные функции . Дмитрий Фонтейн выразился прямо:

Был SQL перед оконными функциями и SQL после оконных функций

Если вам повезло использовать любую из этих баз данных, то вы можете использовать оконные функции самостоятельно:

  • CUBRID
  • DB2
  • жар-птица
  • Informix
  • оракул
  • PostgreSQL
  • SQL Server
  • Sybase SQL Anywhere
  • Teradata

(источник здесь)

Одним из наиболее очевидных и полезных наборов оконных функций являются функции ранжирования, в которых строки из вашего набора результатов ранжируются в соответствии с определенной схемой. Есть три функции ранжирования:

  • ROW_NUMBER()
  • RANK()
  • DENSE_RANK()

Разницу легко запомнить. Для примеров, давайте предположим, что у нас есть эта таблица (с использованием синтаксиса PostgreSQL):

CREATE TABLE t(v) AS
SELECT * FROM (
  VALUES('a'),('a'),('a'),('b'),
        ('c'),('c'),('d'),('e')
) t(v)

ROW_NUMBER ()

… Присваивает уникальные номера каждой строке в PARTITIONданном ORDER BYпредложении. Итак, вы получите:

SELECT v, ROW_NUMBER() OVER()
FROM t

Обратите внимание, что некоторые диалекты SQL (например, SQL Server) требуют явного ORDER BYпредложения в OVER()предложении:

SELECT v, ROW_NUMBER() OVER(ORDER BY v)
FROM t

Приведенный выше запрос возвращает:

(см. также этот SQLFiddle)

РАНГ()

… Ведет себя так ROW_NUMBER(), за исключением того, что «равные» строки ранжируются одинаково. Если мы подставим RANK()в наш предыдущий запрос:

SELECT v, RANK() OVER(ORDER BY v)
FROM t

… тогда мы получаем следующий результат:

(см. также этот SQLFiddle)

Как видите, как и в спортивном рейтинге, у нас есть разрывы между различными рангами. Мы можем избежать этих пробелов, используя

DENSE_RANK ()

Тривиально, DENSE_RANK()это ранг без пробелов, т.е. он «плотный» . Мы можем написать:

SELECT v, DENSE_RANK() OVER(ORDER BY v)
FROM t

… чтобы получить

(см. также этот SQLFiddle)

Одним интересным аспектом DENSE_RANK()является тот факт, что он «ведет себя как», ROW_NUMBER()когда мы добавляем DISTINCTключевое слово .

SELECT DISTINCT v, DENSE_RANK() OVER(ORDER BY v)
FROM t

… чтобы получить

(см. также этот SQLFiddle)

На самом деле, не ROW_NUMBER()позволяет использовать DISTINCT, потому что ROW_NUMBER()генерирует уникальные значения в разделе передDISTINCT применением:

SELECT DISTINCT v, ROW_NUMBER() OVER(ORDER BY v)
FROM t
ORDER BY 1, 2

DISTINCT не имеет никакого эффекта:

(см. также этот SQLFiddle)

Собираем все вместе

Хороший способ понять три функции ранжирования — это увидеть их все в действии бок о бок. Запустите этот запрос

SELECT
  v,
  ROW_NUMBER() OVER(ORDER BY v),
  RANK()       OVER(ORDER BY v),
  DENSE_RANK() OVER(ORDER BY v)
FROM t
ORDER BY 1, 2

… Или этот (используя стандартное WINDOWпредложение SQL , чтобы повторно использовать спецификации окон):

SELECT
  v,
  ROW_NUMBER() OVER(w),
  RANK()       OVER(w),
  DENSE_RANK() OVER(w)
FROM t
WINDOW w AS (ORDER BY v)

… чтобы получить:

(см. также этот SQLFiddle)

Обратите внимание, что, к сожалению, это WINDOWпредложение поддерживается не во всех базах данных.

SQL потрясающий

Эти вещи можно написать очень легко с помощью оконных функций SQL. Как только вы освоите синтаксис, вы больше не захотите пропускать эту убойную функцию в своих ежедневных SQL-выражениях. В восторге?

jOOQ: лучший способ использовать Oracle AQ в Java

Для дальнейшего чтения рассмотрим:

145
Share: