GROUP BY文法與樣本 - AnalyticDB

GROUP BY用於對查詢結果進行分組彙總。您也可以在GROUP BY子句中使用GROUPING SETS、WITH CUBE或ROLLUP，以不同的形式展示分組結果。

GROUP BY expression [, ...]

注意事項

查詢中須使用標準彙總函式（SUM、AVG或COUNT等）聲明非分組列，否則系統將預設使用ARBITRARY函數處理非分組列，隨機返回非分組列中的任意一個值。
不支援啟用ONLY_FULL_GROUP_BY模式。

GROUPING SETS

當您需要對同一資料集進行多個維度彙總統計，並將這些結果合并到一個結果集中，可以使用GROUPING SETS。GROUPING SETS用於在同一結果集中指定多個GROUP BY選項，作用相當於多個GROUP BY查詢的UNION組合形式。

SELECT origin_state, origin_zip, destination_state, sum(package_weight)
FROM shipping
GROUP BY GROUPING SETS (
    (origin_state),
    (origin_state, origin_zip),
    (destination_state));

上述樣本等同於：

SELECT origin_state, NULL, NULL, sum(package_weight)
FROM shipping GROUP BY origin_state
UNION ALL
SELECT origin_state, origin_zip, NULL, sum(package_weight)
FROM shipping GROUP BY origin_state, origin_zip
UNION ALL
SELECT NULL, NULL, destination_state, sum(package_weight)
FROM shipping GROUP BY destination_state;

CUBE

CUBE作用類似於GROUPING SETS，但它會自動產生所有可能的分組組合，不需要手動列出分組。

SELECT origin_state, destination_state, sum(package_weight)
FROM shipping
GROUP BY origin_state, destination_state WITH CUBE;

上述樣本等同於：

SELECT origin_state, destination_state, sum(package_weight)
FROM shipping
GROUP BY GROUPING SETS (
    (origin_state, destination_state),
    (origin_state),
    (destination_state),
    ());

ROLLUP

ROLLUP 會對每個分組列產生層次化的分組組合。它會按照指定的列順序，逐步減少分組的維度，直到最終產生全域匯總。

SELECT origin_state, origin_zip, destination_state, SUM(package_weight)
FROM shipping
GROUP BY ROLLUP (origin_state, origin_zip, destination_state);

上述樣本等同於：

SELECT origin_state, origin_zip, destination_state, SUM(package_weight)
FROM shipping
GROUP BY GROUPING SETS (
    (origin_state, origin_zip, destination_state),
    (origin_state, origin_zip), 
    (origin_state), 
    ()
    );

注意事項

AnalyticDB for MySQL不支援GROUP BY ROLLUP (...)後加列名，即以下SQL語句會報錯。

SELECT origin_state, origin_zip, destination_state, SUM(package_weight)
FROM shipping
GROUP BY ROLLUP (origin_state, origin_zip), destination_state;

如果您想以層級方式實現GROUP BY查詢的UNION組合，可通過GROUP BY GROUPING SETS ((origin_state, origin_zip), (origin_state), ()), destination_state實現。

樣本

以sales表為例，示範GROUP BY、GROUPING SETS、WITH CUBE、ROLLUP的效果。

sales表的建表語句與寫入語句如下。

CREATE TABLE sales (
    year INT,
    region VARCHAR(50),
    amount DECIMAL(10, 2)
);

INSERT INTO sales (year, region, amount) VALUES
(2020, 'North', 1000.00),
(2020, 'South', 1500.00),
(2020, 'East', 1200.00),
(2020, 'West', 1300.00),
(2021, 'North', 2000.00),
(2021, 'South', 2500.00),
(2021, 'East', 2200.00),
(2021, 'West', 2300.00),
(2022, 'North', 3000.00),
(2022, 'South', 3500.00),
(2022, 'East', 3200.00),
(2022, 'West', 3300.00);

樣本1：GROUP BY查詢

以下查詢聲明year和region為分組列，計算amount列的分組總和。

SELECT year, region, SUM(amount) as sum_amount
FROM sales
GROUP BY year, region
ORDER BY 3, 2, 1 --按第3列sum_amount，第2列region，第1列year排序。
LIMIT 5;

返回結果如下：

year | region | sum_amount 
-----+--------+------------
2020 |  North |   1000.00  
2020 |  East  |   1200.00  
2020 |  West  |   1300.00  
2020 |  South |   1500.00  
2021 |  North |   2000.00  
(5 rows)

GROUP BY子句中的運算式也可以使用序號來引用所需的列。上述樣本可改寫為以下形式。

SELECT year, region, SUM(amount) as sum_amount
FROM sales
GROUP BY 1,2  --按year,region分組
ORDER BY 3, 2, 1
LIMIT 5;

返回結果如下：

year | region | sum_amount 
-----+--------+------------
2020 |  North |   1000.00  
2020 |  East  |   1200.00  
2020 |  West  |   1300.00  
2020 |  South |   1500.00  
2021 |  North |   2000.00  
(5 rows)

樣本2：使用GROUPING SETS，將按year和region分組匯總的結果，按year分組匯總的結果，按region分組匯總的結果，以及全域匯總的結果合并到一個結果集。

SELECT year, region, SUM(amount) as sum_amount
FROM sales
GROUP BY GROUPING SETS (
    (year,region), --按year和region分組匯總。
    (region), --按year分組匯總。
    (year), --按region分組匯總
    () --全域匯總
    );

返回結果如下：

year | region | sum_amount 
-----+--------+------------
2020 |  North |   1000.00  
2020 |  East  |   1200.00  
2020 |  West  |   1300.00  
2020 |  South |   1500.00  
2021 |  North |   2000.00
2021 |  East  |   2200.00  
2021 |  West  |   2300.00  
2021 |  South |   2500.00  
2022 |  North |   3000.00  
2022 |  East  |   3200.00  
2022 |  West  |   3300.00  
2022 |  South |   3500.00
NULL |  North |   6000.00  
NULL |  East  |   6600.00  
NULL |  West  |   6900.00  
NULL |  South |   7500.00
2020 |  NULL  |   5000.00  
2021 |  NULL  |   9000.00
2022 |  NULL  |  13000.00
NULL |  NULL  |  27000.00
(20 rows)

樣本3：使用GROUP BY ... WITH CUBE，自動產生所有可能的分組組合，實現和樣本2 GROUPING SETS相同的效果。

SELECT year, region, SUM(amount) as sum_amount
FROM sales
GROUP BY year,region WITH CUBE;

返回效果如下：

year | region | sum_amount 
-----+--------+------------
2020 |  North |   1000.00  
2020 |  East  |   1200.00  
2020 |  West  |   1300.00  
2020 |  South |   1500.00  
2021 |  North |   2000.00
2021 |  East  |   2200.00  
2021 |  West  |   2300.00  
2021 |  South |   2500.00  
2022 |  North |   3000.00  
2022 |  East  |   3200.00  
2022 |  West  |   3300.00  
2022 |  South |   3500.00
NULL |  North |   6000.00  
NULL |  East  |   6600.00  
NULL |  West  |   6900.00  
NULL |  South |   7500.00
2020 |  NULL  |   5000.00  
2021 |  NULL  |   9000.00
2022 |  NULL  |  13000.00
NULL |  NULL  |  27000.00
(20 rows)

樣本3：使用GROUP BY ROLLUP(...)，按指定列的順序，先產生最細粒度的匯總結果（按year和region彙總），再逐步減少分組維度（按year彙總），最終產生全域匯總結果。

SELECT year, region, SUM(amount) as sum_amount
FROM sales
GROUP BY ROLLUP(year,region);

返回結果如下：

year | region | sum_amount 
-----+--------+------------
2020 |  North |   1000.00  
2020 |  East  |   1200.00  
2020 |  West  |   1300.00  
2020 |  South |   1500.00  
2021 |  North |   2000.00
2021 |  East  |   2200.00  
2021 |  West  |   2300.00  
2021 |  South |   2500.00  
2022 |  North |   3000.00  
2022 |  East  |   3200.00  
2022 |  West  |   3300.00  
2022 |  South |   3500.00
2020 |  NULL  |   5000.00  
2021 |  NULL  |   9000.00
2022 |  NULL  |  13000.00
NULL |  NULL  |  27000.00
(16 rows)