SQL聚合函数

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

• 通用聚合函数
  • array_agg
  • avg
  • bit_and
  • bit_or
  • bit_xor
  • bool_and
  • bool_or
  • count
  • first_value
  • last_value
  • max
  • mean
  • median
  • min
  • 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_samp
• 近似聚合函数
  • 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
• last_value
• max
• mean
• median
• min
• 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(water_level) AS water_level_avg
FROM h2o_feet
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

计算所有非空输入值的按位异或（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

返回指定列中的行数。

计数包括 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

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(water_level) AS water_level_max
FROM h2o_feet
GROUP BY location

mean

avg 的别名。

median

返回指定列中的中值。

median(expression)

参数

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

SELECT 
  location,
  median(water_level) AS water_level_max
FROM h2o_feet
GROUP BY location

min

返回指定列中的最小值。

min(expression)

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

参数

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

SELECT 
  location,
  min(water_level) AS water_level_min
FROM h2o_feet
GROUP BY location

sum

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

sum(expression)

参数

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

SELECT 
  location,
  sum(water_level) AS water_level_sum
FROM h2o_feet
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_samp

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_x 的平均值，其中 expression_y 为非空因变量。

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 的平均值。

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 = 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) 返回斜率（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：要操作的表达式。可以是常量、列或函数，以及任何算术运算符的组合。

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

var_samp

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

var_samp(expression)

参数

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

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

近似聚合函数

• 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(x, 0.5) 的别名。

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(expression, percentile, centroids)

参数

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

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

• centroids：在 t-digest 算法中使用的中心点数量。默认为 100。

  如果有这个数量或更少的唯一值，您可以得到精确的结果。更多的中心点数量会导致更精确的近似，但需要更多的内存来计算。

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

approx_percentile_cont_with_weight

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

approx_percentile_cont_with_weight(expression, weight, percentile)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及任何算术运算符的组合。
• 权重：用作权重的表达式。可以是常数、列或函数，以及任意组合的算术运算符。
• percentile：要计算的百分位数。必须是介于 0 和 1（含）之间的浮点值。

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