根据更改时存储在MySQL中的数据计算平均值-Java 学习之路

我们有一个数据库，我们只存储数据值和时间戳（作为MySQL中的一行） when the data value changes. 因此，数据库中的时间戳之间没有固定的间隔 . 该表看起来像这样：

MySQLTimestamp    Data
2014-10-01 18:01  1
2014-10-03 16:13  2
2014-10-07 15:45  1
2014-10-09 10:08  3

THE PROBLEM: 我们想要计算平均值 over time ，假设数据值继续为2，直到值在数据库中的下一行上发生变化 .

一个简单的AVG不会起作用，因为它只计算行数之间的平均值 . 在下一行表示数据值发生变化之前，这不会计算值可以在很长一段时间内继续保持相同的值 .

真的很感谢你的帮助！

1 回答

自我加入并计算日期或时间的持续时间作为权重 Data .

select 
    sum(data*duration_of_date)/sum(duration_of_date) as avg_over_date,
    sum(data*duration_of_hour)/sum(duration_of_hour) as avg_over_hour,
    sum(data*duration_of_sec)/sum(duration_of_sec) as avg_over_sec
from (
    select 
        t1.MySQLTimestamp,
        t1.data,
        min(case when t1.MySQLTimestamp<t2.MySQLTimestamp 
                 then t2.MySQLTimestamp else null end) as next_tm,
        datediff( 
            min(case when t1.MySQLTimestamp<t2.MySQLTimestamp 
                     then t2.MySQLTimestamp else null end) , 
            t1.MySQLTimestamp) as duration_of_date,
        TIME_TO_SEC(timediff( 
            min(case when t1.MySQLTimestamp<t2.MySQLTimestamp 
                     then t2.MySQLTimestamp else null end) , 
            t1.MySQLTimestamp))/60/60 as duration_of_hour,
        TIME_TO_SEC(timediff( 
            min(case when t1.MySQLTimestamp<t2.MySQLTimestamp 
                     then t2.MySQLTimestamp else null end) , 
            t1.MySQLTimestamp)) as duration_of_sec
    from 
        your_table t1
    cross join 
        your_table t2
    group by 
        t1.MySQLTimestamp, 
        t1.data
    ) as t

使用 datediff 将天数间隔计算为重量 . 如果您想要小时或分钟作为间隔，您可以使用 timediff 并将结果传输到小时，分钟或秒 .

这是sql fiddle demo和结果：

AVG_OVER_DATE   AVG_OVER_HOUR   AVG_OVER_SEC
          1.5         1.51887         1.5189

左连接的另一个版本：

select 
    sum(data*duration_of_date)/sum(duration_of_date) as avg_over_date,
    sum(data*duration_of_hour)/sum(duration_of_hour) as avg_over_hour,
    sum(data*duration_of_sec)/sum(duration_of_sec) as avg_over_sec
from (
    select 
        t1.MySQLTimestamp,
        t1.data,
        min(t2.MySQLTimestamp) as next_tm,
        datediff(min(t2.MySQLTimestamp), t1.MySQLTimestamp) as duration_of_date,
        TIME_TO_SEC(timediff(min(t2.MySQLTimestamp), t1.MySQLTimestamp))/60/60 as duration_of_hour,
        TIME_TO_SEC(timediff(min(t2.MySQLTimestamp), t1.MySQLTimestamp)) as duration_of_sec
    from 
        your_table t1
    left join 
        your_table t2
    on 
        t1.MySQLTimestamp<t2.MySQLTimestamp
    group by 
        t1.MySQLTimestamp, 
        t1.data
    ) as t

回复于 2024-04-20T12:38:23+08:00

根据更改时存储在MySQL中的数据计算平均值

1 回答

相关问题