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

O Spanner fornece o tipo NUMERIC que pode armazenar números de precisão decimal com precisão. A semântica do tipo NUMERIC no Spanner varia entre os dois dialetos 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 pode ser qualquer número dentro do intervalo aceito) e, portanto, é uma opção ideal para armazenar dados numéricos de precisão arbitrária.

  • NUMERIC no GoogleSQL é um tipo numérico de precisão fixa (precisão=38 e scale=9) e não pode ser usado para armazenar dados numéricos de precisão arbitrária. Quando for necessário armazenar números de precisão arbitrária em bancos de dados de dialetos GoogleSQL, recomendamos armazená-los como strings.

Precisão dos tipos numéricos do Spanner

A precisão é a quantidade de dígitos em um número. Escala é o número de dígitos à direita do ponto decimal em um 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 assinado de 64 bits chamado INT64 no dialeto GoogleSQL e INT8 no dialeto PostgreSQL.
  • Tipo de ponto flutuante de precisão binária de 64 bits (duplo) do IEEE chamado FLOAT64 no dialeto GoogleSQL e FLOAT8 no dialeto PostgreSQL.
  • Tipo NUMERIC de precisão decimal.

Vejamos cada um deles em termos de precisão e escala.

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

FLOAT64 / FLOAT8 só podem representar valores numéricos decimais aproximados com componentes fracionários e fornecem de 15 a 17 dígitos significativos (contagem de dígitos em um número com todos os zeros à direita removidos) de precisão decimal. Dizemos que esse tipo representa valores numéricos decimais aproximados porque a representação binária de ponto flutuante IEEE de 64 bits que o Spanner usa não pode representar com precisão frações decimais (base-10). Ela pode representar exatamente apenas frações de base 2. Essa perda de precisão introduz erros de arredondamento para algumas frações decimais.

Por exemplo, quando você armazena o valor decimal 0,2 usando o tipo de dados FLOAT64 / FLOAT8, a representação binária converte de volta para um valor decimal de 0,20000000000000001 (para 18 dígitos de precisão). Da mesma forma (1,4 * 165) converte de volta para 230,999999999999971 e (0,1 + 0,2) converte de volta para 0,30000000000000004. É por isso que os pontos flutuantes de 64 bits são descritos como tendo apenas 15 a 17 dígitos significativos de precisão. Somente alguns números com mais de 15 dígitos decimais podem ser representados como flutuantes de 64 bits sem arredondamento. Para mais detalhes sobre como a precisão de ponto flutuante é calculada, consulte Formato de ponto 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 uma precisão de 30 dígitos ou mais geralmente é necessária.

O tipo de dados NUMERIC é adequado para esses aplicativos, já que é capaz de representar valores numéricos de precisão decimal exata com precisão de mais de 30 dígitos decimais.

O tipo de dados NUMERIC do GoogleSQL pode representar números com precisão decimal fixa de 38 e escala fixa de 9. O intervalo do 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 você precisar armazenar números maiores do que a precisão e a escala oferecidas pelo NUMERIC, as seções a seguir descrevem algumas soluções recomendadas.

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

Quando você precisar armazenar um número de precisão arbitrário em um banco de dados do Spanner e precisar de mais precisão do que NUMERIC fornece, recomendamos armazenar o valor como representação decimal em uma coluna STRING / VARCHAR. Por exemplo, o número 123.4 é armazenado como a string "123.4".

Com essa abordagem, seu aplicativo precisa realizar uma conversão sem perdas entre a representação interna do aplicativo do número e o valor da coluna STRING / VARCHAR para leituras e gravações do banco de dados.

A maioria das bibliotecas de precisão arbitrárias tem métodos internos para executar essa conversão sem perdas. Em Java, por exemplo, você pode usar o método BigDecimal.toPlainString() e o construtor BigDecimal(String).

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

Realizar agregações e cálculos exatos

Para executar agregações e cálculos exatos em representações de strings de números de precisão arbitrária, seu aplicativo precisa executar esses cálculos. Não é possível usar funções agregadas de SQL.

Por exemplo, para executar o equivalente a um SUM(value) SQL em um intervalo de linhas, o aplicativo precisa consultar os valores de string das linhas, depois convertê-los e somar internamente.

Realizar agregações, classificação e cálculos aproximados

É possível usar consultas SQL para executar cálculos agregados aproximados fazendo o lançamento dos valores para FLOAT64 / FLOAT8.

GoogleSQL 

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

PostgreSQL 

SELECT SUM(value::FLOAT8) FROM my_table

Da mesma forma, é possível classificar por valor numérico ou valores de limite por intervalo com 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;

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

Alternativas

Há outras maneiras 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 seu aplicativo, considere as seguintes alternativas:

Armazenar valores inteiros dimensionados por aplicativo

Para armazenar números de precisão arbitrária, é possível pré-dimensionar os valores antes de gravar, para que os números sejam sempre armazenados como números inteiros e redimensionar os valores após a leitura. Seu aplicativo armazena um fator de escalonamento fixo, e a precisão é limitada aos 18 dígitos fornecidos pelo tipo de dados INT64 / INT8.

Tomemos, por exemplo, um número que precisa ser armazenado com uma precisão de 5 casas decimais. O aplicativo converte o valor em um inteiro multiplicando-o por 100.000 (mudando o ponto decimal 5 lugares para a direita), portanto, o valor 12,54321 é armazenado como 1254321.

Em termos monetários, essa abordagem é como armazenar valores em dólares como múltiplos de milicentésimos, semelhante ao armazenamento de unidades de tempo como milissegundos.

O aplicativo determina o fator de escalonamento fixo. Se você alterar o fator de escalonamento, precisará converter todos os valores escalonados anteriormente em seu banco de dados.

Essa abordagem armazena valores que são legíveis por humanos (supondo que você saiba o fator de escalonamento). Além disso, é possível usar consultas SQL para executar cálculos diretamente em valores armazenados no banco de dados, desde que o resultado seja dimensionado corretamente e não estoure.

Armazene o valor inteiro sem escalonamento e o escalonamento em colunas separadas

Também é possível armazenar números de precisão arbitrária no Spanner usando dois elementos:

  • O valor inteiro sem escalonamento armazenado em uma matriz de bytes.
  • Um inteiro que especifica o fator de escalonamento.

Primeiro, seu aplicativo converte o decimal de precisão arbitrária em um valor inteiro não escalonado. Por exemplo, o aplicativo converte 12.54321 em 1254321. O escalonamento para este exemplo é 5.

Em seguida, o aplicativo converte o valor inteiro sem escala em uma matriz de bytes usando uma representação binária portátil padrão (por exemplo, o complemento dos dois big-endian).

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

Em Java, você pode usar BigDecimal e BigInteger para realizar estes cálculos:

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

É possível ler novamente em um BigDecimal Java usando o seguinte código:

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

Essa abordagem armazena valores com precisão arbitrária e uma representação portátil, mas os valores não são legíveis pelo usuário no banco de dados e todos os cálculos precisam ser executados pelo aplicativo.

Armazenar representação interna do aplicativo como bytes

Outra opção é serializar os valores decimais de precisão arbitrária para matrizes de bytes usando a representação interna do aplicativo e, em seguida, armazená-los diretamente no banco de dados.

Os valores do banco de dados armazenados não são legíveis e o aplicativo precisa executar todos os cálculos.

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

A seguir