南大通用GBase 8a数据行列转换的实现方法

本文介绍常见的行列转换在GBase8a数据库内实现的方法。

行转列

类似从kv的bigtable转化成标准的行。

数据

学生每个科目的成绩单流水，转化成每个学生的成绩明细行。

gbase> create table score(name varchar(100),subject varchar(100),score int);
Query OK, 0 rows affected (Elapsed: 00:00:00.75)

gbase> insert into score values ('王海', '语文', 86),
    ->         ('王海', '数学', 83),
    ->         ('王海', '英语', 93),
    ->         ('陶俊', '语文', 88),
    ->         ('陶俊', '数学', 84),
    ->         ('陶俊', '英语', 94),
    ->         ('刘可', '语文', 80),
    ->         ('刘可', '数学', 86),
    ->         ('刘可', '英语', 88),
    ->         ('李春', '语文', 89),
    ->         ('李春', '数学', 80),
    ->         ('李春', '英语', 87);;
Query OK, 12 rows affected (Elapsed: 00:00:00.41)
Records: 12  Duplicates: 0  Warnings: 0

ERROR:
No query specified

gbase> select * from score;
+--------+---------+-------+
| name   | subject | score |
+--------+---------+-------+
| 王海   | 语文    |    86 |
| 王海   | 数学    |    83 |
| 王海   | 英语    |    93 |
| 陶俊   | 语文    |    88 |
| 陶俊   | 数学    |    84 |
| 陶俊   | 英语    |    94 |
| 刘可   | 语文    |    80 |
| 刘可   | 数学    |    86 |
| 刘可   | 英语    |    88 |
| 李春   | 语文    |    89 |
| 李春   | 数学    |    80 |
| 李春   | 英语    |    87 |
+--------+---------+-------+
12 rows in set (Elapsed: 00:00:00.00)

通过case when计算新的列

通过case when, 判断当前客户如果是某一种学科，则计算score，否则设置为0.这样，通过不同客户的case when就拿到了每个学科的分数。

select name,
select *,
    case when subject='语文' then score else 0 end '语文' ,
    case when subject='数学' then score else 0 end '数学' ,
    case when subject='英语' then score else 0 end '英语' 
from score

gbase> select *,
    ->     case when subject='语文' then score else 0 end '语文' ,
    ->     case when subject='数学' then score else 0 end '数学' ,
    ->     case when subject='英语' then score else 0 end '英语'
    -> from score;
+--------+---------+-------+--------+--------+--------+
| name   | subject | score | 语文   | 数学   | 英语   |
+--------+---------+-------+--------+--------+--------+
| 王海   | 语文    |    86 |     86 |      0 |      0 |
| 王海   | 数学    |    83 |      0 |     83 |      0 |
| 王海   | 英语    |    93 |      0 |      0 |     93 |
| 陶俊   | 语文    |    88 |     88 |      0 |      0 |
| 陶俊   | 数学    |    84 |      0 |     84 |      0 |
| 陶俊   | 英语    |    94 |      0 |      0 |     94 |
| 刘可   | 语文    |    80 |     80 |      0 |      0 |
| 刘可   | 数学    |    86 |      0 |     86 |      0 |
| 刘可   | 英语    |    88 |      0 |      0 |     88 |
| 李春   | 语文    |    89 |     89 |      0 |      0 |
| 李春   | 数学    |    80 |      0 |     80 |      0 |
| 李春   | 英语    |    87 |      0 |      0 |     87 |
+--------+---------+-------+--------+--------+--------+
12 rows in set (Elapsed: 00:00:00.00)

通过聚合运算，汇总结果

理论上一个学生一门功课不会有多个成绩，这里就用max函数，即使有多个也取最高分。

select name,
    sum(case when subject='语文' then score else 0 end) '语文' ,
    sum(case when subject='数学' then score else 0 end) '数学' ,
    sum(case when subject='英语' then score else 0 end) '英语' 
from score group by name;

gbase> select name,
    ->     sum(case when subject='语文' then score else 0 end) '语文' ,
    ->     sum(case when subject='数学' then score else 0 end) '数学' ,
    ->     sum(case when subject='英语' then score else 0 end) '英语'
    -> from score group by name;
+--------+--------+--------+--------+
| name   | 语文   | 数学   | 英语   |
+--------+--------+--------+--------+
| 陶俊   |     88 |     84 |     94 |
| 刘可   |     80 |     86 |     88 |
| 李春   |     89 |     80 |     87 |
| 王海   |     86 |     83 |     93 |
+--------+--------+--------+--------+
4 rows in set (Elapsed: 00:00:00.06)

列转行

继续前面的例子，我们将字段名改成拼音。

数据

create table score2 as select name,
    sum(case when subject='语文' then score else 0 end) yuwen ,
    sum(case when subject='数学' then score else 0 end) shuxue ,
    sum(case when subject='英语' then score else 0 end) waiyu 
from score group by name;

gbase> create table score2 as select name,
    ->     sum(case when subject='语文' then score else 0 end) yuwen ,
    ->     sum(case when subject='数学' then score else 0 end) shuxue ,
    ->     sum(case when subject='英语' then score else 0 end) waiyu
    -> from score group by name;
Query OK, 4 rows affected (Elapsed: 00:00:01.33)

gbase>
gbase>
gbase> select * from score2;
+--------+-------+--------+-------+
| name   | yuwen | shuxue | waiyu |
+--------+-------+--------+-------+
| 陶俊   |    88 |     84 |    94 |
| 刘可   |    80 |     86 |    88 |
| 李春   |    89 |     80 |    87 |
| 王海   |    86 |     83 |    93 |
+--------+-------+--------+-------+
4 rows in set (Elapsed: 00:00:00.03)

通过union all 转行

select name,'语文' subject, yuwen score  from score2
union all
select name,'数学' subject, shuxue score  from score2
union all
select name,'英语' subject, waiyu score  from score2

gbase> select name,'语文' subject, yuwen score  from score2
    -> union all
    -> select name,'数学' subject, shuxue score  from score2
    -> union all
    -> select name,'英语' subject, waiyu score  from score2;
+--------+---------+-------+
| name   | subject | score |
+--------+---------+-------+
| 陶俊   | 语文    |    88 |
| 刘可   | 语文    |    80 |
| 李春   | 语文    |    89 |
| 陶俊   | 数学    |    84 |
| 刘可   | 数学    |    86 |
| 李春   | 数学    |    80 |
| 陶俊   | 英语    |    94 |
| 刘可   | 英语    |    88 |
| 李春   | 英语    |    87 |
| 王海   | 语文    |    86 |
| 王海   | 数学    |    83 |
| 王海   | 英语    |    93 |
+--------+---------+-------+
12 rows in set (Elapsed: 00:00:00.00)