聚集函数
sum(expression)
描述：所有输入行的expression总和。
返回类型：
通常情况下输入数据类型和输出数据类型是相同的，但以下情况会发生类型转换：
- 对于SMALLINT或INT输入，输出类型为BIGINT。
- 对于BIGINT输入，输出类型为NUMBER 。
- 对于浮点数输入，输出类型为DOUBLE PRECISION。
示例：
1 2 3 4 5 | SELECT SUM(ss_ext_tax) FROM tpcds.STORE_SALES; sum -------------- 213267594.69 (1 row) |
max(expression)
描述：所有输入行中expression的最大值。
参数类型：任意数组、数值、字符串、日期/时间类型。
返回类型：与参数数据类型相同。
示例：
1 2 3 4 5 | SELECT MAX(inv_quantity_on_hand) FROM tpcds.inventory; max --------- 1000000 (1 row) |
min(expression)
描述：所有输入行中expression的最小值。
参数类型：任意数组、数值、字符串、日期/时间类型。
返回类型：与参数数据类型相同。
示例：
1 2 3 4 5 | SELECT MIN(inv_quantity_on_hand) FROM tpcds.inventory; min ----- 0 (1 row) |
avg(expression)
描述：所有输入值的均值（算术平均）。
返回类型：
- 对于任何整数类型输入，结果都是NUMBER类型。
- 对于任何浮点输入，结果都是DOUBLE PRECISION类型。
- 其他，和输入数据类型相同。
示例：
1 2 3 4 5 | SELECT AVG(inv_quantity_on_hand) FROM tpcds.inventory; avg ---------------------- 500.0387129084044604 (1 row) |
count(expression)
描述：返回表中满足expression不为NULL的行数。
返回类型：BIGINT
示例：
1 2 3 4 5 | SELECT COUNT(inv_quantity_on_hand) FROM tpcds.inventory; count ---------- 11158087 (1 row) |
count(*)
描述：返回表中的记录行数。
返回类型：BIGINT
示例：
1 2 3 4 5 | SELECT COUNT(*) FROM tpcds.inventory; count ---------- 11745000 (1 row) |
array_agg(expression)
描述：将所有输入值（包括空）连接成一个数组。函数入参不支持数组形式。
返回类型：参数类型的数组。
示例：
创建表employeeinfo，并插入数据：
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE EXTERNAL TABLE employeeinfo (empno smallint, ename varchar(20), job varchar(20), hiredate date,deptno smallint) store AS orc; INSERT INTO employeeinfo VALUES (7155, 'JACK', 'SALESMAN', '2018-12-01', 30); INSERT INTO employeeinfo VALUES (7003, 'TOM', 'FINANCE', '2016-06-15', 20); INSERT INTO employeeinfo VALUES (7357, 'MAX', 'SALESMAN', '2020-10-01', 30); SELECT * FROM employeeinfo; empno | ename | job | hiredate | deptno -------+-------+----------+---------------------+-------- 7155 | JACK | SALESMAN | 2018-12-01 00:00:00 | 30 7357 | MAX | SALESMAN | 2020-10-01 00:00:00 | 30 7003 | TOM | FINANCE | 2016-06-15 00:00:00 | 20 (3 rows) |
查询部门编号为30的所有员工姓名：
1 2 3 4 5 | SELECT array_agg(ename) FROM employeeinfo where deptno = 30; array_agg ------------ {JACK,MAX} (1 row) |
查询属于同一个部门的所有员工：
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT deptno, array_agg(ename) FROM employeeinfo group by deptno; deptno | array_agg --------+------------ 30 | {JACK,MAX} 20 | {TOM} (2 rows) SELECT distinct array_agg(ename) OVER (PARTITION BY deptno) FROM employeeinfo; array_agg ------------ {TOM} {JACK,MAX} (2 rows) |
查询所有的部门编号且去重：
1 2 3 4 5 6 | SELECT array_agg(distinct deptno) FROM employeeinfo group by deptno; array_agg ----------- {20} {30} (2 rows) |
string_agg(expression, delimiter)
描述：将输入值连接成为一个字符串，用分隔符分开。
返回类型：和参数数据类型相同。
示例：
基于创建的表employeeinfo，查询属于同一个部门的所有员工：
1 2 3 4 5 6 | SELECT deptno, string_agg(ename,',') FROM employeeinfo group by deptno; deptno | string_agg --------+------------ 30 | JACK,MAX 20 | TOM (2 rows) |
查询工号小于7156的员工：
1 2 3 4 5 | SELECT string_agg(ename,',') FROM employeeinfo where empno < 7156; string_agg ------------ TOM,JACK (1 row) |
covar_pop(Y, X)
描述：总体协方差。
返回类型：double precision
示例：
1 2 3 4 5 | 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
示例：
1 2 3 4 5 | 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。
示例：
1 2 3 4 5 | 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。
示例：
1 2 3 4 5 | 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类型。
示例：
1 2 3 4 5 | 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类型。
示例：
1 2 3 4 5 | 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 。
返回类型：和参数数据类型相同。
示例：
1 2 3 4 5 | 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。
返回类型：和参数数据类型相同
示例：
1 2 3 4 5 | SELECT BIT_OR(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1; bit_or -------- 1023 (1 row) |
bool_and(expression)
描述：如果所有输入值都是真，则为真，否则为假。
返回类型：bool
示例：
1 2 3 4 5 | SELECT bool_and(100 <2500); bool_and ---------- t (1 row) |
bool_or(expression)
描述：如果所有输入值只要有一个为真，则为真，否则为假。
返回类型：bool
示例：
1 2 3 4 5 | SELECT bool_or(100 <2500); bool_or ---------- t (1 row) |
corr(Y, X)
描述：相关系数。
返回类型：double precision
示例：
1 2 3 4 5 | SELECT CORR(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000; corr ------------------- 0.0381383624904186 (1 row) |
every(expression)
描述：等效于bool_and。
返回类型：bool
示例：
1 2 3 4 5 | SELECT every(100 <2500); every ------- t (1 row) |
rank(expression)
描述：根据expression对不同组内的元组进行跳跃排序。
返回类型：bigint
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | SELECT d_moy, d_fy_week_seq, rank() OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2; d_moy | d_fy_week_seq | rank -------+---------------+------ 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 2 | 8 1 | 2 | 8 1 | 2 | 8 1 | 2 | 8 1 | 2 | 8 1 | 2 | 8 1 | 2 | 8 1 | 3 | 15 1 | 3 | 15 1 | 3 | 15 1 | 3 | 15 1 | 3 | 15 1 | 3 | 15 1 | 3 | 15 1 | 4 | 22 1 | 4 | 22 1 | 4 | 22 1 | 4 | 22 1 | 4 | 22 1 | 4 | 22 1 | 4 | 22 1 | 5 | 29 1 | 5 | 29 2 | 5 | 1 2 | 5 | 1 2 | 5 | 1 2 | 5 | 1 2 | 5 | 1 2 | 6 | 6 2 | 6 | 6 2 | 6 | 6 2 | 6 | 6 2 | 6 | 6 2 | 6 | 6 2 | 6 | 6 (42 rows) |
regr_avgx(Y, X)
描述：自变量的平均值 (sum(X)/N)。
返回类型：double precision
示例：
1 2 3 4 5 | 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
示例：
1 2 3 4 5 | 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
示例：
1 2 3 4 5 | 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
示例：
1 2 3 4 5 | 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
示例：
1 2 3 4 5 | SELECT REGR_R2(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000; regr_r2 -------------------- 0.00145453469345058 (1 row) |
regr_slope(Y, X)
描述：根据所有输入的点(X, Y)按照最小二乘法拟合成一个线性方程， 然后返回该直线的斜率。
返回类型：double precision
示例：
1 2 3 4 5 | SELECT REGR_SLOPE(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000; regr_slope -------------------- 0.00139920009665259 (1 row) |
regr_sxx(Y, X)
描述：sum(X^2) - sum(X)^2/N （自变量的“平方和”）。
返回类型：double precision
示例：
1 2 3 4 5 | 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
示例：
1 2 3 4 5 | 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
示例：
1 2 3 4 5 | 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。
示例：
1 2 3 4 5 | SELECT STDDEV(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1; stddev ------------------ 289.224359757315 (1 row) |
variance(expression)
描述：var_samp的别名。
返回类型：对于浮点类型的输入返回double precision类型，其他输入返回numeric类型。
示例：
1 2 3 4 5 | SELECT VARIANCE(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1; variance -------------------- 83650.730277028768 (1 row) |
UNIQ(col_name)
描述：计算非重复值个数的聚合函数，与COUNT DISTINCT类似，即计算某一列去重后的行数，结果返回一个去重值。
入参说明：col_name指需要计算去重后行数的列。支持SMALLINT、INTEGER、BIGINT、REAL、DOUBLE PRECISION、TEXT、VARCHAR、TIMESTAMP、TIMESTAMPTZ、DATE、TIMETZ、UUID类型。
- 使用UNIQ函数时，SQL需要包含GROUP BY，为了取得更好性能所选GROUP BY字段分布需要尽量均匀。
- 建议当SQL中需要去重的列大于等于3列时，使用UNIQ函数去重，以取得比COUNT DISTINCT更好的效果。
- 一般情况下UNIQ函数内存消耗低于COUNT DISTINCT，如果使用COUNT DISTINCT时遇到内存超限，可以使用UNIQ函数进行替换。
示例：
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE EXTERNAL TABLE employeeinfo (empno smallint, ename varchar(20), job varchar(20), hiredate date,deptno smallint) store AS orc; INSERT INTO employeeinfo VALUES (7155, 'JACK', 'SALESMAN', '2018-12-01', 30); INSERT INTO employeeinfo VALUES (7003, 'TOM', 'FINANCE', '2016-06-15', 20); INSERT INTO employeeinfo VALUES (7357, 'MAX', 'SALESMAN', '2020-10-01', 30); SELECT UNIQ(deptno) FROM employeeinfo GROUP BY ename; uniq ------ 1 1 1 (3 rows) |
