Implementasikan kemampuan yang disediakan oleh fungsi GROUP_CONCAT - MaxCompute

GROUP_CONCAT adalah fungsi agregat yang disediakan oleh MySQL. Fungsi ini digunakan untuk menggabungkan nilai kolom dari beberapa baris dalam setiap grup yang dihasilkan menggunakan GROUP BY menjadi string independen. Anda dapat menggunakan fungsi ini ketika ingin menggabungkan beberapa catatan data menjadi satu catatan data. Di MaxCompute, Anda dapat menggunakan fungsi WM_CONCAT untuk mencapai efek serupa dengan GROUP_CONCAT.

Contoh

Sebuah tabel bernama price_total berisi kolom-kolom berikut: name, price, dan saleid. Tabel tersebut memiliki data sebagai berikut:

+--------+------------+------------+
| name   | price      | saleid     |
+--------+------------+------------+
| bag    | 50         | 1          |
| sugar  | 20         | 3          |
| noodle | 2          | 4          |
| potato | 5          | 6          |
| bag    | 100        | 2          |
| sugar  | 10         | 4          |
| potato | 4          | 3          |
| sugar  | 50         | 7          |
| noodle | 2          | 5          |
| noodle | 5          | 1          |
+--------+------------+------------+

Dalam contoh ini, semua produk dikelompokkan berdasarkan nama produk, yaitu kolom name dalam tabel. Anda dapat menggunakan fungsi GROUP_CONCAT di MySQL untuk memenuhi persyaratan berikut:

Persyaratan 1: Gabungkan nilai-nilai dalam kolom price dari grup yang sama. Hasil yang dikembalikan mencakup nilai duplikat. Contoh pernyataan:
```
SELECT name, group_concat(price) FROM price_total GROUP BY name;
```
Persyaratan 2: Gabungkan nilai-nilai dalam kolom price dari grup yang sama. Hasil yang dikembalikan tidak mencakup nilai duplikat. Contoh pernyataan:
```
SELECT name, group_concat(distinct price) FROM price_total GROUP BY name;
```
Persyaratan 3: Gabungkan dan urutkan nilai-nilai dalam kolom price dari grup yang sama. Hasil yang dikembalikan mencakup nilai duplikat. Contoh pernyataan:
```
SELECT name, group_concat(price ORDER BY price desc) FROM price_total GROUP BY name;
```
Persyaratan 4: Gabungkan dan urutkan nilai-nilai dalam kolom price dari grup yang sama. Hasil yang dikembalikan tidak mencakup nilai duplikat. Contoh pernyataan:
```
SELECT name, group_concat(distinct price ORDER BY price desc) FROM price_total GROUP BY name;
```
Persyaratan 5: Gabungkan nilai-nilai dalam kolom price dan nilai-nilai dalam kolom saleid dari grup yang sama. Contoh pernyataan:
```
SELECT name, group_concat(concat_ws(':', price, saleid)) FROM price_total GROUP BY name;
```

Solusi

MaxCompute tidak mendukung fungsi GROUP_CONCAT. Namun, Anda dapat menggunakan fungsi WM_CONCAT untuk memenuhi persyaratan dalam contoh ini.

Perlu dicatat bahwa fungsi WM_CONCAT tidak sepenuhnya setara dengan fungsi GROUP_CONCAT. Tabel berikut menjelaskan kemampuan masing-masing fungsi tersebut.

Kemampuan	WM_CONCAT	GROUP_CONCAT	Deskripsi
Menghapus duplikat dengan menggunakan DISTINCT			Tidak ada.
Pemisah			Pemisah dapat digunakan untuk menggabungkan nilai-nilai yang ingin Anda gabungkan. Perhatikan poin-poin berikut saat menyertakan pemisah dalam fungsi WM_CONCAT dan GROUP_CONCAT: Pemisah tidak dapat dihilangkan saat menggunakan fungsi WM_CONCAT. Pemisah dapat dihilangkan saat menggunakan fungsi GROUP_CONCAT. Jika pemisah dihilangkan, koma (,) digunakan sebagai pemisah.
Pengurutan			Jika Anda ingin mengurutkan nilai, gunakan metode berikut: Urutkan bidang yang ingin Anda gabungkan lalu gabungkan bidang tersebut. Gunakan fungsi yang ditentukan pengguna (UDF). Untuk informasi lebih lanjut tentang UDF, lihat MaxCompute UDF.
Penggabungan beberapa kolom			Fungsi WM_CONCAT dan GROUP_CONCAT adalah fungsi yang digunakan untuk menggabungkan baris. Kedua fungsi tersebut dapat digunakan bersama dengan fungsi CONCAT atau fungsi CONCAT_WS yang digunakan untuk menggabungkan kolom untuk menggabungkan nilai dari beberapa kolom dalam grup yang berbeda.

Berdasarkan kemampuan yang dijelaskan dalam tabel sebelumnya, Anda dapat menggunakan fungsi WM_CONCAT untuk memenuhi persyaratan berikut:

Persyaratan 1: Gabungkan nilai-nilai dalam kolom price dari grup yang sama. Hasil yang dikembalikan mencakup nilai duplikat.

SELECT name, wm_concat(',', price) AS price_new FROM price_total GROUP BY name;

Hasil berikut dikembalikan:

+--------+-----------+
| name   | price_new |
+--------+-----------+
| bag    | 50,100    |
| noodle | 2,2,5     |
| potato | 5,4       |
| sugar  | 20,10,50  |
+--------+-----------+

Persyaratan 2: Gabungkan nilai-nilai dalam kolom price dari grup yang sama. Hasil yang dikembalikan tidak mencakup nilai duplikat.

SELECT name, wm_concat(distinct ',', price) AS price_new FROM price_total GROUP BY name;

Hasil berikut dikembalikan:

+--------+-----------+
| name   | price_new |
+--------+-----------+
| bag    | 100,50    |
| noodle | 2,5       |
| potato | 4,5       |
| sugar  | 10,20,50  |
+--------+-----------+

Persyaratan 3: Gabungkan dan urutkan nilai-nilai dalam kolom price dari grup yang sama. Hasil yang dikembalikan mencakup nilai duplikat.

Metode 1:

SELECT name, wm_concat(',', price) WITHIN GROUP (ORDER BY price desc) AS price_new 
FROM (SELECT name, price FROM price_total) GROUP BY name;

Metode 2:

SELECT name, wm_concat(',', price) AS price_new 
FROM (SELECT name, price FROM price_total ORDER BY name, price desc LIMIT 100) GROUP BY name;

Hasil berikut dikembalikan:

+--------+-----------+
| name   | price_new |
+--------+-----------+
| bag    | 100,50    |
| noodle | 5,2,2     |
| potato | 5,4       |
| sugar  | 50,20,10  |
+--------+-----------+

Persyaratan 4: Gabungkan dan urutkan nilai-nilai dalam kolom price dari grup yang sama. Hasil yang dikembalikan tidak mencakup nilai duplikat.

Metode 1:

SELECT name, wm_concat(',', price)  within GROUP (ORDER BY price asc)
FROM (SELECT DISTINCT name, price FROM price_total) GROUP BY name;

Metode 2:

SELECT name, wm_concat(',', price) AS price_new 
FROM (SELECT DISTINCT name, price FROM price_total ORDER BY name, price asc LIMIT 100) GROUP BY name;

Hasil berikut dikembalikan:

+--------+-----------+
| name   | price_new |
+--------+-----------+
| bag    | 50,100    |
| noodle | 2,5       |
| potato | 4,5       |
| sugar  | 10,20,50  |
+--------+-----------+

Persyaratan 5: Gabungkan nilai-nilai dalam kolom price dan nilai-nilai dalam kolom saleid dari grup yang sama.

SELECT name, wm_concat(',', concat_ws(':',price,saleid)) AS price_new FROM price_total GROUP BY name;
-- Pernyataan di atas setara dengan pernyataan berikut:
SELECT name, wm_concat(',', concat(price,':',saleid)) FROM price_total GROUP BY name;

Hasil berikut dikembalikan:

+--------+-----------------+
| name   | price_new       |
+--------+-----------------+
| bag    | 50:1,100:2      |
| noodle | 2:4,2:5,5:1     |
| potato | 5:6,4:3         |
| sugar  | 20:3,10:4,50:7  |
+--------+-----------------+

Referensi

Untuk informasi lebih lanjut tentang cara mengimplementasikan fungsi bawaan Hive, MySQL, dan Oracle di MaxCompute, lihat Pemetaan antara Fungsi Bawaan MaxCompute dan Fungsi Bawaan Hive, MySQL, dan Oracle.
Jika fungsi bawaan yang disediakan oleh MaxCompute tidak memenuhi kebutuhan bisnis Anda, Anda dapat mengembangkan UDF. Untuk informasi lebih lanjut tentang UDF, lihat Ikhtisar.