MaxComputeで列を行に、または行を列に変換する - MaxCompute

データの開発および分析中に、さまざまなディメンションでデータを表示したり、テーブル形式の要件を満たすために、行を列に、または列を行に転置する必要がある場合があります。このトピックでは、MaxComputeでSQL文を使用して行を列に、列を行に転置する方法の例を示します。

背景情報

次の図は、行を列に、列を行に転置する実装を示しています。行转列与列转行

列への行
複数の行を1つの行に転置するか、1つの列を複数の列に転置します。
行への列
1つの行を複数の行に転置するか、複数の列を1つの列に転置します。

サンプルデータ

サンプルソースデータは、行を列に、または列を行に転置する例をよりよく理解するために提供されています。

rowtocolumnという名前のソーステーブルを作成し、ソーステーブルにデータを挿入します。テーブルは、行を列に転置するために使用されます。サンプル文:

CREATE TABLE rowtocolumn (name string, subject string, result bigint);
INSERT INTO TABLE rowtocolumn VALUES 
('Bob' , 'chinese' , 74),
('Bob' , 'mathematics' , 83),
('Bob' , 'physics' , 93),
('Alice' , 'chinese' , 74),
('Alice' , 'mathematics' , 84),
('Alice' , 'physics' , 94);

rowtocolumnテーブルからデータを照会します。例：

SELECT * FROM rowtocolumn;
-- The following result is returned: 
+------------+------------+------------+
| name       | subject    | result     |
+------------+------------+------------+
| Bob         | chinese          | 74         |
| Bob         | mathematics   | 83         |
| Bob         | physics           | 93         |
|Alice         | chinese          | 74         |
| Alice        | mathematics   | 84         |
| Alice        | physics           | 94         |
+------------+------------+------------+

columntorowという名前のソーステーブルを作成し、ソーステーブルにデータを挿入します。テーブルは、列を行に転置するために使用されます。サンプル文:

CREATE TABLE columntorow (name string, chinese bigint, mathematics bigint, physics bigint);
INSERT INTO TABLE columntorow VALUES 
('Bob' , 74, 83, 93),
('Alice', 74, 84, 94);

columntorowテーブルからデータを照会します。例：

SELECT * FROM columntorow;
-- The following result is returned: 
+------------+------------+-------------+------------+
| name       | chinese    | mathematics | physics    |
+------------+------------+-------------+------------+
| Bob   | 74       | 83       | 93        |
| Alice  | 74       | 84       | 94        |
| Bob   | 74       | 83       | 93        |
| Alice   | 74      | 84       | 94        |
+------------+------------+-------------+------------+

行を列に転置する例

次のいずれかの方法を使用して、行を列に転置できます。

方法1: CASE WHEN式を使用して、各サブジェクトの値を別々の列として抽出します。例：

SELECT name AS name
       max(case subject when 'chinese' then result end) AS chinese,
       max(case subject when 'mathematics' then result end) AS mathematics,
       max(case subject when 'physics' then result end) AS physics 
FROM rowtocolumn 
GROUP BY name;

次の応答が返されます。

+--------+------------+------------+------------+
| name   | chinese      | mathematics     | physics      |
+--------+------------+------------+------------+
| Bob       | 74               | 83                      | 93            |
| Alice      | 74               | 84                      | 94        |
+--------+------------+------------+------------+

方法2: 組み込み関数を使用して行を列に転置します。 CONCAT関数とWM_CONCAT関数を使用して、subject列と結果列の値を1つの列にマージします。次に、KEYVALUE関数を使用して、対象の列の値を個別の列として解析します。例：

SELECT name AS name,
       keyvalue(subject, chinese') AS chinese,
       keyvalue(subject, 'mathematics') AS mathematics,
       keyvalue(subject, 'physics') AS physics
FROM(
     SELECT name, wm_concat(';',concat(subject,':',result))as subject 
     FROM rowtocolumn
     GROUP BY name);

次の応答が返されます。

+--------+------------+------------+------------+
| name   | chinese      | mathematics     | physics      |
+--------+------------+------------+------------+
| Bob     | 74                 | 83                     | 93        |
| Alice    | 74                 | 84                     | 94        |
+--------+------------+------------+------------+

説明

実際のビジネス開発では、LATERAL VIEW句、EXPLODE関数、INLINE関数、またはTRANS_ARRAY関数を使用して、1つの行を複数の行に転置することもできます。

列を行に転置する例

次のいずれかの方法を使用して、列を行に転置できます。

方法1: UNION ALL句を使用して、中国語列、数学列、物理列の値を1つの列に結合します。サンプル文:

-- Remove the limit on the simultaneous execution of the ORDER BY and LIMIT clauses. This way, you can use ORDER BY to sort the results by name. 
SET odps.sql.validate.orderby.limit=false;
-- Transpose columns to rows. 
SELECT name AS name, subject AS subject, result AS result 
FROM(
     SELECT name, 'chinese' AS subject, chinese AS result FROM columntorow 
     UNION all 
     SELECT name, 'mathematics' AS subject, mathematics AS result FROM columntorow 
     UNION all 
     SELECT name, 'physics' AS subject, physics AS result FROM columntorow) 
ORDER BY name;

次の応答が返されます。

+--------+--------+------------+
| name   | subject   | result       |
+--------+--------+------------+
| Bob   | chinese   | 74         |
| Bob   | mathematics   | 83         |
| Bob   | physics   | 93         |
| Alice   | chinese   | 74         |
| Alice   | mathematics   | 84         |
| Alice   | physics   | 94         |
+--------+--------+------------+

方法2: 組み込み関数を使用して列を行に転置します。 CONCAT関数を使用して、各サブジェクトの列名と各列の値を連結します。次に、TRANS_ARRAY関数とSPLIT_PART関数を使用して、連結された値をサブジェクト列と結果列に分割します。例：
説明
入力パラメーターがnullの場合、CONCAT関数はnullを返します。したがって、転置するテーブルにnull値が含まれている場合、メソッド2は期待される結果を返しません。方法2でこの問題を解決するには、NVL関数を使用してnull値を0などの特殊な値に変換します。 NVL機能の詳細については、「NVL」をご参照ください。代わりに方法1を使用して、列を行に転置することもできます。
```
SELECT name AS name,
       split_part(subject,':',1) AS subject,
       split_part(subject,':',2) AS result
FROM(
       SELECT trans_array(1,';',name,subject) AS (name,subject) 
       FROM(
            SELECT name,
        concat('chinese',':',chinese,';','mathematics',':',mathematics,';','physics',':',physics) AS subject 
            FROM columntorow)tt)tx;
```
次の応答が返されます。
```
+--------+--------+------------+
| name   | subject   | result       |
+--------+--------+------------+
| Bob   | chinese   | 74         |
| Bob   | mathematics   | 83         |
| Bob   | physics   | 93         |
| Alice   | chinese   | 74         |
| Alice   | mathematics   | 84         |
| Alice   | physics   | 94         |
+--------+--------+------------+
```

MaxCompute:行を列に、または列を行に転置するためのベストプラクティス

背景情報

サンプルデータ

行を列に転置する例

列を行に転置する例

関連ドキュメント