全部產品
Search
文件中心

MaxCompute:交集(INTERSECT)、並集(UNION)和補集(EXCEPT)

更新時間:Feb 28, 2024

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