如何用SQL进行数据库表的小计与总计计算？ROLLUP功能详解

在数据分析中，常常需要进行数据的汇总和聚合分析。

之前的文章中已经介绍了聚合函数、开窗函数、group by语句等方法来实现数据表的聚合分析。接下来，我们将使用MySQL和PostgreSQL这两个常见的关系型数据库，来演示如何在SQL中使用ROLLUP实现数据库表的小计和总计计算。

示例数据：

首先，我们创建一个简单的sales表，并插入一些数据。

-- 建表
CREATE TABLE sales (
year INT,
quarter INT,
amount DECIMAL(10, 2)
);
 
-- 插入数据
INSERT INTO sales VALUES
(2021, 1, 100),
(2021, 2, 150),
(2021, 3, 200),
(2021, 4, 250),
(2022, 1, 120),
(2022, 2, 180),
(2022, 3, 220),
(2022, 4, 270);
 
-- 数据表如下：
SELECT
  *
FROM
  Sales
;

数据表如下：

01 MySQL中的ROLL UP

以下查询按年份和季度对销售额进行汇总，并使用ROLLUP生成各级别的汇总。

SELECT
  year,
  quarter,
  SUM(amount) as total_amount
FROM
  Sales
GROUP BY
  year,
  quarter WITH ROLLUP
;

结果如下：

当使用ROLLUP时，任何被GROUP BY的列都可能在结果集中出现NULL值，这表示该列的汇总值。上面的示例中，2021年所有季度的汇总值为700，2022年所有季度的汇总值为790，所有年份的汇总值为1490。

若要将NULL换成小计或者总计，可以结合COALESCE函数，见如下SQL示例：

SELECT
  COALESCE( year,'总计') as year,
  COALESCE( quarter,'小计') as quarter,
  SUM(amount) as total_amount
FROM
  sales
GROUP BY
  year,
  quarter WITH ROLLUP
;

结果如下：

02 PostgreSQL中的ROLL UP

PostgreSQL也支持ROLLUP，但其语法略有不同。在PostgreSQL中，你需要在GROUP BY子句后使用ROLLUP()函数。

SELECT
  year,
  quarter,
  SUM(amount) as total_amount
FROM
  Sales
GROUP BY
  ROLLUP(year, quarter)
ORDER BY  year,quarter
;

结果如下，和MySQL的结果一致：

若要将NULL换成小计或者总计，可以结合COALESCE函数。这里要注意的是需要将year和quarter字段转换为字符串类型，SQL如下：

SELECT
  COALESCE(year::text,'总计') AS year,
  COALESCE(quarter::text,'小计') AS quarter,
  SUM(amount) as total_amount
FROM
  sales
GROUP BY
  ROLLUP(year, quarter)
ORDER BY
  year,quarter
;

若不转换为字符串类型，会报如下错误：

查询结果如下：

在实践中，根据实际需求和数据库类型，可以使用相应的语法来实现 ROLL UP 功能，并根据结果进行进一步的数据分析和报表制作。