MaxCompute SQL中select语句使用详情 |
您所在的位置：网站首页 › sql查询列数 › MaxCompute SQL中select语句使用详情 |
|
SELECT语法介绍
本文介绍MaxCompute SELECT语法格式及使用SELECT语法执行嵌套查询、排序操作、分组查询等操作的注意事项。在进行操作之前构建操作示例表sale_detail以及insert测试数据： ---------------------------------------数据表和数据准备--------------------------------------- CREATE TABLE IF NOT EXISTS sale_detail( shop_name STRING, customer_id STRING, total_price DOUBLE) PARTITIONED BY (sale_date STRING, region STRING); create table if not exists shop ( shop_id string, shop_name string, address string, owner_id string ); insert into table sale_detail partition (sale_date='202003', region='hangzhou') values ('unique', 'user1', 1000), ('adidas', 'user2', 2000), ('zara', 'user3', 1500), ('veromoda', 'user1', 2000), ('nike', 'user4', 1000), ('ur', 'user2', 1400), ('ochirly', 'user1', 2000), ('lily', 'user3', 3000), ('onemore', "user2", 1200); insert into table sale_detail partition (sale_date='202004', region='beijing') values ('unique', 'user1', 1000), ('adidas', 'user2', 2000), ('zara', 'user3', 1500), ('veromoda', 'user1', 2000), ('nike', 'user4', 1000), ('ur', 'user2', 1400), ('ochirly', 'user1', 2000), ('lily', 'user3', 3000), ('onemore', "user2", 1200); insert into table sale_detail partition (sale_date, region) (shop_name, customer_id, total_price, sale_date, region) values ('unique', 'user1', 1000, '202001', 'shanghai'), ('adidas', 'user2', 2000, '202001', 'shanghai'), ('zara', 'user3', 1500, '202001', 'shanghai'); select * from sale_detail; insert into table shop(shop_id, shop_name, address, owner_id) values ('1', 'unique', 'hangzhou', 'owner1'), ('2', 'adidas', 'beijing', 'owner2'), ('3', 'zara', 'shanghai', 'owner3'), ('4', 'veromoda', 'shanghai', 'owner4'), ('5', 'nike', 'beijing', 'owner5'), ('6', 'ur', 'shanghai', 'owner6'), ('7', 'ochirly', 'shanghai', 'owner7'), ('8', 'lily', 'shanghai', 'owner8'), ('9', 'onemore', 'shanghai', 'owner9'); select * from shop;本文MaxCompute命令参考自： MaxCompute 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语句查询分区表时禁止全表扫描。假设sale_detail表为分区表，需要同时执行如下语句进行全表查询。 set odps.sql.allow.fullscan=true; select * from sale_detail;如果整个项目都需要全表扫描，执行如下命令打开开关。 setproject odps.sql.allow.fullscan=true; 列表达式（select_expr）SELECT操作从表中读取数据，列表达式有以下几种形式： 1. 用列名指定要读取的列。例如，读取表sale_detail的列shop_name。 select shop_name from sale_detail;2. 用*代表所有的列。读取表sale_detail中所有的列。 select * from sale_detail;3. 在WHERE中可以指定过滤的条件。 select * from sale_detail where shop_name like 'hang%';4. 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开头的其它列。5. 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_REFERENCEtable_reference为查询的目标表信息。除了支持已存在的目标表名称还支持使用嵌套子查询，如下所示。 select * from (select region from sale_detail) t where region = 'shanghai'; WHERE子句过滤where子句支持的过滤条件，如下表所示。 过滤条件描述> 、 < 、 =、 >=、 = '2019' AND sale_detail.sale_date 0 GROUP BY key HAVING SUM(value) > 100 ORDER BY key LIMIT 100 ;以上语句的逻辑执行顺序是FROM->WHERE->GROUY BY->HAVING->SELECT->ORDER BY->LIMIT： ORDER BY中只能引用SELECT列表中生成的列，而非访问FROM的源表中的列。HAVING可以访问GROUP BY key和聚合函数。SELECT时，如果语句中有GROUP BY，便只能访问GROUP BY key和聚合函数，而不是FROM中源表中的列。为避免混淆，MaxCompute支持以执行顺序书写查询语句： FROM src t WHERE value > 0 GROUP BY key HAVING SUM(value) > 100 SELECT key ,MAX(value) ORDER BY key LIMIT 100 ;使用示例： SELECT shop_name ,total_price ,region FROM sale_detail WHERE total_price > 150 DISTRIBUTE BY region SORT BY region ;以上语句的逻辑执行顺序是FROM->WHERE->SELECT->DISTRIBUTE BY->SORT BY。 子查询 子查询基本定义普通查询操作的对象是目标表，但是查询的对象也可以是另一个SELECT语句，此查询为子查询（SUBQUERY）。子查询必须要有别名。 --普通查询。 select * from sale_detail; --子查询。 select * from (select shop_name from sale_detail) a;在from子句中，子查询可以被当作一张表，与其它的表或子查询进行join操作。 -- 示例1 select a.shop_id, a.shop_name, a.address from (select * from shop) a join sale_detail b on a.shop_name=b.shop_name; -- 返回值 shop_id shop_name address +--------+----------+--------+ 1 unique hangzhou 2 adidas beijing 3 zara shanghai 1 unique hangzhou 2 adidas beijing 3 zara shanghai 4 veromoda shanghai 5 nike beijing 6 ur shanghai 7 ochirly shanghai 8 lily shanghai 9 onemore shanghai 1 unique hangzhou 2 adidas beijing 3 zara shanghai 4 veromoda shanghai 5 nike beijing 6 ur shanghai 7 ochirly shanghai 8 lily shanghai 9 onemore shanghai -- 示例2 select a.shop_id, a.shop_name, b.total_price, b.customer_id from shop a join sale_detail b on a.shop_name=b.shop_name; -- 返回结果 shop_id shop_name total_price customer_id +--------+----------+------------+------------+ 1 unique 1000.0 user1 2 adidas 2000.0 user2 3 zara 1500.0 user3 4 veromoda 2000.0 user1 5 nike 1000.0 user4 6 ur 1400.0 user2 7 ochirly 2000.0 user1 8 lily 3000.0 user3 9 onemore 1200.0 user2 1 unique 1000.0 user1 2 adidas 2000.0 user2 3 zara 1500.0 user3 1 unique 1000.0 user1 2 adidas 2000.0 user2 3 zara 1500.0 user3 4 veromoda 2000.0 user1 5 nike 1000.0 user4 6 ur 1400.0 user2 7 ochirly 2000.0 user1 8 lily 3000.0 user3 9 onemore 1200.0 user2 IN SUBQUERY/NOT IN SUBQUERY IN SUBQUERYin subquery与left semi join用法类似。 select * from mytable1 where id in (select id from mytable2); --等效于以下语句。 select * from mytable1 a left semi join mytable2 b on a.id = b.id;目前MaxCompute不仅支持in subquery，还支持Correlated条件。 下面查询语句中子查询中的where value = mytable1.value即是一个Correlated条件。MaxCompute 1.0版本不支持这种既引用了子查询中源表，又引用了外层查询源表的表达式。MaxCompute 2.0版本已支持这种用法，这样的过滤条件构成了semi join中on条件的一部分。 select * from mytable1 where id in (select id from mytable2 where value = mytable1.value);使用示例： select * from sale_detail where shop_name in (select shop_name from shop); select * from sale_detail where shop_name in (select shop_name from shop where sale_detail.shop_name=shop_name); select * from sale_detail a left semi join shop b on a.shop_name=b.shop_name; -- 上述三个语句的返回结果均为： shop_name customer_id total_price sale_date region +----------+------------+------------+----------+-------+ unique user1 1000.0 202003 hangzhou adidas user2 2000.0 202003 hangzhou zara user3 1500.0 202003 hangzhou veromoda user1 2000.0 202003 hangzhou nike user4 1000.0 202003 hangzhou ur user2 1400.0 202003 hangzhou ochirly user1 2000.0 202003 hangzhou lily user3 3000.0 202003 hangzhou onemore user2 1200.0 202003 hangzhou unique user1 1000.0 202001 shanghai adidas user2 2000.0 202001 shanghai zara user3 1500.0 202001 shanghai unique user1 1000.0 202004 beijing adidas user2 2000.0 202004 beijing zara user3 1500.0 202004 beijing veromoda user1 2000.0 202004 beijing nike user4 1000.0 202004 beijing ur user2 1400.0 202004 beijing ochirly user1 2000.0 202004 beijing lily user3 3000.0 202004 beijing onemore user2 1200.0 202004 beijing NOT IN SUBQUERYnot in subquery与left anti join用法类似，但并不完全相同。示例如下。 select * from mytable1 where id not in (select id from mytable2); --如果mytable2中的所有id都不为NULL，则等效于以下语句。 select * from mytable1 a left anti join mytable2 b on a.id = b.id;如果mytable2中有任意一列为NULL，则not in表达式会为NULL，导致where条件不成立，无数据返回，这点与left anti join不同。 EXISTS SUBQUERY/NOT EXISTS SUBQUERY使用exists subquery时，当Subquery中有至少一行数据时，返回True，否则返回False。使用not exists subquery时，则相反。目前只支持含有Correlatedwhere条件的子查询。exists subquery和not exists subquery实现的方式是转换为left semi join或者left anti join。 示例一 SELECT * from mytable1 where exists (select * from mytable2 where id = mytable1.id); --等效于以下语句。 SELECT * from mytable1 a left semi join mytable2 b on a.id = b.id; -- exists subquery select * from sale_detail where exists (select shop_name from shop where sale_detail.shop_name=shop_name);示例二 select * from mytable1 where not exists (select * from mytable2 where id = mytable1.id); --等效于以下语句。 select * from mytable1 a left anti join mytable2 b on a.id = b.id; SCALAR SUBQUERY当Subquery的输出结果为单行单列时，可以做为标量使用。 select * from t1 where (select count(*) from t2 where t1.a = t2.a) > 1; --等效于以下语句 select t1.* from t1 left semi join ( select a, count(*) from t2 group by a having count(*) > 1 ) t2 on t1.a = t2.a;select count(*) from t2 where t1.a = t2.a;的输出结果是一个Row Set，可以判断出这条语句的输出有且仅有一行一列。因此它可以作为标量，即可以参与标量运算（‘>’）。但在实现过程中，会尽可能地转成join来处理，例如上述示例中的等效语句。 交集、并集和补集下面介绍UNION、UNOIN ALL、UNION DISTINCT并集，INTERSECT、INTERSECT ALL、INTERSECT DISTINCT交集，EXCEPT、EXCEPT ALL、EXCEPT DISTINCT补集等SQL语法。 语法格式 select_statement UNION ALL select_statement; select_statement UNION [DISTINCT] select_statement; select_statement INTERSECT ALL select_statement; select_statement INTERSECT [DISTINCT] select_statement; select_statement EXCEPT ALL select_statement; select_statement EXCEPT [DISTINCT] select_statement; select_statement MINUS ALL select_statement; select_statement MINUS [DISTINCT] select_statement; UNION（并集）功能说明：求两个数据集的并集，即将两个数据集合并成一个数据集。 1. 当UNION后指定参数ALL时，返回两个数据集的所有记录： SELECT * FROM VALUES (1, 2), (1, 2), (3, 4) t(a, b) UNION ALL SELECT * FROM VALUES (1, 2), (1, 4) t(a, b);返回结果如下。 +------------+------------+ | a | b | +------------+------------+ | 1 | 2 | | 1 | 4 | | 1 | 2 | | 1 | 2 | | 3 | 4 | +------------+------------+
2. 当UNION后不指定参数时，返回记录中会去掉重复的记录，效果等同于UNION DISTINCT。示例如下。 SELECT * FROM VALUES (1, 2), (1, 2), (3, 4) t(a, b) UNION SELECT * FROM VALUES (1, 2), (1, 4) t(a, b); --等同于如下语句。 SELECT DISTINCT * FROM (）t;结果如下： +------------+------------+ | a | b | +------------+------------+ | 1 | 2 | | 1 | 4 | | 3 | 4 | +------------+------------+3. UNION后如果有CLUSTER BY、DISTRIBUTE BY、SORT BY、ORDER BY或者LIMIT子句，当设置set odps.sql.type.system.odps2=false;时，其作用于UNION的最后一个select_statement；当设置set odps.sql.type.system.odps2=true;时，作用于前面所有UNION的结果。 set odps.sql.type.system.odps2=true; SELECT explode(array(3, 1)) AS (a) UNION ALL SELECT explode(array(0, 4, 2)) AS (a) ORDER BY a LIMIT 3;返回结果如下。 +------+ | a | +------+ | 0 | | 1 | | 2 | +------+ INTERSECT（交集）功能说明：求两个数据集的交集，即输出两个数据集均包含的记录。 1. INTERSECT ALL示例 SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (5, 6) t(a, b) INTERSECT ALL SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (5, 7) t(a, b);返回结果如下： +------------+------------+ | a | b | +------------+------------+ | 1 | 2 | | 1 | 2 | | 3 | 4 | +------------+------------+2. INTERSECT DISTINCT示例 SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (5, 6) t(a, b) INTERSECT SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (5, 7) t(a, b);结果如下，等同于SELECT DISTINCT * FROM (< INTERSECT ALL的结果 >) t;语句。 +------------+------------+ | a | b | +------------+------------+ | 1 | 2 | | 3 | 4 | +------------+------------+ EXCEPT功能说明：求第二个数据集在第一个数据集中的补集，即输出第一个数据集包含而第二个数据集不包含的记录。 1. EXCEPT ALL示例。 SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (3, 4), (5, 6), (7, 8) t(a, b) EXCEPT ALL SELECT * FROM VALUES (3, 4), (5, 6), (5, 6), (9, 10) t(a, b);返回结果如下。 +------------+------------+ | a | b | +------------+------------+ | 1 | 2 | | 1 | 2 | | 3 | 4 | | 7 | 8 | +------------+------------+2. EXCEPT DISTINCT示例。 SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (3, 4), (5, 6), (7, 8) t(a, b) EXCEPT SELECT * FROM VALUES (3, 4), (5, 6), (5, 6), (9, 10) t(a, b);结果如下，相当于SELECT DISTINCT * FROM left_branch EXCEPT ALL SELECT DISTINCT * FROM right_branch;。 +------------+------------+ | a | b | +------------+------------+ | 1 | 2 | | 7 | 8 | +------------+------------+ MINUS功能说明：等同于EXCEPT。 Join表连接MaxCompute的JOIN操作分为左连接、右连接、全连接、内连接。支持多路连接，但不支持CROSS JOIN笛卡尔积，即无ON条件的连接。 语法格式命令格式如下。 join_table: table_reference join table_factor [join_condition] | table_reference {left outer|right outer|full outer|inner|Natural} join table_reference join_condition table_reference: table_factor | join_table table_factor: tbl_name [alias] | table_subquery alias | ( table_references ) join_condition: on equality_expression ( and equality_expression ) 语法说明1. Left Outer Join：左连接。返回左表中的所有记录，即使右表中没有与之匹配的记录。 select a.shop_name as ashop, b.shop_name as bshop from shop a left outer join sale_detail b on a.shop_name=b.shop_name;--由于表shop及sale_detail中都有shop_name列，因此需要在select子句中使用别名进行区分。说明 如果右边值不唯一，建议不要连续使用过多left join，以免在join的过程中产生数据膨胀，导致作业停滞。 2. Right Outer Join：右连接。返回右表中的所有记录，即使左表中没有与之匹配的记录。 select a.shop_name as ashop, b.shop_name as bshop from shop a right outer join sale_detail b on a.shop_name=b.shop_name;3. Full Outer Join：全连接。返回左右表中的所有记录。 select a.shop_name as ashop, b.shop_name as bshop from shop a full outer join sale_detail b on a.shop_name=b.shop_name;4. Inner Join：内连接，关键字inner可以省略。表中存在至少一个匹配时，inner join返回行。 select a.shop_name from shop a inner join sale_detail b on a.shop_name=b.shop_name; select a.shop_name from shop a join sale_detail b on a.shop_name=b.shop_name;5. Natural Join ：自然连接， 即参与JOIN的两张表根据字段名称自动决定连接字段。支持Outer Natural Join，支持使用Using子句执行JOIN，输出字段中公共字段只出现一次。假设存在表src（key1, key2, a1, a2），表src2（key1, key2, b1, b2）。 SELECT * FROM src NATURAL JOIN src2; --由于src和src2有两个同名字段(key1, key2)，所以上面的JOIN相当于： SELECT src.key1 as key1, src.key2 as key2, src.a1, src.a2, src2.b1, src2.b2 FROM src INNER JOIN src2 ON src.key1 = src2.key1 AND src.key2 = src2.key2;6. Implicit Join：隐式连接，即不指定Join关键字执行连接。 SELECT * FROM table1, table2 WHERE table1.id = table2.id; --执行的效果相当于以下语句。 SELECT * FROM table1 JOIN table2 ON table1.id = table2.id;连接条件：只允许and连接的等值条件。只有在MAPJOIN中，可以使用不等值连接或者使用or连接多个条件。 --支持多路Join连接示例。 select a.* from shop a full outer join sale_detail b on a.shop_name=b.shop_name full outer join sale_detail c on a.shop_name=c.shop_name; --不支持不等值Join连接条件，报错返回。 select a.* from shop a join sale_detail b on a.shop_name != b.shop_name;使用示例：假设表A为test_table_a，表B为test_table_b，查询两表中分区大于20180101且origin和id一致的记录数。使用left join可以保留左表中test_table_a的全表记录。 错误示例如下： SELECT s.id ,s.name ,s.origin ,d.value FROM test_table_a s LEFT JOIN test_table_b d ON s.origin = d.id WHERE s.ds > "20180101" AND d.ds>"20180101";如果join在where条件之前， 会先进行join操作，然后对join的结果执行where条件过滤。您会发现获取的结果是两个表的交集，而不是全表。修正后的SQL如下，通过这种方式可以显示全表： SELECT s.id ,s.name ,s.origin ,d.value FROM (select * from test_table_a where ds > "20180101" ) s LEFT JOIN (select * from test_table_b where ds > "20180101") d ON s.origin = d.id; SEMI JOIN（半连接）MaxCompute支持SEMI JOIN（半连接）。SEMI JOIN中，右表只用于过滤左表的数据而不出现在结果集中。支持LEFT SEMI JOIN和LEFT ANTI JOIN两种语法。 LEFT SEMI JOIN当join条件成立时，返回左表中的数据。如果mytable1中某行的id在mytable2的所有id中出现过，则此行保留在结果集中。 SELECT * from mytable1 a LEFT SEMI JOIN mytable2 b on a.id=b.id;只会返回mytable1中的数据，只要mytable1的id在mytable2的id中出现。 LEFT ANTI JOIN当join条件不成立时，返回左表中的数据。如果mytable1中某行的id在mytable2的所有id中没有出现过，则此行保留在结果集中。 SELECT * from mytable1 a LEFT ANTI JOIN mytable2 b on a.id=b.id;只会返回mytable1中的数据，只要mytable1的id在mytable2的id没有出现。 HAVING子句MaxCompute SQL的WHERE关键字无法与合计函数一起使用，此时您可以使用HAVING子句来实现。 命令格式如下。 SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value例如有一张订单表Orders，包括客户名称（Customer）、订单金额（OrderPrice）、订单日期（Order_date）、订单号（Order_id）四个字段。现在需要查找订单总额少于2000的客户，SQL语句如下所示。 SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice) |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |