跳转到内容
搜索文档
↵选择
↑↓导航
ESC关闭

聚合函数

最后更新 查看 MarkdownAgent 设置

聚合函数将多行折叠为单个结果。它们与 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 ...) 返回给定百分位数的精确值。百分位数参数必须在 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 算法返回给定百分位数的近似值。百分位数参数必须在 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

返回非 NULL 对的自变量 (x) 的平均值。

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

返回非 NULL 对的因变量 (y) 的平均值。

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

返回组中所有值的按位 AND。

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

bit_or

返回组中所有值的按位 OR。

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

bit_xor

返回组中所有值的按位 XOR。

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

布尔聚合

bool_and

如果组中的所有值为真，则返回真。

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

如果组中的任何值为真，则返回真。

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

这篇文档对您有帮助吗？