Impala中多列轉為一行，impala多列轉為
之前有一位朋友諮詢我，Impala中如何?將多列轉為一行，其實Impala中內建函數可以實現，不用自訂函數。
下面我開始示範：
-bash-4.1$ impala-shell
Starting Impala Shell without Kerberos authentication
Connected to cdha:21000
Server version: impalad version 1.4.2-cdh5 RELEASE (build eac952d4ff674663ec3834778c2b981b252aec78)
Welcome to the Impala shell. Press TAB twice to see a list of available commands.
Copyright (c) 2012 Cloudera, Inc. All rights reserved.
(Shell build version: Impala Shell v1.4.2-cdh5 (eac952d) built on Tue Sep 16 19:15:40 PDT 2014)
[cdha:21000] >
create table student(name string,subject string,score decimal(4,1)); -------------建立示範表：student(脫離學校四年了，還不忘自己是學生^_^)
Query: create table student(name string,subject string,score decimal(4,1))
Returned 0 row(s) in 1.97s
[cdha:21000] >
insert into student values('xiaoming','math',89.5); ----------------插入示範資料 (小明和花花的學習成績，他們感情比較好)
Query: insert into student values('xiaoming','math',89.5)
Inserted 1 rows in 2.56s
[cdha:21000] >
insert into student values('xiaoming','english',92);
Query: insert into student values('xiaoming','english',92)
Inserted 1 rows in 0.39s
[cdha:21000] >
insert into student values('xiaoming','chinese',98);
Query: insert into student values('xiaoming','chinese',98)
Inserted 1 rows in 0.42s
[cdha:21000] >
insert into student values('huahua','chinese',80);
Query: insert into student values('huahua','chinese',80)
Inserted 1 rows in 0.40s
[cdha:21000] >
insert into student values('huahua','math',89.5);
Query: insert into student values('huahua','math',89.5)
Inserted 1 rows in 0.29s
[cdha:21000] >
select * from student; ----------每次考完試，老師報分數時都很緊張
Query: select * from student
+----------+---------+-------+
| name | subject | score |
+----------+---------+-------+
| xiaoming | english | 92.0 |
| huahua | chinese | 80.0 |
| xiaoming | chinese | 98.0 |
| huahua | math | 89.5 |
| xiaoming | math | 89.5 |
+----------+---------+-------+
Returned 5 row(s) in 0.23s
[cdha:21000] >
select name,group_concat(subject,',') from student group by name; ------------小試牛刀，看到了吧，多列拼接到一起了
Query: select name,group_concat(subject,',') from student group by name
+----------+----------------------------+
| name | group_concat(subject, ',') |
+----------+----------------------------+
| xiaoming | english,chinese,math |
| huahua | chinese,math |
+----------+----------------------------+
Returned 2 row(s) in 0.38s
------------下面示範有價值的樣本，顯示高手之招，呵呵
------------這樣就可以在一行上面，看到小明和花花的各科成績了
[cdha:21000] >
select name,group_concat(concat_ws('=',subject,cast(score as string)),',') from student group by name;
Query: select name,group_concat(concat_ws('=',subject,cast(score as string)),',') from student group by name
+----------+-------------------------------------------------------------------+
| name | group_concat(concat_ws('=', subject, cast(score as string)), ',') |
+----------+-------------------------------------------------------------------+
|
xiaoming | english=92.0,chinese=98.0,math=89.5 |
|
huahua | chinese=80.0,math=89.5 |
+----------+-------------------------------------------------------------------+
Returned 2 row(s) in 0.39s
[cdha:21000] >
Hive的行列轉換請查看： http://blog.csdn.net/jiangshouzhuang/article/details/46810529
著作權聲明：本文為博主原創文章，未經博主允許不得轉載。
