将采集的声级数据存储到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;
网友评论