南大通用GBase 8a行转列函数pivot介绍

GBase 8a提供了pivot函数实现行转列功能,也叫数据透视。

语法

pivot(
  <聚合函数>(<要聚合的列>)[ as 别名][,聚合函数>(<要聚合的列>)[ as 别名]...]
  for <列1[,列2...]> 
    in (常量1 [as 别名1] [,常量2[ as 别名2]...])
)

说明

聚合函数部分:为新列的值,可以有多个。

for: 是要旋转的列:符合条件的列值在in里指定;单列时可以不使用小括号,比如for column_name in , 多列时要使用小括号,且多个列的值是整体作为一个in中的常量值。

in:每个常量值会转换成一个新列。常量为字符或日期时需要使用单引号包围起来。新列的列名,将采用常量值或者其别名。如聚合列有设置别名,则追加下划线和别名,比如 常量值别名1_聚合列别名。 常量,如果是多列,强烈建议多列常量设置别名,默认列名不容易使用。

单列:for column in (常量1,常量2....)

多列 for (column1,column2) in ( (col1的值,col2的值) name1,(col1的值,col2的值) name2,(col1的值,col2的值). name3..)

注意:所有未参与for和聚合的列,都将默认group。可以用嵌套查询来减少涉及的列。

select * from ( select XX from tp) tmp pivot (。。。。。)

测试版本

gbase> select version();
+---------------------------+
| version()                 |
+---------------------------+
| 9.5.3.28.4_patch.1bcc63ff |
+---------------------------+
1 row in set (Elapsed: 00:00:00.00)

样例表



gbase> desc tp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| class | varchar(10) | YES  |     | NULL    |       |
| score | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (Elapsed: 00:00:00.01)

样例数据

每个学生,每门功课有多次考试成绩

gbase> select * from tp order by class,name,score;
+--------+--------+-------+
| name   | class  | score |
+--------+--------+-------+
| 张三   | 数学   |    99 |
| 张三   | 数学   |   129 |
| 李四   | 数学   |    88 |
| 李四   | 数学   |   128 |
| 王五   | 数学   |   111 |
| 王五   | 数学   |   161 |
| 张三   | 语文   |   120 |
| 张三   | 语文   |   120 |
| 李四   | 语文   |   140 |
| 李四   | 语文   |   150 |
| 王五   | 语文   |   100 |
| 王五   | 语文   |   120 |
+--------+--------+-------+
12 rows in set (Elapsed: 00:00:00.02)

单个聚合

每门课程,指定人员的总得分

gbase> select * from tp pivot(sum(score) for class in ('语文','数学'));
+--------+--------+--------+
| name   | 语文   | 数学   |
+--------+--------+--------+
| 李四   |    290 |    216 |
| 张三   |    240 |    228 |
| 王五   |    220 |    272 |
+--------+--------+--------+
3 rows in set (Elapsed: 00:00:00.06)

多个聚合

每门课程,指定人员的平均分,最高分。注意,要避免列名重复,所以给每个聚合函数结果一个别名。显示时会自动加上下划线。

当然,其中一个聚合列可以不加别名,其列名就是常量值对应的名字。

gbase> select * from tp pivot(sum(score)sum, avg(score)avg for class in ('语文','数学'));
+--------+------------+------------+------------+------------+
| name   | 语文_sum   | 数学_sum   | 语文_avg   | 数学_avg   |
+--------+------------+------------+------------+------------+
| 李四   |        290 |        216 |   145.0000 |   108.0000 |
| 张三   |        240 |        228 |   120.0000 |   114.0000 |
| 王五   |        220 |        272 |   110.0000 |   136.0000 |
+--------+------------+------------+------------+------------+
3 rows in set (Elapsed: 00:00:00.06)

gbase> select * from tp pivot(sum(score), avg(score)avg for class in ('语文','数学'));
+--------+--------+--------+------------+------------+
| name   | 语文   | 数学   | 语文_avg   | 数学_avg   |
+--------+--------+--------+------------+------------+
| 张三   |    240 |    228 |   120.0000 |   114.0000 |
| 王五   |    220 |    272 |   110.0000 |   136.0000 |
| 李四   |    290 |    216 |   145.0000 |   108.0000 |
+--------+--------+--------+------------+------------+
3 rows in set (Elapsed: 00:00:00.06)

给常量值赋予别名

给课程名字设置列名。

gbase> select * from tp pivot(sum(score)sum, avg(score)avg for class in ('语文' yw,'数学' sx));
+--------+--------+--------+----------+----------+
| name   | yw_sum | sx_sum | yw_avg   | sx_avg   |
+--------+--------+--------+----------+----------+
| 李四   |    290 |    216 | 145.0000 | 108.0000 |
| 张三   |    240 |    228 | 120.0000 | 114.0000 |
| 王五   |    220 |    272 | 110.0000 | 136.0000 |
+--------+--------+--------+----------+----------+
3 rows in set (Elapsed: 00:00:00.06)

FOR多列

增加一个年龄列

gbase> desc tp2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| class | varchar(10) | YES  |     | NULL    |       |
| score | int(11)     | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (Elapsed: 00:00:00.00)

gbase> select * from tp2;
+--------+--------+-------+------+
| name   | class  | score | age  |
+--------+--------+-------+------+
| 张三   | 语文   |   120 |   40 |
| 李四   | 语文   |   140 |   45 |
| 王五   | 语文   |   100 |   50 |
| 张三   | 数学   |    99 |   40 |
| 李四   | 数学   |    88 |   45 |
| 王五   | 数学   |   111 |   50 |
| 张三   | 语文   |   120 |   40 |
| 李四   | 语文   |   150 |   45 |
| 王五   | 语文   |   120 |   50 |
| 张三   | 数学   |   129 |   40 |
| 李四   | 数学   |   128 |   45 |
| 王五   | 数学   |   161 |   50 |
+--------+--------+-------+------+
12 rows in set (Elapsed: 00:00:00.01)

gbase> select * from tp2 order by class,name,score;
+--------+--------+-------+------+
| name   | class  | score | age  |
+--------+--------+-------+------+
| 张三   | 数学   |    99 |   40 |
| 张三   | 数学   |   129 |   40 |
| 李四   | 数学   |    88 |   45 |
| 李四   | 数学   |   128 |   45 |
| 王五   | 数学   |   111 |   50 |
| 王五   | 数学   |   161 |   50 |
| 张三   | 语文   |   120 |   40 |
| 张三   | 语文   |   120 |   40 |
| 李四   | 语文   |   140 |   45 |
| 李四   | 语文   |   150 |   45 |
| 王五   | 语文   |   100 |   50 |
| 王五   | 语文   |   120 |   50 |
+--------+--------+-------+------+
12 rows in set (Elapsed: 00:00:00.03)

多列常量

gbase> select * from tp2 pivot(sum(score)sum for (name,age) in (('张三',40)zs40,('李四',45)ls45));
+--------+----------+----------+
| class  | zs40_sum | ls45_sum |
+--------+----------+----------+
| 语文   |      240 |      290 |
| 数学   |      228 |      216 |
+--------+----------+----------+
2 rows in set (Elapsed: 00:00:00.06)

如果不指定列别名,则默认的列名有些混乱,不方便使用。

gbase> select * from tp2 pivot(sum(score) for (name,age) in (('张三',40),('李四',45)));
+--------+---------------+--------------+
| class  | (('张三',40   | ('李四',45   |
+--------+---------------+--------------+
| 语文   |           240 |          290 |
| 数学   |           228 |          216 |
+--------+---------------+--------------+
2 rows in set (Elapsed: 00:00:00.07)