Стандартные функции ранжирования SQL: 2003 время от времени являются отличными компаньонами и полезными инструментами. Те, которые поддерживаются почти во всех базах данных:
- ROW_NUMBER (): этот генерирует новый номер строки для каждой строки, независимо от дубликатов в разделе.
- RANK (): генерирует новый номер строки для каждой отдельной строки, оставляя пробелы между группами дубликатов внутри раздела.
- DENSE_RANK (): этот генерирует новый номер строки для каждой отдельной строки, не оставляя пробелов между группами дубликатов в пределах раздела.
Как всегда, вышесказанное гораздо проще понять на примере. Давайте предположим следующую схему PostgreSQL, содержащую таблицу с 8 записями, некоторые из которых являются дубликатами:
|
1
2
3
4
5
6
7
8
9
|
CREATE TABLE t ASSELECT 'a' v UNION ALLSELECT 'a' UNION ALLSELECT 'a' UNION ALLSELECT 'b' UNION ALLSELECT 'c' UNION ALLSELECT 'c' UNION ALLSELECT 'd' UNION ALLSELECT 'e' |
Теперь давайте выберем каждое значение вместе с тремя вышеупомянутыми функциями ранжирования. А для пинки и хихиканья мы будем использовать стандартное предложение SQL для WINDOW ! Да, это спасло нас от 15 символов повторяющегося кода SQL. Обратите внимание, что предложение WINDOW вряд ли реализовано отдельно PostgreSQL и Sybase SQL Anywhere…
|
1
2
3
4
5
6
7
8
|
SELECT v, ROW_NUMBER() OVER (w) row_number, RANK() OVER (w) rank, DENSE_RANK() OVER (w) dense_rankFROM tWINDOW w AS (ORDER BY v)ORDER BY v |
И вышеупомянутые результаты в:
|
01
02
03
04
05
06
07
08
09
10
11
12
|
+---+------------+------+------------+| V | ROW_NUMBER | RANK | DENSE_RANK |+---+------------+------+------------+| a | 1 | 1 | 1 || a | 2 | 1 | 1 || a | 3 | 1 | 1 || b | 4 | 4 | 2 || c | 5 | 5 | 3 || c | 6 | 5 | 3 || d | 7 | 7 | 4 || e | 8 | 8 | 5 |+---+------------+------+------------+ |
(Смотрите также этот SQLFiddle )
Как DENSE_RANK () может помочь при написании SELECT DISTINCT
Несомненно, ROW_NUMBER() является наиболее полезной функцией ранжирования среди вышеперечисленных, особенно когда вам нужно эмулировать предложения LIMIT .. OFFSET, как в DB2, Oracle (11g или меньше), Sybase SQL Anywhere (до версии 12), SQL Server (2008 и меньше). Читайте здесь о том, как jOOQ эмулирует это предложение SQL на различных диалектах SQL.
Но использование ROW_NUMBER() имеет ROW_NUMBER() проблему при использовании вместе с DISTINCT или UNION . Это препятствует тому, чтобы база данных была в состоянии удалить дубликаты, потому что ROW_NUMBER всегда будет давать различные значения в пределах раздела. В приведенном выше примере дублированные значения для TV были добавлены специально. Как мы можем сначала удалить дубликаты и только потом перечислять номера строк? Ясно, что мы больше не можем использовать ROW_NUMBER() . Следующий запрос:
|
1
2
3
4
5
6
|
SELECT DISTINCT v, ROW_NUMBER() OVER (w) row_numberFROM tWINDOW w AS (ORDER BY v)ORDER BY v, row_number |
… дает
|
01
02
03
04
05
06
07
08
09
10
11
12
|
+---+------------+| V | ROW_NUMBER |+---+------------+| a | 1 || a | 2 || a | 3 || b | 4 || c | 5 || c | 6 || d | 7 || e | 8 |+---+------------+ |
(Смотрите также этот SQLFiddle )
Но вместо этого мы можем использовать DENSE_RANK() ! С DENSE_RANK() ранжирование применяется таким образом, что дублирующиеся записи получают одинаковый ранг. И нет никаких пробелов между званиями. Следовательно:
|
1
2
3
4
5
6
|
SELECT DISTINCT v, DENSE_RANK() OVER (w) row_numberFROM tWINDOW w AS (ORDER BY v)ORDER BY v, row_number |
… который дает:
|
1
2
3
4
5
6
7
8
9
|
+---+------------+| V | ROW_NUMBER |+---+------------+| a | 1 || b | 2 || c | 3 || d | 4 || e | 5 |+---+------------+ |
(Смотрите также этот SQLFiddle )
Таким образом, помните …
Таким образом, помните: ROW_NUMBER() должен SELECT то, что DENSE_RANK() должен SELECT DISTINCT
Предостережения
Однако для того, чтобы вышеприведенное было верным, необходимо убедиться, что все выражения из предложения SELECT DISTINCT используются в предложении DENSE_RANK() OVER(ORDER BY ...) . Например:
|
1
2
3
4
5
6
7
|
SELECT DISTINCT v1, v2, v3, DENSE_RANK() OVER (w) row_numberFROM tWINDOW w AS (ORDER BY v1, v2, v3) |
Если какие-либо из v1, v2, v3 являются другими функциями ранжирования или агрегатными функциями, или недетерминированными выражениями и т. Д., Описанный выше трюк не сработает. Но это все еще хороший прием, чтобы держать себя в руках для нечетного углового запроса, где отдельным строкам нужны номера строк.