12. BigQuery Soccer Data Analytical Insight

Task 1. Open BigQuery

테스트 랩이라 이미 데이터셋 및 테이블이 작성되어있음

 

쿼리문에 쿼리 작성

SELECT  Events.playerId,  (Players.firstName || ' ' || Players.lastName) AS playerName,  SUM(IF(Tags2Name.Label = 'assist', 1, 0)) AS numAssists FROM  `soccer.events` Events,  Events.tags Tags LEFT JOIN  `soccer.tags2name` Tags2Name ON    Tags.id = Tags2Name.Tag LEFT JOIN  `soccer.players` Players ON    Events.playerId = Players.wyId GROUP BY  playerId, playerName ORDER BY  numAssists DESC

 

 soccer.events 테이블을 기반으로 선수별 어시스트 수를 계산하고, 어시스트가 많은 순으로 정렬하여 출력

 

결과

 

Task 3. Calculate the average pass distance by team

 

WITH Passes AS (  SELECT    *,    /* 1801 is known Tag for 'accurate' from tags2name table */    (1801 IN UNNEST(tags.id)) AS accuratePass,    (CASE      WHEN ARRAY_LENGTH(positions) != 2 THEN NULL      ELSE   /* Translate 0-100 (x,y) coordinate-based distances to absolute positions   using "average" field dimensions of 105x68 before combining in 2D dist calc */        SQRT(          POW(            (positions[ORDINAL(2)].x - positions[ORDINAL(1)].x) * 105/100,            2) +          POW(            (positions[ORDINAL(2)].y - positions[ORDINAL(1)].y) * 68/100,            2)          )      END) AS passDistance  FROM    `soccer.events`  WHERE    eventName = 'Pass' ) SELECT  Passes.teamId,  Teams.name AS team,  Teams.area.name AS teamArea,  COUNT(Passes.Id) AS numPasses,  AVG(Passes.passDistance) AS avgPassDistance,  SAFE_DIVIDE(    SUM(IF(Passes.accuratePass, Passes.passDistance, 0)),    SUM(IF(Passes.accuratePass, 1, 0))    ) AS avgAccuratePassDistance FROM  Passes LEFT JOIN  `soccer.teams` Teams ON    Passes.teamId = Teams.wyId WHERE  Teams.type = 'club' GROUP BY  teamId, team, teamArea ORDER BY  avgPassDistance

축구 경기의 패스 데이터를 바탕으로 각 팀의 패스 횟수, 평균 패스 거리, 그리고 정확한 패스의 평균 거리를 계산

 

결과

 

 

Task 4. Analyze shot distance

WITH Shots AS (  SELECT   *,   /* 101 is known Tag for 'goals' from goals table */   (101 IN UNNEST(tags.id)) AS isGoal,   /* Translate 0-100 (x,y) coordinate-based distances to absolute positions   using "average" field dimensions of 105x68 before combining in 2D dist calc */   SQRT(     POW(       (100 - positions[ORDINAL(1)].x) * 105/100,       2) +     POW(       (50 - positions[ORDINAL(1)].y) * 68/100,       2)      ) AS shotDistance  FROM   `soccer.events`  WHERE   /* Includes both "open play" & free kick shots (including penalties) */   eventName = 'Shot' OR   (eventName = 'Free Kick' AND subEventName IN ('Free kick shot', 'Penalty')) ) SELECT  ROUND(shotDistance, 0) AS ShotDistRound0,  COUNT(*) AS numShots,  SUM(IF(isGoal, 1, 0)) AS numGoals,  AVG(IF(isGoal, 1, 0)) AS goalPct FROM  Shots WHERE  shotDistance <= 50 GROUP BY  ShotDistRound0 ORDER BY  ShotDistRound0

축구 경기에서 슈팅 거리별로 슈팅 횟수, 득점 횟수, 득점 비율을 계산

 

결과

 

 

 

Create a visualization of results

쿼리 결과에서 chart를 누르면 차트를 보여줌

 

 

Task 5. Analyze shot angle

WITH Shots AS (  SELECT   *,   /* 101 is known Tag for 'goals' from goals table */   (101 IN UNNEST(tags.id)) AS isGoal,   /* Translate 0-100 (x,y) coordinates to absolute positions using "average"   field dimensions of 105x68 before using in various distance calcs;   LEAST used to cap shot locations to on-field (x, y) (i.e. no exact 100s) */   LEAST(positions[ORDINAL(1)].x, 99.99999) * 105/100 AS shotXAbs,   LEAST(positions[ORDINAL(1)].y, 99.99999) * 68/100 AS shotYAbs  FROM    `soccer.events`  WHERE    /* Includes both "open play" & free kick shots (including penalties) */    eventName = 'Shot' OR    (eventName = 'Free Kick' AND subEventName IN ('Free kick shot', 'Penalty')) ), ShotsWithAngle AS (  SELECT    Shots.*,    /* Law of cosines to get 'open' angle from shot location to goal, given     that goal opening is 7.32m, placed midway up at field end of (105, 34) */    SAFE.ACOS(      SAFE_DIVIDE(        ( /* Squared distance between shot and 1 post, in meters */          (POW(105 - shotXAbs, 2) + POW(34 + (7.32/2) - shotYAbs, 2)) +          /* Squared distance between shot and other post, in meters */          (POW(105 - shotXAbs, 2) + POW(34 - (7.32/2) - shotYAbs, 2)) -          /* Squared length of goal opening, in meters */          POW(7.32, 2)        ),        (2 *          /* Distance between shot and 1 post, in meters */          SQRT(POW(105 - shotXAbs, 2) + POW(34 + 7.32/2 - shotYAbs, 2)) *          /* Distance between shot and other post, in meters */          SQRT(POW(105 - shotXAbs, 2) + POW(34 - 7.32/2 - shotYAbs, 2))        )      )    /* Translate radians to degrees */    ) * 180 / ACOS(-1)    AS shotAngle  FROM    Shots ) SELECT  ROUND(shotAngle, 0) AS ShotAngleRound0,  COUNT(*) AS numShots,  SUM(IF(isGoal, 1, 0)) AS numGoals,  AVG(IF(isGoal, 1, 0)) AS goalPct FROM  ShotsWithAngle GROUP BY  ShotAngleRound0 ORDER BY  ShotAngleRound0

축구 경기 데이터에서 슈팅 각도별로 슈팅 횟수, 득점 횟수, 득점 비율을 분석

 

결과

 

 

참고:

https://www.cloudskillsboost.google/focuses/58631?parent=catalog