英雄对位胜率
简单解释,就是一个英雄在与其他英雄进行对局时的胜率。
如下图所示,假设共有8个英雄。
对局时,可能是2V2,3V3,5V3 英雄可能重复也可能不充分,以下按3V3 英雄可重复计算。
首先模拟对战记录,建表语句
CREATE TABLE `hero_fight` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`win1` int(11) NOT NULL DEFAULT 0 COMMENT '胜利英雄id1',
`win2` int(11) NOT NULL DEFAULT 0 COMMENT '胜利英雄id2',
`win3` int(11) NOT NULL DEFAULT 0 COMMENT '胜利英雄id3',
`lose1` int(11) NOT NULL DEFAULT 0 COMMENT '失败英雄id1',
`lose2` int(11) NOT NULL DEFAULT 0 COMMENT '失败英雄id2',
`lose3` int(11) NOT NULL DEFAULT 0 COMMENT '失败英雄id3',
PRIMARY KEY (`id`) USING BTREE
)
接下来模拟1000条随机英雄对战数据
CREATE PROCEDURE mock ( a INT ) BEGIN
DECLARE
i INT DEFAULT 1;
WHILE
i <= a DO
SET i = i + 1;
INSERT INTO hero_fight ( win1, win2, win3, lose1, lose2, lose3 ) SELECT
FLOOR( RAND() * 8 + 1 ),
FLOOR( RAND() * 8 + 1 ),
FLOOR( RAND() * 8 + 1 ),
FLOOR( RAND() * 8 + 1 ),
FLOOR( RAND() * 8 + 1 ),
FLOOR( RAND() * 8 + 1 );
END WHILE;
END;
CALL mock ( 1000 );
DROP PROCEDURE
IF
EXISTS mock;
如果感觉数据量少,可以多模拟一些随机对战数据,也可以用下面的sql进行蠕虫复制,每执行一次,数据量翻倍
INSERT INTO hero_fight ( win1, win2, win3, lose1, lose2, lose3 ) SELECT win1, win2, win3, lose1, lose2, lose3 FROM hero_fight;
数据的格式大概是这样的
接下来就是进行计算和统计。胜率计算的公式就是,胜场/总场数*100%
对位胜率同样,英雄A与英雄B的对位胜率就是 A对位B的胜场 / A对位B的总场数 *100%
A对位B的总场数 = A对位B的胜场 + B对位A的胜场 (不考虑平局)
实际英雄对位胜率 = A对位B的胜场 / (A对位B的胜场 + B对位A的胜场) *100%
由此可得,我们只需要计算一个英雄对位其他所有英雄的胜场次数就可以了,不需要考虑失败次数和总场次。
接下来,创建一个中间表,记录胜场次数,下面是建表语句
CREATE TABLE `hero_win_count` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`win` int(11) NOT NULL DEFAULT 0 COMMENT '胜利英雄id',
`lose` int(11) NOT NULL DEFAULT 0 COMMENT '失败英雄id',
`num` int(11) NOT NULL DEFAULT 0 COMMENT '次数',
PRIMARY KEY (`id`) USING BTREE
)
然后需要通过战斗记录表生成胜场记录表
因为对战的英雄是可以重复的,所以同一局对战,相同的英雄胜场只算一次。
查询所有胜利的英雄id 和 失败的英雄id
SELECT DISTINCT win1 win FROM hero_fight UNION SELECT DISTINCT win2 win FROM hero_fight UNION SELECT DISTINCT win3 win FROM hero_fight;
SELECT DISTINCT lose1 lose FROM hero_fight UNION SELECT DISTINCT lose2 lose FROM hero_fight UNION SELECT DISTINCT lose3 lose FROM hero_fight;
然后,通过笛卡尔积计算所有英雄对位的情况
SELECT
*
FROM
hero_fight t1
JOIN ( SELECT DISTINCT win1 win FROM hero_fight UNION SELECT DISTINCT win2 win FROM hero_fight UNION SELECT DISTINCT win3 win FROM hero_fight ) t2 ON t2.win IN ( t1.win1, t1.win2, t1.win3 )
JOIN ( SELECT DISTINCT lose1 lose FROM hero_fight UNION SELECT DISTINCT lose2 lose FROM hero_fight UNION SELECT DISTINCT lose3 lose FROM hero_fight ) t3 ON t3.lose IN ( t1.lose1, t1.lose2, t1.lose3 )
最后,根据英雄id分组,并记录数据
INSERT INTO hero_win_count (win,lose,num)
SELECT
t2.win,t3.lose ,count(0) num
FROM
hero_fight t1
JOIN ( SELECT DISTINCT win1 win FROM hero_fight UNION SELECT DISTINCT win2 win FROM hero_fight UNION SELECT DISTINCT win3 win FROM hero_fight ) t2 ON t2.win IN ( t1.win1, t1.win2, t1.win3 )
JOIN ( SELECT DISTINCT lose1 lose FROM hero_fight UNION SELECT DISTINCT lose2 lose FROM hero_fight UNION SELECT DISTINCT lose3 lose FROM hero_fight ) t3 ON t3.lose IN ( t1.lose1, t1.lose2, t1.lose3 )
GROUP BY t2.win,t3.lose
因为模拟的数据是8个英雄,所以生成的记录数就是8*8 = 64条记录 下面是部分数据
胜场计算出来后就是计算胜率了。
根据公式来,A对位B的胜场 / (A对位B的胜场 + B对位A的胜场) *100%
SELECT
a.win win_hero,
a.lose lose_hero,
a.num win_count,
b.num lose_count,
a.num + b.num total,
CONCAT( ROUND( a.num /( a.num + b.num ) * 100, 2 ), '', '%' ) win_rate
FROM
hero_win_count a
JOIN hero_win_count b
ON a.win = b.lose
AND a.lose = b.win
查询出来的结果大概就是这样的
其实到这一步就可以了,剩下的就可以交给前端进行处理了,或者我们自己进行一下行转列
SELECT
CONCAT('hero_',t.win_hero) 'hero',
MAX(CASE t.lose_hero WHEN 1 THEN t.win_rate ELSE 0 END ) hero_1,
MAX(CASE t.lose_hero WHEN 2 THEN t.win_rate ELSE 0 END ) hero_2,
MAX(CASE t.lose_hero WHEN 3 THEN t.win_rate ELSE 0 END ) hero_3,
MAX(CASE t.lose_hero WHEN 4 THEN t.win_rate ELSE 0 END ) hero_4,
MAX(CASE t.lose_hero WHEN 5 THEN t.win_rate ELSE 0 END ) hero_5,
MAX(CASE t.lose_hero WHEN 6 THEN t.win_rate ELSE 0 END ) hero_6,
MAX(CASE t.lose_hero WHEN 7 THEN t.win_rate ELSE 0 END ) hero_7,
MAX(CASE t.lose_hero WHEN 8 THEN t.win_rate ELSE 0 END ) hero_8
FROM (
SELECT
a.win win_hero,
a.lose lose_hero,
a.num win_count,
b.num lose_count,
a.num + b.num total,
CONCAT( ROUND( a.num /( a.num + b.num ) * 100, 2 ), '', '%' ) win_rate
FROM
hero_win_count a
JOIN hero_win_count b
ON a.win = b.lose
AND a.lose = b.win) t GROUP BY t.win_hero;
查询出来的结果就是下面这样的了