=====数据库表===== [[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