MaxCompute中select命令用法_云原生大数据计算服务 MaxCompute-阿里云帮助中心

命令格式

[with <cte>[, ...] ]
select [all | distinct] <select_expr>[, <except_expr>)][, <replace_expr>] ...
       from <table_reference>
       [where <where_condition>]
       [group by {<col_list>|rollup(<col_list>)}]
           [having <having_condition>]
       [order by <order_condition>]
       [distribute by <distribute_condition> [sort by <sort_condition>]|[ cluster by <cluster_condition>] ]
       [limit <number>]
       [window <window_clause>]

命令中各字段的执行语序请参见 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);

查询分区表sale_detail中的数据，命令示例如下：

select * from sale_detail;
--返回结果。
+------------+------------+------------+------------+------------+
| shop_name  | price      | customer   | sale_date  | region     |
+------------+------------+------------+------------+------------+
| s1         | 100.1      | c1         | 2013       | china      |
| s2         | 100.2      | c2         | 2013       | china      |
| s3         | 100.3      | c3         | 2013       | china      |
+------------+------------+------------+------------+------------+

WITH子句（cte）

可选。WITH子句包含一个或多个常用的表达式CTE。CTE充当当前运行环境中的临时表，您可以在之后的查询中引用该表。CTE使用规则如下：

在同一WITH子句中的CTE必须具有唯一的名字。

在WITH子句中定义的CTE仅对在同一WITH子句中的其他CTE可以使用。

假设A是子句中的第一个CTE，B是子句中的第二个CTE：

A引用A：无效。错误命令示例如下。

with 
A as (select 1 from A) 
select * from A;

A引用B，B引用A：无效，不允许循环引用。错误命令示例如下

with 
A as (select * from B ), 
B as (select * from A ) 
select * from B;

列表达式（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

表中排除列名以

开头的其它列。命令示例如下。

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       |
+------------+------------+

排除列（except_expr）

可选。 except_expr 格式为 except(col1_name, col2_name, ...) 。当您希望读取表内大多数列的数据，同时要排除表中少数列的数据时，可以通过 select * except(col1_name, col2_name, ...) from ...; 语句实现，表示读取表数据时会排除指定列（col1、col2）的数据。

命令示例如下。

--读取sale_detail表的数据，并排除region列的数据。
select * except(region) from sale_detail;

返回结果如下。

+-----------+-------------+-------------+-----------+
| shop_name | customer_id | total_price | sale_date |
+-----------+-------------+-------------+-----------+
| s1        | c1          | 100.1       | 2013      |
| s2        | c2          | 100.2       | 2013      |
| s3        | c3          | 100.3       | 2013      |
+-----------+-------------+-------------+-----------+

修改列（replace_expr）

可选。 replace_expr 格式为 replace(exp1 [as] col1_name, exp2 [as] col2_name, ...) 。当您希望读取表内大多数列的数据，同时要对表中少数列的数据进行修改时，可以通过 select * replace(exp1 as col1_name, exp2 as col2_name, ...) from ...; 实现，表示读取表数据时会将col1的数据修改为exp1，将col2的数据修改为exp2。

命令示例如下。

--读取sale_detail表的数据，并修改total_price、region两列的数据。
select * replace(total_price+100 as total_price, 'shanghai' as region) from sale_detail;

返回结果如下。

+-----------+-------------+-------------+-----------+--------+
| shop_name | customer_id | total_price | sale_date | region |
+-----------+-------------+-------------+-----------+--------+
| s1        | c1          | 200.1       | 2013      | shanghai |
| s2        | c2          | 200.2       | 2013      | shanghai |
| s3        | c3          | 200.3       | 2013      | shanghai |
+-----------+-------------+-------------+-----------+--------+

目标表信息（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

子句中，您可以指定分区范围，只扫描表的指定部分，避免全表扫描。命令示例如下。

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分区裁剪也会失效。

使用UDF实现分区裁剪时，UDF必须在查询表的


          where

条件里才能生效。

用UDF实现分区裁剪正确示例如下。

--UDF必须放在查询的源表的where条件中：
select key, value from srcp where udf(ds) = 'xx';

用UDF实现分区裁剪错误示例如下。

--放在join on后面分区裁剪不会生效
select A.c1, A.c2 from srcp1 A  join srcp2  B on A.c1 = B.c1 and udf(A.ds) ='xx';

在列表达式（select_expr）中，如果被重命名的列字段（赋予了列别名）使用了函数，则不能在


         where

子句中引用列别名。 错误命令示例如下。

select  task_name
        ,inst_id
        ,settings
        ,GET_JSON_OBJECT(settings, '$.SKYNET_ID') as skynet_id
        ,GET_JSON_OBJECT(settings, '$.SKYNET_NODENAME') as user_agent
from    Information_Schema.TASKS_HISTORY
where   ds = '20211215' and skynet_id is not null
limit 10;

GROUP BY分组查询（col_list）

可选。通常，


         group by

和聚合函数配合使用，根据指定的普通列、分区列或正则表达式进行分组。


         group by

使用规则如下：


          group by

操作优先级高于


          select

操作，因此


          group by

的取值是


          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        |
+------------+------------+
| china      | 300.6      |
| shanghai   | 200.9      |
+------------+------------+

ORDER BY全局排序（order_condition）

可选。


          order by

用于对所有数据按照指定普通列、分区列或指定常量进行全局排序。


          order by

使用规则如下：

默认对数据进行升序，如果降序排序，需要使用


           desc

关键字。


           order by

默认要求带


           limit

数据行数限制，没有


           limit

会返回报错。如您需要解除


           order by

必须带


           limit

的限制，详情请参见 LIMIT NUMBER限制输出行数>解除ORDER BY必须带LIMIT的限制。

查询表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

排序时，NULL会被认为比任何值都小，这个行为与MySQL一致，但是与Oracle不一致。

查询表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      |
+------------+-------------+-------------+------------+------------+


           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          |
+------------+
| 100.1      |
| 100.2      |
| 100.3      |
+------------+

当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     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+


           offset

可以和


           order by...limit

语句配合使用，用于指定跳过的行数，格式为


           order by...limit m offset n

，也可以简写为


           order by...limit n, m

。其中：


           limit m

控制输出m行数据，


           offset n

表示在开始返回数据之前跳过的行数。


           offset 0

与省略offset子句效果相同。

将表sale_detail按照total_price升序排序后，输出从第3行开始的3行数据。命令示例如下。

select customer_id,total_price from sale_detail order by total_price limit 3 offset 2;
--等效于如下语句。
select customer_id,total_price from sale_detail order by total_price limit 2, 3;

返回结果如下。

+-------------+-------------+
| customer_id | total_price |
+-------------+-------------+
| c3          | 100.3       |
+-------------+-------------+

由于查询到的数据从第3行开始仅剩1行数据，不足3行，所以返回结果只有1行。

可选。 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

的结果按照指定的列进行排序。

查询表sale_detail中的列region和total_price的值并按照region值进行哈希分片，然后按照total_price对哈希分片结果进行局部升序排序。命令示例如下。

--为直观展示数据呈现效果，向sale_detail表中追加数据。
insert into sale_detail partition (sale_date='2014', region='shanghai') values ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);
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

会对每个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

同时使用。

解除


           order by

必须带


           limit

的限制。

因为


            order by

需要对单个执行节点做全局排序，所以默认带


            limit

限制，避免误用导致单点处理大量数据。如果您的使用场景确实需要


            order by

放开


            limit

限制，可以通过如下两种方式实现：

Project级别：设置


           setproject odps.sql.validate.orderby.limit=false;

关闭


           order by

必须带


           limit

的限制。

Session级别：设置


           set 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行。

Split Size Hint

可选。您可通过调整Split Size来控制并发度，调整计算性能。Split Size功能可以作用到表级别。指定的值单位为MB，默认值为256MB。

使用场景。

当您发现作业有很多子任务都在等待资源，没有办法申请到更多资源的情况，可以通过调高Split Size值，减少并发度，可以节省起停子任务的时间。

当并发度比较低，例如并发度只有几百，而且当前执行的子任务运行很久都没有出结果，但是资源池中还有很多资源时，可以调低值来提高并发度，降低整个作业的运行时间。

注意事项。

此Hint对于Cluster表，如果优化中会使用Cluster的分桶属性，那么Split Size Hint会失效。

Split Size的值按照256MB的倍数进行调整，例如128MB、512MB等。

一个SQL中有重复读同张表，那么Hint会被合并成为指定的最小值。

SQL中有两个读表src的地方，一个Hint为1MB一个为10MB，那么最后会按照1MB来切分。

SQL中有两个读表src的地方，一个Hint为1MB一个没有，那么最后会按照1MB来切分。