在“分组”章节中,我们了解到关键字 GROUP BY 可以在结果集中根据某些列创建分组,同时使用聚合函数(如 SUM())为每个分组计算汇总值。

由于 GROUP BY 可以按多列分组,常常需要为更大的“超级分组”计算汇总值,这些超级分组是通过逐步省略 GROUP BY 中的列而得出的。


示例表

以下示例表和数据用于说明这种情况,并提出一些典型问题。

CREATE TABLE car_pool (
  id           DECIMAL      NOT NULL,
  producer     VARCHAR(50)  NOT NULL,
  model        VARCHAR(50)  NOT NULL,
  yyyy         DECIMAL      NOT NULL CHECK (yyyy BETWEEN 1970 AND 2020),
  counter      DECIMAL      NOT NULL CHECK (counter >= 0),
  CONSTRAINT   car_pool_pk PRIMARY KEY (id)
);

-- 插入示例数据
INSERT INTO car_pool VALUES ( 1, 'VW',     'Golf',    2005, 5);
INSERT INTO car_pool VALUES ( 2, 'VW',     'Golf',    2006, 2);
INSERT INTO car_pool VALUES ( 3, 'VW',     'Golf',    2007, 3);
INSERT INTO car_pool VALUES ( 4, 'VW',     'Golf',    2008, 3);
INSERT INTO car_pool VALUES ( 5, 'VW',     'Passat',  2005, 5);
INSERT INTO car_pool VALUES ( 6, 'VW',     'Passat',  2006, 1);
INSERT INTO car_pool VALUES ( 7, 'VW',     'Beetle',  2005, 1);
INSERT INTO car_pool VALUES ( 8, 'VW',     'Beetle',  2006, 2);
INSERT INTO car_pool VALUES ( 9, 'VW',     'Beetle',  2008, 4);
INSERT INTO car_pool VALUES (10, 'Toyota', 'Corolla', 2005, 4);
INSERT INTO car_pool VALUES (11, 'Toyota', 'Corolla', 2006, 3);
INSERT INTO car_pool VALUES (12, 'Toyota', 'Corolla', 2007, 2);
INSERT INTO car_pool VALUES (13, 'Toyota', 'Corolla', 2008, 4);
INSERT INTO car_pool VALUES (14, 'Toyota', 'Prius',   2005, 1);
INSERT INTO car_pool VALUES (15, 'Toyota', 'Prius',   2006, 1);
INSERT INTO car_pool VALUES (16, 'Toyota', 'Hilux',   2005, 1);
INSERT INTO car_pool VALUES (17, 'Toyota', 'Hilux',   2006, 1);
INSERT INTO car_pool VALUES (18, 'Toyota', 'Hilux',   2008, 1);

COMMIT;

问题示例:

  1. 每个生产商或每种车型的车辆总数。
  2. 按生产商和车型或生产商和年份的组合统计车辆总数。
  3. 所有车辆的总数(不带任何分组条件)。

ROLLUP

GROUP BY 可以为特定分组级别(如生产商和车型)提供汇总数据:

SELECT producer, model, SUM(counter) AS cnt
FROM   car_pool
GROUP BY producer, model
ORDER BY producer, cnt DESC;

结果:

Toyota	Corolla	13
Toyota	Hilux	3
Toyota	Prius	2
VW	    Golf	13
VW	    Beetle	7
VW	    Passat	6

如果需要同时查看每个生产商的总计和所有数据的总计,可以使用 ROLLUP

SELECT producer, model, SUM(counter) AS cnt
FROM   car_pool
GROUP BY ROLLUP (producer, model);

结果:

Toyota	Corolla	13
Toyota	Hilux	3
Toyota	Prius	2
Toyota		18   -- 每个生产商的总计
VW	    Beetle	7
VW	    Golf	13
VW	    Passat	6
VW		    26   -- 每个生产商的总计
		    44   -- 所有数据的总计

ROLLUP 会逐级省略分组条件,生成额外的汇总行:

  • 首先省略车型(model)。
  • 然后省略生产商(producer)。

CUBE

ROLLUP 的自上而下分层汇总不同,CUBE 生成所有可能分组组合的汇总行,适合数据仓库应用中的自由导航。

SELECT producer, model, SUM(counter) AS cnt
FROM   car_pool
GROUP BY CUBE (producer, model);

结果:

Toyota	Corolla	13
Toyota	Hilux	3
Toyota	Prius	2
Toyota		18
VW	    Beetle	7
VW	    Golf	13
VW	    Passat	6
VW		    26
		Corolla	13   -- 仅按车型汇总
		Hilux	3
		Passat	6
		Prius	2
		Beetle	7
		Golf	13
		44       -- 所有数据的总计

CUBE 提供了比 ROLLUP 更多的组合。例如,对于分组列 c1c2c3

  • GROUP BY(c1, c2, c3)
  • ROLLUP(c1, c2, c3), (c1, c2), (c1), ()
  • CUBE(c1, c2, c3), (c1, c2), (c1, c3), (c2, c3), (c1), (c2), (c3), ()

总结

  • ROLLUP:逐级汇总,适用于分层数据分析。
  • CUBE:生成所有可能的分组组合,适合自由数据导航。
  • GROUP BYROLLUP/CUBE 是聚合分析的重要工具。
Last modified: Tuesday, 28 January 2025, 2:15 PM