项目需要,想实现一个类似于mysql的聚合函数的函数来完成自己的sql查询,但是查不到mysql内置的聚合函数是怎么写的,百度了很久都没有找到相关的类似的案例,最终也只是在一遍文章中找到了一些思路。
我们都知道,聚合函数是对我们group by之后的数据进行处理的,那么我们的函数的参数应该会是一个list或者是数组。但是,由于没有类似的案例做参考,像sum、max等这些聚合函数,内部到底是怎么处理这个参数的,于是转变了思路,将函数的参数穿化成一个字符串,然后在函数的内部将这个字符串再进行拆分。这样我们可以在将传入的参数嵌套一层GROUP_CONCAT()函数。
案例如下:
这里先说明一下我的需求,我项目需要计算某段时间内某个点位下ph的平均值,但是ph的平均值不是简单的 总和/总个数,而是一个比较复杂的公式,。
函数如下:
CREATE DEFINER=`root`@`%` FUNCTION `F_CalPhAvgVaule`(str VARCHAR(21845)) RETURNS double
BEGIN
declare lastValue double;
declare avgValue double;
declare counts int default 1;
SELECT CHAR_LENGTH(str) - CHAR_LENGTH(REPLACE(str,',','')) + 1 INTO counts;
SET @i = 1;
SET @dvalue = 0.0;
WHILE @i <= counts DO
SET @dvalue = @dvalue + POW(10,-SUBSTRING_INDEX(subString_index(str,',',@i),',',-1) + 0);
SET @i = @i + 1;
END WHILE;
SET avgValue = @dvalue / counts;
SET lastValue = -LOG10(avgValue);
RETURN lastValue;
END
首先新建一个表来简单的测试
插入几条测试数据
sql
SELECT ITEM 分析项目, CASE WHEN ITEM LIKE '%ph%' THEN F_CalPhAvgVaule ( GROUP_CONCAT( `VALUE` ) ) ELSE AVG( `VALUE` ) END 均值
FROM T_TEST_T T
GROUP BY POSITION, ITEM
运行结果如下:
至此实现函数。
上面的ph均值计算公式是针对同样的体积,如果是体积不等的情况,比如降水类型的则还需要降水量参与计算,降水类型的请参考下面的函数:
CREATE DEFINER=`root`@`%` FUNCTION `F_GET_PH_AVG`(str VARCHAR(21845), str2 VARCHAR(21845)) RETURNS double
BEGIN
declare lastValue double;
declare avgValue double;
declare counts int default 1;
SELECT CHAR_LENGTH(str) - CHAR_LENGTH(REPLACE(str,',','')) + 1 INTO counts;
SET @i = 1;
SET @dvalue = 0.0;
SET @jsSum = 0.0;
WHILE @i <= counts DO
SET @dvalue = @dvalue + POW(10,-SUBSTRING_INDEX(subString_index(str,',',@i),',',-1) + 0) * SUBSTRING_INDEX(subString_index(str2,',',@i),',',-1);
SET @jsSum = @jsSum + SUBSTRING_INDEX(subString_index(str2,',',@i),',',-1);
SET @i = @i + 1;
END WHILE;
SET avgValue = @dvalue / @jsSum;
SET lastValue = -LOG10(avgValue);
RETURN lastValue;
END
其中第一个参数是ph结果值经过group_concat之后的字符串,第二个值对应的是降水量的值对应的group_concat之后的字符串,用法类似上面的例子。