人大金仓数据库KingbaseES KES中PIVOT功能的使用及说明

原创数据猿 2023-12-27

1397

关键字：人大金仓、KingbaseES、pivot

概述

在KES中，PIVOT是SELECT的一种扩展查询方式，其操作是将查询结果集进行行转列。

行列相互转换是在报告方面是一种常见的需求，也是对SELECT语句的扩展。

PIVOT操作为行转列，并在转换过程中聚合数据，新的列表示不同范围的聚合数据，PIVOT操作的输出通常比初始数据行包含更多的列和更少的行。

示例：

SELECT select_list

FROM table_expression

[ PIVOT aggfunction FOR column... IN (('column_const','column_const'...) [AS alais], ....) ]

PIVOT 首先计算指定的聚合函数的值。通常聚合函数必须指定GROUP BY子句才能返回多个值，但是在PIVOT子句不包含显式的GROUP BY子句。在PIVOT子句执行隐式GROUP BY。

隐式GROUP BY是基于所有没有在PIVOT子句中引用的列，以及在在PIVOT的IN子句中指定的一组值。

根据指定的分组列和聚合值，生成的交叉表包含以下列：

所有没在PIVOT子句中引用的隐式分组列值。
以及与PIVOT的IN子句中的值对应的新列，每个聚合的值被转换到适当的新列中。数据库将为每个新列生成一个名称：如果没有为聚合函数提供别名，则使用每个pivot列的值作为聚合函数转置到的每个新列的名称。如果为聚合函数提供别名，则连接PIVOT列名、下划线字符和聚合函数别名为每个新列生成的一个名称。

语法

select_pivot_clause::=

pivot_clause::=

temp

pivot_for_clause::=

pivot_in_clause::=

select_pivot_clause子句：

pivot unpivot的输入对象可以为表、视图或者子查询。
当为一个子查询的时候对于目标列的限制：
1. 支持目标列的类型可以为列名、常量、函数或者表达式。
2. 目标列中存在同名列必须使用别名区分。
3. 目标列中使用除列名外的其他类型，常量、函数、表达式等必须使用别名。
4. 目标列中不支持使用‘*’。
5. Pivot子句中对于列的引用只能使用列名，不支持数据库、模式名、表名的前缀引用。

pivot_clause子句：

pivot子句的作用是将行转换为列，并在转换过程中聚合数据，pivot操作的输出通常比初始数据行包含更多的列和更少的行。Pivot_clause执行以下步骤：

pivot_clause首先计算指定的聚合函数的值。通常聚合函数必须指定GROUP BY子句才能返回多个值，但是pivot_clause不包含显式的GROUP BY子句。pivot_clause执行隐式GROUP BY。隐式GROUP BY是基于所有没有在pivot_clause中引用的列，以及在pivot_in_clause中指定的一组值。
根据步骤1中指定的分组列和聚合值，生成的交叉表包含以下列：
所有没在pivot_clause中引用的隐式分组列值。
以及与pivot_in_clause子句中的值对应的新列，每个聚合的值被转换到适当的新列中。数据库将为每个新列生成一个名称：如果没有为聚合函数提供别名，则使用每个pivot列的值作为聚合函数转置到的每个新列的名称；如果为聚合函数提供别名，则连接pivot列名、下划线字符和聚合函数别名为每个新列生成的一个名称。

pivot_clause子句有以下语义：

pivot_for_clause子句：

指定我们需要将行转成列的字段。

pivot_in_clause子句：

为pivot_for_clause子句中的列定义过滤器，pivot_in_clause子句中每个值的聚合将被转置到单独的列中。

当子句中使用多列的结构时支持使用别名

例:

(（’col1’,’col2’）as A, （’col1’,’col2’）as B)

这种情况下Oracle在语法上其中每个组内部的列不能单独使用别名，在kingbase中可以但不会做任何处理。

例:

(（’col1’ as a,’col2’ as b）as A, （’col1’,’col2’）as B)

aggregate_function:

对于聚合函数可以指定AVG、COUNT、MAX、MIN或SUM函数。

expr：

计算结果为主列的常数值表达式，可以为每个主列提供别名，如果没有别名，列标题将成为带引号标识符。

支持创建表、视图、物化视图和prepare语句、匿名块中使用PIVOT操作。

示例

PIVOT SQL：

SELECT * FROM TABLE PIVOT (SUM(销量) FOR 产品名称 IN

(苹果 as 苹果销量，西瓜 as 西瓜销量，桃子 as 桃子销量));

如下图所示，从左边到右边为PIVOT操作，将左表的产品名称这一列的值转换为新的列。从右边到左边为UNPIVOT操作，将右表多个单品销量列合并到一个列中。

ivot的返回类型是可以支持create table操作的产生新列的类型为原类型：

原表：

使用pivot的返回结果集create table:

查看表结构：

例子：

--Basic table and data

create table pivot_t1 ( month int ,name varchar(20) , nums int);

insert into pivot_t1 values(1,'apple',1000);

insert into pivot_t1 values(2,'apple',2000);

insert into pivot_t1 values(3,'apple',4000);

insert into pivot_t1 values(1,'orange',400);

insert into pivot_t1 values(2,'orange',500);

insert into pivot_t1 values(3,'orange',500);

insert into pivot_t1 values(1,'grape',3500);

insert into pivot_t1 values(2,'grape',3500);

insert into pivot_t1 values(3,'grape',3500);

select * from pivot_t1;

month | name | nums

-------+--------+------

1 | apple | 1000

2 | apple | 2000

3 | apple | 4000

1 | orange | 400

2 | orange | 500

3 | orange | 500

1 | grape | 3500

2 | grape | 3500

3 | grape | 3500

(9 rows)

--An aggregate function argument is a column

select * from pivot_t1 pivot (sum(nums) for name in ('apple' as pingguo ,'orange' as juzi ,'grape' as putao));

month | pingguo | juzi | putao

-------+---------+------+-------

1 | 1000 | 400 | 3500

2 | 2000 | 500 | 3500

3 | 4000 | 500 | 3500

(3 rows)

--An aggregate function argument is an expr that evaluates column and constant

select * from pivot_t1 pivot (sum(nums+1) for name in ('apple' as pingguo ,'orange' as juzi ,'grape' as putao));

month | pingguo | juzi | putao

-------+---------+------+-------

1 | 1001 | 401 | 3501

2 | 2001 | 501 | 3501

3 | 4001 | 501 | 3501

(3 rows)

--An aggregate function argument is an expr that evaluates column and constants

select * from pivot_t1 pivot (sum(2*nums+1) for name in ('apple' as pingguo ,'orange' as juzi ,'grape' as putao));

month | pingguo | juzi | putao

-------+---------+------+-------

1 | 2001 | 801 | 7001

2 | 4001 | 1001 | 7001

3 | 8001 | 1001 | 7001

(3 rows)

--An aggregate function argument is an expr that evaluates constants

select * from pivot_t1 pivot (sum(2*4+1) for name in ('apple' as pingguo ,'orange' as juzi ,'grape' as putao));

month | nums | pingguo | juzi | putao

-------+------+---------+------+-------

1 | 400 | | 9 |

1 | 1000 | 9 | |

1 | 3500 | | | 9

2 | 500 | | 9 |

2 | 2000 | 9 | |

2 | 3500 | | | 9

3 | 500 | | 9 |

3 | 3500 | | | 9

3 | 4000 | 9 | |

(9 rows)

--An aggregate function argument is NULL

select * from pivot_t1 pivot (sum(null) for name in ('apple' as pingguo ,'orange' as juzi ,'grape' as putao));

month | nums | pingguo | juzi | putao

-------+------+---------+------+-------

1 | 400 | | |

1 | 1000 | | |

1 | 3500 | | |

2 | 500 | | |

2 | 2000 | | |

2 | 3500 | | |

3 | 500 | | |

3 | 3500 | | |

3 | 4000 | | |

(9 rows)

--An aggregate function argument is "*"

select * from pivot_t1 pivot (count(*) for name in ('apple' as pingguo ,'orange' as juzi ,'grape' as putao));

month | nums | pingguo | juzi | putao

-------+------+---------+------+-------

1 | 400 | 0 | 1 | 0

1 | 1000 | 1 | 0 | 0

1 | 3500 | 0 | 0 | 1

2 | 500 | 0 | 1 | 0

2 | 2000 | 1 | 0 | 0

2 | 3500 | 0 | 0 | 1

3 | 500 | 0 | 1 | 0

3 | 3500 | 0 | 0 | 1

3 | 4000 | 1 | 0 | 0

(9 rows)

--An aggregate function argument is an expr that evaluates column and column

select * from pivot_t1 pivot (sum(nums+nums) for name in ('apple' as pingguo ,'orange' as juzi ,'grape' as putao));

month | pingguo | juzi | putao

-------+---------+------+-------

1 | 2000 | 800 | 7000

2 | 4000 | 1000 | 7000

3 | 8000 | 1000 | 7000

(3 rows)

--paramref

select * from pivot_t1 pivot (sum(a[1]) for name in ('apple' as pingguo ,'orange' as juzi ,'grape' as putao));