金仓数据库 KingbaseES 行列转换

关键字

行专列，列转行,pivot,unpivot

概述

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

下面介绍pivot和unpivot通用的写法，并给出了pivot和unpivot 用法。

行转列（pivot）

构造数据：

1. create table pivot_t1(month integer,fruitname text,quantity integer);
   
2. insert into pivot_t1 values(1,'apple',1000);
   
3. insert into pivot_t1 values(2,'apple',2000);
   
4. insert into pivot_t1 values(3,'apple',3000);
   
5. insert into pivot_t1 values(4,'apple',4000);
   
6. insert into pivot_t1 values(1,'orange',1500);
   
7. insert into pivot_t1 values(2,'orange',2500);
   
8. insert into pivot_t1 values(3,'orange',3500);
   
9. insert into pivot_t1 values(4,'orange',4500);
   
10. insert into pivot_t1 values(1,'grape',1800);
    
11. insert into pivot_t1 values(2,'grape',2800);
    
12. insert into pivot_t1 values(3,'grape',3800);
    
13. insert into pivot_t1 values(4,'grape',4800);
    
14. insert into pivot_t1 values(1,'banana',1600);
    
15. insert into pivot_t1 values(2,'banana',2600);
    
16. insert into pivot_t1 values(3,'banana',3600);
    
17. insert into pivot_t1 values(4,'banana',4600);

复制代码

1. case when语法

1. test=# select month,
   
2. test-#        sum(case fruitname when 'apple' then quantity end) as apple,
   
3. test-#        sum(case fruitname when 'orange' then quantity end) as orange,
   
4. test-#        sum(case fruitname when 'grape' then quantity end) as grape,
   
5. test-#        sum(case fruitname when 'banana' then quantity end) as banana
   
6. test-# from pivot_t1 group by month order by 1;
   
7. month | apple | orange | grape | banana
   
8. -------+-----------+------------+----------+--------
   
9.      1 |  1000 |   1500 |  1800 |   1600
   
10.      2 |  2000 |   2500 |  2800 |   2600
    
11.      3 |  3000 |   3500 |  3800 |   3600
    
12.      4 |  4000 |   4500 |  4800 |   4600
    
13. (4 rows)

复制代码

2. CROSSTAB语法

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

安装扩展 create extension tablefunc;

1. test=# \df crosstab
   
2.                          函数列表
   
3. 架构模式 |   名称   | 结果数据类型 | 参数数据类型  | 类型
   
4. ----------+----------+--------------+---------------+------
   
5. public   | crosstab | SETOF record | text          | 函数
   
6. public   | crosstab | SETOF record | text, integer | 函数
   
7. 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. test=# SELECT *
   
2. test-# FROM crosstab(
   
3. test(#   'select month,fruitname,quantity
   
4. test'#    from pivot_t1 order by 1,2','select distinct fruitname from pivot_t1 order by 1')
   
5. test-# AS (month int, apple varchar, banana varchar, grape varchar, orange varchar);
   
6. month | apple | banana | grape | orange
   
7. -------+-------+--------+-------+--------
   
8.      1 | 1000  | 1600   | 1800  | 1500
   
9.      2 | 2000  | 2600   | 2800  | 2500
   
10.      3 | 3000  | 3600   | 3800  | 3500
    
11.      4 | 4000  | 4600   | 4800  | 4500

复制代码

crosstab() 关键点：

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

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

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

3. pivot 语法

1. test3=# select * from (select month,fruitname,quantity from pivot_t1)
   
2. test3-# pivot(sum(quantity) for fruitname in ('banana','apple' ,'orange','grape'));
   
3. month | banana | apple | orange | grape
   
4. -------+--------+-------+--------+-------
   
5.      1 |   1600 |  1000 |   1500 |  1800
   
6.      2 |   2600 |  2000 |   2500 |  2800
   
7.      3 |   3600 |  3000 |   3500 |  3800
   
8.      4 |   4600 |  4000 |   4500 |  4800
   
9. (4 行记录)

复制代码

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);
   
2. insert into unpivot_t1 values('apple', 1100,1200,1300,1400);
   
3. insert into unpivot_t1 values('orange',2100,2200,2300,null);
   
4. insert into unpivot_t1 values('grape', 3100,null,3300,3400);
   
5. insert into unpivot_t1 values('banana',4100,4200,4300,4400);

复制代码

1.union all 语法

1. test=# select fruitname,'q1',q1 from unpivot_t1
   
2. test-# union all
   
3. test-# select fruitname,'q2',q2 from unpivot_t1
   
4. test-# union all
   
5. test-# select fruitname,'q3',q3 from unpivot_t1
   
6. test-# union all
   
7. test-# select fruitname,'q4',q4 from unpivot_t1;
   
8. fruitname | ?COLUMN? |  q1  
   
9. -----------+----------+------
   
10. apple     | q1       | 1100
    
11. orange    | q1       | 2100
    
12. grape     | q1       | 3100
    
13. banana    | q1       | 4100
    
14. apple     | q2       | 1200
    
15. orange    | q2       | 2200
    
16. grape     | q2       |     
    
17. banana    | q2       | 4200
    
18. apple     | q3       | 1300
    
19. orange    | q3       | 2300
    
20. grape     | q3       | 3300
    
21. banana    | q3       | 4300
    
22. apple     | q4       | 1400
    
23. orange    | q4       |     
    
24. grape     | q4       | 3400
    
25. banana    | q4       | 4400
    
26. (16 rows)

复制代码

2. unnest 函数

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

1. test=# select fruitname,unnest(array['q1','q2','q3','q4']),unnest(array[q1,q2,q3,q4]) from unpivot_t1;
   
2. fruitname | unnest | unnest
   
3. -----------+--------+--------
   
4. apple     | q1     |   1100
   
5. apple     | q2     |   1200
   
6. apple     | q3     |   1300
   
7. apple     | q4     |   1400
   
8. orange    | q1     |   2100
   
9. orange    | q2     |   2200
   
10. orange    | q3     |   2300
    
11. orange    | q4     |      
    
12. grape     | q1     |   3100
    
13. grape     | q2     |      
    
14. grape     | q3     |   3300
    
15. grape     | q4     |   3400
    
16. banana    | q1     |   4100
    
17. banana    | q2     |   4200
    
18. banana    | q3     |   4300
    
19. banana    | q4     |   4400

复制代码

3. unpivot 语法

1. test=# select fruitname,month,quantity from unpivot_t1 unpivot include nulls
   
2. test-# (quantity for month in (q1 as 'Q1',q2 as 'Q2',q3 as 'Q3',q4 as 'Q4')) order by fruitname,month;
   
3. fruitname | month | quantity
   
4. -----------+-------+----------
   
5. apple     | Q1    |     1100
   
6. apple     | Q2    |     1200
   
7. apple     | Q3    |     1300
   
8. apple     | Q4    |     1400
   
9. banana    | Q1    |     4100
   
10. banana    | Q2    |     4200
    
11. banana    | Q3    |     4300
    
12. banana    | Q4    |     4400
    
13. grape     | Q1    |     3100
    
14. grape     | Q2    |         
    
15. grape     | Q3    |     3300
    
16. grape     | Q4    |     3400
    
17. orange    | Q1    |     2100
    
18. orange    | Q2    |     2200
    
19. orange    | Q3    |     2300
    
20. orange    | Q4    |         
    
21. (16 行记录)
22. https://bbs.kingbase.com.cn/thread-14637-1-1.html