Membuat Table Numbers untuk SQL
Soeleman 25 Februari 2017
Dalam SQL banyak kita dapati 'pekerjaan' yang memerlukan hal-hal yang diulang. Terlebih lagi, keperluan akan data sebagai pembantu membuat query.
Urutan Angka
Yang di maksud disini adalah urutan-angka. Kita bisa saja membuat dalam query-nya, dalam artian kita membuat urutan-angka pada saat mengesekusi ke database. Untuk prototype atau proof-of-concept tidak masalah, untuk production database?
Sederhana saja, dari pada kita meng-generated urutan-angka itu setiap kali dipanggil. Itu akan menggunakan resource baik IO, CPU dan process-time. Bekerja dengan database, hal-hal seperti ini sensitif sekali. Oleh karena itu kita dapat buat sebuah Table dengan satu column yang berisi urutan-angka, maka kita tidak perlu lagi meng-generated setiap kali itu dibuat. Dengan begitu, query yang dibuatpun lebih sederhana. Satu lagi kita bisa mengoptimasikan dengan Index atau dalam beberapa database bisa di-set ke dalam memory-table.
Beberapa database memang menawarkan fungsi sebagai solusi, seperti di MariaDB dengan Sequence Engine, PostgreSQL menggunakan generate_series Function atau bisa juga menggunakan RowNum dalam Oracle. Semua itu spesifik sekali perdatabasenya, lagi pula itu tetap membutuhkan resource juga.
SQL
Banyak teknik membuat urutan-angka ini, dari penggunaan fungsi atau menggunakan teknik yang bisa dijalankan pada banyak database. Dengan tambahan INSERT INTO kita dengan mudah menambahkan ke dalam table numbers yang kita sudah siapkan.
Paling mudah dengan menggunakan Arithmetics atau perhitungan seperti dibawah.
< SQL >
Cara lainnya dengan memainkan boolean dalam query-nya.
< SQL >
MySQL
Juga kita bisa memanfaatkan table yang memiliki rows yang banyak untuk membuat urutan-angka ini. Di sini kita menggunakan table mysql.time_zone_transition atau table lainnya.
< MySQL >
Dengan bit shift yang bisa dilakukan pada database MySQL juga bisa jadi solusi.
< MySQL >
SQL Server
Sama seperti diatas dengan MySQL, kita juga bisa menggunakan cara itu di SQLServer.
< T-SQL >
Atau kita bisa memanfaatkan fungsi ROW_NUMBER seperti pada gambar dibawah.
< T-SQL >
Common Table Expression (CTE) dengan cara rekursif dimungkinkan untuk digunakan.
< T-SQL >
Penggunaan
Seperti yang di sajikan diatas, banyak cara membuat urutan-angka. Dan urutan-angka itu kita masukkan ke table numbers agar kita tidak perlu berulang-ulang membuatnya. Dan pada saat kita mengesekusi query, sql engine hanya perlu melihat ke table itu saja. Ini akan membantu sql engine mengoptimasikan query yang ada.
Range-Date
Salah satu ide lagi adalah membuat table Date, yang berisi urutan-tanggal. Dan kita bisa membuat itu dengan bantuan table numbers lalu di simpan ke dalam table.
< MySQL >
< T-SQL >
Column CSV-to-Rows
Kadang kita mendapatkan sebuah legacy database yang didesign untuk kebutuhan yang tidak terlalu besar.
Seperti alamat email yang ditaruh pada sebuah column sebagai Comma-separated values (CSV).
Contoh datanya, mr1@yahoo.com, mr1@google.com, mr1@outlook.com, dengan data seperti itu mendapatkan email google saja kita perlu banyak parsing-nya. Oleh karena itu sekarang kita coba menjadikan column CSV itu menjadi rows dengan bantuan table numbers.
Dengan table numbers kita gunakan untuk memecah column-string dengan index. Caranya kita Join-kan dengan sebuah table yang berisi CSV pada column-nya. Untuk menyederhanakan tulisan ini kita buat saja string yang berisi Satu, Dua, Tiga, Empat, Lima, Dua Puluh.
Di sini kita memanfaatkan fungsi SUBSTRING_INDEX untuk mendapatkan data per-index-nya. Dengan kombinasi dari CHAR_LENGTH dan REPLACE pada WHERE kita membatasi max-index yang akan diambil oleh fungsi SUBSTRING_INDEX.
< MySQL >
Untuk SQLServer prinsipnya hampir sama, tapi kita mesti mencari persamaan untuk SUBSTRING_INDEX. Untungnya dengan kombinasi NULLIF, SUBSTRING dan CHARINDEX kita bisa men-emulate fungsi itu.
Dan kita juga memerlukan perubahan pada WHERE-nya juga. LEN, SUBSTRING dan CHARINDEX merupakan ramuan untuk membuat emulate fungsi itu dapat bekerja.
< T-SQL >
Dan hasilnya seperti yang diharapkan.
Number-to-Letter
Ini juga bisa jadi contoh menarik. Disini kita ada table NumberLetter yang berisi data seperti pada gambar. Tujuan kita adalah me-map angka yang di input menjadi huruf.
Untuk itu kita harus memecahkan input itu ke dalam rows lalu di-map ke table diatas. Sebelum itu, dipecahkan input itu ke rows dengan bantuan table numbers. Urutan dari table numbers akan membantu 'fungsi' dari query. Query itu sendiri cukup banyak menggunakan internal-method dan itu dipadatkan dalam alur penyelusuran data.
< MySQL >
Query ini hampir sama, tapi kita perlu men-emulate fungsi GROUP_CONCAT dengan For XML.
< T-SQL >
Dan hasilnya kita bisa me-map angka ke hurup. Hasil yang sederhana dari process yang tidak sederhana.
Penutup
Dengan membuat table numbers seperti diatas akan mengurangi kebutuhan membuat urutan-angka setiap kali kita memerlukannya. Query yang kita buatpun akan terlihat lebih rapi dan sql engine lebih mudah mengoptimalkan query yang kita buat.
Bekerja dengan SQL memerlukan pengetahuan akan fungsi-fungsi yang ada dan mengkombinasikan untuk menghasilkan resultset yang kita butuhkan. Design database kadang tidak selalu ideal dan dengan cara-cara seperti di atas kita mentolerasinya.
Referensi
Perhatian! Code yang di tampilkan dalam tulisan ini merupakan ilustrasi dari yang ingin dipaparkan dan bukan production ready code. Sudah banyak kejadian karena asal meng-copy-and-paste tanpa mengerti code yang di ambil itu ke dalam production. Selain itu perlu ada tambahan code dan test sebelum siap untuk di gunakan secara utuh.