《SAS编程与数据挖掘商业案例》学习笔记之十七
继续读书笔记,本次重点sas sql语句,因为sql内容多且复杂,本文仅仅介绍商业应用中经常使用的而且easy出错的地方,内容包含:单表操作、多表关联、子查询以及merge和join的差别
1.单表操作
eg1:
Proc sql outobs=10。 *可选项,功能类似于data步中的obs数据集选项
Select
From sashelp.class(where=(height between 50 and 70))
Group by name,calculated sex_tran;
eg2:
proc sql;
create table test1 as
select
id,
max (txn_seq) as txn_seq,
sum (amount) as sum_amt
from chap11.having1
group by id
having calculated sum_amt ge 70
;
quit;
注:having语句出如今group by后面,假设没哟group by语句,系统默认having语句和where语句效果同样
proc sql;
create table test2 as
select
id,
txn_seq,
amount
from chap11.having1
group by id
having txn_seq= max (txn_seq)
;
quit;
注:having语句和group by语句同一时候出现时,select后面不一定须要汇总函数如sum等,上例中。依照每个id取最大的txn_seq
proc sql;
create table test3 as
select
id,
txn_seq,
amount
from chap11.having2
group by id
having txn_seq = max (txn_seq)
;
quit;
having语句和max或min结合使用时,一定要注意having后面的变量在每个id中的唯一性。
2.多表关联
左联接在join变量是一对一的情况下,假设where在表的外面,则where条件是先关联表。后筛选数据,假设where在表中,则是先筛选数据后关联表,and也是先筛选数据后关联表。因而表内写where和表外写and是全然一样的。
下面程序。2和3全然一样,可是1和后面两个不一样
eg:
proc sql;
case
when b.rmb_id eq . then a.id
;
quit;
proc sql;
create table leftjoin2 as
select
case
when b.rmb_id eq . then a.id
else b.rmb_id
end as all_rmb,
a.id,
b.rmb_id,
b.usd_id
from
chap11.left_join_1 as a
left join chap11.left_join_2
(where=(rmb_id le 4)) as b
on a.id=b.usd_id
;
quit;
proc sql;
create table leftjoin3 as
select
case
when b.rmb_id eq . then a.id
else b.rmb_id
end as all_rmb,
a.id,
b.rmb_id,
b.usd_id
from chap11.left_join_1 as a
left join chap11.left_join_2 as b
on a.id=b.usd_id
and rmb_id le 4
;
quit;
3.子查询
in子查询效率比join低非常多,而exist子查询效率更低;
4.merge和sql的比較
在“一对一”和“多对一”的情况下,效果全然同样。可是在“多对多”情况下。则全然不同。
创建測试数据集
data a1; input x y @@; cards;
101 1 103 30 104 5 ; run;
|
data a2; input x y @@; cards; 101 10 102 30
103 5 ; run; |
data a3; input x y z1 @@; cards; 101 11 1 102 33 2 102 300 3 104 5 4 ; run; |
data a4; input x y z2 @@; cards; 101 1 5 102 30 6 102 5 7 102 100 8 102 200 9 105 50 10 ; run; |
eg1:求a1和a2的交集
sql实现: |
merge实现 |
proc sql; select a1.x ,a2.y from a1 join a2 on a1.x=a2.x ; quit; |
proc sort data=a1;by x;run; proc sort data=a2;by x;run; data a12; merge a1(in=ina) a2(in=inb); by x; if ina and inb; proc print;run; |
eg2:用数据集a2来更新数据集a1
sql实现: |
merge实现 |
proc sql; select a1.x ,case when a2.y is not null then a2.y else a1.y end as yy from a1 left join a2 on a1.x=a2.x ; quit; |
proc sort data=a1;by x;run; proc sort data=a2;by x;run;
data a12; merge a1(in=ina) a2(in=inb); by x; if ina; proc print;run; |
注:sql通过左联接方式实现。merge通过if ina方式实现。等价于左联接
eg3:用数据集a2来更新数据集a1,同一时候保留两个数据集全部观測
sql实现: |
merge实现 |
proc sql; select coalesce(a1.x,a2.x) as x ,coalesce(a2.y,a1.y) as yy from a1 full join a2 on a1.x=a2.x ; quit; |
proc sort data=a1;by x;run; proc sort data=a2;by x;run;
data a12; merge a1 a2; by x;
proc print;run; |
注:sql通过全连接方式实现,需求中须要用a2全部变量更新a1,所以一定要把a2变量放在前面,被更新的数据集放在后面,可是对join的匹配变量,对这样的顺序无要求;merge没有使用in=选项,等价于全连接;
eg4:多对多
sql实现 |
merge实现 |
proc sql; select a3.x,a4.y ,a3.z1,a4.z2 from a3 join a4 on a3.x=a4.x ; quit; |
proc sort data=a3;by x;run; proc sort data=a4;by x;run;
data a34; merge a3(in=ina) a4(in=inb); by x; if ina and inb; run; proc print;run; |
注:sql形式会出现反复值,匹配到的数据会进行笛卡尔。
而merge则由于if ina and inb的作用,避免了反复
5.创建表
复制已有的表属性
proc sql;
create table a
like sashelp.class;
describe table a;
quit;
6.行操作
加入行操作
使用set语句 |
使用value语句 |
使用select语句 |
proc sql; insert into countries set name='bangladesh', capital='dhaka', population=126391060 set name='japan', capital='tokyo', population=126352003; quit; |
proc sql; insert into countries values ('pakistan', 'islamabad', 123060000) values ('nigeria', 'lagos', 99062000); quit; |
proc sql; create table newconntries like countries; insert into newconntries select * from countries where population ge 120000000; quit; |
最后注意:
多表关联时。最好不要超过5个表。除非都是代码表。否则sql会产生非常大的暂时空间。从而减少程序性能
除非必须,否则优先使用关联,而不要用子查询
在使用set操作符时。假设set表没有反复行,则使用union all会提高性能
假设有索引。尽可能用索引和where语句结合
尽量避免多对多join
相关文章
- Spring学习笔记(六)——SpringMVC 概述和入门案例
- 一篇案例读懂国央企如何实现数字化管控
- CSS笔记(27)之旋转木马案例
- 移动端笔记(8)之bootstrap案例
- 【案例赏析】百词斩APP,这些设计细节大家要学起来~
- PQ实战案例拆解 | 汇总多股票交易数据,计算5日均线的操作与算法优化 - 2
- POSTGRESQL 存储过程--如何写出新版本PG的存储过程的小案例
- 佛萨奇2.0波场链/币安链/马蹄链DAPP智能合约系统开发成功案例及源码交付
- 「Docker学习系列教程」10-Docker容器数据卷案例
- SpringMVC 入门案例
- Web Spider XHR断点 堆栈跟值 逆向案例(三)
- Cypress学习笔记4——编写第二个测试脚本(登录案例)
- 【运筹学】线性规划 人工变量法 ( 人工变量法案例 | 第一次迭代 | 中心元变换 | 检验数计算 | 选择入基变量 | 选择出基变量 )
- RabbitMQ 七种队列模式应用场景案例分析(通俗易懂)
- 客户案例 GoldenGate Replicat 进程延迟问题处理
- 研究:患者案例强烈暗示辉瑞COVID-19疫苗与贝尔面瘫之间的联系
- Oracle数据库修改主键的实践案例(oracle修改主键6)
- OraclePL/SQL入门案例实践