Oracle 数据库提供了强大的分组功能，用于聚合多行数据并返回单个汇总行。本指南将深入探讨 GROUP BY 子句的各种扩展，包括 ROLLUP、CUBE、GROUPING 函数、GROUPING SETS 以及复合列和连接分组集，以实现更复杂的数据分析和报表生成。

聚合函数

聚合函数（如 AVG, SUM, COUNT, MAX, MIN, STDDEV）用于处理多行数据，并返回单行结果。它们通常与 GROUP BY 子句结合使用，对数据进行分组聚合。

基本 GROUP BY

GROUP BY 子句用于根据一个或多个列的值对结果集进行分组。聚合函数将应用于每个组。

SELECT
    department_id,
    job_id,
    SUM(salary) AS total_salary,
    COUNT(employee_id) AS employee_count
FROM
    employees
GROUP BY
    department_id,
    job_id
ORDER BY
    department_id,
    job_id;
    

HAVING 子句

HAVING 子句用于过滤由 GROUP BY 子句产生的组。它与 WHERE 子句类似，但应用于分组后的结果，通常用于基于聚合函数的结果进行筛选。

SELECT
    department_id,
    job_id,
    SUM(salary) AS total_salary,
    COUNT(employee_id) AS employee_count
FROM
    hr.employees
GROUP BY
    department_id,
    job_id
HAVING
    SUM(salary) > 50000;
    

ROLLUP 操作符

ROLLUP 是 GROUP BY 的一个扩展，它生成多级汇总。对于 n 个分组键，ROLLUP 会生成 n+1 个分组集，包括所有可能的子集汇总，从最细粒度到最粗粒度。

-- 不使用 ROLLUP
SELECT
    department_id,
    job_id,
    SUM(salary) AS total_salary
FROM
    hr.employees
WHERE
    department_id < 60
GROUP BY
    department_id,
    job_id
ORDER BY
    department_id,
    job_id;

-- 使用 ROLLUP
SELECT
    department_id,
    job_id,
    SUM(salary) AS total_salary
FROM
    employees
GROUP BY
    ROLLUP(department_id, job_id);
    

ROLLUP(a, b) 会生成以下分组：(a, b), (a), ()。注意 ROLLUP 的顺序很重要，它决定了汇总的层级。

CUBE 操作符

CUBE 是 GROUP BY 的另一个扩展，它为所有指定的分组键的组合生成汇总行。对于 n 个分组键，CUBE 会生成 2^n 个分组集，提供更全面的交叉汇总。

SELECT
    department_id,
    job_id,
    SUM(salary) AS total_salary
FROM
    hr.employees
WHERE
    department_id < 60
GROUP BY
    CUBE(department_id, job_id);
    

CUBE(a, b) 会生成以下分组：(a, b), (a), (b), ()。

GROUPING 函数

GROUPING 函数与 ROLLUP 或 CUBE 结合使用，用于区分由分组键本身产生的 NULL 值（表示该行是汇总行）与数据中实际存在的 NULL 值。GROUPING() 函数返回 1 表示该列是汇总列（即 NULL 是由于汇总产生的），返回 0 表示该列是实际分组列（NULL 是数据本身的值或该组中所有行的值）。

SELECT
    department_id AS DEPTID,
    job_id AS JOB,
    SUM(salary) AS total_salary,
    GROUPING(department_id) AS GRP_DEPT,
    GROUPING(job_id) AS GRP_JOB
FROM
    hr.employees
WHERE
    department_id < 50
GROUP BY
    ROLLUP(department_id, job_id);

SELECT
    department_id AS DEPTID,
    job_id AS JOB,
    SUM(salary) AS total_salary,
    GROUPING(department_id) AS GRP_DEPT,
    GROUPING(job_id) AS GRP_JOB
FROM
    hr.employees
WHERE
    department_id < 50
GROUP BY
    CUBE(department_id, job_id);
    

GROUPING SETS

GROUPING SETS 允许您在单个查询中定义多个分组集。它通过将各个分组集的结果使用 UNION ALL 操作结合起来，从而避免了编写复杂的 UNION ALL 语句，并可能提高性能。

SELECT
    department_id,
    job_id,
    manager_id,
    AVG(salary) AS average_salary
FROM
    hr.employees
GROUP BY
    GROUPING SETS (
        (department_id, job_id),
        (job_id, manager_id)
    );
    

复合列

可以使用括号将多个列组合成一个"复合列"，以便在 ROLLUP 或 CUBE 操作中将它们作为一个整体进行处理。这有助于避免不必要的中间汇总。

SELECT
    department_id,
    job_id,
    manager_id,
    SUM(salary) AS total_salary
FROM
    hr.employees
GROUP BY
    ROLLUP(department_id, (job_id, manager_id));
    

在这个例子中，(job_id, manager_id) 被视为一个单一的复合分组键。

连接分组集

可以将多个分组集、ROLLUP、CUBE 或其他分组选项通过逗号连接在 GROUP BY 子句中。Oracle 会为这些组合生成笛卡尔积式的分组结果，提供非常灵活的聚合能力。

SELECT
    department_id,
    job_id,
    manager_id,
    SUM(salary) AS total_salary
FROM
    hr.employees
GROUP BY
    department_id,
    ROLLUP(job_id),
    CUBE(manager_id)
ORDER BY
    department_id,
    job_id,
    manager_id;
    

这种方式会结合 department_id 的独立分组、job_id 的 ROLLUP 分组以及 manager_id 的 CUBE 分组，产生所有可能的组合。