使用MySQL计算查询字符串中参数的平均值

03 June 2014

一、需求场景

前端用javascript代码统计了页面的一些性能数据，如DOMContentLoaded、onload开始时间，业务代码加载、执行等耗时时间。这些数据以url查询字符串的形式作为一条http请求发送给服务器。形式如下（只显示查询字符串）：

domReady=500&onload=900&jobLoad=2000&jobExec=200
domReady=510&onload=930&jobLoad=2100&jobExec=230
domReady=530&onload=950&jobLoad=2300&jobExec=250
domReady=550&onload=970&jobLoad=2100&jobExec=230
domReady=570&onload=990&jobLoad=2000&jobExec=210

现在每条http日志都存储到MySQL中了，查询字符串单独作为一列（varchar(500)的列），需要计算查询字符串中各个key的去掉最大/小值之后的平均值。

二、解决方案

其实SELECT语句能解决求Number类型列的去掉N个最大/最小值之后的平均值：

SELECT COUNT(*) INTO rowNum FROM tableName; -- 先算出总的行数
SET avgCount = rowCount - N * 2; -- 计算参与求平均值的行数
SELECT AVG(colname) INTO filtered_avg FROM (SELECT colname FROM tablename ORDER BY colname ASC LIMIT N, avgCount); -- 执行计算

上面就是出发点(上面最后一条是动态SQL，执行之前先拼接sql语句，Prepare之后再Execute)。只要把每个查询字符串分解成key,value之后，收集所有的key，然后创建一个临时表，包含名为各个key的列。然后每个查询字符串作为临时表中的一行，把value存储到key对应的列中，然后求各个key的列的平均值即可。

现在任务分解成下面几项：

收集所有的key
创建一张临时表，它的列名对应每个key
对每一个查询字符串，分解成<key，value>，把每个value存储到临时表的名为key的列中。
求临时表中每个key的去掉N个最大/小值之后的平均值。

三、关键细节与代码

计划使用MySQL存储过程来进行开发，然后在shell脚本中调用存储过程获取结果。

3.1 收集所有的key

MySQL存储过程提供子串查找SUBSTRING_INDEX和子串提取SUBSTRING函数可用来提取所有的Key。现在有个问题，提取到的key存储到哪里？在常规的编程语言中，非常容易想到数组，但是MySQL存储过程不支持数组！

其实MySQL最基本的数据结构就是关系表了。所以，提取到key存储到临时表（keyNameTable的keyName列）中。

思路是：先创建一张临时表存储key，列名为keyname,唯一键约束。在解析查询字符串时，每次分解得到一个key时，就拼接一个动态的REPLACE INTO语句执行(自动去重)。

下面贴具体代码：

DROP PROCEDURE IF EXISTS `extract_keys`;
DELIMITER //
CREATE PROCEDURE `extract_keys`(
    IN tableName VARCHAR(20), -- 表名称
    IN colName INT,   -- 列名称
    IN keyNameTable INT  -- 最终存储keyName的临时表表名
)     
BEGIN
    BEGIN
        -- 创建keyName表
        SET @createKeyNameTableSQL=CONCAT('CREATE TEMPORARY TABLE ', keyNameTable, '(keyName VARCHAR(50) PRIMARY KEY, averageVal DECIMAL(10,2))');
        PREPARE createKeyNameTableSTMT FROM @createKeyNameTableSQL;
        EXECUTE createKeyNameTableSTMT;
        DEALLOCATE PREPARE createKeyNameTableSTMT;  
    END;
    BEGIN
        -- 这里使用了一个临时表，因为MySQL不支持动态游标（游标声明时表名不能是变量）
        DROP TEMPORARY TABLE IF EXISTS temp_table_remaining;
        SET @createTempQueryStringTableSQL=CONCAT('CREATE TEMPORARY TABLE `temp_table_query_string` AS SELECT ', colName, ' AS `queryStr` FROM ', tableName);
        PREPARE createTempQueryStringTableSTMT FROM @createTempQueryStringTableSQL;
        EXECUTE createTempQueryStringTableSTMT;
        DEALLOCATE PREPARE createTempQueryStringTableSTMT;
    END;
    BEGIN
        DECLARE sep_field VARCHAR(10) DEFAULT '&';
        DECLARE sep_key   VARCHAR(10) DEFAULT '=';
        DECLARE queryStr  VARCHAR(500);
        DECLARE sTail     VARCHAR(500);
        DECLARE sHead     VARCHAR(500);
        DECLARE sep_field_len INT DEFAULT 0;
        DECLARE first_row INT DEFAULT 1;
        DECLARE last_row INT DEFAULT 0;
        -- 这里游标从临时表中取数据
        DECLARE getAllQueryStringCursor CURSOR FOR SELECT `queryStr` FROM `temp_table_query_string`;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET last_row = 1;
        --  变量、游标和异常处理声明完毕
        BEGIN
            -- 先准备好动态SQL
            SET @insertOrReplaceSQL:=CONCAT('REPLACE INTO `', keyNameTable, '` (keyName) VALUES(?)');
            PREPARE insertOrReplaceSTMT FROM @insertOrReplaceSQL;
            -- 开始读取
            OPEN getAllQueryStringCursor;
            queryStringLoop: LOOP
                FETCH getAllQueryStringCursor INTO queryStr;
                IF (last_row = 1) THEN
                    LEAVE queryStringLoop;
                END IF;
                SET sTail=queryStr;
                SET sep_field_len=LENGTH(sep_field);
                WHILE sTail != '' DO
                    SET sHead=SUBSTRING_INDEX(sTail, sep_field, 1);
                    SET sTail=SUBSTRING(sTail, LENGTH(sHead) + sep_field_len + 1);
                    SET @fieldName=SUBSTRING_INDEX(sHead, sep_key, 1);
                    EXECUTE insertOrReplaceSTMT USING @fieldName;
                END WHILE;              
            END LOOP queryStringLoop;
            DEALLOCATE PREPARE insertOrReplaceSTMT;
            CLOSE getAllQueryStringCursor;
        END;
    END;
END;
//
DELIMITER ;

3.2 创建包含所有key对应列的临时表keyValueTable

其实这一步也是靠读出上一步生成的临时表keyNameTable的keyName列的所有结果，拼接成一个动态的CREATE TABLE语句执行来实现。

下面贴具体代码：

    -- TODO
    -- TODO

3.3 分解每个查询字符串，将所有的value组成一行存储到临时表keyValueTable中

这一步也是拼接INSERT INTO keyValueTable(keyName1, keyName2) VALUES(value1),(value2)的SQL语句进行执行。

3.4 求临时表中各个列的去掉N个最大/小值之和的平均值

下面贴具体代码：

    -- TODO
    -- TODO

四、总结

先总结下MySQL存储过程开发中遇到的坑：

1. 不支持动态游标，表名不能是变量，需要借助临时表

2. 不支持在临时表上建立视图

3. MySQL函数中不能使用动态SQL语句，只有存储过程可以

4. MySQL触发器不能执行自身表的DML语句

未完待续。。。。

work 43