聚集函数

聚集函数

  • sum(expression)

    描述:所有输入行的expression总和。

    返回类型:

    通常情况下输入数据类型和输出数据类型是相同的,但以下情况会发生类型转换:

    • 对于SMALLINT或INT输入,输出类型为BIGINT。
    • 对于BIGINT输入,输出类型为NUMBER 。
    • 对于浮点数输入,输出类型为DOUBLE PRECISION。

    示例:

    1. openGauss=# SELECT SUM(ss_ext_tax) FROM tpcds.STORE_SALES;
    2. sum
    3. --------------
    4. 213267594.69
    5. (1 row)
  • max(expression)

    描述:所有输入行中expression的最大值。

    参数类型:任意数组、数值、字符串、日期/时间类型、IPV4和IPV6地址(INET型和CIDR型)。

    返回类型:与参数数据类型相同

    示例:

    1. openGauss=# SELECT MAX(inv_quantity_on_hand) FROM tpcds.inventory;
  • min(expression)

    描述:所有输入行中expression的最小值。

    参数类型:任意数组、数值、字符串、日期/时间类型、IPV4和IPV6地址(INET型和CIDR型)。

    返回类型:与参数数据类型相同

    示例:

    1. openGauss=# SELECT MIN(inv_quantity_on_hand) FROM tpcds.inventory;
    2. min
    3. -----
    4. 0
    5. (1 row)
  • avg(expression)

    描述:所有输入值的均值(算术平均)。

    返回类型:

    对于任何整数类型输入,结果都是NUMBER类型。

    对于任何浮点输入,结果都是DOUBLE PRECISION类型。

    否则和输入数据类型相同。

    示例:

    1. openGauss=# SELECT AVG(inv_quantity_on_hand) FROM tpcds.inventory;
    2. avg
    3. ----------------------
    4. 500.0387129084044604
    5. (1 row)
  • count(expression)

    描述:返回表中满足expression不为NULL的行数。

    返回类型:BIGINT

    示例:

    1. openGauss=# SELECT COUNT(inv_quantity_on_hand) FROM tpcds.inventory;
    2. count
    3. ----------
    4. 11158087
    5. (1 row)
  • count(*)

    描述:返回表中的记录行数。

    返回类型:BIGINT

    示例:

    1. openGauss=# SELECT COUNT(*) FROM tpcds.inventory;
    2. count
    3. ----------
    4. 11745000
    5. (1 row)
  • median(expression) [over (query partition clause)]

    描述:返回表达式的中位数,计算时NULL将会被median函数忽略。可以使用distinct关键字排除表达式中的重复记录。输入expression的数据类型可以是数值类型(包括integer, double,bigint等), 也可以是interval类型。其他数据类型不支持求取中位数。

    返回类型:double或interval类型

    示例:

    1. select median(id) from (values(1), (2), (3), (4), (null)) test(id);
    2. median
    3. --------
    4. 2.5
    5. (1 row)
  • array_agg(expression)

    描述:将所有输入值(包括空)连接成一个数组。

    返回类型:参数类型的数组

    示例:

    1. openGauss=# SELECT ARRAY_AGG(sr_fee) FROM tpcds.store_returns WHERE sr_customer_sk = 2;
    2. array_agg
    3. ---------------
    4. {22.18,63.21}
    5. (1 row)
  • string_agg(expression, delimiter)

    描述:将输入值连接成为一个字符串,用分隔符分开。

    返回类型:和参数数据类型相同。

    示例:

    1. openGauss=# SELECT string_agg(sr_item_sk, ',') FROM tpcds.store_returns where sr_item_sk < 3;
    2. string_agg
    3. ---------------------------------------------------------------------------------
    4. ------------------------------
    5. 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,
    6. 2,1,1,1,1,1,1,2,2,1,1,2,1,1,1
    7. (1 row)
  • listagg(expression [, delimiter]) WITHIN GROUP(ORDER BY order-list)

    描述:将聚集列数据按WITHIN GROUP指定的排序方式排列,并用delimiter指定的分隔符拼接成一个字符串。

    • expression:必选。指定聚集列名或基于列的有效表达式,不支持DISTINCT关键字和VARIADIC参数。
    • delimiter:可选。指定分隔符,可以是字符串常数或基于分组列的确定性表达式,缺省时表示分隔符为空。
    • order-list:必选。指定分组内的排序方式。

    返回类型:text

    示例:

    聚集列是文本字符集类型。

    1. openGauss=# SELECT deptno, listagg(ename, ',') WITHIN GROUP(ORDER BY ename) AS employees FROM emp GROUP BY deptno;
    2. deptno | employees
    3. --------+--------------------------------------
    4. 10 | CLARK,KING,MILLER
    5. 20 | ADAMS,FORD,JONES,SCOTT,SMITH
    6. 30 | ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
    7. (3 rows)

    聚集列是整型。

    1. openGauss=# SELECT deptno, listagg(mgrno, ',') WITHIN GROUP(ORDER BY mgrno NULLS FIRST) AS mgrnos FROM emp GROUP BY deptno;
    2. deptno | mgrnos
    3. --------+-------------------------------
    4. 10 | 7782,7839
    5. 20 | 7566,7566,7788,7839,7902
    6. 30 | 7698,7698,7698,7698,7698,7839
    7. (3 rows)

    聚集列是浮点类型。

    1. openGauss=# SELECT job, listagg(bonus, '($); ') WITHIN GROUP(ORDER BY bonus DESC) || '($)' AS bonus FROM emp GROUP BY job;
    2. job | bonus
    3. ------------+-------------------------------------------------
    4. CLERK | 10234.21($); 2000.80($); 1100.00($); 1000.22($)
    5. PRESIDENT | 23011.88($)
    6. ANALYST | 2002.12($); 1001.01($)
    7. MANAGER | 10000.01($); 2399.50($); 999.10($)
    8. SALESMAN | 1000.01($); 899.00($); 99.99($); 9.00($)
    9. (5 rows)

    聚集列是时间类型。

    1. openGauss=# SELECT deptno, listagg(hiredate, ', ') WITHIN GROUP(ORDER BY hiredate DESC) AS hiredates FROM emp GROUP BY deptno;
    2. deptno | hiredates
    3. --------+------------------------------------------------------------------------------------------------------------------------------
    4. 10 | 1982-01-23 00:00:00, 1981-11-17 00:00:00, 1981-06-09 00:00:00
    5. 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
    6. 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
    7. (3 rows)

    聚集列是时间间隔类型。

    1. openGauss=# SELECT deptno, listagg(vacationTime, '; ') WITHIN GROUP(ORDER BY vacationTime DESC) AS vacationTime FROM emp GROUP BY deptno;
    2. deptno | vacationtime
    3. --------+------------------------------------------------------------------------------------
    4. 10 | 1 year 30 days; 40 days; 10 days
    5. 20 | 70 days; 36 days; 9 days; 5 days
    6. 30 | 1 year 1 mon; 2 mons 10 days; 30 days; 12 days 12:00:00; 4 days 06:00:00; 24:00:00
    7. (3 rows)

    分隔符缺省时,默认为空。

    1. openGauss=# SELECT deptno, listagg(job) WITHIN GROUP(ORDER BY job) AS jobs FROM emp GROUP BY deptno;
    2. deptno | jobs
    3. --------+----------------------------------------------
    4. 10 | CLERKMANAGERPRESIDENT
    5. 20 | ANALYSTANALYSTCLERKCLERKMANAGER
    6. 30 | CLERKMANAGERSALESMANSALESMANSALESMANSALESMAN
    7. (3 rows)

    listagg作为窗口函数时,OVER子句不支持ORDER BY的窗口排序,listagg列为对应分组的有序聚集。

    1. openGauss=# SELECT deptno, mgrno, bonus, listagg(ename,'; ') WITHIN GROUP(ORDER BY hiredate) OVER(PARTITION BY deptno) AS employees FROM emp;
    2. deptno | mgrno | bonus | employees
    3. --------+-------+----------+-------------------------------------------
    4. 10 | 7839 | 10000.01 | CLARK; KING; MILLER
    5. 10 | | 23011.88 | CLARK; KING; MILLER
    6. 10 | 7782 | 10234.21 | CLARK; KING; MILLER
    7. 20 | 7566 | 2002.12 | FORD; SCOTT; ADAMS; SMITH; JONES
    8. 20 | 7566 | 1001.01 | FORD; SCOTT; ADAMS; SMITH; JONES
    9. 20 | 7788 | 1100.00 | FORD; SCOTT; ADAMS; SMITH; JONES
    10. 20 | 7902 | 2000.80 | FORD; SCOTT; ADAMS; SMITH; JONES
    11. 20 | 7839 | 999.10 | FORD; SCOTT; ADAMS; SMITH; JONES
    12. 30 | 7839 | 2399.50 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN
    13. 30 | 7698 | 9.00 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN
    14. 30 | 7698 | 1000.22 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN
    15. 30 | 7698 | 99.99 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN
    16. 30 | 7698 | 1000.01 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN
    17. 30 | 7698 | 899.00 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN
    18. (14 rows)
  • covar_pop(Y, X)

    描述:总体协方差。

    返回类型:double precision

    示例:

    1. openGauss=# SELECT COVAR_POP(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
    2. covar_pop
    3. ------------------
    4. 829.749627587403
    5. (1 row)
  • covar_samp(Y, X)

    描述:样本协方差。

    返回类型:double precision

    示例:

    1. openGauss=# SELECT COVAR_SAMP(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
    2. covar_samp
    3. ------------------
    4. 830.052235037289
    5. (1 row)
  • stddev_pop(expression)

    描述:总体标准差。

    返回类型:对于浮点类型的输入返回double precision,其他输入返回numeric。

    示例:

    1. openGauss=# SELECT STDDEV_POP(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;
    2. stddev_pop
    3. ------------------
    4. 289.224294957556
    5. (1 row)
  • stddev_samp(expression)

    描述:样本标准差。

    返回类型:对于浮点类型的输入返回double precision,其他输入返回numeric。

    示例:

    1. openGauss=# SELECT STDDEV_SAMP(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;
    2. stddev_samp
    3. ------------------
    4. 289.224359757315
    5. (1 row)
  • var_pop(expression)

    描述:总体方差(总体标准差的平方)

    返回类型:对于浮点类型的输入返回double precision类型,其他输入返回numeric类型。

    示例:

    1. openGauss=# SELECT VAR_POP(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;
    2. var_pop
    3. --------------------
    4. 83650.692793695475
    5. (1 row)
  • var_samp(expression)

    描述:样本方差(样本标准差的平方)

    返回类型:对于浮点类型的输入返回double precision类型,其他输入返回numeric类型。

    示例:

    1. openGauss=# SELECT VAR_SAMP(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;
    2. var_samp
    3. --------------------
    4. 83650.730277028768
    5. (1 row)
  • bit_and(expression)

    描述:所有非NULL输入值的按位与(AND),如果全部输入值皆为NULL,那么结果也为NULL 。

    返回类型:和参数数据类型相同。

    示例:

    1. openGauss=# SELECT BIT_AND(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;
    2. bit_and
    3. ---------
    4. 0
    5. (1 row)
  • bit_or(expression)

    描述:所有非NULL输入值的按位或(OR),如果全部输入值皆为NULL,那么结果也为NULL。

    返回类型:和参数数据类型相同

    示例:

    1. openGauss=# SELECT BIT_OR(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;
    2. bit_or
    3. --------
    4. 1023
    5. (1 row)
  • bool_and(expression)

    描述:如果所有输入值都是真,则为真,否则为假。

    返回类型:bool

    示例:

    1. openGauss=# SELECT bool_and(100 <2500);
    2. bool_and
    3. ----------
    4. t
    5. (1 row)
  • bool_or(expression)

    描述:如果所有输入值只要有一个为真,则为真,否则为假。

    返回类型:bool

    示例:

    1. openGauss=# SELECT bool_or(100 <2500);
    2. bool_or
    3. ----------
    4. t
    5. (1 row)
  • corr(Y, X)

    描述:相关系数

    返回类型:double precision

    示例:

    1. openGauss=# SELECT CORR(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
    2. corr
    3. -------------------
    4. .0381383624904186
    5. (1 row)
  • every(expression)

    描述:等效于bool_and。

    返回类型:bool

    示例:

    1. openGauss=# SELECT every(100 <2500);
    2. every
    3. -------
    4. t
    5. (1 row)
  • regr_avgx(Y, X)

    描述:自变量的平均值 (sum(X)/N)

    返回类型:double precision

    示例:

    1. openGauss=# SELECT REGR_AVGX(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
    2. regr_avgx
    3. ------------------
    4. 578.606576740795
    5. (1 row)
  • regr_avgy(Y, X)

    描述:因变量的平均值 (sum(Y)/N)

    返回类型:double precision

    示例:

    1. openGauss=# SELECT REGR_AVGY(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
    2. regr_avgy
    3. ------------------
    4. 50.0136711629602
    5. (1 row)
  • regr_count(Y, X)

    描述:两个表达式都不为NULL的输入行数。

    返回类型:bigint

    示例:

    1. openGauss=# SELECT REGR_COUNT(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
    2. regr_count
    3. ------------
    4. 2743
    5. (1 row)
  • regr_intercept(Y, X)

    描述:根据所有输入的点(X, Y)按照最小二乘法拟合成一个线性方程,然后返回该直线的Y轴截距。

    返回类型:double precision

    示例:

    1. openGauss=# SELECT REGR_INTERCEPT(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
    2. regr_intercept
    3. ------------------
    4. 49.2040847848607
    5. (1 row)
  • regr_r2(Y, X)

    描述:相关系数的平方

    返回类型:double precision

    示例:

    1. openGauss=# SELECT REGR_R2(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
    2. regr_r2
    3. --------------------
    4. .00145453469345058
    5. (1 row)
  • regr_slope(Y, X)

    描述:根据所有输入的点(X, Y)按照最小二乘法拟合成一个线性方程, 然后返回该直线的斜率。

    返回类型:double precision

    示例:

    1. openGauss=# SELECT REGR_SLOPE(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
    2. regr_slope
    3. --------------------
    4. .00139920009665259
    5. (1 row)
  • regr_sxx(Y, X)

    描述:sum(X^2) - sum(X)^2/N (自变量的“平方和”)

    返回类型:double precision

    示例:

    1. openGauss=# SELECT REGR_SXX(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
    2. regr_sxx
    3. ------------------
    4. 1626645991.46135
    5. (1 row)
  • regr_sxy(Y, X)

    描述:sum(X*Y) - sum(X) * sum(Y)/N (自变量和因变量的“乘方积”)

    返回类型:double precision

    示例:

    1. openGauss=# SELECT REGR_SXY(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
    2. regr_sxy
    3. ------------------
    4. 2276003.22847225
    5. (1 row)
  • regr_syy(Y, X)

    描述:sum(Y^2) - sum(Y)^2/N(因变量的”平方和”)

    返回类型:double precision

    示例:

    1. openGauss=# SELECT REGR_SYY(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
    2. regr_syy
    3. -----------------
    4. 2189417.6547314
    5. (1 row)
  • stddev(expression)

    描述:stddev_samp的别名。

    返回类型:对于浮点类型的输入返回double precision,其他输入返回numeric。

    示例:

    1. openGauss=# SELECT STDDEV(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;
    2. stddev
    3. ------------------
    4. 289.224359757315
    5. (1 row)
  • variance(expexpression,ression)

    描述:var_samp的别名。

    返回类型:对于浮点类型的输入返回double precision类型,其他输入返回numeric类型。

    示例:

    1. openGauss=# SELECT VARIANCE(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;
    2. variance
    3. --------------------
    4. 83650.730277028768
    5. (1 row)
  • delta

    描述:返回当前行和前一行的差值。

    参数:numeric

    返回值类型:numeric

  • checksum(expression)

    描述:返回所有输入值的CHECKSUM值。使用该函数可以用来验证openGauss数据库(不支持openGauss之外的其他数据库)的备份恢复或者数据迁移操作前后表中的数据是否相同。在备份恢复或者数据迁移操作前后都需要用户通过手工执行SQL命令的方式获取执行结果,通过对比获取的执行结果判断操作前后表中的数据是否相同。

    聚集函数 - 图1 说明:

    • 对于大表,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值。

    1. openGauss=# SELECT CHECKSUM(inv_quantity_on_hand) FROM tpcds.inventory;
    2. checksum
    3. -------------------
    4. 24417258945265247
    5. (1 row)

    表中不能默认转为TEXT类型的某列的CHECKSUM值。注意此时CHECKSUM参数是列名::TEXT。

    1. openGauss=# SELECT CHECKSUM(inv_quantity_on_hand::TEXT) FROM tpcds.inventory;
    2. checksum
    3. -------------------
    4. 24417258945265247
    5. (1 row)

    表中所有列的CHECKSUM值。注意此时CHECKSUM参数是表名::TEXT,且表名前不加Schema。

    1. openGauss=# SELECT CHECKSUM(inventory::TEXT) FROM tpcds.inventory;
    2. checksum
    3. -------------------
    4. 25223696246875800
    5. (1 row)
  • first(anyelement)

    描述:返回第一个非NULL输入。

    返回类型:anyelement

    1. openGauss=# select * from tba;
    2. name
    3. -----
    4. A
    5. A
    6. D
    7. (4 rows)
    8. openGauss=# select first(name) from tba;
    9. first
    10. -----
    11. A
    12. (1 rows)
  • last(anyelement)

    描述:返回最后一个非NULL输入。

    返回类型:anyelement

    1. openGauss=# select * from tba;
    2. name
    3. -----
    4. A
    5. A
    6. D
    7. (4 rows)
    8. openGauss=# select last(name) from tba;
    9. last
    10. -----
    11. D
    12. (1 rows)
  • mode() within group (order by value anyelement)

    描述:返回某列中出现频率最高的值,如果多个值频率相同,则返回最小的那个值。排序方式和该列类型的默认排序方式相同。其中value为输入参数,可以为任意类型。

    返回类型:与输入参数类型相同。

    示例:

    1. openGauss=# select mode() within group (order by value) from (values(1, 'a'), (2, 'b'), (2, 'c')) v(value, tag);
    2. mode
    3. ------
    4. 2
    5. (1 row)
    6. openGauss=# select mode() within group (order by tag) from (values(1, 'a'), (2, 'b'), (2, 'c')) v(value, tag);
    7. mode
    8. ------
    9. a
    10. (1 row)
  • json_agg(any)

    描述:将值聚集为json数组。

    返回类型:array-json

    示例:

    1. openGauss=# select * from classes;
    2. name | score
    3. -----+-------
    4. A | 2
    5. A | 3
    6. D | 5
    7. D |
    8. (4 rows)
    1. openGauss=# select name, json_agg(score) score from classes group by name order by name;
    2. name | score
    3. -----+-----------------
    4. A | [2, 3]
    5. D | [5, null]
    6. | [null]
    7. (3 rows)
  • json_object_agg(any, any)

    描述:将值聚集为json对象。

    返回类型:object-json

    示例:

    1. openGauss=# select * from classes;
    2. name | score
    3. -----+-------
    4. A | 2
    5. A | 3
    6. D | 5
    7. D |
    8. (4 rows)
    1. openGauss=# select json_object_agg(name, score) from classes group by name order by name;
    2. json_object_agg
    3. -------------------------
    4. { "A" : 2, "A" : 3 }
    5. { "D" : 5, "D" : null }
    6. (2 rows)