5.2 使用子句的查询
1）统计浙江省销量大于某量的销售城市排名 ：
select city,sum(amt) as total_amt from t_dml where province='浙江省' group by city having count(*)>1 and sum(amt) > 2000 order by total_amt desc limit 10;
2）城市排名统计
select city, cnt, amt from t_dml distribute by city sort by cnt;
第 6 章：数据更新
6.1 追加记录
1） // insert into table: 追加插入
（提示：本部分使用到表dual ,上面的实验步骤 第3章3小节 已建立，如果未建立此表，请先执行如下语句建立此表：
create table dual (X string); ----创建此表 insert into table dual select count(*) from dual; ---插入一条数据，数值类型会自动转换 select * from dual ; ---检查数据
insert into table t_dml select -1,'1900-01-01 00:00:00','','',0,0,0 from dual;
//检查结果
select * from t_dml where detail_id=-1;
2）分区表数据操作
—添加分区:
alter table t_dml_p add if not exists partition (sale_date='2015-01-01');
—往分区添加数据：
insert into table t_dml_p partition (sale_date='2015-01-01') select -1, '', '', 0, 0, 0 from dual;
检查数据，结果如图：
select * from t_dml_p where sale_date='2015-01-01' ;
6.2 覆盖插入记录
1）覆盖插入非分区数据表：
insert overwrite table t_dml select -2,'1900-01-01 00:00:00', '', '',0,0,0 from dual; select * from t_dml where detail_id in (-1,-2);
（为了编辑方便、直观，我们也可以到控制台上进行处理）如图：
选中执行操作，执行插入语句，点击“运行”，出现下述系统检查SQL语法界面：
检查无错误，点击“运行”,成功执行出现FINISH
查看结果：（执行选中检查语句 ）
select * from t_dml where detail_id in (-1,-2);
2）分区表数据操作 （依次执行下面的三个操作，添加分区、插入数据、检查分区表数据）
alter table t_dml_p add if not exists partition (sale_date='2015-01-01'); --添加分区 insert overwrite table t_dml_p partition (sale_date='2015-01-01') --插入数据 select -2, '', '', 0, 0, 0 from dual; select * from t_dml_p where sale_date='2015-01-01' ; ---检查数据
(说明：分区表一般需附带分区条件，避免全分区扫描，在操作未加分区条件的分区表时，MaxCompute提示告警 “full scan with all partitions” )
3）清空分区表
使用覆盖插入的方式清空非分区表
insert overwrite table t_dml select * from t_dml where 1=2; --清空操作
count t_dml; --核查数据
4）使用覆盖插入的方式清空某个分区
insert overwrite table t_dml_p partition(sale_date='2015-01-01') select detail_id, province, city, product_id, cnt, amt from t_dml_p where 1=2;
select count(*) from t_dml_p where sale_date='2015-01-01';
5） 也可以通过删除分区的方式清空分区
alter table t_dml_p drop if exists partition (sale_date='2015-01-01'); select count(*) from t_dml_p where sale_date='2015-01-01';
6）重新加载数据，准备下面的实验(命令示例：Tunnel upload –c GBK “自己的文件目录”\t_dml.csv t_dml ;）
第 7 章：多路输出
7.1 实验场景
多路输出即在一个语句中插入不同的结果表或者分区，实验场景说明：
7.2 实验操作
1）增加分区，依次增加两个分区，操作如下
alter table t_dml_p add if not exists partition (sale_date='20150501'); alter table t_dml_p add if not exists partition (sale_date='20150502');
2）创建实验临时表
创建表 create table t_dml_01 like t_dml;
3）将表t_dml中的数据按不同条件同时输出到新建的表和新建的t_dml_p的两个分区中
参考语句
from t_dml insert into table t_dml_01 select detail_id,sale_date,province,city,product_id,cnt,amt where detail_id > 5340000 insert overwrite table t_dml_p partition (sale_date='20150501') select detail_id,province,city,product_id,cnt,amt where sale_date >= '2015-05-01 00:00:00' and sale_date <= '2015-05-01 23:59:59' insert overwrite table t_dml_p partition (sale_date='20150502') select detail_id,province,city,product_id,cnt,amt where sale_date >= '2015-05-02 00:00:00' and sale_date <= '2015-05-02 23:59:59' ;
第 8 章：动态分区
8.1 分区背景
动态分区即分区数值不是直接写死的，是由参数控制或数据中的实际数据控制的。
实际应用中通常会出现类似将表 t_dml 中的数据插入到分区表 t_dml_p中去的现象，由于分区个数多，不能手工逐个处理，太麻烦，需要动态分区。
8.2 分区实验
1）删除上步建立的分区：
alter table t_dml_p drop if exists partition (sale_date='20150501'); alter table t_dml_p drop if exists partition (sale_date='20150502');
2）利用动态分区直接完成数据插入：
insert into table t_dml_p partition(sale_date) select detail_id, province, city, product_id, cnt, amt,to_char(sale_date, 'yyyymmdd') as sale_date from t_dml;
3）利用overwrite 覆盖原来的分区
insert overwrite table t_dml_p partition(sale_date) select detail_id, province, city, product_id, cnt, amt, to_char(sale_date, 'yyyymmdd') as sale_date from t_dml;
第 9 章：join操作
9.1 加载实验数据
1）切换到客户端，切换至目录\ODPS_DEMO\resources\03-SQL（具体目录随自己而定），找到t_product_crt.sql文件，创建表，也可以直接将文件中的内容粘贴到控制台处理（或直接从附件下载实验文件）
（如果执行命令报找不到文件错误，建议执行命令写全路径，示例如下：
自己的目录\odpscmd –f 自己的目录\ODPS_DEMO\resources\03-SQL\t_product_crt.sql ）
执行结果如图：
2） 加载数据 t_product.csv：进入交互界面，加载数据，如下图（目录为参考）：
(命令示例： tunnel upload 自己的目录盘\ODPS_DEMO\resources\03-SQL\t_product.csv )
9.2 join操作
1） 普通的JOIN操作
业务背景：事实表t_dml包含了销售记录信息，其中字段 product_id为产品标识，可以关联另一张维表t_product获得产品的说明信息，现在想通过SQL得到针对产品大类的销售金额统计：
// 按照产品分类（category_name）统计销售金额 //1-left outer join
select t2.category_name, sum(t1.amt) from t_dml t1 left outer join t_product t2 on t1.product_id=t2.product_id group by t2.category_name;
//2-inner join (join)
select t2.category_name, sum(t1.amt) from t_dml t1 inner join t_product t2 on t1.product_id=t2.product_id group by t2.category_name;
//3-right outer join
select t2.category_name, sum(t1.amt) from t_dml t1 right outer join t_product t2 on t1.product_id=t2.product_id group by t2.category_name;
select t1.category_name, sum(t2.amt) from t_product t1 right outer join t_dml t2 on t1.product_id=t2.product_id group by t1.category_name;
//4-full outer join
select t2.category_name, sum(t1.amt) from t_dml t1 full outer join t_product t2 on t1.product_id=t2.product_id group by t2.category_name;
第 10 章：MapJoin HINT
10.1 场景介绍
由于各种原因，造成销售信息表 t_dml 中的记录存在一些质量问题，可能的问题包括：
1- 产品标识错误： 可以通过单价判断，单价相等的标识不同，则可能存在错误
2- 价格错误：如果销售记录中的平均单价高于产品维表中的定价，则可能存在问题
请协助发现这些可能存在问题的记录。
10.2 MapJoin HINT操作
1） left outer join 实现质量问题
select /*+mapjoin(t2)*/t1.*,t1.amt/t1.cnt,t2.product_id,t2.price from t_dml t1 left outer join t_product t2 on t1.product_id<>t2.product_id and t1.amt/t1.cnt = t2.price where t2.price is not null;
2）inner join (join) 实现质量问题
select /*+mapjoin(t1)*/ t1.*, t1.amt/t1.cnt,t2.product_id, t2.price from t_dml t1 inner join t_product t2 on t1.product_id=t2.product_id or t1.amt/t1.cnt - t2.price < 0.01;
3）right outer join: 重写left outer join实现的逻辑
select /*+mapjoin(t1)*/t2.*,t2.amt/t2.cnt,t1.product_id,t1.price from t_product t1 right outer join t_dml t2 on t1.product_id<>t2.product_id and t2.amt/t2.cnt = t1.price where t1.price is not null;
注意：在做关联时，如果关联条件比较复杂（比如包含 or 等连接条件）或者是关联条件中存在非等值关联（比如大于、小于或者不等于等），则普通的 join 无法实现，可以采用带有 mapjoin HINT 的 join 方式。
第 11 章：子查询
11.1 简单查询
ODPS SQL 支持将子查询作为一张表来用，可以用于简单查询、join等。在使用中，必须为子查询指定别名。
子查询用于简单查询，如： select * from (select distinct province from t_dml) t; 执行结果：
11.2 join子查询
1） 子查询用于join
select t2.category_name, sum(t1.amt) from (select * from t_dml where amt > 800) t1 inner join t_product t2 on t1.product_id=t2.product_id group by t2.category_name;
2） 子查询用于mapjoin
select /*+mapjoin(t1)*/t2.*,t2.amt/t2.cnt,t1.product_id,t1.price from t_product t1 right outer join (select * from t_dml where detail_id > 5340000) t2 on t1.product_id=t2.product_id and t2.amt/t2.cnt <> t1.price where t1.price is not null;
第 12 章：SQL联合与条件表达式
12.1 联合处理UNION ALL
在销售记录中，由于实际售卖价钱和产品的标称价并不一致，如果想获得产品的所有出现过的单价（包括实际售卖价和标称价），则采用如下语句处理：
select * from ( select product_id,price, 'STD' type from t_product UNION ALL select distinct product_id, amt/cnt as price, 'USED' type from t_dml ) t order by product_id,type,price desc limit 100;
12.2 条件表达式CASE WHEN
如果市场部准备做一次市场营销活动，对于一次购买3-5个产品的，在目前的售价上实行9折优惠，一次购买6个及以上产品的，给与8折优惠。请基于5月份数据想评估一下此次活动的成本（为了简单可行，活动成本定义为目前销售额减掉优惠后的销售额）。则处理语句如下：
select sum(amt)-sum(case when cnt>=6 then amt*0.8 when cnt>=3 then amt*0.9 else amt end) cost from t_dml;
第 13 章：SQL执行结果的验证
13.1 简单SQL通过结果直接验证
简单SQL通过执行结果判定是否正确，如通过简单查询语句验证：
如验证分区是否存在： select * from t_dml_p where sale_date='20150501' ;
数据显示，表明SQL正常执行。
13.2 复杂SQL验证
复杂SQL不容易理解或从结果中无法直接得到信息，采用逆向抽样的方法，如上述实验中涉及到的，利用动态分区快速插入数据
insert into table t_dml_p partition(sale_date) select detail_id, province, city, product_id, cnt, amt, to_char(sale_date, 'yyyymmdd') as sale_date from t_dml;
1）先检查分区是否存在
2）执行删除分区的操作
alter table t_dml_p drop if exists partition (sale_date='20150501');
3）核查操作结果，则需要进行抽样验证，上步操作即检查删除的分区是否存在
如：
select * from t_dml_p where sale_date ='抽样数据' （sale_date=‘20150101’）
第 14 章：课后任务
14.1 课后任务
思考题 ：
在常规的数据需求中，通常会遇到类似的统计查询，从一个千万级别甚至上亿级别的记录表中，将数据按不同条件分散到不同的表中，如按省份将各省数据分装到不同的表中，如果数据源表在MaxCompute中，采用哪种方式比较简单？相反如果统计所有省的数据，每个省数据放置在不同表中或不同分区中，考虑这样又如何做，效率如何？
课后练习：
假设一个学生期末考试成绩单，包含学号、课程、课程得分三列信息，请写出按每个课程的学生成绩排名？
