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;

age more than 65 — **定期回診資料中年齡大於65歲的長者**

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 的分組。

hba1c more than 8 — **至少一次檢查為血糖控制不佳 (HbA1c > 8%) 的人**

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.

什麼是 Subquery?

以 SAS 為例

結論

參考資料

Published by Epic Data Studio

如有任何問題和意見歡迎提出取消回覆