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 非常关键，
