Skip to content

Data Manipulation in SQL SQL中的資料控制

在章節 Nested simple subqueries 中

以下查詢為匯集比賽紀錄之 賽季 、最大比分 以及綜觀全資料中最大比分以及 _每一季中七月的最大比數

SELECT
	-- Select the season and max goals scored in a match
	season,
    MAX(home_goal + away_goal) AS max_goals,
    -- Select the overall max goals scored in a match
   (SELECT MAX(home_goal + away_goal) FROM match) AS overall_max_goals,
   -- Select the max number of goals scored in any match in July
   (SELECT MAX(home_goal + away_goal) 
    FROM match
    WHERE id IN (
          SELECT id FROM match WHERE EXTRACT(MONTH FROM date) = 07)) AS 
          july_max_goals
FROM match
GROUP BY season;

結果如下

season	max_goals	overall_max_goals	july_max_goals  
2013/2014	10	11	7  
2012/2013	11	11	7  
2014/2015	10	11	7  
2011/2012	10	11	7

第一時間沒有看懂，是因為所有的查詢組成都很類似，因為巢狀結構讓條件分散在查詢的不同位置

但後來由查詢中的第一行 MAX(home_goal + away_goal) AS max_goals, 可知
單一賽季中的最高比分＝MAX加上home_goal+away_goal
而且僅該句受到主查詢中的GROUP BY 影響

因為依照查詢結構來說，overall_max_goals的組成也是 SELECT MAX(home_goal + away_goal) FROM match
但查詢已完成，僅回傳了一組數值＝11，就算回到主查詢加上了GROUP BY 也不受影響

第三個查詢則使用了子查詢中

問題紀錄時間 2023/2/5 17:00

實際查詢狀況以及理解編譯後如下

Spinner
DataFrameas
df
variable
SELECT
     -- 下方為選擇所有match欄位中的賽季field
    season,
    -- 下方將主、客隊伍得分加總（作為主查詢使用之一，且主查詢的附加條件過濾器GROUP BY season）
    MAX(home_goal + away_goal) AS max_goals,
    -- 下方查詢結果僅傳回一個值：11，所以未受到主查詢filter的影響
    (SELECT MAX(home_goal + away_goal) FROM soccer.match) AS overall_max_goals,
    -- 抽取所有賽季中七月且最大比分的比賽，進行了兩次的子查詢
        -- 第一層，先決定了呈現的內容為比分
    (SELECT MAX(home_goal + away_goal) FROM soccer.match 
            --第二層，在WHERE中加入了篩選id=七月份
    WHERE id IN(
          SELECT id FROM soccer.match 
            --加上EXTRACT，這裡的新用法待鑽研，看起來是擷取特定資料時可以細分，例如此處的日期中擷取月份
          WHERE EXTRACT(MONTH FROM date) = 07 )) --此處要記得補上關閉子查詢的右括號
      -- 再補上別名，作為主查詢的其中一欄
      AS july_max_goals
FROM soccer.match --回到主查詢
GROUP BY season;

我是分隔島

在章節 Window Functions 中

第一次接觸 Window function
目前已知的第一個概念是，如果啟用了如 AVG、SUM、MAX、MIN 諸如此類的運算子
在查詢結束後必須加上 GROUP BY 來確保數值能夠正常呈現
因為運算子會將依照需求可能計算一整個欄位不同列的內容變成 單一列、僅一個 數值回傳
但理論上回傳的查詢可能還是需要呈現多列，所以要再使用Window Function來額外收攏
用法為查詢子句中的項目加上 OVER()

Spinner
DataFrameas
df
variable

-- 舉例選取計算足球比賽中的比分，以及加上當季主隊平均得分數
SELECT
	date,
    home_goal,
    away_goal,
	AVG(home_goal) OVER() AS avg_home_goal
-- OVER(打包前項運算子的結果作為一個單獨的項目)
FROM soccer.match
LIMIT 3;

即啟用了OVER()這個Window Function
讓原本需要在最後加上GROUP BY的AVG運算可以正常顯示
如果不使用OVER()函數，則就需要在AVG這個選項中啟用子查詢如下方

Spinner
DataFrameas
df
variable
SELECT
    date,
    home_goal,
    away_goal,
	(SELECT AVG(home_goal) FROM soccer.match) AS avg_homegoal
FROM soccer.match
LIMIT 3;
Spinner
DataFrameas
df
variable
Run cancelled
-- 突然想到的練習，計算每一隊在2013/2014賽季中的得分與平均主隊得分
SELECT         
    t.team_long_name AS team,
    SUM(home_goal) AS season_home_goal,
    SUM(away_goal) AS season_away_goal,
	AVG(home_goal) AS avg_home_goal
FROM soccer.match AS m
LEFT JOIN soccer.team AS t
     ON t.team_api_id = m.hometeam_id
--  WHERE season = '2013/2014'
GROUP BY team
ORDER BY avg_home_goal DESC;

單元總結

子查詢

本章節學到了兩個區塊的概念，第一部分是嵌套巢狀的子查詢 以及 到處都可以子查詢
子查詢可以套用在 SELECT FROM WHERE 等子句中
還滿簡易的作法之一，但缺點是查詢寫起來很長，不利於閱讀和延續

CASE函數

以及的各種函數功能例如 CASE 函數 可利用WHEN <參數> THEN <參數> ELSE <參數> END AS的句型
等同於Excel中IFS()以及Python中的IF函數
可以依照寫下的順序來做布林邏輯的推斷，接在ELSE之後的即是不符合前項條件的內容 最後再使用END關閉函數條件並加上別名

CTE宣告

加上 CTEs ＝ 預先寫好的查詢，必須存在在SELECT 之前，主查詢搭配JOIN讓CTEs最後可以發揮功用
CTEs 宣告需要以下起手式

WITH '我是第一個CTE的別名' AS(
	--開始編寫CTE的結構
    SELCET ...'下略'
    FROM
	),
    '我是第二個CTE的別名' AS(
	--開始編寫CTE的結構
    SELCET ...'下略'
    FROM
    )

以上作為一個完整的宣告

Window Functions

Window Functions可以藉由OVER()啟用，可以單獨使用作為幫查詢做打包一個子查詢
SELECT AVG(某個欄位) OVER() AS 給他一個別名，就可以越過GROUP BY的限制
或是指定使用哪些特別的函數有特別的功用，並且在OVER(將選用的函數與欄位包在這裡)，例如下方

  • OVER( PARTITION BY <欄位名稱>) AS：用來分配 每一列資料 並給予對應限定區塊的值
  • OVER(ROWS BETWEEN {PERCIDING（往前）FOLLOWING（往後）CURRENT ROW（當前列或用來指定）} AND {同前一格} Windows 的SQL解釋，滿清楚的

暫時紀錄以上，完成Data Manipulation in SQL的練習囉！

感謝Datacamp如此好用的筆記與學習功能，大推