1、第20章 SQL從單個表中檢索數(shù)據(jù),清華大學經(jīng)管學院 朱世武,SELECT語句綜述,SELECT語句是PROC SQL的主要工具。使用SELECT語句可以識別、檢索和操作表中的數(shù)據(jù)，使用SELECT子句可以設定查詢條件。,SELECT語句格式,SELECT object-item FROM from-list ;,SELECT 子句,語句格式 SELECT object-item FROM from-list,選擇所有列,語句格式： COLUMN-NAME= * (asterisk) select * from from-list SELECT子句中，“*”號表示選擇表中的所有列。,例：pro

2、c sql outobs=3; select * from ResDat.dret;,選擇特定列,語句格式： Select column-name from from-list,例20.1 選擇特定列。 proc sql outobs=3; title 股票代碼; select stkcd from resdat.lstkinfo; proc sql outobs=3; title 股票代碼和名稱; select stkcd,lstknm from resdat.lstkinfo; quit;,用DISTINCT語句剔除查詢結(jié)果中重復觀測,語句格式： =distinct,例20.2 刪除重復觀

3、測。 proc sql; select distinct stkcd from ResDat.yrret; quit;,創(chuàng)建說明列,語句格式：SELECT TEXT， COLUMN-NAME,例20.4 創(chuàng)建說明列。 proc sql outobs=3; select Stock Code for, lstknm, is, stkcd from ResDat.lstkinfo; quit;,輸出窗口結(jié)果：,計算新列值,語句格式：Calculating Values =Calculating Expression of columns,例20.5 計算股票每日成交金額。 proc sql out

4、obs=3; title Trading Sum; select stkcd, lstknm, clpr*trdvol format=12.2 from ResDat.qttndist; quit;,為列分配別名,語句格式：SELECT calculation-form 別名必須符合SAS名稱要求，別名只在當前的查詢中有效。,例20.6 為列分配別名。 proc sql outobs=3; title Trading Sum; select stkcd, lstknm, clpr*trdvol as trdsum format=12.2 from ResDat.qttndist; quit;,

5、CALCULATED 語句,語句格式：Calculated Column-name 使用別名引用一個計算過的列值時，必須使用Calculated 關(guān)鍵詞，并將Calculated放在列名稱之前，以此告知PROC SQL這個列是經(jīng)計算得到的。,proc sql outobs=3; select stkcd,lstknm,date,clpr*mcfacpr as adjpr format 8.2, (calculated adjpr*trdvol) as trdsum format 12.2 from resdat.qttndist; quit;,CASE表達式,語句格式： CASE WHEN w

6、hen-condition THEN result-expression END,例20.7 簡單形式的Case表達式。 proc sql outobs=3; select stkcd,lstknm,lstdt, case when exchflg=1 then 上交所 when exchflg=2 then 深交所 else 所有交易所 end as exchflg from resdat.lstkinfo order by lstdt; quit; 例中，將股票信息表lstkinfo交易所標識（exchflg）的編碼值進行轉(zhuǎn)換，同時得到新的交易所標識列及相關(guān)數(shù)值。,使用ORDER BY語句

7、排序,語句格式 ORDER BY order-by-item ;,選項說明： order-by-item 可以是如下：,例20.9 對股票上市時間列進行排序。 proc sql outobs=3; select lstknm,lstdt from resdat.lstkinfo order by lstdt; quit;,可以通過指定列在SELECT子句中的整數(shù)位置對該列排列。 例20.11 按第4列排序。 proc sql outobs=3; select stkcd,lstknm,date,clpr*mcfacpr as adjpr format 8.2 from resdat.qttnd

8、ist order by 4 desc; quit;,用WHERE語句選擇觀測,語句格式 WHERE sql-expression 選項說明： sql-expression （見sql-expression定義）.,例20.13 用WHERE語句選擇1991年以前上市的股票。 proc sql outobs=3; select lstknm, lstdt from resdat.lstkinfo where lstdt31dec1991d; quit;,使用IN算符 例20.14 簡單IN算符用法。 proc sql outobs=3; select lstknm, stkcd from re

9、sdat.lstkinfo where stkcd in (000001 600651 000004); quit;,使用BETWEEN-AND 算符,例20.16 使用BETWEEN-AND算符選擇滿足一定范圍的觀測。 proc sql; create table stkinfo1991 as select * from resdat.lstkinfo where lstdt between 1jan1991d and 31dec1991d; quit;,使用LIKE算符,例20.17 使用匹配算符LIKE選擇觀測。 proc sql; select stkcd,lstknm from re

10、sdat.lstkinfo where lstknm like ST%; quit;,使用匯總函數(shù)匯總數(shù)據(jù),使用匯總函數(shù)（aggregate function or summary function）可以產(chǎn)生數(shù)據(jù)的統(tǒng)計量。,用WHERE子句匯總數(shù)據(jù),例20.19 使用乘法函數(shù)算出代碼為000002的股票調(diào)整后的收盤價。 proc sql outobs=3; select stkcd,lstknm,clpr*mcfacpr as adjpr from resdat.qttndist where stkcd=000002; quit;,使用SUM函數(shù),例20.20 使用sum函數(shù)計算深發(fā)展歷年派發(fā)

11、現(xiàn)金紅利總額。 proc sql; select sum(dividend) format=8.2 as totledv from resdat.stk000001; quit;,觀測數(shù)匯總,語句格式： Select count(distinct ) as ,proc sql; title Number of total Rows; select count(*) as number from resdat.lstkinfo; quit;,使用GROUP BY子句進行分組匯總,語句格式 ,選項說明：,例20.22 分組進行匯總。 proc sql outobs=3; title 2005年股票

12、月收益平均值; select stkcd, avg(monret) from resdat.monret where 1jan2005d=date=31dec2005d group by stkcd; quit;,用HAVING子句選擇分組數(shù)據(jù),語句格式 ,例20.25 算出A股市場股票2005年的交易天數(shù)。 proc sql; select stkcd,count(*)as trday from resdat.dret where 1jan2005d=date=31dec2005d group by stkcd having substr(stkcd,1,1) in (0,6) or sub

13、str(stkcd,1,2)=99; quit;,第21章 SQL從多個表中檢索數(shù)據(jù),清華大學經(jīng)管學院 朱世武 Z Resdat樣本數(shù)據(jù)： SAS論壇： ,本章內(nèi)容： 使用連接從多個表中選取數(shù)據(jù)； 使用子查詢通過表與表之間的聯(lián)系選取數(shù)據(jù)； 使用SET算符合并查詢結(jié)果。 如果沒有另外說明，本章的PROC SQL語對表和視圖都適用。,使用連接語句JOIN從多個表中檢索數(shù)據(jù),最基本的連接就是兩個表的簡單連接。 例21.1 表china與表 usa的簡單連接程序： proc sql; select * from resdat.china, resdat.usa; quit;,JOIN方式分類,通常連接

14、Join方式分為兩種類型： 內(nèi)部join，只返回連接表中匹配連接條件的行。 外部join，是內(nèi)部連接的補充，還包括除內(nèi)部連接部分以外不符合連接條件的行。 外部連接分三種：left 左連接,right右連接 and full完全連接。,內(nèi)部連接,數(shù)據(jù)格式 Proc sql; Select object-item from table-name alias, table-name alias Where sql-expression;,例21.2 只對相同水平的運動員進行連接。 proc sql; select * from resdat.china, resdat.usa where china

15、.level=usa.level; quit;,使用表的別名,通常的查詢時會遇到兩個表有相同名字的列，為了在引用時不產(chǎn)生混淆，需要在列名前加上表名或者表的別名。 語句格式: From table-name table-alias,proc sql; select * from resdat.china as a, resdat.usa as b where a.level=b.level; quit;,使用關(guān)鍵詞INNER JOIN的內(nèi)部連接,語句格式： From table-name JOIN table-name ON sql-expression,proc sql; select * f

16、rom resdat.china a inner join resdat.china b on a.level=b.level; order by level; quit;,關(guān)鍵詞INNER JOIN用于連接多個表的數(shù)據(jù)。關(guān)鍵詞inner是可選的，語句中用ON代替了原來設定匹配條件的where語句。,從多于兩個表的數(shù)據(jù)集中查詢數(shù)據(jù),例21.6 簡單的多表連接。 proc sql outobs=3; select a.stkcd,b.lstknm,c.clpr from resdat.sampstk a,resdat.lstkinfo b,resdat.qttndist c where a.st

17、kcd=b.stkcd and b.stkcd=c.stkcd and a.stkcd=c.stkcd; quit;,不同形式的外部連接,語句格式 From table-name LEFT JOIN | RIGHT JOIN | FULL JOIN,左外部連接,proc sql; select * from resdat.china a left join resdat.usa b on a.level=b.level; quit;,結(jié)果比以前的內(nèi)部連接多了一行，該行就是Table china與Table usa不匹配的行，不匹配行中Table usa的列為缺失值。,右外部連接,proc sq

18、l; select * from resdat.china a right join resdat.usa b on a.level=b.level; quit;,結(jié)果比以前的內(nèi)部連接多了一行，該行就是Table usa與Table china不匹配的行，不匹配行中Table china的列都是缺失值。,完全外部連接,proc sql; select * from resdat.china a full join resdat.usa b on a.level=b.level; quit;,結(jié)果顯示，兩個表中所有不匹配的行都出現(xiàn)在完全連接的輸出結(jié)果中。,MERGE語句和JOIN連接比較,所有行

19、匹配無重復值情況,兩個表中的by變量的值都相等且沒有重復值的時候，可以使用一個內(nèi)部連連接來產(chǎn)生同樣的效果。 例21.7 BY變量值相等且沒有重復值。 Table a Table b code manager code Assitant 145 Max 145 Tracy 150 Jack 150 Yao 155 Paul 155 Chen 程序如下： data merge1; merge a b; by code; run;,proc print data=merge1 noobs; title Table MERGE1; run;,Merge在合并前的兩個數(shù)據(jù)集已經(jīng)按code排過序，而PRO

20、C SQL則不需要排序，下面程序給出和上面同樣的結(jié)果。 proc sql; title Table MERGE1; select a.code, a.manager, b.Assitant from a, b where a.code=b.code; quit;,有重復值情況,當用來連接兩個表的列變量或者BY組中有重復值時，Merge和Proc sql的處理方式有所區(qū)別。 例21.9 BY組中有重復值。 Table newone Table newtwo code Manager code Assistant 145 Max 145 Jerry 145 Xam 145 Tracy 155 Pa

21、ul 155 Chen Data步 data merge3; merge a b; by code; run; proc print data=merge3 noobs; title Table MERGE3; run;,若用SQL，則會出現(xiàn)下面的結(jié)果： Proc sql; Title Table Merge3; Select a.code, a.manager, b.assistant From a full join b On a.code=b.code; quit;,使用子查詢語句選擇數(shù)據(jù),語言格式： (select .from.);,產(chǎn)生單個值的子查詢,例21.10 考慮例21.9的兩

22、個表。 Proc sql; Title Which Manager has the same code as Assistant Chen; Select * From a Where code eq (select code from b where assistant=Chen); Quit；,產(chǎn)生多個值的子查詢,例21.11 根據(jù)表sampstk中給定股票的股票代碼，從表lstkinfo中選出相應的股票信息。 Proc sql; select stkcd,lstknm,lstdt from resdat.lstkinfo where stkcd in (select stkcd from resdat.sampstk);