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
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
|
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;
|
