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;
basic
visit
address

  • 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 等運算子才不會產生錯誤。

basic 資料裡檢查地點為新北市的人

除此之外,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。

basic 資料裡的平均血壓

結論

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.

如有任何問題和意見歡迎提出