三章节查询语言SQL.ppt

《三章节查询语言SQL.ppt》由会员分享，可在线阅读，更多相关《三章节查询语言SQL.ppt（66页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Slide 1三章节查询语言SQL Still waters run deep.流静水深流静水深,人静心深人静心深 Where there is life,there is hope。有生命必有希望。有生命必有希望Slide 2内容提要内容提要SQL过程与过程与SQL语言简介语言简介lSQL语言简介语言简介lSQL过程简介过程简介lSQL中的语句及其作用中的语句及其作用从单个表中查询和加工数据；从单个表中查询和加工数据；l利用利用select 语句处理和加工数据；语句处理和加工数据；l创建新表存储查询结果；创建新表存储查询结果；从多个表中查询和拼接数据；从多个表中查询和拼接数据；l从多个数据表

2、查询数据实例从多个数据表查询数据实例Slide 3涉及章节涉及章节SAS编程技术教程第十九、二十、二十一章SASV8基础教程第六章帮助目录：SAS productsBase SASSAS SQL过程用户指南SAS productsBase SASSAS过程过程SQL过程Slide 4本讲目的本讲目的SQL是一种强大的查询语言，用它可以简化挑选和处理数据的程序。本讲的目的是：掌握掌握SQL过程的简单应用过程的简单应用;会使用会使用SQL查询语句处理单个数据集和多个数据集查询语句处理单个数据集和多个数据集的查询；的查询；会使用会使用SQL查询语句进行简单的求和、求均值等运查询语句进行简单的求和、求

3、均值等运算算Slide 53.1 SQL过程与过程与SQL语言简介语言简介 参见第十九章参见第十九章.Slide 6SQL的英文全称是Structured Query Language,翻译成汉语为“结构化查询语言结构化查询语言”，是在关系型数据库中广泛使用的一种标准化查询语言，通常使用它对数据表和基于表的数据视图进行查询和加工。（SQL术语中的表即为SAS术语中的数据文件（数据集、数据视图、索引）一、一、SQL语言语言Slide 7SQL术语与术语与SAS术语对应表术语对应表SQL术语SAS术语数据处理术语表(table)SAS数据文件(SAS data file)文件(file)行(row

4、)观测(observation)记录(record)列(column)变量(variable)字段(field)Slide 8二、二、SQL过程过程 在在SAS中可以使用中可以使用SQL过程调用过程调用SQL语言语言，实现，实现以下功能：以下功能：l读入、展示和加工读入、展示和加工SAS数据文件（数据集和数数据文件（数据集和数据视图）；据视图）；l在数据集中增加和修改数据值；在数据集中增加和修改数据值；l增加、修改和删除数据文件中的变量；增加、修改和删除数据文件中的变量；l合并表和视图中的数据；合并表和视图中的数据；l建立表、视图和索引；建立表、视图和索引；l生成报告；生成报告；Slide 9

5、SQL过程的简单形式过程的简单形式Proc SQL;查询表达式 其中查询表达式规定展示的数据和处理数据的其中查询表达式规定展示的数据和处理数据的 方式方式Slide 10PROC SQL特点特点 l因为因为PROC SQL继承了继承了SQL，所以，所以和其它和其它SAS过程步过程步有一定的区别有一定的区别。1)PROC SQL持续运行直至遇到持续运行直至遇到QUIT语句、语句、DATA步步,或其它或其它SAS过程。因此，不用在每个过程。因此，不用在每个SQL语句中重复语句中重复PROC SQL。例例3.1:列出列出data.stk000001的的2002年以后的年以后的date、stkcd值，

6、列出值，列出data.stk000002的收盘价大于开的收盘价大于开盘价的记录的盘价的记录的date、oppr，clpr,clpr-oppr的值的值,令令distance=clpr-oppr,查询结果按照查询结果按照date升序升序输出。输出。Slide 11proc sql;select stkcd,date from data.stk000001where year(date)=2002;select date,oppr,clpr,clpr-oppr as distance from data.stk000002 where clproppr order by date;quit;例中有两

7、个例中有两个SQL语句，每个语句用分号结束，写语句，每个语句用分号结束，写 第二个语句之前只有没有退出第二个语句之前只有没有退出SQL环境，就可以环境，就可以 不用再加不用再加proc sql；来声明。；来声明。Slide 122)SQL 过程语句在一句话中有多个从句，实现过程语句在一句话中有多个从句，实现不同的功能。不同的功能。在在例例3.13.1中，第一个中，第一个SQLSQL语句包含：语句包含：selectselect从句、从句、fromfrom从句和从句和wherewhere从句；从句；第二个第二个SQLSQL语句还包含语句还包含 as as从句和从句和 order by order

8、by从句。从句。3)SELECT语句在检索数据的同时会在输出窗语句在检索数据的同时会在输出窗口输出数据，使用口输出数据，使用NOPRINT选项可以阻止该选项可以阻止该项输出。项输出。在没有为查询到的结果指定数据集的情况下，在没有为查询到的结果指定数据集的情况下，SQLSQL将查询结果直接输出到输出（将查询结果直接输出到输出（outputoutput）窗）窗口口.Slide 134)用ORDER BY语句可以代替SORT过程来完成排序。5)RUN语句在PROC SQL语句中不起作用。Slide 14SQL过程中最主要、最常用的就是Select语句语句，使用SELECT语句可以识别、检索和操作表中

9、的数据：展示查询结果；展示查询结果；让数据以一定格式显示；让数据以一定格式显示；将报告在将报告在output窗口输出；窗口输出；另外为了将查询结果输出到数据集，需要用creat语句语句，在creat语句中，select语句就称为它的从句。SQL语言还有许多其他语句，这里不再介绍，可参考 帮助目录：Base SASSAS过程过程SQL过程三、三、SQL中的语句及其作用中的语句及其作用Slide 15Select语句的格式语句的格式SELECT object-item INTO macro-variable-specification FROM from-list GROUP BY group-b

10、y-item /*见SQL过程帮助*/ORDER BY order-by-item;Slide 16Select语句中的子句顺序是有严格规定的：语句中的子句顺序是有严格规定的：Select/*设定查询变量设定查询变量*/From /*给出数据来源给出数据来源*/Where/*列出查询需要满足的条件列出查询需要满足的条件*/Group by /*查询结果分组查询结果分组*/Having/*跟在跟在group by之后，限定分组条之后，限定分组条件件*/Order by/*给出查询结果的排序变量给出查询结果的排序变量,将将结果按排序变量排序结果按排序变量排序*/Slide 17Creat 语句格式

11、语句格式Creat table table-names as+select语句Slide 183.2 3.2 从单个表中查询和加工数据从单个表中查询和加工数据 参考章节：第二十章，第二十二章参考章节：第二十章，第二十二章Slide 193.2.1利用利用select 语句处理和加工数据语句处理和加工数据一、SELECT 子句（20.2节）语句格式语句格式 SELECT object-item FROM from-listSlide 20Select 子句应用子句应用选择所有列选择所有列例例3.2：选择数据集：选择数据集data.dret的所有列输出的所有列输出 proc sql outobs=

12、3;select*from data.dret;注：注：*可以代表数据源中所有的列Slide 21选择特定列选择特定列 语句格式：Select column-name from from-list例例3.33.3 输出data.lstkinfo中的股票代码(stkcd)和股票名称(lstknm)。proc sql;title 股票代码和名称;select stkcd,lstknm from data.lstkinfo;quit;Slide 22剔除查询结果中的重复观测剔除查询结果中的重复观测 语句格式：语句格式：=distinct例例3.4 输出data.yrret中不同的股票代码，并输出相应

13、的股票名称。proc sql;select distinct stkcd,lstknm /*distinct指一指一条记录中的被选变量值不完全相同条记录中的被选变量值不完全相同*/from data.yrret;quit;Slide 23计算新列值计算新列值 例例3.5 计算股票每日成交金额计算股票每日成交金额。proc sql outobs=3;title Trading Sum;select stkcd,lstknm,clpr*trdvol format=12.2 from data.qttndist;quit;Slide 24为列分配别名为列分配别名 例例3.6proc sql outo

14、bs=3;title Trading Sum;select stkcd,lstknm,clpr*trdvol as trdsum format=12.2 from data.qttndist;quit;语句格式：SELECT calculation-form 别名必须符合SAS名称要求，别名只在当前的查询中有效。Slide 25CALCULATED 语句语句 例例3.7proc sql outobs=3;select stkcd,lstknm,date,clpr*mcfacpr as adjpr format 8.2,(calculated adjpr*trdvol)as trdsum for

15、mat 12.2 from data.qttndist;quit;语句格式：Calculated Column-name使用别名引用一个计算过的列值时使用别名引用一个计算过的列值时，必须使用必须使用Calculated 关键词关键词，并将Calculated放在列名称之前，以此告知PROC SQL这个列是经计算得到的。Slide 26二、二、WHERE从句从句(20.4节节)可以对输出变量的观测进行条件选择，可以以可以对输出变量的观测进行条件选择，可以以选中的列为条件，也可以以未选择的列为条件。选中的列为条件，也可以以未选择的列为条件。语句格式语句格式WHERE sql-expression

16、选项说明：sql-expression（见sql-expression定义）.Sql-expression可以是：常数、列变量名、SAS函数、汇总函数、SAS表达式等Slide 27例例3.8 用WHERE语句选择1991年以前上市的股票。proc sql;select lstknm,lstdt from data.lstkinfo where lstdt31dec1991d;quit;Where从句的应用从句的应用Slide 28例例3.9 使用使用IN算符用法算符用法。proc sql outobs=3;select lstknm,stkcd from data.lstkinfo wher

17、e stkcd in(000001 600651 000004);quit;Slide 29例例3.10 使用BETWEEN-AND算符选择满足一定范围的观测。proc sql;select*from resdat.lstkinfowhere lstdt between 1jan1991d and 31dec1991d;quit;Slide 30例例3.11 使用匹配算符使用匹配算符LIKE选择观测。选择观测。proc sql;select stkcd,lstknm from resdat.lstkinfo where lstknm like ST%;quit;Slide 31三、三、ORDE

18、R BY语句排序语句排序 可以对表中的观测进行排序，被排序的列可以可以对表中的观测进行排序，被排序的列可以是是select子句的列、被计算出的列和没有被选子句的列、被计算出的列和没有被选择的列择的列。语句格式语句格式ORDER BY order-by-item,.order-by-item;Slide 32 integer等同于一个列在SELECT子句中的位置column-name列的名称或者别名sql-expression见附录中的sql-expressionASC升序排列数据，默认方式DESC降序排列数据选项说明：order-by-item 可以是如下：Slide 33例例3.12 对股票

19、上市时间列进行排序对股票上市时间列进行排序。proc sql outobs=3;select lstknm,lstdt from data.lstkinfoorder by lstdt;quit;按指定的变量排序按指定的变量排序Slide 34例例3.13 按第按第4列排序列排序proc sql outobs=3;select stkcd,lstknm,date,clpr*mcfacpr as adjpr format 8.2from data.qttndistorder by 4 desc;/*等价于order by adjpr desc*/quit;按指定列在按指定列在SELECTSELE

20、CT子句中的整数位置排序。子句中的整数位置排序。Slide 35课堂实践任务课堂实践任务1.A)从data.exchbdqttn_1中选择债券代码、日期、债券名称、收盘全价、收盘净价、净价成交金额、成交笔数，并生成year、qtr、month三个变量，存储日期的年、季、月，年限范围为2000年至2010年；b)将上述结果存储到数据集ex.3_1中；c)对数据集ex.3_1按照年、季、月排序；d).删除ex.3_1中成交笔数为0和收盘净价缺失的数据，将剩余结果保存到数据集ex.3_2中Slide 362.将数据集ex.3_2中的数据导出到excel表格ex3_2.XLS3.查询data.exch

21、bdqttn_1中所有不同的股票代码；课堂实践任务课堂实践任务Slide 37四、使用汇总函数汇总数据四、使用汇总函数汇总数据 使用汇总函数（使用汇总函数（summary function）可以产生数）可以产生数据的统计量。据的统计量。函数函数定义定义函数函数定义定义AVG,MEANCount,FREQ,NCSSCVMAXMINNMISSPRT均值均值非缺失值数据个数非缺失值数据个数修正平方和修正平方和变异系数变异系数(百分比百分比)最大值最大值最小值最小值缺失值个数缺失值个数T分布中绝对值大分布中绝对值大于该值的概率于该值的概率RANGESTDSTDERRSUMSUMWGTTUSSVAR取值

22、范围取值范围标准差标准差均值的标准差均值的标准差求和求和加权和加权和总体均值是否为总体均值是否为0的的t值值未修正平方和未修正平方和方差方差Slide 38使用使用SUM函数函数 例例3.143.14 使用使用sumsum函数计算深发展历年派发现金红函数计算深发展历年派发现金红利总额。利总额。proc sql;select sum(dividend)format=8.2 as totledv from data.stk000001;quit;/*注：这里的注：这里的sum()是计算一个变量所有观测的加是计算一个变量所有观测的加和，而和，而data步中的步中的sum(x1,x2)是求不同变量的当

23、前是求不同变量的当前记录加和。记录加和。*/Slide 39观测数汇总观测数汇总 语句格式：语句格式：Select count(distinct)as 例例3.15 a)统计在统计在data.qttndist中有多少不同的中有多少不同的stkcdproc sql;title Number of different stkcd;select count(distinct stkcd)as number from data.qttndist;Quit;Slide 40b)统计统计data.lstkinfo中有中有Lzipcd为非缺失值的记录个数为非缺失值的记录个数proc sql;select c

24、ount(lzipcd)as number from data.lstkinfo;Quit;c)统计统计data.lstkinfo中的记录个数中的记录个数proc sql;select count(*)as number from data.lstkinfo;Quit;Slide 41使用使用avg求平均值求平均值例例3.16 3.16 求计算列的平均值求计算列的平均值Proc sql;Select clpr*trdvol as avgpr,avg(calculated avgpr)as totalfrom data.stk000001;quit;另一方法：另一方法：Proc sql;Sele

25、ct avg(clpr*trdvol)as totalfrom data.stk000001;quit;Slide 42五、使用五、使用GROUP BY子句进行分组汇总子句进行分组汇总 设定分组标志，并将观测进行分组。同时使用汇设定分组标志，并将观测进行分组。同时使用汇总函数时，将分组对数据进行汇总；当没有汇总总函数时，将分组对数据进行汇总；当没有汇总函数时，等同于函数时，等同于order by。语句格式语句格式GROUP BY group-by-item group-by-item选项说明：integer等同于一个列在SELECT子句中的位置Column-name列的名称或别名Sql-exp

26、ression见附录sql-expressionSlide 43例例3.18 分组进行汇总分组进行汇总a)对对data.monretdata.monret中的中的monretmonret分股票求分股票求2005年年的月收益均值的月收益均值proc sql;title 2005年股票月收益平均值;select stkcd,avg(monret)from data.monret where 1jan2005d=date=31dec2005d group by stkcd;quit;Slide 44B）order by 对分组后结果进行排序对分组后结果进行排序proc sql;title 2005年

27、股票月收益平均值;select stkcd,avg(monret)as amret from data.monret where 1jan2005d=date=31dec2005d group by stkcd;order by calculated amret desc;quit;Slide 45六、用六、用HAVING子句选择分组数据子句选择分组数据 对对group by的分组，挑选出满足条件的组输出。的分组，挑选出满足条件的组输出。（where放在放在group by前，对组选择不起作用）前，对组选择不起作用）语句格式语句格式 Slide 46例例3.19 算出算出A股市场股票股市场股票

28、2005年的交易天数年的交易天数。proc sql;select stkcd,count(*)as trday from data.dretwhere 1jan2005d=date=31dec2005dgroup by stkcdhaving substr(stkcd,1,1)in(0,6)or substr(stkcd,1,2)=99;quit;Slide 47例例3.20 列出列出2005年交易天数不小于年交易天数不小于240天的股票天的股票proc sql;select stkcd,count(*)as trday from data.dretwhere 1jan2005d=date=2

29、40;quit;Slide 483.2.2 创建新表存储查询结果创建新表存储查询结果从查询结果创建表，使用create table语句，格式为：CREATE TABLE table-name AS query-expression Slide 49例例3.21 用用Create Table语句从股票信息表创建的查语句从股票信息表创建的查询结果表。询结果表。proc sql outobs=3;create table stkinfo as select stkcd,lstknm,lstdt from data.lstkinfo;quit;Slide 503.3 从多个表中查询和拼接数据从多个表中

30、查询和拼接数据 参见第二十一章参见第二十一章Slide 51在面向多个数据表的加工中，常用在面向多个数据表的加工中，常用SQL进行数进行数据表的匹配横向并接，使用据表的匹配横向并接，使用SQL实现数据表的匹实现数据表的匹配并接配并接并不需要将原有的数据集按匹配字段排序并不需要将原有的数据集按匹配字段排序，而且匹配字段在不同的数据表中可以有不同的名而且匹配字段在不同的数据表中可以有不同的名称称。为了用过程为了用过程SQL拼接拼接SAS数据集，必须数据集，必须l在在from从句中列举读入的数据集；从句中列举读入的数据集；l用用where从句说明行（观测）是如何匹配的从句说明行（观测）是如何匹配的S

31、lide 52例例3.22 表china与表 usa的简单连接程序：proc sql;select*from data.china,data.usa;quit;多表查询的简单应用多表查询的简单应用过程SQL实现两个表匹配并解的一般形式为：Proc sql;select 列1,from 表1 temp-name1,表2 temp-name2 where 表1.变量表2.变量;Slide 53 level china level usa -0 c01 1 u00 0 c01 2 u01 0 c01 2 u02 0 c01 3 u03 0 c01 4 u04 1 c02 1 u00 1 c02 2

32、u01 1 c02 2 u02 1 c02 3 u03 1 c02 4 u04 2 c03 1 u00 2 c03 2 u01 2 c03 2 u02 2 c03 3 u03 2 c03 4 u04 3 c04 1 u00 3 c04 2 u01 3 c04 2 u02 3 c04 3 u03 3 c04 4 u04Slide 54使用表名或表的别名使用表名或表的别名 通常的查询时会遇到两个表有相同名字的列，为了在引用时不产生混淆，需要在列名前加上表名或者表的别名。Slide 55例例3.23.只对相同水平的运动员进行连接只对相同水平的运动员进行连接中国队和美国队打乒乓球比赛，队员分成5个水平

33、，每个水平的球员都要和对手同一水平的队员打一场比赛，基础表数据如下。China Playerlevel china0 c011 c022 c033 c04USA playerlevel usa1 u002 u012 u023 u034 u04Slide 56proc sql;select*from data.china,data.usawhere china.level=usa.level;quit;level china level usa-1 c02 1 u002 c03 2 u012 c03 2 u023 c04 3 u03Slide 57从多个表中查询数据从多个表中查询数据 例例3.2

34、4 为比较不同股票间的相对价格变化，从data.idx.399106中提取data.stk000001,data.stk000002,data.stk00004共有的交易日数据，存储在ex.price中，将data.idx.399106，data.stk000001 data.stk000002,data.stk00004的收盘价clpr分别改名为clpr0-clpr3，在ex.price中保存date,clpr0-clpr3。Slide 58例例3.25.从从data.lstkinfo中挑选出股票代码在中挑选出股票代码在data.sampstk中的记录中的记录proc sql;select

35、stkcd,lstknm,lstdt from data.lstkinfowhere stkcd in(select stkcd from data.sampstk);quit;使用子查询语句选择数据使用子查询语句选择数据Slide 59MERGE语句和语句和JOIN连接比较连接比较 所有行匹配无重复值情况所有行匹配无重复值情况 两个表中的by变量的值都相等且没有重复值的时候，可以使用一个内部连连接来产生同样的效果。例例3.26 BY变量值相等且没有重复值。Table a Table bcode manager code Assitant 145 Max 145 Tracy 150 Jack

36、150 Yao 155 Paul 155 Chen程序如下：data merge1;merge a b;by code;run;proc print data=merge1 noobs;title Table MERGE1;run;Slide 60 Table MERGE1code manager Assitant145 Max Tracy 150 Jack Yao 155 Paul ChenMerge在合并前的两个数据集已经按code排过序，而PROC SQL则不需要排序，下面程序给出和上面同样的结果。proc sql;title Table MERGE1;select a.code,a.m

37、anager,b.Assitant from a,b where a.code=b.code;quit;Slide 61有重复值情况有重复值情况 当用来连接两个表的列变量或者BY组中有重复值时，Merge和Proc sql的处理方式有所区别。例例21.9 BY组中有重复值。Table newone Table newtwocode Manager code Assistant 145 Max 145 Jerry 145 Xam 145 Tracy 155 Paul 155 ChenData步 data merge3;merge a b;by code;run;proc print data=m

38、erge3 noobs;title Table MERGE3;run;Table Merge3code Manager Assitant145 Max Jerry145 Xam Tracy155 Paul Chen Slide 62若用SQL，则会出现下面的结果：Proc sql;Title Table Merge3;Select a.code,a.manager,b.assistant From a full join b On a.code=b.code;quit;Table Merge3 code manager assistant-145 Max Jerry145 Max Tracy1

39、45 Xam Jerry145 Xam Tracy155 Paul ChenSlide 63课堂实践任务课堂实践任务1.利用数据集data.dret计算各股票日平均收益，并按平均收益进行排序2.对上次课堂实践中获得的ex.ex3_1按季度分组计算可转债总交易量和总成交金额，存储在数据集ex.sum_qtr中,该数据集应该还有年，季两个时间指标；3.从从data.lstkinfo中挑选出股票代码不在中挑选出股票代码不在data.sampstk中的记录中的记录Slide 641.以2008年A股上市公司股票为样本，找出在2008年摘帽的公司（即公司名称中ST被去掉的）2.2009年首次被ST的上市

40、公司3.查找截止到2009年9月30日，一直没有被特别处理的股票。4.计算data.dret中各股票的月累计收益率，增加变量positive,若月累计收益率大于0，值为1，否则值为0.作业作业作业作业Slide 655.试由以下的9种德国马克对美元汇率看跌期权和9种英镑对美元汇率的看跌期权产生81种组合。一年期，DM/USD汇率的9种不同看跌期权的执行价格和成本执行价格Kdm成本 Cdm0.660.0858550.650.0321910.640.0207950.630.0170010.620.0137110.610.0108510.600.0083880.590.0062910.550.001

41、401一年期，BP/USD汇率的9种不同看跌期权的执行价格和成本执行价格 Kbp成本 Cbp1.300.1372131.250.0826451.200.0450601.150.0283481.100.0161461.050.0078601.000.0032770.950.0011340.900.000245作业作业Slide 666.很多情况下，读者可能会综合数据库中多张表的内容，来更直观更方便地获得全面信息。比如，需要把同一公司的主要财务比率和财务指标放在一起来研究整个公司。数据合并举例：Is_old数据集：利润及利润分配表_旧准则。Finind_Old数据集：财务指标_旧准则。Scf_ol

42、d数据集：现金流量表_旧准则。需要把这三个表中的以下变量综合起来：Is_old表:主营业务利润(Mopeprf),营业利润(Opeprf),营业外收支净额=营业外收入Noperev-营业外支出Nopeexp，利润总额(Totprf),净利润Finind_Old表：每股收益摊薄(EPS)，流动比率(Currt)，速动比率(Qckrt)，资产负债率(Dbassrt)，每股净资产(NetassPS)，销售净利率(Netprfrt)，每股经活动产生的现金流量净额(OpeCFPS)，总资产周转率(次)(Totassrat)，净利润（扣除非经常性损益后Netprfcut）Scf_old表:经营活动产生的现金流量净额(NCFope)，现金及现金等价物净增加额(NetincrCCE).作业作业