幫助中心
首頁 PolarDB 雲原生資料庫PolarDB分布式版 開發指南 企業版（分布式） SQL語句相關操作 Grouping Sets、Rollup和Cube擴充

Grouping Sets、Rollup和Cube擴充

更新時間：
Copy as MD
產品

在關係型資料庫中，通常需要使用多個SELECT + UNION語句來實現按照多組維度結果分組，PolarDB-X新增支援通過Grouping Sets、Rollup和Cube擴充來實現這一目的。此外，PolarDB-X還支援在SELECT命令或HAVING子句中使用GROUPING函數和GROUPING_ID函數，來協助解釋使用上述擴充的結果。本文將介紹相關文法和樣本。

注意事項

  • 本文介紹的所有GROUP BY相關的擴充文法，均不支援查詢下推至LogicalView運算元中執行。關於查詢下推，請參見查詢改寫與下推。
  • 本文樣本中所用測試資料資訊如下：
    使用如下語句建立一張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,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 dbpartition BY hash(`id`) tbpartition BY hash(`id`);
    在requests表中使用如下語句插入測試所需的資料：
    INSERT INTO requests (id, os, device, city) VALUES
(1, 'windows', 'PC', 'Beijing'),
(2, 'windows', 'PC', 'Shijiazhuang'),
(3, 'linux', 'Phone', 'Beijing'),
(4, 'windows', 'PC', 'Beijing'),
(5, 'ios', 'Phone', 'Shijiazhuang'),
(6, 'linux', 'PC', 'Beijing'),
(7, 'windows', 'Phone', 'Shijiazhuang');

GROUPING SETS擴充

  • 功能介紹

    GROUPING SETS是GROUP BY子句的擴充，可以產生一個結果集，該結果集實際上是基於不同分組的多個結果集的串聯（與UNION ALL運算結果類似），但UNION ALL運算和GROUPING SETS擴充並不會消除或合并結果集中的重複行。

  • 文法
    GROUPING SETS (
  { expr_1 | ( expr_1a [, expr_1b ] ...) |
    ROLLUP ( expr_list ) | CUBE ( expr_list )
  } [, ...] )
    說明 GROUPING SETS擴充可包含一個或多個由半形逗號（,）分隔運算式（如expr_1或(expr_1a [, expr_1b ] ...)）的任意組合，以及帶半形圓括弧（()）的運算式列表（如( expr_list )），其中：
    • 每個運算式都可用於確定結果集的分組方式。
    • GROUPING SETS內也支援嵌套使用ROLLUP或者CUBE。
  • 示例
    • 通過GROUPING SETS擴充對資料進行分組查詢，文法如下：
      select os,device, city ,count(*)
from requests
group by grouping sets((os, device), (city), ());

      上述語句等效於如下語句：

      select os, device, NULL, count(*)
from requests group by os, device
union all
select NULL, NULL, NULL, count(*)
from requests
union all
select null, null, city, count(*)
from requests group by city;
      返回結果如下：
      +---------+--------+--------------+----------+
| os      | device | city         | count(*) |
+---------+--------+--------------+----------+
| windows | PC     | NULL         |        3 |
| linux   | PC     | NULL         |        1 |
| linux   | Phone  | NULL         |        1 |
| windows | Phone  | NULL         |        1 |
| ios     | Phone  | NULL         |        1 |
| NULL    | NULL   | Shijiazhuang |        3 |
| NULL    | NULL   | Beijing      |        4 |
| NULL    | NULL   | NULL         |        7 |
+---------+--------+--------------+----------+
      說明 未在分組集中使用的運算式，會用NULL充當預留位置，便於對這些未在分組集使用的結果集進行操作，例如結果city列中顯示為NULL的行。
    • 通過在GROUPING SETS中嵌套ROLLUP來對資料進行分組，文法如下：
      select os,device, city ,count(*) from requests 
group by grouping sets((city), ROLLUP(os, device));

上述語句等效於如下語句：
select os,device, city ,count(*) from requests 
group by grouping sets((city), (os), (os, device), ());
      返回結果如下：
      +---------+--------+--------------+----------+
| os      | device | city         | count(*) |
+---------+--------+--------------+----------+
| NULL    | NULL   | Shijiazhuang |        3 |
| NULL    | NULL   | Beijing      |        4 |
| windows | PC     | NULL         |        3 |
| linux   | PC     | NULL         |        1 |
| ios     | Phone  | NULL         |        1 |
| linux   | Phone  | NULL         |        1 |
| windows | Phone  | 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 
group by grouping sets((city), CUBE(os, device));

上述語句等效於如下語句：
select os,device, city ,count(*) from requests 
group by grouping sets((city), (os), (os, device), (), (device));
      返回結果如下：
      +---------+--------+--------------+----------+
| os      | device | city         | count(*) |
+---------+--------+--------------+----------+
| NULL    | NULL   | Beijing      |        4 |
| NULL    | NULL   | Shijiazhuang |        3 |
| windows | PC     | NULL         |        3 |
| ios     | Phone  | NULL         |        1 |
| linux   | Phone  | NULL         |        1 |
| windows | Phone  | NULL         |        1 |
| linux   | PC     | NULL         |        1 |
| windows | NULL   | NULL         |        4 |
| ios     | NULL   | NULL         |        1 |
| linux   | NULL   | NULL         |        2 |
| NULL    | PC     | NULL         |        4 |
| NULL    | Phone  | NULL         |        3 |
| NULL    | NULL   | NULL         |        7 |
+---------+--------+--------------+----------+
    • 通過GROUP BY、CUBE和GROUPING SETS組合產生GROUPING SETS，樣本如下：
      select os,device, city, count(*)
from requests 
group by os, cube(os,device), grouping sets(city);

上述語句等效於如下語句：
select os,device, city, count(*)
from requests 
group by grouping sets((os,device,city),(os,city),(os,device,city));
      返回結果如下：
      +---------+--------+--------------+----------+
| os      | device | city         | count(*) |
+---------+--------+--------------+----------+
| linux   | Phone  | Beijing      |        1 |
| windows | Phone  | Shijiazhuang |        1 |
| windows | PC     | Shijiazhuang |        1 |
| linux   | PC     | Beijing      |        1 |
| windows | PC     | Beijing      |        2 |
| ios     | Phone  | Shijiazhuang |        1 |
| linux   | NULL   | Beijing      |        2 |
| windows | NULL   | Shijiazhuang |        2 |
| windows | NULL   | Beijing      |        2 |
| ios     | NULL   | Shijiazhuang |        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, ...)的組合，PolarDB-X將為第二項的每個分組中的每個唯一值返回一個小計，依此類推。最後，PolarDB-X將為整個結果集返回一個總計。
    • 對於小計行，將為小計包含的各項返回NULL。
  • 示例
    • 通過ROLLUP對(os, device, city)按層級彙總的方式產生GROUPING SETS，文法如下：
      select os,device, city, count(*)
from requests 
group by rollup (os, device, city);

上述語句等效於如下語句：
select os,device, city, count(*)
from requests 
group by os, device, city with rollup;

也等效於如下語句：
select os,device, city, count(*)
from requests 
group by grouping sets ((os, device, city),(os, device),(os),());
      返回結果如下：
      +---------+--------+--------------+----------+
| os      | device | city         | count(*) |
+---------+--------+--------------+----------+
| windows | PC     | Beijing      |        2 |
| ios     | Phone  | Shijiazhuang |        1 |
| windows | PC     | Shijiazhuang |        1 |
| linux   | PC     | Beijing      |        1 |
| linux   | Phone  | Beijing      |        1 |
| windows | Phone  | Shijiazhuang |        1 |
| windows | PC     | NULL         |        3 |
| ios     | Phone  | NULL         |        1 |
| linux   | PC     | NULL         |        1 |
| linux   | Phone  | NULL         |        1 |
| windows | Phone  | NULL         |        1 |
| windows | NULL   | NULL         |        4 |
| ios     | NULL   | NULL         |        1 |
| linux   | NULL   | NULL         |        2 |
| NULL    | NULL   | NULL         |        7 |
+---------+--------+--------------+----------+
    • 通過ROLLUP對os, (os,device), city按層級彙總的方式產生GROUPING SETS，文法如下：
      select os,device, city, count(*)
from requests 
group by rollup (os, (os,device), city);

上述語句等效於如下語句：
select os,device, city, count(*)
from requests 
group by os, (os,device), city with rollup;

也等效於如下語句：
select os,device, city, count(*)
from requests 
group by grouping sets ((os, device, city),(os, device),(os),());
      返回結果如下：
      +---------+--------+--------------+----------+
| os      | device | city         | count(*) |
+---------+--------+--------------+----------+
| windows | PC     | Beijing      |        2 |
| windows | PC     | Shijiazhuang |        1 |
| linux   | PC     | Beijing      |        1 |
| linux   | Phone  | Beijing      |        1 |
| windows | Phone  | Shijiazhuang |        1 |
| ios     | Phone  | Shijiazhuang |        1 |
| windows | PC     | NULL         |        3 |
| linux   | PC     | NULL         |        1 |
| linux   | Phone  | NULL         |        1 |
| windows | Phone  | NULL         |        1 |
| ios     | Phone  | 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運算式列表中所有項的每個排列產生分組和小計。因此，與對同一運算式列表執行的ROLLUP相比，CUBE結果集會包含更多的行。

  • 文法
    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, ...)的組合，PolarDB-X在為每個唯一值返回一個小計的同時，還將為第一項和第二項的每個唯一組合返回一個小計。如果存在第三項，PolarDB-X則會為第三項的每個唯一值、第三項和第一項組合的每個唯一值、第三項和第二項組合的每個唯一值以及第三項、第二項和第一項組合的每個唯一值返回一個小計。最後，再將為整個結果集返回一個總計。
    • 對於小計行，將為小計包含的各項返回NULL。
  • 示例
    • 通過CUBE枚舉(os, device, city)的所有可能列為GROUPING SETS，文法如下：
      select os,device, city, count(*)
from requests 
group by cube (os, device, city);

上述語句等效於如下語句：
select os,device, city, count(*)
from requests 
group by grouping sets ((os, device, city),(os, device),(os, city),(device,city),(os),(device),(city),());
      返回結果如下：
      +---------+--------+--------------+----------+
| os      | device | city         | count(*) |
+---------+--------+--------------+----------+
| linux   | Phone  | Beijing      |        1 |
| windows | Phone  | Shijiazhuang |        1 |
| windows | PC     | Beijing      |        2 |
| ios     | Phone  | Shijiazhuang |        1 |
| windows | PC     | Shijiazhuang |        1 |
| linux   | PC     | Beijing      |        1 |
| linux   | Phone  | NULL         |        1 |
| windows | Phone  | NULL         |        1 |
| windows | PC     | NULL         |        3 |
| ios     | Phone  | NULL         |        1 |
| linux   | PC     | NULL         |        1 |
| linux   | NULL   | Beijing      |        2 |
| windows | NULL   | Shijiazhuang |        2 |
| windows | NULL   | Beijing      |        2 |
| ios     | NULL   | Shijiazhuang |        1 |
| linux   | NULL   | NULL         |        2 |
| windows | NULL   | NULL         |        4 |
| ios     | NULL   | NULL         |        1 |
| NULL    | Phone  | Beijing      |        1 |
| NULL    | Phone  | Shijiazhuang |        2 |
| NULL    | PC     | Beijing      |        3 |
| NULL    | PC     | Shijiazhuang |        1 |
| NULL    | Phone  | NULL         |        3 |
| NULL    | PC     | NULL         |        4 |
| NULL    | NULL   | Beijing      |        4 |
| NULL    | NULL   | Shijiazhuang |        3 |
| NULL    | NULL   | NULL         |        7 |
+---------+--------+--------------+----------+
    • 通過CUBE枚舉(os, device),(device, city)所有可能列為GROUPING SETS，文法如下：
      select os,device, city, count(*) 
from requests 
group by cube ((os, device), (device, city));

上述語句等效於如下語句：
select os,device, city, count(*) 
from requests 
group by grouping sets ((os, device, city),(os, device),(device,city),());
      返回結果如下：
      +---------+--------+--------------+----------+
| os      | device | city         | count(*) |
+---------+--------+--------------+----------+
| linux   | Phone  | Beijing      |        1 |
| windows | Phone  | Shijiazhuang |        1 |
| windows | PC     | Beijing      |        2 |
| windows | PC     | Shijiazhuang |        1 |
| linux   | PC     | Beijing      |        1 |
| ios     | Phone  | Shijiazhuang |        1 |
| linux   | Phone  | NULL         |        1 |
| windows | Phone  | NULL         |        1 |
| windows | PC     | NULL         |        3 |
| linux   | PC     | NULL         |        1 |
| ios     | Phone  | NULL         |        1 |
| NULL    | Phone  | Beijing      |        1 |
| NULL    | Phone  | Shijiazhuang |        2 |
| NULL    | PC     | Beijing      |        3 |
| NULL    | PC     | Shijiazhuang |        1 |
| NULL    | NULL   | NULL         |        7 |
+---------+--------+--------------+----------+

GROUPING和GROUPING_ID函數

  • 功能介紹
    • GROUPING函數

      在GROUP BY子句使用GROUPING SETS、ROLLUP、或CUBE擴充時，GROUPING SETS結果中會使用NULL來充當預留位置，導致無法區分佔位符NULL與資料中真正的NULL。此時，您可以使用PolarDB-X提供的GROUPING函數來作區分。

      GROUPING函數接受一個列名作為參數，如果結果對應行使用了參數列做彙總，則結果返回0，此時意味著NULL來自輸入資料。如果結果對應行未使用參數列做彙總，則返回1，此時意味著NULL來自GROUPING SETS結果中的預留位置。

    • GROUPING_ID函數

      GROUPING_ID函數簡化了GROUPING函數，用於確定ROLLBACK、CUBE或GROUPING SETS擴充的結果集中行的小計層級。GROUPING函數僅採用一個列運算式並返回一個值來指示行是否為給定列的所有值的小計。因此，當解釋具有多個分組列的查詢的小計層級時，可能需要多個GROUPING函數。GROUPING_ID函數接受ROLLBACK、CUBE或GROUPINGSETS擴充中已使用的一個或多個列運算式，並返回單個整數，該整數可用於確定其中哪一列已彙總小計。

  • 文法
    • GROUPING函數
      SELECT [ expr ...,] GROUPING( col_expr ) [, expr ] ...
FROM ...
GROUP BY { ROLLUP | CUBE | 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 | CUBE | GROUPING SETS }( [...,] col_expr_1
  [, col_expr_2 ] [, ...] ) [, ...]
  • 示例
    通過GROUPING_ID函數將多個列名作為參數，並將參數列的GROUPING結果按照Bitmap的方式組成整數，文法如下：
    select a,b,c,count(*),
grouping(a) ga, grouping(b) gb, grouping(c) gc, grouping_id(a,b,c) groupingid 
from (select 1 as a ,2 as b,3 as c)
group by cube(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 |
+------+------+------+----------+------+------+------+------------+
上一篇：子查詢下一篇：EXPLAIN