PostgreSQL 教程: ROLLUP

九月 4, 2023

摘要:在本教程中,您将学习如何使用 PostgreSQL 的ROLLUP生成多个分组集。

目录

ROLLUP 简介

PostgreSQL 的ROLLUPGROUP BY子句的一个子句,它提供了定义多个分组集的简写。分组集是一组用于分组的列。查看分组集教程以获取详细信息。

CUBE子子句不同,ROLLUP不会根据指定的列生成所有可能的分组集。它只是其中的一个子集。

ROLLUP假设输入列之间存在层次结构,并生成考虑层次结构有意义的所有分组集。这就是为什么ROLLUP经常用于生成报告的小计和总计的原因。

例如,CUBE (c1,c2,c3)创建所有八个可能的分组集:

(c1, c2, c3)
(c1, c2)
(c2, c3)
(c1,c3)
(c1)
(c2)
(c3)
()

但是,假设层次结构c1 > c2 > c3ROLLUP(c1,c2,c3)仅生成四个分组集,如下:

(c1, c2, c3)
(c1, c2)
(c1)
()

ROLLUP的常见用途是根据层次结构year > month > date,按年、月和日期计算数据的聚合。

下面说明了 PostgreSQL ROLLUP的语法:

SELECT
    c1,
    c2,
    c3,
    aggregate(c4)
FROM
    table_name
GROUP BY
    ROLLUP (c1, c2, c3);

还可以进行部分汇总以减少生成的小计数量。

SELECT
    c1,
    c2,
    c3,
    aggregate(c4)
FROM
    table_name
GROUP BY
    c1, 
    ROLLUP (c2, c3);

PostgreSQL ROLLUP 示例

如果您还没有创建 sales 表,可以使用以下脚本:

DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
    brand VARCHAR NOT NULL,
    segment VARCHAR NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (brand, segment)
);

INSERT INTO sales (brand, segment, quantity)
VALUES
    ('ABC', 'Premium', 100),
    ('ABC', 'Basic', 200),
    ('XYZ', 'Premium', 100),
    ('XYZ', 'Basic', 300);

以下查询使用ROLLUP子句来查找按品牌(小计)以及所有品牌和细分市场(总计)销售的产品数量。

SELECT
    brand,
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    ROLLUP (brand, segment)
ORDER BY
    brand,
    segment;

PostgreSQL ROLLUP example

从输出中可以清楚地看到,第三行显示ABC品牌的销售额,第六行显示XYZ品牌的销售额。最后一行显示所有品牌和细分市场的总计。在此示例中,层次结构为brand > segment

如果更改品牌和细分市场的顺序,结果将有所不同,如下所示:

SELECT
    segment,
    brand,
    SUM (quantity)
FROM
    sales
GROUP BY
    ROLLUP (segment, brand)
ORDER BY
    segment,
    brand;

PostgreSQL ROLLUP example 2

在这种情况下,层次结构是segment > brand

以下语句执行部分汇总:

SELECT
    segment,
    brand,
    SUM (quantity)
FROM
    sales
GROUP BY
    segment,
    ROLLUP (brand)
ORDER BY
    segment,
    brand;

PostgreSQL ROLLUP - partial roll up

请参阅示例数据库中的rental表,如下。

Rental Table

以下语句使用ROLLUP查找每天、每月和每年的出租数量:

SELECT
    EXTRACT (YEAR FROM rental_date) y,
    EXTRACT (MONTH FROM rental_date) M,
    EXTRACT (DAY FROM rental_date) d,
    COUNT (rental_id)
FROM
    rental
GROUP BY
    ROLLUP (
        EXTRACT (YEAR FROM rental_date),
        EXTRACT (MONTH FROM rental_date),
        EXTRACT (DAY FROM rental_date)
    );

PostgreSQL ROLLUP example with year month and date

在本教程中,您学习了如何使用 PostgreSQL 的ROLLUP生成多个分组集。