本文为您介绍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语句时,屏显目前最多只能显示10000行结果。当SELECT语句作为子句时则无此限制,SELECT子句会将全部结果返回给上层查询。
  • SELECT语句查询分区表时禁止全表扫描。

    2018-01-10 20点后创建的新项目执行SQL时,默认情况下,针对该Project里的分区表不允许全表扫描。在查询分区表数据时必须指定分区,由此减少SQL的不必要I/O,从而减少计算资源的浪费以及按量付费模式下不必要的计算费用。

    如果您需要对分区表进行全表扫描,可以在对分区表全表扫描的SQL语句前加上命令set odps.sql.allow.fullscan=true;,并和SQL语句一起提交执行。假设sale_detail表为分区表,需要同时执行如下语句进行全表查询。
    set odps.sql.allow.fullscan=true;
    select * from sale_detail;
    如果整个项目都需要全表扫描,执行如下命令打开开关。
    setproject odps.sql.allow.fullscan=true;

列表达式(select_expr)

SELECT操作从表中读取数据,列表达式有以下几种形式:
  • 用列名指定要读取的列。例如,读取表sale_detail的列shop_name
    select shop_name from sale_detail;
  • *代表所有的列。读取表sale_detail中所有的列。
    select * from sale_detail;
    WHERE中可以指定过滤的条件。
    select * from sale_detail where shop_name like 'hang%';
  • 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个分区无需被查询,一个分区名为ds,另一个为dshh,由于前者是后者的前缀,则正确表达式为SELECT `(dshh|ds)?+.+` FROM tbl;;错误表达式为SELECT `(ds|dshh)?+.+` FROM tbl;

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

TABLE_REFERENCE

table_reference为查询的目标表信息。除了支持已存在的目标表名称还支持使用嵌套子查询,如下所示。
select * from (select region from sale_detail) t where region = 'shanghai';

WHERE子句过滤

where子句支持的过滤条件,如下表所示。
过滤条件 描述
> 、 < 、 =、 >=、 <=、 <> 关系操作符。
like、rlike like和rlike的source和pattern参数均仅接受STRING类型。
in、not in 如果在in/not in条件后加子查询,子查询只能返回一列值,且返回值的数量不能超过1000。
Between…and 限定查询范围。
示例
  • 在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分区裁剪也会失效。
  • between…and查询示例如下。
    SELECT sale_detail.* 
    FROM sale_detail 
    WHERE sale_detail.sale_date BETWEEN '2008' AND '2014';

GROUP BY分组查询

通常,GROUP BY聚合函数配合使用。在SELECT中包含聚合函数时有以下规则:
  • 在SQL解析中,GROUP BY操作先于SELECT操作,因此GROUP BY的取值是SELECT输入表的列名或者由输入表的列构成的表达式,不允许是SELECT语句的输出列的别名。
  • GROUP BY的值既是输入表的列或表达式,又是SELECT的输出列时,取值为输入表的列名。
  • 当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值(组内唯一)及销售额总量。
示例
--直接使用输入表列名region作为group by的列,即以region值分组。
select region from sale_detail group by region;
--以region值分组,返回每一组的销售额总量。
select sum(total_price) from sale_detail group by region;
--以region值分组,返回每一组的region值(组内唯一)及销售额总量。
select region, sum(total_price) from sale_detail group by region;
--使用select列的别名运行,报错返回。
select region as r from sale_detail group by r;
--必须使用列的完整表达式。
select 2 + total_price as r from sale_detail group by 2 + total_price;
--报错返回,select的所有列中,没有使用聚合函数的列,必须出现在group by中。
select region, total_price from sale_detail group by region;
没有使用聚合函数的列,出现在group by中后,运行成功。
select region, total_price from sale_detail group by region, total_price;     

ORDER BY/SORT BY/DISTRIBUTE BY

  • DISTRIBUTE BY

    功能说明:用于对数据按照某几列的值做Hash分片,必须使用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;
  • ORDER BY
    功能说明:用于对所有数据按照指定列进行全局排序。
    • 对记录进行降序排序,需要使用desc关键字。默认以升序排列。
    • 在使用order by排序时,NULL会被认为比任何值都小,这个行为与MySQL一致,但是与Oracle不一致。
    • order by后面须加SELECT列的别名。当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的取值必须是SELECT语句的输出列,即列的别名。列的别名可以为中文。
    • 在MaxCompute SQL解析中,order by/sort by/distribute by是后于SELECT操作的,因此它们的取值只能为SELECT语句的输出列。
    示例
    --查询表sale_detail的信息,并按照region升序排列前100条。
    select * from sale_detail order by region limit 100;
    -- order by没有与limit共同使用时,报错返回。
    select * from sale_detail order by region;
    --order by加列的别名。
    select region as r from sale_detail order by region limit 100;
    select region as r from sale_detail order by r limit 100;

LIMIT NUMBER限制输出行数

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