您可以通过MaxCompute对查询结果数据集执行取交集、并集或补集操作。本文为您介绍交集(INTERSECT
、INTERSECT ALL
、INTERSECT DISTINCT
)、并集(UNION
、UNION ALL
、UNION DISTINCT
)和补集(
、EXCEPT
EXCEPT ALL
、EXCEPT DISTINCT
、MINUS
、MINUS ALL
、MINUS DISTINCT
)的使用方法。
功能介绍
使用限制
对数据集取交集、并集或补集的使用限制如下:
MaxCompute最多允许同时对256个数据集进行操作,超出256个将报错。
左右两个数据集的列数必须保持一致。
注意事项
对数据集取交集、并集或补集的注意事项如下:
对数据集进行操作的结果不一定会按序排列。
如果数据集的数据类型不一致,系统会进行隐式转换。由于兼容性原因,STRING类型和非STRING类型数据在集合操作中的隐式转换已被禁用。
交集
命令格式
--取交集不去重。 <select_statement1> INTERSECT ALL <select_statement2>; --取交集并去重。intersect效果等同于intersect distinct。 <select_statement1> INTERSECT [DISTINCT] <select_statement2>;
参数说明
select_statement1、select_statement2:必填。
select
语句,格式请参见SELECT语法。distinct:可选。对两个数据集取交集的结果去重。
使用示例
示例1:对两个数据集取交集,不去重。命令示例如下:
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:对两个查询结果取交集并去重。命令示例如下:
SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (5, 6) t(a, b) intersect distinct SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (5, 7) t(a, b); --等效于如下语句。 SELECT DISTINCT * FROM (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)) t;
返回结果如下:
+------------+------------+ | a | b | +------------+------------+ | 1 | 2 | | 3 | 4 | +------------+------------+
并集
命令格式
--取并集不去重。 <select_statement1> UNION ALL <select_statement2>; --取并集并去重。 <select_statement1> UNION [DISTINCT] <select_statement2>;
注意事项
存在多个
UNION ALL
时,支持通过括号指定UNION ALL
的优先级。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
的结果。
参数说明
select_statement1、select_statement2:必填。
select
语句,格式请参见SELECT语法。distinct:可选。对两个数据集取并集的结果去重。
使用示例
示例1:对两个数据集取并集,不去重。命令示例如下:
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 | 2 | | 3 | 4 | | 1 | 2 | | 1 | 4 | +------------+------------+
示例2:对两个数据集取并集并去重。命令示例如下:
SELECT * FROM VALUES (1, 2), (1, 2), (3, 4) t(a, b) UNION DISTINCT SELECT * FROM VALUES (1, 2), (1, 4) t(a, b); --等效于如下语句。 SELECT DISTINCT * FROM ( 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 | | 3 | 4 | +------------+------------+
示例3:通过括号指定
UNION ALL
的优先级。命令示例如下:SELECT * FROM VALUES (1, 2), (1, 2), (5, 6) t(a, b) 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 | 2 | | 5 | 6 | | 1 | 2 | | 1 | 2 | | 3 | 4 | | 1 | 2 | | 1 | 4 | +------------+------------+
示例4:
UNION
后有cluster by
、distribute by
、sort by
、order by
或limit
子句,设置set odps.sql.type.system.odps2=true;
属性。命令示例如下: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 | +------------+
示例5:
UNION
后有cluster by
、distribute by
、sort by
、order by
或limit
子句,设置set odps.sql.type.system.odps2=false;
属性。命令示例如下:SET odps.sql.type.system.odps2=false; SELECT explode(ARRAY(3, 1)) AS (a) UNION ALL SELECT explode(ARRAY(0, 4, 2)) AS (a) ORDER BY a limit 3;
返回结果如下:
+------------+ | a | +------------+ | 3 | | 1 | | 0 | | 2 | | 4 | +------------+
补集
命令格式
--取补集不去重。 <select_statement1> EXCEPT ALL <select_statement2>; <select_statement1> minus ALL <select_statement2>; --取补集并去重。 <select_statement1> EXCEPT [DISTINCT] <select_statement2>; <select_statement1> minus [DISTINCT] <select_statement2>;
说明except
和minus
等效。参数说明
select_statement1、select_statement2:必填。
select
语句,格式请参见SELECT语法。distinct:可选。对取补集的结果去重。
使用示例
示例1:求数据集的补集,不去重。命令示例如下:
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); --等效于如下语句。 SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (3, 4), (5, 6), (7, 8) t(a, b) MINUS 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:求数据集的补集并去重。命令示例如下:
SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (3, 4), (5, 6), (7, 8) t(a, b) EXCEPT DISTINCT SELECT * FROM VALUES (3, 4), (5, 6), (5, 6), (9, 10) t(a, b); --等效于如下语句。 SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (3, 4), (5, 6), (7, 8) t(a, b) MINUS DISTINCT SELECT * FROM VALUES (3, 4), (5, 6), (5, 6), (9, 10) t(a, b); --等效于如下语句。 SELECT DISTINCT * 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 | | 7 | 8 | +------------+------------+