例1:
select explode(map(1, 'Tom', 2, 'Mary', 3, 'Mary'));
傳回:
key value
1 Tom
2 Mary
3 Mary
例2:
行轉列
select recommendid,collect_list(status) status from ods.ods_aen_erp_comments where recommendid =686871 group by recommendid
注意,status這時是一個Array
列轉行
這裡使用explode函數看下效果:
select explode(status) from (
select recommendid,collect_list(status) status from os.os_aen_erp_comments where recommendid =686871 group by recommendid ) t
提示:(應是列轉行)
差別:為什麼要用lateral view(構造一個側寫的表)?為啥說生成一張虛拟表?
用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解釋:用于和split, explode等UDTF一起使用,它能夠将一列資料拆成多行資料,在此基礎上可以對拆分後的資料進行聚合。
先看如下兩張截圖,單獨使用explode的話别的字段是取不到的:
lateral view用于和split, explode等UDTF一起使用,它能夠将一行資料拆成多行資料,在此基礎上可以對拆分後的資料進行聚合。lateral view首先為原始表的每行調用UDTF,UTDF會把一行拆分成一或者多行,lateral view再把結果組合,産生一個支援别名表的虛拟表。
參考:
https://blog.csdn.net/clerk0324/article/details/58600284
或
https://github.com/AlibabaCloudDocs/odps/blob/master/intl.zh-CN/使用者指南/SQL/Lateral%20View.md?spm=a2c63.p38356.879954.6.3fc66db1j1wdCL&file=Lateral%20View.md
注意:後可以增加多個 lateral view(前提是你多個字段需要行轉列)
例3:
a表資料格式:
CA100681473 320401
CA522590 170102,170301
CA883141 1101
CA101184716 170302,170103
CA101003587 1101
CA101496678 320403
CA101011976 100101,100104,100103
CA101475480 100106
CA104331761 120101,150401,160108
先看下我們将要使用的兩個函數split和explode:
隻加split函數:
select split(expect_job_category,',') from (select distinct(resume_id) resume_id,expect_job_category from resume.ods__t_cv_job_intention) f
加上explod函數效果:
select explode(split(expect_job_category,',')) from (select distinct(resume_id) resume_id,expect_job_category from resume.ods_t_cv_job_intention) f
接下來進行 行轉列:
select resume_id,job_category from a lateral view explode(split(expect_job_category,',')) tmp as job_category
轉後效果: