您可以通過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 | +------------+------------+