GROUPBY语法、注意事项和示例_云原生数据仓库AnalyticDB MySQL版(AnalyticDB for MySQL)-阿里云帮助中心

您可以使用 GROUP BY 子句根据指定的列对查询结果进行分析，也可以在 GROUP BY 字句中使用 GROUPING SETS 、 CUBE 或 ROLLUP ，以不同的形式展示分组结果。

GROUP BY expression [, ...]

注意事项

查询中需使用标准聚合函数（ SUM 、 AVG 或 COUNT ）声明非分组列，若未声明，则会使用 ARBITRARY 函数声明非分组列。
GROUP BY 中的列或表达式列表必须与查询列表中的非聚合表达式的列相同。

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 用于列出所有可能的分组集。

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, SUM(package_weight)
FROM shipping
GROUP BY ROLLUP (origin_state, origin_zip)

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

SELECT listid, eventid, SUM(pricepaid) as revenue,
count(qtysold) as numtix
FROM sales
GROUP BY listid, eventid
ORDER BY 3, 4, 2, 1
LIMIT 5;

listid | eventid | revenue | numtix
-------+---------+---------+--------
89397  |      47 |   20.00 |      1
106590 |      76 |   20.00 |      1
124683 |     393 |   20.00 |      1
103037 |     403 |   20.00 |      1
147685 |     429 |   20.00 |      1
(5 rows)

SELECT listid, eventid, SUM(pricepaid) as revenue,
count(qtysold) as numtix
FROM sales
GROUP BY 1,2
ORDER BY 3, 4, 2, 1
LIMIT 5;

listid | eventid | revenue | numtix
-------+---------+---------+--------
89397  |      47 |   20.00 |      1
106590 |      76 |   20.00 |      1
124683 |     393 |   20.00 |      1
103037 |     403 |   20.00 |      1
147685 |     429 |   20.00 |      1