加入視窗函式

太棒了！你現在已取得一個結果集，能抓到比賽日期、主隊、客隊，以及兩隊各自的進球數。還剩下最後一個重點——曼聯在每場比賽中「輸得多慘」？

為了判定這件事，讓我們在主查詢中加入一個視窗函式，依 home_goal 與 away_goal 差值的「絕對值」來為比賽排名。這樣就能直接比較比分差，而不必考慮曼聯是主場還是客場！

等式已經幫你寫好——你只需要把視窗函式補完整就行了！

本練習屬於課程

SQL 資料操作

檢視課程

練習說明

設定 CTE，讓主場與客場各自帶有名稱、ID，以及對應的得分。
在主查詢中選出 date、home team name、away team name、home goals、away goals。
依比分差進行排名，並以「遞減」順序排序。

動手互動練習

試著完成這個範例程式碼，體驗一下這個練習。

-- Set up the home team CTE
___ ___ ___ (
  SELECT m.id, t.team_long_name,
	  CASE WHEN m.home_goal > m.away_goal THEN 'MU Win'
		   WHEN m.home_goal < m.away_goal THEN 'MU Loss' 
  		   ELSE 'Tie' END AS outcome
  FROM match AS m
  LEFT JOIN team AS t ON m.hometeam_id = t.team_api_id),
-- Set up the away team CTE
___ ___ (
  SELECT m.id, t.team_long_name,
	  CASE WHEN m.home_goal > m.away_goal THEN 'MU Loss'
		   WHEN m.home_goal < m.away_goal THEN 'MU Win' 
  		   ELSE 'Tie' END AS outcome
  FROM match AS m
  LEFT JOIN team AS t ON m.awayteam_id = t.team_api_id)
-- Select columns and and rank the matches by goal difference
SELECT DISTINCT
    ___,
    ___.___ AS home_team,
    ___.___ AS away_team,
    m.home_goal, m.away_goal,
    ___() ___(___ ___ ABS(home_goal - away_goal) ___) as match_rank
-- Join the CTEs onto the match table
FROM match AS m
___ JOIN ___ ON m.id = ___
___ JOIN ___ ON m.id = ___
WHERE m.season = '2014/2015'
      AND ((home.team_long_name = 'Manchester United' AND home.outcome = 'MU Loss')
      OR (away.team_long_name = 'Manchester United' AND away.outcome = 'MU Loss'));

編輯並執行程式碼