GBASE南大通用GBase 8s数据库Pivot函数

摘要

Summary

作为一款企业级的数据库产品，南大通用GBase 8s数据库具备杰出的数据处理能力。日常数据处理中，无论是报表制作还是数据分析，我们经常会遇到将行数据转换为列数据的需求。Pivot函数的存在完美解决这一问题，轻松实现行数据到列数据的转换，帮助我们从不同的角度审视数据。本文将详细介绍如何使用Pivot函数进行动态行转列操作，并提供具体的操作示例。

GBase 8s Pivot函数的概述

Pivot，即行转列，是一种将表中的行数据转换为列数据的过程。通过Pivot操作，我们可以将数据表中的一列或多列的数据转化为多列的形式，以便更好地进行横向统计和分析。

Unpivot，即列转行，与Pivot相反，Unpivot则是将表中的列数据转换为行数据。以便更好地进行纵向统计和分析。

Pivot和Unpivot是数据转换中非常有用的两个函数，它们可以根据需要改变数据的呈现方式，使得数据更易于理解和分析。在数据库查询、数据分析、报表制作等领域中，都有着广泛的应用。我们将在下一篇文章介绍Unpivot函数。

GBase 8s pivot的语法详解

Pivot 转换数据的过程：多行被聚合，然后转换成列，每一列表示聚合数据的不同范围。语法如下：

pivot紧跟from，位于from和where之间。

【一句话来说明】：pivot(聚合函数1 as 别名1，聚合函数2 as 别名2 ...) for 需要转为列的字段1,字段2.... in((字段1值1，字段2值1) as 别名1,(字段1值2，字段2值2) as 别名2...)

GBase 8s Pivot示例

注意：以下所用示例使用的数据库版为:GBase8sV8.8_TL_3.5.1_x86_64

下面示例都是对pivot语法的简单演示，使用emp表，在转换前，先看一下基础数据：

--用例1：单列转换

这里将通过deptno和job来汇总sal1，然后转换对deptno的汇总到它们各自的列上

需要聚合的列：sal1

需要转换的列：deptno

转换的列需要过滤的值：10,20,30,40

关于这个例子需要注意的地方：

pivot操作实际执行了隐式的GROUP BY，使用的列是没有出现在pivot_clause中的列（本例为job和deptno），大多数的转换查询都是在列的特定的子集上来执行。像所有的聚合查询一样，额外列的出现会影响分组。在本例中，除了sal 1列，其余所有列都成了分组集，其中deptno为转换列。基础数据中的其他列如ename等如果加入就会影响分组，所以可以使用子查询等来定义基列的集合，例子中的SELECT deptno, job, sal 1 FROM emp作用就是定义基列的集合。

--用例2：所有列都参与的效果：

本例中，除了sal 1列，其余所有列都成了分组集，其中deptno为转换列。这个转换没有什么意义。

--用例3：with as

下面再举一个with as子查询的例子，效果与上述内联视图结果一致。

--用例4：多列转换

上面的例子中 pivot_clause 中定义 2 个聚合，这里得到的转换列是双倍（因为聚合的数量是双倍）。

转换列的数量=聚合列的数量 * pivot_in_clause 中值的个数（2*2=4），

转换列的数量不能超过 1024。

--用例5，多列多值转换

GBase 8s Pivot转换查询注意事项

在 pivot_for_clause 中使用的列（如例子中为 deptno 列)不能出现在SELECT投影列中，在 pivot_for_clause 中的列会根据我们在 pivot_in_clause中提供的值的范围进行分组。下列中试图查询 deptno 列，会引发异常，因为这种情况下，deptno完全从投影中被移除了。

同样也不能查询任何出现在pivot_clause 中的列。比如试图查询 sal1 列，也会引发了同样的异常。

pivot_clause中的列必须使用聚合函数，没有使用聚合函数，也会引发异常。

pivot_in_clause 中，如果指定别名就用别名，如果没有指定就用值当列名；而 pivot_clause中如果指定别名就连接到pivot_in_clause 转换后的相应列的名称的后面，如果没有指定就不用。pivot_clause中有多个聚合函数时，8s允许都不设置别名，8s会自动在后面加上_1,_2..依次类推。别名都不支持加单引号。

其他使用上的限制，可参考《GBase 8s V8.8 SQL 指南：语法.pdf》

GBase 8s数据库官方文档：https://www.gbase.cn/download/gbase-8s-1?category=DOCUMENT

南大通用GBase技术社区：https://www.gbase.cn/community

通过本文的介绍，您可能已经对GBase 8s中的Pivot功能有了全面的认识。它不仅仅是一个简单的数据转换工具，更是数据分析的利器。下一篇文章，我们会展开介绍Unpivot的使用技巧，感谢您的阅读。