すべてのプロダクト
Search

このプロダクト

    PolarDB:GROUPING SETS、ROLLUP、およびCUBE拡張

    ドキュメントセンター

    PolarDB:GROUPING SETS、ROLLUP、およびCUBE拡張

    最終更新日:Jun 04, 2024

    リレーショナルデータベースでは、複数のディメンションのグループに基づいて結果をグループ化するには、複数のSELECTおよびUNIONステートメントを使用する必要があります。 PolarDB-Xは、複数のディメンショングループに基づいて結果をグループ化できるGROUPING SETS、ROLLUP、およびCUBE拡張機能を提供します。 さらに、PolarDB-Xでは、SELECTステートメントまたはHAVING句でGROUPING関数とGROUPING_ID関数を使用できます。 これは、前述の拡張の結果を説明するために使用できます。 このトピックでは、構文について説明し、GROUPING SETS、ROLLUP、およびCUBE拡張機能、およびGROUPING関数とGROUPING_ID関数の例を示します。

    注意

    • このトピックで説明するすべてのGROUP BY拡張機能の構文では、SQLクエリをLogicalView演算子にプッシュダウンして実行することはできません。 SQLクエリのプッシュダウンの詳細については、クエリのプッシュダウンと書き換え.
    • このトピックの例では、次のテストデータを使用しています。
      次のステートメントを実行して、requestsという名前のテーブルを作成します。
      create table requests (
  'id' int (10) UNSIGNED NOT NULL,
  'os' varchar(20) DEFAULT NULL、
  'device' varchar(20) DEFAULT NULL、
  'city' varchar(20) DEFAULT NULL、
  主要なキー ('id')
) ENGINE = InnoDB DEFAULT CHARSET = utf8 dbpartition BY hash('id') tbpartition BY hash('id');
      insert into requests (id、os、device、city) VALUES INSERT INTO requestsテーブルに必要なテストデータを挿入するには、次のステートメントを実行します。
      
(1、「ウィンドウ」、「PC」、「北京」) 、(2、「ウィンドウ」、「PC」、「石家荘」) 、(3、'linux' 、'Phone' 、'Beijing') 、(4、「ウィンドウ」、「PC」、「北京」) 、(5、「ios」、「電話」、「石家荘」) 、(6、'linux '、'PC' 、'Beijing') 、(7、「窓」、「電話」、「石家荘」);

    GROUPING SETS 拡張

    • 概要

      GROUPING SETSはGROUP BY句の拡張であり、結果セットを生成するために使用できます。 結果セットは、異なるグループに基づく複数の結果セットの連結である。 GROUPING SETS拡張によって返される結果は、UNION ALL演算子の結果と同様です。 UNION ALL演算子とGROUPING SETS拡張では、結合された結果セットから重複する行は削除されません。

    • グループ化セットの構文 
      (
  {expr_1 | ( expr_1a [, expr_1b ] ...) |
    ROLLUP ( expr_list ) | CUBE ( expr_list )
  } [, ...] )
      説明 GROUPING SETS拡張機能には、expr_1や (expr_1a [, expr_1b ] ...) などのコンマ区切りの式と、( expr_list ) などの括弧 () で囲まれた式のリストを含めることができます。 構文では:
      • 各式を使用して、結果セットのグループ化方法を決定できます。
      • ROLLUPまたはCUBEエクステンションをGROUPING SETSエクステンションにネストできます。
    • 例
      • GROUPING SETS拡張機能を使用して、クエリするデータをグループ化できます。 次のコードブロックは、関連する構文を提供します: 
        select os,device, city ,count(*)
要求から
グループ化セット ((os, device), (city), ());

        上記のステートメントは、次のステートメントと同等です。

        select os, device, NULL, count(*)
os、デバイスによる要求グループから
すべてのユニオン
NULL, NULL, NULL, count(*) を選択します。要求から
すべてのユニオン
セレクトnull, null, city, count(*)
リクエストグループからの都市;
        次の結果が返されます: 
        -------- --------- --------------- -------------
| os | デバイス | 都市 | count(*) |
+ -------- -------- ------------------------------------- +
| windows | PC | NULL | 3 |
| linux | PC | NULL | 1 |
| linux | 電話 | NULL | 1 |
| windows | 電話 | NULL | 1 |
| ios | 電話 | NULL | 1 |
| NULL | NULL | 石家荘 | 3 |
| NULL | NULL | 北京 | 4 |
| NULL | NULL | NULL | 7 |
+ -------- -------- --------------------------------------
        説明 式がグループ化セットで使用されていない場合、NULLは式のプレースホルダーとして使用されます。 このようにして、グループ化セットで使用されていない結果セットに対して操作を実行できます。 たとえば、結果セットは、返された結果でcity列の値がNULLである行のグループです。
      • ROLLUPエクステンションをGROUPING SETSエクステンションにネストすることで、データをグループ化できます。 次のコードブロックは、関連する構文を示しています: 
        select os,device, city ,count(*) from requests
グループ化セット ((都市) 、ROLLUP(os、デバイス));

上記のステートメントは、次のステートメントと同等です。リクエストからos、デバイス、都市、カウント (*) を選択
グループ化セット ((city) 、(os) 、(os、device) 、());
        次の結果が返されます: 
        -------- --------- --------------- -------------
| os | デバイス | 都市 | count(*) |
+ -------- -------- ------------------------------------- +
| NULL | NULL | 石家荘 | 3 |
| NULL | NULL | 北京 | 4 |
| windows | PC | NULL | 3 |
| linux | PC | NULL | 1 |
| ios | 電話 | NULL | 1 |
| linux | 電話 | NULL | 1 |
| windows | 電話 | NULL | 1 |
| windows | NULL | NULL | 4 |
| linux | NULL | NULL | 2 |
| ios | NULL | NULL | 1 |
| NULL | NULL | NULL | 7 |
+ -------- -------- --------------------------------------
      • GROUPING SETSエクステンションにネストされたCUBEエクステンションを指定することで、データをグループ化できます。 次のコードブロックは、関連する構文を示しています: 
        select os,device, city ,count(*) from requests
グループ化セット ((都市) 、CUBE(os、デバイス));

上記のステートメントは、次のステートメントと同等です。リクエストからos、デバイス、都市、カウント (*) を選択
グループ化セット ((都市) 、(os) 、(os、デバイス) 、() 、(デバイス));
        次の結果が返されます: 
        -------- --------- --------------- -------------
| os | デバイス | 都市 | count(*) |
+ -------- -------- ------------------------------------- +
| NULL | NULL | 北京 | 4 |
| NULL | NULL | 石家荘 | 3 |
| windows | PC | NULL | 3 |
| ios | 電話 | NULL | 1 |
| linux | 電話 | NULL | 1 |
| windows | 電話 | NULL | 1 |
| linux | PC | NULL | 1 |
| windows | NULL | NULL | 4 |
| ios | NULL | NULL | 1 |
| linux | NULL | NULL | 2 |
| NULL | PC | NULL | 4 |
| NULL | 電話 | NULL | 3 |
| NULL | NULL | NULL | 7 |
+ -------- -------- --------------------------------------
      • GROUP BY句とCUBEおよびGROUPING SETS拡張を組み合わせて、次の例
        に示すようにグループ化セットを生成できます。要求から
os、キューブ (os、デバイス) 、グループ化セット (都市) によるグループ化。上記のステートメントは、次のステートメントと同等です。select os,device, city, count(*)
要求から
グループ化セット ((os,device,city),(os,city),(os,device,city));
        次の結果が返されます: 
        -------- --------- --------------- -------------
| os | デバイス | 都市 | count(*) |
+ -------- -------- ------------------------------------- +
| linux | 電話 | 北京 | 1 |
| windows | 電話 | 石家荘 | 1 |
| windows | PC | 石家荘 | 1 |
| linux | PC | 北京 | 1 |
| windows | PC | 北京 | 2 |
| ios | 電話 | 石家荘 | 1 |
| linux | NULL | 北京 | 2 |
| windows | NULL | 石家荘 | 2 |
| windows | NULL | 北京 | 2 |
| ios | NULL | 石家荘 | 1 |
+ -------- -------- --------------------------------------

    ROLLUP 拡張

    • 概要

      ROLLUP拡張は、グループの階層的セットを生成する。 このセットには、各階層グループの総合計と小計が含まれています。 階層的な順序は、ROLLUP式リストで指定されている式の順序によって決まります。 階層の一番上は、リストの左側から最初の項目です。 右側に進む各連続アイテムは、階層の下に移動します。 階層内の最後の項目は最下位レベルにあります。

    • ROLLUP構文 
      ( { expr_1 | ( expr_1a [, expr_1b ] ...) })
  [, expr_2 | ( expr_2a [, expr_2b ] ...) ] ...)
      説明
      • 各式は、結果セットのグループ化方法を決定するために使用されます。 式が ( expr_1a, expr_1b, ...) のように括弧 () で囲まれている場合、expr_1aとexpr_1bによって返される値の組み合わせは、階層の単一のグループ化レベルを示します。
      • expr_1または ( expr_1a, expr_1b, ...) の組み合わせなど、リスト内の最初の項目について、PolarDB-Xは最初の項目の一意の値ごとに小計を返します。 expr_2または ( expr_2a, expr_2b, ...) の組み合わせなど、リスト内の2番目のアイテムについて、PolarDB-Xは、2番目のアイテム内の各グループの一意の値ごとに小計を返します。 同様のルールは、後続のアイテムの各グループ化レベルで使用されます。 その結果、PolarDB-Xは結果セット全体の総合計を返します。
      • 小計行の場合、小計が取られる項目に対してNULLが返されます。
    • 例
      • ROLLUPは、(os, device, city) を階層的に集約してグループ化セットを生成するために使用される。 次のコードブロックは、関連する構文を示しています: 
        select os,device, city, count(*)
要求から
ロールアップによるグループ (os、デバイス、都市);

上記のステートメントは、次のステートメントと同等です。select os,device, city, count(*)
要求から
グループによるos、デバイス、都市ロールアップ;

最初のステートメントも次のステートメントと同じです。select os,device, city, count(*)
要求から
グループ化セット ((os, device, city),(os, device),(os),());
        次の結果が返されます: 
        -------- --------- --------------- -------------
| os | デバイス | 都市 | count(*) |
+ -------- -------- ------------------------------------- +
| windows | PC | 北京 | 2 |
| ios | 電話 | 石家荘 | 1 |
| windows | PC | 石家荘 | 1 |
| linux | PC | 北京 | 1 |
| linux | 電話 | 北京 | 1 |
| windows | 電話 | 石家荘 | 1 |
| windows | PC | NULL | 3 |
| ios | 電話 | NULL | 1 |
| linux | PC | NULL | 1 |
| linux | 電話 | NULL | 1 |
| windows | 電話 | NULL | 1 |
| windows | NULL | NULL | 4 |
| ios | NULL | NULL | 1 |
| linux | NULL | NULL | 2 |
| NULL | NULL | NULL | 7 |
+ -------- -------- --------------------------------------
      • ROLLUPは、os、(os、device) 、およびcityを階層的に集約してグループ化セットを生成するために使用されます。 次のコードブロックは、関連する構文を示しています: 
        select os,device, city, count(*)
要求から
ロールアップによるグループ (os、(os、device) 、city);

上記のステートメントは、次のステートメントと同等です。select os,device, city, count(*)
要求から
グループによるos、(os、デバイス) 、都市ロールアップ;

最初のステートメントも次のステートメントと同じです。select os,device, city, count(*)
要求から
グループ化セット ((os, device, city),(os, device),(os),());
        次の結果が返されます: 
        -------- --------- --------------- -------------
| os | デバイス | 都市 | count(*) |
+ -------- -------- ------------------------------------- +
| windows | PC | 北京 | 2 |
| windows | PC | 石家荘 | 1 |
| linux | PC | 北京 | 1 |
| linux | 電話 | 北京 | 1 |
| windows | 電話 | 石家荘 | 1 |
| ios | 電話 | 石家荘 | 1 |
| windows | PC | NULL | 3 |
| linux | PC | NULL | 1 |
| linux | 電話 | NULL | 1 |
| windows | 電話 | NULL | 1 |
| ios | 電話 | NULL | 1 |
| windows | NULL | NULL | 4 |
| linux | NULL | NULL | 2 |
| ios | NULL | NULL | 1 |
| NULL | NULL | NULL | 7 |
+ -------- -------- --------------------------------------

    CUBE 拡張

    • 概要

      CUBE拡張は、ROLLUP拡張と同様である。 ROLLUP拡張は、グループを生成し、ROLLUP式リスト内のアイテムの左から右へのリストに基づく階層をもたらす。 CUBE拡張は、CUBE式リスト内のすべての項目の各順列に基づいて、グルーピングと小計を生成します。 CUBE拡張は、同じ式リストで実行されたROLLUP拡張よりも多くの行を生成された結果セットで返します。

    • CUBE構文 
      ( { expr_1 | ( expr_1a [, expr_1b ] ...) })
  [, expr_2 | ( expr_2a [, expr_2b ] ...) ] ...)
      説明
      • 各式は、結果セットのグループ化方法を決定するために使用されます。 式が ( expr_1a, expr_1b, ...) のように括弧 () で囲まれている場合、expr_1aおよびexpr_1bによって返される値の組み合わせは、単一のグループを定義します。
      • expr_1または ( expr_1a, expr_1b, ...) の組み合わせなど、リスト内の最初の項目について、PolarDB-Xは最初の項目の一意の値ごとに小計を返します。 expr_2または ( expr_2a, expr_2b, ...) の組み合わせなど、リスト内の2番目のアイテムについて、PolarDB-Xは2番目のアイテムの一意の値ごとに小計を返します。 小計も、第1および第2のアイテムの一意の組み合わせごとに返される。 3番目のアイテムが存在する場合、PolarDB-Xは、3番目のアイテムの各一意の値、3番目と1番目のアイテムの各一意の組み合わせ、3番目と2番目のアイテムの各一意の組み合わせ、3番目、2番目、1番目のアイテムの各一意の組み合わせの小計を返します。 その結果、結果セット全体の総合計が返されます。
      • 小計行の場合、小計が取得された項目に対してNULLが返されます。
    • 例
      • CUBEは、(os、device、city) 列のすべての可能な組み合わせをグループ化セットとしてリストします。 次のコードブロックは、関連する構文を示しています: 
        select os,device, city, count(*)
要求から
キューブ (os、デバイス、都市) によるグループ。上記のステートメントは、次のステートメントと同等です。select os,device, city, count(*)
要求から
セットをグループ化してグループ化します ((os, device, city) 、(os, device) 、(os, city) 、(device, city) 、(os) 、(device) 、(city) 、());
        次の結果が返されます: 
        -------- --------- --------------- -------------
| os | デバイス | 都市 | count(*) |
+ -------- -------- ------------------------------------- +
| linux | 電話 | 北京 | 1 |
| windows | 電話 | 石家荘 | 1 |
| windows | PC | 北京 | 2 |
| ios | 電話 | 石家荘 | 1 |
| windows | PC | 石家荘 | 1 |
| linux | PC | 北京 | 1 |
| linux | 電話 | NULL | 1 |
| windows | 電話 | NULL | 1 |
| windows | PC | NULL | 3 |
| ios | 電話 | NULL | 1 |
| linux | PC | NULL | 1 |
| linux | NULL | 北京 | 2 |
| windows | NULL | 石家荘 | 2 |
| windows | NULL | 北京 | 2 |
| ios | NULL | 石家荘 | 1 |
| linux | NULL | NULL | 2 |
| windows | NULL | NULL | 4 |
| ios | NULL | NULL | 1 |
| NULL | 電話 | 北京 | 1 |
| NULL | 電話 | 石家荘 | 2 |
| NULL | PC | 北京 | 3 |
| NULL | PC | 石家荘 | 1 |
| NULL | 電話 | NULL | 3 |
| NULL | PC | NULL | 4 |
| NULL | NULL | 北京 | 4 |
| NULL | NULL | 石家荘 | 3 |
| NULL | NULL | NULL | 7 |
+ -------- -------- --------------------------------------
      • CUBEは、(os, device) 、(device, city) 列のすべての可能な組み合わせをグループ化セットとしてリストします。
        select os,device, city, count(*)
要求から
キューブによるグループ ((os、デバイス) 、(デバイス、都市));

上記のステートメントは、次のステートメントと同等です。select os,device, city, count(*)
要求から
グループ化セット ((os, device, city),(os, device),(device, city),());
        次の結果が返されます: 
        -------- --------- --------------- -------------
| os | デバイス | 都市 | count(*) |
+ -------- -------- ------------------------------------- +
| linux | 電話 | 北京 | 1 |
| windows | 電話 | 石家荘 | 1 |
| windows | PC | 北京 | 2 |
| windows | PC | 石家荘 | 1 |
| linux | PC | 北京 | 1 |
| ios | 電話 | 石家荘 | 1 |
| linux | 電話 | NULL | 1 |
| windows | 電話 | NULL | 1 |
| windows | PC | NULL | 3 |
| linux | PC | NULL | 1 |
| ios | 電話 | NULL | 1 |
| NULL | 電話 | 北京 | 1 |
| NULL | 電話 | 石家荘 | 2 |
| NULL | PC | 北京 | 3 |
| NULL | PC | 石家荘 | 1 |
| NULL | NULL | NULL | 7 |
+ -------- -------- --------------------------------------

    GROUPINGおよびGROUPING_ID関数

    • 概要
      • GROUPING関数

        GROUP BY句でGROUPING SETS、ROLLUP、またはCUBE拡張を使用する場合、GROUPING SETS拡張の戻り値のプレースホルダーとしてNULLが使用されます。 その結果、プレースホルダNULLはNULL値と区別することができない。 この場合、PolarDB-Xが提供するGROUPING関数を使用して、プレースホルダーとNULL値を識別できます。

        GROUPING関数を使用すると、列名をパラメーターとして使用できます。 対応する行が列に基づいて集計されている場合、結果には0が返されます。 この場合、NULLは値です。 対応する行が列に基づいて集約されていない場合、1が返されます。 この場合、NULLはGROUPING SETS拡張の戻り値のプレースホルダーです。

      • GROUPING_ID関数

        GROUPING_ID関数は、GROUPING関数を簡素化します。 GROUPING_ID関数は、ROLLBACK、CUBE、またはGROUPING SETS拡張の結果セット内の行の小計レベルを決定するために使用されます。 GROUPING関数は、1つの列式のみを使用し、指定された列のすべての値に対して行が小計であるかどうかを示す値を返します。 複数のグループ化列を含むクエリの小計のレベルを決定するには、複数のGROUPING関数が必要になる場合があります。 GROUPING_ID関数は、ROLLBACK、CUBE、またはGROUPING SETS拡張で指定された1つ以上の列式をサポートし、単一の整数を返します。 この整数は、小計が集約される列を示します。

    • 構文
      • GROUPING関数
        SELECT [ expr ...,] GROUPING( col_expr ) [, expr ] ...
FROM ...
GROUP BY { ROLLUP | キューブ | GROUPING SETS }( [...,] col_expr
  [, ...] ) [, ...]
        説明 GROUPING関数は単一のパラメーターを使用します。 このパラメーターは、GROUP BY句のROLLUP、CUBE、またはGROUPING SETS拡張の式リストで指定されたディメンション列の式である必要があります。
      • GROUPING_ID関数
        SELECT [ expr ...,]
  GROUPING_ID( col_expr_1 [, col_expr_2 ] ... )
  [, expr ] ...
FROM ...
GROUP BY { ROLLUP | キューブ | GROUPING SETS }( [...,] col_expr_1
  [, col_expr_2 ] [, ...] ) [, ...]
    • 例
      GROUPING_ID関数は、複数の列名をパラメーターとして使用します。 この関数は、ビットマップアルゴリズムを使用して、パラメーター列のグループ化結果を整数に変換します。 次のコードブロックは、関連する構文を示しています: 
      select a,b,c,count(*),
grouping(a) ga、grouping(b) gb、grouping(c) gc、grouping_id(a,b,c) groupingid
から (1をaとして、2をbとして、3をcとして選択)
キューブによるグループ (a、b、c);
      次の結果が返されます: 
      ------- -------- ---------- ---------- ----------- -----------------------------------
| a | b | c | count(*) | ga | gb | gc | groupingid |
------ ------- --------------------------------------------------------
| 1 | 2 | 3 | 1 | 0 | 0 | 0 | 0 |
| 1 | 2 | NULL | 1 | 0 | 0 | 1 | 1 |
| 1 | NULL | 3 | 1 | 0 | 1 | 0 | 2 |
| 1 | NULL | NULL | 1 | 0 | 1 | 1 | 3 |
| NULL | 2 | 3 | 1 | 1 | 0 | 0 | 4 |
| NULL | 2 | NULL | 1 | 1 | 0 | 1 | 5 |
| NULL | NULL | 3 | 1 | 1 | 1 | 0 | 6 |
| NULL | NULL | NULL | 1 | 1 | 1 | 1 | 7 |
------ ------- -------------------------------------------------------