Project_6. 사용 쿼리

소고기는레어·2021년 1월 20일
analysisleague of legendsprojectquerysql
0

TIL 📝

목록 보기
64/96
post-thumbnail

대시보드용 테이블 생성(퍼플팀)

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)
;

CS별 경기 수 & 승리 수

WITH bucket 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
  ),
red AS
  (
  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
  (
  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 cs_lower,
  COALESCE(red.upper, blue.upper) AS cs_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
;

승리 / 패배 / 전체 평균 CS

WITH sub AS
  (
  SELECT
    ROUND(AVG(redtotalminionskilled), 2) AS red_cs_win,
    ROUND(AVG(bluetotalminionskilled), 2) AS blue_cs_lose
  FROM
    `vaulted-cogency-295111.practice.lol_diamond_10min`
  WHERE
    bluewins = 0
  ),
sub2 AS
  (
  SELECT
    ROUND(AVG(bluetotalminionskilled), 2) AS blue_cs_win,
    ROUND(AVG(redtotalminionskilled), 2) AS red_cs_lose
  FROM
    `vaulted-cogency-295111.practice.lol_diamond_10min`
  WHERE
    bluewins = 1
  ),
sub3 AS
  (
  SELECT
    ROUND(AVG(bluetotalminionskilled), 2) AS blue_cs,
    ROUND(AVG(redtotalminionskilled), 2) AS red_cs
  FROM
    `vaulted-cogency-295111.practice.lol_diamond_10min`
  )
SELECT
  ROUND((red_cs_win + blue_cs_win) / 2, 2) AS win_avg_cs,
  ROUND((blue_cs_lose + red_cs_lose) / 2, 2) AS lose_avg_cs,
  ROUND((blue_cs + red_cs) / 2, 2) AS avg_cs
FROM
  sub,
  sub2,
  sub3
;

평균 레벨별 경기 수 & 승리 수

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
;

승리 / 패배 / 전체 평균 레벨

WITH sub AS
  (
  SELECT
    ROUND(AVG(redAvgLevel), 2) AS red_lev_win,
    ROUND(AVG(blueAvgLevel), 2) AS blue_lev_lose
  FROM
    `vaulted-cogency-295111.practice.lol_diamond_10min`
  WHERE
    bluewins = 0
  ),
sub2 AS
  (
  SELECT
    ROUND(AVG(blueAvgLevel), 2) AS blue_lev_win,
    ROUND(AVG(redAvgLevel), 2) AS red_lev_lose
  FROM
    `vaulted-cogency-295111.practice.lol_diamond_10min`
  WHERE
    bluewins = 1
  ),
sub3 AS
  (
  SELECT
    ROUND(AVG(blueAvgLevel), 2) AS blue_lev,
    ROUND(AVG(redAvgLevel), 2) AS red_lev
  FROM
    `vaulted-cogency-295111.practice.lol_diamond_10min`
  )
SELECT
  ROUND((red_lev_win + blue_lev_win) / 2, 2) AS win_avg_lev,
  ROUND((blue_lev_lose + red_lev_lose) / 2, 2) AS lose_avg_lev,
  ROUND((blue_lev + red_lev) / 2, 2) AS avg_lev
FROM
  sub,
  sub2,
  sub3
;

킬별 경기 수 & 승리 수

WITH red AS
  (
  SELECT
    redkills AS kills,
    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
    bluekills AS kills,
    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.kills, blue.kills) AS kills,
  (COALESCE(red.total_matches, 0) + COALESCE(blue.total_matches, 0)) AS total_matches,
  (COALESCE(red.wins, 0) + COALESCE(blue.wins, 0)) AS wins,
  (COALESCE(red.total_matches, 0) + COALESCE(blue.total_matches, 0)) - (COALESCE(red.wins, 0) + COALESCE(blue.wins, 0)) AS lose
FROM
  red
FULL JOIN
  blue
USING(kills)
ORDER BY
  1
;

승리 / 패배 / 전체 평균 킬

WITH sub AS
  (
  SELECT
    ROUND(AVG(redkills), 2) AS red_kill_win,
    ROUND(AVG(bluekills), 2) AS blue_kill_lose
  FROM
    `vaulted-cogency-295111.practice.lol_diamond_10min`
  WHERE
    bluewins = 0
  ),
sub2 AS
  (
  SELECT
    ROUND(AVG(bluekills), 2) AS blue_kill_win,
    ROUND(AVG(redkills), 2) AS red_kill_lose
  FROM
    `vaulted-cogency-295111.practice.lol_diamond_10min`
  WHERE
    bluewins = 1
  ),
sub3 AS
  (
  SELECT
    ROUND(AVG(bluekills), 2) AS blue_kill,
    ROUND(AVG(redkills), 2) AS red_kill
  FROM
    `vaulted-cogency-295111.practice.lol_diamond_10min`
  )
SELECT
  ROUND((red_kill_win + blue_kill_win) / 2, 2) AS win_avg_kill,
  ROUND((blue_kill_lose + red_kill_lose) / 2, 2) AS lose_avg_kill,
  ROUND((blue_kill + red_kill) / 2, 2) AS avg_kill
FROM
  sub,
  sub2,
  sub3
;

획득 골드별 경기 수 & 승리 수

WITH bucket 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
  ),
red AS
  (
  SELECT
    lower,
    upper,
    COUNT(gameid) AS total_matches,
    COUNT(gameid) - SUM(bluewins) AS wins
  FROM
    bucket,
    `vaulted-cogency-295111.practice.lol_diamond_10min`
  WHERE
    redTotalgold >= lower
    AND redTotalgold < upper
  GROUP BY
    1, 2
  ORDER BY
    1
  ),
blue AS
  (
  SELECT
    lower,
    upper,
    COUNT(gameid) AS total_matches,
    SUM(bluewins) AS wins
  FROM
    bucket,
    `vaulted-cogency-295111.practice.lol_diamond_10min`
  WHERE
    blueTotalgold >= lower
    AND blueTotalgold < upper
  GROUP BY
    1, 2
  ORDER BY
    1
  )
SELECT
  COALESCE(red.lower, blue.lower) AS gold_lower,
  COALESCE(red.upper, blue.upper) AS gold_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
;

승리 / 패배 / 전체 평균 획득 골드

WITH sub AS
  (
  SELECT
    ROUND(AVG(redtotalgold), 2) AS red_gold_win,
    ROUND(AVG(bluetotalgold), 2) AS blue_gold_lose
  FROM
    `vaulted-cogency-295111.practice.lol_diamond_10min`
  WHERE
    bluewins = 0
  ),
sub2 AS
  (
  SELECT
    ROUND(AVG(bluetotalgold), 2) AS blue_gold_win,
    ROUND(AVG(redtotalgold), 2) AS red_gold_lose
  FROM
    `vaulted-cogency-295111.practice.lol_diamond_10min`
  WHERE
    bluewins = 1
  ),
sub3 AS
  (
  SELECT
    ROUND(AVG(bluetotalgold), 2) AS blue_gold,
    ROUND(AVG(redtotalgold), 2) AS red_gold
  FROM
    `vaulted-cogency-295111.practice.lol_diamond_10min`
  )
SELECT
  ROUND((red_gold_win + blue_gold_win) / 2, 2) AS win_avg_gold,
  ROUND((blue_gold_lose + red_gold_lose) / 2, 2) AS lose_avg_gold,
  ROUND((blue_gold + red_gold) / 2, 2) AS avg_gold
FROM
  sub,
  sub2,
  sub3
;

요소별 승리 상관계수

SELECT
  corr(bluewins, bluekills) AS corr_kill,
  corr(bluewins, bluetotalminionskilled) AS corr_cs,
  corr(bluewins, bluefirstblood) AS corr_fk,
  corr(bluewins, bluetotalexperience) AS corr_exp,
  corr(bluewins, bluetotalgold) AS corr_gold
FROM
  `vaulted-cogency-295111.practice.lol_diamond_10min`
profile
소고기는레어
https://www.rarebeef.co.kr/
이전 포스트

TIL_63. Project 6

다음 포스트

TIL_64. Project 6

0개의 댓글