聚集函数
聚集函数
sum(expression)
描述：所有输入行的expression总和。
返回类型：
通常情况下输入数据类型和输出数据类型是相同的，但以下情况会发生类型转换：
- 对于SMALLINT或INT输入，输出类型为BIGINT。
- 对于BIGINT输入，输出类型为NUMBER 。
- 对于浮点数输入，输出类型为DOUBLE PRECISION。
示例：
1、创建测试表并插入数据。
CREATE TABLE tab(a int); Insert into tab values(1); Insert into tab values(1);2、执行查询语句。
SELECT SUM(a) FROM tab;结果显示为：
sum -------------- 3 (1 row)max(expression)
描述：所有输入行中expression的最大值。
参数类型：任意数组、数值、字符串、日期/时间类型。
返回类型：与参数数据类型相同
示例：
执行查询语句：
SELECT MAX(inv_quantity_on_hand) FROM tpcds.inventory;min(expression)
描述：所有输入行中expression的最小值。
参数类型：任意数组、数值、字符串、日期/时间类型。
返回类型：与参数数据类型相同
示例：
执行查询语句：
SELECT MIN(inv_quantity_on_hand) FROM tpcds.inventory;结果显示为：
min ----- 0 (1 row)-
描述：所有输入值的均值（算术平均）。
返回类型：
对于任何整数类型输入，结果都是NUMBER类型。
对于任何浮点输入，结果都是DOUBLE PRECISION类型。
否则和输入数据类型相同。
示例：
执行查询语句：
SELECT AVG(inv_quantity_on_hand) FROM tpcds.inventory;结果显示为：
avg ----------------------- 500.0387129084044604 (1 row) -
描述：返回表中满足expression不为NULL的行数。
返回类型：BIGINT
示例：
执行查询语句：
SELECT COUNT(inv_quantity_on_hand) FROM tpcds.inventory;结果显示为：
count ----------- 11158087 (1 row) count(*)
描述：返回表中的记录行数。
返回类型：BIGINT
示例：
SELECT COUNT(*) FROM tpcds.inventory;结果显示为：
count ----------- 11745000 (1 row)median(expression) [over (query partition clause)]
描述：返回表达式的中位数，计算时NULL将会被median函数忽略。可以使用distinct关键字排除表达式中的重复记录。输入expression的数据类型可以是数值类型（包括integer、 double、bigint等），也可以是interval类型。其他数据类型不支持求取中位数。
返回类型：double或interval类型
示例：
执行查询语句：
SELECT MEDIAN(id) FROM (values(1), (2), (3), (4), (null)) test(id);结果显示为：
median --------- 2.5 (1 row)array_agg(expression)
描述：将所有输入值（包括空）连接成一个数组。
返回类型：参数类型的数组。
示例：
SELECT ARRAY_AGG(sr_fee) FROM tpcds.store_returns WHERE sr_customer_sk = 2;结果显示为：
array_agg ---------------- {22.18,63.21} (1 row)string_agg(expression, delimiter)
描述：将输入值连接成为一个字符串，用分隔符分开。
返回类型：和参数数据类型相同。
示例：
执行查询语句：
SELECT string_agg(sr_item_sk, ',') FROM tpcds.store_returns where sr_item_sk < 3;结果显示为：
string_agg ---------------------------------------------------------------------------------- ------------------------------- 1,2,1,2,2,1,1,2,2,1,2,1,2,1,1,1,2,1,1,1,1,1,2,1,1,1,1,1,2,2,1,1,1,1,1,1,1,1,1,2, 2,1,1,1,1,1,1,2,2,1,1,2,1,1,1 (1 row)listagg(expression [, delimiter]) WITHIN GROUP(ORDER BY order-list)
描述：将聚集列数据按WITHIN GROUP指定的排序方式排列，并用delimiter指定的分隔符拼接成一个字符串。
- expression：必选。指定聚集列名或基于列的有效表达式，不支持DISTINCT关键字和VARIADIC参数。
- delimiter：可选。指定分隔符，可以是字符串常数或基于分组列的确定性表达式，缺省时表示分隔符为空。
- order-list：必选。指定分组内的排序方式。
返回类型：text
示例：
示例1：聚集列是文本字符集类型。
执行查询语句：
SELECT deptno, listagg(ename, ',') WITHIN GROUP(ORDER BY ename) AS employees FROM emp GROUP BY deptno;结果显示为：
deptno | employees ---------+-------------------------------------- 10 | CLARK,KING,MILLER 20 | ADAMS,FORD,JONES,SCOTT,SMITH 30 | ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD (3 rows)示例2：聚集列是整型。
执行查询语句：
SELECT deptno, listagg(mgrno, ',') WITHIN GROUP(ORDER BY mgrno NULLS FIRST) AS mgrnos FROM emp GROUP BY deptno;结果显示为：
deptno | mgrnos ---------+------------------------------- 10 | 7782,7839 20 | 7566,7566,7788,7839,7902 30 | 7698,7698,7698,7698,7698,7839 (3 rows)示例3：聚集列是浮点类型。
执行查询语句：
SELECT job, listagg(bonus, '($); ') WITHIN GROUP(ORDER BY bonus DESC) || '($)' AS bonus FROM emp GROUP BY job;结果显示为：
job | bonus -------------+------------------------------------------------- CLERK | 10234.21($); 2000.80($); 1100.00($); 1000.22($) PRESIDENT | 23011.88($) ANALYST | 2002.12($); 1001.01($) MANAGER | 10000.01($); 2399.50($); 999.10($) SALESMAN | 1000.01($); 899.00($); 99.99($); 9.00($) (5 rows)示例4：聚集列是时间类型。
执行查询语句：
SELECT deptno, listagg(hiredate, ', ') WITHIN GROUP(ORDER BY hiredate DESC) AS hiredates FROM emp GROUP BY deptno;结果显示为：
deptno | hiredates ---------+------------------------------------------------------------------------------------------------------------------------------ 10 | 1982-01-23 00:00:00, 1981-11-17 00:00:00, 1981-06-09 00:00:00 20 | 2001-04-02 00:00:00, 1999-12-17 00:00:00, 1987-05-23 00:00:00, 1987-04-19 00:00:00, 1981-12-03 00:00:00 30 | 2015-02-20 00:00:00, 2010-02-22 00:00:00, 1997-09-28 00:00:00, 1981-12-03 00:00:00, 1981-09-08 00:00:00, 1981-05-01 00:00:00 (3 rows)示例5：聚集列是时间间隔类型。
执行查询语句：
SELECT deptno, listagg(vacationTime, '; ') WITHIN GROUP(ORDER BY vacationTime DESC) AS vacationTime FROM emp GROUP BY deptno;结果显示为：
deptno | vacationtime ---------+------------------------------------------------------------------------------------ 10 | 1 year 30 days; 40 days; 10 days 20 | 70 days; 36 days; 9 days; 5 days 30 | 1 year 1 mon; 2 mons 10 days; 30 days; 12 days 12:00:00; 4 days 06:00:00; 24:00:00 (3 rows)示例6：分隔符缺省时，默认为空。
执行查询语句：
SELECT deptno, listagg(job) WITHIN GROUP(ORDER BY job) AS jobs FROM emp GROUP BY deptno; deptno | jobs ---------+---------------------------------------------- 10 | CLERKMANAGERPRESIDENT 20 | ANALYSTANALYSTCLERKCLERKMANAGER 30 | CLERKMANAGERSALESMANSALESMANSALESMANSALESMAN (3 rows)示例7：listagg作为窗口函数时，OVER子句不支持ORDER BY的窗口排序，listagg列为对应分组的有序聚集。
执行查询语句：
SELECT deptno, mgrno, bonus, listagg(ename,'; ') WITHIN GROUP(ORDER BY hiredate) OVER(PARTITION BY deptno) AS employees FROM emp;结果显示为：
deptno | mgrno | bonus | employees ---------+-------+----------+------------------------------------------- 10 | 7839 | 10000.01 | CLARK; KING; MILLER 10 | | 23011.88 | CLARK; KING; MILLER 10 | 7782 | 10234.21 | CLARK; KING; MILLER 20 | 7566 | 2002.12 | FORD; SCOTT; ADAMS; SMITH; JONES 20 | 7566 | 1001.01 | FORD; SCOTT; ADAMS; SMITH; JONES 20 | 7788 | 1100.00 | FORD; SCOTT; ADAMS; SMITH; JONES 20 | 7902 | 2000.80 | FORD; SCOTT; ADAMS; SMITH; JONES 20 | 7839 | 999.10 | FORD; SCOTT; ADAMS; SMITH; JONES 30 | 7839 | 2399.50 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN 30 | 7698 | 9.00 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN 30 | 7698 | 1000.22 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN 30 | 7698 | 99.99 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN 30 | 7698 | 1000.01 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN 30 | 7698 | 899.00 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN (14 rows)covar_pop(Y, X)
描述：总体协方差。
返回类型：double precision
示例：
执行查询语句：
SELECT COVAR_POP(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;结果显示为：
covar_pop ------------------- 829.749627587403 (1 row)covar_samp(Y, X)
描述：样本协方差。
返回类型：double precision
示例：
执行查询语句：
SELECT COVAR_SAMP(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;结果显示为：
covar_samp ------------------- 830.052235037289 (1 row)stddev_pop(expression)
描述：总体标准差。
返回类型：对于浮点类型的输入返回double precision，其他输入返回numeric。
示例：
支持查询语句：
SELECT STDDEV_POP(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;结果显示为：
stddev_pop ------------------- 289.224294957556 (1 row)stddev_samp(expression)
描述：样本标准差。
返回类型：对于浮点类型的输入返回double precision，其他输入返回numeric。
示例：
支持查询语句：
SELECT STDDEV_SAMP(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;结果显示为：
stddev_samp ------------------- 289.224359757315 (1 row)var_pop(expression)
描述：总体方差（总体标准差的平方）
返回类型：对于浮点类型的输入返回double precision类型，其他输入返回numeric类型。
示例：
执行查询语句：
SELECT VAR_POP(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;结果显示为：
var_pop --------------------- 83650.692793695475 (1 row)var_samp(expression)
描述：样本方差（样本标准差的平方）
返回类型：对于浮点类型的输入返回double precision类型，其他输入返回numeric类型。
示例：
执行查询语句：
SELECT VAR_SAMP(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;结果显示为：
var_samp --------------------- 83650.730277028768 (1 row)bit_and(expression)
描述：所有非NULL输入值的按位与(AND)，如果全部输入值皆为NULL，那么结果也为NULL 。
返回类型：和参数数据类型相同。
示例：
执行查询语句：
SELECT BIT_AND(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;结果显示为：
bit_and ---------- 0 (1 row)bit_or(expression)
描述：所有非NULL输入值的按位或(OR)，如果全部输入值皆为NULL，那么结果也为NULL。
返回类型：和参数数据类型相同
示例：
执行查询语句：
SELECT BIT_OR(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;结果显示为：
bit_or --------- 1023 (1 row)bool_and(expression)
描述：如果所有输入值都是真，则为真，否则为假。
返回类型：bool
示例：
执行查询语句：
SELECT bool_and(100 <2500);结果显示为：
bool_and ----------- t (1 row)bool_or(expression)
描述：如果所有输入值只要有一个为真，则为真，否则为假。
返回类型：bool
示例：
执行查询语句：
SELECT bool_or(100 <2500);结果显示为：
bool_or ----------- t (1 row)corr(Y, X)
描述：相关系数
返回类型：double precision
示例：
执行查询语句：
SELECT CORR(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;结果显示为：
corr -------------------- .0381383624904186 (1 row)every(expression)
描述：等效于bool_and。
返回类型：bool
示例：
执行查询语句：
SELECT every(100 <2500);结果显示为：
every -------- t (1 row)regr_avgx(Y, X)
描述：自变量的平均值 (sum(X)/N)
返回类型：double precision
示例：
执行查询语句：
SELECT REGR_AVGX(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;结果显示为：
regr_avgx ------------------- 578.606576740795 (1 row)regr_avgy(Y, X)
描述：因变量的平均值 (sum(Y)/N)
返回类型：double precision
示例：
执行查询语句：
SELECT REGR_AVGY(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;结果显示为：
regr_avgy ------------------- 50.0136711629602 (1 row)regr_count(Y, X)
描述：两个表达式都不为NULL的输入行数。
返回类型：bigint
示例：
执行查询语句：
SELECT REGR_COUNT(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;结果显示为：
regr_count ------------- 2743 (1 row)regr_intercept(Y, X)
描述：根据所有输入的点(X, Y)按照最小二乘法拟合成一个线性方程，然后返回该直线的Y轴截距。
返回类型：double precision
示例：
执行查询语句：
SELECT REGR_INTERCEPT(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;结果显示为：
regr_intercept ------------------- 49.2040847848607 (1 row)regr_r2(Y, X)
描述：相关系数的平方。
返回类型：double precision
示例：
执行查询语句：
SELECT REGR_R2(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;结果显示为：
regr_r2 --------------------- .00145453469345058 (1 row)regr_slope(Y, X)
描述：根据所有输入的点(X, Y)按照最小二乘法拟合成一个线性方程， 然后返回该直线的斜率。
返回类型：double precision
示例：
执行查询语句：
SELECT REGR_SLOPE(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;结果显示为：
regr_slope --------------------- .00139920009665259 (1 row)regr_sxx(Y, X)
描述：sum(X^2) - sum(X)^2/N （自变量的“平方和”）。
返回类型：double precision
示例：
执行查询语句：
SELECT REGR_SXX(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;结果显示为：
regr_sxx ------------------- 1626645991.46135 (1 row)regr_sxy(Y, X)
描述：sum(X*Y) - sum(X) * sum(Y)/N （自变量和因变量的“乘方积”）。
返回类型：double precision
示例：
执行查询语句：
SELECT REGR_SXY(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;结果显示为：
regr_sxy ------------------- 2276003.22847225 (1 row)regr_syy(Y, X)
描述：sum(Y^2) - sum(Y)^2/N（因变量的”平方和”）。
返回类型：double precision
示例：
执行查询语句：
SELECT REGR_SYY(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;结果显示为：
regr_syy ------------------ 2189417.6547314 (1 row)stddev(expression)
描述：stddev_samp的别名。
返回类型：对于浮点类型的输入返回double precision，其他输入返回numeric。
示例：
执行查询语句：
SELECT STDDEV(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;结果显示为：
stddev ------------------- 289.224359757315 (1 row)variance(expexpression,ression)
描述：var_samp的别名。
返回类型：对于浮点类型的输入返回double precision类型，其他输入返回numeric类型。
示例：
执行查询语句：
SELECT VARIANCE(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;结果显示为：
variance --------------------- 83650.730277028768 (1 row)delta
描述：返回当前行和前一行的差值。
参数：numeric
返回值类型：numeric
**checksum(expression)
描述：返回所有输入值的CHECKSUM值。使用该函数可以用来验证Vastbase数据库（不支持Vastbase之外的其他数据库）的备份恢复或者数据迁移操作前后表中的数据是否相同。在备份恢复或者数据迁移操作前后都需要用户通过手工执行SQL命令的方式获取执行结果，通过对比获取的执行结果判断操作前后表中的数据是否相同。
- 对于大表，CHECKSUM函数可能会需要很长时间。
- 如果某两表的CHECKSUM值不同，则表明两表的内容是不同的。由于CHECKSUM函数中使用散列函数不能保证无冲突，因此两个不同内容的表可能会得到相同的CHECKSUM值，存在这种情况的可能性较小。对于列进行的CHECKSUM也存在相同的情况。
- 对于时间类型timestamp, timestamptz和smalldatetime，计算CHECKSUM值时请确保时区设置一致。
- 若计算某列的CHECKSUM值，且该列类型可以默认转为TEXT类型，则expression为列名。
- 若计算某列的CHECKSUM值，且该列类型不能默认转为TEXT类型，则expression为列名::TEXT。
- 若计算所有列的CHECKSUM值，则expression为表名::TEXT。
可以默认转换为TEXT类型的类型包括：char、name、 int8、 int2、 int1、 int4、 raw、 pg_node_tree、 float4、 float8、 bpchar、 varchar、 nvarchar、 nvarchar2、 date、 timestamp、 timestamptz、 numeric、 smalldatetime，其他类型需要强制转换为TEXT。
返回类型：numeric。
示例：
表中可以默认转为TEXT类型的某列的CHECKSUM值。
执行查询语句：
SELECT CHECKSUM(inv_quantity_on_hand) FROM tpcds.inventory;结果显示为：
checksum ------------------- 24417258945265247 (1 row)表中不能默认转为TEXT类型的某列的CHECKSUM值。注意此时CHECKSUM参数是列名::TEXT。
执行查询语句：
SELECT CHECKSUM(inv_quantity_on_hand::TEXT) FROM tpcds.inventory;结果显示为：
checksum ------------------- 24417258945265247 (1 row)表中所有列的CHECKSUM值。注意此时CHECKSUM参数是表名::TEXT，且表名前不加Schema。
执行查询语句：
SELECT CHECKSUM(inventory::TEXT) FROM tpcds.inventory;结果显示为：
checksum ------------------ 25223696246875800 (1 row)first(anyelement)
描述：返回第一个非NULL输入。
返回类型：anyelement
执行查询语句：
select * from tba; select first(name) from tba;结果显示为：
name ------ A A D (4 rows) first ------ A (1 rows)last(anyelement)
描述：返回最后一个非NULL输入。
返回类型：anyelement
示例：
执行查询语句：
select * from tba; select last(name) from tba;结果显示为：
name ------ A A D (4 rows) last ------ D (1 rows)mode() within group (order by value anyelement)
描述：返回某列中出现频率最高的值，如果多个值频率相同，则返回最小的那个值。排序方式和该列类型的默认排序方式相同。其中value为输入参数，可以为任意类型。
返回类型：与输入参数类型相同。
示例：
执行查询语句：
select mode() within group (order by value) from (values(1, 'a'), (2, 'b'), (2, 'c')) v(value, tag); select mode() within group (order by tag) from (values(1, 'a'), (2, 'b'), (2, 'c')) v(value, tag);查询结果显示为：
mode ------- 2 (1 row) mode ------- a (1 row)json_agg(any)
描述：将值聚集为json数组。
返回类型：array-json
示例：
1、创建测试表并插入数据。
create table t2(c1 int,c2 int); insert into t2 values(1,1),(2,2),(3,3),(4,4),(4,4),(5,5);2、执行查询语句：
select * from t2; select c1, json_agg(c2) c2 from t2 group by c1;结果依次显示为：
c1 | c2 ----+---- 1 | 1 2 | 2 3 | 3 4 | 4 4 | 4 5 | 5 (6 rows) c1 | c2 ----+-------- 1 | [1] 4 | [4, 4] 3 | [3] 5 | [5] 2 | [2] (5 rows)json_object_agg(any, any)
描述：将值聚集为json对象。
返回类型：object-json
示例：
执行查询语句：
select * from t2; select json_object_agg(c1, c2) from t1 group by c1 order by c1;查询结果依次为：
c1 | c2 -----+---- 1 | 1 2 | 2 3 | 3 4 | 4 4 | 4 5 | 5 (6 rows) json_object_agg ------------------ { "1" : 1 } { "2" : 2 } { "3" : 3 } (3 rows)
