Dota2 API How to proc TI3 matches

Almost 15K fans visited TI3 statistic page. So, how to create such page?

First of all you need to get matches data. It’s not difficult if you use DotA2 API. If you want it «here and now» you can just download sql-dump.

Time to code. For first example I’ll show how to get most «bloodiest match»:

SELECT s.match_id AS match_id,
       m.radiant_name AS radiant_name,
       m.dire_name AS dire_name,
       SUM(s.deaths) AS deaths
FROM matches AS m,
     slots AS s
WHERE s.match_id = m.match_id
GROUP BY s.match_id
ORDER BY deaths DESC LIMIT 1

You should check that we calculate sum of deaths and not kills, because hero may be killed by creeps, Roshan, tower etc.

Another example — get match with highest «kills per minute» value:

SELECT s.match_id AS match_id,
       m.radiant_name AS radiant_name,
       m.dire_name AS dire_name,
       (SUM(s.deaths) / m.duration * 60) AS kills_per_min
FROM matches AS m,
     slots AS s
WHERE s.match_id = m.match_id
GROUP BY s.match_id
ORDER BY kills_per_min DESC LIMIT 1

Hero list by popularity (pick+ban sum):

SELECT COUNT(hero_id) AS COUNT,
       hero_id
FROM picks_bans
GROUP BY hero_id
ORDER BY COUNT DESC

Best average KDA:

SELECT ROUND(AVG(kda),2) AS avg_kda,
       account_id
FROM
  (SELECT (IF(deaths = 0, kills+assists, (kills + assists)/deaths)) AS kda,
          account_id
   FROM slots) AS v
GROUP BY account_id
ORDER BY avg_kda DESC LIMIT 10

Count matches without deaths for each player:

SELECT COUNT(*) AS COUNT,
       account_id
FROM slots
WHERE deaths = 0
GROUP BY account_id
ORDER BY COUNT DESC

Common stats for each player (heroes played, avg gpm, avg kills, avg deaths, avg assists, avg tower damage, avg last hits, avg denies):

SELECT COUNT(DISTINCT hero_id) AS distinct_count,
       ROUND(AVG(gold_per_min),2) AS gpm,
       ROUND(AVG(kills),2) AS kills,
       ROUND(AVG(deaths),2) AS deaths,
       ROUND(AVG(assists),2) AS assists,
       ROUND(AVG(last_hits),2) AS last_hits,
       ROUND(AVG(denies),2) AS denies,
       ROUND(AVG(tower_damage),2) AS tower_damage,
       GROUP_CONCAT(hero_id) AS all_heroes,
       account_id
FROM slots
GROUP BY account_id
ORDER BY distinct_count DESC

Top killers (4294967295 — default anonymous id):

SELECT SUM(kills) AS kills,
       account_id
FROM slots
WHERE account_id <> 4294967295
GROUP BY account_id
ORDER BY kills DESC LIMIT 10

Top killers (hero):

SELECT SUM(kills) AS kills,
       hero_id
FROM slots
GROUP BY hero_id
ORDER BY kills DESC LIMIT 10

Same way to get top feeders and assistants (both — heroes and players).

Match with latest FB:

SELECT match_id,
       radiant_name,
       dire_name,
       SEC_TO_TIME(first_blood_time) AS first_blood_time
FROM matches
ORDER BY first_blood_time DESC

How to make player profile url? First, you need to get players_mapper and load users ids there. Then call method «load»:

$players_mapper_web = new players_mapper_web();
$players_mapper_web->add_id(player::convert_id(86723143)); // For one api-request you can get info about many users
$players = $players_mapper_web->load();

And profile link will looks like this:

$steam_id = player::convert_id(86723143);
echo '<a href="'.$players[$steam_id]->get('profileurl').'"><img class="avatar" src="'.$players[$steam_id]->get('avatar').'" alt="'.$players[$steam_id]->get('personaname').'" /> '.$players[$steam_id]->get('personaname').'</a>';

Hero image and name may be generated like this:

$heroes = new heroes();
$heroes->parse();
echo '<img src="'.$heroes->get_img_url_by_id($hero_id).'" alt="" /> '.$heroes->get_field_by_id($hero_id, 'localized_name')';

So, I wrote about basic components of the statistic. If you want, you can easily find the page template and put data on it.

, , ,

Оставить комментарий

Top ↑ | Main page | Back