范围函数
6.19 聚集函数
t(1 row)
● upper_inc(anyrange) 描述：是否包含上界 返回类型：boolean 示例：
SELECT upper_inc(numrange(1.1,2.2)) AS RESULT;
result f(1 row)
● lower_inf(anyrange) 描述：下界是否为无穷 返回类型：boolean 示例：
SELECT lower_inf('(,)'::daterange) AS RESULT;
result t(1 row)
● upper_inf(anyrange) 描述：上界是否为无穷 返回类型：boolean 示例：
SELECT upper_inf('(,)'::daterange) AS RESULT;
result t(1 row)
如果范围是空或者需要的界限是无穷的，lower和upper函数将返回null。lower_inc、
upper_inc、lower_inf和upper_inf函数均对空范围返回false。
6.19 聚集函数
聚集函数
● sum(expression)
描述：所有输入行的expression总和。
返回类型：
通常情况下输入数据类型和输出数据类型是相同的，但以下情况会发生类型转 换：
– 对于SMALLINT或INT输入，输出类型为BIGINT。
– 对于BIGINT输入，输出类型为NUMBER 。
– 对于浮点数输入，输出类型为DOUBLE PRECISION。
示例：
SELECT SUM(ss_ext_tax) FROM tpcds.STORE_SALES;
sum
213267594.69 (1 row)
● max(expression)
描述：所有输入行中expression的最大值。
参数类型：任意数组、数值、字符串、日期/时间类型。
返回类型：与参数数据类型相同。
示例：
SELECT MAX(inv_quantity_on_hand) FROM tpcds.inventory;
max 1000000 (1 row)
● min(expression)
描述：所有输入行中expression的最小值；
参数类型：任意数组、数值、字符串、日期/时间类型。
返回类型：与参数数据类型相同。
示例：
SELECT MIN(inv_quantity_on_hand) FROM tpcds.inventory;
min 0(1 row)
● avg(expression)
描述：所有输入值的均值（算术平均）。
返回类型：
对于任何整数类型输入，结果都是NUMBER类型。
对于任何浮点输入，结果都是DOUBLE PRECISION类型。
否则和输入数据类型相同。
示例：
SELECT AVG(inv_quantity_on_hand) FROM tpcds.inventory;
avg 500.0387129084044604 (1 row)
● median(expression)
描述：所有输入值的中位数值。当前只支持数值类型和interval类型。其中空值不 参与计算。
返回类型：对于任何整型数据输入，结果都是NUMERIC类型。否则与输入数据类 型相同。
Teradata兼容模式下，如果输入为整型，则返回的数据精度只有整数位。
示例：
SELECT MEDIAN(inv_quantity_on_hand) FROM tpcds.inventory;
median 500 (1 row)
● percentile_cont(const) within group(order by expression)
描述：返回一个对应于目标列排序中指定分位数的值，如有必要就在相邻的输入 项之间插入值。其中空值不参与计算。
输入：const为在0-1之间的数值，expression当前只支持数值类型和interval类 型。
返回类型：对于任何整型数据输入，结果都是NUMERIC类型。否则与输入数据类 型相同。
Teradata兼容模式下，如果输入为整型，则返回的数据精度只有整数位。
示例：
select percentile_cont(0.3) within group(order by x) from (select generate_series(1,5) as x) as t;
percentile_cont ---2.2(1 row)
select percentile_cont(0.3) within group(order by x desc) from (select generate_series(1,5) as x) as t;
percentile_cont ---3.8(1 row)
● percentile_disc(const) within group(order by expression)
描述：返回第一个在排序中位置等于或者超过指定分数的输入值。
输入：const为在0-1之间的数值，expression当前只支持数值类型和interval类 型。其中空值不参与计算。
返回类型：对于任何整型数据输入，结果都是NUMERIC类型。否则与输入数据类 型相同。
示例：
select percentile_disc(0.3) within group(order by x) from (select generate_series(1,5) as x) as t;
percentile_disc ---2(1 row)
select percentile_disc(0.3) within group(order by x desc) from (select generate_series(1,5) as x) as t;
percentile_disc ---4(1 row)
● count(expression)
描述：返回表中满足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)
● 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 说明
listagg是兼容Oracle 11g2的列转行聚集函数，可以指定OVER子句用作窗口函数。为了避 免与函数本身WITHIN GROUP子句的ORDER BY造成二义性，listagg用作窗口函数时，
OVER子句不支持ORDER BY的窗口排序或窗口框架。
示例：
聚集列是文本字符集类型。
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)
聚集列是整型。
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)
聚集列是浮点类型。
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)
聚集列是时间类型。
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)
聚集列是时间间隔类型。
SELECT deptno, listagg(vacationTime, '; ') WITHIN GROUP(ORDER BY vacationTime DESC) AS vacationTime FROM emp GROUP BY deptno;
deptno | vacationtime
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)
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
● 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
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
● 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)
● checksum(expression)
描述：返回所有输入值的CHECKSUM值。使用该函数可以用来验证
GaussDB(DWS)数据库（不支持GaussDB(DWS)之外的其他数据库）的备份恢复 或者数据迁移操作前后表中的数据是否相同。在备份恢复或者数据迁移操作前后
都需要用户通过手工执行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, 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)