# 聚集函数 聚集函数是数据库中常用的函数，它将多行数据合并计算为单个统计值，帮助用户快速从大量数据中获取关键数据概览，如总数、平均数、最大值和去重计数等，广泛应用于数据报表和业务分析等场景。 表1聚集函数 | 函数分类 | 函数名称 | 功能 | |:---|:---|:---| | 基础统计计算 | [sum(expression)] | 计算表达式的总和。 | | 基础统计计算 | [max(expression)] | 返回表达式的最大值。 | | 基础统计计算 | [min(expression)] | 返回表达式的最小值。 | | 基础统计计算 | [avg(expression)] | 计算表达式的平均值。 | | 基础统计计算 | [count(expression)] | 返回表中非空（NOT NULL）的行数。 | | 基础统计计算 | [count(\*)] | 返回表中的总行数（包括含NULL值的行）。 | | 分位数与分布分析 | [median(expression)] | 计算中位数。 | | 分位数与分布分析 | [percentile_cont(const) within group(order by expression)] | 连续分布百分位数。通过线性插值计算指定百分位的值。 | | 分位数与分布分析 | [percentile_disc(const) within group(order by expression)] | 离散百分位数。返回数据集中最接近指定百分位的实际值。 | | 聚集与字符串拼接 | [array_agg(expression)] | 将多行值聚合到一个数组中。 | | 聚集与字符串拼接 | [string_agg(expression, delimiter)] | 将多行字符串用指定分隔符连接成一个字符串。 | | 聚集与字符串拼接 | [listagg(expression \[, delimiter\]) WITHIN GROUP(ORDER BY order-list)] | 将多行字符串按指定顺序连接成一个字符串。 | | 聚集与字符串拼接 | [group_concat(expression \[ORDER BY {col_name \| expr} \[ASC \| DESC\]\] \[SEPARATOR str_val\])] | 将分组中的多行字符串值连接成一个字符串。 | | 统计学高级分析 | [var_pop(expression)] | 计算总体方差。 | | 统计学高级分析 | [var_samp(expression)] | 计算样本方差。 | | 统计学高级分析 | [variance(expression)] | 计算样本方差。 | | 统计学高级分析 | [stddev_pop(expression)] | 计算总体标准差。 | | 统计学高级分析 | [stddev_samp(expression)] | 计算样本标准差。 | | 统计学高级分析 | [stddev(expression)] | 计算样本标准差。 | | 统计学高级分析 | [covar_pop(Y, X)] | 计算Y和X的总体协方差。 | | 统计学高级分析 | [covar_samp(Y, X)] | 计算Y和X的样本协方差。 | | 统计学高级分析 | [corr(Y, X)] | 计算Y和X的相关系数。 | | 线性回归分析 | [regr_avgx(Y, X)] | 计算X的平均值。 | | 线性回归分析 | [regr_avgy(Y, X)] | 计算Y的平均值。 | | 线性回归分析 | [regr_count(Y, X)] | 计算非空对 (X, Y) 的数量。 | | 线性回归分析 | [regr_r2(Y, X)] | 计算相关系数的平方。 | | 线性回归分析 | [regr_slope(Y, X)] | 根据输入点(X, Y)拟合成一个线性方程， 然后计算该直线的斜率。 | | 线性回归分析 | [regr_intercept(Y, X)] | 根据输入点(X, Y)拟合成一个线性方程，然后计算该直线的Y轴截距。 | | 线性回归分析 | [regr_sxx(Y, X)] | 计算X的平方和。 | | 线性回归分析 | [regr_syy(Y, X)] | 计算Y的平方和。 | | 线性回归分析 | [regr_sxy(Y, X)] | 计算X和Y的乘积和。 | | 逻辑与位运算聚合 | [bit_and(expression)] | 对所有行的值进行按位与（AND）运算。 | | 逻辑与位运算聚合 | [bit_or(expression)] | 对所有行的值进行按位或（OR）运算。 | | 逻辑与位运算聚合 | [bool_and(expression)] | 如果所有输入值都是真，则返回真（TRUE）。 | | 逻辑与位运算聚合 | [bool_or(expression)] | 如果所有输入值只要有一个为真，则返回真（TRUE） | | 逻辑与位运算聚合 | [every(expression)] | 等效于bool_and()，如果所有输入值都是真，则返回真（TRUE）。 | | 去重与校验 | [checksum(expression)] | 计算数据的校验和，通过对比结果判断操作前后表中的数据是否一致。 | | 去重与校验 | [approx_count_distinct(col_name)] | 近似计算唯一值的数量 | | 去重与校验 | [UNIQ(col_name)] | 计算唯一值的数量。 | #### 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(\*) 描述：返回表中的总行数（包括含NULL值的行）。 返回类型：BIGINT 示例： ``` SELECT COUNT(*) FROM tpcds.inventory; count ---------- 11745000 (1 row) ``` #### array_agg(expression) 描述：将所有输入值（包括空）连接成一个数组。函数入参不支持数组类型。 返回类型：参数类型的数组。 示例： 创建表employeeinfo，并插入数据： ``` CREATE TABLE employeeinfo (empno smallint, ename varchar(20), job varchar(20), hiredate date,deptno smallint); 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的所有员工姓名： ``` SELECT array_agg(ename) FROM employeeinfo where deptno = 30; array_agg ------------ {JACK,MAX} (1 row) ``` 查询属于同一个部门的所有员工： ``` 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) ``` 查询所有的部门编号且去重： ``` SELECT array_agg(distinct deptno) FROM employeeinfo group by deptno; array_agg ----------- {20} {30} (2 rows) ``` 查询所有的部门编号去重后按降序排列： ``` SELECT array_agg(distinct deptno order by deptno desc) FROM employeeinfo; array_agg ----------- {30,20} (1 row) ``` #### string_agg(expression, delimiter) 描述：将输入值用指定的分隔符连接成一个字符串。 返回类型：和参数数据类型相同。 示例： 基于创建的表employeeinfo，查询属于同一个部门的所有员工： ``` SELECT deptno, string_agg(ename,',') FROM employeeinfo group by deptno; deptno | string_agg --------+------------ 30 | JACK,MAX 20 | TOM (2 rows) ``` 查询工号小于7156的员工： ``` SELECT string_agg(ename,',') FROM employeeinfo where empno < 7156; string_agg ------------ TOM,JACK (1 row) ``` #### listagg(expression \[, delimiter\]) WITHIN GROUP(ORDER BY order-list) 描述：将聚集列数据按WITHIN GROUP指定的排序方式排列，并用delimiter指定的分隔符拼接成一个字符串。 参数说明： - expression：必选。指定聚集列名或基于列的有效表达式，不支持DISTINCT关键字和VARIADIC参数。 - delimiter：可选。指定分隔符，可以是字符串常数或基于分组列的确定性表达式，缺省时表示分隔符为空。 - order-list：必选。指定分组内的排序方式。 返回类型：text ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/note_3.0-zh-cn.png) 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 --------+------------------------------------------------------------------------------------ 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) ``` 分隔符缺省时，默认为空： ``` 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 --------+-------+----------+------------------------------------------- 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) ``` #### group_concat(expression \[ORDER BY {col_name \| expr} \[ASC \| DESC\]\] \[SEPARATOR str_val\]) 描述：将列数据使用指定的str_val分隔符，按照ORDER BY子句指定的排序方式拼接成字符串，ORDER BY子句必须指定排序方式，不支持"ORDER BY 1"的写法。 参数说明： - expression：必选。指定列名或基于列的有效表达式，不支持DISTINCT关键字和VARIADIC参数。 - str_val：可选。指定的分隔符，可以是字符串常数或基于分组列的确定性表达式。缺省时表示分隔符为逗号。 返回类型：text 示例： 默认分隔符为逗号： ``` SELECT group_concat(sname) FROM group_concat_test; group_concat ------------------------------------------ ADAMS,FORD,JONES,KING,MILLER,SCOTT,SMITH (1 row) ``` group_concat函数支持自定义分隔符： ``` SELECT group_concat(sname separator ';') from group_concat_test; group_concat ------------------------------------------ ADAMS;FORD;JONES;KING;MILLER;SCOTT;SMITH (1 row) ``` group_concat函数支持ORDER BY子句，将列数据进行有序拼接： ``` SELECT group_concat(sname order by snumber separator ';') FROM group_concat_test; group_concat ------------------------------------------ MILLER;FORD;SCOTT;SMITH;KING;JONES;ADAMS (1 row) ``` #### 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) 描述：如果所有输入值都是真，则返回真（TRUE），否则返回假（FALSE）。 返回类型：bool 示例： ``` SELECT bool_and(100 <2500); bool_and ---------- t (1 row) ``` #### bool_or(expression) 描述：如果所有输入值只要有一个为真，则返回真（TRUE），否则返回假（FALSE）。 返回类型：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 ------------------- 0.0381383624904186 (1 row) ``` #### every(expression) 描述：等效于[bool_and(expression)]。如果所有输入值都是真，则返回真（TRUE），否则返回假（FALSE）。 返回类型：bool 示例： ``` SELECT every(100 <2500); every ------- t (1 row) ``` #### regr_avgx(Y, X) 描述：计算自变量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) 描述：计算因变量Y的平均值 (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 -------------------- 0.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 -------------------- 0.00139920009665259 (1 row) ``` #### regr_sxx(Y, X) 描述：计算辅助统计量sum(X\^2) - sum(X)\^2/N（自变量X的平方和） 。 返回类型：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（自变量X和因变量Y的乘方积）。 返回类型：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（因变量Y的平方和）。 返回类型：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(expression)]的别名，计算样本标准差。 返回类型：对于浮点类型的输入返回double precision，其他输入返回numeric。 示例： ``` SELECT STDDEV(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1; stddev ------------------ 289.224359757315 (1 row) ``` #### variance(expression) 描述：[var_samp(expression)]的别名，计算样本方差。 返回类型：对于浮点类型的输入返回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值。使用该函数可以用来验证DWS数据库（不支持DWS之外的其他数据库）的备份恢复或者数据迁移操作前后表中的数据是否一致。在备份恢复或者数据迁移操作前后，都需要用户手动执行SQL命令获取结果，通过对比结果判断操作前后表中的数据是否一致。 ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/note_3.0-zh-cn.png) - 对于数据量大的表，执行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) ``` #### approx_count_distinct(col_name) 描述：使用HyperLogLog++ (HLL++) 算法估算某一列去重后的行数（即基数，Cardinality）。该函数仅8.3.0及以上集群版本支持。 参数说明：col_name，需要估算基数的列名。 ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/note_3.0-zh-cn.png) 可通过GUC参数approx_count_distinct_precision调整误差率。 - 参数取值范围为\[10,20\]，默认值为17，理论误差率为千分之三。 - 该参数表示HyperLogLog++ (HLL++)算法中分桶个数，参数值越大，分桶数越多，理论误差率则越小。 - 该参数值越大，相应的计算时间和内存资源开销越大，但仍远小于精确的count distinct语句对应的开销。推荐在估算基数较大时使用该函数替换count distinct。 示例： ``` CREATE TABLE employeeinfo (empno smallint, ename varchar(20), job varchar(20), hiredate date,deptno smallint) WITH (ORIENTATION = COLUMN); 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 APPROX_COUNT_DISTINCT(empno) from employeeinfo; approx_count_distinct ----------------------- 3 (1 row) SELECT COUNT(DISTINCT empno) FROM employeeinfo GROUP BY ename; count ------- 1 1 1 (3 rows) ``` #### UNIQ(col_name) 描述：计算某一列去重后的行数，结果返回一个去重值。与COUNT DISTINCT功能类似。该函数仅8.3.0及以上集群版本支持。 参数说明：col_name，表示需要计算去重后行数的列。支持SMALLINT、INTEGER、BIGINT、REAL、DOUBLE PRECISION、TEXT、VARCHAR、TIMESTAMP、TIMESTAMPTZ、DATE、TIMETZ、UUID类型。 ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/note_3.0-zh-cn.png) - DWS目前仅列存表支持UNIQ()函数。 - 使用UNIQ()函数时，SQL需要包含GROUP BY，为了取得更好性能所选GROUP BY字段分布需要尽量均匀。 - 建议当SQL中需要去重的列大于等于3列时，使用UNIQ()函数去重，以取得比COUNT DISTINCT更好的效果。 - 一般情况下UNIQ()函数内存消耗低于COUNT DISTINCT，如果使用COUNT DISTINCT时遇到内存超限，可以使用UNIQ()函数进行替换。 示例： ``` CREATE TABLE employeeinfo (empno smallint, ename varchar(20), job varchar(20), hiredate date,deptno smallint) WITH (ORIENTATION = COLUMN); 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) ```