1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
|
delimiter |;
# aggregate AND function
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|
delimiter ;|
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;
delimiter |;
# aggregate AVG function
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|
delimiter ;|
select agg_avg(sal) from t1 where id>= 1;
delimiter |;
# aggregate MAX function
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|
delimiter ;|
select agg_max(sal) from t1 where id>= 1;
delimiter |;
# aggregate MIN function
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|
delimiter ;|
select agg_min(sal) from t1 where id>= 1;
delimiter |;
# aggregate XOR function
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|
delimiter ;|
select agg_xor(sal) from t1 where id>= 1;
delimiter |;
# aggregate SUM function
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|
delimiter ;|
select agg_sum(sal) from t1 where id>= 1;
delimiter |;
# aggregate INCREM function
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|
delimiter ;|
# no parameters
select agg_increm() from t1;
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);
# null values
select agg_sum(sal) from t2;
# no tables
select agg_sum(1);
# aggregate function called from regular functions
create function f1() returns int
return (select agg_sum(sal) from t1);
select f1();
create function increase() returns INT
return 1;
# regular functions called from aggregate functions
delimiter |;
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|
delimiter ;|
select agg_reg_func() from t2;
delimiter |;
# aggregate functions called from aggregate functions
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|
delimiter ;|
select agg_agg_func(sal) from t2;
# GROUP BY AND ORDER BY
--sorted_result
select id, agg_sum(sal) from t1 where id>= 1 group by id;
--sorted_result
select val, agg_sum(sal) from t1 where id>= 1 group by val;
select counter, agg_sum(sal) from t1 where id>= 1 group by id order by counter;
select counter, id, agg_sum(sal), agg_sum(sal) from t1 where id>= 1 group by id order by counter;
select counter, id, agg_sum(sal), sum(distinct sal) from t1 where id>= 1 group by id order by counter desc;
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;
##### insert aggregate function value into a table ######
create table t3 (i int);
INSERT INTO t3 (i) select agg_sum(sal) from t1;
select * from t3;
delimiter |;
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|
delimiter ;|
select agg_func(sal) from t1;
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;
|
