← R2 SQL / r2-sql / sql-reference

Агрегатные функции

Агрегатные функции объединяют несколько строк в один результат. Они используются с GROUP BY чтобы вычислять сводные показатели по группам, либо без GROUP BY чтобы вычислить единый результат по всем строкам.

Большинство агрегатных функций принимают DISTINCT модификатор, например COUNT(DISTINCT customer_id) или SUM(DISTINCT total_amount).

Базовые агрегатные функции

COUNT

Подсчитывает строки. COUNT(*) подсчитывает все строки. COUNT(column) подсчитывает значения, отличные от NULL.

SELECT COUNT(*) AS total_rows
FROM my_namespace.sales_data

SELECT department, COUNT(*) AS dept_count
FROM my_namespace.sales_data
GROUP BY department
ORDER BY dept_count DESC

SUM

Возвращает сумму значений столбца.

SELECT SUM(total_amount) AS grand_total
FROM my_namespace.sales_data

SELECT department, SUM(total_amount) AS dept_total
FROM my_namespace.sales_data
GROUP BY department
ORDER BY dept_total DESC

AVG

Возвращает среднее значение столбца. Псевдоним: mean.

SELECT AVG(total_amount) AS avg_amount
FROM my_namespace.sales_data

SELECT department, AVG(total_amount) AS avg_amount
FROM my_namespace.sales_data
GROUP BY department
ORDER BY avg_amount DESC

MIN

Возвращает минимальное значение. Работает с числовыми и строковыми столбцами.

SELECT MIN(total_amount) AS min_amount, MIN(customer_id) AS first_customer
FROM my_namespace.sales_data

SELECT department, MIN(total_amount) AS min_amount
FROM my_namespace.sales_data
GROUP BY department

MAX

Возвращает максимальное значение. Работает с числовыми и строковыми столбцами.

SELECT MAX(total_amount) AS max_amount, MAX(customer_id) AS last_customer
FROM my_namespace.sales_data

SELECT department, MAX(total_amount) AS max_amount
FROM my_namespace.sales_data
GROUP BY department

MEDIAN

Возвращает точное значение медианы. Для больших наборов данных используйте approx_median взамен.

SELECT MEDIAN(total_amount) AS median_amount
FROM my_namespace.sales_data

SELECT department, MEDIAN(total_amount) AS median_amount
FROM my_namespace.sales_data
GROUP BY department

PERCENTILE_CONT

Возвращает точное значение заданного перцентиля с использованием WITHIN GROUP (ORDER BY ...). Параметр percentile должен находиться в диапазоне от 0.0 и 1.0 включительно. Для больших наборов данных используйте approx_percentile_cont взамен.

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_amount) AS median,
       PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total_amount) AS p95
FROM my_namespace.sales_data

Приближённые агрегатные функции

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

approx_percentile_cont

Возвращает приблизительное значение заданного перцентиля с использованием алгоритма T-Digest. Параметр percentile должен находиться между 0.0 и 1.0 включительно.

SELECT approx_percentile_cont(total_amount, 0.5) AS median,
       approx_percentile_cont(total_amount, 0.95) AS p95
FROM my_namespace.sales_data

SELECT department,
       approx_percentile_cont(total_amount, 0.5) AS median
FROM my_namespace.sales_data
GROUP BY department
ORDER BY median DESC

approx_percentile_cont_with_weight

Возвращает приблизительный взвешенный перцентиль. Строки взвешиваются по столбцу weight столбец.

SELECT approx_percentile_cont_with_weight(unit_price, quantity, 0.5) AS weighted_median
FROM my_namespace.sales_data
WHERE unit_price IS NOT NULL AND quantity IS NOT NULL

approx_median

Возвращает приблизительную медиану. Эквивалентно approx_percentile_cont(column, 0.5).

SELECT approx_median(total_amount) AS median_amount
FROM my_namespace.sales_data

SELECT department, approx_median(total_amount) AS median
FROM my_namespace.sales_data
GROUP BY department

approx_distinct

Возвращает приблизительное количество уникальных значений с использованием алгоритма HyperLogLog.

SELECT approx_distinct(customer_id) AS unique_customers
FROM my_namespace.sales_data

SELECT department, approx_distinct(customer_id) AS unique_customers
FROM my_namespace.sales_data
GROUP BY department

approx_top_k

Возвращает k наиболее часто встречающихся значений с их приближенным количеством.

SELECT approx_top_k(department, 5) AS top_departments
FROM my_namespace.sales_data

Статистические агрегатные функции

var / var_samp

Возвращает выборочную дисперсию.

SELECT var(total_amount) AS variance
FROM my_namespace.sales_data

SELECT department, var(total_amount) AS variance
FROM my_namespace.sales_data
GROUP BY department

var_pop

Возвращает генеральную дисперсию.

SELECT var_pop(total_amount) AS pop_variance
FROM my_namespace.sales_data

stddev / stddev_samp

Возвращает выборочное стандартное отклонение.

SELECT stddev(total_amount) AS std_dev
FROM my_namespace.sales_data

SELECT department, stddev(total_amount) AS std_dev
FROM my_namespace.sales_data
GROUP BY department

stddev_pop

Возвращает генеральное стандартное отклонение.

SELECT stddev_pop(total_amount) AS pop_std_dev
FROM my_namespace.sales_data

covar_samp

Возвращает выборочную ковариацию. Псевдоним: covar.

SELECT covar_samp(total_amount, CAST(quantity AS DOUBLE)) AS covariance
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

covar_pop

Возвращает генеральную ковариацию.

SELECT covar_pop(total_amount, CAST(quantity AS DOUBLE)) AS pop_covariance
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

corr

Возвращает коэффициент корреляции Пирсона между двумя столбцами.

SELECT corr(total_amount, CAST(quantity AS DOUBLE)) AS correlation
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

regr_slope

Возвращает наклон линии линейной регрессии.

SELECT regr_slope(total_amount, CAST(quantity AS DOUBLE)) AS slope
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

regr_intercept

Возвращает точку пересечения линии линейной регрессии с осью Y.

SELECT regr_intercept(total_amount, CAST(quantity AS DOUBLE)) AS intercept
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

regr_count

Возвращает количество пар без NULL.

SELECT regr_count(total_amount, CAST(quantity AS DOUBLE)) AS pair_count
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

regr_r2

Возвращает коэффициент детерминации (R-squared).

SELECT regr_r2(total_amount, CAST(quantity AS DOUBLE)) AS r_squared
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

regr_avgx

Возвращает среднее значение независимой переменной (x) для пар без NULL.

SELECT regr_avgx(total_amount, CAST(quantity AS DOUBLE)) AS avg_qty
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

regr_avgy

Возвращает среднее значение зависимой переменной (y) для пар без NULL.

SELECT regr_avgy(total_amount, CAST(quantity AS DOUBLE)) AS avg_amount
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

regr_sxx

Возвращает сумму квадратов независимой переменной.

SELECT regr_sxx(total_amount, CAST(quantity AS DOUBLE)) AS sxx
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

regr_syy

Возвращает сумму квадратов зависимой переменной.

SELECT regr_syy(total_amount, CAST(quantity AS DOUBLE)) AS syy
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

regr_sxy

Возвращает сумму произведений парных переменных.

SELECT regr_sxy(total_amount, CAST(quantity AS DOUBLE)) AS sxy
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

Битовые агрегатные функции

bit_and

Возвращает результат побитового И для всех значений в группе.

SELECT department, bit_and(quantity) AS and_result
FROM my_namespace.sales_data
WHERE quantity IS NOT NULL
GROUP BY department

bit_or

Возвращает результат побитового ИЛИ для всех значений в группе.

SELECT department, bit_or(quantity) AS or_result
FROM my_namespace.sales_data
WHERE quantity IS NOT NULL
GROUP BY department

bit_xor

Возвращает результат побитового исключающего ИЛИ для всех значений в группе.

SELECT department, bit_xor(quantity) AS xor_result
FROM my_namespace.sales_data
WHERE quantity IS NOT NULL
GROUP BY department

Логические агрегатные функции

bool_and

Возвращает true, если все значения в группе истинны.

SELECT department, bool_and(is_completed) AS all_completed
FROM my_namespace.sales_data
WHERE is_completed IS NOT NULL
GROUP BY department

bool_or

Возвращает true, если хотя бы одно значение в группе истинно.

SELECT department, bool_or(is_completed) AS any_completed
FROM my_namespace.sales_data
WHERE is_completed IS NOT NULL
GROUP BY department

Позиционные агрегатные функции

first_value

Возвращает первое значение в группе согласно заданному порядку сортировки.

SELECT department,
       first_value(customer_id ORDER BY total_amount ASC) AS lowest_spender
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
GROUP BY department

last_value

Возвращает последнее значение в группе согласно заданному порядку сортировки.

SELECT department,
       last_value(customer_id ORDER BY total_amount ASC) AS highest_spender
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
GROUP BY department

Агрегатные функции для коллекций

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

array_agg

Собирает значения группы в массив.

SELECT department, array_agg(customer_id) AS customers
FROM my_namespace.sales_data
GROUP BY department

string_agg

Объединяет значения группы в одну строку, разделённую заданным разделителем.

SELECT department, string_agg(customer_id, ', ') AS customer_list
FROM my_namespace.sales_data
GROUP BY department