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，并添加数据，命令示例如下：
--创建一张分区表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。命令示例如下：
select shop_name from sale_detail;
返回结果如下：
+------------+
| shop_name  |
+------------+
| s1         |
| s2         |
| s3         |
+------------+
② 用星号（*）代表查询所有的列。可配合where子句指定过滤条件。
  • 读取表sale_detail中所有的列。命令示例如下：
    --开启全表扫描，仅此Session有效。
set odps.sql.allow.fullscan=true;
select * from sale_detail;
    返回结果如下：
    +------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+
  • 在where子句中指定过滤条件。命令示例如下：
    select * from sale_detail where shop_name='s1';
    返回结果如下：
    +------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
+------------+-------------+-------------+------------+------------+
③ 可以使用正则表达式。
  • 选出sale_detail表中所有列名以sh开头的列。命令示例如下：
    select `sh.*` from sale_detail;
    返回结果如下：
    +------------+
| shop_name  |
+------------+
| s1         |
| s2         |
| s3         |
+------------+
  • 选出sale_detail表中列名不为shop_name的所有列。命令示例如下：
    select `(shop_name)?+.+` from sale_detail;
    返回结果如下：
    +-------------+-------------+------------+------------+
| customer_id | total_price | sale_date  | region     |
+-------------+-------------+------------+------------+
| c1          | 100.1       | 2013       | china      |
| c2          | 100.2       | 2013       | china      |
| c3          | 100.3       | 2013       | china      |
+-------------+-------------+------------+------------+
  • 选出sale_detail表中排除shop_name和customer_id两列的其它列。命令示例如下：
    select `(shop_name|customer_id)?+.+` from sale_detail;
    返回结果如下：
    +-------------+------------+------------+
| total_price | sale_date  | region     |
+-------------+------------+------------+
| 100.1       | 2013       | china      |
| 100.2       | 2013       | china      |
| 100.3       | 2013       | china      |
+-------------+------------+------------+
  • 选出sale_detail表中排除列名以t开头的其它列。命令示例如下：
    select `(t.*)?+.+` from sale_detail;
    返回结果如下：
    +------------+-------------+------------+------------+
| shop_name  | customer_id | sale_date  | region     |
+------------+-------------+------------+------------+
| s1         | c1          | 2013       | china      |
| s2         | c2          | 2013       | china      |
| s3         | c3          | 2013       | china      |
+------------+-------------+------------+------------+
    说明

    在排除多个列时，如果col2是col1的前缀，则需保证col1写在col2的前面（较长的col写在前面）。例如，一个表有2个分区无需被查询，一个分区名为ds，另一个分区名为dshh，由于前者是后者的前缀，正确表达式为select `(dshh|ds)?+.+` from t;；错误表达式为select `(ds|dshh)?+.+` from t; 。

④ 在选取的列名前可以使用distinct去掉重复字段，只返回去重后的值。使用all会返回字段中所有重复的值。不指定此选项时，默认值为all。
  • 查询表sale_detail中region列数据，如果有重复值时仅显示一条。命令示例如下：
    select distinct region from sale_detail;
    返回结果如下：
    +------------+
| region     |
+------------+
| china      |
+------------+
  • 去重多列时，distinct的作用域是select的列集合，不是单个列。命令示例如下：
    select distinct region, sale_date from sale_detail;
    返回结果如下：
    +------------+------------+
| region     | sale_date  |
+------------+------------+
| china      | 2013       |
+------------+------------+

目标表信息（table_reference）

必填。table_reference表示查询的目标表信息。目标表使用规则如下。
序号 描述 样例
① 直接指定目标表名。 命令示例如下：
select customer_id from sale_detail;
返回结果如下：
+-------------+
| customer_id |
+-------------+
| c1          |
| c2          |
| c3          |
+-------------+
② 嵌套子查询。 命令示例如下：
select * from (select region,sale_date from sale_detail) t where region = 'china';
返回结果如下：
+------------+------------+
| region     | sale_date  |
+------------+------------+
| china      | 2013       |
| china      | 2013       |
| china      | 2013       |
+------------+------------+

WHERE子句（where_condition）

可选。where子句为过滤条件。如果表是分区表，可以实现列裁剪。使用规则如下。

序号 描述 样例
① 配合关系运算符，筛选满足指定条件的数据。关系运算符包含：
  • >、<、=、>=、<=、<>
  • like、rlike
  • in、not in
  • between…and

详情请参见关系运算符。

在where子句中，您可以指定分区范围，只扫描表的指定部分，避免全表扫描。命令示例如下：
select * 
from sale_detail
where sale_date >= '2008' and sale_date <= '2014';
--等价于如下语句。
select * 
from sale_detail 
where sale_date between '2008' and '2014';
返回结果如下：
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+
说明 您可以通过EXPLAIN语句查看分区裁剪是否生效。普通的UDF或JOIN的分区条件写法都有可能导致分区裁剪不生效，详情请参见分区剪裁合理性评估。
② 通过UDF实现分区裁剪，将UDF语句先当作一个小作业执行，再将执行的结果替换到原来UDF出现的位置。实现的方式有以下两种：
  • 在编写UDF的时候时，UDF类上加入Annotation。 
    @com.aliyun.odps.udf.annotation.UdfProperty(isDeterministic=true)
    说明 com.aliyun.odps.udf.annotation.UdfProperty定义在odps-sdk-udf.jar文件中。您需要把引用的odps-sdk-udf版本提高到0.30.x或以上。
  • 在SQL语句前增加set odps.sql.udf.ppr.deterministic = true;语句，此时SQL中所有的UDF均被视为deterministic。该操作执行的原理是进行执行结果回填，但是结果回填最多回填1000个分区。因此，如果UDF类加入Annotation，则可能会导致出现超过1000个回填结果的报错。此时您如果需要忽视此错误，可以通过设置set odps.sql.udf.ppr.to.subquery = false;全局关闭此功能。关闭后，UDF分区裁剪也会失效。
无。

GROUP BY分组查询（col_list）

可选。通常，group by和聚合函数配合使用，根据指定的普通列、分区列或正则表达式进行分组。group by使用规则如下。
序号 描述 样例
① group by操作优先级高于select操作，因此group by的取值是select输入表的列名或由输入表的列构成的表达式。需要注意的是：
  • 不允许是select语句的输出列的别名。
  • group by取值为正则表达式时，必须使用列的完整表达式。
  • select语句中没有使用聚合函数的列必须出现在group by中。
  • 直接使用输入表列名region作为group by的列，即以region值分组。命令示例如下：
    select region from sale_detail group by region;
    返回结果如下：
    +------------+
| region     |
+------------+
| china      |
+------------+
  • 以region值分组，返回每一组的销售额总量。命令示例如下：
    select sum(total_price) from sale_detail group by region;
    返回结果如下：
    +------------+
| _c0        |
+------------+
| 300.6      |
+------------+
  • 以region值分组，返回每一组的region值（组内唯一）及销售额总量。命令示例如下：
    select region, sum (total_price) from sale_detail group by region;
    返回结果如下：
    +------------+------------+
| region     | _c1        |
+------------+------------+
| china      | 300.6      |
+------------+------------+
  • 以select列的别名分组，返回报错。错误命令示例如下：
    select region as r from sale_detail group by r;
    正确命令示例如下：
    select region as r from sale_detail group by region;
    返回结果如下：
    +------------+
| r          |
+------------+
| china      |
+------------+
  • 以列表达式分组，必须使用列的完整表达式。命令示例如下：
    select 2 + total_price as r from sale_detail group by 2 + total_price;
    返回结果如下：
    +------------+
| r          |
+------------+
| 102.1      |
| 102.2      |
| 102.3      |
+------------+
  • select的所有列中没有使用聚合函数的列，必须出现在group by中，否则返回报错。错误命令示例如下：
    select region, total_price from sale_detail group by region;
    正确命令示例如下：
    select region, total_price from sale_detail group by region, total_price;
    返回结果如下：
    +------------+-------------+
| region     | total_price |
+------------+-------------+
| china      | 100.1       |
| china      | 100.2       |
| china      | 100.3       |
+------------+-------------+
② 当SQL语句设置了属性，即set odps.sql.groupby.position.alias=true;，group by中的整型常量会被当做select的列序号处理。 命令示例如下：
--与下一条SQL语句一起执行。
set odps.sql.groupby.position.alias=true;
--1代表select的列中第一列即region，以region值分组，返回每一组的region值（组内唯一）及销售额总量。
select region, sum(total_price) from sale_detail group by 1;
返回结果如下：
+------------+------------+
| region     | _c1        |
+------------+------------+
| china      | 300.6      |
+------------+------------+

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关键字。
  • 查询表sale_detail的信息，并按照total_price升序排列前2条。命令示例如下：
    select * from sale_detail order by total_price limit 2;
    返回结果如下：
    +------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
+------------+-------------+-------------+------------+------------+
  • 查询表sale_detail的信息，并按照total_price降序排列前2条。命令示例如下：
    select * from sale_detail order by total_price desc limit 2;
    返回结果如下：
    +------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s3         | c3          | 100.3       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
+------------+-------------+-------------+------------+------------+
② order by默认要求带limit数据行数限制，没有limit会返回报错。如您需要解除order by必须带limit的限制，详情请参见LIMIT NUMBER限制输出行数>解除ORDER BY必须带LIMIT的限制。
③ 在使用order by排序时，NULL会被认为比任何值都小，这个行为与MySQL一致，但是与Oracle不一致。 查询表sale_detail的信息，并按照total_price升序排列前5条。命令示例如下：
select * from sale_detail order by total_price limit 5;
返回结果如下：
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| null       | c5          | NULL        | 2013       | china      |
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| s6         | c6          | 100.4       | 2013       | shanghai      |
+------------+-------------+-------------+------------+------------+
④ order by后面需要加上select列的别名。当select某列时，如果没有指定列的别名，则列名会被作为列的别名。 order by加列的别名。命令示例如下：
select total_price as t from sale_detail order by total_price limit 3;
--等效于如下语句。
select total_price as t from sale_detail order by t limit 3;
返回结果如下：
+------------+
| t          |
+------------+
| NULL       |
| 100.1      |
| 100.2      |
+------------+
⑤ 当SQL语句设置了属性，即set odps.sql.orderby.position.alias=true;，order by中的整型常量会被当做select的列序号处理。 命令示例如下：
--与下一条SQL语句一起执行。
set odps.sql.orderby.position.alias=true;
select * from sale_detail order by 3 limit 3;
返回结果如下：
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| null       | c5          | NULL        | 2014       | shanghai   |
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
+------------+-------------+-------------+------------+------------+
⑥ offset和order by...limit语句配合，可以指定跳过的行数。 将表sale_detail按照total_price升序排序后，输出第4~6行。命令示例如下：
select customer_id,total_price from sale_detail order by total_price limit 6 offset 3;
返回结果如下：
+-------------+-------------+
| customer_id | total_price |
+-------------+-------------+
| c3          | 100.3       |
| c6          | 100.4       |
| c7          | 100.5       |
+-------------+-------------+

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关键字。
  • 查询表sale_detail中的列region和total_price的值并按照region值进行哈希分片，然后按照total_price对哈希分片结果进行局部升序排序。命令示例如下：
    select region,total_price from sale_detail distribute by region sort by total_price;
    返回结果如下：
    +------------+-------------+
| region     | total_price |
+------------+-------------+
| shanghai   | NULL        |
| china      | 100.1       |
| china      | 100.2       |
| china      | 100.3       |
| shanghai   | 100.4       |
| shanghai   | 100.5       |
+------------+-------------+
  • 查询表sale_detail中的列region和total_price的值并按照region值进行哈希分片，然后按照total_price对哈希分片结果进行局部降序排序。命令示例如下：
    select region,total_price from sale_detail distribute by region sort by total_price desc;
    返回结果如下：
    +------------+-------------+
| region     | total_price |
+------------+-------------+
| shanghai   | 100.5       |
| shanghai   | 100.4       |
| china      | 100.3       |
| china      | 100.2       |
| china      | 100.1       |
| shanghai   | NULL        |
+------------+-------------+
② 如果sort by语句前有distribute by，sort by会对distribute by的结果按照指定的列进行排序。
③ 如果sort by语句前没有distribute by，sort by会对每个Reduce中的数据进行局部排序。

保证每个Reduce的输出数据都是有序的，从而增加存储压缩率，同时读取时如果有过滤，能够减少真正从磁盘读取的数据量，提高后续全局排序的效率。

 命令示例如下：
select region,total_price from sale_detail sort by total_price desc;
返回结果如下：
+------------+-------------+
| region     | total_price |
+------------+-------------+
| china      | 100.3       |
| china      | 100.2       |
| china      | 100.1       |
| shanghai   | 100.5       |
| shanghai   | 100.4       |
| shanghai   | NULL        |
+------------+-------------+
说明
  • 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限制，可以通过如下两种方式实现：
  • Project级别：设置setproject odps.sql.validate.orderby.limit=false;关闭order by必须带limit的限制。
  • Session级别：设置setproject odps.sql.validate.orderby.limit=false;关闭order by必须带limit的限制，需要与SQL语句一起提交。
    说明 如果关闭order by必须带limit的限制，在单个执行节点有大量数据排序的情况下，资源消耗或处理时长等性能表现会受到影响。
解除屏显限制 当使用无limit的select语句或limit的number数量超过设置的屏显上限时，如果您直接从屏显窗口查看结果，最多只能输出屏显上限设置的行数。
每个项目空间的屏显上限可能不同，您可以参考如下方法控制：
  • 如果关闭了项目空间数据保护，修改odpscmd config.ini文件。

    设置odpscmd config.ini文件中的use_instance_tunnel=true，如果不配置instance_tunnel_max_record参数，则屏显行数不受限制；否则，屏显行数受instance_tunnel_max_record参数值限制。instance_tunnel_max_record参数值上限为10000行。Instance Tunnel详情请参见使用说明。

  • 如果开启了项目空间数据保护，屏显行数受READ_TABLE_MAX_ROW参数值限制，配置上限为10000行。
说明 您可以执行show SecurityConfiguration;命令查看ProjectProtection属性配置。如果ProjectProtection=true，根据项目空间数据保护需求判断是否关闭数据保护机制。如果可以关闭，通过set ProjectProtection=false;命令关闭。ProjectProtection属性默认不开启。项目空间数据保护机制详情请参见项目空间的数据保护。