MaxCompute SQL使用小技巧之行列轉換

行列轉換根據具體業務需求有跟多方式，這裡介紹下個人的想法，話不多說，上示例

案例行轉列：有一張成績表(如下)

name	subject	score
兮辰	國文	85
	數學	92
	英語	98
	體育	91
無盡		90
		89
		93
		86
...

展示如下：

	Chi	Math	Eng	P.E

首先先生成示例資料

with tb as (
    select 
        name,
        subject,
        score
    from values('兮辰','國文',85),
               ('兮辰','數學',92),
               ('兮辰','英語',98),
               ('兮辰','體育',91),
               ('無盡','國文',90),
               ('無盡','數學',89),
               ('無盡','英語',93),
               ('無盡','體育',86)
               t(name,subject,score)
)      

方式1：使用case when配合聚合函數max

select 
    name,
    max(case when subject = '國文' then score end) as Chi,
    max(case when subject = '數學' then score end) as Math,
    max(case when subject = '英語' then score end) as Eng,
    max(case when subject = '體育' then score end) as PE
from tb
group by name;
--結果如下：
name chi math eng   pe
兮辰  85  92  98  91
無盡  90  89  93  86      

方式2：使用collect_list函數(根據需求不同，也可以使用collect_list+array_contains組合方式)

--該方式也有弊端，必須保障原表各科目順序是一緻的，否則從數組裡拿出來的成績将不對應
select 
    name,
    score_list[0] as Chi,
    score_list[1] as Math,
    score_list[2] as Eng,
    score_list[3] as PE
from (
    select 
        name,
        collect_list(score) as score_list
    from tb 
    group by name
)tmp;
--結果如下：
name    chi math    eng pe
兮辰  85  92  98  91
無盡  90  89  93  86      

方式3：使用keyvalue函數，詳細使用方法見阿裡雲文檔

https://help.aliyun.com/document_detail/48973.html?spm=a2c4g.11186623.6.833.2dd76fd0jGM2C7#section-lnq-tyz-vdb

--将字元串'1:a;2:b'拆分為Key-Value對，傳回其中key為1的value值
select keyvalue('1:a;2:b',1);--a      

select 
    name,
    keyvalue(subject,'國文') as Chi,
    keyvalue(subject,'數學') as Math,
    keyvalue(subject,'英語') as Eng,
    keyvalue(subject,'體育') as PE
from (
    select 
        name,
        wm_concat(';',concat(subject,':',score)) as subject
    from ta
    group by name
)tmp
--結果如下：
name    chi math    eng pe
兮辰  85  92  98  91
無盡  90  89  93  86      

案例列轉行：有一張成績表(如下)

首先生成示例資料

with tb as (
    select 
        name,
        Chi,
        Math,
        Eng,
        PE
    from values ('兮辰',85,92,98,91),
                ('無盡',90,89,93,86) 
                t(name,Chi,Math,Eng,PE)
)      

方式1：使用union all，較為常用

select 
    name,
    subject,
    score
from (
    select name,'國文' as subject,Chi as score from tb
    union all 
    select name,'數學' as subject,Math as score from tb
    union all 
    select name,'英語' as subject,Eng as score from tb
    union all 
    select name,'體育' as subject,PE as score from tb
);
--結果如下：
name subject    score
兮辰  國文  85
無盡  國文  90
兮辰  數學  92
無盡  數學  89
兮辰  英語  98
無盡  英語  93
兮辰  體育  91
無盡  體育  86      

方式2：map函數+explode展開

select
    name,
    subject,
    score
from (
        select 
            name,
            map(
                '國文',Chi,
                '數學',Math,
                '英語',Eng,
                '體育',PE
            ) as kv
        from tb
    ) tmp 
lateral view explode(kv) t as subject,score;
--結果如下：
name subject    score
兮辰  體育  91
兮辰  數學  92
兮辰  英語  98
兮辰  國文  85
無盡  體育  86
無盡  數學  89
無盡  英語  93
無盡  國文  90      

方式3：使用trans_array函數：将一行資料轉為多行的UDTF，将列中存儲的以固定分隔符格式分隔的數組轉為多行。具體使用方法見阿裡雲文檔

https://help.aliyun.com/document_detail/48976.html?spm=a2c4g.11186623.6.834.7b052785nBNKFP#section-vxw-9dg-ypz

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('國文',':',Chi,';','數學',':',Math,';','英語',':',Eng,';','體育',':',PE) as subject
        from tb
    )tmp1
)tmp2;
--結果如下：
name    subject result
兮辰  國文  85
兮辰  數學  92
兮辰  英語  98
兮辰  體育  91
無盡  國文  90
無盡  數學  89
無盡  英語  93
無盡  體育  86      

上面的示例隻是提供了一種思路，可能與具體的業務需求不同，有不同想法的歡迎交流。

拜了個拜