stmtStatistics1

set @bandwidth:=2;
set @limitnum:=15;

select @bandwidth:=2 as bandwidth,@limitnum:=15 as limitnum;

Drop View v;
CREATE VIEW v AS
select * from data as a
where a.sensorid='0014.4F01.0000.722E'
ORDER BY counter DESC
LIMIT @limitnum;


#select sensorid,max(timestamp) from data as a
#where a.sensorid='0014.4F01.0000.722E';



select * from v_s1;

select
count(*) as cont,
@sensorid:=sensorid as sensorid,
@lasttimestamp:=max(timestamp) as lasttimestamp,
@lasttimer:=max(timer) as lasttimer,
@lightavg:=avg(light) as lightavg,
@lightstd:=std(light) as lightstd,
@axavg:=avg(ax) as axavg,
@axstd:=std(ax) as axstd,
@ayavg:=avg(ay) as ayavg,
@aystd:=std(ay) as aystd,
@azavg:=avg(az) as azavg,
@azstd:=std(az) as azstd,
@movementavg:=avg(movement) as movementavg,
@movementstd:=std(movement) as movementstd,
@tempavg:=avg(temp) as tempavg,
@tempstd:=std(temp) as tempstd
from v;


select
@sensorid as sensorid,
@lasttimestamp as lasttimestamp,
@lasttimer as lasttimer,
(@lightavg-@bandwidth*@lightstd) as light_low_band,
@lightavg as lightavg,
(@lightavg+@bandwidth*@lightstd) as light_high_band,
(@movementavg-@bandwidth*@movementstd) as movement_low_band,
 @movementavg as movementavg,
(@movementavg+@bandwidth*@movementstd) as movement_high_band,
(@tempavg-@bandwidth*@tempstd) as temp_low_band,
@tempavg as tempavg,
(@tempavg+@bandwidth*@tempstd) as temp_high_band;


##counter bigint(20) NOT NULL ,//auto_increment
#PRIMARY KEY  (scounter));

drop table statistics;

create table statistics (
sensorid char(20) NOT NULL,
lattimestamp timestamp NOT NULL default CURRENT_TIMESTAMP,
lattimer long NOT NULL ,
light_low_band double,
lightavg double,
light_high_band double,
movement_low_band double,
movementavg double,
movement_high_band double,
temp_low_band double,
tempavg double,
temp_high_band double);


describe statistics;

insert into statistics
select
@sensorid as sensorid,
@lasttimestamp as lasttimestamp,
@lasttimer as lasttimer,
(@lightavg-@bandwidth*@lightstd) as light_low_band,
@lightavg as lightavg,
(@lightavg+@bandwidth*@lightstd) as light_high_band,
(@movementavg-@bandwidth*@movementstd) as movement_low_band,
 @movementavg as movementavg,
(@movementavg+@bandwidth*@movementstd) as movement_high_band,
(@tempavg-@bandwidth*@tempstd) as temp_low_band,
@tempavg as tempavg,
(@tempavg+@bandwidth*@tempstd) as temp_high_band;

select * from statistics;
