optimize—从循环内的temp表中进行选择以获得json值的平均值的函数

dfddblmv 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(216)

我有一个mysql函数，它作为读取几百万条记录的更大查询的一部分运行。为了检测异常，我计算出了平均变化时间。表中的数据以json对象的形式存储，其中unix时间戳作为键，最长可存储30天。
例如，输入（input\数组）类似于：

[{"1532944806": 16}, {"1533031206": 14}, {"1533117605": 13}, {"1533204305": 12}, {"1533290708": 10}, {"1533463506": 9}, {"1533549907": 9}, {"1533636306": 9}, {"1533722707": 9}, {"1533809108": 9}, {"1533895506": 9}, {"1533981906": 8}, {"1534068306": 7}, {"1534154706": 7}, {"1534241108": 7}, {"1534590304": 7}, {"1534673106": 12}, {"1534759508": 6}, {"1534845905": 7}, {"1534932306": 7}, {"1535018707": 5}, {"1535105106": 3}, {"1535191505": 7}, {"1535277907": 6}, {"1535364305": 7}, {"1535450706": 2}, {"1535537107": 1}]

我只看平均下降的变化-没有任何变化，增加了一天。
我正在检查前一天的值是否存在，如果存在，我将计算更改并将其添加到一个临时表中，在该表中查询以选择平均值。
到目前为止，我已经：

CREATE FUNCTION `daily_averages`(input_array JSON) RETURNS int(4)
READS SQL DATA
DETERMINISTIC
BEGIN

    DECLARE array_length INTEGER(2);
    DECLARE prev_value INTEGER(4);
    DECLARE idx INTEGER(4);

    DROP TEMPORARY TABLE IF EXISTS collection;
    CREATE TEMPORARY TABLE collection (change INTEGER(4) SIGNED DEFAULT 0);

    SELECT JSON_LENGTH(input_array) INTO array_length;

    SET idx = 0;

    WHILE idx < array_length DO

        SELECT 

        IF(idx-1 > -1,
            CONVERT(
                JSON_EXTRACT(
                    JSON_EXTRACT(
                        JSON_EXTRACT( input_array, CONCAT( '$[', idx-1, ']' ) )
                        , '$.*'
                    )
                    , '$[0]'
                ), SIGNED INTEGER
            ) 
            , -1
        )

        INTO prev_value;

        INSERT INTO collection
        SELECT (prev_value - 
            (
                CONVERT(
                    JSON_EXTRACT(
                        JSON_EXTRACT(
                            JSON_EXTRACT( input_array, CONCAT( '$[', idx, ']' ) )
                            , '$.*'
                        )
                        , '$[0]'
                    ), SIGNED INTEGER
                )
            )
       )
       FROM DUAL
       WHERE prev_value > 0;

       SET idx = idx + 1;

    END WHILE;

    RETURN (SELECT AVG(change) FROM collection WHERE change > -1);

END

目前大约有270万条记录，运行大约需要20分钟。我希望通过避免drop/create开销来优化或重写它。

mysql JSON query-performance query-optimization

来源：https://stackoverflow.com/questions/52087885/optimize-function-that-selects-from-temp-table-within-loop-to-get-averages-of

2条答案

按热度按时间

q9yhzks01#

挖掘一百万个json字符串。我很惊讶只用了20分钟。
插入行时，进行一些计算并将结果存储在某个位置。然后用它来监视。
即使不能在插入行时执行，也只能对“新”行执行。再次保存以前的信息。
至于删除/创建。。。这可以通过有一个永久表来加速，然后只使用 TRUNCATE TABLE 在每个进程调用的开始。
这个 (4) 在 INTEGER(4) 没有任何意义。您将始终得到一个32位整数(此注解可能对过程没有影响。）

赞(0）回复(0）举报 2021-06-21

wvmv3b1j2#

仅仅为了计算平均值而创建一个表似乎是不必要的，在循环中这样做很简单。与其将每个值插入表中，不如将其添加到总变量中。最后，返回 total/count .
既然你要计算价值观之间的差异，
你也可以使用 SET 语句来分配变量，而不是 SELECT ... INTO variable .

DECLARE array_length INTEGER(2);
DECLARE prev_value INTEGER(4);
DECLARE idx INTEGER(4);
DECLARE total INTEGER(4);
DECLARE counter INTEGER(4);
DECLARE cur_value INTEGER(4);

SET array_length = JSON_LENGTH(input_array);
SET total = 0;
SET counter = 0;
-- Initialize prev_value to the first element
SET prev_value = CONVERT(
                JSON_EXTRACT(
                    JSON_EXTRACT(
                        JSON_EXTRACT( input_array, '$[0]' )
                        , '$.*'
                    )
                    , '$[0]'
                ), SIGNED INTEGER
            );
SET idx = 1;

WHILE idx < array_length DO
    SET cur_value = CONVERT(
                JSON_EXTRACT(
                    JSON_EXTRACT(
                        JSON_EXTRACT( input_array, CONCAT( '$[', idx, ']' ) )
                        , '$.*'
                    )
                    , '$[0]'
                ), SIGNED INTEGER
            );
    IF cur_value < prev_value
    THEN
        SET total = total + (prev_value - cur_value);
        SET counter = counter + 1;
    END IF;
    SET prev_value = cur_value;
    SET idx = idx + 1;
END WHILE;

RETURN total / counter;

赞(0）回复(0）举报 2021-06-20

我来回答

optimize—从循环内的temp表中进行选择以获得json值的平均值的函数

2条答案

相关问题

热门标签

最新问答