2021. 01. 12 화요일

대시보드에 사용할 지표를 정리하였다.

• 스코어카드
  • 승률(원형 차트 사용)
  • 평균 킬 수
  • 평균 데스 수
  • 평균 어시스트
  • 평균 CS
  • 평균 획득 골드
  • 평균 설치 와드
  • 평균 파괴 와드
  • 평균 파괴 타워
  • 첫 킬 차지률
  • 평균 레벨
• 차트
  • CS에 따른 승패 비율
  • 획득 골드에 따른 승패 비율
  • 킬 수에 따른 승패 비율
  • 데스 수에 따른 승패 비율
  • 타워 철거 수에 따른 승패 비율
  • 평균 레벨에 따른 승패 비율
  • 와드 설치 수에 따른 승패 비율
  • 첫 킬 차지에 따른 승패 비율

어제 결정한 방법을 바탕으로 쿼리를 작성하였다.

• 팀별로 승률을 따로 구하기 위해 두개의 테이블을 생성하였다.
• 요소별 버킷을 생성하고 퍼플팀의 경우에는 bluewins를 퍼플팀에 해당하는 데이터로 수정하였다.

퍼플팀 대시보드 쿼리

create or replace table `vaulted-cogency-295111.practice.lol_diamond_10min_red` AS
WITH gold AS
  (
  SELECT 
    lower, upper 
  FROM 
    UNNEST(GENERATE_ARRAY(10000, 23000, 1000)) AS lower, 
    UNNEST(GENERATE_ARRAY(11000, 24000, 1000)) AS upper 
  WHERE
    lower + 1000 = upper
  ),
cs AS
  (
  SELECT 
    lower, upper 
  FROM 
    UNNEST(GENERATE_ARRAY(90, 280, 10)) AS lower, 
    UNNEST(GENERATE_ARRAY(100, 290, 10)) AS upper 
  WHERE
    lower + 10 = upper
  ),
ward AS
  (
  SELECT 
    lower, upper 
  FROM 
    UNNEST(GENERATE_ARRAY(0, 260, 20)) AS lower, 
    UNNEST(GENERATE_ARRAY(20, 280, 20)) AS upper 
  WHERE
    lower + 20 = upper
  )
SELECT
  gameId,
  CASE WHEN bluewins = 1 THEN 0
    WHEN bluewins = 0 THEN 1 END AS redwins,
  blueWardsPlaced,
  blueWardsDestroyed,	
  blueFirstBlood,	
  blueKills,	
  blueDeaths,	
  blueAssists,	
  blueEliteMonsters,	
  blueDragons,	
  blueHeralds,	
  blueTowersDestroyed,	
  blueTotalGold,	
  blueAvgLevel,	
  blueTotalExperience,	
  blueTotalMinionsKilled,	
  blueTotalJungleMinionsKilled,	
  blueGoldDiff,	
  blueExperienceDiff,	
  blueCSPerMin,	
  blueGoldPerMin,	
  redWardsPlaced,	
  redWardsDestroyed,	
  redFirstBlood,	
  redKills,	
  redDeaths,	
  redAssists,	
  redEliteMonsters,	
  redDragons,	
  redHeralds,	
  redTowersDestroyed,	
  redTotalGold,	
  redAvgLevel,	
  redTotalExperience,	
  redTotalMinionsKilled,	
  redTotalJungleMinionsKilled,	
  redGoldDiff,	
  redExperienceDiff,	
  redCSPerMin,	
  redGoldPerMin,
  gold.lower AS gold_lower, 
  gold.upper AS gold_upper, 
  cs.lower AS cs_lower, 
  cs.upper AS cs_upper,
  ward.lower AS ward_lower,
  ward.upper AS ward_upper
FROM
  `vaulted-cogency-295111.practice.lol_diamond_10min`,
  gold,
  cs,
  ward
WHERE
  (redTotalgold >= gold.lower
  AND redTotalgold < gold.upper)
  AND (redTotalMinionsKilled >= cs.lower
  AND redTotalMinionsKilled < cs.upper)
  AND (redwardsplaced >= ward.lower
  AND redwardsplaced < ward.upper)
;

블루팀 대시보드 쿼리

create or replace table `vaulted-cogency-295111.practice.lol_diamond_10min_blue` AS
WITH gold AS
  (
  SELECT 
    lower, upper 
  FROM 
    UNNEST(GENERATE_ARRAY(10000, 23000, 1000)) AS lower, 
    UNNEST(GENERATE_ARRAY(11000, 24000, 1000)) AS upper 
  WHERE
    lower + 1000 = upper
  ),
cs AS
  (
  SELECT 
    lower, upper 
  FROM 
    UNNEST(GENERATE_ARRAY(90, 280, 10)) AS lower, 
    UNNEST(GENERATE_ARRAY(100, 290, 10)) AS upper 
  WHERE
    lower + 10 = upper
  ),
ward AS
  (
  SELECT 
    lower, upper 
  FROM 
    UNNEST(GENERATE_ARRAY(0, 260, 20)) AS lower, 
    UNNEST(GENERATE_ARRAY(20, 280, 20)) AS upper 
  WHERE
    lower + 20 = upper
  )
SELECT
  gameId,
  bluewins,
  blueWardsPlaced,
  blueWardsDestroyed,	
  blueFirstBlood,	
  blueKills,	
  blueDeaths,	
  blueAssists,	
  blueEliteMonsters,	
  blueDragons,	
  blueHeralds,	
  blueTowersDestroyed,	
  blueTotalGold,	
  blueAvgLevel,	
  blueTotalExperience,	
  blueTotalMinionsKilled,	
  blueTotalJungleMinionsKilled,	
  blueGoldDiff,	
  blueExperienceDiff,	
  blueCSPerMin,	
  blueGoldPerMin,	
  redWardsPlaced,	
  redWardsDestroyed,	
  redFirstBlood,	
  redKills,	
  redDeaths,	
  redAssists,	
  redEliteMonsters,	
  redDragons,	
  redHeralds,	
  redTowersDestroyed,	
  redTotalGold,	
  redAvgLevel,	
  redTotalExperience,	
  redTotalMinionsKilled,	
  redTotalJungleMinionsKilled,	
  redGoldDiff,	
  redExperienceDiff,	
  redCSPerMin,	
  redGoldPerMin,
  gold.lower AS gold_lower, 
  gold.upper AS gold_upper, 
  cs.lower AS cs_lower, 
  cs.upper AS cs_upper,
  ward.lower AS ward_lower,
  ward.upper AS ward_upper
FROM
  `vaulted-cogency-295111.practice.lol_diamond_10min`,
  gold,
  cs,
  ward
WHERE
  (blueTotalgold >= gold.lower
  AND blueTotalgold < gold.upper)
  AND (blueTotalMinionsKilled >= cs.lower
  AND blueTotalMinionsKilled < cs.upper)
  AND (bluewardsplaced >= ward.lower
  AND bluewardsplaced < ward.upper)
;

데이터를 불러온 후 차트를 제작하였다.

• 스코어 카드를 제외한 모든 차트는 막대차트를 사용하여 구간별 승률을 확인할 수 있도록 하였다.
• 측정 항목이 모든 차트에서 승/패로만 구분되어 차트의 색상은 통일하였다.