根据特定字符分行函数：https://www.alibabacloud.com/help/zh/maxcompute/user-guide/trans-array

关键函数：#TRANS_ARRAY；#SUBSTRING_INDEX

--构建示例数据集合

CREATE TABLE IF NOT EXISTS xinorange_sql_demo
LIFECYCLE 1 AS
SELECT  12345001 AS item_id
        ,18 AS search_pv
        ,'卡特莲:品牌;粉底膏:款式' AS seg_se_keyword
UNION ALL
SELECT  12345002 AS item_id
        ,20 AS search_pv
        ,'卡特莲:品牌;粉底膏:功能功效' AS seg_se_keyword
;

-- 实现方案
SELECT  item_id
        ,search_pv
        ,SUBSTRING_INDEX(seg_se_keyword,':',-1) AS word
        ,SUBSTRING_INDEX(seg_se_keyword,':',1) AS word_type
FROM    (
            SELECT  TRANS_ARRAY(2,';',item_id,search_pv,seg_se_keyword) AS (item_id,search_pv,seg_se_keyword)
            FROM    xinorange_sql_demo
            WHERE   seg_se_keyword IS NOT NULL
        )