mysql - 计算滑动平均值

Question

我在mysql中有一个DataTime的timestamp列，还有个rtd的列存储的是float类型数据
DataTime每小时有一条，我需要计算每个小时的24个小时滑动平均值，比如
2号0点的数据是 1号1点到2号0点的24条数据做平均
2号1点的数据是 1号2点到2号1点的24条数据做平均
。。。。。。
以此类推

有办法用一条语句来实现么？
我只知道用循环取24次肯定是可以实现，有办法用一条SQL实现么？

Answer 1

Time series data is not suitable for operations in SQL. You should consider doing the math in a program.

Answer 2

First normal form in database design: If all attributes of a relational schema R are indivisible basic data items, then R∈1NF. The data in your model is related to the current time, which obviously does not conform to the first normal form. It is not recommended to store it in the database. It is recommended to write it into Memcache and then use Cron job to update the value.

If you must use a database to implement it, you can also find the average without looping.

SELECT
    avg(val) as avgVal
FROM
    `table`
WHERE
    `DataTime` < unix_timestamp('2013-12-02 02:00:00')
ORDER BY
    `DataTime` DESC
LIMIT
    twenty four;

What is taken out is the average value of the past 24 hours at 2013-12-02 02:00:00.