Moving average

I was trying to create a moving average.

Here's the sample data:

create temporary table if not exists window (id int);

delete from window;

insert into window(id)
select 1 union
select 2 union
select 3 union
select 4 union
select 15 union
select 6 union
select 7 union
select 8 union
select 9;

So first I came up with one that works with a fixed windows size (ex: 3):

set @summ:=@last3:=@last2:=@last1:=0;

select concat(a,',',b,',',c) w, summ/3 average
from (
	select 
		@summ:=@summ+id-@last3 summ, 
		@last3:=@last2 a, 
		@last2:=@last1 b, 
		@last1:=id c
	from window
) a
limit 2, 18446744073709551615;

I don't know if I'm doing this all wrong, but I think that's better than doing three self-joins. Well if that's a terrible hack, then this next one is a disaster.

The next challenge was to extend this for the general case:

set @windowsize = 5;
set @waiting = @windowsize;
set @trail = '';
set @summ = 0;

select w, summ/@windowsize average
from (
	select 
		(
			case when @waiting>=0 then @waiting:=@waiting-1
			end
		),
		(
			case when @waiting>=0 then @summ:=@summ+id
			else @summ:=@summ+id-cast(substring_index(@trail, ',', 1) as decimal)
			end
		) summ,
		(
			case when @waiting>=0 then @trail:=concat(@trail,id,',') 
			else @trail:=concat(substring_index(@trail, ',' , -@windowsize), id, ',')
			end
		) w
	from window
) inn
limit 4, #should actually be @window-1; or use no limit and ignore the first @window-1 rows
18446744073709551615;

:icon_eek:
(At least it works)

I'm open to your ideas!

Thanks,
Thomas