文档

交集(INTERSECT)、并集(UNION)和补集(EXCEPT)

更新时间:

您可以通过MaxCompute对查询结果数据集执行取交集、并集或补集操作。本文为您介绍交集(intersectintersect allintersect distinct)、并集(unionunion allunion distinct)和补集(exceptexcept allexcept distinctminusminus allminus distinct)的使用方法。

功能介绍

MaxCompute支持如下三种操作:

  • 交集:求两个数据集的交集,即输出两个数据集均包含的记录。

  • 并集:求两个数据集的并集,即将两个数据集合并成一个数据集。

  • 补集:求第二个数据集在第一个数据集中的补集,即输出第一个数据集包含而第二个数据集不包含的记录。

使用限制

对数据集取交集、并集或补集的使用限制如下:

  • MaxCompute最多允许同时对256个数据集进行操作,超出256个将报错。

  • 左右两个数据集的列数必须保持一致。

注意事项

对数据集取交集、并集或补集的注意事项如下:

  • 对数据集进行操作的结果不一定会按序排列。

  • 如果数据集的数据类型不一致,系统会进行隐式转换。由于兼容性原因,STRING类型和非STRING类型数据在集合操作中的隐式转换已被禁用。

交集

  • 命令格式

    --取交集不去重。
    <select_statement1> intersect all <select_statement2>;
    --取交集并去重。intersect效果等同于intersect distinct。
    <select_statement1> intersect [distinct] <select_statement2>;
  • 参数说明

    • select_statement1select_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 bydistribute bysort byorder bylimit子句时,如果设置set odps.sql.type.system.odps2=false;,其作用于union的最后一个select_statement;如果设置set odps.sql.type.system.odps2=true;时,作用于前面所有union的结果。

  • 参数说明

    • select_statement1select_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 bydistribute bysort byorder bylimit子句,设置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 bydistribute bysort byorder bylimit子句,设置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>;
    说明

    exceptminus等效。

  • 参数说明

    • select_statement1select_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          |
      +------------+------------+