이전부터 다뤄보고 싶은 주제이기도 하고, 전에 다뤘던 주제와는 다른 내용이어서 결정하게 되었다.
다만 비지니스 상황에 대한 문제는 계속 고민해 보아야 할 것이다.
게임, 그리고 데이터의 특성상 하나의 gameid에 양 팀의 데이터가 모두 존재한다.
지금은 아직 기획을 검토하는 기간이고, 실제로 분석에 들어가는 것은 다음주 부터지만,
해당 내용에 맞는 query를 미리 작성해보았다.
평균 레벨별 승률을 구하기 위한 총 경기수와 승리수를 구하였다.
쿼리에서 직접 승률을 구해도 무관하지만, 퍼센티지는 스프레드 시트나 대시보드에서 자동으로 구해주기에 굳이 쿼리로 구할 필요는 없다.
WITH red AS #레드팀 평균 레벨별 경기수 & 승리수
(
SELECT
redavglevel AS avglevel,
COUNT(gameid) AS total_matches,
COUNT(gameid) - SUM(bluewins) AS wins
FROM
`vaulted-cogency-295111.practice.lol_diamond_10min`
GROUP BY
1
ORDER BY
1
),
blue AS #블루팀 평균 레벨별 경기수 & 승리수
(
SELECT
blueavglevel AS avglevel,
COUNT(gameid) AS total_matches,
SUM(bluewins) AS wins
FROM
`vaulted-cogency-295111.practice.lol_diamond_10min`
GROUP BY
1
ORDER BY
1
)
SELECT #양팀 합산
COALESCE(red.avglevel, blue.avglevel) AS avglevel,
(COALESCE(red.total_matches, 0) + COALESCE(blue.total_matches, 0)) AS total_matches,
(COALESCE(red.wins, 0) + COALESCE(blue.wins, 0)) AS wins
FROM
red
FULL JOIN
blue
USING(avglevel)
ORDER BY
1
;
위의 쿼리에서 COALESCE()
를 사용한 이유는 아래와 같다.
평균 레벨이라는 데이터가 레드팀과 블루팀 양쪽에 모두 존재한다.
예를 들어 블루팀의 평균 레벨 = (1, 2, 4, 5),
레드팀의 평균 레벨 = (1, 2, 3, 6) 일 경우에,
블루팀의 평균 레벨 (1, 2, 4, 5) 를 기준으로 삼는다면
레드팀의 (3, 6) 에 해당하는 데이터가 누락된다.
위와 같은 상황을 방지하기 위해서는
기준으로 삼을 평균 레벨이 레드팀과 블루팀 양쪽에 모두 포함되어야 한다.
따라서 FULL JOIN
을 통해 양 측의 데이터를 모두 불러오고,
COALESCE()
를 사용하여 양 측의 평균 레벨 (1, 2, 3, 4, 5, 6) 이 모두 포함된 Column을 생성하는 것이다.
+를 사용하여 데이터를 합산할 때도
어느 한 쪽의 데이터가 NULL일 경우 합산이 되지 않기에
NULL을 0으로 치환하여 합산하기 위해 COALESCE()
를 활용하였다.
이 경우는 평균 레벨과 달리 경기별 CS가 매우 다양해서 따로 버킷을 생성해야한다.
그 외의 부분은 위의 Query와 거의 같다고 볼 수 있다.
WITH bucket AS #CS 버킷 생성
(
SELECT
lower, upper
FROM
UNNEST(GENERATE_ARRAY(90, 280, 10)) AS lower,
UNNEST(GENERATE_ARRAY(100, 290, 10)) AS upper
WHERE
lower + 10 = upper
),
red AS #레드팀 CS별 경기수 & 승리수
(
SELECT
lower,
upper,
COUNT(gameid) AS total_matches,
COUNT(gameid) - SUM(bluewins) AS wins
FROM
bucket,
`vaulted-cogency-295111.practice.lol_diamond_10min`
WHERE
redTotalMinionsKilled >= lower
AND redTotalMinionsKilled < upper
GROUP BY
1, 2
ORDER BY
1
),
blue AS #블루팀 CS별 경기수 & 승리수
(
SELECT
lower,
upper,
COUNT(gameid) AS total_matches,
SUM(bluewins) AS wins
FROM
bucket,
`vaulted-cogency-295111.practice.lol_diamond_10min`
WHERE
blueTotalMinionsKilled >= lower
AND blueTotalMinionsKilled < upper
GROUP BY
1, 2
ORDER BY
1
)
SELECT #양팀 합산
COALESCE(red.lower, blue.lower) AS lower,
COALESCE(red.upper, blue.upper) AS upper,
(COALESCE(red.total_matches, 0) + COALESCE(blue.total_matches, 0)) AS total_matches,
(COALESCE(red.wins, 0) + COALESCE(blue.wins, 0)) AS wins
FROM
red
FULL JOIN
blue
USING(lower, upper)
ORDER BY
1
;
버킷을 생성할 때 generate_series
를 사용하려 했으나,
big query에서는 지원하지 않는다는 사실을 이번에 처음 알게 되었다.
따라서 버킷을 생성할 다른 방법을 알아보았는데,
GENERATE_ARRAY
를 통해 버킷의 값에 해당하는 어레이를 생성하고
UNNEST()
을 통해 어레이를 푸는 방법으로 가능했다.
또한 생성된 각각의 버킷에 lower + 10 = upper
라는 조건을 부여해서 필요한 버킷만 남겨두었다.
마찬가지로 CTE를 통해 각 팀별로 데이터를 구한 뒤 메인 쿼리에서 합산하였다.
중요한 것은 CTE를 통해 팀별 데이터를 구할 때,
redTotalMinionsKilled >= lower AND redTotalMinionsKilled < upper
와 같은 조건을 부여해서 각각의 데이터를 버킷에 포함시켜 주는 것이다.