KingbaseES 行列转换函数

关键字：    行专列，列转行, pivot, unpivot

行列转换是在数据分析中经常用到的一项功能，KingbaseES从V8R6C3B0071版本开始通过扩展插件(kdb_utils_function)支持了pivot和unpivot功能。在之前的版本如果需要进行行列转换操作要如何处理呢？下面介绍通用的写法，最后再介绍pivot和unpivot 用法。

一、行转列（pivot）

构造数据：

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

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

1. case when语法

test=# select month,
test-#        sum(case fruitname when 'apple' then quantity end) as apple,
test-#        sum(case fruitname when 'orange' then quantity end) as orange,
test-#        sum(case fruitname when 'grape' then quantity end) as grape,
test-#        sum(case fruitname when 'banana' then quantity end) as banana
test-# from pivot_t1 group by month order by 1;

month | apple | orange | grape | banana
-------+-----------+------------+----------+--------
     1 |  1000 |   1500 |  1800 |   1600
     2 |  2000 |   2500 |  2800 |   2600
     3 |  3000 |   3500 |  3800 |   3600
     4 |  4000 |   4500 |  4800 |   4600
(4 rows)

2. CROSSTAB语法

crosstab() 函数由 tablefunc扩展包提供。

安装扩展 create extension tablefunc;

test=# \df crosstab
                         函数列表
 架构模式 |   名称   | 结果数据类型 | 参数数据类型  | 类型
----------+----------+--------------+---------------+------
 public   | crosstab | SETOF record | text          | 函数
 public   | crosstab | SETOF record | text, integer | 函数
 public   | crosstab | SETOF record | text, text    | 函数　

函数说明：

crosstab ( sql text ) ：生成一个“数据透视表”，其中包含行名称和 N 列值，其中 N 由调用查询中指定的行类型决定。

crosstab ( source_sql text, category_sql text ) ：产生一个“数据透视表”，其值列由第二个查询指定。

crosstab ( sql text, N integer ) ：crosstab(text)的废弃版本。

test=# SELECT *
test-# FROM crosstab(
test(#   'select month,fruitname,quantity
test'#    from pivot_t1 order by 1,2','select distinct fruitname from pivot_t1 order by 1')
test-# AS (month int, apple varchar, banana varchar, grape varchar, orange varchar);

 month | apple | banana | grape | orange
-------+-------+--------+-------+--------
     1 | 1000  | 1600   | 1800  | 1500
     2 | 2000  | 2600   | 2800  | 2500
     3 | 3000  | 3600   | 3800  | 3500
     4 | 4000  | 4600   | 4800  | 4500　

crosstab() 关键点：

第一个参数，带有按X，Y汇总的SQL子句，返回X，Y，Value格式的数据集；

第二个参数，SQL子句，返回用于水平表头中透视内容的所有值；

使用AS子句明确指定返回的每一个字段名称和类型，子句中列名需要与第二个参数order by结果一一对应。

3. pivot 语法：

 

test3=# select * from (select month,fruitname,quantity from pivot_t1)
test3-# pivot(sum(quantity) for fruitname in ('banana','apple' ,'orange','grape'));

 month | banana | apple | orange | grape
-------+--------+-------+--------+-------
     1 |   1600 |  1000 |   1500 |  1800
     2 |   2600 |  2000 |   2500 |  2800
     3 |   3600 |  3000 |   3500 |  3800
     4 |   4600 |  4000 |   4500 |  4800

(4 行记录)

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

 

二、列转行（unpivot）

构造数据：

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);

1.union all 语法

test=# select fruitname,'q1',q1 from unpivot_t1
test-# union all
test-# select fruitname,'q2',q2 from unpivot_t1
test-# union all
test-# select fruitname,'q3',q3 from unpivot_t1
test-# union all
test-# select fruitname,'q4',q4 from unpivot_t1;

 fruitname | ?COLUMN? |  q1 
-----------+----------+------
 apple     | q1       | 1100
 orange    | q1       | 2100
 grape     | q1       | 3100
 banana    | q1       | 4100
 apple     | q2       | 1200
 orange    | q2       | 2200
 grape     | q2       |    
 banana    | q2       | 4200
 apple     | q3       | 1300
 orange    | q3       | 2300
 grape     | q3       | 3300
 banana    | q3       | 4300
 apple     | q4       | 1400
 orange    | q4       |    
 grape     | q4       | 3400
 banana    | q4       | 4400

(16 rows)

 

　　

2. unnest 函数

Unnest：将一个数组分解成一组行。

test=# select fruitname,unnest(array['q1','q2','q3','q4']),unnest(array[q1,q2,q3,q4]) from unpivot_t1
 fruitname | unnest | unnest
-----------+--------+--------
 apple     | q1     |   1100
 apple     | q2     |   1200
 apple     | q3     |   1300
 apple     | q4     |   1400
 orange    | q1     |   2100
 orange    | q2     |   2200
 orange    | q3     |   2300
 orange    | q4     |      
 grape     | q1     |   3100
 grape     | q2     |      
 grape     | q3     |   3300
 grape     | q4     |   3400
 banana    | q1     |   4100
 banana    | q2     |   4200
 banana    | q3     |   4300
 banana    | q4     |   4400
      

　　

3. unpivot 语法

test=# select fruitname,month,quantity from unpivot_t1 unpivot include nulls

test-# (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 行记录)

　　

参考文档：

[应用开发及迁移][服务器编程]SQL语言