MaxCompute列轉行、行轉列 - MaxCompute

在資料開發、分析的過程中，您可能需要在不同維度下展示資料或為了符合某些資料表格式要求，此時需要對資料行轉列或列轉行。本文以一個具體樣本為您介紹在MaxCompute中實現行轉列、列轉行的SQL。

背景資訊

行轉列與列轉行的示意圖如下。

行轉列
將多行資料轉換成一行顯示，或將一列資料轉換成多列顯示。
列轉行
將一行資料轉換成多行顯示，或將多列資料轉換成一列顯示。

樣本資料

為便於理解後續程式碼範例，本文為您提供來源資料，並基於來源資料提供相關轉換樣本。

建立用於實現行轉列的源表並插入資料，命令樣本如下。

CREATE TABLE rowtocolumn (name string, subject string, result bigint);
INSERT INTO TABLE rowtocolumn VALUES 
('張三' , '語文' , 74),
('張三' , '數學' , 83),
('張三' , '物理' , 93),
('李四' , '語文' , 74),
('李四' , '數學' , 84),
('李四' , '物理' , 94);

查詢表rowtocolumn中的資料，命令樣本如下：

SELECT * FROM rowtocolumn;
--返回結果。
+------------+------------+------------+
| name       | subject    | result     |
+------------+------------+------------+
| 張三        | 語文        | 74         |
| 張三        | 數學        | 83         |
| 張三        | 物理        | 93         |
| 李四        | 語文        | 74         |
| 李四        | 數學        | 84         |
| 李四        | 物理        | 94         |
+------------+------------+------------+

建立用於實現列轉行的源表並插入資料，命令樣本如下。

CREATE TABLE columntorow (name string, chinese bigint, mathematics bigint, physics bigint);
INSERT INTO TABLE columntorow VALUES 
('張三' , 74, 83, 93),
('李四' , 74, 84, 94);

查詢表columntorow中的資料，命令樣本如下：

SELECT * FROM columntorow;
--返回結果。
+------------+------------+-------------+------------+
| name       | chinese    | mathematics | physics    |
+------------+------------+-------------+------------+
| 張三        | 74         | 83          | 93         |
| 李四        | 74         | 84          | 94         |
| 張三        | 74         | 83          | 93         |
| 李四        | 74         | 84          | 94         |
+------------+------------+-------------+------------+

行轉列樣本

您可以通過如下兩種方法實現行轉列：

方法一：使用case when運算式，靈活提取各科目（subject）的值作為單獨的列，命令樣本如下。

SELECT name AS 姓名,
       max(case subject when '語文' then result end) AS 語文,
       max(case subject when '數學' then result end) AS 數學,
       max(case subject when '物理' then result end) AS 物理 
FROM rowtocolumn 
GROUP BY name;

返回結果如下。

+--------+------------+------------+------------+
| 姓名   | 語文      | 數學     | 物理      |
+--------+------------+------------+------------+
| 張三   | 74       | 83       | 93        |
| 李四   | 74       | 84       | 94        |
+--------+------------+------------+------------+

方法二：藉助MaxCompute提供的內建函數實現，先基於CONCAT和WM_CONCAT函數合并科目和成績為一列，然後通過KEYVALUE函數解析科目（subject）的值作為單獨的列。命令樣本如下。

SELECT name AS 姓名,
       keyvalue(subject, '語文') AS 語文,
       keyvalue(subject, '數學') AS 數學,
       keyvalue(subject, '物理') AS 物理
FROM(
     SELECT name, wm_concat(';',concat(subject,':',result))as subject 
     FROM rowtocolumn
     GROUP BY name);

返回結果如下。

+--------+------------+------------+------------+
| 姓名   | 語文      | 數學      | 物理      |
+--------+------------+------------+------------+
| 張三   | 74       | 83        | 93        |
| 李四   | 74       | 84        | 94        |
+--------+------------+------------+------------+

說明

在實際業務開發過程中，如果您遇到行轉列需求，還可以通過Lateral View、EXPLODE函數、INLINE函數以及TRANS_ARRAY函數將單行資料轉為多行資料。

列轉行樣本

您可以通過如下兩種方法實現列轉行：

方法一：使用union all，將各科目（chinese、mathematics、physics）整合為一列，命令樣本如下。

--解除order by必須帶limit的限制，方便列轉行SQL命令對結果按照姓名排序。
SET odps.sql.validate.orderby.limit=false;
--列轉行SQL。
SELECT name AS 姓名, subject AS 科目, result AS 成績 
FROM(
     SELECT name, '語文' AS subject, chinese AS result FROM columntorow 
     UNION all 
     SELECT name, '數學' AS subject, mathematics AS result FROM columntorow 
     UNION all 
     SELECT name, '物理' AS subject, physics AS result FROM columntorow) 
ORDER BY name;

返回結果如下。

+--------+--------+------------+
| 姓名   | 科目   | 成績       |
+--------+--------+------------+
| 張三   | 語文   | 74         |
| 張三   | 數學   | 83         |
| 張三   | 物理   | 93         |
| 李四   | 語文   | 74         |
| 李四   | 數學   | 84         |
| 李四   | 物理   | 94         |
+--------+--------+------------+

方法二：藉助MaxCompute提供的內建函數實現，先基於CONCAT函數拼接各科目和成績，然後基於TRANS_ARRAY和SPLIT_PART函數逐層拆解科目和成績作為單獨的列。命令樣本如下。

說明

當您的待轉換資料包含有NULL值時，使用此方式轉換結果會不符合預期，因為CONCAT函數任一參數為NULL，返回結果就為NUL。您可以在使用方法二前先使用NVL函數，將NULL值轉換為其他特殊值（例如0），NVL函數詳情請參見NVL；或使用上述方法一進行轉換。

SELECT name AS 姓名,
       split_part(subject,':',1) AS 科目,
       split_part(subject,':',2) AS 成績
FROM(
       SELECT trans_array(1,';',name,subject) AS (name,subject) 
       FROM(
            SELECT name,
        concat('語文',':',chinese,';','數學',':',mathematics,';','物理',':',physics) AS subject 
            FROM columntorow)tt)tx;

返回結果如下。

+--------+--------+------------+
| 姓名   | 科目   | 成績       |
+--------+--------+------------+
| 張三   | 語文   | 74         |
| 張三   | 數學   | 83         |
| 張三   | 物理   | 93         |
| 李四   | 語文   | 74         |
| 李四   | 數學   | 84         |
| 李四   | 物理   | 94         |
+--------+--------+------------+

MaxCompute：行轉列及列轉行最佳實務

背景資訊

樣本資料

行轉列樣本

列轉行樣本

相關文檔