MaxCompute:TRANS_ARRAY

Batasan Penggunaan

• Semua kolom yang digunakan sebagai key harus ditempatkan di awal, sedangkan kolom yang akan ditransposisi harus ditempatkan setelahnya.

• Hanya boleh terdapat satu UDTF dalam satu pernyataan select, dan tidak boleh ada kolom tambahan lainnya.

Format Perintah

trans_array (<num_keys>, <separator>, <key1>,<key2>,…,<col1>,<col2>,<col3>) as (<key1>,<key2>,...,<col1>, <col2>)

Penjelasan Parameter

• num_keys: Wajib diisi. Konstanta bertipe BIGINT dengan nilai >=0. Menunjukkan jumlah kolom yang berfungsi sebagai key saat data dikonversi menjadi beberapa baris.

• separator: Wajib diisi. Konstanta bertipe STRING yang digunakan sebagai delimiter untuk memisahkan string menjadi beberapa elemen. Jika kosong, sistem akan mengembalikan error.

• keys: Wajib diisi. Kolom yang berfungsi sebagai key selama transposisi, dengan jumlah kolom ditentukan oleh parameter num_keys. Jika num_keys menetapkan bahwa semua kolom berfungsi sebagai key (yaitu num_keys sama dengan jumlah total kolom), maka hanya satu baris yang dikembalikan.

• cols: Wajib diisi. Array yang akan dikonversi menjadi baris. Semua kolom setelah keys dianggap sebagai array yang akan ditransposisi dan harus bertipe STRING. Isinya berupa array dalam format string, misalnya Hangzhou;Beijing;shanghai, yaitu array yang dipisahkan oleh titik koma (;).

Penjelasan Nilai Kembalian

Mengembalikan baris hasil transposisi, dengan nama kolom baru yang ditentukan oleh klausa as. Tipe data kolom yang berfungsi sebagai key tetap tidak berubah, sedangkan semua kolom lainnya bertipe STRING. Jumlah baris hasil mengikuti array dengan jumlah elemen terbanyak; array yang jumlah elemennya lebih sedikit akan diisi dengan NULL.

Contoh Penggunaan

• Contoh 1: Misalnya data dalam tabel t_table adalah sebagai berikut.

  +----------+----------+------------+
  | login_id | login_ip | login_time |
  +----------+----------+------------+
  | wangwangA | 192.168.0.1,192.168.0.2 | 20120101010000,20120102010000 |
  | wangwangB | 192.168.45.10,192.168.67.22,192.168.6.3 | 20120111010000,20120112010000,20120223080000 |
  +----------+----------+------------+
  --Jalankan SQL berikut.
  select trans_array(1, ",", login_id, login_ip, login_time) as (login_id,login_ip,login_time) from t_table;
  --Hasil yang dikembalikan adalah sebagai berikut.
  +----------+----------+------------+
  | login_id | login_ip | login_time |
  +----------+----------+------------+
  | wangwangB | 192.168.45.10 | 20120111010000 |
  | wangwangB | 192.168.67.22 | 20120112010000 |
  | wangwangB | 192.168.6.3 | 20120223080000 |
  | wangwangA | 192.168.0.1 | 20120101010000 |
  | wangwangA | 192.168.0.2 | 20120102010000 |
  +----------+----------+------------+
  
  --Jika data dalam tabel adalah sebagai berikut.
  Login_id LOGIN_IP LOGIN_TIME 
  wangwangA 192.168.0.1,192.168.0.2 20120101010000
  --Elemen array yang tidak mencukupi akan diisi dengan NULL. 
  Login_id Login_ip Login_time 
  wangwangA 192.168.0.1 20120101010000
  wangwangA 192.168.0.2 NULL

• Contoh 2: Misalnya data dalam tabel mf_fun_array_test_t adalah sebagai berikut.

  +------------+------------+------------+------------+
  | id         | name       | login_ip   | login_time |
  +------------+------------+------------+------------+
  | 1          | Tom        | 192.168.100.1,192.168.100.2 | 20211101010101,20211101010102 |
  | 2          | Jerry      | 192.168.100.3,192.168.100.4 | 20211101010103,20211101010104 |
  +------------+------------+------------+------------+
  
  --Gunakan dua key, yaitu id dan name, untuk melakukan transposisi array. Jalankan SQL berikut.
  select trans_array(2, ",", Id,Name, login_ip, login_time) as (Id,Name,login_ip,login_time) from mf_fun_array_test_t;
  --Hasil yang dikembalikan adalah sebagai berikut, dengan key id dan name telah dikelompokkan dan dipecah.
  +------------+------------+------------+------------+
  | id         | name       | login_ip   | login_time |
  +------------+------------+------------+------------+
  | 1          | Tom        | 192.168.100.1 | 20211101010101 |
  | 1          | Tom        | 192.168.100.2 | 20211101010102 |
  | 2          | Jerry      | 192.168.100.3 | 20211101010103 |
  | 2          | Jerry      | 192.168.100.4 | 20211101010104 |
  +------------+------------+------------+------------+

Fungsi Terkait

Fungsi TRANS_ARRAY termasuk dalam kategori fungsi lainnya. Untuk informasi lebih lanjut mengenai fungsi lainnya dalam berbagai skenario bisnis, lihat Fungsi Lainnya.