GBase 8c V3.0.0数据类型——聚集函数

l sum(expression) 

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

返回类型： 

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

n 对于SMALLINT或INT输入，输出类型为BIGINT。

n 对于BIGINT输入，输出类型为NUMBER 。

n 对于浮点数输入，输出类型为DOUBLE PRECISION。

示例： 

 gbase=# SELECT SUM(ss_ext_tax) FROM public.STORE_SALES;

  sum      

--------------

 213267594.69

(1 row)

l max(expression) 

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

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

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

示例： 

 gbase=# SELECT MAX(inv_quantity_on_hand) FROM public.inventory;

max

---------------

21000000

(1 row)

l min(expression) 

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

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

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

示例： 

 gbase=# SELECT MIN(inv_quantity_on_hand) FROM public.inventory;

 min

-----

   0

(1 row)

l avg(expression) 

描述：所有输入值的均值（算术平均）。 

返回类型： 

对于任何整数类型输入，结果都是NUMBER类型。对于任何浮点输入，结果都是DOUBLE PRECISION类型。否则和输入数据类型相同。

示例： 

 gbase=# SELECT AVG(inv_quantity_on_hand) FROM public.inventory;

         avg          

----------------------

 500.0387129084044604

(1 row)

l count(expression) 

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

返回类型：BIGINT 

示例： 

 gbase=# SELECT COUNT(inv_quantity_on_hand) FROM public.inventory;

  count   

----------

 11158087

(1 row)

l count(*) 

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

返回类型：BIGINT 

示例： 

 gbase=# SELECT COUNT(*) FROM public.inventory;

  count   

----------

 11745000

(1 row)

l median(expression) [over (query partition clause)] 

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

返回类型：double或interval类型 

示例： 

gbase=# SELECT MEDIAN(id) FROM (values(1), (2), (3), (4), (null)) test(id);

​median

--------

    ​2.5

(1 row)

l array_agg(expression) 

描述：将所有输入值（包括空）连接成一个数组。 

返回类型：参数类型的数组。 

示例： 

 gbase=# SELECT ARRAY_AGG(sr_fee) FROM public.store_returns WHERE sr_customer_sk = 2;

   array_agg   

---------------

 {22.18,63.21}

(1 row)

l string_agg(expression, delimiter) 

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

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

示例： 

 gbase=# SELECT string_agg(sr_item_sk, ',') FROM public.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)

l listagg(expression [, delimiter]) WITHIN GROUP(ORDER BY order-list) 

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

n expression：必选。指定聚集列名或基于列的有效表达式，不支持DISTINCT关键字和VARIADIC参数。

n delimiter：可选。指定分隔符，可以是字符串常数或基于分组列的确定性表达式，缺省时表示分隔符为空。

n order-list：必选。指定分组内的排序方式。

返回类型：text 

示例： 

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

 gbase=# 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)

聚集列是整型。 

 gbase=# 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)

聚集列是浮点类型。 

 gbase=# 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)

聚集列是时间类型。 

 gbase=# 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)

聚集列是时间间隔类型。 

 gbase=# 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)

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

 gbase=# 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列为对应分组的有序聚集。 

 gbase=# 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)

l covar_pop(Y, X) 

描述：总体协方差。 

返回类型：double precision 

示例： 

 gbase=# SELECT COVAR_POP(sr_fee, sr_net_loss) FROM public.store_returns WHERE sr_customer_sk < 1000;

    covar_pop     

------------------

 829.749627587403

(1 row)

l covar_samp(Y, X) 

描述：样本协方差。 

返回类型：double precision 

示例： 

 gbase=# SELECT COVAR_SAMP(sr_fee, sr_net_loss) FROM public.store_returns WHERE sr_customer_sk < 1000;

    covar_samp    

------------------

 830.052235037289

(1 row)

l stddev_pop(expression) 

描述：总体标准差。 

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

示例： 

 gbase=# SELECT STDDEV_POP(inv_quantity_on_hand) FROM public.inventory WHERE inv_warehouse_sk = 1;

    stddev_pop    

------------------

 289.224294957556

(1 row)

l stddev_samp(expression) 

描述：样本标准差。 

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

示例： 

 gbase=# SELECT STDDEV_SAMP(inv_quantity_on_hand) FROM public.inventory WHERE inv_warehouse_sk = 1;

   stddev_samp    

------------------

 289.224359757315

(1 row)

l var_pop(expression) 

描述：总体方差（总体标准差的平方） 

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

示例： 

 gbase=# SELECT VAR_POP(inv_quantity_on_hand) FROM public.inventory WHERE inv_warehouse_sk = 1;

      var_pop       

--------------------

 83650.692793695475

(1 row)

l var_samp(expression) 

描述：样本方差（样本标准差的平方） 

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

示例： 

 gbase=# SELECT VAR_SAMP(inv_quantity_on_hand) FROM public.inventory WHERE inv_warehouse_sk = 1;

      var_samp      

--------------------

 83650.730277028768

(1 row)

l bit_and(expression) 

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

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

示例： 

 gbase=# SELECT BIT_AND(inv_quantity_on_hand) FROM public.inventory WHERE inv_warehouse_sk = 1;

 bit_and

---------

       0

(1 row)

l bit_or(expression) 

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

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

示例： 

 gbase=# SELECT BIT_OR(inv_quantity_on_hand) FROM public.inventory WHERE inv_warehouse_sk = 1;

 bit_or

--------

   1023

(1 row)

l bool_and(expression) 

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

返回类型：bool 

示例： 

 gbase=# SELECT bool_and(100 <2500);

 bool_and

----------

 t

(1 row)

l bool_or(expression) 

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

返回类型：bool 

示例： 

 gbase=# SELECT bool_or(100 <2500);

 bool_or

----------

 t

(1 row)

l corr(Y, X) 

描述：相关系数 

返回类型：double precision 

示例： 

 gbase=# SELECT CORR(sr_fee, sr_net_loss) FROM public.store_returns WHERE sr_customer_sk < 1000;

       corr        

-------------------

 .0381383624904186

(1 row)

l every(expression) 

描述：等效于bool_and。 

返回类型：bool 

示例： 

 gbase=# SELECT every(100 <2500);

 every

-------

 t

(1 row)

l regr_avgx(Y, X) 

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

返回类型：double precision 

示例： 

 gbase=# SELECT REGR_AVGX(sr_fee, sr_net_loss) FROM public.store_returns WHERE sr_customer_sk < 1000;

    regr_avgx     

------------------

 578.606576740795

(1 row)

l regr_avgy(Y, X) 

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

返回类型：double precision 

示例： 

 gbase=# SELECT REGR_AVGY(sr_fee, sr_net_loss) FROM public.store_returns WHERE sr_customer_sk < 1000;

    regr_avgy     

------------------

 50.0136711629602

(1 row)

l regr_count(Y, X) 

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

返回类型：bigint 

示例： 

 gbase=# SELECT REGR_COUNT(sr_fee, sr_net_loss) FROM public.store_returns WHERE sr_customer_sk < 1000;

 regr_count

------------

       2743

(1 row)

l regr_intercept(Y, X) 

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

返回类型：double precision 

示例： 

 gbase=# SELECT REGR_INTERCEPT(sr_fee, sr_net_loss) FROM public.store_returns WHERE sr_customer_sk < 1000;

  regr_intercept  

------------------

 49.2040847848607

(1 row)

l regr_r2(Y, X) 

描述：相关系数的平方。 

返回类型：double precision 

示例： 

 gbase=# SELECT REGR_R2(sr_fee, sr_net_loss) FROM public.store_returns WHERE sr_customer_sk < 1000;

      regr_r2       

--------------------

 .00145453469345058

(1 row)

l regr_slope(Y, X) 

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

返回类型：double precision 

示例： 

 gbase=# SELECT REGR_SLOPE(sr_fee, sr_net_loss) FROM public.store_returns WHERE sr_customer_sk < 1000;

     regr_slope     

--------------------

 .00139920009665259

(1 row)

l regr_sxx(Y, X) 

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

返回类型：double precision 

示例： 

 gbase=# SELECT REGR_SXX(sr_fee, sr_net_loss) FROM public.store_returns WHERE sr_customer_sk < 1000;

     regr_sxx     

------------------

 1626645991.46135

(1 row)

l regr_sxy(Y, X) 

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

返回类型：double precision 

示例： 

 gbase=# SELECT REGR_SXY(sr_fee, sr_net_loss) FROM public.store_returns WHERE sr_customer_sk < 1000;

     regr_sxy     

------------------

 2276003.22847225

(1 row)

l regr_syy(Y, X) 

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

返回类型：double precision 

示例： 

 gbase=# SELECT REGR_SYY(sr_fee, sr_net_loss) FROM public.store_returns WHERE sr_customer_sk < 1000;

    regr_syy     

-----------------

 2189417.6547314

(1 row)

l stddev(expression) 

描述：stddev_samp的别名。 

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

示例： 

 gbase=# SELECT STDDEV(inv_quantity_on_hand) FROM public.inventory WHERE inv_warehouse_sk = 1;

      stddev      

------------------

 289.224359757315

(1 row)

l variance(expexpression,ression) 

描述：var_samp的别名。 

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

示例： 

 gbase=# SELECT VARIANCE(inv_quantity_on_hand) FROM public.inventory WHERE inv_warehouse_sk = 1;

      variance      

--------------------

 83650.730277028768

(1 row)

l delta 

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

参数：numeric 

返回值类型：numeric 

checksum(expression) 

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

对于大表，CHECKSUM函数可能会需要很长时间。 

如果某两表的CHECKSUM值不同，则表明两表的内容是不同的。由于CHECKSUM函数中使用散列函数不能保证无冲突，因此两个不同内容的表可能会得到相同的CHECKSUM值，存在这种情况的可能性较小。对于列进行的CHECKSUM也存在相同的情况。 

n 对于时间类型timestamp, timestamptz和smalldatetime，计算CHECKSUM值时请确保时区设置一致。 

n 若计算某列的CHECKSUM值，且该列类型可以默认转为TEXT类型，则expression为列名。

n 若计算某列的CHECKSUM值，且该列类型不能默认转为TEXT类型，则expression为列名::TEXT。

n 若计算所有列的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值。 

​gbase=# SELECT CHECKSUM(inv_quantity_on_hand) FROM public.inventory;

      ​checksum      

 ​-------------------

  ​24417258945265247

 ​(1 row)

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

​gbase=# SELECT CHECKSUM(inv_quantity_on_hand::TEXT) FROM public.inventory;

      ​checksum      

 ​-------------------

  ​24417258945265247

 ​(1 row)

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

gbase=# SELECT CHECKSUM(inventory::TEXT) FROM public.inventory;

      ​checksum      

 ​-------------------

  ​25223696246875800

 ​(1 row)

l first(anyelement) 

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

返回类型：anyelement 

 gbase=# select * from tba;

name

-----

A    

A    

D    

(4 rows)

    

gbase=# select first(name) from tba;

first

-----

A

(1 rows)

l last(anyelement) 

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

返回类型：anyelement 

 gbase=# select * from tba;

name

-----

A    

A    

D    

(4 rows)

    

gbase=# select last(name) from tba;

last

-----

D

(1 rows)

l mode() within group (order by value anyelement) 

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

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

示例： 

 gbase=# select mode() within group (order by value) from (values(1, 'a'), (2, 'b'), (2, 'c')) v(value, tag);

 mode

------

    2

(1 row)

gbase=# select mode() within group (order by tag) from (values(1, 'a'), (2, 'b'), (2, 'c')) v(value, tag);

 mode

------

 a

(1 row)

l json_agg(any) 

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

返回类型：array-json 

示例： 

 gbase=# select * from classes;

name | score

-----+-------

A    |     2

A    |     3

D    |     5

D    |

(4 rows)

 gbase=# select name, json_agg(score) score from classes group by name order by name;

name |      score

-----+-----------------

A    | [2, 3]

D    | [5, null]

(2 rows)

l json_object_agg(any, any) 

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

返回类型：object-json 

示例： 

 gbase=# select * from classes;

name | score

-----+-------

A    |     2

A    |     3

D    |     5

D    |

(4 rows)

 gbase=# select json_object_agg(name, score) from classes group by name order by name;

json_object_agg

-------------------------

{ "A" : 2, "A" : 3 }

{ "D" : 5, "D" : null }

(2 rows)