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
• 计数
• first_value
• last_value
• 最大值
• 均值
• 中位数
• min
• 求和

数组聚合

返回一个由表达式元素创建的数组。

array_agg(expression)

参数

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

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

平均值

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

avg(expression)

参数

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

别名

• mean

SELECT 
  location,
  avg(water_level) AS water_level_avg
FROM h2o_feet
GROUP BY location

位与

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

bit_and(expression)

参数

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

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

位或

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

bit_or(expression)

参数

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

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

位异或

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

bit_xor(expression)

参数

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

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

布尔与

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

bool_and(expression)

参数

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

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

布尔或

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

bool_or(expression)

参数

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

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

计数

返回指定列中的行数。

计数包括 null 值在总计数中。
要从总计数中排除 null 值，请在 WHERE 子句中包含 IS NOT NULL。

count(expression)

参数

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

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

首个值

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

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(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(expression)

要返回最大值及其相关时间戳，请使用 selector_max.

参数

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

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

均值

avg 的别名。

中位数

返回指定列中的中位数值。

median(expression)

参数

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

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

最小值

返回指定列中的最小值。

min(expression)

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

参数

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

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

总和

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

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_pop
• stddev_samp
• 变量
• var_pop
• var_samp

相关性

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

corr(expression1, expression2)

参数

• expression1: 第一个操作的列或字面值。
• expression2: 用于操作的第二列或字面值。

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

协方差

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

covar(expression1, expression2)

参数

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

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

总体协方差

返回一组数字对的种群协方差。

covar_pop(expression1, expression2)

参数

• expression1: 要操作的第一列或字面值。
• expression2: 第二列或操作的常量值。

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

样本协方差

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

covar_samp(expression1, expression2)

参数

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

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

回归平均值

计算自变量（输入）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

平均回归

计算依赖变量（输出）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(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

回归截距

计算线性回归线的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

回归R平方

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

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

回归斜率

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

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(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

回归_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

回归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(expression)

参数

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

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

总体标准差

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

stddev_pop(expression)

参数

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

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

样本标准差

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

stddev_samp(expression)

参数

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

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

变量

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

var(expression)

参数

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

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

方差

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

var_pop(expression)

参数

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

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

方差样本

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

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

近似唯一值

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

approx_distinct(expression)

参数

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

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

近似中位数

返回输入值的近似中位数（第50百分位数）。它是 approx_percentile_cont(x, 0.5) 的别名。

approx_median(expression)

参数

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

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

近似百分位数连续

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

approx_percentile_cont(expression, percentile, centroids)

参数

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

• percentile: 要计算的百分位数。必须是一个介于 0 和 1 之间（包括 0 和 1）的浮点值。

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

  如果唯一值的数量等于或少于这个数字，您可以期待一个精确的结果。 更高数量的质心会导致更精确的近似，但 需要更多的内存来计算。

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

加权近似分位数连续

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

approx_percentile_cont_with_weight(expression, weight, percentile)

参数

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

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