mysql - 如何在按小时分组时获得三小时滚动平均值？

Question

我有一张电话表。它具有以下几列:start_time、type、duration。

我有一个查询，用于获取每小时的平均通话时间:

SELECT
    CONCAT(FROM_UNIXTIME(UNIX_TIMESTAMP(t1.start_time), '%Y-%m-%d %H'), ':00') as h,
    t1.type,
    AVG(t1.duration)
FROM
    phone_calls t1

WHERE
    t1.start_time > '2015-09-02 00:00'
    AND t1.duration is not null
GROUP BY
    t1.type,
    h
ORDER BY
    h asc

我不想获取一小时的平均值，而是想获取过去三个小时的每小时平均值。

我尝试了这个，但没有成功:

SELECT
    h,
    type,
    (

        SELECT
            dur_sum * dur_count / SUM(dur_count) rolling_avg
        FROM(
            SELECT
                CONCAT(FROM_UNIXTIME(UNIX_TIMESTAMP(t1.start_time), '%Y-%m-%d %H'), ':00') as h,
                t1.type,
                SUM(t1.duration),
                COUNT(t1.duration)
            FROM
                phone_calls t1

            WHERE
                t1.start_time > '2015-09-02 00:00'
                AND t1.duration is not null
            GROUP BY
                t1.type,
                h
            ORDER BY
                h asc
        ) as q1
        WHERE
            h BETWEEN DATE_SUB(h, INTERVAL 3 HOUR) AND h
    ) as rolling
FROM(
    SELECT
        CONCAT(FROM_UNIXTIME(UNIX_TIMESTAMP(t1.start_time), '%Y-%m-%d %H'), ':00') as h,
        t1.type,
        SUM(t1.duration),
        COUNT(t1.duration)
    FROM
        phone_calls t1

    WHERE
        t1.start_time > '2015-09-02 00:00'
        AND t1.duration is not null
    GROUP BY
        t1.type,
        h
    ORDER BY
        h asc
) as q2

该查询为我的所有行的滚动列提供了相同的值。我如何更改我的查询以获得我正在寻找的内容？谢谢!

编辑:

这是我的第一个查询返回的内容:

h                 , type ,t_avg    
"2015-09-02 00:00", 2    ,773.5000    
"2015-09-02 00:00", 3    ,246.7966  
"2015-09-02 00:00", 1    ,377.4337  
"2015-09-02 01:00", 1    ,258.7692  
"2015-09-02 01:00", 3    ,188.9737  
"2015-09-02 02:00", 3    ,144.6471  
"2015-09-02 02:00", 1    ,254.4400  
"2015-09-02 03:00", 1    ,67.9048  
"2015-09-02 03:00", 3    ,88.9333  

等等

以下是表本身的一些示例数据:

start_time            ,type ,duration  
"2015-09-02 00:00:11" ,3    ,174  
"2015-09-02 00:00:15" ,1    ,1088  
"2015-09-02 00:00:27" ,1    ,23  
"2015-09-02 00:00:43" ,3    ,125  
"2015-09-02 00:00:52" ,1    ,31  
"2015-09-02 00:01:05" ,3    ,21  
"2015-09-02 00:01:16" ,1    ,43  
"2015-09-02 00:01:40" ,1    ,88  
"2015-09-02 00:02:17" ,1    ,117  
"2015-09-02 00:04:06" ,1    ,22  
"2015-09-02 00:04:13" ,1    ,46  
"2015-09-02 00:04:15" ,1    ,116  
"2015-09-02 00:04:29" ,3    ,57  
"2015-09-02 00:04:44" ,3    ,728  
"2015-09-02 00:04:57" ,3    ,132   

这就是我想要从数据中得到的:

h                 , type ,rolling_avg    
"2015-09-02 00:00", 2    ,<average duration for past three hours for type 2>     
"2015-09-02 00:00", 3    ,<average duration for past three hours for type 3>  
"2015-09-02 00:00", 1    ,<average duration for past three hours for type 1>  
"2015-09-02 01:00", 1    ,<average duration for past three hours for type 1>  
"2015-09-02 01:00", 3    ,<average duration for past three hours for type 3>  
"2015-09-02 02:00", 3    ,<average duration for past three hours for type 3>  
"2015-09-02 02:00", 1    ,<average duration for past three hours for type 1>  
"2015-09-02 03:00", 1    ,<average duration for past three hours for type 1>  
"2015-09-02 03:00", 3    ,<average duration for past three hours for type 3>  

Answer 1

我会给你一个简单的方法。因为没有架构。

如果已经有每小时平均值，只需添加 count 字段

SELECT
    CONCAT(FROM_UNIXTIME(UNIX_TIMESTAMP(t1.start_time), '%Y-%m-%d %H'), ':00') as h,
    t1.type,
    AVG(t1.duration) t_avg,
    COUNT(t1.duration) t_count

添加限制和排序依据

ORDER BY h
LIMIT 3

然后通过该查询

SELECT SUM( t_avg * t_count ) / SUM( t_count )
FROM <previous query>