Оконные и аналитические функции в Hive

Константин Алапшкин

Оконные и аналитические функции Hive позволяют производить вычисления над выделенным набором строк (окном, партицией) в исходной выборке SELECT. В отличие от агрегатных функций, которые сводят несколько значений столбцов к одному значению, результатом работы оконной функции является один или более столбцов с результатами вычислений, добавленных к исходному набору результатов. Оконные и аналитические функции очень полезны для решения аналитических задач и являются незаменимым инструментом для таких запросов как "получить топ X проданных товаров по категориям", "получить значение максимальной зарплаты по отделам", "получить первые N строк из результирующего набора" и так далее.

Все функции можно условно разделить на группы:

В статье приведены примеры и рекомендации по использованию основных оконных и аналитических функций Hive. Более детальная информация о поддерживаемых функциях Hive доступна в документации Apache Hive.

Тестовая база данных

Для демонстрации примеров в данной статье используются Hive-таблица transactions, структура которой описана ниже.

SELECT * FROM transactions;
+----------------------+----------------------+--------------------------+------------------------+
| transactions.txn_id  | transactions.acc_id  | transactions.txn_amount  | transactions.txn_date  |
+----------------------+----------------------+--------------------------+------------------------+
| 1                    | 1002                 | 10.00                    | 2023-01-01             |
| 2                    | 1002                 | 20.00                    | 2023-01-03             |
| 3                    | 1002                 | 30.00                    | 2023-01-02             |
| 4                    | 1001                 | 100.50                   | 2023-01-02             |
| 5                    | 1001                 | 150.50                   | 2023-01-04             |
| 6                    | 1001                 | 200.50                   | 2023-01-03             |
| 7                    | 1003                 | 50.00                    | 2023-01-03             |
| 8                    | 1003                 | 50.00                    | 2023-01-01             |
| 9                    | 1003                 | 75.00                    | 2023-01-04             |
+----------------------+----------------------+--------------------------+------------------------+
SQL для создания тестовой таблицы

 
Для создания и наполнения тестовой таблицы Hive выполните следующий SQL с помощью /bin/beeline.

DROP TABLE IF EXISTS `transactions`;
CREATE TABLE `transactions`(`txn_id` int, `acc_id` int, `txn_amount` decimal(10,2), `txn_date` date);
INSERT INTO transactions VALUES
(1, 1002, 10.00, '2023-01-01'),
(2, 1002, 20.00, '2023-01-03'),
(3, 1002, 30.00, '2023-01-02'),
(4, 1001, 100.50, '2023-01-02'),
(5, 1001, 150.50, '2023-01-04'),
(6, 1001, 200.50, '2023-01-03'),
(7, 1003, 50.00, '2023-01-03'),
(8, 1003, 50.00, '2023-01-01'),
(9, 1003, 75.00, '2023-01-04');

Синтаксис

Базовый синтаксис оконных и аналитических функций имеет следующий вид:

SELECT <func>([[, ...]]) OVER ([<window_spec>]) [<window_name>]
FROM <table_name>;

Где:

  • <func> — стандартная агрегатная функция SQL (например, AVG) или специальная оконная/аналитическая функция.

  • OVER — означает, что функция должна выполняться для выделенных окон в исходном наборе результатов.

  • <window_spec> — выражение, определяющее критерий формирования окон в наборе результатов. Подробности использования описаны ниже.

  • <window_name> — алиас для столбца с результатами выполнения функции.

Описание окна

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

[PARTITION BY <col> [, ...]]
[ORDER BY <col> [ASC|DESC] [, ...]]
(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)

Где:

  • PARTITION BY <col> — разбивает исходный результирующий набор на партиции, используя значения столбца <col>.

  • ORDER BY <col> — упорядочивает строки в каждой партиции перед выполнением функции.

  • ROWS | RANGE …​ — указывает верхний и нижний пределы фрейма окна.

Также существует альтернативный синтаксис с отдельным указанием предложения WINDOW:

SELECT <func>([[, ...]]) OVER <window_name>
FROM <table_name>
WINDOW <window_name> AS ([<window_spec>])

Ключевое слово OVER

В самом простом виде оконная функция объявляется путем добавления OVER в запрос SELECT. При указании столбцов для выборки могут использоваться оконные/аналитические функции, а также стандартные агрегатные функции SQL (SUM, AVG и прочие).

В следующем примере пустое предложение OVER() означает, что границы окна явно не указаны. Поэтому, весь результирующий набор, который вернул SELECT, является одним большим окном для вычисления средней суммы транзакций.

SELECT *, AVG(txn_amount)
OVER() AS average_txn
FROM transactions;

Результат содержит дополнительный столбец average_txn, в котором хранится среднее значение для всех транзакций:

+----------------------+----------------------+--------------------------+------------------------+--------------+
| transactions.txn_id  | transactions.acc_id  | transactions.txn_amount  | transactions.txn_date  | average_txn  |
+----------------------+----------------------+--------------------------+------------------------+--------------+
| 1                    | 1002                 | 10.00                    | 2023-01-01             | 76.277778    |
| 2                    | 1002                 | 20.00                    | 2023-01-03             | 76.277778    |
| 3                    | 1002                 | 30.00                    | 2023-01-02             | 76.277778    |
| 4                    | 1001                 | 100.50                   | 2023-01-02             | 76.277778    |
| 5                    | 1001                 | 150.50                   | 2023-01-04             | 76.277778    |
| 6                    | 1001                 | 200.50                   | 2023-01-03             | 76.277778    |
| 7                    | 1003                 | 50.00                    | 2023-01-03             | 76.277778    |
| 8                    | 1003                 | 50.00                    | 2023-01-01             | 76.277778    |
| 9                    | 1003                 | 75.00                    | 2023-01-04             | 76.277778    |
+----------------------+----------------------+--------------------------+------------------------+--------------+

OVER + PARTITION BY

Использование конструкции OVER (PARTITION BY col) позволяет условно разделить результирующий набор на партиции (partitions), чтобы функция выполнялась для каждой отдельной партиции.

Например, следующий запрос вычисляет среднюю сумму транзакций для каждого аккаунта.

SELECT *, AVG(txn_amount)
OVER (PARTITION BY acc_id) AS average_txn_per_acc
FROM transactions;

Теперь в результатах содержатся средние значения, рассчитанные с привязкой к определенному аккаунту:

+----------------------+----------------------+--------------------------+------------------------+----------------------+
| transactions.txn_id  | transactions.acc_id  | transactions.txn_amount  | transactions.txn_date  | average_txn_per_acc  |
+----------------------+----------------------+--------------------------+------------------------+----------------------+
| 4                    | 1001                 | 100.50                   | 2023-01-02             | 150.500000           |
| 5                    | 1001                 | 150.50                   | 2023-01-04             | 150.500000           |
| 6                    | 1001                 | 200.50                   | 2023-01-03             | 150.500000           |
| 1                    | 1002                 | 10.00                    | 2023-01-01             | 20.000000            |
| 2                    | 1002                 | 20.00                    | 2023-01-03             | 20.000000            |
| 3                    | 1002                 | 30.00                    | 2023-01-02             | 20.000000            |
| 7                    | 1003                 | 50.00                    | 2023-01-03             | 58.333333            |
| 8                    | 1003                 | 50.00                    | 2023-01-01             | 58.333333            |
| 9                    | 1003                 | 75.00                    | 2023-01-04             | 58.333333            |
+----------------------+----------------------+--------------------------+------------------------+----------------------+

OVER + ORDER BY

С помощью ключевого слова ORDER можно реализовать сортировку в функции.

Например, следующий запрос вычисляет общую сумму, оплаченную каждым аккаунтом в определенный день, сортируя результаты по дате.

SELECT *, SUM(txn_amount)
OVER (PARTITION BY acc_id ORDER BY txn_date) AS running_total_paid
FROM transactions;

Теперь для каждого аккаунта строки отсортированы по дате транзакции. Обратите внимание, что столбец running_total_paid содержит значения нарастающего итога. Это происходит потому, что появление ORDER BY в описании окна приводит к неявному использованию RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW для обозначения предела фрейма окна. Таким образом, при подсчете среднего значения для каждой строки функция учитывает значение текущей строки + значения всех предыдущих строк в окне. Без ORDER BY общая сумма в столбце running_total_paid была бы одинаковой для всех записей.

+----------------------+----------------------+--------------------------+------------------------+---------------------+
| transactions.txn_id  | transactions.acc_id  | transactions.txn_amount  | transactions.txn_date  | running_total_paid  |
+----------------------+----------------------+--------------------------+------------------------+---------------------+
| 4                    | 1001                 | 100.50                   | 2023-01-02             | 100.50              |
| 6                    | 1001                 | 200.50                   | 2023-01-03             | 301.00              |
| 5                    | 1001                 | 150.50                   | 2023-01-04             | 451.50              |
| 1                    | 1002                 | 10.00                    | 2023-01-01             | 10.00               |
| 3                    | 1002                 | 30.00                    | 2023-01-02             | 40.00               |
| 2                    | 1002                 | 20.00                    | 2023-01-03             | 60.00               |
| 8                    | 1003                 | 50.00                    | 2023-01-01             | 50.00               |
| 7                    | 1003                 | 50.00                    | 2023-01-03             | 100.00              |
| 9                    | 1003                 | 75.00                    | 2023-01-04             | 175.00              |
+----------------------+----------------------+--------------------------+------------------------+---------------------+

Оконные функции

LEAD и LAG

Функции LEAD/LAG позволяют извлекать данные из следующей/предыдущей строки соответственно. Такие функции полезны для сравнения значений соседних строк или для вычисления разницы между строками.

Следующий запрос группирует результаты выборки по аккаунтам и для каждой строки отображает ID следующей транзакции, относящейся к данной группе.

SELECT txn_id, txn_amount, txn_date, LEAD(txn_id)
OVER (PARTITION BY acc_id ORDER BY txn_date) AS next_txn_id
FROM transactions;

Результат показан ниже. Значения NULL в столбце next_txn_id указывают на выход за пределы партиции, то есть в данной группе больше нет следующих строк.

+---------+-------------+-------------+--------------+
| txn_id  | txn_amount  |  txn_date   | next_txn_id  |
+---------+-------------+-------------+--------------+
| 4       | 100.50      | 2023-01-02  | 6            |
| 6       | 200.50      | 2023-01-03  | 5            |
| 5       | 150.50      | 2023-01-04  | NULL         |
| 1       | 10.00       | 2023-01-01  | 3            |
| 3       | 30.00       | 2023-01-02  | 2            |
| 2       | 20.00       | 2023-01-03  | NULL         |
| 8       | 50.00       | 2023-01-01  | 7            |
| 7       | 50.00       | 2023-01-03  | 9            |
| 9       | 75.00       | 2023-01-04  | NULL         |
+---------+-------------+-------------+--------------+

По умолчанию функции LEAD и LAG возвращают значения столбцов, которые находятся в 1 шаге от текущей строки, а также возвращают NULL, если достигнута граница партиции. Это поведение можно переопределить, используя следующую сигнатуру:

LEAD(col, i, j)
LAG(col, i, j)

Где:

  • col — имя столбца, для которого необходимо получить следующее/предыдущее значение.

  • i — шаг смещения. Количество строк до следующего/предыдущего результата.

  • j — значение, возвращаемое вместо NULL при достижении границ партиции.

ROW_NUMBER

Функция ROW_NUMBER() присваивает уникальный порядковый номер для каждой строки в партиции в соответствии с предложениями PARTITION BY и ORDER BY.

В следующем примере запрос получает первые 2 транзакции, совершенные каждым аккаунтом.

SELECT * FROM
     (SELECT *, ROW_NUMBER()
      OVER (PARTITION BY acc_id ORDER BY txn_date) AS row_num
      FROM transactions) tx
WHERE tx.row_num < 3;

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

+------------+------------+----------------+--------------+-------------+
| tx.txn_id  | tx.acc_id  | tx.txn_amount  | tx.txn_date  | tx.row_num  |
+------------+------------+----------------+--------------+-------------+
| 4          | 1001       | 100.50         | 2023-01-02   | 1           |
| 6          | 1001       | 200.50         | 2023-01-03   | 2           |
| 1          | 1002       | 10.00          | 2023-01-01   | 1           |
| 3          | 1002       | 30.00          | 2023-01-02   | 2           |
| 8          | 1003       | 50.00          | 2023-01-01   | 1           |
| 7          | 1003       | 50.00          | 2023-01-03   | 2           |
+------------+------------+----------------+--------------+-------------+

FIRST_VALUE и LAST_VALUE

Функции FIRST_VALUE/LAST_VALUE возвращают значение столбца из первой и последней строки партиции соответственно.

В следующем примере запрос возвращает наименьшее значение ID транзакций для каждой партиции аккаунтов.

SELECT txn_id, acc_id, FIRST_VALUE(txn_id)
OVER (PARTITION BY acc_id) lowest_id_per_acc
FROM transactions;

Результат:

+---------+---------+--------------------+
| txn_id  | acc_id  | lowest_id_per_acc  |
+---------+---------+--------------------+
| 4       | 1001    | 4                  |
| 5       | 1001    | 4                  |
| 6       | 1001    | 4                  |
| 1       | 1002    | 1                  |
| 2       | 1002    | 1                  |
| 3       | 1002    | 1                  |
| 7       | 1003    | 7                  |
| 8       | 1003    | 7                  |
| 9       | 1003    | 7                  |
+---------+---------+--------------------+

Обе функции FIRST_VALUE/LAST_VALUE принимают необязательный Boolean-параметр, который по умолчанию равен false. Если указать значение true, то при подсчете первой/последней строки Hive будет игнорировать строки со значениями столбцов NULL.

Аналитические функции

RANK и DENSE_RANK

Функция RANK присваивает ранг каждой строке в партиции. Если есть повторяющиеся значения, функция возвращает одинаковый ранг для таких строк, пропуская при этом следующий числовой ранг.

В следующем примере RANK вычисляет ранг каждой транзакции среди всех остальных транзакций (предложение PARTITION BY отсутствует). Для ранжирования транзакций по каждому отдельному аккаунту необходимо добавить предложение PARTITION BY acc_id.

SELECT txn_amount, acc_id, RANK()
OVER (ORDER BY txn_amount DESC) rank_global
-- ранжирование внутри аккаунта:
-- OVER (PARTITION BY acc_id ORDER BY txn_amount DESC) rank_per_acc
FROM transactions;

Результат:

+-------------+---------+--------------+
| txn_amount  | acc_id  | rank_global  |
+-------------+---------+--------------+
| 200.50      | 1001    | 1            |
| 150.50      | 1001    | 2            |
| 100.50      | 1001    | 3            |
| 75.00       | 1003    | 4            |
| 50.00       | 1003    | 5            |
| 50.00       | 1003    | 5            |
| 30.00       | 1002    | 7            |
| 20.00       | 1002    | 8            |
| 10.00       | 1002    | 9            |
+-------------+---------+--------------+

Обратите внимание, что в итоговом наборе результатов есть две транзакции с одинаковым рангом (5) в силу равных значений txn_amount. Поскольку этим двум строкам был присвоен одинаковый ранг 5, ранг 6 отсутствует в наборе результатов, а следующий доступный ранг — 7. Чтобы избежать подобных "пробелов" в последовательности рангов, используйте функцию DENSE_RANK. Если в предыдущем примере заменить RANK на DENSE_RANK, результат будет следующим:

+-------------+---------+--------------+
| txn_amount  | acc_id  | rank_global  |
+-------------+---------+--------------+
| 200.50      | 1001    | 1            |
| 150.50      | 1001    | 2            |
| 100.50      | 1001    | 3            |
| 75.00       | 1003    | 4            |
| 50.00       | 1003    | 5            |
| 50.00       | 1003    | 5            |
| 30.00       | 1002    | 6            |
| 20.00       | 1002    | 7            |
| 10.00       | 1002    | 8            |
+-------------+---------+--------------+

PERCENT_RANK

Функция PERCENT_RANK вычисляет ранг в процентном выражении (число от 0 до 1 включительно) для каждой строки в партиции. Указание ORDER BY в предложении OVER является обязательным. Функция добавляет к исходному набору результатов столбец с возрастающей числовой последовательностью, которая отражает позицию каждой строки внутри партиции. Первая строка в партиции всегда имеет значение 0, а последняя строка всегда равна 1.

Например, следующий запрос ранжирует транзакции, осуществленные с участием одного и того же аккаунта.

SELECT txn_id, acc_id, txn_amount, PERCENT_RANK()
OVER (PARTITION BY acc_id ORDER BY txn_amount) position_within_acc
FROM transactions;

Результат содержит столбец position_within_acc, который отражает позицию транзакции в партиции:

+---------+---------+-------------+-------------------+
| txn_id  | acc_id  | txn_amount  |position_within_acc|
+---------+---------+-------------+-------------------+
| 4       | 1001    | 100.50      | 0.0               |
| 5       | 1001    | 150.50      | 0.5               |
| 6       | 1001    | 200.50      | 1.0               |
| 1       | 1002    | 10.00       | 0.0               |
| 2       | 1002    | 20.00       | 0.5               |
| 3       | 1002    | 30.00       | 1.0               |
| 7       | 1003    | 50.00       | 0.0               |
| 8       | 1003    | 50.00       | 0.0               |
| 9       | 1003    | 75.00       | 1.0               |
+---------+---------+-------------+-------------------+

NTILE

Функция NTILE(n) разбивает отсортированный набор результатов на n примерно равных групп (buckets), а каждой строке присваивается число, указывающее, к какой группе принадлежит данная строка. Указание ORDER BY в предложении OVER является обязательным. С помощью этой универсальной функции можно вычислять такие статистические понятия, как медиана, процентиль, квартиль и так далее.

Следующий пример разбивает все транзакции на 4 группы (квартили) по их сумме и определяет, в какой квартиль попадает каждая транзакция. Обратите внимание, что партиция сортируется по столбцу txn_amount от наименьшего к большему, таким образом, самые маленькие транзакции попадают в первый квартиль.

SELECT txn_id, txn_amount, NTILE(4)
OVER (ORDER BY txn_amount) AS quartile
FROM transactions;

Результат:

+---------+-------------+-----------+
| txn_id  | txn_amount  | quartile  |
+---------+-------------+-----------+
| 1       | 10.00       | 1         |
| 2       | 20.00       | 1         |
| 3       | 30.00       | 1         |
| 7       | 50.00       | 2         |
| 8       | 50.00       | 2         |
| 9       | 75.00       | 3         |
| 4       | 100.50      | 3         |
| 5       | 150.50      | 4         |
| 6       | 200.50      | 4         |
+---------+-------------+-----------+
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней