无影响,无限制。具体Group by分组查询使用规则可参考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表中排除ds和pt两列的其它列。 SELECT (d.*)?+.+ FROM t;选出t表中排除列名以d开头的其它列。
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子句支持的过滤条件,如下表所示。
示例
在SELECT语句的where子句中,您可以指定分区范围,只扫描表的指定部分,避免全表扫描,如下所示。
SELECT sale_detail.*
FROM sale_detail
WHERE sale_detail.sale_date >= '2008'
AND sale_detail.sale_date <= '2014';
UDF支持分区裁剪。支持的方式是将UDF语句先当作一个小作业执行,再将执行的结果替换到原来UDF出现的位置 。实现的方式有以下两种:
在编写UDF的时候,UDF类上加入Annotation。
@com.aliyun.odps.udf.annotation.UdfProperty(isDeterministic=true)
在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;
示例
--查询表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是常数,限制输出行数。当使用无limit的SELECT语句直接从屏幕输出查看结果时,最多只输出10000行。每个项目空间的这个屏显最大限制可能不同,您可以通过setproject命令控制。
欢迎扫码加入 MaxCompute开发者社区钉钉群,或点击申请加入。