KingbaseES 支持pivot and unpivot 功能

KingbaseES 通过扩展插件支持了pivot 和unpivot 功能。以下以例子的方式介绍。

一、功能介绍

创建扩展：

create extension kdb_utils_function;

具体功能：

• pivot(聚合函数 for 列名 in (类型)),其中 in ('') 中可以指定列名，还可以指定子查询
• pivot(任一聚合函数 for 需转为列的值所在列名 in (需转为列名的值))
• unpivot(新增值所在列的列名 for 新增列转为行后所在列的列名 in (需转为行的列名))

二、PIVOT 行转列

1、创建测试数据

create table pivot_t1(month integer,fruitname text,quantity integer, otherval integer);

insert into pivot_t1 values(1,'apple',1000,150);
insert into pivot_t1 values(2,'apple',2000,150);
insert into pivot_t1 values(3,'apple',3000,150);
insert into pivot_t1 values(4,'apple',4000,150);
insert into pivot_t1 values(1,'orange',1500,150);
insert into pivot_t1 values(2,'orange',2500,150);
insert into pivot_t1 values(3,'orange',3500,150);
insert into pivot_t1 values(4,'orange',4500,150);
insert into pivot_t1 values(1,'grape',1800,250);
insert into pivot_t1 values(2,'grape',2800,250);
insert into pivot_t1 values(3,'grape',3800,250);
insert into pivot_t1 values(4,'grape',4800,250);
insert into pivot_t1 values(1,'banana',1600,250);
insert into pivot_t1 values(2,'banana',2600,250);
insert into pivot_t1 values(3,'banana',3600,250);
insert into pivot_t1 values(4,'banana',4600,250);

2、例子

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

 month | pingguo | juzi | putao 
-------+---------+------+-------
     1 |    1000 | 1500 |  1800
     2 |    2000 | 2500 |  2800
     3 |    3000 | 3500 |  3800
     4 |    4000 | 4500 |  4800

test=# select * from (select month,fruitname,quantity from pivot_t1) pivot(sum(quantity) for fruitname in ('apple' ,'orange','grape'));
 month | apple | orange | grape 
-------+-------+--------+-------
     1 |  1000 |   1500 |  1800
     2 |  2000 |   2500 |  2800
     3 |  3000 |   3500 |  3800
     4 |  4000 |   4500 |  4800
(4 rows)

test=# select * from  pivot_t1 pivot(sum(quantity) for fruitname in ('apple' ,'orange','grape'));
 month | otherval | apple | orange | grape 
-------+----------+-------+--------+-------
     1 |      150 |  1000 |   1500 |      
     1 |      250 |       |        |  1800
     2 |      150 |  2000 |   2500 |      
     2 |      250 |       |        |  2800
     3 |      150 |  3000 |   3500 |      
     3 |      250 |       |        |  3800
     4 |      150 |  4000 |   4500 |      
     4 |      250 |       |        |  4800
(8 rows)

pivot 计算指定的聚合值( sum(quantity) )，但是pivot 不包含显示的group by子句，pivot 隐式group by 是基于所有没在pivot子句中引用的列(month)，以及在pivot in子句中指定的一组值。

三、UNPIVOT 列转行

1、创建测试数据

create table unpivot_t1(fruitname text,q1 integer,q2 integer,q3 integer,q4 integer);
insert into unpivot_t1 values('apple', 1100,1200,1300,1400);
insert into unpivot_t1 values('orange',2100,2200,2300,null);
insert into unpivot_t1 values('grape', 3100,null,3300,3400);
insert into unpivot_t1 values('banana',4100,4200,4300,4400);

2、测试结果

select fruitname,month,quantity from unpivot_t1 unpivot include nulls (quantity for month in (q1 as 'Q1',q2 as 'Q2',q3 as 'Q3',q4 as 'Q4')) order by fruitname,month;

 fruitname | month | quantity 
-----------+-------+----------
 apple     | Q1    |     1100
 apple     | Q2    |     1200
 apple     | Q3    |     1300
 apple     | Q4    |     1400
 banana    | Q1    |     4100
 banana    | Q2    |     4200
 banana    | Q3    |     4300
 banana    | Q4    |     4400
 grape     | Q1    |     3100
 grape     | Q2    |         
 grape     | Q3    |     3300
 grape     | Q4    |     3400
 orange    | Q1    |     2100
 orange    | Q2    |     2200
 orange    | Q3    |     2300
 orange    | Q4    |         
(16 rows)

 四、crosstab 行转列

create extension tablefunc;

test=# select * from crosstab('select month,fruitname,quantity from pivot_t1 order by month',$$values('apple'),('orange'),('grape')$$) as sg(month int,pingguo int,juzi int,putao int);
 month | pingguo | juzi | putao 
-------+---------+------+-------
     1 |    1000 | 1500 |  1800
     2 |    2000 | 2500 |  2800
     3 |    3000 | 3500 |  3800
     4 |    4000 | 4500 |  4800
(4 rows)

test=# select * from crosstab('select month,fruitname,quantity from pivot_t1 order by month') as sg(month int,"apple" int,"orange" int,"grape" int,"banana" int);
month | apple | orange | grape | banana
-------+-------+--------+-------+--------
1 | 1000 | 1500 | 1800 | 1600
2 | 2500 | 2800 | 2600 | 2000
3 | 3800 | 3500 | 3600 | 3000
4 | 4600 | 4500 | 4000 | 4800
(4 rows)

注意：对于crosstab，order by 非常关键，