前言

我上的課程一樣是郭耀仁老師在台大資訊系統訓練班所開設的『SQL資料分析』

這篇文章會使用老師上課教到的觀念

練習的SQL查詢語法分成兩個部分

1.) 條件邏輯

2.) 分組與聚合結果篩選

當我們用SELECT挑出我們想要的欄位之後

可以利用條件邏輯的CASE衍生計算欄位

搭配布林值更進階的過濾欄位內的條件

也可以用在不同的保留字像是ORDER BY之後

當我們想要找出獨一值並且排序

或是加上聚合函數用來匯總資訊

可以使用GROUP BY

或是以HAVING 篩選分組聚合結果

下面就來開始今天的練習吧

使用的資料庫來源分別是：

1. IMDb.com 最高評等的電影 https://www.imdb.com/chart/top
2. NBA 官方 API https://data.nba.net/prod/v1/today.json

條件

以 CASE篩選

條件除了能夠運用在 WHERE 篩選資料，還能使用 CASE
CASE的用法：

SELECT CASE WHEN condition_1 THEN result_1
            WHEN condition_2 THEN result_2
            ...
            ELSE result_else
        END AS alias;

在電影的資料集裡，若是想看電影是否在2000年之後上映

SELECT title,
       release_year,
       CASE WHEN release_year >= 2000 THEN 1
            ELSE 0 END AS after_millennium
  FROM movies
 LIMIT 5;

可以看到在2000年後上映的電影在after_millennium那欄裡顯示1，之前的顯示為0
若是想改成用true/false表示
只要改變then後的參數

SELECT title,
       release_year,
       CASE WHEN release_year >= 2000 THEN 'TRUE' 
            ELSE 'FALSE' END AS after_millennium
  FROM movies
 LIMIT 5;

可以看到從上面的1/0變為下面的true/false

如果在某個觀測值發生 condition_1 與 condition_2 都判斷為真的情況下，會以 result_1 表示

SELECT CASE WHEN condition_1 THEN result_1
            WHEN condition_2 THEN result_2
            ...
            ELSE result_else
        END AS alias;

CASE ＋ SELECT ＋ORDER BY

SELECT CASE WHEN condition_1 THEN result_1
            WHEN condition_2 THEN result_2
            ...
            ELSE result_else END AS alias
  FROM table_name
 ORDER BY alias;

SELECT firstName,
       lastName,
       CASE WHEN pos IN ('C', 'C-F') THEN 'Center'
            WHEN pos IN ('G', 'G-F') THEN 'Guard'
            ELSE 'Forward' END AS pos_new
  FROM players
 ORDER BY pos_new
 LIMIT 8;

分組與聚合

* GROUP BY 分組 ＝ DISTINCT ＋ ORDER BY *

用法如下

SELECT column_names
  FROM table_name
 GROUP BY column_names;

當我想篩選出獨一值的球員位置並且排序獨一值

SELECT DISTINCT pos AS distinct_pos
  FROM players
 ORDER BY distinct_pos;

上面的程式碼使用DISTICT之後加上ORDER BY

會顯示出想要的結果

SELECT pos AS distinct_pos
  FROM players
 GROUP BY pos;

在使用GROUP BY之後

可以看到得到相同結果

所以當我們想要找出獨一值並且排序

就可以使用到GROUP BY

假如希望計算不同位置的球員平均身高

原先要拆成兩步驟

1. 先知道有哪些鋒衛位置。
2. 篩選不同鋒衛位置的球員，計算平均身高。

但是到了第二部要計算不同衝鋒位置的平均身高時

就會要一一計算平均身高（因為當作篩選條件欄位有很多獨一值）

會非常耗時，看起來版面也會很亂

結合聚合函數與GROUP BY 可以便捷地完成分組聚合
使用方式如下

SELECT AGGREGATE_FUNCTION(column_names) AS alias
  FROM table_name
 GROUP BY column_names;

使用GROUP BY之後就可以不用分成兩段的計算含有聚合函數的需求

現在一樣是篩選不同位置的球員計算平均身高

SELECT pos,
       ROUND(AVG(heightMeters), 2) AS avg_height_meters
  FROM players
 GROUP BY pos;  

來計算看看不同國家球員的平均身高

SELECT country,
       ROUND(AVG(heightMeters), 2) AS avg_height_meters
  FROM players
 GROUP BY country; 

* HAVING 篩選分組聚合後的結果*

用來篩選資料的兩種方式：

1. WHERE:用在觀測值。
2. HAVING：用在分組聚合後的結果

SELECT AGGREGATE_FUNCTION(column_names) AS alias
  FROM table_name
 GROUP BY column_names
HAVING conditions;

當我們想篩選平均身高大於 2 公尺的 pos（鋒衛位置）

就可以使用HAVING 篩選分組聚合結果

SELECT pos,
       ROUND(AVG(heightMeters), 2) AS avg_height_meters
  FROM players
 GROUP BY pos
HAVING AVG(heightMeters) >= 2;  

小結

因為寫作順序必須遵從標準 SQL 的規定

要注意保留字的使用順序

目前已經大致處理好同個資料表內的資料查詢

明天就要進階到

處理多張有關連的資料表

這也是關聯式資料表的重要之處