Понимание разницы между этими оконными функциями в SQL
В мире SQL оконная функция — это мощная конструкция, позволяющая пользователям точно сегментировать данные и манипулировать ими. Группируя данные на основе определенных столбцов и критериев сортировки, оконные функции позволяют выполнять расширенные вычисления внутри разделов.
В этом всеобъемлющем руководстве мы рассмотрим три наиболее часто используемые оконные функции: ROW_NUMBER(), DENSE_RANK() и RANK(). Независимо от того, являетесь ли вы опытным специалистом по SQL или только начинаете, это руководство предоставит вам знания и практические примеры, необходимые для освоения этих основных инструментов.
Все включенные фрагменты кода были протестированы в базе данных MySQL, и эти функции должны работать практически для любого варианта SQL с минимальными изменениями или без них.
Во-первых, давайте создадим примерную таблицу, на которую мы будем ссылаться на протяжении всего руководства, чтобы продемонстрировать несколько различных концепций.
CREATE TABLE employees ( id integer, first_name varchar(20), last_name varchar(20), position varchar(20), salary varchar(20) ) INSERT INTO employees VALUES (1, 'Andrew', 'Brown', 'Manager', 100000), (2, 'Maria', 'Johnson', 'Manager', 105000), (3, 'John', 'Anderson', 'Senior Manager', 130000), (4, 'Alex', 'Purple', 'Associate', 50000), (5, 'George', 'Bull', 'Senior Associate', 65000), (6, 'Jess', 'Fridman', 'Associate', 48000), (7, 'Marion', 'White', 'Senior Associate', 65000), (8, 'Andreea', 'Berton', 'Manager', 102000), (9, 'Bob', 'Johanson', 'Associate', 45000), (10, 'Georgia', 'Hoffman', 'Senior Associate', 66000), (11, 'Johan', 'Peterson', 'Senior Associate', 58000);
А вот как выглядит наша примерная таблица:
SELECT * FROM employees; | id | first_name | last_name | position | salary | | --- | ---------- | --------- | ---------------- | ------ | | 1 | Andrew | Brown | Manager | 100000 | | 2 | Maria | Johnson | Manager | 105000 | | 3 | John | Anderson | Senior Manager | 130000 | | 4 | Alex | Purple | Associate | 50000 | | 5 | George | Bull | Senior Associate | 65000 | | 6 | Jess | Fridman | Associate | 48000 | | 7 | Marion | White | Senior Associate | 65000 | | 8 | Andreea | Berton | Manager | 102000 | | 9 | Bob | Johanson | Associate | 45000 | | 10 | Georgia | Hoffman | Senior Associate | 66000 | | 11 | Johan | Peterson | Senior Associate | 58000 |
Что такое оконная функция?
Оконная функция используется в SQL для выполнения вычислений над группой строк и возвращает одно значение для каждой строки. Обратите внимание на отличие от агрегатной функции, которая используется для возврата только одного значения для каждой группы. Оконные функции обычно используются для вычисления кумулятивных сумм, ранжирования, а также скользящих средних.
Действительный синтаксис для оконных функций должен включать
- предложение
OVERуказывается после функции и используется для ссылки на окно - и спецификацию окна, указывающую способ группировки строк (эта спецификация может включать предложения
PARTITION BYи/илиORDER BY).
Оконная функция RANK()
Функция RANK() возвращает ранг для каждой строки в указанной группе или разделе (разделах). Если несколько строк имеют одинаковое значение в разделе, всем им будет присвоен одинаковый рейтинг. В этом случае в ранжировании будет создан пробел, учитывая, что последующим рядам будет присвоен номер, соответствующий их фактическому положению в ранге (а не следующему доступному рангу).
Теперь давайте предположим, что мы хотели бы создать рейтинг для записей в employees таким образом, чтобы сотрудники, занимающие одну и ту же должность, ранжировались на основе их доходов. Этого можно добиться с помощью следующего запроса:
SELECT *, RANK() OVER (PARTITION BY position ORDER BY salary DESC) AS emp_pos_rank FROM employees; | id | first_name | last_name | position | salary | emp_pos_rank | | --- | ---------- | --------- | ---------------- | ------ | ------------ | | 4 | Alex | Purple | Associate | 50000 | 1 | | 6 | Jess | Fridman | Associate | 48000 | 2 | | 9 | Bob | Johanson | Associate | 45000 | 3 | | 2 | Maria | Johnson | Manager | 105000 | 1 | | 8 | Andreea | Berton | Manager | 102000 | 2 | | 1 | Andrew | Brown | Manager | 100000 | 3 | | 10 | Georgia | Hoffman | Senior Associate | 66000 | 1 | | 5 | George | Bull | Senior Associate | 65000 | 2 | | 7 | Marion | White | Senior Associate | 65000 | 2 | | 11 | Johan | Peterson | Senior Associate | 58000 | 4 | | 3 | John | Anderson | Senior Manager | 130000 | 1 |
Обратите внимание на разрыв, созданный в рейтинге для Senior Associate позиции. Двум сотрудникам был присвоен второй ранг, что означает, что записи, следующей за этими двумя записями, будет присвоен ранг 4 (а не 3).
Оконная функция DENSE_RANK()
Функция DENSE_RANK() возвращает ранг для каждой строки в указанной группе или разделе (разделах). В отличие от RANK(), DENSE_RANK() не будет иметь пробелов:
SELECT *, DENSE_RANK() OVER (PARTITION BY position ORDER BY salary DESC) AS emp_pos_rank FROM employees; | id | first_name | last_name | position | salary | emp_pos_rank | | --- | ---------- | --------- | ---------------- | ------ | ------------ | | 4 | Alex | Purple | Associate | 50000 | 1 | | 6 | Jess | Fridman | Associate | 48000 | 2 | | 9 | Bob | Johanson | Associate | 45000 | 3 | | 2 | Maria | Johnson | Manager | 105000 | 1 | | 8 | Andreea | Berton | Manager | 102000 | 2 | | 1 | Andrew | Brown | Manager | 100000 | 3 | | 10 | Georgia | Hoffman | Senior Associate | 66000 | 1 | | 5 | George | Bull | Senior Associate | 65000 | 2 | | 7 | Marion | White | Senior Associate | 65000 | 2 | | 11 | Johan | Peterson | Senior Associate | 58000 | 3 | | 3 | John | Anderson | Senior Manager | 130000 | 1 |
Обратите внимание, что 4-й старший сотрудник теперь получит ранг, равный 3, учитывая, что два других сотрудника делят второй ранг, и оконная функция DENSE_RANK не должна создавать пробелов.
Оконная функция ROW_NUMBER()
Наконец, оконная функция ROW_NUMBER назначит номер каждой строке, начиная с индекса 1 для первой строки в каждом разделе.
SELECT *, ROW_NUMBER() OVER (PARTITION BY position ORDER BY salary DESC) AS emp_pos_rank FROM employees; | id | first_name | last_name | position | salary | emp_pos_rank | | --- | ---------- | --------- | ---------------- | ------ | ------------ | | 4 | Alex | Purple | Associate | 50000 | 1 | | 6 | Jess | Fridman | Associate | 48000 | 2 | | 9 | Bob | Johanson | Associate | 45000 | 3 | | 2 | Maria | Johnson | Manager | 105000 | 1 | | 8 | Andreea | Berton | Manager | 102000 | 2 | | 1 | Andrew | Brown | Manager | 100000 | 3 | | 10 | Georgia | Hoffman | Senior Associate | 66000 | 1 | | 5 | George | Bull | Senior Associate | 65000 | 2 | | 7 | Marion | White | Senior Associate | 65000 | 3 | | 11 | Johan | Peterson | Senior Associate | 58000 | 4 | | 3 | John | Anderson | Senior Manager | 130000 | 1 |
Последние мысли
В заключение можно сказать, что оконные функции — это мощная функция SQL, которая позволяет выполнять сложные вычисления над определенным подмножеством строк в результирующем наборе. Их можно использовать для расчета промежуточных сумм, скользящих средних и других показателей, для которых требуется ссылка на соседние строки.
В сегодняшней статье мы обсудили разницу между функциями RANK(), DENSE_RANK() и ROW_NUMBER(). Понимая разницу между этими типами оконных функций, вы можете выбрать наиболее подходящую для вашего конкретного случая использования и оптимизировать производительность ваших SQL-запросов.
👉 Стань участником и читай все истории на Medium. Ваш членский взнос напрямую поддерживает меня и других писателей, которых вы читаете. Вы также получите полный доступ ко всем историям на Medium.
👇Похожие статьи, которые вам также могут понравиться👇
