mysql - 获取每N组的平均值

Question

我看到这里有些人遇到了和我现在遇到的同样的问题......但没有任何帮助:/

我有一个包含 2 列的表测试。一个日期和一个整数值。我需要每 N 条记录的平均值。例如

Date        | Info
2013-01-01  |  210
2013-01-02  |  213
2013-01-03  |  214
2013-01-05  |  197
2013-01-06  |  192

....

假设 N = 3

我需要填充一个表

Averages
212.3333 (that's the average of the 3 first Info's) (210+213+214)/3
208.0000 (213+214+197)/3
201.0000 (214+197+192)/3

我试过了

delimiter $$
DROP PROCEDURE IF EXISTS calcAvg$$
CREATE PROCEDURE calcAvg()
begin

DECLARE v_finished INTEGER DEFAULT 0;
declare v_avg DECIMAL default 0.0;
DECLARE initial_date date;

DEClARE cr CURSOR FOR 
    select avg(i) from  (
        select t.info i
           from test t
          where date >= initial_date
            limit 3
        )as s;

-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

set initial_date = '2013-01-01';

get_avg: LOOP

    OPEN cr;

    cr_loop: LOOP

        FETCH cr INTO v_avg;

        IF v_finished = 1 THEN 
            LEAVE cr_loop;
        END IF;

        insert into result values (v_avg);

    END LOOP cr_loop;

    CLOSE cr;

    #I've to change this line... the table does not have all days
    set v_dt_ini = date_add(v_dt_ini,interval 1 DAY);
    #But the problem it's not the line yet...
    set v_finished = 0;

    if v_dt_ini > '2013-05-30' then
        leave get_avg;
    end if;

END LOOP get_avg;

end$$

在代码中我将 N 固定为 3问题是，当我调用该过程时...它计算 3 条记录的第一个平均值...当再次执行游标时...它返回相同的值...前 3 条记录...但它不是我需要什么...

光标没有改变结果:/

Answer 1

SQLFiddle

SELECT date,info,(select avg(info) from
                  (select date,info,@row1:=@row1+1 as row
                   from test,(SELECT @row1:=0)r
                   order by date desc
                  )wrn1
                  where wrn1.row between wrn2.start and wrn2.end)as avg,
       start,end

FROM
    (select date,info,@row:=@row+1 as row,(@row - 2) as start,@row as end
     from test,(SELECT @row:=0)r
     order by date desc
    )wrn2
WHERE start > 0
ORDER BY date asc

这将返回

        DATE                   INFO     AVG       START     END
January, 01 2013 00:00:00+0000     210  212.3333    3        5
January, 02 2013 00:00:00+0000     213  208         2        4
January, 03 2013 00:00:00+0000     214  201         1        3