Mengubah kolom menjadi baris atau baris menjadi kolom di MaxCompute - MaxCompute

Selama pengembangan data dan analitik, Anda mungkin perlu mengubah baris menjadi kolom atau kolom menjadi baris untuk menampilkan data dalam dimensi yang berbeda atau memenuhi persyaratan format tabel. Topik ini memberikan contoh cara menggunakan pernyataan SQL untuk mengubah baris menjadi kolom dan kolom menjadi baris di MaxCompute.

Informasi latar belakang

Gambar berikut menunjukkan implementasi dari mengubah baris menjadi kolom dan kolom menjadi baris. 行转列与列转行

Baris ke Kolom
Mengubah beberapa baris menjadi satu baris, atau mengubah satu kolom menjadi beberapa kolom.
Kolom ke Baris
Mengubah satu baris menjadi beberapa baris, atau mengubah beberapa kolom menjadi satu kolom.

Data sampel

Data sampel sumber disediakan untuk membantu Anda lebih memahami contoh-contoh mengubah baris menjadi kolom atau kolom menjadi baris.

Buat tabel sumber bernama rowtocolumn dan masukkan data ke dalam tabel tersebut. Tabel ini digunakan untuk mengubah baris menjadi kolom. Contoh pernyataan:

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);

Query data dari tabel rowtocolumn. Contoh pernyataan:

SELECT * FROM rowtocolumn;
-- Hasil berikut dikembalikan: 
+------------+------------+------------+
| name       | subject    | result     |
+------------+------------+------------+
| Bob         | chinese          | 74         |
| Bob         | mathematics   | 83         |
| Bob         | physics           | 93         |
|Alice         | chinese          | 74         |
| Alice        | mathematics   | 84         |
| Alice        | physics           | 94         |
+------------+------------+------------+

Buat tabel sumber bernama columntorow dan masukkan data ke dalam tabel tersebut. Tabel ini digunakan untuk mengubah kolom menjadi baris. Contoh pernyataan:

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

Query data dari tabel columntorow. Contoh pernyataan:

SELECT * FROM columntorow;
-- Hasil berikut dikembalikan: 
+------------+------------+-------------+------------+
| name       | chinese    | mathematics | physics    |
+------------+------------+-------------+------------+
| Bob   | 74       | 83       | 93        |
| Alice  | 74       | 84       | 94        |
| Bob   | 74       | 83       | 93        |
| Alice   | 74      | 84       | 94        |
+------------+------------+-------------+------------+

Contoh mengubah baris menjadi kolom

Anda dapat menggunakan salah satu metode berikut untuk mengubah baris menjadi kolom:

Metode 1: Gunakan ekspresi CASE WHEN untuk mengekstrak nilai setiap mata pelajaran sebagai kolom terpisah. Contoh pernyataan:

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;

Hasil berikut dikembalikan:

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

Metode 2: Gunakan fungsi bawaan untuk mengubah baris menjadi kolom. Gabungkan nilai kolom subject dan result menjadi satu kolom dengan menggunakan fungsi CONCAT dan WM_CONCAT. Kemudian, pisahkan nilai kolom subject sebagai kolom terpisah dengan menggunakan fungsi KEYVALUE. Contoh pernyataan:

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);

Hasil berikut dikembalikan:

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

Catatan

Dalam pengembangan bisnis sebenarnya, Anda juga dapat menggunakan klausa LATERAL VIEW, fungsi EXPLODE, fungsi INLINE, atau fungsi TRANS_ARRAY untuk mengubah satu baris menjadi beberapa baris.

Contoh mengubah kolom menjadi baris

Anda dapat menggunakan salah satu metode berikut untuk mengubah kolom menjadi baris:

Metode 1: Gunakan klausa UNION ALL untuk menggabungkan nilai dalam kolom chinese, mathematics, dan physics menjadi satu kolom. Contoh pernyataan:

-- Hapus batasan pada eksekusi simultan klausa ORDER BY dan LIMIT. Dengan cara ini, Anda dapat menggunakan ORDER BY untuk mengurutkan hasil berdasarkan nama.
SET odps.sql.validate.orderby.limit=false;
-- Mengubah kolom menjadi baris.
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;

Hasil berikut dikembalikan:

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

Metode 2: Gunakan fungsi bawaan untuk mengubah kolom menjadi baris. Gabungkan nama kolom setiap mata pelajaran dan nilai di setiap kolom dengan menggunakan fungsi CONCAT. Kemudian, pisahkan nilai gabungan menjadi kolom subject dan result sebagai kolom terpisah dengan menggunakan fungsi TRANS_ARRAY dan SPLIT_PART. Contoh pernyataan:
Catatan
Fungsi CONCAT mengembalikan null jika parameter input adalah null. Oleh karena itu, jika tabel yang ingin Anda ubah berisi nilai null, Metode 2 tidak akan mengembalikan hasil yang diharapkan. Untuk menyelesaikan masalah ini di Metode 2, Anda dapat menggunakan fungsi NVL untuk mengonversi nilai null menjadi nilai khusus, seperti 0. Untuk informasi lebih lanjut tentang fungsi NVL, lihat NVL. Anda juga dapat menggunakan Metode 1 sebagai gantinya untuk mengubah kolom menjadi baris.
```
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;
```
Hasil berikut dikembalikan:
```
+--------+--------+------------+
| name   | subject   | result       |
+--------+--------+------------+
| Bob   | chinese   | 74         |
| Bob   | mathematics   | 83         |
| Bob   | physics   | 93         |
| Alice   | chinese   | 74         |
| Alice   | mathematics   | 84         |
| Alice   | physics   | 94         |
+--------+--------+------------+
```

Referensi

Anda juga dapat menggunakan kata kunci PIVOT untuk mengubah baris menjadi kolom dan menggunakan kata kunci UNPIVOT untuk mengubah kolom menjadi baris. Untuk informasi lebih lanjut, lihat PIVOT and UNPIVOT.