GBASE南大通用GBase 8s数据库常用函数-聚合函数

GBASE南大通用分享：GBase 8s数据库常用函数

聚合函数

数据准备

drop table if exists t_dept;

create table t_dept(f_deptid int, f_deptname varchar(50));

insert into t_dept values(1, 'Dev');
insert into t_dept values(2, 'Test');
insert into t_dept values(3, 'Market');

drop table if exists t_employee;

create table t_employee(f_employeeid int, f_deptid int, f_employeename varchar(50), f_salary money);

insert into t_employee values(1, 1, 'Tom', 6000.00);
insert into t_employee values(2, 1, 'Jack', 8000.00);
insert into t_employee values(3, 1, 'Mary', 6600.00);

insert into t_employee values(4, 2, 'Henry', 5000.00);
insert into t_employee values(5, 2, 'Rose', 7500.00);
insert into t_employee values(6, 2, 'Bill', 6500.00);

insert into t_employee values(7, 3, 'Kate', 5000.00);
insert into t_employee values(8, 3, 'Bob', 9000.00);

COUNT

select a.f_deptid, b.f_deptname, count(1) as f_cnt from t_employee a, t_dept b where a.f_deptid = b.f_deptid group by a.f_deptid, b.f_deptname order by a.f_deptid;

> select a.f_deptid, b.f_deptname, count(1) as f_cnt from t_employee a, t_dept b where a.f_deptid = b.f_deptid group by a.f_deptid, b.f_deptname order by a.f_deptid;

f_deptid    1
f_deptname  Dev
f_cnt       3

f_deptid    2
f_deptname  Test
f_cnt       3

f_deptid    3
f_deptname  Market
f_cnt       2

3 row(s) retrieved.

> 

SUM

select a.f_deptid, b.f_deptname, sum(f_salary) as f_salary from t_employee a, t_dept b where a.f_deptid = b.f_deptid group by a.f_deptid, b.f_deptname order by a.f_deptid;

> select a.f_deptid, b.f_deptname, sum(f_salary) as f_salary from t_employee a, t_dept b where a.f_deptid = b.f_deptid group by a.f_deptid, b.f_deptname order by a.f_deptid;

f_deptid    1
f_deptname  Dev
f_salary    $20600.00

f_deptid    2
f_deptname  Test
f_salary    $19000.00

f_deptid    3
f_deptname  Market
f_salary    $14000.00

3 row(s) retrieved.

> 

GBASE南大通用分享：AVG

select a.f_deptid, b.f_deptname, avg(f_salary) as f_salary_avg from t_employee a, t_dept b where a.f_deptid = b.f_deptid group by a.f_deptid, b.f_deptname order by a.f_deptid;

> select a.f_deptid, b.f_deptname, avg(f_salary) as f_salary_avg from t_employee a, t_dept b where a.f_deptid = b.f_deptid group by a.f_deptid, b.f_deptname order by a.f_deptid;

f_deptid      1
f_deptname    Dev
f_salary_avg  $6866.67

f_deptid      2
f_deptname    Test
f_salary_avg  $6333.33

f_deptid      3
f_deptname    Market
f_salary_avg  $7000.00

3 row(s) retrieved.

> 

GBASE南大通用分享：MAX/MIN

select a.f_deptid, b.f_deptname, max(f_salary) as f_salary_max, min(f_salary) as f_salary_min from t_employee a, t_dept b where a.f_deptid = b.f_deptid group by a.f_deptid, b.f_deptname order by a.f_deptid;

> select a.f_deptid, b.f_deptname, max(f_salary) as f_salary_max, min(f_salary) as f_salary_min from t_employee a, t_dept b where a.f_deptid = b.f_deptid group by a.f_deptid, b.f_deptname order by a.f_deptid;

f_deptid      1
f_deptname    Dev
f_salary_max  $8000.00
f_salary_min  $6000.00

f_deptid      2
f_deptname    Test
f_salary_max  $7500.00
f_salary_min  $5000.00

f_deptid      3
f_deptname    Market
f_salary_max  $9000.00
f_salary_min  $5000.00

3 row(s) retrieved.

> 

GBASE南大通用分享：WM_CONCAT

select a.f_deptid, b.f_deptname, wm_concat(f_employeename) as f_employees from t_employee a, t_dept b where a.f_deptid = b.f_deptid group by a.f_deptid, b.f_deptname order by a.f_deptid;

> select a.f_deptid, b.f_deptname, wm_concat(f_employeename) as f_employees from t_employee a, t_dept b where a.f_deptid = b.f_deptid group by a.f_deptid, b.f_deptname order by a.f_deptid;


f_deptid     1
f_deptname   Dev
f_employees  Tom,Jack,Mary 

f_deptid     2
f_deptname   Test
f_employees  Henry,Rose,Bill 

f_deptid     3
f_deptname   Market
f_employees  Kate,Bob 

3 row(s) retrieved.

>