Оптимизировать - функция, которая выбирает из TEMP TABLE в цикле, чтобы получить средние значения JSON

Question

У меня есть функция Mysql, которая выполняется как часть более крупного запроса, считывающего несколько миллионов записей. Чтобы выявить аномалии, я вычисляю среднее изменение с течением времени. Данные в таблице хранятся в виде объектов JSON с метками времени UNIX в качестве ключа на срок до 30 дней.

Например, вход (input_array) будет выглядеть примерно так:

[{"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

В настоящее время около 2,7 миллионов записей занимает около 20 минут. Я ищу, чтобы оптимизировать это или переписать это, избегая издержек DROP/CREATE.

Answer 1

Кажется ненужным создавать таблицу только для вычисления среднего, это просто сделать в цикле. Вместо того, чтобы вставлять каждое значение в таблицу, добавьте его в общую переменную. В конце вернитесь 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;

Answer 2

Копание в миллион строк JSON. Я поражен, что это заняло всего 20 минут.

Вставляя строки, сделайте некоторые вычисления и сохраните результаты где-нибудь. Затем используйте это для мониторинга.

Даже если вы не можете сделать это, вставляя строки, делайте это только с "новыми" строками. Снова сохраните предыдущую информацию где-нибудь.

Что касается DROP/CREATE... Это может быть ускорено, имея постоянный стол, тогда используйте только TRUNCATE TABLE в начале каждого вызова процедуры.

(4) в INTEGER(4) ничего не значит. Вы всегда получите 32-разрядное целое число. (Эта заметка, вероятно, не влияет на процесс.)