KingbaseES 行列转换函数

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

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

一、行转列（pivot）

构造数据：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

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语法

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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;<br> 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扩展包提供。

?

1 2 3 4 5 6 7 8 9

安装扩展 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)的废弃版本。

?

1 2 3 4 5 6 7 8 9 10 11 12

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 语法：

 

?

1 2 3 4 5 6 7 8 9 10 11

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）

构造数据：

?

1 2 3 4 5

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 语法

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

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 函数

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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 语法

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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语言