Menyimpan data numerik presisi arbitrer

Spanner menyediakan jenis NUMERIC yang dapat menyimpan angka presisi desimal dengan tepat. Semantik jenis NUMERIC di Spanner bervariasi antara dua dialek SQL-nya (GoogleSQL dan PostgreSQL), terutama seputar batas pada skala dan presisi:

  • NUMERIC dalam dialek PostgreSQL adalah jenis numerik presisi desimal arbitrer (skala atau presisi dapat berupa angka berapa pun dalam rentang yang didukung) sehingga merupakan pilihan ideal untuk menyimpan data numerik presisi arbitrer.

  • NUMERIC di GoogleSQL adalah jenis numerik presisi tetap (presisi=38 dan skala=9) dan tidak dapat digunakan untuk menyimpan data numerik presisi arbitrer. Jika perlu menyimpan angka presisi arbitrer dalam database dialek GoogleSQL, sebaiknya simpan angka tersebut sebagai string.

Presisi jenis numerik Spanner

Presisi adalah jumlah digit dalam angka. Skala adalah jumlah digit di sebelah kanan titik desimal dalam angka. Misalnya, angka 123,456 memiliki presisi 6 dan skala 3. Spanner memiliki tiga jenis numerik:

  • Jenis bilangan bulat dengan tanda tangan 64-bit yang disebut INT64 dalam dialek GoogleSQL dan INT8 dalam dialek PostgreSQL.
  • Jenis floating point presisi biner 64-bit (ganda) yang disebut FLOAT64 dalam dialek GoogleSQL dan FLOAT8 dalam dialek PostgreSQL.
  • Jenis NUMERIC presisi desimal.

Mari kita lihat masing-masing dari segi presisi dan skala.

INT64 / INT8 mewakili nilai numerik yang tidak memiliki komponen pecahan. Jenis data ini memberikan 18 digit presisi, dengan skala nol.

FLOAT64 / FLOAT8 hanya dapat merepresentasikan perkiraan nilai numerik desimal dengan komponen pecahan dan memberikan 15 hingga 17 digit signifikan (jumlah digit dalam angka dengan semua angka nol diakhirnya dihapus) presisi desimal. Kami mengatakan bahwa jenis ini mewakili nilai numerik desimal perkiraan karena representasi biner IEEE 64-bit yang digunakan Spanner tidak dapat secara tepat merepresentasikan fraksi desimal (basis-10) (hanya dapat merepresentasikan pecahan basis-2 dengan tepat). Hilangnya presisi ini menyebabkan kesalahan pembulatan untuk beberapa pecahan desimal.

Misalnya, jika Anda menyimpan nilai desimal 0,2 menggunakan jenis data FLOAT64 / FLOAT8, representasi biner akan mengonversi kembali ke nilai desimal 0,20000000000000001 (ketepatan 18 digit). Demikian pula, (1,4 * 165) mengonversi kembali menjadi 230,999999999999971 dan (0,1 + 0,2) mengonversi kembali ke 0,30000000000000004. Inilah sebabnya mengapa float 64-bit digambarkan sebagai hanya memiliki 15-17 digit presisi yang signifikan (hanya beberapa angka dengan lebih dari 15 digit desimal yang dapat direpresentasikan sebagai float 64-bit tanpa pembulatan). Untuk detail selengkapnya tentang cara penghitungan presisi floating point, lihat Format floating point presisi ganda.

INT64 / INT8 maupun FLOAT64 / FLOAT8 tidak memiliki presisi yang ideal untuk penghitungan keuangan, ilmiah, atau teknik, yang umumnya memerlukan presisi 30 digit atau lebih.

Jenis data NUMERIC cocok untuk aplikasi tersebut, karena mampu merepresentasikan nilai numerik presisi desimal yang tepat yang memiliki presisi lebih dari 30 digit desimal.

Jenis data NUMERIC GoogleSQL dapat mewakili angka dengan presisi desimal tetap 38 dan skala tetap 9. Rentang NUMERIC GoogleSQL adalah -99999999999999999999999999.999999999 hingga 99999999999999999999999999.999999999.999999999.

Jenis dialek PostgreSQL NUMERIC dapat mewakili angka dengan presisi desimal maksimum 147.455 dan skala maksimum 16.383.

Jika Anda perlu menyimpan angka yang lebih besar dari presisi dan skala yang ditawarkan oleh NUMERIC, bagian berikut menjelaskan beberapa solusi yang direkomendasikan.

Rekomendasi: simpan angka presisi arbitrer sebagai string

Jika Anda perlu menyimpan angka presisi arbitrer dalam database Spanner, dan Anda membutuhkan presisi lebih dari yang diberikan NUMERIC, sebaiknya simpan nilai sebagai representasi desimalnya di kolom STRING / VARCHAR. Misalnya, angka 123.4 disimpan sebagai string "123.4".

Dengan pendekatan ini, aplikasi Anda harus melakukan konversi lossless antara representasi internal aplikasi-internal angka dan nilai kolom STRING / VARCHAR untuk pembacaan dan penulisan database.

Sebagian besar library presisi arbitrer memiliki metode bawaan untuk melakukan konversi lossless ini. Di Java, misalnya, Anda dapat menggunakan metode BigDecimal.toPlainString() dan konstruktor BigDecimal(String).

Menyimpan angka sebagai string memiliki keuntungan bahwa nilai disimpan dengan presisi yang tepat (hingga batas panjang kolom STRING / VARCHAR), dan nilai tetap dapat dibaca manusia.

Melakukan agregasi dan penghitungan yang tepat

Untuk melakukan agregasi dan kalkulasi yang persis pada representasi string angka presisi arbitrer, aplikasi Anda harus melakukan penghitungan ini. Anda tidak dapat menggunakan fungsi agregat SQL.

Misalnya, untuk menjalankan operasi SQL SUM(value) yang setara pada rentang baris, aplikasi harus membuat kueri nilai string untuk baris tersebut, lalu mengonversi dan menjumlahkannya secara internal di aplikasi.

Melakukan agregasi perkiraan, pengurutan, dan penghitungan

Anda dapat menggunakan kueri SQL untuk melakukan perkiraan penghitungan agregat dengan mentransmisikan nilai ke FLOAT64 / FLOAT8.

GoogleSQL 

SELECT SUM(CAST(value AS FLOAT64)) FROM my_table

PostgreSQL 

SELECT SUM(value::FLOAT8) FROM my_table

Demikian pula, Anda dapat mengurutkan berdasarkan nilai numerik atau membatasi nilai menurut rentang dengan melakukan transmisi:

GoogleSQL 

SELECT value FROM my_table ORDER BY CAST(value AS FLOAT64);
SELECT value FROM my_table WHERE CAST(value AS FLOAT64) > 100.0;

PostgreSQL 

SELECT value FROM my_table ORDER BY value::FLOAT8;
SELECT value FROM my_table WHERE value::FLOAT8 > 100.0;

Penghitungan ini merupakan perkiraan untuk batas jenis data FLOAT64 / FLOAT8.

Alternatif

Ada cara lain untuk menyimpan angka presisi arbitrer di Spanner. Jika menyimpan angka presisi arbitrer sebagai string tidak berfungsi untuk aplikasi Anda, pertimbangkan alternatif berikut:

Menyimpan nilai bilangan bulat yang diskalakan aplikasi

Untuk menyimpan angka presisi arbitrer, Anda dapat menskalakan nilai terlebih dahulu sebelum menulis, sehingga angka selalu disimpan sebagai bilangan bulat, dan menskalakan ulang nilai setelah membaca. Aplikasi Anda menyimpan faktor skala tetap, dan presisinya terbatas pada 18 digit yang diberikan oleh jenis data INT64 / INT8.

Misalnya, angka yang perlu disimpan dengan akurasi 5 desimal. Aplikasi mengonversi nilai ini menjadi bilangan bulat dengan mengalikannya dengan 100.000 (menggeser titik desimal 5 ke kanan), sehingga nilai 12.54321 disimpan sebagai 1254321.

Dalam istilah moneter, pendekatan ini seperti menyimpan nilai dolar sebagai kelipatan milisen, mirip dengan menyimpan satuan waktu sebagai milidetik.

Aplikasi menentukan faktor penskalaan tetap. Jika mengubah faktor penskalaan, Anda harus mengonversi semua nilai yang diskalakan sebelumnya dalam database Anda.

Pendekatan ini menyimpan nilai yang dapat dibaca manusia (dengan asumsi Anda mengetahui faktor penskalaan). Selain itu, Anda dapat menggunakan kueri SQL untuk melakukan penghitungan secara langsung pada nilai yang tersimpan dalam database, selama hasilnya diskalakan dengan benar dan tidak melebihi batas.

Simpan nilai bilangan bulat yang tidak diskalakan dan skala di kolom terpisah

Anda juga dapat menyimpan angka presisi arbitrer di Spanner menggunakan dua elemen:

  • Nilai bilangan bulat yang tidak diskalakan disimpan dalam array byte.
  • Bilangan bulat yang menentukan faktor penskalaan.

Pertama, aplikasi Anda mengonversi desimal presisi arbitrer menjadi nilai bilangan bulat yang tidak diskalakan. Misalnya, aplikasi mengonversi 12.54321 menjadi 1254321. Skala untuk contoh ini adalah 5.

Kemudian aplikasi mengonversi nilai bilangan bulat yang tidak diskalakan menjadi array byte menggunakan representasi biner portabel standar (misalnya, pelengkap dua big-endian).

Database kemudian menyimpan array byte (BYTES / BYTEA) dan skala bilangan bulat (INT64 / INT8) dalam dua kolom terpisah, dan mengonversinya kembali saat dibaca.

Di Java, Anda dapat menggunakan BigDecimal dan BigInteger untuk melakukan penghitungan ini:

byte[] storedUnscaledBytes = bigDecimal.unscaledValue().toByteArray();
int storedScale = bigDecimal.scale();

Anda dapat membaca kembali ke BigDecimal Java menggunakan kode berikut:

BigDecimal bigDecimal = new BigDecimal(
    new BigInteger(storedUnscaledBytes),
    storedScale);

Pendekatan ini menyimpan nilai dengan presisi arbitrer dan representasi portabel, tetapi nilai tersebut tidak dapat dibaca manusia dalam database, dan semua kalkulasi harus dilakukan oleh aplikasi.

Menyimpan representasi internal aplikasi sebagai byte

Opsi lainnya adalah melakukan serialisasi nilai desimal presisi arbitrer untuk melakukan byte pada array menggunakan representasi internal aplikasi, lalu menyimpannya langsung di database.

Nilai database yang tersimpan tidak dapat dibaca manusia, dan aplikasi harus melakukan semua penghitungan.

Pendekatan ini memiliki masalah portabilitas. Jika Anda mencoba membaca nilai dengan bahasa pemrograman atau library yang berbeda dengan yang aslinya menulisnya, nilai tersebut mungkin tidak akan berfungsi. Membaca kembali nilai ini mungkin tidak dapat dilakukan karena library presisi arbitrer yang berbeda dapat memiliki representasi serial yang berbeda untuk array byte.

Langkah selanjutnya