南大通用GBase 8s 高级分组查询 —— GROUP BY ROLLUP介绍

转载

南大通用GBase 2024-08-27 14:37:37

数据分析和商业智能领域对数据进行快速而准确的汇总是至关重要的。南大通用GBase 8s数据库通过其高级分组查询功能——GROUP BY ROLLUP支持多维度的数据汇总和分析。本文将详细介绍GROUP BY ROLLUP的基本概念、语法结构、应用示例以及实际使用场景，帮助您深入理解并有效利用这一功能。

一、GROUP BY ROLLUP的基本功能

GROUP BY ROLLUP是GBase 8s数据库中的一个高级分组查询功能，它可以根据指定的列进行分组统计，并支持不同级别的汇总计算。

基本语法：

SELECT 列1, 列2, ...

FROM 表名

GROUP BY ROLLUP(列1, 列2, ...);

使用时，系统首先按照指定的多列进行分组，然后将多组结果集进行 UNION ALL 。

分组个数：列个数 n + 1。

分组方式：先按照全部指定列分组，然后从右向左依次减少一列进行分组，直到最后不按照任何列分组，不参与分组的列对应结果集内容为NULL ，返回以上分组结果。

假如 ROLLUP 分组列为 (A, B, C) ，首先对 (A,B,C) 进行分组，然后对 (A,B) 进行分组，接着对 (A) 进行分组，无分组列进行分组，查询结果是把每种分组的结果集进行 UNION ALL 合并输出。

汇总计算：

在每个分组级别上，可以使用聚合函数（如SUM、AVG、MAX等）来计算汇总值。

例如，使用SUM函数可以计算每个分组的总和。

结果集：

GROUP BY ROLLUP返回的结果集包含了所有分组统计数据的并集，并且不会去除重复的数据。这意味着每个分组的统计数据都会被单独列出来。

辅助函数：

GROUPING()函数：用于判断某个特定的分组是否为统计行，如果为统计行则返回1，否则返回0。

GROUPING_ID()函数：用于标识分组的级别，它接受一列或多列作为参数，并返回一个二进制数值，表示这些列的分组级别。

二、示例分析

假设有一个销售数据表sales，包含字段year（年份）、region（地区）和sales（销售额）。

注：示例数据库版本为：GBase8sV8.8_3.5.1

--创建 sales 表
>  CREATE TABLE sales ( 
    id INT PRIMARY KEY,   
    year INT,   
    region VARCHAR(50), 
sales DECIMAL(10, 2) ); 
--插入数据
INSERT INTO sales (id,year, region, sales) VALUES (1,2020, '华北', 10000.00); 
INSERT INTO sales (id,year, region, sales) VALUES (2,2020, '华南', 15000.00); 
INSERT INTO sales (id,year, region, sales) VALUES (3,2021, '华北', 12000.00); 
INSERT INTO sales (id,year, region, sales) VALUES (4,2021, '华南', 18000.00); 
INSERT INTO sales (id,year, region, sales) VALUES (5,2021, '华东', 20000.00);
--以下是一个使用GROUP BY ROLLUP的示例查询：
>  SELECT year, region, SUM(sales) AS total_sales
FROM sales
GROUP BY ROLLUP(year, region);

YEAR         2021
REGION       华南
TOTAL_SALES  18000.00

YEAR         2020
REGION       华南
TOTAL_SALES  15000.00

YEAR         2021
REGION       华北
TOTAL_SALES  12000.00

YEAR         2021
REGION       华东
TOTAL_SALES  20000.00

YEAR         2020
REGION       华北
TOTAL_SALES  10000.00

YEAR         2020
REGION
TOTAL_SALES  25000.00

YEAR         2021
REGION
TOTAL_SALES  50000.00

YEAR
REGION
TOTAL_SALES  75000.00

8 row(s) retrieved.

这个查询将返回以下结果：

按年份和地区分组的销售额。
按年份分组的销售额总计（地区为NULL）。
所有年份和地区的总销售额总计（年份和地区均为NULL）。

--使用GROUPING_ID()函数
> SELECT
    year,
    region,
    SUM(sales) AS total_sales,
        decode( GROUPING_ID(year, region),1,'按年份销售额总计',0,'按年份区域销售额小计',3,'全局销售额总计')
FROM
    sales
GROUP BY
    ROLLUP(year, region);



YEAR          2021
REGION        华南
TOTAL_SALES   18000.00
(EXPRESSION)  按年份区域销售额小计

YEAR          2020
REGION        华南
TOTAL_SALES   15000.00
(EXPRESSION)  按年份区域销售额小计

YEAR          2021
REGION        华北
TOTAL_SALES   12000.00
(EXPRESSION)  按年份区域销售额小计

YEAR          2021
REGION        华东
TOTAL_SALES   20000.00
(EXPRESSION)  按年份区域销售额小计

YEAR          2020
REGION        华北
TOTAL_SALES   10000.00
(EXPRESSION)  按年份区域销售额小计

YEAR          2020
REGION
TOTAL_SALES   25000.00
(EXPRESSION)  按年份销售额总计

YEAR          2021
REGION
TOTAL_SALES   50000.00
(EXPRESSION)  按年份销售额总计

YEAR
REGION
TOTAL_SALES   75000.00
(EXPRESSION)  全局销售额总计

8 row(s) retrieved.