sql行列转换

列转行

一列转多行

核心函数：explode(数组类型)
搭配：一般和split('a,b',',')结合使用，因为我们数据库中一般保存的是String类型，需要转化为数组类型

SELECT explode(split('外语，数学，语文',','));

但是 explode 有一个缺点，他只能包含 explode 的字段

SELECT explode(split(学科,',')),name FROM t_demo;

当我包含 name 字段时，他会报错

only a single expression in the SELECT clause is supported with UDTF's
UDTF只支持SELECT子句中的单个表达式

此时可以用LATERAL VIEW

LATERAL VIEW扩展

SELECT  name
        ,单个学科
FROM    (
            SELECT  '小明' AS name
                    ,'外语,数学,语文' AS 学科
        ) t
LATERAL VIEW explode(split(学科,',')) t AS 单个学科
;

有些数据库是unnest函数，corss join unnest
corss join ：交叉连接
unnest:把数组拆分成多行 as date_column

行转列

多行拼接一列

变成

SELECT  name
        ,concat_ws(',',collect_set(学科))
FROM    (
            SELECT  '小明' AS name
                    ,'外语' AS 学科
                    ,88 AS score
            UNION
            SELECT  '小明' AS name
                    ,'数学' AS 学科
                    ,98 AS score
            UNION
            SELECT  '小红' AS name
                    ,'语文' AS 学科
                    ,88 AS score
            UNION
            SELECT  '小红' AS name
                    ,'数学' AS 学科
                    ,100 AS score
        ) t
GROUP BY name
;

多行转多列

有一个前提，不会出现小明有两条数学的数据，如果有的话会按照第一个出现的数据的值
变成

方法1：

SELECT  name
        ,coalesce(keyvalue(new_score,',','|','数学'),0) AS  数学
        ,coalesce(keyvalue(new_score,',','|','外语'),0) AS  外语
        ,coalesce(keyvalue(new_score,',','|','语文'),0) AS  语文
FROM    (
            SELECT  name
                    ,concat_ws( ',' ,collect_set(concat_ws('|',学科,score)) ) AS new_score
            FROM    (
                        SELECT  '小明' AS name
                                ,'外语' AS 学科
                                ,88 AS score
                        UNION
                        SELECT  '小明' AS name
                                ,'数学' AS 学科
                                ,98 AS score
                        UNION
                        SELECT  '小红' AS name
                                ,'语文' AS 学科
                                ,88 AS score
                        UNION
                        SELECT  '小红' AS name
                                ,'数学' AS 学科
                                ,100 AS score
                    ) t
            GROUP BY name
        ) 
;

补充 keyvalue()：

SELECT keyvalue('外语|88,数学|98',',','|','数学');
得到98

方法2：
用 join 的方法，但是一个是这个写法很笨，列多了你 join 不过来，另外join很消耗性能

SELECT  t0.name
        ,COALESCE(t1.score,0) AS 外语
        ,COALESCE(t2.score,0) AS 数学
        ,COALESCE(t3.score,0) AS 语文
FROM    (
            SELECT  name
            FROM    t_demo
            GROUP BY name
        ) t0
LEFT JOIN (
              SELECT  name
                      ,score
              FROM    t_demo
              WHERE   学科 = '外语'
          ) t1
ON      t0.name = t1.name LEFT
JOIN    (
            SELECT  name
                    ,score
            FROM    t_demo
            WHERE   学科 = '数学'
        ) t2
ON      t0.name = t2.name
LEFT JOIN (
              SELECT  name
                      ,score
              FROM    t_demo
              WHERE   学科 = '语文'
          ) t3
ON      t0.name = t3.name
;

方法3：
这种方法最简单，直接用json_tuple 转为tuple元祖即可