本文介绍常见的行列转换在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)