零基礎初探SQL (二)

好不容易產出自己要的資訊，該如何有序地整理？

擅長使用excel 的朋友一定不陌生使用「篩選」並將資料: 降冪 or 升冪排序，或者使用 樞紐分析表 pivot table 快速探索資料，那在SQL實踐中，你會需要熟練地使用分組(GROUP BY) 與 排序 (ORDER BY)！

分組(GROUP BY) 與 排序 (ORDER BY) 傻傻分不清?

GROUP BY的使用時機

• 目的：GROUP BY用於將查詢結果集中的行分組成較小的集合。
• 場景：常與聚合函數一起使用，用於對每個分組的數據進行匯總或統計分析。
• • 當你需要對某些欄位的值進行聚合計算，例如SUM()、AVG()、COUNT()、MAX()、MIN()等）時，使用GROUP BY來指定基於哪些欄位的值進行分組。
  • 例：你想要計算每個部門的員工數，按部門欄進行分組並計算每組的行數。

ORDER BY的使用時機

• 目的：ORDER BY用於對查詢結果的行進行排序。
• 場景：不改變數據的分組，只是改變行的顯示順序。
• • 當你需要按照某一列或多列的值對結果集進行排序時使用。排序可以是升序（ASC）或降序（DESC）。
  • 如：如果你想要查看所有員工的薪資列表，從高到低排序，就可以使用ORDER BY語句按薪資降序排列。

範例

假設有一個employees表，包含department, salary等列。

• 計算每個部門的平均薪資：

  sqlCopy codeSELECT department, AVG(salary) AS average_salary
  FROM employees
  GROUP BY department;

  這裡使用GROUP BY按部門分組，然後計算每個部門的平均薪資。
• 查看員工薪資列表，按薪資降序排列：

  sqlCopy codeSELECT * FROM employees
  ORDER BY salary DESC;

  這裡使用ORDER BY按薪資降序排列所有員工的記錄。

簡單來說，當你想要對數據進行匯總或按組統計時，使用GROUP BY；當你想要改變查詢結果的顯示順序時，使用ORDER BY。

ORDER BY的注意事項

假設有一個user_profile表，包含device_id, gpa,age等列。

若我想按gpa降序排序，然後在gpa相同的情況下按age降序排序：

sqlCopy codeSELECT device_id, gpa, age
FROM user_profile
ORDER BY gpa DESC, age DESC

在SQL中，當指定多個排序條件時，每個排序條件都需要指明是升序（ASC）還是降序（DESC）。如果沒有為每個條件明確指定，那麼只有第一個條件被認為是指定的排序方式，其餘條件默認為升序。

這樣，SQL查詢首先會按gpa進行降序排序，如果有兩個用戶的gpa相同，則會按他們的age進行降序排序。

擷取資料串中的字元

EXCEL大神們面對長長的資料串，除了使用函數 left or right之外，處理大批量資料的話對「資料剖析」按照 分隔符號 或 固定寬度拆分，想必不陌生！

在SQL 中也有類似的函數 SUBSTRING_INDEX

SUBSTRING_INDEX提取需要的字元

假設有一個profile欄位，其值是像"180cm,75kg,27,male"這樣的字符串，包含了四部分信息：身高、體重、年齡、性別，且這些信息由逗號分隔。

我要如何單純提取年齡？

當使用SUBSTRING_INDEX(profile, ",", 3)函數時，這是怎麼工作的：

• SUBSTRING_INDEX是一個字符串函數，用來根據指定的分隔符（在這裡是逗號,）來分割字符串。
• 第一個參數是原始字符串，即profile。
• 第二個參數是分隔符，這裡是逗號,。
• 第三個參數指定了從字符串的開頭開始，要提取多少個分隔的元素。在這裡是3，意味著從左邊開始數，要提取直到第三個逗號之前的所有內容。

因此，SUBSTRING_INDEX(profile, ",", 3)會從"180cm,75kg,27,male"中提取出"180cm,75kg,27"。從開頭到第三個逗號之前的部分，也就是包括身高、體重和年齡，性別部分不在此次提取的範圍。

當再次使用SUBSTRING_INDEX(profile, ",", -1)函數時，這是怎麼工作的：

SUBSTRING_INDEX函數，這次是以","為分隔符，使用-1作為第三個參數。這告訴函數從字符串的右側開始計數，並提取最後一個逗號之後的所有內容。在我們的例子中，這意味著從"180cm,75kg,27"中提取出"27"，也就是年齡。

熟悉這些指令，可以幫助我們再使用SQL時，如同使用EXCEL 單次對大批量的資料進行處理！