Oracle 小计-汇总处理

假设我们有一个名为employees的表，它包含部门（department）、员工姓名（employee）和工资（salary）

CREATE TABLE employees (
    department VARCHAR2(50),
    employee VARCHAR2(50),
    salary NUMBER(10, 2)
);

初始化数据

INSERT INTO employees (department, employee, salary) VALUES ('Sales', 'John', 50000);
INSERT INTO employees (department, employee, salary) VALUES ('Sales', 'Jane', 70000);
INSERT INTO employees (department, employee, salary) VALUES ('Marketing', 'Lisa', 60000);
INSERT INTO employees (department, employee, salary) VALUES ('Marketing', 'Bob', 40000);

ROLLUP

• 使用 ROLLUP 的结果集

ROLLUP为每个GROUP BY列表中的列生成总计，并在结果集的最后添加一个所有行的总计。它按照GROUP BY列表中列的顺序生成汇总。

SELECT department, employee, SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(department, employee);

department	employee	total_salary
Sales	John	50000.00
Sales	Jane	70000.00
Sales	NULL	120000.00 (Sales部门的总和)
Marketing	Lisa	60000.00
Marketing	Bob	40000.00
Marketing	NULL	100000.00 (Marketing部门的总和)
NULL	NULL	220000.00 (所有部门的总和)

ROLLUP 结合 GROUPING

SELECT 
  CASE 
    WHEN GROUPING(department) = 1 THEN '总计' -- 如果部门字段是汇总，则显示'总计'
    ELSE department -- 否则显示实际的部门名称
  END AS department,
  CASE 
    WHEN GROUPING(employee) = 1 THEN '分计' -- 如果员工字段是汇总，则显示'分计'
    ELSE employee -- 否则显示实际的员工名称
  END AS employee,
  SUM(salary) AS total_salary
FROM 
  employees
GROUP BY 
  ROLLUP(department, employee); -- 先按员工分组，然后按部门分组，最后生成总体汇总

department	employee	total_salary
Sales	John	50000
Sales	Jane	70000
Sales	分计	120000 (Sales部门的总和)
Marketing	Lisa	60000
Marketing	Bob	40000
Marketing	分计	100000 (Marketing部门的总和)
总计	分计	220000 (所有部门的总和)

GROUPING(department)和GROUPING(employee)用于确定每一列是否为汇总级别。如果是汇总级别，则CASE语句将显示“总计”或“分计”。

最后一行是所有部门和员工的总体汇总，其中department和employee都显示为“总计”和“分计”

CUBE

• 使用 CUBE 的结果集

CUBE为GROUP BY列表中的每一列的所有可能组合生成一个总计。这意味着它生成的汇总级别比ROLLUP多，因为它包括了所有列的组合。

SELECT department, employee, SUM(salary) AS total_salary
FROM employees
GROUP BY CUBE(department, employee);

department	employee	total_salary
Sales	John	50000.00
Sales	Jane	70000.00
Sales	NULL	120000.00 (Sales部门的总和)
Marketing	Lisa	60000.00
Marketing	Bob	40000.00
Marketing	NULL	100000.00 (Marketing部门的总和)
NULL	John	50000.00
NULL	Jane	70000.00
NULL	Lisa	60000.00
NULL	Bob	40000.00
NULL	NULL	220000.00 (所有部门和员工的总和)

GROUPING SETS

• 使用 GROUPING SETS 的结果集
  (department)：仅按部门分组，不包括员工。
  (employee)：仅按员工分组，不包括部门。
  ()：空的分组集合，表示所有行的总体汇总。

SELECT department, employee, SUM(salary) AS total_salary
FROM employees
GROUP BY GROUPING SETS (department, employee, ());

department	employee	total_salary
Sales	NULL	120000.00 (Sales部门的总和)
Marketing	NULL	100000.00 (Marketing部门的总和)
NULL	NULL	220000.00 (所有部门的总和)

• 结果集解释：

部门汇总：第一行显示了Sales部门的汇总，employee列为NULL，因为这一汇总是基于部门而不是员工。total_salary为120000.00，这是Sales部门所有员工的工资总和。

另一个部门汇总：第二行显示了Marketing部门的汇总，同样employee列为NULL。total_salary为100000.00，这是Marketing部门所有员工的工资总和。

总体汇总：最后一行是所有部门和员工的总体汇总，department和employee列都为NULL。total_salary为220000.00，这是表中所有员工工资的总和。

• 为什么这样分组？

GROUPING SETS提供了一种灵活的方式来生成多个不同级别的汇总，而不需要编写多个查询。

使用空的分组集合()是为了包括整个数据集的总体汇总

• 注意事项：

结果集中的每一行都代表了一个不同的汇总级别，这有助于理解数据在不同维度上的汇总情况。