=====数据库表=====

[[generated:lb_battle_power_info]]	战力排行 **定时执行mysql事件刷新**。

[[generated:lb_stage_info]]	推图排行 **EndBattle 实时排序**。

[[generated:lb_ladder_info]]	天梯排行 **定时执行mysql事件刷新**。

[[generated:lb_tower_info]]	通天塔 **EndBattle 实时排序**。

[[generated:lb_guild_info]]	公会排行 **定时执行mysql事件刷新**。

每个数据库 __**仅存100条记录**__ 

=====协议=====

[[generated:GetTopLeaderBoard]] 	取得全部排行榜名次	#排行榜#

    返回不超过100条数据

[[generated:GetMyLeaderBoard]] 	取得我的排行榜名次	#排行榜#

    返回一条数据
=====新增客户端表=====
    
====today_leader_board_info(今日排名信息)====

[[generated:today_leader_board_info]]



    暂定使用redis存储，存活周期4小时。
    
    redis key：today_leader_board_info_[character_id]。
    
    刷新排名时机：key 不存在

    
    
====character_statistic_info 新增字段 ====

^字段名^描述^类型^默认值^
|**battle_power**|历史最高战力|int|0|
|**ladder_score**|历史最高天梯积分|int|0|

<del>====character_info 新增字段 ====</del>

<del>^字段名^描述^类型^默认值^
|**ladder_score**|本场天梯积分|int|0|</del>


==== character_percentage_rank_sampling_info 角色百分比排名采样表 ====

[[generated:character_percentage_rank_sampling_info]] 

//或许可以做原型表//


    静态数据
    TODO 采样规则：每隔一段时间，根据last_online逆序抽取1000或更多。

==== 公会排名 ====

    //TODO
    
==== 天梯排名 ====

    //TODO
    
==== 通天塔排名 ====

    //TODO
    
==== Mysql存储过程，定时事件 ====

**refresh_leader_board** 刷新lb_*_info ，**同名**定时事件每一小时执行一次

<code mysql>
BEGIN
	#Routine body goes here...
	#最高战力
  #lb_battle_power_info
	truncate TABLE lb_battle_power_info;
	alter table lb_battle_power_info AUTO_INCREMENT=1;
	###################################################
  #using character_statistic_info.battle_power TODO
	###################################################
	insert into `lb_battle_power_info`(avatar_url,character_id,`name`,career,battle_power,`level`,stage,tower,ladder_level,ladder_score)
	select character_info.avart,character_info.id,character_info.`name`,character_info.career,character_statistic_info.battle_power,character_info.`level`,character_statistic_info.stage_progress,
				 0,0,0 from character_info,character_statistic_info where character_info.id = character_statistic_info.id and character_statistic_info.battle_power >1 ORDER BY character_statistic_info.battle_power desc limit 100;

	update lb_battle_power_info set lb_battle_power_info.`order` = lb_battle_power_info.id;




	#主线推图：推到第几关
	#lb_stage_info
	truncate TABLE lb_stage_info;
	alter table lb_stage_info AUTO_INCREMENT=1;
	insert into `lb_stage_info`(avatar_url,character_id,`name`,career,battle_power,`level`,stage,tower,ladder_level,ladder_score)
  select character_info.avart,character_info.id,character_info.`name`,character_info.career,character_info.battle_power,character_info.`level`,character_statistic_info.stage_progress,
				 0,0,0 from character_info,character_statistic_info where character_info.id = character_statistic_info.id ORDER BY character_statistic_info.stage_progress%10000 desc limit 100;
	update lb_stage_info set lb_stage_info.`order` = lb_stage_info.id;





	#王者掠夺：掠夺天梯王者组
	#lb_ladder_info
	truncate TABLE lb_ladder_info;
	alter table lb_ladder_info AUTO_INCREMENT=1;
	/*insert into `lb_ladder_info`(avatar_url,character_id,`name`,career,battle_power,`level`,stage,tower,ladder_level,ladder_score)
  select character_info.avart,character_info.id,character_info.`name`,character_info.career,character_info.battle_power,character_info.`level`,character_statistic_info.stage_progress,
				 0,0,0 from character_info,character_statistic_info where character_info.id = character_statistic_info.id ORDER BY character_statistic_info.stage_progress%10000 desc limit 100;

	update lb_ladder_info set lb_ladder_info.`order` = lb_stage_info.id;*/

	
	#最强公会：按公会战排名，或者公会等级，或者公会总战力
	#lb_guild_info
	#TODO
	truncate TABLE lb_guild_info;
	alter table lb_guild_info AUTO_INCREMENT=1;


	#通天塔：看具体设计
	#lb_tower_info
	#TODO
	truncate TABLE lb_tower_info;
	alter table lb_tower_info AUTO_INCREMENT=1;



END
</code>

**refresh_character_rank** 刷新角色当前排名 

<code mysql>
BEGIN
	declare _battle_power INT DEFAULT(0);
	declare _stage_progress INT DEFAULT(0);
	declare _ladder_score INT DEFAULT(0);
	declare _tower INT DEFAULT(0);
	declare _guild_battle_power INT DEFAULT(0);
	declare _battle_power_rank float(6,2) DEFAULT(0.00);
	declare _stage_progress_rank float(6,2) DEFAULT(0.00);
	declare _ladder_rank float(6,2) DEFAULT(0.00);
	declare _tower_rank float(6,2) DEFAULT(0.00);
	declare _guild_battle_power_rank float(6,2) DEFAULT(0.00);
	
  declare _max_character INT DEFAULT(0);
  declare _max_guild INT DEFAULT(0);

	#query current status
	select character_statistic_info.battle_power,
				 character_statistic_info.stage_progress,
				 character_info.ladder_score 
				 #tower TODO
					into _battle_power,
							 _stage_progress,
							 _ladder_score
         #_tower TODO
					from character_statistic_info,character_info where character_statistic_info.id = _character_id and character_info.id= _character_id;

  
	#calc rank
	select sum(character_percentage_rank_sampling_info.num) into _max_character from character_percentage_rank_sampling_info where character_percentage_rank_sampling_info.type=1;
	
	select lb_battle_power_info.`order` INTO _battle_power_rank from lb_battle_power_info where lb_battle_power_info.character_id=_character_id;
	select lb_stage_info.`order` INTO _stage_progress_rank from lb_stage_info where lb_stage_info.character_id=_character_id;
	select lb_ladder_info.`order` INTO _ladder_rank from lb_ladder_info where lb_ladder_info.character_id=_character_id;

	if(_battle_power_rank=0.00)THEN
			select sum(num)/_max_character into _battle_power_rank from  character_percentage_rank_sampling_info where character_percentage_rank_sampling_info.battle_power<=_stage_progress and type=1;
	end IF;

	if(_stage_progress_rank=0.00)THEN
			select sum(num)/_max_character into _stage_progress_rank from  character_percentage_rank_sampling_info where character_percentage_rank_sampling_info.stage <=_battle_power and type=1;
	end IF;

	if(_ladder_rank=0.00)THEN
			select sum(num)/_max_character into _ladder_rank from  character_percentage_rank_sampling_info where character_percentage_rank_sampling_info.ladder_score <=_ladder_score and type=1;
	end IF;
	
	#select _battle_power_rank,_stage_progress_rank,_ladder_rank;
  #tower TODO
	#select lb_tower_info
	
	#guild TODO
  select sum(character_percentage_rank_sampling_info.num) into _max_guild from character_percentage_rank_sampling_info where character_percentage_rank_sampling_info.type=2;
  #_guild_battle_power TODO
  #_guild_battle_power_rank TODO

	#update or insert
	insert into `today_leader_board_info`(id,battle_power,battle_power_rank,stage_progress,stage_progress_rank,ladder_score,ladder_rank,tower,tower_rank,guild_battle_power,guild_battle_power_rank,last_order_time)
							VALUES(_character_id,_battle_power,_battle_power_rank,_stage_progress,_stage_progress_rank,_ladder_score,_ladder_rank,_tower,_tower_rank,_guild_battle_power,_guild_battle_power_rank,now())
					    ON DUPLICATE KEY UPDATE battle_power=_battle_power,
																			battle_power_rank=_battle_power_rank,
																			stage_progress=_stage_progress,
																			stage_progress_rank=_stage_progress_rank,
																			ladder_score=_ladder_score,
																			ladder_rank=_ladder_rank,
																			tower=_tower,
																			tower_rank=_tower_rank,
																			guild_battle_power=_guild_battle_power,
																			guild_battle_power_rank=_guild_battle_power_rank,
																			last_order_time=now();

select * from today_leader_board_info where today_leader_board_info.id = _character_id ;

END
</code>    
==== 公会，天梯，通天塔排名 ====

    block