[시각화] 대표 선수들의 선호 무기 top3와 사용비율 변화

[haein-kim]

대표선수 6명: 2HEART, DUMBO, EJ, HIKARI, PIO, UNDER

1) 선수별 선호무기 TOP3

• [x] 쿼리
• [x] 엑셀
• [x] 태블로 시각화
• 모든 토너먼트 및 매치를 통틀어 집계
• 이름 / 무기의 획득수(weapon_name) 및 사용수(attack_count) 기준 랭크

2) 선호 무기의 변화

• 토너먼트 별 집계
• 이름 / 토너먼트 / 전체 대비 해당 무기 획득률 / 전체 대비 해당 무기 사용률
• [x] 쿼리
• [x] 엑셀
• [x] 태블로 시각화

[haein-kim]

UNDER

# 평균 매치 시간
sql_input = """
# UNDER
WITH weapon as (
    SELECT player_name,
           tournament_id,
           created_at,
           weapon_name,
           SUM(attack_count) sum_attack_count,
           RANK() OVER (PARTITION BY tournament_id, player_name ORDER BY SUM(attack_count) DESC) use_rank
    FROM `kr_weapons`
    GROUP BY tournament_id, player_name, weapon_name
    ORDER BY player_name, created_at
    ),
    tournament as (
    SELECT player_name, tournament_id,
            SUM(attack_count) sum_attack_count
    FROM `kr_weapons`
    GROUP BY tournament_id, player_name
    )

SELECT
weapon.tournament_id                                                              토너먼트,
weapon.weapon_name                                                                무기이름,
ROUND((weapon.sum_attack_count / tournament.sum_attack_count) * 100, 1)           사용량비율
FROM weapon
INNER JOIN tournament
ON weapon.player_name = tournament.player_name AND
weapon.tournament_id = tournament.tournament_id
WHERE weapon.use_rank < 4
AND weapon.player_name = 'UNDER'
ORDER BY created_at, 사용량비율
"""
under_use = pd.read_sql_query(sql_input, conn)
under_use

sql_input = """
SELECT tournament_id,
       DATE(MIN(created_at)) first,
       DATE(MAX(created_at)) last
FROM matches_summary
GROUP BY tournament_id
"""
time = pd.read_sql_query(sql_input, conn)
time

under_use = under_use.merge(time, left_on=under_use['토너먼트'], right_on= time['tournament_id']).drop(columns=['tournament_id', 'key_0'])
under_use.to_excel('under_use.xlsx', index='False')

[haein-kim]

선호 무기 변화 (쿼리)

players-prefer-weapons.sql

WITH weapon as (
    SELECT player_name,
           tournament_id,
           created_at,
           weapon_name,
           SUM(attack_count) sum_attack_count,
           RANK() OVER (PARTITION BY tournament_id, player_name ORDER BY SUM(attack_count) DESC) use_rank
    FROM `kr_weapons`
    GROUP BY tournament_id, player_name, weapon_name
    ORDER BY player_name, created_at
    ),
    tournament as (
    SELECT player_name, tournament_id,
            SUM(attack_count) sum_attack_count
    FROM `kr_weapons`
    GROUP BY tournament_id, player_name
    ),
     times as
         (SELECT tournament_id,
                 DATE(MIN(created_at)) first,
                 DATE(MAX(created_at)) last
         FROM matches_summary
         GROUP BY tournament_id)

SELECT
weapon.player_name 선수이름,
weapon.tournament_id 토너먼트,
weapon.weapon_name 무기이름,
ROUND((weapon.sum_attack_count / tournament.sum_attack_count) * 100, 1) 사용량비율
FROM weapon
INNER JOIN tournament
ON weapon.player_name = tournament.player_name AND weapon.tournament_id = tournament.tournament_id
INNER JOIN times
ON weapon.tournament_id = times.tournament_id
WHERE weapon.use_rank < 4
AND weapon.player_name
ORDER BY weapon.player_name, times.first, 사용량비율 DESC