The ability to create stored aggregate functions was added in MariaDB 10.3.3.
Aggregate functions are functions that are computed over a sequence of rows and return one result for the sequence of rows.
Creating a custom aggregate function is done using the CREATE FUNCTION statement with two main differences:
CREATE AGGREGATE FUNCTION
FETCH GROUP NEXT ROW
instruction inside the loop CREATE AGGREGATE FUNCTION function_name (parameters) RETURNS return_type BEGIN All types of declarations DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN return_val; LOOP FETCH GROUP NEXT ROW; // fetches next row from table other instructions END LOOP; END
Stored aggregate functions were a 2016 Google Summer of Code project by Varun Gupta.
SET sql_mode=Oracle; DELIMITER // CREATE AGGREGATE FUNCTION function_name (parameters) RETURN return_type declarations BEGIN LOOP FETCH GROUP NEXT ROW; -- fetches next row from table -- other instructions END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN return_val; END // DELIMITER ;
First a simplified example:
CREATE TABLE marks(stud_id INT, grade_count INT); INSERT INTO marks VALUES (1,6), (2,4), (3,7), (4,5), (5,8); SELECT * FROM marks; +---------+-------------+ | stud_id | grade_count | +---------+-------------+ | 1 | 6 | | 2 | 4 | | 3 | 7 | | 4 | 5 | | 5 | 8 | +---------+-------------+ DELIMITER // CREATE AGGREGATE FUNCTION IF NOT EXISTS aggregate_count(x INT) RETURNS INT BEGIN DECLARE count_students INT DEFAULT 0; DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN count_students; LOOP FETCH GROUP NEXT ROW; IF x THEN SET count_students = count_students+1; END IF; END LOOP; END // DELIMITER ;
A non-trivial example that cannot easily be rewritten using existing functions:
DELIMITER // CREATE AGGREGATE FUNCTION medi_int(x INT) RETURNS DOUBLE BEGIN DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN DECLARE res DOUBLE; DECLARE cnt INT DEFAULT (SELECT COUNT(*) FROM tt); DECLARE lim INT DEFAULT (cnt-1) DIV 2; IF cnt % 2 = 0 THEN SET res = (SELECT AVG(a) FROM (SELECT a FROM tt ORDER BY a LIMIT lim,2) ttt); ELSE SET res = (SELECT a FROM tt ORDER BY a LIMIT lim,1); END IF; DROP TEMPORARY TABLE tt; RETURN res; END; CREATE TEMPORARY TABLE tt (a INT); LOOP FETCH GROUP NEXT ROW; INSERT INTO tt VALUES (x); END LOOP; END // DELIMITER ;
This uses the same marks table as created above.
SET sql_mode=Oracle; DELIMITER // CREATE AGGREGATE FUNCTION aggregate_count(x INT) RETURN INT AS count_students INT DEFAULT 0; BEGIN LOOP FETCH GROUP NEXT ROW; IF x THEN SET count_students := count_students+1; END IF; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN count_students; END aggregate_count // DELIMITER ; SELECT aggregate_count(stud_id) FROM marks;
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/stored-aggregate-functions/