这是本节的多页打印视图。 点击此处打印.

返回本页常规视图.

GROUP BY 子句

在 GROUP BY 中将 SELECT 子句与聚合函数一起使用可以收集多个记录中的数据。Vertica 将结果分组成为一个或多个与表达式匹配的行集。

不包含聚合的 GROUP BY 子句的使用方式类似于 SELECT DISTINCT。

ROLLUP 是对 GROUP BY 子句的扩展。 ROLLUP 执行小计聚合。

语法

GROUP BY [/*+GBYTYPE(algorithm)*/] { expression | aggregate‑expression }[,...]

参数

/*+GBYTYPE(algorithm)*/
指定实现 GROUP BY 子句时优先级高于 Vertica 查询优化器可能选择的算法的算法。可以将 algorithm 设置为以下值之一：

  • HASH：GROUPBY HASH 算法

  • PIPE：GROUPBY PIPELINED 算法

有关两种算法的详细信息，请参阅 GROUP BY 实施选项。

表达式
任何表达式，包括 FROM 子句中指定的表中的常数和列引用。例如： 
column,... column, (expression)
aggregate‑expression
列、表达式、CUBE, GROUPING SETS 或 ROLLUP 聚合按顺序排列的列表。

可以将 CUBE 和 ROLLUP 聚合包括在一个 GROUPING SETS 聚合内。 CUBE 和 ROLLUP 聚合可产生大量输出。在这种情况下，使用 GROUPING SETS 仅返回某些结果。

不能将任何聚合包含在 CUBE 或 ROLLUP 表达式中。

可以将多个 GROUPING SETS、CUBE 或 ROLLUP 聚合附加到同一个查询中。例如：

  
GROUP BY a,b,c,d, ROLLUP(a,b)
GROUP BY a,b,c,d, CUBE((a,b),c,d)
GROUP BY a,b,c,d, CUBE(a,b), ROLLUP (c,d)
GROUP BY ROLLUP(a), CUBE(b), GROUPING SETS(c)
GROUP BY a,b,c,d, GROUPING SETS ((a,d),(b,c),CUBE(a,b))
GROUP BY a,b,c,d, GROUPING SETS ((a,d),(b,c),(a,b),(a),(b),())

用法注意事项

  • 表达式不能包括聚合函数。但是，可以将 GROUP BY 子句与 CUBE、GROUPING SETS 和 ROLLUP 一起使用，以返回每个组的汇总值。

  • 创建 GROUP BY 子句时，必须包括 SELECT 列表中显示的所有非聚合列。

  • 如果 GROUP BY 子句包括 WHERE 子句，Vertica 会忽略所有不满足 WHERE 子句的行。

示例

此示例显示了如何将 WHERE 子句与 GROUP BY 一起使用。在这种情况下，示例检索姓氏以 S 开头的所有员工，忽略所有不符合此条件的行。GROUP BY 子句使用 ILIKE 函数仅检索以 S 开头的姓氏。聚合函数 SUM 计算每个组的总休假天数。

=> SELECT employee_last_name, SUM(vacation_days)
   FROM employee_dimension
   WHERE employee_last_name ILIKE 'S%'
   GROUP BY employee_last_name;
 employee_last_name | SUM
--------------------+------
 Sanchez            | 2892
 Smith              | 2672
 Stein              | 2660
(3 rows)

以下示例中的 GROUP BY 子句按供应商地区和供应商地区的最大交易对结果分组：


=> SELECT vendor_region, MAX(deal_size) AS "Biggest Deal"
   FROM vendor_dimension
   GROUP BY vendor_region;
 vendor_region | Biggest Deal
---------------+--------------
 East          |       990889
 MidWest       |       699163
 NorthWest     |        76101
 South         |       854136
 SouthWest     |       609807
 West          |       964005
(6 rows)

以下查询使用 HAVING 子句修改上一个查询，该子句指定仅返回最大交易规模超过 $900,000 的组：

=> SELECT vendor_region, MAX(deal_size) as "Biggest Deal"
   FROM vendor_dimension
   GROUP BY vendor_region
   HAVING MAX(deal_size) > 900000;
 vendor_region | Biggest Deal
---------------+--------------
 East          |       990889
 West          |       964005
(2 rows)

可以将 GROUP BY 子句与标量类型的一维数组一起使用。在以下示例中，grants 为 ARRAY[VARCHAR]，grant_values 为 ARRAY[INT]。

=> CREATE TABLE employees (id INT, department VARCHAR(50), grants ARRAY[VARCHAR], grant_values ARRAY[INT]);

=> COPY employees FROM STDIN;
42|Physics|[US-7376,DARPA-1567]|[65000,135000]
36|Physics|[US-7376,DARPA-1567]|[10000,25000]
33|Physics|[US-7376]|[30000]
36|Astronomy|[US-7376,DARPA-1567]|[5000,4000]
\.

=> SELECT department, grants, SUM(apply_sum(grant_values)) FROM employees GROUP BY grants, department;
 department |          grants          |  SUM
------------+--------------------------+--------
 Physics    | ["US-7376","DARPA-1567"] | 235000
 Astronomy  | ["US-7376","DARPA-1567"] |   9000
 Physics    | ["US-7376"]              |  30000
(3 rows)

不包含聚合的 GROUP BY 子句的使用方式类似于 SELECT DISTINCT。例如，以下两个查询返回相同的结果：

=> SELECT DISTINCT household_id FROM customer_dimension;

=> SELECT household_id FROM customer_dimension GROUP BY household_id;

另请参阅

1 - ROLLUP 聚合

作为 GROUP BY 子句的扩展来自动执行小计聚合。 ROLLUP 在单个 SQL 查询内的不同级别跨多个维度执行聚合。

您可以将 ROLLUP 子句与三个分组函数一起使用：

语法

ROLLUP grouping-expression[,...]

参数

group‑expression
以下两项中的一个或两个：

  • 一个包含常数和 FROM 指定的表中的列引用且不是聚合或分组函数的表达式。例如：

    column1, (column2+1), column3+column4

  • 一个多级表达式，是以下几项之一：

    • ROLLUP

    • CUBE

    • GROUPING SETS

限制

GROUP BY ROLLUP 不会对结果排序。要对数据进行排序，ORDER BY 子句必须跟在 GROUP BY 子句之后。

聚合级别

如果 n 为分组列的数量，则 ROLLUP 将生成 n+1 个小计和总计级别。由于 ROLLUP 将删除每一步中最右侧的列，所以请仔细指定列顺序。

假设 ROLLUP(A, B, C) 创建 4 个组：

  • (A, B, C)

  • (A, B)

  • (A)

  • ()

由于 ROLLUP 将删除每一步中最右侧的列，所以没有 (A, C) 和 (B, C) 组。

如果将 2 个或更多的列括在圆括号内，GROUP BY 会将其视为单一实体。例如：

  • ROLLUP(A, B, C) 将创建 4 个组：

    
(A, B, C)
(A, B)
(A)
()

  • ROLLUP((A, B), C) 会将 (A, B) 视为单一实体并创建 3 个组：

    (A, B, C)
(A, B)
()

示例：聚合完整数据集

以下示例显示了如何使用 GROUP BY 子句确定几年内家庭用电和书籍的开支。SUM 聚合函数计算每年在各类别上的总开销。

假设您有一个表，其中包含关于家庭书籍和用电开支的信息：

=> SELECT * FROM expenses ORDER BY Category, Year;
 Year |  Category   | Amount
------+-------------+--------
2005  | Books       |  39.98
2007  | Books       |  29.99
2008  | Books       |  29.99
2005  | Electricity | 109.99
2006  | Electricity | 109.99
2007  | Electricity | 229.98

对于 expenses 表，ROLLUP 计算 2005–2007 年之间各类别的小计：

  • 书籍：$99.96

  • 用电：$449.96

  • 总计：$549.92。

使用 ORDER BY 子句对结果排序：

=> SELECT Category, Year, SUM(Amount) FROM expenses
   GROUP BY ROLLUP(Category, Year) ORDER BY 1,2, GROUPING_ID();
 Category    | Year |  SUM
-------------+------+--------
 Books       | 2005 |  39.98
 Books       | 2007 |  29.99
 Books       | 2008 |  29.99
 Books       |      |  99.96
 Electricity | 2005 | 109.99
 Electricity | 2006 | 109.99
 Electricity | 2007 | 229.98
 Electricity |      | 449.96
             |      | 549.92

示例：将 ROLLUP 与 HAVING 子句一起使用

此示例说明如何将 ROLLUP 与 HAVING 子句一起使用，以限制 GROUP BY 结果。以下查询只生成 ROLLUP 类别，其中 year 根据 GROUPING 函数中的表达式得出小计：

=> SELECT Category, Year, SUM(Amount) FROM expenses
   GROUP BY ROLLUP(Category,Year) HAVING GROUPING(Year)=1
   ORDER BY 1, 2, GROUPING_ID();
 Category    | Year |  SUM
-------------+------+--------
 Books       |      |  99.96
 Electricity |      | 449.96
             |      | 549.92

下一个示例对 (Category, Year) 而非完整结果进行汇总。GROUPING_ID 函数指定聚合 3 个以下的级别：

=> SELECT Category, Year, SUM(Amount) FROM expenses
   GROUP BY ROLLUP(Category,Year) HAVING GROUPING_ID(Category,Year)<3
   ORDER BY 1, 2, GROUPING_ID();
 Category    | Year |  SUM
-------------+------+--------
 Books       | 2005 |  39.98
 Books       | 2007 |  29.99
 Books       | 2008 |  29.99
 Books       |      |  99.96
 Electricity | 2005 | 109.99
 Electricity | 2006 | 109.99
 Electricity | 2007 | 229.98
 Electricity |      | 449.96

另请参阅

2 - CUBE 聚合

作为 GROUP BY 子句的扩展，自动对指定列执行所有可能的聚合。

您可以将 ROLLUP 子句与三个分组函数一起使用：

语法

GROUP BY group-expression[,...]

参数

group-expression
以下两项中的一个或两个：

  • 一个包含常数和 FROM 指定的表中的列引用且不是聚合或分组函数的表达式。例如：

    column1, (column2+1), column3+column4

  • 一个多级表达式，是以下几项之一：

    • ROLLUP

    • CUBE

    • GROUPING SETS

限制

  • GROUP BY CUBE 不对数据排序。如果要对数据排序，请使用 ORDER BY 子句。ORDER BY 子句必须跟在 GROUP BY 子句的后面。
  • CUBE 可以用在 GROUPING SETS 表达式中，但不可以用在 ROLLUP 表达式或另一个 CUBE 表达式中。

CUBE 聚合级别

如果 n 是指分组列的数量，则 CUBE 将生成 2n 个聚合级别。例如：

CUBE (A, B, C) 将生成所有可能的分组，即产生以下八个组：

  • (A, B, C)
  • (A, B)
  • (A, C)
  • (B, C)
  • (A)
  • (B)
  • (C)
  • ()

如果增加 CUBE 列数，则 CUBE 分组数会呈指数增加。CUBE 查询可能会占用大量资源，并产生不感兴趣的组合。在这种情况下，请考虑使用 GROUPING SETS 聚合，因为通过它可以选择特定的分组。

示例

使用 CUBE 返回所有分组

假设您有一个表，其中包含关于家庭书籍和用电开支的信息：

=> SELECT * FROM expenses ORDER BY Category, Year;
 Year |  Category   | Amount
------+-------------+--------
2005  | Books       |  39.98
2007  | Books       |  29.99
2008  | Books       |  29.99
2005  | Electricity | 109.99
2006  | Electricity | 109.99
2007  | Electricity | 229.98

使用 CUBE 聚合按类别和年份聚合数据：

=> SELECT Category, Year, SUM(Amount) FROM expenses
   GROUP BY CUBE(Category, Year) ORDER BY 1, 2, GROUPING_ID();
 Category    | Year |  SUM
-------------+------+--------
 Books       | 2005 |  39.98
 Books       | 2007 |  29.99
 Books       | 2008 |  29.99
 Books       |      |  99.96
 Electricity | 2005 | 109.99
 Electricity | 2006 | 109.99
 Electricity | 2007 | 229.98
 Electricity |      | 449.96
             | 2005 | 149.97
             | 2006 | 109.99
             | 2007 | 259.97
             | 2008 |  29.99
             |      | 549.92

结果会包含每个类别和年份的小计以及总计 ($549.92)。

将 CUBE 与 HAVING 子句一起使用

此示例显示了如何限制 GROUP BY 结果以及如何将 CUBE 聚合与 HAVING 子句结合使用。此查询仅返回类别合计和总计：

=> SELECT Category, Year, SUM(Amount) FROM expenses
   GROUP BY CUBE(Category,Year) HAVING GROUPING(Year)=1;
 Category    | Year |  SUM
-------------+------+--------
 Books       |      |  99.96
 Electricity |      | 449.96
             |      | 549.92

下一个查询仅返回每个年度两种类别的聚合。GROUPING ID 函数指定忽略总计 ($549.92)：

=> SELECT Category, Year, SUM (Amount) FROM expenses
   GROUP BY CUBE(Category,Year) HAVING GROUPING_ID(Category,Year)<2
   ORDER BY 1, 2, GROUPING_ID();
 Category   | Year |  SUM
------------+------+--------
 Books      | 2005 |  39.98
 Books      | 2007 |  29.99
 Books      | 2008 |  29.99
 Books      |      |  99.96
 Electrical | 2005 | 109.99
 Electrical | 2006 | 109.99
 Electrical | 2007 | 229.98
 Electrical |      | 449.96

另请参阅

3 - GROUPING SETS 聚合

GROUPING SETS 聚合是对在您指定的分组上自动执行小计聚合的 GROUP BY 子句的扩展。

您可以将 GROUPING SETS 子句与三个分组函数一起使用：

若要对数据排序，请使用 ORDER BY 子句。ORDER BY 子句必须跟在 GROUP BY 子句之后。

语法

GROUP BY group-expression[,...]

参数

group-expression
以下两项中的一个或两个：

  • 一个包含常数和 FROM 指定的表中的列引用且不是聚合或分组函数的表达式。例如：

    column1, (column2+1), column3+column4

  • 一个多级表达式，是以下几项之一：

    • ROLLUP

    • CUBE

    • GROUPING SETS

定义分组

GROUPING SETS 允许您准确指定结果中需要哪些组。您还可以按照以下方式联接分组：

以下示例子句的分组结果将会显示。

示例：选择分组

此示例显示了如何仅选择您需要的分组。假设您仅希望聚合列，并且您不需要总计。第一个查询会忽略合计。在第二个查询中，您将 () 添加到 GROUPING SETS 列表以获得合计。使用 ORDER BY 子句按分组对结果进行排序：

=> SELECT Category, Year, SUM(Amount) FROM expenses
   GROUP BY GROUPING SETS((Category, Year), (Year))
   ORDER BY 1, 2, GROUPING_ID();
  Category  | Year |  SUM
------------+------+--------
 Books      | 2005 |  39.98
 Books      | 2007 |  29.99
 Books      | 2008 |  29.99
 Electrical | 2005 | 109.99
 Electrical | 2006 | 109.99
 Electrical | 2007 | 229.98
            | 2005 | 149.97
            | 2006 | 109.99
            | 2007 | 259.97
            | 2008 |  29.99

=> SELECT Category, Year, SUM(Amount) FROM expenses
   GROUP BY GROUPING SETS((Category, Year), (Year), ())
   ORDER BY 1, 2, GROUPING_ID();
  Category  | Year |  SUM
------------+------+--------
 Books      | 2005 |  39.98
 Books      | 2007 |  29.99
 Books      | 2008 |  29.99
 Electrical | 2005 | 109.99
 Electrical | 2006 | 109.99
 Electrical | 2007 | 229.98
            | 2005 | 149.97
            | 2006 | 109.99
            | 2007 | 259.97
            | 2008 |  29.99
            |      | 549.92

另请参阅