SQL 聚合函数

SQL 聚合函数会为每个组或 SQL 分区聚合指定列中的值，并为每个组返回一个包含聚合值的单行。

• 通用聚合函数
  • array_agg
  • avg
  • bit_and
  • bit_or
  • bit_xor
  • bool_and
  • bool_or
  • count
  • first_value
  • grouping
  • last_value
  • max
  • mean
  • median
  • min
  • nth_value
  • string_agg
  • sum
• 统计聚合函数
  • corr
  • covar
  • covar_pop
  • covar_samp
  • regr_avgx
  • regr_avgy
  • regr_count
  • regr_intercept
  • regr_r2
  • regr_slope
  • regr_sxx
  • regr_syy
  • regr_sxy
  • stddev
  • stddev_pop
  • stddev_samp
  • var
  • var_pop
  • var_population
  • var_samp
  • var_sample
• 近似聚合函数
  • approx_distinct
  • approx_median
  • approx_percentile_cont
  • approx_percentile_cont_with_weight

通用聚合函数

• array_agg
• avg
• bit_and
• bit_or
• bit_xor
• bool_and
• bool_or
• count
• first_value
• grouping
• last_value
• max
• mean
• median
• min
• nth_value
• string_agg
• sum

array_agg

从表达式元素创建数组。

array_agg(expression)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT
  room,
  array_agg(temp)[3] AS '3rd_temp'
FROM home
GROUP BY room

avg

返回指定列中数值的平均值。

avg(expression)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

别名

• mean

SELECT 
  location,
  avg(precip) AS avg_precip
FROM weather
GROUP BY location

bit_and

计算所有非空输入值的按位 AND。

bit_and(expression)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT 
  location,
  bit_and(precip::BIGINT) AS precip_bit_and
FROM weather
GROUP BY location

bit_or

计算所有非空输入值的按位 OR。

bit_or(expression)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT 
  location,
  bit_or(precip::BIGINT) AS precip_bit_or
FROM weather
GROUP BY location

bit_xor

计算所有非空输入值的按位异或。

bit_xor(expression)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT 
  location,
  bit_xor(precip::BIGINT) AS precip_bit_xor
FROM weather
GROUP BY location

bool_and

如果所有非空输入值均为 true，则返回 true，否则返回 false。

bool_and(expression)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT 
  location,
  bool_and(precip > 0) AS precip_bool_and
FROM weather
GROUP BY location

bool_or

如果任何非空输入值为 true，则返回 true，否则返回 false。

bool_or(expression)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT 
  location,
  bool_or(precip > 0) AS precip_bool_or
FROM weather
GROUP BY location

count

返回指定列中的行数。

Count 包括 null 值在内的总计数。要从总计数中排除 null 值，请在 WHERE 子句中包含 <column> IS NOT NULL。

count(expression)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT 
  location,
  count(precip) AS precip_count
FROM weather
GROUP BY location

first_value

根据指定的排序返回聚合组中的第一个元素。如果没有指定排序，则返回组中的任意元素。

first_value(expression [ORDER BY expression])

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT 
  location,
  first_value(temp_max ORDER BY time) AS temp_max_first_value
FROM weather
GROUP BY location

grouping

如果数据跨指定列进行聚合，则返回 1；如果未在结果集中进行聚合，则返回 0。

grouping(expression)

参数

• expression：用于评估数据是否跨指定列进行聚合的表达式。可以是常量、列或函数。

SELECT 
  location,
  avg(temp_max) AS avg_max_temp,
  grouping(location) AS grouping
FROM weather
GROUP BY GROUPING SETS ((location), ())

last_value

根据指定的排序返回聚合组中的最后一个元素。如果没有指定排序，则返回组中的任意元素。

last_value(expression [ORDER BY expression])

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT 
  location,
  last_value(temp_max ORDER BY time) AS temp_max_last_value
FROM weather
GROUP BY location

max

返回指定列中的最大值。

max(expression)

要同时返回最大值及其关联的时间戳，请使用 selector_max。

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT 
  location,
  max(precip) AS max_precip
FROM weather
GROUP BY location

mean

avg 的别名。

median

返回指定列中的中位数。

median(expression)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT 
  location,
  median(temp_avg) AS median_temp_avg
FROM weather
GROUP BY location

min

返回指定列中的最小值。

min(expression)

要同时返回最小值及其关联的时间戳，请使用 selector_max。

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT 
  location,
  min(temp_min) AS min_temp_min
FROM weather
GROUP BY location

nth_value

返回一组值中的第 n 个值。

nth_value(expression, n [ORDER BY order_expression_1, ... order_expression_n])

参数

• expression：从中检索第 n 个值的列或表达式。
• n：要检索的值的位置（第 n 个），基于排序。
• order_expression_1, … order_expression_n：用于排序的表达式。可以是列或函数，以及任意算术运算符的组合。

SELECT
  room,
  nth_value(temp, 3 ORDER BY time) AS "3rd_temp"
FROM
  home
GROUP BY
  room

string_agg

连接字符串表达式的值，并在它们之间放置分隔符。

string_agg(expression, delimiter)

参数

• expression：要连接的字符串表达式。可以是列或任何有效的字符串表达式。
• delimiter：用作连接值之间分隔符的文字字符串。

SELECT
  location,
  string_agg(temp_avg::STRING, ', ') AS string_agg
FROM 
  weather
WHERE
  time > '2020-01-01T00:00:00Z'
  AND time < '2020-01-05T00:00:00Z'
GROUP BY
  location

sum

返回指定列中所有值的总和。

sum(expression)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT 
  location,
  sum(precip) AS total_precip
FROM weather
GROUP BY location

统计聚合函数

• corr
• covar
• covar_pop
• covar_samp
• regr_avgx
• regr_avgy
• regr_count
• regr_intercept
• regr_r2
• regr_slope
• regr_sxx
• regr_syy
• regr_sxy
• stddev
• stddev_pop
• stddev_samp
• var
• var_pop
• var_population
• var_samp
• var_sample

corr

返回两个数值之间的相关系数。

corr(expression1, expression2)

参数

• expression1：要操作的第一个列或文字值。
• expression2：要操作的第二个列或文字值。

SELECT
  room,
  corr(hum, temp) AS correlation
FROM home
GROUP BY room

covar

返回一组数值对的协方差。

covar(expression1, expression2)

参数

• expression1：要操作的第一个列或文字值。
• expression2：要操作的第二个列或文字值。

SELECT
  room,
  covar(hum, temp) AS covar
FROM home
GROUP BY room

covar_pop

返回一组数值对的总体协方差。

covar_pop(expression1, expression2)

参数

• expression1：要操作的第一个列或文字值。
• expression2：要操作的第二个列或文字值。

SELECT
  room,
  covar_pop(hum, temp) AS covar_pop
FROM home
GROUP BY room

covar_samp

返回一组数值对的样本协方差。

covar_samp(expression1, expression2)

参数

• expression1：要操作的第一个列或文字值。
• expression2：要操作的第二个列或文字值。

SELECT
  room,
  covar_samp(hum, temp) AS covar_samp
FROM home
GROUP BY room

regr_avgx

计算非空因变量 expression_y 的自变量（输入）expression_x 的平均值。

regr_avgx(expression_y, expression_x)

参数

• expression_y：因变量。可以是常量、列或函数，以及任意算术运算符的组合。
• expression_x：自变量。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT 
  location,
  regr_avgx(temp_min, temp_max) AS temp_regr_avgx
FROM weather
GROUP BY location

regr_avgy

计算非空因变量 expression_y 的因变量（输出）expression_y 的平均值。

regr_avgy(expression_y, expression_x)

参数

• expression_y：因变量。可以是常量、列或函数，以及任意算术运算符的组合。
• expression_x：自变量。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT 
  location,
  regr_avgy(temp_min, temp_max) AS temp_regr_avgy
FROM weather
GROUP BY location

regr_count

计算非空配对数据的数量。

regr_count(expression_y, expression_x)

参数

• expression_y：因变量。可以是常量、列或函数，以及任意算术运算符的组合。
• expression_x：自变量。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT 
  location,
  regr_count(temp_min, temp_max) AS temp_regr_count
FROM weather
GROUP BY location

regr_intercept

计算线性回归线的 y 截距。对于方程 (y = kx + b)，此函数返回 b。

regr_intercept(expression_y, expression_x)

参数

• expression_y：因变量。可以是常量、列或函数，以及任意算术运算符的组合。
• expression_x：自变量。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT 
  location,
  regr_intercept(temp_min, temp_max) AS temp_regr_intercept
FROM weather
GROUP BY location

regr_r2

计算自变量和因变量之间的相关系数的平方。

regr_r2(expression_y, expression_x)

参数

• expression_y：因变量。可以是常量、列或函数，以及任意算术运算符的组合。
• expression_x：自变量。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT 
  location,
  regr_r2(temp_min, temp_max) AS temp_regr_r2
FROM weather
GROUP BY location

regr_slope

返回聚合列中非空对的线性回归线的斜率。给定输入列 Y 和 X：regr_slope(Y, X) 使用最小 RSS 拟合返回斜率（Y = k*X + b 中的 k）。

regr_slope(expression_y, expression_x)

参数

• expression_y：要操作的 Y 表达式。可以是常量、列或函数，以及任意算术运算符的组合。
• expression_x：要操作的 X 表达式。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT 
  location,
  regr_slope(temp_min, temp_max) AS temp_regr_slope
FROM weather
GROUP BY location

regr_sxx

计算自变量的平方和。

regr_sxx(expression_y, expression_x)

参数

• expression_y：因变量。可以是常量、列或函数，以及任意算术运算符的组合。
• expression_x：自变量。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT 
  location,
  regr_sxx(temp_min, temp_max) AS temp_regr_sxx
FROM weather
GROUP BY location

regr_syy

计算因变量的平方和。

regr_syy(expression_y, expression_x)

参数

• expression_y：因变量。可以是常量、列或函数，以及任意算术运算符的组合。
• expression_x：自变量。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT 
  location,
  regr_syy(temp_min, temp_max) AS temp_regr_syy
FROM weather
GROUP BY location

regr_sxy

计算配对数据点的乘积和。

regr_sxy(expression_y, expression_x)

参数

• expression_y：因变量。可以是常量、列或函数，以及任意算术运算符的组合。
• expression_x：自变量。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT 
  location,
  regr_sxy(temp_min, temp_max) AS temp_regr_sxy
FROM weather
GROUP BY location

stddev

返回一组数字的标准差。

stddev(expression)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT
  room,
  stddev(co) AS stddev
FROM home
GROUP BY room

stddev_pop

返回一组数字的总体标准差。

stddev_pop(expression)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT
  room,
  stddev_pop(co) AS stddev_pop
FROM home
GROUP BY room

stddev_samp

返回一组数字的样本标准差。

stddev_samp(expression)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT
  room,
  stddev_samp(co) AS stddev_samp
FROM home
GROUP BY room

var

返回一组数字的统计方差。

var(expression)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT
  room,
  var(co) AS var
FROM home
GROUP BY room

var_pop

返回一组数字的统计总体方差。

var_pop(expression)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

别名

• var_population

SELECT
  room,
  var_pop(co) AS var_pop
FROM home
GROUP BY room

var_population

var_pop 的别名。

var_samp

返回一组数字的统计样本方差。

var_samp(expression)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

别名

• var_sample

SELECT
  room,
  var_samp(co) AS var_samp
FROM home
GROUP BY room

var_sample

var_samp 的别名。

近似聚合函数

• approx_distinct
• approx_median
• approx_percentile_cont
• approx_percentile_cont_with_weight

approx_distinct

使用 HyperLogLog 算法计算不同输入值的近似数量。

approx_distinct(expression)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT
  room,
  approx_distinct(co::string) AS approx_distinct
FROM home
GROUP BY room

approx_median

返回输入值的近似中位数（第 50 百分位数）。它是 approx_percentile_cont(0.5) WITHIN GROUP (ORDER BY expression) 的别名。

approx_median(expression)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

SELECT
  room,
  approx_median(temp) AS approx_median
FROM home
GROUP BY room

approx_percentile_cont

使用 t-digest 算法返回输入值的近似百分比。

approx_percentile_cont(percentile [, centroids]) WITHIN GROUP (ORDER BY expression)
-- OR
approx_percentile_cont(expression, percentile, centroids)

参数

• percentile：要计算的百分比。必须是介于 0 和 1（含）之间的浮点值。

• centroids：t-digest 算法中要使用的质心数量。默认值为 100。较高的数值会提高近似精度，但需要更多内存。

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。

  如果唯一值的数量等于或少于此数量，则可以预期获得精确结果。质心数量越多，近似越精确，但计算所需的内存也越多。

SELECT
  room,
  approx_percentile_cont(0.99) WITHIN GROUP (ORDER BY temp) AS "99th_percentile"
FROM home
GROUP BY room

approx_percentile_cont_with_weight

使用 t-digest 算法返回输入值的加权近似百分比。

approx_percentile_cont_with_weight(weight, percentile [, centroids]) WITHIN GROUP (ORDER BY expression)
-- OR
approx_percentile_cont_with_weight(expression, weight, percentile)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任意算术运算符的组合。
• weight：要用作权重的表达式。可以是常量、列或函数，以及任意算术运算符的组合。
• percentile：要计算的百分比。必须是介于 0 和 1（含）之间的浮点值。
• centroids：t-digest 算法中要使用的质心数量。默认值为 100。较高的数值会提高近似精度，但需要更多内存。

SELECT
  room,
  approx_percentile_cont_with_weight(co, 0.99) WITHIN GROUP (ORDER BY temp) AS "co_weighted_99th_percentile"
FROM home
GROUP BY room