2021. 01. 08 금요일

프로젝트 진행을 기획 2번안으로 결정하였다.

• 이전부터 다뤄보고 싶은 주제이기도 하고, 전에 다뤘던 주제와는 다른 내용이어서 결정하게 되었다.
• 다만 비지니스 상황에 대한 문제는 계속 고민해 보아야 할 것이다.

Query를 몇가지 작성해보았다.

• 게임, 그리고 데이터의 특성상 하나의 gameid에 양 팀의 데이터가 모두 존재한다.

  • 각 팀의 상황별 승률을 계산하는 것은 문제가 없지만, 팀 구분 없이 상황별 양팀 통합 승률을 구하는 것은 조금 복잡한 query가 될 것 같았다.

• 블루팀, 퍼플팀(레드팀) 각각의 데이터를 CTE로 구하고, 메인 쿼리에서 데이터를 합산하는 방법이 좋을 것 같다.

• 지금은 아직 기획을 검토하는 기간이고, 실제로 분석에 들어가는 것은 다음주 부터지만,
  해당 내용에 맞는 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별 경기수 & 승리수

• 이 경우는 평균 레벨과 달리 경기별 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
  와 같은 조건을 부여해서 각각의 데이터를 버킷에 포함시켜 주는 것이다.