等效声级的数据采集和计算，累计百分声级计算

将采集的声级数据存储到VALUESA 字段

/*

  等效声级的计算

( 8 * 60 )  表示要计算等效声级的间隔是多少， 8*60表示8小时转换成分钟后，每分钟等效声级

SUM( POWER( 10, VALUESA / 10 ))  VALUESA 是采集的声级样本数据字段

where 条件为要计算多长时间的等效声级，示例是以1200秒内，即20min的等效声级

*/

SELECT

10 * LOG10( 1 / ( 8 * 60 ) * SUM( POWER( 10, VALUESA / 10 )) )

FROM

sound_levels

WHERE

TIMESTAMP >= DATE_SUB( NOW(), INTERVAL 1200 SECOND );

/*

等效百分声级

*/

SELECT L50

FROM (

    SELECT valuesa AS L50,

          @running_total := @running_total + time_diff AS cumulative_time,

          @total_time := (SELECT SUM(time_diff) FROM (

                SELECT TIMESTAMPDIFF(SECOND, t1.sj, t2.sj) AS time_diff

                FROM sound_levels t1

                JOIN sound_levels t2 ON t2.sj > t1.sj

            --    WHERE t1.sj >= DATE_SUB(NOW(), INTERVAL 1 HOUR)

                ORDER BY t1.valuesa ASC

            ) AS time_diff_table) AS total_time

    FROM (

        SELECT t1.sj, t1.valuesa,

            TIMESTAMPDIFF(SECOND, t1.sj, t2.sj) AS time_diff

        FROM sound_levels t1

        JOIN sound_levels t2 ON t2.sj > t1.sj

    --    WHERE t1.sj >= DATE_SUB(NOW(), INTERVAL 1 HOUR)

        ORDER BY t1.valuesa ASC

    ) AS t,

    (SELECT @running_total := 0, @total_time := 0) AS vars

    HAVING cumulative_time >= total_time * 0.5

    LIMIT 1

) AS result;