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 terkait batas pada skala dan presisi:

  • NUMERIC dalam dialek PostgreSQL adalah jenis numerik presisi desimal arbitrer (skala atau presisi dapat berupa angka apa 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 Anda perlu menyimpan angka presisi arbitrer dalam database dialek GoogleSQL, sebaiknya simpan sebagai string.

Presisi jenis numerik Spanner

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

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

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

INT64 / INT8 mewakili nilai numerik yang tidak memiliki komponen pecahan. Jenis data ini memberikan presisi 18 digit, 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 nol di belakang dihapus) presisi desimal. Kita mengatakan bahwa jenis ini merepresentasikan nilai numerik desimal perkiraan karena representasi biner floating point 64-bit IEEE yang digunakan Spanner tidak dapat merepresentasikan pecahan desimal (basis 10) secara tepat (hanya dapat merepresentasikan pecahan basis 2 secara tepat). Hilangnya presisi ini menyebabkan error pembulatan untuk beberapa pecahan desimal.

Misalnya, saat Anda menyimpan nilai desimal 0,2 menggunakan jenis data FLOAT64 / FLOAT8, representasi biner akan dikonversi kembali menjadi nilai desimal 0,20000000000000001 (hingga 18 digit presisi). Demikian pula, (1,4 * 165) dikonversi kembali menjadi 230,999999999999971 dan (0,1 + 0,2) dikonversi kembali menjadi 0,30000000000000004. Itulah sebabnya bilangan floating point 64-bit hanya memiliki presisi 15-17 angka signifikan (hanya beberapa angka dengan lebih dari 15 angka desimal yang dapat direpresentasikan sebagai bilangan floating point 64-bit tanpa pembulatan). Untuk mengetahui 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 kalkulasi keuangan, ilmiah, atau teknik, yang biasanya memerlukan presisi 30 digit atau lebih.

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

Jenis data NUMERIC GoogleSQL dapat merepresentasikan angka dengan presisi desimal tetap 38 dan skala tetap 9. Rentang NUMERIC GoogleSQL adalah -99999999999999999999999999999.999999999 hingga 99999999999999999999999999999.999999999.

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

Jika Anda perlu menyimpan angka yang lebih besar daripada 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 memerlukan presisi yang lebih tinggi daripada yang disediakan NUMERIC, sebaiknya simpan nilai sebagai representasi desimalnya dalam kolom STRING / VARCHAR. Misalnya, angka 123.4 disimpan sebagai string "123.4".

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

Sebagian besar library presisi arbitrer memiliki metode bawaan untuk melakukan konversi tanpa kehilangan data ini. Misalnya, di Java, 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 penghitungan exact pada representasi string dari angka presisi arbitrer, aplikasi Anda harus melakukan penghitungan ini. Anda tidak dapat menggunakan fungsi agregat SQL.

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

Melakukan agregasi, pengurutan, dan penghitungan perkiraan

Anda dapat menggunakan kueri SQL untuk melakukan penghitungan agregat perkiraan dengan melakukan transmisi 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 menurut nilai numerik atau membatasi nilai menurut rentang dengan casting:

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 mendekati 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 presisi dibatasi hingga 18 digit yang disediakan oleh jenis data INT64 / INT8.

Misalnya, angka yang perlu disimpan dengan akurasi 5 angka di belakang koma. Aplikasi mengonversi nilai menjadi bilangan bulat dengan mengalikannya dengan 100.000 (menggeser titik desimal 5 tempat ke kanan), sehingga nilai 12,54321 disimpan sebagai 1254321.

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

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

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 disimpan dalam database, selama hasilnya diskalakan dengan benar dan tidak meluap.

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

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

  • Nilai bilangan bulat yang tidak diskalakan dan 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, komplemen dua big-endian).

Kemudian, database 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 tidak dapat dibaca oleh manusia dalam database, dan semua perhitungan harus dilakukan oleh aplikasi.

Menyimpan representasi internal aplikasi sebagai byte

Opsi lainnya adalah menyerialkan nilai desimal presisi arbitrer ke array byte menggunakan representasi internal aplikasi, lalu menyimpannya langsung di database.

Nilai database yang disimpan tidak dapat dibaca oleh manusia, dan aplikasi perlu melakukan semua penghitungan.

Pendekatan ini memiliki masalah portabilitas. Jika Anda mencoba membaca nilai dengan bahasa atau library pemrograman yang berbeda dari yang awalnya menulisnya, maka mungkin tidak akan berfungsi. Membaca kembali nilai mungkin tidak berfungsi karena berbagai library presisi arbitrer dapat memiliki representasi berseri yang berbeda untuk array byte.

Langkah berikutnya