使用sql程式碼作分析的時候，幾次遇到需要將長格式資料轉換成寬格式資料，一般使用left join或者case when實現，程式碼看起來冗長，探索一下，可以使用更簡單的方式實現長格式資料轉換成寬格式資料。

長寬格式資料

舉個栗子

640?wx_fmt=png

寬格式資料：每個變數單獨成一列為寬格式資料，例如變數name、age等。

長格式資料：長資料中變數的ID沒有單獨列成一列，而是整合在同一列。

需求描述

某電商資料庫中存在一張客戶資訊表user_info，記錄著客戶屬性資料和消費資料，需要將左邊長格式資料轉化成右邊寬格式資料。

640?wx_fmt=png

需求實現

做以下說明

640?wx_fmt=png

當然，其他資料庫語句可以同等替換上面的函式。

需求實現思路

步驟一：將客戶資訊轉化成map格式的資料

u001 {"age":"25","education":"master","first_buytime":"2018/1/3","name":"Sulie","regtime":"2018/1/2","sex":"male"}

u002 {"age":"30","education":"Bachelor","first_buytime":"2018/5/5","name":"LuBan","regtime":"2018/3/4","sex":"male"}

u003 {"age":"27","education":"PhD","first_buytime":"2018/5/4","name":"ZhenJi","regtime":"2018/4/3","sex":"female"}

步驟二：將map格式資料中的key與value提取出來，key就是每一列變數名，value就是變數值

user_no name sex age education regtime first_buytime

u001 Sulie male 25 master 2018/1/2 2018/1/3

u002 LuBan male 30 Bachelor 2018/3/4 2018/5/5

u003 ZhenJi female 27 PhD 2018/4/3 2018/5/4

需求實現程式碼

步驟一實現

select

user_no,

str_to_map(concat_ws(",",collect_set(concat_ws(":", message, detail)))) message1

from user_info

group by user_no

order by user_no

collect_set形成的集合是無序的，若想得到有序集合，可以使用sort_array對集合元素進行排序。

select

user_no,

str_to_map(concat_ws(",",sort_array(collect_set(concat_ws(":", message, detail))))) message1

from user_info

group by user_no

order by user_no

上面程式碼用到了字串連線函式、集合函式、排序函式以及字串轉化成map格式函式，可以每一步執行出來檢視結果更容易理解。

步驟二實現

select

user_no,

message1["name"] name,

message1["sex"] sex,

message1["age"] age,

message1["education"] education,

message1["regtime"] regtime,

message1["first_buytime"] first_buytime

from

(select

user_no,

str_to_map(concat_ws(",",collect_set(concat_ws(":", message, detail)))) message1

from user_info

group by user_no

order by user_no

) a

上面程式碼就是提取map格式資料中的key與value，即列名["key"]得到value值。