[개인 프로젝트] FC Online 공식 경기 분석 (5) - Snowflake analytics 테이블 생성

이전에 Snowflake에서 S3의 csv 파일을 가져와 데이터베이스에 저장하는 Worksheets를 작성하였다. 이번에는 Snowflake의 raw_data Schema에 존재하는 데이터를 가지고 analytics Schema에 생성할 데이터를 만들어볼 것이다.

[개인 프로젝트] FC Online 공식 경기 분석 (4) - Snowflake 기본 설정 및 COPY

Analytics Schema에 저장할 Table

승 / 패 / 무 여부에 따른 테이블 (match_detail)

컨트롤러 (Keyboard, pad) 수 비교

• raw_data Schema의 match_detail Table 사용
• matchResult (승 / 패 / 무)에 따른 controller (keyboard, pad) 수 비교 (count)

파울, 옐로카드, 레드카드 합 비교

• raw_data Schema의 match_detail Table 사용
• matchResult (승 / 패 / 무)에 따른 yellowCards, redCards, foul 합 비교 (sum)

드리블, 점유율 평균 비교

• raw_data Schema의 match_detail Table 사용
• matchResult (승 / 패 / 무)에 따른 dribble, possession 평균 비교 (avg)

코드

-- matchResult_with_match_detail
CREATE OR REPLACE TABLE fc_online.analytics.matchResult_with_match_detail AS
SELECT
    matchResult,
    COUNT(CASE WHEN controller = 'gamepad' THEN 1 END) AS gamepad_count,
    COUNT(CASE WHEN controller = 'keyboard' THEN 1 END) AS keyboard_count,
    SUM (yellowCards) AS total_yellow_cards,
    SUM (redCards) AS total_red_cards,
    SUM (foul) AS total_foul,
    ROUND(AVG (dribble), 1) AS avg_dribble,
    ROUND(AVG (possession), 1) AS avg_possession
FROM fc_online.raw_data.match_detail
GROUP BY matchResult;

결과 예시

승 / 패 / 무 여부에 따른 테이블 (shoot)

유효슛, 골, 페널티 킥 합 비교

• match_detail과 shoot을 RIGHT JOIN (id 기준)
• matchResult (승 / 패 / 무)에 따른 effectiveShootTotal, goalTotal, shootPenaltyKick 합 비교 (sum)

코드

-- matchResult_with_shoot
CREATE OR REPLACE TABLE fc_online.analytics.matchResult_with_shoot AS
SELECT
    matchResult,
    SUM (effectiveshoottotal) AS total_effective_shoot_total,
    SUM (goaltotal) AS total_goal_total,
    SUM (shootpenaltykick) AS total_shoot_penalty_kick
FROM fc_online.raw_data.shoot A
LEFT JOIN fc_online.raw_data.match_detail B ON A.id = B.id
GROUP BY B.matchresult;

결과 예시

승 / 패 / 무 여부에 따른 테이블 (pass)

패스 시도, 패스 성공, 패스 성공률 평균 비교

• match_detail과 pass를 RIGHT JOIN (id 기준)
• matchResult (승 / 패 / 무)에 따른 passTry, passSuccess, passSuccess / passTry 평균 비교 (avg)

코드

-- matchResult_with_pass
CREATE OR REPLACE TABLE fc_online.analytics.matchResult_with_pass AS
SELECT
    matchResult,
    ROUND(AVG (passTry), 1) AS avg_pass_try,
    ROUND(AVG (passSuccess), 1) AS avg_pass_success,
    ROUND(AVG(CASE WHEN passTry = 0 THEN 0 ELSE (passSuccess * 100) / passTry END), 1) AS avg_pass_success_rate
FROM fc_online.raw_data.pass A
LEFT JOIN fc_online.raw_data.match_detail B ON A.id = B.id
GROUP BY B.matchresult;

결과 예시

승 / 패 / 무 여부에 따른 테이블 (defence)

블락 시도, 블락 성공, 블락 성공률 평균 비교

• match_detail과 defence를 RIGHT JOIN (id 기준)
• matchResult (승 / 패 / 무)에 따른 blockTry, blockSuccess, blockSuccess / passTry 평균 비교 (avg)

태클 시도, 태클 성공, 태클 성공률 평균 비교

• match_detail과 defence를 RIGHT JOIN (id 기준)
• matchResult (승 / 패 / 무)에 따른 tackleTry, tackleSuccess, tackleSuccess / tackleTry 평균 비교 (avg)

코드

-- matchResult_with_defence
CREATE OR REPLACE TABLE fc_online.analytics.matchResult_with_defence AS
SELECT
    matchResult,
    ROUND(AVG (blocktry), 1) AS avg_block_try,
    ROUND(AVG (blocksuccess), 1) AS avg_block_success,
    ROUND(AVG(CASE WHEN blocktry = 0 THEN 0 ELSE (blocksuccess * 100) / blocktry END), 1) AS avg_block_success_rate,
    ROUND(AVG (tackletry), 1) AS avg_tackle_try,
    ROUND(AVG (tacklesuccess), 1) AS avg_tackle_success,
    ROUND(AVG(CASE WHEN tackletry = 0 THEN 0 ELSE (tacklesuccess * 100) / tackletry END), 1) AS avg_tackle_success_rate
FROM fc_online.raw_data.defence A
LEFT JOIN fc_online.raw_data.match_detail B ON A.id = B.id
GROUP BY B.matchresult;

결과 예시

지금까지 차트를 제외한 플로우는 모두 작업이 완료됐다. 따라서 다음부터 자동화를 진행해보려 한다. 먼저 EC2 - Crontab을 통해 EC2에서 python 파일을 주기적으로 실행하여 S3에 적재되도록 구성할 것이다.

[개인 프로젝트] FC Online 공식 경기 분석 (6) - EC2 / crontab 자동화 1