Armazene dados numéricos de precisão arbitrária

O Spanner fornece o tipo NUMERIC que pode armazenar números de precisão decimal exatamente. A semântica do tipo NUMERIC no Spanner varia entre os seus dois dialetos de SQL (GoogleSQL e PostgreSQL), especialmente em torno dos limites de escala e precisão:

  • NUMERIC no dialeto PostgreSQL é um tipo numérico de precisão decimal arbitrária (a escala ou a precisão podem ser qualquer número dentro do intervalo suportado) e, por isso, é uma escolha ideal para armazenar dados numéricos de precisão arbitrária.

  • NUMERIC no GoogleSQL é um tipo numérico de precisão fixa (precision=38 e scale=9) e não pode ser usado para armazenar dados numéricos de precisão arbitrária. Quando precisa de armazenar números de precisão arbitrária em bases de dados do dialeto GoogleSQL, recomendamos que os armazene como strings.

Precisão dos tipos numéricos do Spanner

A precisão é o número de dígitos num número. A escala é o número de dígitos à direita da vírgula decimal num número. Por exemplo, o número 123,456 tem uma precisão de 6 e uma escala de 3. O Spanner tem três tipos numéricos:

  • Tipo de número inteiro com sinal de 64 bits denominado INT64 no dialeto GoogleSQL e INT8 no dialeto PostgreSQL.
  • Tipo de ponto flutuante binário de 64 bits (duplo) de precisão IEEE denominado FLOAT64 no dialeto GoogleSQL e FLOAT8 no dialeto PostgreSQL.
  • Tipo de NUMERIC precisão decimal.

Vamos analisar cada uma em termos de precisão e escala.

INT64 / INT8 representam valores numéricos que não têm um componente fracionário. Este tipo de dados oferece 18 dígitos de precisão, com uma escala de zero.

FLOAT64 / FLOAT8 só pode representar valores numéricos decimais aproximados com componentes fracionários e fornece 15 a 17 dígitos significativos (contagem de dígitos num número com todos os zeros finais removidos) de precisão decimal. Afirmamos que este tipo representa valores numéricos decimais aproximados porque a representação binária de vírgula flutuante de 64 bits da IEEE que o Spanner usa não pode representar com precisão frações decimais (de base 10) (só pode representar com precisão frações de base 2). Esta perda de precisão introduz erros de arredondamento para algumas frações decimais.

Por exemplo, quando armazena o valor decimal 0,2 usando o tipo de dados FLOAT64 / FLOAT8, a representação binária é convertida novamente num valor decimal de 0,20000000000000001 (com 18 dígitos de precisão). Da mesma forma, (1,4 * 165) é convertido novamente em 230,999999999999971 e (0,1 + 0,2) é convertido novamente em 0,30000000000000004. É por este motivo que os números de vírgula flutuante de 64 bits são descritos como tendo apenas 15 a 17 algarismos significativos de precisão (apenas alguns números com mais de 15 algarismos decimais podem ser representados como números de vírgula flutuante de 64 bits sem arredondamento). Para mais detalhes sobre como a precisão da vírgula flutuante é calculada, consulte o formato de vírgula flutuante de precisão dupla.

Nem INT64 / INT8 nem FLOAT64 / FLOAT8 têm a precisão ideal para cálculos financeiros, científicos ou de engenharia, em que é comummente necessária uma precisão de 30 dígitos ou mais.

O tipo de dados NUMERIC é adequado para essas aplicações, uma vez que é capaz de representar valores numéricos com precisão decimal exata com uma precisão de mais de 30 dígitos decimais.

O tipo de dados NUMERIC do GoogleSQL pode representar números com uma precisão decimal fixa de 38 e uma escala fixa de 9. O intervalo de NUMERIC do GoogleSQL é de -99999999999999999999999999999,999999999 a 99999999999999999999999999999,999999999.

O tipo NUMERIC do dialeto PostgreSQL pode representar números com uma precisão decimal máxima de 147 455 e uma escala máxima de 16 383.

Se precisar de armazenar números superiores à precisão e à escala oferecidas pelo NUMERIC, as secções seguintes descrevem algumas soluções recomendadas.

Recomendação: armazene números de precisão arbitrária como strings

Quando precisa de armazenar um número de precisão arbitrária numa base de dados do Spanner e precisa de mais precisão do que a que o tipo de dados NUMERIC oferece, recomendamos que armazene o valor como a respetiva representação decimal numa coluna STRING / VARCHAR. Por exemplo, o número 123.4 é armazenado como a string "123.4".

Com esta abordagem, a sua aplicação tem de realizar uma conversão sem perdas entre a representação interna da aplicação do número e o valor da coluna STRING / VARCHAR para leituras e escritas na base de dados.

A maioria das bibliotecas de precisão arbitrária tem métodos incorporados para realizar esta conversão sem perdas. Em Java, por exemplo, pode usar o método BigDecimal.toPlainString() e o construtor BigDecimal(String).

Armazenar o número como uma string tem a vantagem de o valor ser armazenado com precisão exata (até ao limite de comprimento da coluna STRING / VARCHAR) e o valor permanecer legível.

Realizar agregações e cálculos exatos

Para realizar agregações e cálculos exact em representações de strings de números de precisão arbitrária, a sua aplicação tem de realizar estes cálculos. Não pode usar funções de agregação SQL.

Por exemplo, para realizar o equivalente de um SUM(value) SQL num intervalo de linhas, a aplicação tem de consultar os valores de string das linhas e, em seguida, convertê-los e somá-los internamente na app.

Realizar agregações, ordenações e cálculos aproximados

Pode usar consultas SQL para fazer cálculos agregados aproximados convertendo os valores em FLOAT64 / FLOAT8.

GoogleSQL 

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

PostgreSQL 

SELECT SUM(value::FLOAT8) FROM my_table

Da mesma forma, pode ordenar por valor numérico ou limitar valores por intervalo com a conversão:

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;

Estes cálculos são aproximados aos limites do tipo de dados FLOAT64 / FLOAT8.

Alternativas

Existem outras formas de armazenar números de precisão arbitrária no Spanner. Se o armazenamento de números de precisão arbitrária como strings não funcionar para a sua aplicação, considere as seguintes alternativas:

Armazene valores inteiros dimensionados pela aplicação

Para armazenar números de precisão arbitrária, pode pré-dimensionar os valores antes de escrever, para que os números sejam sempre armazenados como números inteiros, e redimensionar os valores após a leitura. A sua aplicação armazena um fator de escala fixo e a precisão está limitada aos 18 dígitos fornecidos pelo tipo de dados INT64 / INT8.

Considere, por exemplo, um número que tem de ser armazenado com uma precisão de 5 casas decimais. A aplicação converte o valor num número inteiro multiplicando-o por 100 000 (deslocando a vírgula decimal 5 casas para a direita). Assim,o valor 12,54321 é armazenado como 1254321.

Em termos monetários, esta abordagem é semelhante a armazenar valores em euros como múltiplos de milésimos de cêntimos, semelhante ao armazenamento de unidades de tempo como milissegundos.

A aplicação determina o fator de escalabilidade fixo. Se alterar o fator de escala, tem de converter todos os valores com escala anterior na sua base de dados.

Esta abordagem armazena valores legíveis por humanos (partindo do princípio de que conhece o fator de escala). Além disso, pode usar consultas SQL para fazer cálculos diretamente em valores armazenados na base de dados, desde que o resultado seja dimensionado corretamente e não exceda o limite.

Armazene o valor inteiro não dimensionado e a escala em colunas separadas

Também pode armazenar números de precisão arbitrária no Spanner através de dois elementos:

  • O valor inteiro não dimensionado armazenado numa matriz de bytes.
  • Um número inteiro que especifica o fator de escalabilidade.

Primeiro, a sua aplicação converte o decimal de precisão arbitrária num valor inteiro não dimensionado. Por exemplo, a aplicação converte 12.54321 em 1254321. A escala para este exemplo é 5.

Em seguida, a aplicação converte o valor inteiro não dimensionado num conjunto de bytes através de uma representação binária portátil padrão (por exemplo, complemento de dois big-endian).

Em seguida, a base de dados armazena a matriz de bytes (BYTES / BYTEA) e a escala de números inteiros (INT64 / INT8) em duas colunas separadas e converte-as novamente na leitura.

Em Java, pode usar BigDecimal e BigInteger para fazer estes cálculos:

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

Pode ler novamente para um Java BigDecimal através do seguinte código:

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

Esta abordagem armazena valores com precisão arbitrária e uma representação portátil, mas os valores não são legíveis na base de dados, e todos os cálculos têm de ser realizados pela aplicação.

Armazenar a representação interna da aplicação como bytes

Outra opção é serializar os valores decimais de precisão arbitrária em matrizes de bytes através da representação interna da aplicação e, em seguida, armazená-los diretamente na base de dados.

Os valores da base de dados armazenados não são legíveis por humanos e a aplicação tem de fazer todos os cálculos.

Esta abordagem tem problemas de portabilidade. Se tentar ler os valores com uma linguagem de programação ou uma biblioteca diferente da que os escreveu originalmente, pode não funcionar. A leitura dos valores pode não funcionar porque diferentes bibliotecas de precisão arbitrária podem ter diferentes representações serializadas para matrizes de bytes.

O que se segue?