多维分析函数： 1. GROUPING_SETS 用法 2. cube的用法 . 3 rollup

三者语法：

1. GROUP BY GROUPING SETS ( (product_name, region),（）） ;

2. GROUP BY year, region WITH CUBE;

3.GROUP BY ROLLUP(year, region, product);

1. GROUPING SETS 多维分析

案例1 基本案例

在Hive中，`GROUPING SETS` 是一个用于生成多个分组聚合的SQL功能，它可以让你在一个查询中指定多个分组集，这样可以有效地生成多维度的汇总数据。下面我将通过一个例子来展示如何使用 `GROUPING SETS`，并创建一个Hive表以及插入十条数据进行演示。

### 步骤 1: 创建Hive表

首先，我们创建一个简单的销售数据表，包含商品名称、销售地区和销售额：

sql
CREATE TABLE sales_data (
product_name STRING,
region STRING,
sales_amount INT
);

### 步骤 2: 插入数据

接下来，向表中插入一些示例数据：

sql
INSERT INTO sales_data VALUES
('Product A', 'North', 100),
('Product A', 'South', 150),
('Product A', 'East', 200),
('Product A', 'West', 250),
('Product B', 'North', 200),
('Product B', 'South', 300),
('Product B', 'East', 400),
('Product B', 'West', 500),
('Product C', 'North', 150),
('Product C', 'South', 250);

### 步骤 3: 使用 GROUPING SETS

现在，我们使用 `GROUPING SETS` 来查询不同维度的销售总额。我们将计算每个产品的总销售额、每个地区的总销售额以及全体总销售额：

SELECT product_name, region, SUM(sales_amount) AS total_sales
FROM sales_data
GROUP BY GROUPING SETS (
    (product_name, region),  -- 每个产品在每个地区的销售额
    (product_name),          -- 每个产品的总销售额
    (region),                -- 每个地区的总销售额
    ()                       -- 全体总销售额
) order by  product_name ,region
;



product_name            region    total_sales
NULL                    NULL    2500
NULL                    East    600
NULL                    North    450
NULL                    South    700
NULL                    West    750
Product A                NULL    700
Product A                East    200
Product A                North    100
Product A                South    150
Product A                West    250
Product B                NULL    1400
Product B                East    400
Product B                North    200
Product B                South    300
Product B                West    500
Product C                NULL    400
Product C                North    150
Product C                South    250

案例2 ， GROUPING(product) AS product_flag 作为flag字段举例

SELECT
    region,
    product,
    SUM(sales_amount) AS total_sales,
    GROUPING(region) AS region_flag,
    GROUPING(product) AS product_flag
FROM
    sales
GROUP BY
    GROUPING SETS (
        (region),
        (product),
        (region, product)
    )
ORDER BY
    region_flag, product_flag;

region_flag 和 product_flag 用于标识当前行是否为某个分组的总和（0表示当前分组存在，1表示当前分组不存在）。
结果将显示不同分组方式的销售总额：

region   | product   | total_sales | region_flag | product_flag
--------------------------------------------------------------
North    | NULL      | 3500        | 0           | 1
South    | NULL      | 3000        | 0           | 1
NULL     | Laptop    | 2700        | 1           | 0
NULL     | Smartphone| 3800        | 1           | 0
North    | Laptop    | 1500        | 0           | 0
North    | Smartphone| 2000        | 0           | 0
South    | Laptop    | 1200        | 0           | 0
South    | Smartphone| 1800        | 0           | 0

2. CUBE 多维分析函数

在 Hive 中使用 `CUBE` 函数进行多维聚合分析时，首先需要创建一个表并插入一些示例数据。下面我将提供一个完整的示例，包括创建表、插入数据以及使用 `CUBE` 进行查询的过程。

### 步骤 1: 创建 Hive 表
假设我们有一个简单的销售记录表，包含销售年份、地区和销售额。

```sql
CREATE TABLE SalesData (
year INT,
region STRING,
sales_amount INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
```

### 步骤 2: 插入数据
接下来，我们将插入一些示例数据到 `SalesData` 表中。

```sql
INSERT INTO TABLE SalesData VALUES
(2020, 'North', 1200),
(2020, 'South', 1500),
(2020, 'East', 1800),
(2020, 'West', 1100),
(2021, 'North', 1300),
(2021, 'South', 1600),
(2021, 'East', 1700),
(2021, 'West', 1400),
(2022, 'North', 1500),
(2022, 'South', 1400);
```

### 步骤 3: 使用 `CUBE` 进行查询
现在，我们将使用 `CUBE` 来分析不同年份和地区的销售总额，以及所有可能的聚合组合。

```sql
SELECT year, region, SUM(sales_amount) AS total_sales
FROM SalesData
GROUP BY year, region WITH CUBE;
```

### 解释
- **查询**：这个查询使用 `CUBE` 对 `year` 和 `region` 进行分组，生成所有可能的聚合组合。
- **结果**：结果将包括：
- 每个年份和地区组合的销售总额。
- 每个年份的所有地区的销售总额（当 `region` 为 `NULL` 时）。
- 每个地区的所有年份的销售总额（当 `year` 为 `NULL` 时）。
- 所有年份和地区的总销售额（当 `year` 和 `region` 都为 `NULL` 时）。

### 注意事项
- 在使用 `CUBE` 时，生成的结果集可能会非常大，尤其是当分组列较多时，因为它包括了所有可能的聚合组合。
- 在实际应用中，根据数据的具体情况和分析需求，可能需要对 `CUBE` 生成的数据进行进一步的筛选或处理。

通过上述步骤，你可以在 Hive 中有效地使用 `CUBE` 函数来进行复杂的多维数据分析，这对于理解数据的不同维度之间的关系非常有帮助。

查询结果如下：

spark-sql> SELECT year, region, SUM(sales_amount) AS total_sales
         > FROM SalesData
         > GROUP BY year, region WITH CUBE
         > ORDER BY   SUM(sales_amount);
year    region    total_sales
2020    West    1100
2020    North    1200
2021    North    1300
2022    South    1400
2021    West    1400
2020    South    1500
2022    North    1500
2021    South    1600
2021    East    1700
2020    East    1800
NULL    West    2500
2022    NULL    2900
NULL    East    3500
NULL    North    4000
NULL    South    4500
2020    NULL    5600
2021    NULL    6000
NULL    NULL    14500
Time taken: 0.3 seconds, Fetched 18 row(s)



====
如下是原表结果

spark-sql> select * from SalesData order by  sales_amount  ;
year    region    sales_amount
2020    West    1100
2020    North    1200
2021    North    1300
2021    West    1400
2022    South    1400
2020    South    1500
2022    North    1500
2021    South    1600
2021    East    1700
2020    East    1800
Time taken: 0.155 seconds, Fetched 10 row(s)

3.两者的区别

使用 CUBE 时，如果你有 n 个列，它会生成 (2^n) 个结果组合，包括所有单列、所有列对、所有三列组合，以及所有列的完全组合。

CUBE 提供了快速生成所有组合的能力，适合于当你需要完整的数据立方体时使用。
GROUPING SETS 提供了更高的灵活性，允许你精确控制哪些组合被生成，适合于只需要特定聚合组合的情况。

CUBE 函数原表有多少个字段，就有有 2^n 种组合，而grouping sets 他是个性化的指定

如下两个sql等价：

    SELECT year, region, SUM(sales_amount) AS total_sales
    FROM SalesData
    GROUP BY year, region WITH CUBE
    ORDER BY   SUM(sales_amount);


============

 SELECT year, region, SUM(sales_amount) AS total_sales
 FROM SalesData
 GROUP BY grouping sets( (year),(year,region) ,(region) ())
 ORDER BY   SUM(sales_amount);

4.ROLLUP 多维分析函数

总结：比如有 A B C 三个维度他这样汇总

汇总 ABC

汇总 AB

汇总A

总体汇总

### ROLLUP 函数简介
`ROLLUP` 是 SQL 中的一个分析函数，用于创建数据的层次化汇总，这通常用于生成报表或进行多层次的数据分析。`ROLLUP` 生成的结果包括从最具体到最抽象的聚合级别，使得它非常适合于需要执行分级汇总的场景。

### 功能
`ROLLUP` 通过添加额外的汇总行来扩展 `GROUP BY` 语句的功能，这些汇总行代表了数据的层次化聚合。例如，如果你对 `year`, `region` 和 `product` 使用 `ROLLUP`，你将得到：
- 每个 `year`, `region`, `product` 的组合的聚合
- 每个 `year`, `region` 的组合的聚合（忽略 `product`）
- 每个 `year` 的聚合（忽略 `region` 和 `product`）
- 总体聚合（忽略 `year`, `region`, 和 `product`）

### 示例
假设我们有一个名为 `Sales` 的表，包含以下列：
- `year` (年份)
- `region` (地区)
- `product` (产品)
- `amount` (销售额)

我们想要使用 `ROLLUP` 来分析销售数据，以下是一个使用 `ROLLUP` 的 Hive SQL 查询示例：

```sql
SELECT year, region, product, SUM(amount) AS total_sales
FROM Sales
GROUP BY ROLLUP(year, region, product);

5.他们之间的区别汇总

Hive 是一个建立在 Hadoop 之上的数据仓库工具，它提供了丰富的 SQL 功能，包括多维分析函数。这些函数通常用于执行复杂的数据聚合和分析，特别是在处理大数据环境中的数据仓库查询时。以下是 Hive 中常用的几种多维分析函数：

### 1. **CUBE**
- `CUBE` 用于生成指定列的所有可能的聚合组合。
- 它适用于需要全面分析数据的所有维度组合的场景。
- 例如：`GROUP BY CUBE (column1, column2)` 会生成包括单独列、两列组合以及所有列的完全组合的聚合结果。

### 2. **ROLLUP**
- `ROLLUP` 生成一个层次化的数据聚合，从最具体到最抽象的层次。
- 它适用于需要按层次进行数据汇总的场景，如逐步汇总到更高的维度。
- 例如：`GROUP BY ROLLUP (column1, column2)` 会生成从具体到抽象的聚合结果，如单独的 `column1`，`column1` 和 `column2` 的组合，以及所有数据的总汇总。

### 3. **GROUPING SETS**
- `GROUPING SETS` 允许你指定一个或多个分组集，这些集合中的每一个都是一个独立的 `GROUP BY` 操作。
- 它提供了比 `CUBE` 和 `ROLLUP` 更高的灵活性，允许精确控制哪些组合需要进行聚合。
- 例如：`GROUP BY GROUPING SETS ((column1, column2), (column2))` 会生成指定的两个聚合组合。

### 4. **GROUPING**
- `GROUPING` 函数用于识别当前行是否为超级聚合行（即汇总行），在使用 `ROLLUP`、`CUBE` 或 `GROUPING SETS` 时特别有用。
- 它返回 1 如果当前行是由于聚合而生成的超级聚合行，否则返回 0。
- 例如：在使用 `ROLLUP` 或 `CUBE` 时，可以用 `GROUPING(column)` 来检查某列是否在当前聚合级别中被省略。

### 5. **GROUPING_ID**
- `GROUPING_ID` 函数用于返回一个数字，这个数字表示当前行在 `GROUP BY` 语句中使用的分组列。
- 它在使用 `CUBE` 或 `ROLLUP` 时特别有用，因为它可以帮助识别当前行是由哪些列的组合产生的聚合结果。
- 例如：`GROUPING_ID(column1, column2)` 会为每个聚合结果返回一个唯一的数字，表示哪些列参与了聚合。

这些多维分析函数在数据分析和报告中非常有用，特别是在需要对数据进行多角度和多层次分析的场景中。使用这些函数可以帮助你更有效地理解和展示数据的多维关系。

posted @ 2024-09-06 09:28 萌哥-爱学习阅读(214) 评论(0) 编辑收藏举报

刷新页面返回顶部

萌哥

爱学习