本文为您介绍MaxCompute SELECT语法格式及使用SELECT语法执行嵌套查询、排序操作、分组查询等操作的注意事项。

SELECT语法格式

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY order_condition]
[DISTRIBUTE BY distribute_condition [SORT BY sort_condition] ]
[LIMIT number]
使用select语句时的注意事项请参见本文后续部分。

列表达式

select操作从表中读取数据,要读的列可以用列名指定,或者用*代表所有的列,一个简单的select语句,如下所示。
select * from sale_detail;
只读取sale_detail的一列shop_name
select shop_name from sale_detail;
where中可以指定过滤的条件。
select * from sale_detail where shop_name like 'hang%';

当使用select语句屏显时,目前最多只能显示10000行结果。当select作为子句时,无此限制,select子句会将全部结果返回给上层查询。

使用说明:
  • select分区表时禁止全表扫描。

    2018-01-10 20点后创建的新项目,默认情况下执行SQL时,针对该Project里的分区表不允许全表扫描,必须有分区条件指定需要扫描的分区,由此减少SQL的不必要I/O,从而减少计算资源的浪费,同时也减少了不必要的后付费模式的计算费用(后付费模式中,数据输入量是计量计费参数之一)。

    例如表定义是t1(c1,c2) partitioned by(ds),在新项目里执行如下语句会被禁止,返回Error。
    select * from t1 where c1=1;
    select * from t1 where (ds=‘20180202’ or c2=3);
    select * from t1 left outer join t2 on a.id =b.id and a.ds=b.ds and b.ds=‘20180101’);  
    --join进行关联时,若分区剪裁条件放在where中,则分区剪裁生效,如果放在on条件中,从表的分区剪裁会生效,主表则进行全表扫描。

    如果您需要对分区表进行全表扫描,可以在对分区表全表扫描的SQL语句前加set odps.sql.allow.fullscan=true;,并和SQL语句一起提交执行。假设sale_detail表为分区表,则要全表扫描需同时提交以下的简单查询命令。

    set odps.sql.allow.fullscan=true;
    select * from sale_detail;
    如果需要整个项目都允许全表扫描,可以通过开关自行打开或关闭(True/False),命令如下。
    setproject odps.sql.allow.fullscan=true;
  • table_reference中支持使用嵌套子查询,如下所示。
    select * from (select region from sale_detail) t where region = 'shanghai';
    说明 当使用select语句屏显时,目前最多只能显示10000行结果。当select作为子句时则无此限制,select子句会将全部结果返回给上层查询。

DISTINCT去重

您可以通过在字段前使用distinct去掉重复字段,只返回一个值;而使用all会返回字段中所有重复的值。不指定此选项时,默认效果和使用all的效果相同。
使用distinct只返回一行记录,如下所示。
select distinct region from sale_detail;
select distinct region, sale_date from sale_detail;
-- distinct多列,distinct的作用域是select的列集合,不是单个列。

SELECT_EXPR正则表达式

MaxCompute SQL支持使用正则表达式select_expr选列。

使用正则表达式select_expr
  • SELECT `abc.*` FROM t;选出t表中所有列名以abc开头的列。
  • SELECT `(ds)?+.+` FROM t;选出t表中列名不为ds的所有列。
  • SELECT `(ds|pt)?+.+` FROM t;选出t表中排除dspt两列的其它列。
  • SELECT `(d.*)?+.+` FROM t;选出t表中排除列名以d开头的其它列。
说明

在排除多个列时,如果col2是col1的前缀,则需保证col1写在col2的前面(较长的col写前面)。例如,一个表有2个分区无需被select,一个分区名为ds,另一个为dshh,则正确表达式为SELECT `(dshh|ds)?+.+` FROM tbl;;错误表达式为SELECT `(ds|dshh)?+.+` FROM tbl;

WHERE子句过滤

where子句支持的过滤条件,如下表所示。
过滤条件 描述
> 、 < 、 =、 >=、 <=、 <> 关系操作符。
like、rlike like和rlike的source和pattern参数均仅接受STRING类型。
in、not in 如果在in/not in条件后加子查询,子查询只能返回一列值,且返回值的数量不能超过1000。
select语句的where子句中,您可以指定分区范围,只扫描表的指定部分,避免全表扫描,如下所示。
SELECT sale_detail.* 
FROM sale_detail
WHERE sale_detail.sale_date >= '2008'
AND sale_detail.sale_date <= '2014';
说明 您可以通过explain select语句查看分区裁剪是否生效。普通的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语句前设置Flag:set odps.sql.udf.ppr.deterministic = true;,此时SQL中所有的UDF均被视为deterministic。该操作执行的原理是做执行结果回填,但是结果回填存在限制,即最多回填1000个Partition。因此,如果UDF类加入Annotation,则可能会导致出现超过1000个回填结果的报错。此时您如果需要忽视此错误,可以通过设置Flag:set odps.sql.udf.ppr.to.subquery = false;全局关闭此功能。关闭后,UDF分区裁剪也会失效。

MaxCompute SQL的where子句支持between…and条件查询。

SELECT sale_detail.* 
FROM sale_detail 
WHERE sale_detail.sale_date BETWEEN '2008' AND '2014';

GROUP BY分组查询

通常,group by聚合函数会配合使用。在select中包含聚合函数时有以下规则:
  1. group by的Key可以是输入表的列名。
  2. 也可以是由输入表的列构成的表达式,不允许是select语句的输出列的别名。
  3. 规则1的优先级高于规则2。当规则1和规则2发生冲突时,即group by的Key既是输入表的列或表达式,又是select的输出列时,以规则1为准。
  4. 当SQL语句set flag,即set hive.groupby.position.alias=true;时,GROUP BY 中的整型常量会被当做SELECT的列序号处理。
    set hive.groupby.position.alias=true;--与下一条sql语句一起执行
    select region, sum(total_price) from sale_detail group by 1;-- 1代表select的列中第一列即region,以region值分组,返回每一组的region值(组内唯一)及销售额总量。
示例
select region from sale_detail group by region;
-- 直接使用输入表列名作为group by的列,可以运行。
select sum(total_price) from sale_detail group by region;
-- 以region值分组,返回每一组的销售额总量,可以运行。
select region, sum(total_price) from sale_detail group by region;
-- 以region值分组,返回每一组的region值(组内唯一)及销售额总量,可以运行。
select region as r from sale_detail group by r;
-- 使用select列的别名运行,报错返回。
select 2 + total_price as r from sale_detail group by 2 + total_price;
-- 必须使用列的完整表达式。
select region, total_price from sale_detail group by region;
-- 报错返回,select的所有列中,没有使用聚合函数的列,必须出现在group by中。
select region, total_price from sale_detail group by region, total_price;
-- 可以运行。

通常,在SQL解析中,group by操作先于select操作,因此group by只能接受输入表的列或表达式为Key。

说明
  • 关于聚合函数的详情请参见聚合函数
  • order by用于对所有数据按照某几列进行全局排序。如果您希望按照降序对记录进行排序,可以使用desc关键字。由于是全局排序,order by必须与limit共同使用。在使用order by排序时,NULL会被认为比任何值都小,这个行为与MySQL一致,但是与Oracle不一致。
    group by不同,order by后面必须加select列的别名。当select某列时,如果没有指定列的别名,则列名会被作为列的别名。
    select * from sale_detail order by region;
    -- 报错返回,order by没有与limit共同使用。
    select * from sale_detail order by region limit 100;
    select region as r from sale_detail order by region limit 100;
    -- 报错返回,order by后面必须加列的别名。
    select region as r from sale_detail order by r limit 100;

LIMIT NUMBER限制输出行数

limit numbernumber是常数,限制输出行数。当使用无limitselect语句直接从屏幕输出查看结果时,最多只输出10000行。每个项目空间的这个屏显最大限制可能不同,您可以通过setproject命令控制。

ORDER BY/SORT BY/DISTRIBUTE BY

  • distribute by用于对数据按照某几列的值做Hash分片,必须使用select的输出列别名。
    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用于局部排序,语句前必须加distribute by。实际上sort by是对distribute by的结果进行局部排序。必须使用select的输出列别名。
    select region from sale_detail distribute by region sort by region;
    select region as r from sale_detail sort by region;
    -- 没有distribute by,报错退出。
  • order by不和distribute by/sort by共用,同时group by也不和distribute by/sort by共用,必须使用select的输出列别名。
  • 当SQL语句set flag,set hive.orderby.position.alias=true;时,ORDER BY 中的整型常量会被当做SELECT的列序号处理。
    --set flag
    set hive.orderby.position.alias=true;
    --创建表src
    creat table src(key BIGINT,value BIGINT);
    --以value值分组,返回表src的所有信息。
    SELECT * FROM src ORDER BY 2 limit 100;
    等同于
    SELECT * FROM src ORDER BY value limit 100;
  • OFFSET 和 ORDER BY LIMIT语句配合,可以指定跳过OFFSET数目的行。
    --将 src 按照 key从小到大排序后,输出第11到第30行 (OFFSET 10 指定跳过前10行,LIMIT 20 指定最多输出20行)。
    SELECT * FROM src ORDER BY key LIMIT 20 OFFSET 10;
说明
  • order by/sort by/distribute by的Key必须是select语句的输出列,即列的别名。列的别名可以为中文。
  • 在MaxCompute SQL解析中,order by/sort by/distribute by是后于select操作的,因此它们只能接受select语句的输出列为Key。