Агрегатные функции в Hive
Агрегатные функции Hive — это встроенные функции, которые принимают несколько значений и возвращают одно агрегированное значение. В данной статье приведены примеры и рекомендации по использованию базовых и расширенных агрегатных функций Hive. Полный список агрегатных функций, поддерживаемых в Hive по умолчанию, доступен в документации Apache Hive.
Тестовая база данных
В данной статье для демонстрации агрегатных функций используются Hive-таблицы employees и transactions, структура которых описана ниже.
SELECT * FROM employees; +---------------+-----------------+----------------------+----------------------------+----------------+-------------------+--------------------+ | employees.id | employees.name | employees.last_name | employees.email | employees.age | employees.salary | employees.dept_id | +---------------+-----------------+----------------------+----------------------------+----------------+-------------------+--------------------+ | 1 | Ivan | Ivanov | ivan_ivanov123@mail.ru | 30 | 1000 | 1 | | 2 | Sarah | Connor | sarah_connor123@yahoo.com | 35 | 1500 | 2 | | 3 | Rick | Sanchez | rick_123@mail.ru | 29 | 1300 | 2 | | 4 | John | Smith | john_smith123@gmail.com | 29 | 2000 | 3 | +---------------+-----------------+----------------------+----------------------------+----------------+-------------------+--------------------+ SELECT * FROM transactions; +----------------------+----------------------+--------------------------+------------------------+ | transactions.txn_id | transactions.acc_id | transactions.txn_amount | transactions.txn_date | +----------------------+----------------------+--------------------------+------------------------+ | 1 | 101 | 10.20 | 2023-10-26 | | 2 | 101 | 102.30 | 2023-10-26 | | 3 | 102 | 95.00 | 2023-10-26 | | 4 | 103 | 900.50 | 2023-10-27 | | 5 | 102 | 910.50 | 2023-10-27 | | 6 | 101 | 850.00 | 2023-10-27 | +----------------------+----------------------+--------------------------+------------------------+
Для создания и наполнения тестовых таблиц Hive выполните следующий SQL с помощью /bin/beeline.
CREATE TABLE IF NOT EXISTS employees (id int, name string, last_name string, email string, age int, salary int, dept_id int)
COMMENT 'Employee Table'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
INSERT INTO employees VALUES
(1, 'Ivan', 'Ivanov', 'ivan_ivanov123@mail.ru', 30, 1000, 1),
(2, 'Sarah', 'Connor', 'sarah_connor123@yahoo.com', 35, 1500, 2),
(3, 'Rick', 'Sanchez', 'rick_123@mail.ru', 29, 1300, 2),
(4, 'John', 'Smith', 'john_smith123@gmail.com', 29, 2000, 3);
CREATE TABLE IF NOT EXISTS transactions(txn_id int, acc_id int, txn_amount decimal(10,2), txn_date date)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe';
INSERT INTO transactions VALUES
(1, 101, 10.20, '2023-10-26'),
(2, 101, 102.30, '2023-10-26'),
(3, 102, 95.00, '2023-10-26'),
(4, 103, 900.50, '2023-10-27'),
(5, 102, 910.50, '2023-10-27'),
(6, 101, 850.00, '2023-10-27');Базовая агрегация
Функция COUNT(col) возвращает общее количество строк в результирующем наборе данных.
SELECT COUNT(id) AS emp_total_cnt FROM employees;Результат:
+----------------+ | emp_total_cnt | +----------------+ | 4 | +----------------+
|
РЕКОМЕНДАЦИЯ
С помощью ключевого слова DISTINCT можно получить агрегированное значение только для уникальных значений в столбце.
|
Функции MIN() и MAX() возвращают минимальное/максимальное значение числового столбца.
SELECT MIN(salary) from employees;Результат:
+-------+ | _c0 | +-------+ | 1000 | +-------+
Функция AVG() возвращает среднее значение числового столбца.
Функция не может использоваться внутри предложения WHERE.
Например, следующий запрос приведет к ошибке:
SELECT e.salary, e.age
FROM employees e
WHERE e.age > AVG(e.age);Текст ошибки:
Error: Error while compiling statement: FAILED: SemanticException [Error 10128]: Line 3:14 Not yet supported place for UDAF 'avg' (state=42000,code=10128)
В этом конкретном случае в качестве обходного пути можно вложить AVG() внутрь подзапроса как показано ниже.
SELECT e.age, e.last_name
FROM employees e
WHERE e.age > (SELECT AVG(age) FROM employees);Результат успешного выполнения запроса:
+--------+--------------+ | e.age | e.last_name | +--------+--------------+ | 35 | Connor | +--------+--------------+
Другие агрегатные функции Hive
Помимо стандартных агрегатных функций SQL, таких как SUM, AVG и прочих, Hive предоставляет дополнительные функции, которые могут пригодиться при анализе наборов данных.
Например, с помощью collect_*(col) можно упаковать все значения столбца col в список или множество.
SELECT collect_set(age) FROM employees; (1)
SELECT d.dep_name AS department_name, collect_list(e.age) AS ages (2)
FROM employees e
JOIN departments d
ON e.dept_id=d.id
GROUP BY d.dep_name;| 1 | Возвращает единственную строку с множеством уникальных значений. |
| 2 | Возвращает список значений для каждого департамента. |
Результат:
+-------------+ | _c0 | +-------------+ | [30,35,29] | +-------------+ +------------------+----------+ | department_name | ages | +------------------+----------+ | it | [35,29] | | sales | [30] | | support | [29] | +------------------+----------+
Hive также предоставляет специальные функции для вычисления математических показателей, таких как отклонение, дисперсия и другие. Примеры использования некоторых из них показаны ниже.
SELECT variance(salary) FROM employees; (1)
SELECT var_pop(salary) FROM employees; (1)
SELECT var_samp(salary) FROM employees; (2)
SELECT stddev_pop(salary) FROM employees; (3)
SELECT stddev_samp(salary) FROM employees; (4)| 1 | Запросы возвращают показатель дисперсии для числового столбца salary. |
| 2 | Возвращает показатель несмещенной выборочной дисперсии (unbiased sample variance) для столбца. |
| 3 | Возвращает статистическое стандартное отклонение для значений в столбце. |
| 4 | Возвращает показатель несмещенного выборочного стандартного отклонения (unbiased sample standard deviation) для числового столбца. |
Результат:
SELECT variance(salary) FROM employees; +-----------+ | _c0 | +-----------+ | 132500.0 | +-----------+ SELECT var_pop(salary) FROM employees; +-----------+ | _c0 | +-----------+ | 132500.0 | +-----------+ SELECT var_samp(salary) FROM employees; +---------------------+ | _c0 | +---------------------+ | 176666.66666666666 | +---------------------+ SELECT stddev_pop(salary) FROM employees; +--------------------+ | _c0 | +--------------------+ | 364.0054944640259 | +--------------------+ SELECT stddev_samp(salary) FROM employees; +---------------------+ | _c0 | +---------------------+ | 420.31734043061635 | +---------------------+
Полный список поддерживаемых агрегатных функций доступен в документации Apache Hive.
Расширенная агрегация
GROUPING SETS
Предложение GROUPING SETS, используемое в паре с GROUP BY, позволяет указывать несколько критериев GROUP BY в одном запросе.
Использование нескольких группирующих наборов (например, GROUPING SETS (a,b)) эквивалентно выполнению нескольких запросов с разными выражениями GROUP BY a, GROUP BY b и объединенных с помощью UNION.
Стоит отметить, что выборка с помощью группирующих наборов выполняется за одно сканирование таблицы, а не в несколько итераций, как в случае с отдельными запросами, объединенными UNION.
В следующем примере используется только один группирующий набор (GROUPING SETS (acc_id)), который группирует результаты по аккаунту, но без агрегации по дате.
SELECT acc_id, txn_date, SUM(txn_amount) as total_paid
FROM transactions
GROUP BY acc_id, txn_date
GROUPING SETS (acc_id);Результатом такого запроса является общая сумма, оплаченная каждым аккаунтом.
Значения NULL в столбце txn_date указывают на отсутствие агрегации по дате.
+---------+-----------+-------------+ | acc_id | txn_date | total_paid | +---------+-----------+-------------+ | 101 | NULL | 962.50 | | 102 | NULL | 1005.50 | | 103 | NULL | 900.50 | +---------+-----------+-------------+
В следующем примере показано использование нескольких группирующих наборов (GROUPING SETS (acc_id, txn_date)).
SELECT acc_id, txn_date, SUM(txn_amount) as total_paid, grouping__id
FROM transactions
GROUP BY acc_id, txn_date
GROUPING SETS (acc_id, txn_date);В этом случае результат выборки содержит наборы строк, сгруппированных по txn_date и acc_id:
+---------+-------------+-------------+ | acc_id | txn_date | total_paid | +---------+-------------+-------------+ | NULL | 2023-10-26 | 207.50 | | NULL | 2023-10-27 | 2661.00 | | 101 | NULL | 962.50 | | 102 | NULL | 1005.50 | | 103 | NULL | 900.50 | +---------+-------------+-------------+
Значения NULL указывают, что агрегация по данному столбцу не выполнялась.
Обратите внимание, что полученный результат представляет собой UNION двух результатов, полученных с помощью следующих запросов:
SELECT NULL, txn_date, SUM(txn_amount)
FROM transactions
GROUP BY txn_date
UNION
SELECT acc_id, NULL, SUM(txn_amount)
FROM transactions
GROUP BY acc_id;|
ВАЖНО
Выполнение отдельных запросов с |
WITH CUBE
Функция CUBE используется вместе с GROUP BY для вычисления всех возможных комбинаций агрегации столбцов, указанных в GROUP BY.
Следующие выражения HiveQL тождественны:
... GROUP BY a, b, c WITH CUBE
-- эквивалентно:
... GROUP BY a, b, c GROUPING SETS (
(a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( )
)Ниже показан пример использования WITH CUBE.
SELECT acc_id, txn_date, SUM(txn_amount) as total_sum
FROM transactions
GROUP BY acc_id, txn_date WITH CUBE;Результат содержит комбинацию всех возможных способов группировки данных:
+---------+-------------+-------------+ | acc_id | txn_date | total_sum | +---------+-------------+-------------+ | NULL | NULL | 2868.50 | | NULL | 2023-10-26 | 207.50 | | NULL | 2023-10-27 | 2661.00 | | 101 | NULL | 962.50 | | 101 | 2023-10-26 | 112.50 | | 101 | 2023-10-27 | 850.00 | | 102 | NULL | 1005.50 | | 102 | 2023-10-26 | 95.00 | | 102 | 2023-10-27 | 910.50 | | 103 | NULL | 900.50 | | 103 | 2023-10-27 | 900.50 | +---------+-------------+-------------+
В этом примере использование конструкции WITH CUBE эквивалентно следующему запросу:
SELECT acc_id, txn_date, SUM(txn_amount) as total_sum
FROM transactions
GROUP BY acc_id, txn_date
GROUPING SETS ((acc_id, txn_date), acc_id, txn_date, ( ));WITH ROLLUP
Функция ROLLUP используется вместе с GROUP BY для вычисления нескольких уровней промежуточных результатов по заданной группе измерений.
Функция вычисляет промежуточные результаты агрегации от самого детального уровня до самого общего, следуя списку критериев, указанных в GROUP BY.
Следующие конструкции HiveQL тождественны:
... GROUP BY a, b, c, WITH ROLLUP
-- эквивалентно:
... GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (a), ( ))Ниже показан пример использования WITH ROLLUP.
SELECT acc_id, txn_date, SUM(txn_amount) as total_sum
FROM transactions
GROUP BY acc_id, txn_date WITH ROLLUP;Результат содержит записи с различными уровнями агрегации — от наиболее детальной (сумма, оплаченная определенным аккаунтом в конкретный день) до наиболее общих показателей (общая сумма уплаченных денег).
+---------+-------------+------------+ | acc_id | txn_date | total_sum | +---------+-------------+------------+ | NULL | NULL | 2868.50 | | 101 | NULL | 962.50 | | 101 | 2023-10-26 | 112.50 | | 101 | 2023-10-27 | 850.00 | | 102 | NULL | 1005.50 | | 102 | 2023-10-26 | 95.00 | | 102 | 2023-10-27 | 910.50 | | 103 | NULL | 900.50 | | 103 | 2023-10-27 | 900.50 | +---------+-------------+------------+
В этом примере использование конструкции ROLLUP эквивалентно следующему запросу:
SELECT acc_id, txn_date, SUM(txn_amount) as total_sum
FROM transactions
GROUP BY acc_id, txn_date
GROUPING SETS ((acc_id, txn_date), acc_id, ( ));