-- 用于统计终端每天的活跃度、开机时长占比
delimiter $$ 
create procedure client_count(IN p_sdat INT, IN p_edat INT, IN p_launcher varchar(60) ) 
  begin
    declare client_total int ; 
    declare p_active int ; 
    declare p_30 int ; 
    declare p_60 int ; 
    declare p_90 int ; 
    declare p_120 int ; 
    declare p_120more int ;
    declare p_180more int ;
    declare p_240more int ; 
    -- declare cnt int ; 

    set @cnt=p_sdat ; 
    -- 创建一个临时表
    drop table if exists tmp_client_count; 
    create temporary table tmp_client_count(
      id int primary key auto_increment, 
      dat  int, 
      total  int,
      active int,
      pct_active decimal(3,2),
      pct_30min decimal(3,2),
      pct_60min decimal(3,2),
      pct_90min decimal(3,2),
      pct_120min decimal(3,2),
      pct_120more decimal(3,2),
      pct_180more decimal(3,2), 
      pct_240more decimal(3,2) ); 
      
    while @cnt<=p_edat do
      if char_length(p_launcher)=0 then 
        select count(id) into client_total from terminals where unix_timestamp(created_at) <= unix_timestamp(concat(substr(@cnt,1,4),'-',substr(@cnt,5,2),'-',substr(@cnt,7,2),' 23:59:59' ) ) ; 
        select count(distinct(mac)) into p_active  from  heartbeats_new where day=@cnt ; 
        select count(*) into p_30 from (select mac from heartbeats_new where day=@cnt group by mac having max(created_at)-min(created_at)>=0 and max(created_at)-min(created_at)<1800) p30 ; 
        select count(*) into p_60 from (select mac from heartbeats_new where day=@cnt group by mac having max(created_at)-min(created_at)>=1800 and max(created_at)-min(created_at)<3600) p60 ; 
        select count(*) into p_90 from (select mac from heartbeats_new where day=@cnt group by mac having max(created_at)-min(created_at)>=3600 and max(created_at)-min(created_at)<5400) p90 ; 
        select count(*) into p_120 from (select mac from heartbeats_new where day=@cnt group by mac having max(created_at)-min(created_at)>=5400 and max(created_at)-min(created_at)<7200) p120 ; 
        select count(*) into p_120more from (select mac from heartbeats_new where day=@cnt group by mac having max(created_at)-min(created_at)>=7200 ) p120more ; 
        select count(*) into p_180more from (select mac from heartbeats_new where day=@cnt group by mac having max(created_at)-min(created_at)>=10800 ) p180more ; 
        select count(*) into p_240more from (select mac from heartbeats_new where day=@cnt group by mac having max(created_at)-min(created_at)>=14400 ) p240more ; 
      else
        select count(id) into client_total from terminals where unix_timestamp(created_at) <= unix_timestamp(concat(substr(@cnt,1,4),'-',substr(@cnt,5,2),'-',substr(@cnt,7,2),' 23:59:59' ) ) and launcher like concat('%',p_launcher,'%') ; 
        select count(distinct(mac)) into p_active  from  heartbeats_new where day=@cnt  and launcher like concat('%',p_launcher,'%') ; 
        select count(*) into p_30 from (select mac from heartbeats_new where day=@cnt and launcher like concat('%',p_launcher,'%') group by mac having max(created_at)-min(created_at)>=0 and max(created_at)-min(created_at)<1800) p30 ; 
        select count(*) into p_60 from (select mac from heartbeats_new where day=@cnt and launcher like concat('%',p_launcher,'%') group by mac having max(created_at)-min(created_at)>=1800 and max(created_at)-min(created_at)<3600) p60 ; 
        select count(*) into p_90 from (select mac from heartbeats_new where day=@cnt and launcher like concat('%',p_launcher,'%') group by mac having max(created_at)-min(created_at)>=3600 and max(created_at)-min(created_at)<5400) p90 ; 
        select count(*) into p_120 from (select mac from heartbeats_new where day=@cnt and launcher like concat('%',p_launcher,'%')  group by mac having max(created_at)-min(created_at)>=5400 and max(created_at)-min(created_at)<7200) p120 ; 
        select count(*) into p_120more from (select mac from heartbeats_new where day=@cnt and launcher like concat('%',p_launcher,'%')  group by mac having max(created_at)-min(created_at)>=7200 ) p120more ;
        select count(*) into p_180more from (select mac from heartbeats_new where day=@cnt and launcher like concat('%',p_launcher,'%')  group by mac having max(created_at)-min(created_at)>=10800 ) p180more ;
        select count(*) into p_240more from (select mac from heartbeats_new where day=@cnt and launcher like concat('%',p_launcher,'%')  group by mac having max(created_at)-min(created_at)>=14400 ) p240more ;
      end if; 

      insert into tmp_client_count(dat, total, active,pct_active,pct_30min,pct_60min,pct_90min,pct_120min,pct_120more,pct_180more,pct_240more) values(@cnt, client_total, p_active, p_active/client_total,p_30/p_active,p_60/p_active,p_90/p_active,p_120/p_active,p_120more/p_active,p_180more/p_active,p_240more/p_active ) ; 
      
      set @cnt=@cnt+1 ; 
    end while; 

    select * from tmp_client_count ; 
  end
$$
delimiter ; 

	

 

-- 列出某个月机器增长的酒店榜单

delimiter $$
create procedure topHotel(in p_month varchar(30), in p_launcher varchar(30), in p_limit int ) 
begin
   select hotel_id,count(*) as nums from terminals where created_at like concat(p_month,'%') and launcher like concat('%',p_launcher,'%') group by hotel_id order by nums desc limit p_limit; 
end 
$$
delimiter ;