Working with SAS PROC SQL, High Five to SUBQUERY
上一期我們曾利用 PROC SQL 的 subquery 找出一人多筆的資料中每個人最大的前幾個值,那麼我們就接著來認識什麼是 subquery 以及 subquery 的常用語法。
什麼是 Subquery?
在 SAS SQL 語言中,SQL 語法的組成像是個三明治:由 PROC SQL 開頭,並以 QUIT 結尾,中間夾著的就是所謂的 query ,而每個 query 都必須 (且至少) 由 SELECT 及 FROM 組成,視使用狀況可以再結合 WHERE、HAVING 等語句一起使用。 也因此,subquery 顧名思義就是「在 query 裡的另一個 query」。
subquery 本身是一個完整的 query,所以也 (必須) 擁有 SELECT 與 FROM 語句,而且由括號所包裏,與外部的 query 區隔開來。
外部 query 與 內部的 subquery 運作的原則是:subquery 先執行,得到的結果再回傳至外部 query。subqueries 沒有數量上的限制,因此如果有多層的 subqueries,則會從最內部的 subquery 先執行,並一層層的向外回傳、執行下一層的 subquery 直到最外層的 query 為止。
Subquery 最常出現在 SELECT 及 WHERE,而實際應用上並不限於此,除了 ORDER BY 語句之外都可以視需要自由加入 subquery 使用。以下介紹幾種常見的 subquery 使用時機與方式。
以 SAS 為例
我們模擬一組糖尿病病人的定期回診資料 (visit),內容為數次回診的抽血檢查,包含糖化血素色 (Glycated Hemolgobin, HbA1c) 與空腹血糖值 (Fasting Blood Glucose, FBG)。另外的資料則為病人的基本資料 (basic) 以及居住地資訊 (address)。
HbA1c 是血液中葡萄糖與紅血球內的血內素結合而成,血中的葡萄糖愈高,HbA1c 的值也愈高;紅血球的壽命約為三個月,因此檢測HbA1c 可以反應過去二至三個月內的血糖平均狀態。空腹血糖 FBG 則反應出當下的血糖狀態。資料檔的語法如下:
data basic; /*DM病人基本資料*/
input id age sex $ edu :$10. history;
datalines;
111 66 M high 1
222 76 F Elementary 0
333 48 F Colloege 0
444 62 M high 1
;
run;
data visit; /*DM病人回診資料*/
input id visit_date :yymmdd10. HbA1c FBG;
format visit_date yymmdd10.;
datalines;
111 2019/01/01 7.2 110
111 2019/04/04 6.8 90
111 2019/07/07 8.1 95
222 2019/01/01 6.0 90
222 2019/01/01 6.0 90
222 2019/04/04 6.0 90
333 2019/01/01 5.8 88
333 2019/04/04 6.0 89
333 2019/07/07 6.0 90
444 2019/04/04 8.5 90
444 2019/07/07 7.8 110
;
run;
data address; /*DM病人居住地資料*/
input id county & $16. district $;
datalines;
111 Taipei Xinyi
222 New Taipei City Yonghe
333 New Taipei City Banqiao
444 Taipei Songshan
555 New Taipei City Banqiao
666 Taipei Songshan
777 Taipei Shilin
888 Taipei Shilin
;
run;



- Subquery 1:找出定期回診資料 (visit) 中年齡大於65歲的人
定期回診資料 (visit) 中雖然有各次的血糖檢測值,但是年齡資訊位於 basic 資料,這時候可以利用 WHERE 和條件運算子(Conditional Operator) IN 加上subquery 來處理。
proc sql;
select *
from visit
where id in
(select id from basic where age >= 65);
quit;
SAS 的 SQL procedure 會優先處理內層的 subquery,也是就是 (select id from basic where age >= 65)。括號內的語法會找出 basic 資料中年齡大於 65 歲的人,再傳回給外層的 query (select * from visit…) 繼續處理,也就是說,上述的這個 PROC SQL 的語法等同於:
proc sql;
select *
from visit
where id in ('111' '222');
quit;

- Subquery 2:找出至少一次檢查為血糖控制不佳 (HbA1c > 8%) 的人
proc sql;
select id, age, sex,
case
when id in
(select id from visit
where HbA1c > 8)
then 'poorly controlled diabetes'
else 'good controlled diabetes'
end as result
from basic;
quit;
subquery 不只在 WHERE 使用,也可以在 SELECT 中與 CASE WHEN 語法聯用。內層括號內的 subquery 會先找出在定期回診資料中血糖控制不佳的病人,再回傳給外層 query,完成 CASE WHEN 的分組。

- Subquery 3:在 basic 資料裡找出檢查地點為新北市的人
上述 subquery 1 & 2 的例子中所使用的都是獨立的 subquery,也就是括號內的語法可以獨自執行,與外部 query 彼此間沒有直接關聯。然而有時候內部的 subquery 會需要使用到外部 query 的值,此類型的 subqery 稱為 correlated subquery。Subquery 3 就是一個例子。
proc sql;
select *
from basic a
where 'New Taipei City'=
(select county from address b
where b.id=a.id);
quit;
為了在 basic 資料中連接到 address 資料以便找出檢查地點為新北市的病人,外部 query 會先從 basic 取出第一筆 (first row) 資料,接著把欄位 id 的值 (也就是第一筆病人 id = 111) 傳給內部的 subquery,此時的 subquery 就變成了:
(select county from address b
where b.id = 111);
subquery 接著搜索 id 為 111 的病人,並回傳其居住縣市 (county=Taipei) 的值給外部的 query ,再由 WHERE = ‘New Taipei City’ 進行篩選。
由於 id 為 111 的病人居住縣市為 Taipei 不是 New Taipei City,因此不符合條件而被排除。外部的 query 會依序把 basic 資料中的每一筆 (row) 皆按此方式處理,內部的 subquery 也會回傳適當的值給外部 query,直到資料的最後一筆。
這裡要注意的是,外部 query 的 WHERE 使用的運算子是等號「=」,這是因為此例中要比對的只有居住縣市 county 單一值,其他情況中 subquery 回傳的如果是多個值,就要使用 IN 或者 ANY、ALL 等運算子才不會產生錯誤。

除此之外,SAS SQL 亦提供條件式 (condition) 的運算符號,應用在 subquery 3 的時候,其寫法如下:
proc sql;
select *
from basic a
where exists
(select county from address b
where a.id=b.id and county='New Taipei City');
quit;
EXISTS condition 的運作方法為判斷 subquery (條件) 是否為真,也就是括號內條件是否存在,滿足條件即回傳值到外部 query,不滿足條件則不回傳任何值。以此例來說,第一筆資料 id=111 對於 subquery 來說等同於是:
(select county from address b
where 111=b.id
and county='New Taipei City');
id=111 的病人其居住縣市不是 New Taipei City,因此不回傳任何值。值得注意的是,EXISTS condition 與上述 correlated subquery 不同,此時 subquery 括號內的 WHERE 有指定篩選條件。
- Subquery 4:在 basic 資料裡計算平均血壓
/* GROUP BY only*/
proc sql;
select a.*, mean(FBG) as Avg_FBG
from basic a join visit b
on a.id=b.id
group by a.id, age, sex, edu, history;
quit;
/*Using a subquery with GROUP BY*/
proc sql;
select a.*, b.Avg_FBG
from basic a join
(select id, mean(FBG) as Avg_FBG
from visit
group by id) b
on a.id=b.id;
quit;
假如我們想要知道每位病人回診的空腹血糖平均值,並串聯其基本資料,這時候可以使用 GROUP BY 來幫我們計算。然而,在使用 GROUP BY 時,若想保留其他欄位,這些欄位都必須一一放進 GROUP BY 裡,如果要保留的欄位很多的時候 (甚至是所有的欄位),就會變成一件麻煩事。
好消息是,Subquery 可以在 FROM 使用並且與 GROUP BY 聯用,如此一來可以省去把欄位放進 GROUP BY 的苦差事。此時的 Subquery 可視為一個先計算過後的資料,再與外部 query 的資料進行 JOIN。

結論
subquery 是 SQL 語言中十分有用的工具,他可以靈活應用在幾乎任何一段 SQL 語句中,只要使用得宜,subquery 將是最有效率的幫手。除此之外,有時遭遇相嵌多層的 subquery 時,在眼花撩亂之餘,只需要記得 subquery 的原則永遠是從最內層開始執行處理,並層層向外傳遞,如此一來,再複雜的 subquery 都可迎刃而解。
High five to SUBQUERY and have fun !
參考資料
SAS® 9.4 SQL Procedure User’s Guide.
Howard Schreier. PROC SQL by Example: Using SQL within SAS.
Tasha Chapman. The Many Uses of SQL Subqueries.
Lei Zhang et al. Working with Subquery in the SQL procedure.