Sql rank dense rank: RANK() and DENSE_RANK() functions | Interactive tutorial on SQL

Как работают и чем различаются RANK и DENSE RANK в PySpark

В прошлой статье мы говорили о ранжирующей функции ROWS NUMBER в PySpark. Сегодня поговорим о RANK DENSE_RANK, а также узнаем, чем они различаются.

Данные с затонувшего Титаника

Возьмем для примера датасет с затонувшим Титаником. Его можно взять тут. У него много столбцов, поэтому вы покажем только его схему.

df = spark.read.csv("titanic_dataset.csv", header=True, inferScheme=True)

"""
root
 |-- PassengerId: integer (nullable = true)
 |-- Survived: integer (nullable = true)
 |-- Pclass: integer (nullable = true)
 |-- Name: string (nullable = true)
 |-- Sex: string (nullable = true)
 |-- Age: double (nullable = true)
 |-- SibSp: integer (nullable = true)
 |-- Parch: integer (nullable = true)
 |-- Ticket: string (nullable = true)
 |-- Fare: double (nullable = true)
 |-- Cabin: string (nullable = true)
 |-- Embarked: string (nullable = true)
"""

Как использовать RANK в PySpark

Функция RANK вычисляет ранг для каждой партиции. Эта функция очень похожа на ROW NUMBER. Функция ROWS NUMBER нумерует строки последовательно (например, 1, 2, 3, 4). RANK же выдает ранг каждой партиции или порядку, при этом сохраняя внутренний подсчет (например, 1, 2, 2, 4, 4 6).

Поясним это на примере. Определим ранг в PySpark в порядке стоимости билета Fare:

SELECT t.Name, t.Survived, t.Fare,
    RANK() OVER(ORDER BY t.Fare) as rnk,
    ROW NUMBER() OVER(ORDER BY t.Fare) as rown
FROM titanic t;

"""
+--------------------+--------+------+---+----+
|                Name|Survived|  Fare|rnk|rown|
+--------------------+--------+------+---+----+
| Leonard, Mr. Lionel|       0|   0.0|  1|   1|
|Harrison, Mr. Wil...|       0|   0.0|  1|   2|
|Tornquist, Mr. Wi...|       1|   0.0|  1|   3|
|"Parkes, Mr. Fran...|       0|   0.0|  1|   4|
|Johnson, Mr. Will...|       0|   0.0|  1|   5|
|Cunningham, Mr.  A...|       0|   0.0|  1|   6|
|Campbell, Mr. Wil...|       0|   0.0|  1|   7|
|"Frost, Mr. Antho...|       0|   0.0|  1|   8|
| Johnson, Mr. Alfred|       0|   0.0|  1|   9|
|Parr, Mr. William...|       0|   0.0|  1|  10|
|Watson, Mr. Ennis...|       0|   0.0|  1|  11|
|Knight, Mr. Robert J|       0|   0.0|  1|  12|
|Andrews, Mr. Thom...|       0|   0.0|  1|  13|
|    Fry, Mr. Richard|       0|   0.0|  1|  14|
|Reuchlin, Jonkhee...|       0|   0.0|  1|  15|
| Betros, Mr. Tannous|       0|4.0125| 16|  16|
|Carlsson, Mr. Fra...|       0|   5.0| 17|  17|
|Nysveen, Mr. Joha...|       0|6.2375| 18|  18|
|Lemberopolous, Mr...|       0|6.4375| 19|  19|
|Holm, Mr. John Fr...|       0|  6.45| 20|  20|
+--------------------+--------+------+---+----+
"""

Как видим, 16-я строка имеет ранг 16, а не 2.

Этот же запрос для нахождения ранга в PySpark:

import pyspark. sql.functions as F
from pyspark.sql import Window

df.select(
    "Name", "Survived", "Fare",
    F.rank().over(w).alias("rnk"),
    F.row_number().over(w).alias("rown")
)

Мы также можем указать партиции (группы) и считать ранги для каждой партиции и порядка. Например, выдадим ранги по классу пассажира Pclass и убывающему порядку следования стоимости проезда Fare. Такой SQL-запрос формируется следующим образом:

SELECT t.Name, t.Pclass, t.Fare,
    RANK() OVER(PARTITION BY Pclass ORDER BY t.Fare DESC) as rnk,
FROM titanic t;

"""
+--------------------+------+--------+---+
|                Name|Pclass|    Fare|rnk|
+--------------------+------+--------+---+
|    Ward, Miss. Anna|     1|512.3292|  1|
|Cardeza, Mr. Thom...|     1|512.3292|  1|
|Lesurer, Mr. Gust...|     1|512.3292|  1|
|Fortune, Mr. Char...|     1|   263. 0|  4|
|Fortune, Miss. Ma...|     1|   263.0|  4|
|Fortune, Miss. Al...|     1|   263.0|  4|
|   Fortune, Mr. Mark|     1|   263.0|  4|
|Ryerson, Miss. Em...|     1| 262.375|  8|
|"Ryerson, Miss. S...|     1| 262.375|  8|
|Baxter, Mr. Quigg...|     1|247.5208| 10|
|Baxter, Mrs. Jame...|     1|247.5208| 10|
|Bidois, Miss. Ros...|     1| 227.525| 12|
| Robbins, Mr. Victor|     1| 227.525| 12|
|Astor, Mrs. John ...|     1| 227.525| 12|
|Endres, Miss. Car...|     1| 227.525| 12|
|  Farthing, Mr. John|     1|221.7792| 16|
|Widener, Mr. Harr...|     1|   211.5| 17|
|Madill, Miss. Geo...|     1|211.3375| 18|
|Allen, Miss. Elis...|     1|211.3375| 18|
|Robert, Mrs. Edwa...|     1|211.3375| 18|
+--------------------+------+--------+---+
"""

Чтобы указать убывающий порядок в самом PySpark, то лучше всего это сделать через функцию desc:

w = Window. partitionBy("Pclass").orderBy(F.desc("Fare"))
df.select(
    "Name", "Pclass", "Fare",
    F.rank().over(w).alias("rnk")
)

Use case: взять самые высокие значения по каждой группе

Вдруг нам понадобилось взять первые N значений (наименьших или наибольших) в каждой группе. Мы можем это сделать так же, как это делали с ROW NUMBER в предыдущей статье, т.е. через подзапрос и обычный фильтр.

Например, SQL-запрос возьмем пассажиров из каждого класса, которые заплатили больше всего,

SELECT * FROM (
    SELECT t.Name, t.Pclass, t.Fare,
    RANK() OVER(PARTITION BY Pclass ORDER BY t.Fare) AS rnk
    FROM titanic t
) x
WHERE x.rnk < 4;

"""
+--------------------+------+--------+---+
|                Name|Pclass|    Fare|rnk|
+--------------------+------+--------+---+
|    Ward, Miss. Anna|     1|512.3292|  1|
|Cardeza, Mr. Thom...|     1|512. 3292|  1|
|Lesurer, Mr. Gust...|     1|512.3292|  1|
|Sage, Master. Tho...|     3|   69.55|  1|
|Sage, Miss. Const...|     3|   69.55|  1|
| Sage, Mr. Frederick|     3|   69.55|  1|
|Sage, Mr. George ...|     3|   69.55|  1|
|Sage, Miss. Stell...|     3|   69.55|  1|
|Sage, Mr. Douglas...|     3|   69.55|  1|
|"Sage, Miss. Doro...|     3|   69.55|  1|
|Hood, Mr. Ambrose Jr|     2|    73.5|  1|
|Hickman, Mr. Stan...|     2|    73.5|  1|
|Davies, Mr. Charl...|     2|    73.5|  1|
|Hickman, Mr. Leon...|     2|    73.5|  1|
|  Hickman, Mr. Lewis|     2|    73.5|  1|
+--------------------+------+--------+---+
"""

То же самое в PySpark:

w = Window.partitionBy("Pclass").orderBy(F.desc("Fare"))
x = df.select(
    "Name", "Pclass", "Fare",
    F.rank().over(w).alias("rnk")
)
x.where("rnk < 4").show()

RANK vs DENSE RANK в PySpark

Apache Spark поддерживает еще и DENSE RANK. В отличие от обычного RANK он выдает ранг группам последовательно (1, 2, 3, 4, 5). Например, выведем оба ранга:

df.select(
    "Name", "Pclass", "Fare",
    F.rank().over(w).alias("rnk"),
    F.dense_rank().over(w).alias("dense")
)

"""
+--------------------+------+--------+---+-----+
|                Name|Pclass|    Fare|rnk|dense|
+--------------------+------+--------+---+-----+
|    Ward, Miss. Anna|     1|512.3292|  1|    1|
|Cardeza, Mr. Thom...|     1|512.3292|  1|    1|
|Lesurer, Mr. Gust...|     1|512.3292|  1|    1|
|Fortune, Mr. Char...|     1|   263.0|  4|    2|
|Fortune, Miss. Ma...|     1|   263.0|  4|    2|
|Fortune, Miss. Al...|     1|   263.0|  4|    2|
|   Fortune, Mr. Mark|     1|   263.0|  4|    2|
|Ryerson, Miss. Em...|     1| 262.375|  8|    3|
|"Ryerson, Miss. S...|     1| 262.375|  8|    3|
|Baxter, Mr. Quigg...|     1|247. 5208| 10|    4|
|Baxter, Mrs. Jame...|     1|247.5208| 10|    4|
|Bidois, Miss. Ros...|     1| 227.525| 12|    5|
| Robbins, Mr. Victor|     1| 227.525| 12|    5|
|Astor, Mrs. John ...|     1| 227.525| 12|    5|
|Endres, Miss. Car...|     1| 227.525| 12|    5|
|  Farthing, Mr. John|     1|221.7792| 16|    6|
|Widener, Mr. Harr...|     1|   211.5| 17|    7|
|Madill, Miss. Geo...|     1|211.3375| 18|    8|
|Allen, Miss. Elis...|     1|211.3375| 18|    8|
|Robert, Mrs. Edwa...|     1|211.3375| 18|    8|
+--------------------+------+--------+---+-----+
"""

На 4-й строке плотный ранг дал строке значение 2, а не 4, как это делает обычный ранг.

А в следующей статье поговорим о функциях LEAD и LAG. Еще больше подробностей о оконных функциях в PySpark вы узнаете на наших образовательных курсах в лицензированном учебном центре обучения и повышения квалификации руководителей и IT-специалистов (менеджеров, архитекторов, инженеров, администраторов, Data Scientist’ов и аналитиков Big Data) в Москве:

Машинное обучение в Apache Spark

• Анализ данных с Apache Spark
• Машинное обучение в Apache Spark
• Графовые алгоритмы в Apache Spark
• Потоковая обработка в Apache Spark
• Основы Apache Spark для разработчиков

Записаться на курс

Смотреть раcписание

Источники

1. rank
2. dense rank

Как использовать функции SQL RANK и DENSE_RANK | Мэдисон Шотт

Опубликовано в

·

Чтение: 4 мин.

·

23 апреля 2021 г. Я предложил в Top Skills to Ace Каждый вопрос интервью SQL, то вы, вероятно, столкнулись с проблемами, которые используют оконные функции. Или вы столкнулись с ними на странице обсуждений или решений на Leetcode.

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

Сегодня я собираюсь поговорить о двух функциях, полезных для любого типа задач ранжирования — RANK и DENSE_RANK. Несмотря на то, что эти функции похожи, они имеют свои различия. Во многих сценариях оба будут работать для решения вашей проблемы. Тем не менее, по-прежнему важно знать разницу, когда вам требуется специальное решение.

RANK и DENSE_RANK используются для упорядочения значений и присвоения им номеров в зависимости от того, где они находятся по отношению друг к другу.

Например, у вас есть 3 учащегося с 3 разными результатами тестов: один учащийся получил 100, другой получил 85, а последний получил 72. Эти функции присвоят 1, 2 или 3 каждому учащемуся в зависимости от в том порядке, в котором вы хотите ранжировать их.

Обе функции используют предложение OVER() вместе с PARTITION BY и ORDER BY. Часть функций PARTITION BY не является обязательной, но ORDER BY всегда необходим.

SELECT student_name, RANK() OVER(ORDER BY scores DESC) ASgrade_ranking

Изображение автора

Здесь вы можете видеть, что наивысшая оценка оценивается с 1, а самая низкая с 5, поскольку они расположены в порядке убывания.

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

В приведенном выше фрагменте кода мы упорядочили от самого высокого к самому низкому уровню (DESC), ниже мы упорядочили от самого низкого до самого высокого уровня (ASC).

SELECT student_name, RANK() OVER(ORDER BY Assessments ASC) ASgrade_ranking

Изображение автора

Здесь вы можете видеть, что самой низкой оценке присваивается ранг 1, а самой высокой — 5 ранг, так как оценки в порядке возрастания.

РАЗДЕЛЕНИЕ ПО группирует рейтинги. Когда значение для указанного здесь столбца изменяется, ранжирование начинается заново. Допустим, мы добавили предмет к экзаменационным баллам ученика. Если вы разделите данные по темам, это даст вам рейтинг каждого балла, сгруппированного по темам.

SELECT student_name, DENSE_RANK() OVER(PARTITION BY ПРЕДМЕТ ORDER BY оценки DESC) ASgrade_ranking

Изображение автора

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

Разница между этими двумя функциями сводится к тому, как они обрабатывают одинаковые значения. Допустим, у нас есть два ученика с одинаковыми оценками; оба набрали 90 баллов на тесте по математике.

RANK и DENSE_RANK присваивают оценкам один и тот же ранг в зависимости от того, насколько они падают по сравнению с другими значениями. Однако тогда RANK пропустит следующее доступное значение ранжирования, тогда как DENSE_RANK по-прежнему будет использовать следующее значение ранжирования в хронологическом порядке.

Таким образом, с RANK, если двум 90-м присвоен ранг 2, следующему наименьшему значению будет присвоен ранг 4 с пропуском 3. С DENSE_RANK следующему наименьшему значению будет присвоен ранг 3, без пропуска над любыми значениями.

Давайте сравним результаты обеих этих функций.

ВЫБЕРИТЕ имя_учащегося, RANK() OVER(ORDER BY class DESC) AS rank_w_rank, DENSE_RANK() OVER(ORDER BY class DESC) AS rank_w_dense_rank

Изображение автора

Опять же, вы можете видеть, что в столбце нет ранга 2 с использованием RANK в отличие от столбца DENSE_RANK, который содержит ранг 2 и заканчивается рангом 4, несмотря на то, что в таблице 5 строк.

Надеюсь, теперь вы понимаете, как использовать RANK и DENSE_RANK и когда их использовать. Обычно я использую DENSE_RANK в качестве функции ранжирования по умолчанию в SQL. Я нахожу больше проблем, которые вы хотите, чтобы вы шли в хронологическом порядке, не пропуская номер. Однако убедитесь, что вы внимательно прочитали задачу и подумали о результате, которого пытаетесь достичь.

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

Удачных запросов!

Узнайте больше о SQL и других инструментах, используемых инженерами-аналитиками, подписавшись на мою рассылку.

Оконные функции SQL Server RANK, DENSE_RANK и NTILE

Автор: Koen Verbeeck

Обзор

Тема этой части посвящена функциям ранжирования. Они были введены в SQL
Сервер 2005. ROW_NUMBER также является функцией ранжирования, но она уже была рассмотрена.
подробно в предыдущей части этого руководства.

РАНГ и DENSE_RANK

В отличие от функции ROW_NUMBER,

РАНГ и

DENSE_RANK не должен генерировать уникальные числа. Разница между
все эти функции — это то, как они обрабатывают связи.

• ROW_NUMBER всегда будет генерировать уникальные значения без пробелов, даже если
  есть галстуки.
• RANK может иметь пробелы в своей последовательности, и когда значения совпадают, они получают
  тот же ранг.
• DENSE_RANK также возвращает тот же ранг для ничьих, но не имеет
  пробелы в последовательности.

Проиллюстрируем на примере. Предположим, у нас есть следующий образец
значения:

Мы можем написать следующий запрос для проверки трех функций ранжирования:

 ВЫБЕРИТЕ
     [Группа]
    ,[Ценить]
    ,RowNumber = ROW_NUMBER() OVER (РАЗДЕЛЕНИЕ ПО [Группе] ORDER BY [Значение])
    ,[Rank] = RANK() OVER (РАЗДЕЛЕНИЕ ПО [Группе] ORDER BY [Значение])
    ,[DenseRank] = DENSE_RANK() OVER (РАЗДЕЛЕНИЕ ПО [Группе] ORDER BY [Значение])
ОТ [dbo].[Тест];
 

Это дает нам следующий набор результатов:

Как видите, РАНГ вернет тот же ранг для ничьих. Однако следующий ранг
в группе A это номер 3, опуская ранг 2. С другой стороны, DENSE_RANK не
пропустить ранг 2. Когда в наборе записей нет ничьих, все три функции ранжирования
вернуть точно такой же результат.

С помощью ROW_NUMBER и RANK мы можем создать альтернативную конструкцию для обнаружения дубликатов.
ряды. С теми же выборочными данными, которые использовались в
части 5 этого руководства, мы можем создать следующий запрос, чтобы найти все строки
у которых есть дубликат бизнес-ключа:

 ВЫБЕРИТЕ
     [Ключ сотрудника]
    ,[Имя сотрудника]
    ,[ВставитьДату]
    ,RID = ROW_NUMBER() OVER (ЗАКАЗ ПО [EmployeeKey])
    ,RankID = RANK() OVER (ЗАКАЗАТЬ ПО [EmployeeKey])
FROM [dbo].[EmployeesDuplicate];
 

Набор результатов:

Теперь нам нужно фильтровать только те записи, для которых задан сгенерированный номер строки (RID).
отличается от ранга (RankID). Это вернет запись для Боба, поэтому мы знаем
существует дублирующаяся запись для этого сотрудника.

Когда использовать RANK или DENSE_RANK?

Как указывалось ранее, RANK может иметь пробелы в последовательности, а DENSE_RANK — нет.
Предположим, у вас есть задача найти 5 лучших клиентов по объему продаж.
С помощью RANK вы можете ранжировать своих клиентов, а затем отфильтровывать клиентов с помощью
ранг больше 5. Если бы вы сделали то же самое с DENSE_RANK, вы могли бы получить
более 6 клиентов!

Предположим, у нас есть такой набор данных о наших клиентах:

При добавлении рангов с RANK и DENSE_RANK у нас есть следующий набор данных:

При фильтрации по рангу <= 5 вы получите ровно 5 строк. Однако, если вы отфильтруете по плотности <= 5, вы получите 6 строк, что может быть не так. что вы хотите. Чем больше связей, тем больше строк может вернуть DENSE_RANK. Если бы галстук находится точно в строке номер 5, функция RANK также вернет 6 строк. В этом случае, вам нужно разорвать галстук. Вы можете сделать это, используя ROW_NUMBER функцией или путем сортировки по дополнительному столбцу.

НТИЛЕ

Последняя функция ранжирования немного особенная. NTILE делит строки примерно
ведра одинакового размера. Предположим, у вас есть 20 строк и вы указали NTILE(2). Это будет
дать вам 2 ведра по 10 рядов в каждом. При использовании NTILE(3) вы получаете 2 ведра с
7 рядов и 1 ведро с 6 рядами.