MaxCompute支持通过select
语句查询数据。本文为您介绍select
命令格式及如何实现嵌套查询、分组查询、排序等操作。
执行select
操作前需要具备目标表的读取数据权限(Select)。授权操作请参见授权。
功能介绍
select
语句用于从表中选取满足指定条件的数据。您可以根据实际场景结合以下功能完成多样化的查询操作。
类型 | 功能 |
---|---|
子查询(SUBQUERY) | 在某个查询的执行结果基础上进一步执行查询操作时,可以通过子查询操作实现。 |
交集、并集和补集 | 对查询结果数据集执行取交集、并集或补集操作。 |
JOIN | 通过join 操作连接表并返回符合连接条件和查询条件的数据信息。
|
SEMI JOIN(半连接) | 通过右表过滤左表的数据,右表的数据不出现在结果集中。 |
MAPJOIN HINT | 对一个大表和一个或多个小表执行join 操作时,可以在select 语句中显式指定mapjoin Hint提示以提升查询性能。
|
Lateral View(行转列) | 通过Lateral View与UDTF(表生成函数)结合,将单行数据拆成多行数据,即行转列。 |
GROUPING SETS | 对数据进行多维度的聚合分析。 |
SELECT TRANSFORM | select transform 语法允许您启动一个指定的子进程,将输入数据按照一定的格式通过标准输入至子进程,并且通过解析子进程的标准输出获取输出数据。
|
使用限制
- 当使用
select
语句时,屏显最多只能显示10000行结果。当select
语句作为子句时则无此限制,select
子句会将全部结果返回给上层查询。 select
语句查询分区表时默认禁止全表扫描。自2018年1月10日20:00:00后,在新创建的项目上执行SQL语句时,默认情况下,针对该项目里的分区表不允许执行全表扫描操作。在查询分区表数据时必须指定分区,由此减少SQL的不必要I/O,从而减少计算资源的浪费以及按量计费模式下不必要的计算费用。
如果您需要对分区表进行全表扫描,可以在全表扫描的SQL语句前加上命令set odps.sql.allow.fullscan=true;
,并和SQL语句一起提交执行。假设sale_detail
表为分区表,需要同时执行如下语句进行全表查询:set odps.sql.allow.fullscan=true; select * from sale_detail;
如果整个项目都需要开启全表扫描,项目空间Owner执行如下命令打开开关:setproject odps.sql.allow.fullscan=true;
命令格式
select [all | distinct] <select_expr>, <select_expr>, ...
from <table_reference>
[where <where_condition>]
[group by <col_list>]
[having <having_condition>]
[order by <order_condition>]
[distribute by <distribute_condition> [sort by <sort_condition>] ]
[limit <number>]
命令中各字段的执行语序请参见SELECT语序。
示例数据
--创建一张分区表sale_detail。
create table if not exists sale_detail
(
shop_name string,
customer_id string,
total_price double
)
partitioned by (sale_date string, region string);
--向源表增加分区。
alter table sale_detail add partition (sale_date='2013', region='china');
--向源表追加数据。
insert into sale_detail partition (sale_date='2013', region='china') values ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
列表达式(select_expr)
select_expr
格式为col1_name, col2_name, 列表达式,...
,表示待查询的普通列、分区列或正则表达式。列表达式使用规则如下。
序号 | 描述 | 样例 |
---|---|---|
① | 用列名指定要读取的列。 | 读取表sale_detail 的列shop_name 。命令示例如下: 返回结果如下:
|
② | 用星号(* )代表查询所有的列。可配合where 子句指定过滤条件。
|
|
③ | 可以使用正则表达式。 |
|
④ | 在选取的列名前可以使用distinct 去掉重复字段,只返回去重后的值。使用all 会返回字段中所有重复的值。不指定此选项时,默认值为all 。
|
|
目标表信息(table_reference)
table_reference
表示查询的目标表信息。目标表使用规则如下。
序号 | 描述 | 样例 |
---|---|---|
① | 直接指定目标表名。 | 命令示例如下: 返回结果如下:
|
② | 嵌套子查询。 | 命令示例如下: 返回结果如下:
|
WHERE子句(where_condition)
可选。where
子句为过滤条件。如果表是分区表,可以实现列裁剪。使用规则如下。
序号 | 描述 | 样例 |
---|---|---|
① | 配合关系运算符,筛选满足指定条件的数据。关系运算符包含:
详情请参见关系运算符。 |
在where 子句中,您可以指定分区范围,只扫描表的指定部分,避免全表扫描。命令示例如下: 返回结果如下:
|
② | 通过UDF实现分区裁剪,将UDF语句先当作一个小作业执行,再将执行的结果替换到原来UDF出现的位置。实现的方式有以下两种:
|
无。 |
GROUP BY分组查询(col_list)
group by
和聚合函数配合使用,根据指定的普通列、分区列或正则表达式进行分组。group by
使用规则如下。
序号 | 描述 | 样例 |
---|---|---|
① | group by 操作优先级高于select 操作,因此group by 的取值是select 输入表的列名或由输入表的列构成的表达式。需要注意的是:
|
|
② | 当SQL语句设置了属性,即set odps.sql.groupby.position.alias=true; ,group by 中的整型常量会被当做select 的列序号处理。
|
命令示例如下: 返回结果如下:
|
HAVING子句(having_condition)
having
子句与聚合函数一起使用,实现过滤。命令示例如下:--为直观展示数据呈现效果,向sale_detail表中追加数据。
insert into sale_detail partition (sale_date='2014', region='shanghai') values ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);
--使用having
子句配合聚合函数实现过滤。
select region,sum(total_price) from sale_detail
group by region
having sum(total_price)<305;
返回结果如下:+------------+------------+
| region | _c1 |
+------------+------------+
| shanghai | 200.9 |
+------------+------------+
ORDER BY全局排序(order_condition)
可选。order by
用于对所有数据按照指定普通列、分区列或指定常量进行全局排序。order by
使用规则如下。
序号 | 描述 | 样例 |
---|---|---|
① | 默认对数据进行升序,如果降序排序,需要使用desc 关键字。
|
|
② | order by 默认要求带limit 数据行数限制,没有limit 会返回报错。如您需要解除order by 必须带limit 的限制,详情请参见LIMIT NUMBER限制输出行数>解除ORDER BY必须带LIMIT的限制。
|
|
③ | 在使用order by 排序时,NULL会被认为比任何值都小,这个行为与MySQL一致,但是与Oracle不一致。
|
查询表sale_detail的信息,并按照total_price升序排列前5条。命令示例如下: 返回结果如下:
|
④ | order by 后面需要加上select 列的别名。当select 某列时,如果没有指定列的别名,则列名会被作为列的别名。
|
order by 加列的别名。命令示例如下: 返回结果如下:
|
⑤ | 当SQL语句设置了属性,即set odps.sql.orderby.position.alias=true; ,order by 中的整型常量会被当做select 的列序号处理。
|
命令示例如下: 返回结果如下:
|
⑥ | offset 和order by...limit 语句配合,可以指定跳过的行数。
|
将表sale_detail按照total_price升序排序后,输出第4~6行。命令示例如下: 返回结果如下:
|
DISTRIBUTE BY哈希分片(distribute_condition)
可选。distribute by
用于对数据按照某几列的值做Hash分片。
distribute by
控制Map(读数据)的输出在Reducer中是如何划分的,如果不希望Reducer的内容存在重叠,或需要对同一分组的数据一起处理,您可以使用distribute by
来保证同组数据分发到同一个Reducer中。
select
的输出列别名,当select
某列时,如果没有指定列的别名,则列名会被作为列的别名。命令示例如下:--查询表sale_detail中的列region值并按照region值进行哈希分片。
select region from sale_detail distribute by region;
--等价于如下语句。
select region as r from sale_detail distribute by region;
select region as r from sale_detail distribute by r;
SORT BY局部排序(sort_condition)
可选。通常,配合distribute by
使用。sort by
使用规则如下。
序号 | 描述 | 样例 |
---|---|---|
① | sort by 默认对数据进行升序,如果降序排序,需要使用desc 关键字。
|
|
② | 如果sort by 语句前有distribute by ,sort by 会对distribute by 的结果按照指定的列进行排序。
|
|
③ | 如果sort by 语句前没有distribute by ,sort by 会对每个Reduce中的数据进行局部排序。
保证每个Reduce的输出数据都是有序的,从而增加存储压缩率,同时读取时如果有过滤,能够减少真正从磁盘读取的数据量,提高后续全局排序的效率。 |
命令示例如下: 返回结果如下:
|
order by|distribute by|sort by
的取值必须是select
语句的输出列,即列的别名。列的别名可以为中文。- 在MaxCompute SQL解析中,
order by|distribute by|sort by
执行顺序在select
操作之后,因此它们的取值只能为select
语句的输出列。 order by
不和distribute by
、sort by
同时使用,group by
也不和distribute by
、sort by
同时使用。
LIMIT限制输出行数(number)
可选。limit <number>
中的number
是常数,用于限制输出行数。
limit
基于分布式系统对数据进行扫描后过滤,您无法通过limit
减少返回数据量进而减少计算费用。
当您涉及到如下场景时,可参考对应解决方案处理:
场景 | 解决方案 |
---|---|
解除order by 必须带limit 的限制
|
因为order by 需要对单个执行节点做全局排序,所以默认带limit 限制,避免误用导致单点处理大量数据。如果您的使用场景确实需要order by 放开limit 限制,可以通过如下两种方式实现:
|
解除屏显限制 | 当使用无limit 的select 语句或limit 的number 数量超过设置的屏显上限时,如果您直接从屏显窗口查看结果,最多只能输出屏显上限设置的行数。
每个项目空间的屏显上限可能不同,您可以参考如下方法控制:
说明 您可以执行
show SecurityConfiguration; 命令查看ProjectProtection 属性配置。如果ProjectProtection=true ,根据项目空间数据保护需求判断是否关闭数据保护机制。如果可以关闭,通过set ProjectProtection=false; 命令关闭。ProjectProtection 属性默认不开启。项目空间数据保护机制详情请参见项目空间的数据保护。
|
在文档使用中是否遇到以下问题
更多建议
匿名提交