MySQL 시계열은 window 함수를 사용하여 평균 무게를 측정했습니다.

MySQL 시계열은 window 함수를 사용하여 평균 무게를 측정했습니다.

간단한 것이 있습니다.data시계열 데이터에 대한 테이블, 기본적으로 타임스탬프와 값만:

Field, Type, Null, Key, Default, Extra
'id','int(11)','NO','PRI',NULL,'auto_increment'
'channel_id','int(11)','YES','MUL',NULL,''
'timestamp','bigint(20)','NO','',NULL,''
'value','double','NO','',NULL,''

저는 기간별 가중치 평균을 계산하기 위한 다소 정교한 쿼리를 개발했습니다. 기본적으로 계산합니다.sum(val x delta time).그@prev_timestamp변수는 기본적으로 다음을 시뮬레이션합니다.LAG()기능.예:

SELECT 
    MAX(agg.timestamp) AS timestamp, 
    COALESCE( 
        SUM(agg.val_by_time) / (MAX(agg.timestamp) - MIN(agg.prev_timestamp)), 
        AVG(agg.value)
    ) AS value
FROM ( 
    SELECT 
        timestamp, 
        value, 
        value * (timestamp - @prev_timestamp) AS val_by_time, 
        COALESCE(@prev_timestamp, 0) AS prev_timestamp, 
        @prev_timestamp := timestamp 
    FROM data 
    CROSS JOIN (
        SELECT @prev_timestamp := NULL
    ) AS vars 
    WHERE channel_id=56  AND timestamp >= 1546297161097 AND timestamp <= 1552950000000 
    ORDER BY timestamp ASC
) AS agg 
GROUP BY (timestamp - 1546288811393) >> 23 
ORDER BY timestamp ASC
;

최근에, 저는 이 쿼리를 hacky MySQL 변수 접근 방식에서 윈도우 기능을 사용하도록 변환했습니다.

SELECT 
    MAX(agg.timestamp) AS timestamp, 
    COALESCE(
        SUM(agg.val_by_time) / (MAX(agg.timestamp) - MIN(agg.prev_timestamp)), 
        AVG(agg.value)
    ) AS value
FROM ( 
    SELECT 
        timestamp, 
        value, 
        LAG(timestamp) OVER(ORDER BY channel_id, timestamp ASC) AS prev_timestamp,
        value * (timestamp - LAG(timestamp) OVER(ORDER BY channel_id,timestamp ASC)) AS val_by_time
    FROM data 
    WHERE channel_id=56  
    AND timestamp >= 1546297161097 AND timestamp <= 1552950000000 
    ORDER BY timestamp ASC

) AS agg 
GROUP BY (timestamp - 1546288811393) >> 23 
ORDER BY timestamp ASC
;

단점은 창 기능을 사용하는 버전이 분명히 더 깨끗하지만 실행 속도가 약 25% 느리다는 것입니다(MariaDB 10.3, Raspi 3).

창 기능을 더 잘 활용하여 파생된 표를 제거하여 이 쿼리를 작성하는 더 나은(더 성능이 뛰어난) 방법이 있습니까?

언급URL : https://stackoverflow.com/questions/55221264/mysql-timeseries-weighed-average-using-window-functions