聚合函数。包括了很多数学上的函数公式。非常方便。
Function | Argument Type(s) | Return Type | Partial Mode | Description |
array_agg(expression) | any non-array type | array of the argument type | No | 输入值,包括空值,连接到一个数组中 |
array_agg(expression) | any array type | same as argument data type | No | 输入数组连接到一个更高维度的数组(输入必须都具有相同的维数,不能为空或空 |
avg(expression) | smallint, int, bigint, real, double precision,numeric, or interval | numeric for any integer-type argument, double precision for a floating-point argument, otherwise the same as the argument data type | Yes | 所有输入值的平均值(算术平均值) |
bit_and(expression) | smallint, int, bigint, orbit | same as argument data type | Yes | 所有非空输入值的按位和,或如果没有,则为空。 |
bit_or(expression) | smallint, int, bigint, orbit | same as argument data type | Yes | 所有非空输入值的按位或,如果没有,则为空。 |
bool_and(expression) | bool | bool | Yes | 如果所有输入值为true,否则为false |
bool_or(expression) | bool | bool | Yes | 如果至少一个输入值为true,则为true,否则为false |
count(*) |
| bigint | Yes | 输入的行数 |
count(expression) | any | bigint | Yes | 表达式的值不是空的输入行数 |
every(expression) | bool | bool | Yes | 等价于布尔和 |
json_agg(expression) | any | json | No | 作为JSON数组的聚合值 |
jsonb_agg(expression) | any | jsonb | No | 作为JSON数组的聚合值 |
json_object_agg(name,value) | (any, any) | json | No | 将名称/值对聚合为JSON对象 |
jsonb_object_agg(name,value) | (any, any) | jsonb | No | 将名称/值对聚合为JSON对象 |
max(expression) | any numeric, string, date/time, network, or enum type, or arrays of these types | same as argument type | Yes | 所有输入值表达式的最大值 |
min(expression) | any numeric, string, date/time, network, or enum type, or arrays of these types | same as argument type | Yes | 所有输入值表达式的最小值 |
string_agg(expression,delimiter) | (text, text) or (bytea,bytea) | same as argument types | No | 连接到字符串中的输入值,由分隔符分隔 |
sum(expression) | smallint, int, bigint, real, double precision,numeric, interval, or money | bigint for smallint or int arguments,numeric for bigint arguments, otherwise the same as the argument data type | Yes | 所有输入值表达式的和 |
xmlagg(expression) | xml | xml | No | XML值的连接 |
Function | Argument Type | Return Type | Partial Mode | Description |
corr(Y, X) | double precision | double precision | Yes | 相关系数 |
covar_pop(Y, X) | double precision | double precision | Yes | 总体协方差 |
covar_samp(Y, X) | double precision | double precision | Yes | 样本协方差 |
regr_avgx(Y, X) | double precision | double precision | Yes | 独立变量的平均数(和(x)/n) |
regr_avgy(Y, X) | double precision | double precision | Yes | 因变量的平均值(和(y)/n) |
regr_count(Y, X) | double precision | bigint | Yes | 两个表达式均为非空的输入行数 |
regr_intercept(Y, X) | double precision | double precision | Yes | 由(x,y)对确定的最小二乘拟合线性方程的Y截距 |
regr_r2(Y, X) | double precision | double precision | Yes | 相关系数的平方 |
regr_slope(Y, X) | double precision | double precision | Yes | 由(x,y)对确定的最小二乘拟合线性方程的斜率 |
regr_sxx(Y, X) | double precision | double precision | Yes | 和(x^ 2)-和(x)^ 2/n*(自变量的平方和) |
regr_sxy(Y, X) | double precision | double precision | Yes | 和(x*y)-和(x)*和(y)/n*(独立乘积因变量的乘积和) |
regr_syy(Y, X) | double precision | double precision | Yes | 和(y ^ 2)-和(y)^ 2/n(因变量的平方和) |
stddev(expression) | smallint, int, bigint, real, double precision, or numeric | double precision for floating-point arguments, otherwise numeric | Yes | stddev_samp历史别名 |
stddev_pop(expression) | smallint, int, bigint, real, double precision, or numeric | double precision for floating-point arguments, otherwise numeric | Yes | 输入值的标准差 |
stddev_samp(expression) | smallint, int, bigint, real, double precision, or numeric | double precision for floating-point arguments, otherwise numeric | Yes | 输入值的样本标准偏差 |
variance(expression) | smallint, int, bigint, real, double precision, or numeric | double precision for floating-point arguments, otherwise numeric | Yes | var_samp的历史别名 |
var_pop(expression) | smallint, int, bigint, real, double precision, or numeric | double precision for floating-point arguments, otherwise numeric | Yes | 输入值的方差(标准偏差的平方) |
var_samp(expression) | smallint, int, bigint, real, double precision, or numeric | double precision for floating-point arguments, otherwise numeric | Yes | 输入值的样本方差(样本标准偏差的平方) |
Function | Direct Argument Type(s) | Aggregated Argument Type(s) | Return Type | Partial Mode | Description |
mode() WITHIN GROUP (ORDER BY sort_expression) | any sortable type | same as sort expression | No | 返回最频繁的输入值(如果有多个同样频繁的结果,则任意选择第一个 | |
percentile_cont(fraction) WITHIN GROUP (ORDER BYsort_expression) | double precision | double precisionor interval | same as sort expression | No | 连续百分位数:返回对应于排序中指定分数的值,如果需要,在相邻输入项之间进行插值。 |
percentile_cont(fractions) WITHIN GROUP (ORDER BYsort_expression) | double precision[] | double precisionor interval | array of sort expression's type | No | 多个连续百分位数:返回一个与“分数”参数的形状相匹配的结果数组,每个非空元素由对应于百分位数的值替换。 |
percentile_disc(fraction) WITHIN GROUP (ORDER BYsort_expression) | double precision | any sortable type | same as sort expression | No | 离散百分位数:返回在排序中的位置等于或超过指定分数的第一个输入值。 |
percentile_disc(fractions) WITHIN GROUP (ORDER BYsort_expression) | double precision[] | any sortable type | array of sort expression's type | No | 多个离散百分位数:返回一个与“分数”参数的形状相匹配的结果数组,每个非空元素由对应于该百分位数的输入值代替。 |
Function | Direct Argument Type(s) | Aggregated Argument Type(s) | Return Type | Partial Mode | Description |
rank(args) WITHIN GROUP (ORDER BYsorted_args) | VARIADIC "any" | VARIADIC "any" | bigint | No | 假设行的秩,具有重复行的间隙 |
dense_rank(args) WITHIN GROUP (ORDER BY sorted_args) | VARIADIC "any" | VARIADIC "any" | bigint | No | 假设行的秩,没有间隙 |
percent_rank(args) WITHIN GROUP (ORDER BY sorted_args) | VARIADIC "any" | VARIADIC "any" | double precision | No | 假设行的相对秩,范围从0到1 |
cume_dist(args) WITHIN GROUP (ORDER BY sorted_args) | VARIADIC "any" | VARIADIC "any" | double precision | No | 假设行的相对秩,从1/N到1不等。 |
Function | Return Type | Description |
GROUPING(args...) | integer | 整数位掩码指示当前组中没有包含哪些参数 |