MaxCompute 行轉列 列轉行

發現MaxCompute相關的帖子真不多，補充發佈一個。實際上，實現行轉列和列轉行的方法，根據實際需求應該還有很多種方法。MaxCompute其實也提供了很多的函數來幫助大家實現，有機會可以多探索幾種方式。
一、行轉列

with ta as (
select * from values
 ('張三' , '語文' , 74)
,('張三' , '數學' , 83)
,('張三' , '物理' , 93)
,('李四' , '語文' , 74)
,('李四' , '數學' , 84)
,('李四' , '物理' , 94)
t(name , subject , result))

--方法一：使用case when end結構,通用寫法
select name as 姓名
,max(case subject when '語文' then result end) as 語文
,max(case subject when '數學' then result end) as 數學
,max(case subject when '物理' then result end) as 物理
  from ta
 group by name
;
+--------+------------+------------+------------+
| 姓名 | 語文     | 數學     | 物理     |
+--------+------------+------------+------------+
| 張三 | 74         | 83         | 93         |
| 李四 | 74         | 84         | 94         |
+--------+------------+------------+------------+
--方法二：非要多寫一步的寫法
with ta as (
select * from values
 ('張三' , '語文' , 74)
,('張三' , '數學' , 83)
,('張三' , '物理' , 93)
,('李四' , '語文' , 74)
,('李四' , '數學' , 84)
,('李四' , '物理' , 94)
t(name , subject , result))
select name
      ,keyvalue(subject,'語文') as 語文
      ,keyvalue(subject,'數學') as 數學
      ,keyvalue(subject,'物理') as 物理
  from(
select name,wm_concat(';',concat(subject,':',result))as subject
  from ta
 group by name)tt
;

這個方法用wm_concat做聚合之前，先用concat把多列合併為一列。然後在拼接後用keyvalue去解析出來。
二、列轉行

with tb as (
select * from values
 ('張三', 88,99,89)
,('李四', 78,77,87)
t(name , subject_yw, subject_sx, subject_wl))
--方法一：使用union all,通用寫法
select name,subject,result
  from(
select name,'語文' as subject,subject_yw as result from tb
 union all
select name,'數學' as subject,subject_sx as result from tb
 union all
select name,'物理' as subject,subject_wl as result from tb)tt;
+------+---------+--------+
| name | subject | result |
+------+---------+--------+
| 張三 | 語文  | 88     |
| 張三 | 數學  | 99     |
| 張三 | 物理  | 89     |
| 李四 | 語文  | 78     |
| 李四 | 數學  | 77     |
| 李四 | 物理  | 87     |
+------+---------+--------+
with tb as (
select * from values
 ('張三', 88,99,89)
,('李四', 78,77,87)
t(name , subject_yw, subject_sx, subject_wl))
--方法二：
select name,split_part(subject,':',1) as subject
           ,split_part(subject,':',2) as result
  from(
select trans_array(1,';',name,subject) as (name,subject) 
  from(
select name
,concat('語文',':',subject_yw
   ,';','數學',':',subject_sx
   ,';','物理',':',subject_wl) as subject
  from tb)tt)tx;

這個例子，為了與通用方式產出一致。用到了 trans_array() 函數,split_part()函數，為了構造結構，還用concat拼接了字符串。

上面這兩個針對maxcompute的例子，實際上看上去並不會比通用方法效率高，只是提供了一種思路。大家實際使用中有類似的結構，可以參考借鑑。