三章节查询语言SQLppt课件

1、Slide 1第三讲第三讲 查询语言查询语言SQLSQL金融学院金融学院 冯建芬冯建芬博学楼博学楼 913, 64495048danxin_97yahooSlide 2内容提要内容提要SQL过程与过程与SQL语言简介语言简介SQL语言简介语言简介SQL过程简介过程简介SQL中的语句及其作用中的语句及其作用从单个表中查询和加工数据；从单个表中查询和加工数据；利用利用select 语句处理和加工数据；语句处理和加工数据；创建新表存储查询结果；创建新表存储查询结果；从多个表中查询和拼接数据；从多个表中查询和拼接数据；从多个数据表查询数据实例从多个数据表查询数据实例Slide 3涉及章节涉及章节SAS

2、编程技术教程第十九、二十、二十一章SASV8基础教程第六章帮助目录：SAS productsBase SASSAS SQL过程用户指南SAS productsBase SASSAS过程过程SQL过程Slide 4本讲目的本讲目的SQL是一种强大的查询语言，用它可以简化挑选和处理数据的程序。本讲的目的是：掌握SQL过程的简单应用;会使用SQL查询语句处理单个数据集和多个数据集的查询；会使用SQL查询语句进行简单的求和、求均值等运算Slide 53.1 SQL过程与过程与SQL语言简介语言简介 参见第十九章参见第十九章.Slide 6SQL的英文全称是Structured Query Langua

3、ge, 翻译成汉语为“结构化查询语言” ，是在关系型数据库中广泛使用的一种标准化查询语言，通常使用它对数据表和基于表的数据视图进行查询和加工。（SQL术语中的表即为SAS术语中的数据文件数据集、数据视图、索引）一、一、SQL语言语言Slide 7SQL术语与术语与SAS术语对应表术语对应表SQL术语SAS术语数据处理术语表(table)SAS数据文件(SAS data file)文件(file)行(row)观测(observation)记录(record)列(column)变量(variable)字段(field)Slide 8二、二、SQL过程过程 在在SAS中可以使用中可以使用SQL过程调

4、用过程调用SQL语言语言 ，实现，实现以下功能：以下功能：读入、展示和加工读入、展示和加工SAS数据文件数据集和数据数据文件数据集和数据视图）；视图）；在数据集中增加和修改数据值；在数据集中增加和修改数据值；增加、修改和删除数据文件中的变量；增加、修改和删除数据文件中的变量；合并表和视图中的数据；合并表和视图中的数据；建立表、视图和索引；建立表、视图和索引；生成报告；生成报告；Slide 9SQL过程的简单形式过程的简单形式Proc SQL; 查询表达式 其中查询表达式规定展示的数据和处理数据的其中查询表达式规定展示的数据和处理数据的 方式方式Slide 10PROC SQL特点特点 l因为因

5、为PROC SQL继承了继承了SQL，所以和其它，所以和其它SAS过程步过程步有一定的区别。有一定的区别。 l1) PROC SQL持续运行直至遇到持续运行直至遇到QUIT语句、语句、DATA步步,或其它或其它SAS过程。因而，不用在每个过程。因而，不用在每个SQL语句中重复语句中重复PROC SQL。l例例3.1: 列出列出data.stk000001的的2019年以后的年以后的date、stkcd值，列出值，列出data.stk000002的收盘价大于开盘价的记录的的收盘价大于开盘价的记录的date、oppr，clpr, clpr-oppr的值的值, 令令distance=clpr-opp

6、r, 查询结果按照查询结果按照date升序输出。升序输出。Slide 11proc sql;select stkcd,date from data.stk000001where year(date)=2019;select date, oppr,clpr,clpr-oppr as distance from data.stk000002 where clproppr order by date;quit; 例中有两个例中有两个SQL语句，每个语句用分号结束，写语句，每个语句用分号结束，写 第二个语句之前只有没有退出第二个语句之前只有没有退出SQL环境，就可以环境，就可以 不用再加不用再加pro

7、c sql；来声明。；来声明。Slide 122) SQL 过程语句在一句话中有多个从句，实现过程语句在一句话中有多个从句，实现不同的功能。不同的功能。在例在例3.1中，第一个中，第一个SQL语句包含：语句包含：select从句、从句、from从句和从句和where从句；从句；第二个第二个SQL语句还包含语句还包含 as从句和从句和 order by从句。从句。3) SELECT语句在检索数据的同时会在输出窗语句在检索数据的同时会在输出窗口输出数据，使用口输出数据，使用NOPRINT选项可以阻止该选项可以阻止该项输出。项输出。 在没有为查询到的结果指定数据集的情况下，在没有为查询到的结果指定数

8、据集的情况下，SQL将查询结果直接输出到输出将查询结果直接输出到输出output窗窗口口.Slide 134) 用ORDER BY语句可以代替SORT过程来完成排序。5) RUN语句在PROC SQL语句中不起作用。Slide 14SQL过程中最主要、最常用的就是Select语句，使用SELECT语句可以识别、检索和操作表中的数据：展示查询结果；让数据以一定格式显示；将报告在output窗口输出；另外为了将查询结果输出到数据集，需要用creat语句，在creat语句中，select语句就称为它的从句。SQL语言还有许多其他语句，这里不再介绍，可参考 帮助目录： Base SASSAS过程过程S

9、QL过程三、三、SQL中的语句及其作用中的语句及其作用Slide 15Select语句的格式语句的格式SELECT object-item INTO macro-variable-specification FROM from-list GROUP BY group-by-item /*见见SQL过程帮助过程帮助*/ORDER BY order-by-item ; Slide 16Select语句中的子句顺序是有严格规定的：语句中的子句顺序是有严格规定的： Select /*设定查询变量设定查询变量*/ From /*给出数据来源给出数据来源*/ Where /*列出查询需要满足的条件列出查询

10、需要满足的条件*/ Group by /*查询结果分组查询结果分组*/ Having /* 跟在跟在group by之后，限定分组条之后，限定分组条件件*/ Order by /*给出查询结果的排序变量给出查询结果的排序变量,将结将结果按排序变量排序果按排序变量排序*/Slide 17Creat 语句格式语句格式Creat table table-names as+select语句Slide 183.2 3.2 从单个表中查询和加工数据从单个表中查询和加工数据 参考章节：第二十章，第二十二章参考章节：第二十章，第二十二章Slide 193.2.1利用利用select 语句处理和加工数据语句处理

11、和加工数据一、SELECT 子句20.2节）语句格式 SELECT object-item FROM from-listSlide 20Select 子句应用子句应用选择所有列选择所有列例例3.2 ：选择数据集：选择数据集data.dret的所有列输出的所有列输出 proc sql outobs=3; select * from data.dret; 注：注：* 可以代表数据源中所有的列可以代表数据源中所有的列Slide 21选择特定列选择特定列 语句格式：Select column-name from from-list例例3.3 3.3 输出输出data.lstkinfodata.lstk

12、info中的股票代码中的股票代码(stkcd)(stkcd)和股票名称和股票名称(lstknm)(lstknm)。proc sql;proc sql;title title 股票代码和名称股票代码和名称;select stkcd,lstknm from data.lstkinfo;select stkcd,lstknm from data.lstkinfo;quit;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

14、outobs=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

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

16、ql-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 fro

17、m data.lstkinfo where 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 wher

18、e lstknm like ST%;quit;Slide 31三、三、ORDER BY语句排序语句排序 可以对表中的观测进行排序，被排序的列可以可以对表中的观测进行排序，被排序的列可以是是select子句的列、被计算出的列和没有被选子句的列、被计算出的列和没有被选择的列。择的列。语句格式语句格式ORDER BY order-by-item , . order-by-item ; Slide 32 integer等同于一个列在SELECT子句中的位置column-name列的名称或者别名sql-expression见附录中的sql-expressionASC升序排列数据，默认方式DESC降序排列

19、数据选项说明： order-by-item 可以是如下：Slide 33例例3.12 对股票上市时间列进行排序。对股票上市时间列进行排序。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; /*

20、等价于等价于order by adjpr desc*/quit; 按指定列在按指定列在SELECTSELECT子句中的整数位置排序。子句中的整数位置排序。Slide 35课堂实践任务课堂实践任务 A)从data.exchbdqttn_1中选择债券代码、日期、债券名称、收盘全价、收盘净价、净价成交金额、成交笔数，并生成year、 qtr、 month三个变量，存储日期的年、季、月，年限范围为2000年至2019年； b)将上述结果存储到数据集ex.3_1中； c)对数据集ex.3_1按照年、季、月排序； d).删除ex.3_1中成交笔数为0和收盘净价缺失的数据，将剩余结果保存到数据集ex.3_2

21、中Slide 362.将数据集ex.3_2中的数据导出到excel表格ex3_2.XLS3. 查询data.exchbdqttn_1中所有不同的股票代码；课堂实践任务课堂实践任务Slide 37四、使用汇总函数汇总数据四、使用汇总函数汇总数据 使用汇总函数使用汇总函数summary function可以产生数可以产生数据的统计量。据的统计量。 函数函数定义定义函数函数定义定义AVG,MEANCount,FREQ,NCSSCVMAXMINNMISSPRT均值均值非缺失值数据个数非缺失值数据个数修正平方和修正平方和变异系数变异系数(百分比百分比)最大值最大值最小值最小值缺失值个数缺失值个数T分布中

22、绝对值大分布中绝对值大于该值的概率于该值的概率RANGESTDSTDERRSUMSUMWGTTUSSVAR取值范围取值范围标准差标准差均值的标准差均值的标准差求和求和加权和加权和总体均值是否为总体均值是否为0的的t值值未修正平方和未修正平方和方差方差Slide 38使用使用SUM函数函数 例例3.14 3.14 使用使用sumsum函数计算深发展历年派发现金红函数计算深发展历年派发现金红利总额。利总额。proc sql;proc sql;select sum(dividend) format=8.2 as totledv select sum(dividend) format=8.2 as t

23、otledv from data.stk000001;from data.stk000001;quit; quit; / /* *注：这里的注：这里的sum()sum()是计算一个变量所有观测的加是计算一个变量所有观测的加和，而和，而datadata步中的步中的sum(x1,x2)sum(x1,x2)是求不同变量的当是求不同变量的当前记录加和。前记录加和。* */ /Slide 39观测数汇总观测数汇总 语句格式：语句格式：Select count(distinct ) as 例例3.15 a)统计在统计在data.qttndist中有多少不同的中有多少不同的stkcdproc sql;tit

24、le Number of different stkcd;select count(distinct stkcd) as number from data.qttndist;Quit;Slide 40b) 统计统计data.lstkinfo中有中有Lzipcd为非缺失值的记录个数为非缺失值的记录个数proc sql;select count(lzipcd) as number from data.lstkinfo;Quit;c) 统计统计data.lstkinfo中的记录个数中的记录个数proc sql;select count(*) as number from data.lstkinfo;

25、Quit;Slide 41使用使用avg求平均值求平均值例例3.16 3.16 求计算列的平均值求计算列的平均值Proc sql;Proc sql;Select clprSelect clpr* *trdvol as avgpr, trdvol as avgpr, avg(calculated avgpr) as totalavg(calculated avgpr) as totalfrom data.stk000001;from data.stk000001;quit; quit; 另一方法：另一方法：Proc sql;Proc sql;Select avg(clprSelect avg(c

26、lpr* *trdvol) as totaltrdvol) as totalfrom data.stk000001;from data.stk000001;quit;quit;Slide 42五、使用五、使用GROUP BY子句进行分组汇总子句进行分组汇总 设定分组标志，并将观测进行分组。同时使用汇设定分组标志，并将观测进行分组。同时使用汇总函数时，将分组对数据进行汇总；当没有汇总总函数时，将分组对数据进行汇总；当没有汇总函数时，等同于函数时，等同于order by。语句格式语句格式GROUP BY group-by-item group-by-item选项说明： integer等同于一个列在

27、SELECT子句中的位置Column-name列的名称或别名Sql-expression见附录sql-expressionSlide 43例例3.18 分组进行汇总分组进行汇总a)对对data.monret中的中的monret分股票求分股票求2019年年的月收益均值的月收益均值proc sql;title 2019年股票月收益平均值年股票月收益平均值;select stkcd, avg(monret) from data.monret where 1jan2019d=date=31dec2019d group by stkcd;quit;Slide 44Border by 对分组后结果进行排序

28、proc sql;title 2019年股票月收益平均值;select stkcd, avg(monret) as amret from data.monret where 1jan2019d=date=31dec2019d group by stkcd; order by calculated amret desc;quit;Slide 45六、用六、用HAVING子句选择分组数据子句选择分组数据 对对group by的分组，挑选出满足条件的组输出。的分组，挑选出满足条件的组输出。（where放在放在group by前，对组选择不起作用）前，对组选择不起作用）语句格式语句格式 Slide 4

29、6例例3.19 算出算出A股市场股票股市场股票2019年的交易天数。年的交易天数。proc sql;select stkcd,count(*) as trday from data.dretwhere 1jan2019d=date=31dec2019dgroup by stkcdhaving substr(stkcd,1,1) in (0,6) or substr(stkcd,1,2)=99;quit;Slide 47例例3.20 列出列出2019年交易天数不小于年交易天数不小于240天的股票天的股票proc sql;select stkcd,count(*) as trday from da

30、ta.dretwhere 1jan2019d=date=240;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;q

31、uit;Slide 503.3 从多个表中查询和拼接从多个表中查询和拼接数据数据 参见第二十一章参见第二十一章Slide 51在面向多个数据表的加工中，常用在面向多个数据表的加工中，常用SQL进行数进行数据表的匹配横向并接，使用据表的匹配横向并接，使用SQL实现数据表的匹实现数据表的匹配并接并不需要将原有的数据集按匹配字段排序，配并接并不需要将原有的数据集按匹配字段排序，而且匹配字段在不同的数据表中可以有不同的名而且匹配字段在不同的数据表中可以有不同的名称。称。为了用过程为了用过程SQL拼接拼接SAS数据集，必需数据集，必需在在from从句中列举读入的数据集；从句中列举读入的数据集；用用whe

32、re从句说明行观测是如何匹配的从句说明行观测是如何匹配的Slide 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

33、2 u02 0 c01 3 u03 0 c01 4 u04 1 c02 1 u00 1 c02 2 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

34、.只对相同水平的运动员进行连只对相同水平的运动员进行连接接中国队和美国队打乒乓球比赛，队员分成5个水平，每个水平的球员都要和对手同一水平的队员打一场比赛，基础表数据如下。 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 u0

35、12 c03 2 u023 c04 3 u03Slide 57从多个表中查询数据从多个表中查询数据 例例3.24 为比较不同股票间的相对价格变化，从为比较不同股票间的相对价格变化，从data.idx.399106中提取中提取data.stk000001, data.stk000002, data.stk00004共有的交易日数共有的交易日数据，存储在据，存储在ex.price中，将中，将data.idx.399106 ，data.stk000001 data.stk000002, data.stk00004的收盘价的收盘价clpr分别改名为分别改名为clpr0-clpr3，在，在ex.pric

36、e中保存中保存date,clpr0-clpr3。Slide 58例例3.25. 从从data.lstkinfo中挑选出股票代码在中挑选出股票代码在data.sampstk中的记录中的记录proc sql;select stkcd,lstknm,lstdt from data.lstkinfowhere stkcd in (select stkcd from data.sampstk);quit;使用子查询语句选择数据使用子查询语句选择数据Slide 59MERGE语句和语句和JOIN连接比较连接比较 所有行匹配无重复值情况所有行匹配无重复值情况 两个表中的by变量的值都相等且没有重复值的时候，

37、可以使用一个内部连连接来产生同样的效果。例3.26 BY变量值相等且没有重复值。Table a Table bcode 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; Slide 60 Table MERGE1code manager Assitant145 Max Tracy 150 Jack Yao 15

38、5 Paul ChenMerge在合并前的两个数据集已经按code排过序，而PROC SQL则不需要排序，下面程序给出和上面同样的结果。proc sql; title Table MERGE1; select a.code, a.manager, 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 Ass

39、istant 145 Max 145 Jerry 145 Xam 145 Tracy 155 Paul 155 ChenData步 data merge3; merge a b; by code;run;proc print data=merge3 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