create aggregate function agg_and(x INT) returns INT
begin
declare z double default 1000;
declare continue handler for not found return z;
loop
fetch group next row;
set z= (z&x);
end loop;
end|
create table t1 (sal int, id int, val int, counter int);
INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 2, 10, 2);
INSERT INTO t1 (sal, id, val, counter) VALUES (7000, 1, 16, 5);
INSERT INTO t1 (sal, id, val, counter) VALUES (6000, 2, 18, 1);
INSERT INTO t1 (sal, id, val, counter) VALUES (5000, 3, 15, 3);
INSERT INTO t1 (sal, id, val, counter) VALUES (3000, 4, 11, 4);
INSERT INTO t1 (sal, id, val, counter) VALUES (2000, 5, 10, 7);
INSERT INTO t1 (sal, id, val, counter) VALUES (5000, 7, 13, 8);
INSERT INTO t1 (sal, id, val, counter) VALUES (6000, 6, 19, 9);
INSERT INTO t1 (sal, id, val, counter) VALUES (7000, 7, 12, 0);
INSERT INTO t1 (sal, id, val, counter) VALUES (4000, 6, 14, 6);
INSERT INTO t1 (sal, id, val, counter) VALUES (8000, 5, 19, 11);
INSERT INTO t1 (sal, id, val, counter) VALUES (9000, 4, 11, 12);
INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 3, 11, 13);
select agg_and(sal) from t1 where id>= 1;
agg_and(sal)
768
create aggregate function agg_avg(x INT) returns double
begin
declare z double default 0;
declare count double default 0;
declare continue handler for not found return z/count;
loop
fetch group next row;
set z= z+x;
set count= count+1;
end loop;
end|
select agg_avg(sal) from t1 where id>= 1;
agg_avg(sal)
4923.076923076923
create aggregate function agg_max(x INT) returns INT
begin
declare maxi INT default 1;
declare continue handler for not found return maxi;
loop
fetch group next row;
if maxi < x then
set maxi= x;
end if;
end loop;
end|
select agg_max(sal) from t1 where id>= 1;
agg_max(sal)
9000
create aggregate function agg_min(x INT) returns double
begin
declare mini INT default 100000;
declare continue handler for not found return mini;
loop
fetch group next row;
if mini > x then
set mini = x;
end if;
end loop;
end|
select agg_min(sal) from t1 where id>= 1;
agg_min(sal)
1000
create aggregate function agg_xor(x INT) returns double
begin
declare z double default 0;
declare continue handler for not found return z;
loop
fetch group next row;
set z= z^x;
end loop;
end|
select agg_xor(sal) from t1 where id>= 1;
agg_xor(sal)
16288
create aggregate function agg_sum(x INT) returns INT
begin
declare z int default 0;
declare continue handler for not found return z;
loop
fetch group next row;
set z= z+x;
end loop;
end|
select agg_sum(sal) from t1 where id>= 1;
agg_sum(sal)
64000
create aggregate function agg_increm() returns INT
begin
declare z double default 0;
declare continue handler for not found return z;
loop
fetch group next row;
set z= z+1;
end loop;
end|
select agg_increm() from t1;
agg_increm()
13
create table t2 (sal int, id int);
INSERT INTO t2 (sal, id) VALUES (NULL, 1);
INSERT INTO t2 (sal, id) VALUES (2000, 1);
INSERT INTO t2 (sal, id) VALUES (3000, 1);
select agg_sum(sal) from t2;
agg_sum(sal)
NULL
select agg_sum(1);
agg_sum(1)
1
create function f1() returns int
return (select agg_sum(sal) from t1);
select f1();
f1()
64000
create function increase() returns INT
return 1;
create aggregate function agg_reg_func() returns INT
begin
declare z double default 0;
declare continue handler for not found return z;
loop
fetch group next row;
set z= z+f1();
end loop;
end|
select agg_reg_func() from t2;
agg_reg_func()
192000
Warnings:
Note	4092	At line 6 in test.agg_reg_func
Note	4092	At line 6 in test.agg_reg_func
Note	4092	At line 6 in test.agg_reg_func
create aggregate function agg_agg_func(x INT) returns INT
begin
declare z int default 0;
declare continue handler for not found return z;
loop
fetch group next row;
if x then
set z= z+(select agg_sum(sal) from t1);
end if;
end loop;
end|
select agg_agg_func(sal) from t2;
agg_agg_func(sal)
128000
Warnings:
Note	4092	At line 6 in test.agg_agg_func
Note	4092	At line 6 in test.agg_agg_func
select id, agg_sum(sal) from t1 where id>= 1 group by id;
id	agg_sum(sal)
1	7000
2	7000
3	6000
4	12000
5	10000
6	10000
7	12000
select val, agg_sum(sal) from t1 where id>= 1 group by val;
val	agg_sum(sal)
10	3000
11	13000
12	7000
13	5000
14	4000
15	5000
16	7000
18	6000
19	14000
select counter, agg_sum(sal) from t1 where id>= 1 group by id order by counter;
counter	agg_sum(sal)
0	12000
2	7000
4	12000
5	7000
7	10000
9	10000
13	6000
select counter, id, agg_sum(sal), agg_sum(sal) from t1 where id>= 1 group by id order by counter;
counter	id	agg_sum(sal)	agg_sum(sal)
0	7	12000	12000
2	2	7000	7000
4	4	12000	12000
5	1	7000	7000
7	5	10000	10000
9	6	10000	10000
13	3	6000	6000
select counter, id, agg_sum(sal), sum(distinct sal) from t1 where id>= 1 group by id order by counter desc;
counter	id	agg_sum(sal)	sum(distinct sal)
13	3	6000	6000
9	6	10000	10000
7	5	10000	10000
5	1	7000	7000
4	4	12000	12000
2	2	7000	7000
0	7	12000	12000
select val, counter, id, agg_sum(sal), sum(distinct sal), agg_min(sal), agg_max(sal), agg_avg(sal) from t1 where id>= 1 group by id, val order by counter, val;
val	counter	id	agg_sum(sal)	sum(distinct sal)	agg_min(sal)	agg_max(sal)	agg_avg(sal)
12	0	7	7000	7000	7000	7000	7000
18	1	2	6000	6000	6000	6000	6000
10	2	2	1000	1000	1000	1000	1000
15	3	3	5000	5000	5000	5000	5000
11	4	4	12000	12000	3000	9000	6000
16	5	1	7000	7000	7000	7000	7000
14	6	6	4000	4000	4000	4000	4000
10	7	5	2000	2000	2000	2000	2000
13	8	7	5000	5000	5000	5000	5000
19	9	6	6000	6000	6000	6000	6000
19	11	5	8000	8000	8000	8000	8000
11	13	3	1000	1000	1000	1000	1000
create table t3 (i int);
INSERT INTO t3 (i) select agg_sum(sal) from t1;
select * from t3;
i
64000
create aggregate function agg_func(x INT) returns INT
begin
declare z int default 0;
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE a,b,c  INT;
DECLARE cur1 CURSOR FOR SELECT id FROM test.t2;
declare continue handler for not found return z;
outer_loop: LOOP
FETCH GROUP NEXT ROW;
set z= z+x;
inner_block: begin
DECLARE cur2 CURSOR FOR SELECT id FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur2;
read_loop: LOOP
FETCH cur2 INTO a;
IF done THEN
CLOSE cur2;
LEAVE read_loop;
END IF;
END LOOP read_loop;
end inner_block;
END LOOP outer_loop;
end|
select agg_func(sal) from t1;
agg_func(sal)
64000
Warnings:
Note	4092	At line 9 in test.agg_func
Note	4092	At line 9 in test.agg_func
Note	4092	At line 9 in test.agg_func
Note	4092	At line 9 in test.agg_func
Note	4092	At line 9 in test.agg_func
Note	4092	At line 9 in test.agg_func
Note	4092	At line 9 in test.agg_func
Note	4092	At line 9 in test.agg_func
Note	4092	At line 9 in test.agg_func
Note	4092	At line 9 in test.agg_func
Note	4092	At line 9 in test.agg_func
Note	4092	At line 9 in test.agg_func
Note	4092	At line 9 in test.agg_func
drop table t1;
drop table t2;
drop table t3;
drop function agg_sum;
drop function agg_min;
drop function agg_max;
drop function agg_avg;
drop function agg_xor;
drop function agg_and;
drop function agg_increm;
drop function f1;
drop function increase;
drop function agg_reg_func;
drop function agg_agg_func;
drop function agg_func;