mysql - 聚合具有不规则时间戳的 SQL 函数

Question

我有一个包含时间戳和河流流量的表。有时我有多个记录，但有时我没有记录。

如何计算两个日期之间的平均流量和总流量？

假设两点之间的线性值是可以接受的。也许是某种加权平均值。如果有一些最小二乘回归算法或类似的算法可以提供更准确的结果，那就太好了。

编辑。对于某一天，我有以下虚构数据用于说明目的。如果可能的话，我希望做得比假设平均值为 146 更好，因为流量在较长时间内较高，而实际平均值可能超过 200。

10/12/15 12:00 AM   100
10/12/15 12:01 AM   102
10/12/15 12:02 AM   104
10/12/15 12:03 AM   106
10/12/15 12:04 AM   200
10/12/15 10:00 PM   204
10/12/15 11:00 PM   208

Average             146

Answer 1

您需要进行加权平均。为此，您需要下一个时间戳:

select rf.*,
       (select rf2.timestamp
        from riverflow rf2
        where rf2.timestamp > rf.timestamp
        order by rf.timestamp asc
        limit 1
       ) as nextTimestamp
from riverflow rf;

接下来是加权平均值。我不知道你想如何处理测量周期可能与给定日期不一致的问题。相反，我们将只获取值并报告开始和结束观察时间:

select min(timestamp) as start, max(timestamp) as end,
       (sum(riverflow * timestampdiff(second, timestamp, nexttimestamp) / (24*60*60)) /
        (timestampdiff(second, min(timestamp), max(timestamp)) / (24*60*60)
       ) as avgRiverflow
from (select rf.*,
             (select rf2.timestamp
              from riverflow rf2
              where rf2.timestamp > rf.timestamp
              order by rf2.timestamp asc
              limit 1
             ) as nextTimestamp
      from riverflow rf
      where timestamp >= $date1 and timestamp < $date2
     ) t;