Esta página foi traduzida pela API Cloud Translation.
Switch to English

Expressões, funções e operadores

Esta página explica as expressões do Cloud Spanner SQL, incluindo as funções e os operadores.

Regras de chamada de função

A menos que seja indicado de outra forma na descrição da função, as regras a seguir aplicam-se a todas as funções:

  • Nas funções que aceitam tipos numéricos, se um operando for um ponto flutuante e o outro for um outro tipo numérico, ambos serão convertidos para FLOAT64 antes que a função seja avaliada.
  • Se um operando for NULL, o resultado será NULL, com exceção do operador IS.
  • Para funções sensíveis ao fuso horário (como indicado na descrição da função), o padrão America/Los_Angeles será utilizado se o fuso não for especificado.

Prefixo SAFE.

Sintaxe:

SAFE.function_name()

Descrição

Se você iniciar uma função escalar com o prefixo SAFE., ela retornará NULL em vez de um erro. O prefixo SAFE. apenas evita erros da função prefixada: ele não evita erros que ocorrem durante a avaliação de expressões de argumentos. O prefixo SAFE. apenas evita erros que ocorrem devido ao valor das entradas da função, como erros de "valor fora do intervalo"; outros erros, como erros internos ou do sistema, ainda podem ocorrer. Se a função não retornar um erro, SAFE. não terá efeito na saída. Se a função nunca retornar um erro, como RAND, SAFE. não tem efeito.

Operadores, como + e =, não são compatíveis com o prefixo SAFE.. Para evitar erros de uma operação de divisão, use SAFE_DIVIDE. Alguns operadores, como IN, ARRAY e UNNEST, são parecidos com funções, mas não são compatíveis com o prefixo SAFE.. As funções CAST e EXTRACT também não são compatíveis com o prefixo SAFE.. Para evitar erros de cast, use SAFE_CAST.

Exemplo

No seguinte exemplo, o primeiro uso da função SUBSTR normalmente retorna um erro, porque a função não é compatível com argumentos de comprimento com valores negativos. No entanto, o prefixo SAFE. faz com que a função retorne NULL. O segundo uso da função SUBSTR fornece a saída esperada: o prefixo SAFE. não tem efeito.

SELECT SAFE.SUBSTR('foo', 0, -2) AS safe_output UNION ALL
SELECT SAFE.SUBSTR('bar', 0, 2) AS safe_output;

+-------------+
| safe_output |
+-------------+
| NULL        |
| ba          |
+-------------+

Regras de conversão

A "conversão" inclui, entre outros tipos, cast e coerção.

  • Cast é conversão explícita e usa a função CAST().
  • "Coerção" é a conversão implícita realizada automaticamente pelo Cloud Spanner SQL nas condições descritas abaixo.

Também há conversões que têm os próprios nomes de função, como PARSE_DATE(). Para saber mais sobre essas funções, consulte Funções de conversão.

Gráfico de comparação

A tabela a seguir resume todas as possibilidades de CAST e coerção dos tipos de dados do Cloud Spanner SQL. A coluna "Coerção para" se aplica a todas as expressões de um determinado tipo de dados (por exemplo, uma coluna).

Do tipo CAST para Coerção para
INT64 BOOL
INT64
NUMERIC
FLOAT64
STRING
NUMERIC
FLOAT64
NUMERIC INT64
NUMERIC
FLOAT64
STRING
FLOAT64
FLOAT64 INT64
NUMERIC
FLOAT64
STRING
 
BOOL BOOL
INT64
STRING
 
STRING BOOL
INT64
NUMERIC
FLOAT64
STRING
BYTES
DATE
TIMESTAMP
 
BYTES STRING
BYTES
 
DATE STRING
DATE
TIMESTAMP
TIMESTAMP STRING
DATE
TIMESTAMP
 
ARRAY ARRAY  
STRUCT STRUCT  

Cast

A maioria dos tipos de dados pode ser transmitida de um tipo para outro com a função CAST. Quando a função CAST é usada, pode ocorrer falha na consulta se o SQL do Cloud Spanner não executar a conversão. Caso queira proteger suas consultas contra esses tipos de erros, use SAFE_CAST. Para saber mais sobre as regras para CAST, SAFE_CAST e outras funções de transmissão, consulte Funções de conversão.

Coerção

O SQL do Cloud Spanner força o tipo de resultado de uma expressão de argumento para outro tipo, se necessário, para corresponder às assinaturas da função. Por exemplo, se a função func() for definida para ter um único argumento do tipo FLOAT64 e uma expressão for usada como um argumento que tenha um tipo de resultado INT64, então o resultado da expressão será forçado para o tipo FLOAT64 antes de func() ser calculado.

Funções de agregação

A função de agregação resume as linhas de um grupo em um único valor. COUNT, MIN e MAX são exemplos de funções de agregação.

SELECT COUNT(*) as total_count, COUNT(fruit) as non_null_count,
       MIN(fruit) as min, MAX(fruit) as max
FROM (SELECT NULL as fruit UNION ALL
      SELECT "apple" as fruit UNION ALL
      SELECT "pear" as fruit UNION ALL
      SELECT "orange" as fruit)

+-------------+----------------+-------+------+
| total_count | non_null_count | min   | max  |
+-------------+----------------+-------+------+
| 4           | 3              | apple | pear |
+-------------+----------------+-------+------+

Quando usados em conjunto com uma cláusula GROUP BY, os grupos resumidos costumam ter pelo menos uma linha. Quando o SELECT associado não tem nenhuma cláusula GROUP BY ou quando determinados modificadores de função de agregação filtram as linhas do grupo para resumi-lo, é possível que a função de agregação precise resumir um grupo vazio. Nesse caso, as funções COUNT e COUNTIF retornam 0, enquanto todas as outras funções agregadas retornam NULL.

As seções a seguir descrevem as funções de agregação compatíveis com o Cloud Spanner SQL.

ANY_VALUE

ANY_VALUE(expression [HAVING {MAX | MIN} expression2])

Descrição

Retorna expression para alguma linha escolhida do grupo. A escolha da linha é um processo não determinístico e não aleatório. Retorna NULL quando a entrada não produz linhas. Retorna NULL quando expression é NULL para todas as linhas no grupo.

ANY_VALUE se comporta como se IGNORE NULLS fosse especificado. As linhas para as quais expression é NULL não são consideradas e não serão selecionadas.

Tipos de argumentos compatíveis

Qualquer um

Cláusula opcional

HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte Cláusula HAVING MAX e HAVING MIN para detalhes.

Tipos de dados retornados

Corresponde aos tipos de dados de entrada.

Exemplos

SELECT ANY_VALUE(fruit) as any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;

+-----------+
| any_value |
+-----------+
| apple     |
+-----------+

ARRAY_AGG

ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS] [HAVING {MAX | MIN} expression2])

Descrição

Retorna um ARRAY de valores expression.

Tipos de argumentos compatíveis

Todos os tipos de dados, exceto ARRAY.

Cláusulas opcionais

As cláusulas são aplicadas na seguinte ordem:

  1. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  2. IGNORE NULLS ou RESPECT NULLS: se IGNORE NULLS for especificado, os valores NULL serão excluídos do resultado. Se RESPECT NULLS estiver especificado, os valores NULL serão incluídos no resultado. Se nenhum deles for especificado, os valores NULL serão incluídos no resultado.
  3. HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte a cláusula HAVING MAX e HAVING MIN para mais detalhes.

Ordem dos elementos na saída

A ordem dos elementos na saída não é determinista, o que significa que você pode receber um resultado diferente cada vez que usar essa função.

Tipos de dados retornados

ARRAY

Se houver zero linhas de entrada, essa função retornará NULL.

Exemplos

SELECT ARRAY_AGG(x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+-------------------------+
| array_agg               |
+-------------------------+
| [2, 1, -2, 3, -2, 1, 2] |
+-------------------------+
SELECT ARRAY_AGG(DISTINCT x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+---------------+
| array_agg     |
+---------------+
| [2, 1, -2, 3] |
+---------------+
SELECT ARRAY_AGG(x IGNORE NULLS) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

+-------------------+
| array_agg         |
+-------------------+
| [1, -2, 3, -2, 1] |
+-------------------+

ARRAY_CONCAT_AGG

ARRAY_CONCAT_AGG(expression [HAVING {MAX | MIN} expression2])

Descrição

Concatena elementos de expression do tipo ARRAY, retornando um único ARRAY como resultado. Essa função ignora matrizes de entrada NULL, mas respeita os elementos NULL em matrizes de entrada diferentes de NULL.

Tipos de argumentos compatíveis

ARRAY

Cláusula opcional

HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte Cláusula HAVING MAX e HAVING MIN para detalhes.

Ordem dos elementos na saída

A ordem dos elementos na saída não é determinista, o que significa que você pode receber um resultado diferente cada vez que usar essa função.

Tipos de dados retornados

ARRAY

Retorna NULL se não houver nenhuma linha de entrada ou expression for avaliado como NULL para todas as linhas.

Exemplos

SELECT ARRAY_CONCAT_AGG(x) AS array_concat_agg FROM (
  SELECT [NULL, 1, 2, 3, 4] AS x
  UNION ALL SELECT NULL
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+-----------------------------------+
| array_concat_agg                  |
+-----------------------------------+
| [NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9] |
+-----------------------------------+

AVG

AVG([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descrição

Retorna a média de valores de entrada NULL, ou NaN se a entrada tiver um NaN.

Tipos de argumentos compatíveis

Qualquer tipo de entrada numérica, como INT64. Para tipos de entrada de ponto flutuante, o resultado retornado não é determinista. Isso significa que o resultado pode ser diferente sempre que essa função for usada.

Cláusulas opcionais

As cláusulas são aplicadas na seguinte ordem:

  1. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  2. HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte a cláusula HAVING MAX e HAVING MIN para mais detalhes.

Tipos de dados retornados

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

Exemplos

SELECT AVG(x) as avg
FROM UNNEST([0, 2, 4, 4, 5]) as x;

+-----+
| avg |
+-----+
| 3   |
+-----+
SELECT AVG(DISTINCT x) AS avg
FROM UNNEST([0, 2, 4, 4, 5]) AS x;

+------+
| avg  |
+------+
| 2.75 |
+------+

BIT_AND

BIT_AND([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descrição

Executa uma operação bit a bit AND em expression e retorna o resultado.

Tipos de argumentos compatíveis

  • INT64

Cláusulas opcionais

As cláusulas são aplicadas na seguinte ordem:

  1. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  2. HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte a cláusula HAVING MAX e HAVING MIN para mais detalhes.

Tipos de dados retornados

INT64

Exemplos

SELECT BIT_AND(x) as bit_and FROM UNNEST([0xF001, 0x00A1]) as x;

+---------+
| bit_and |
+---------+
| 1       |
+---------+

BIT_OR

BIT_OR([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descrição

Executa uma operação bit a bit OR em expression e retorna o resultado.

Tipos de argumentos compatíveis

  • INT64

Cláusulas opcionais

As cláusulas são aplicadas na seguinte ordem:

  1. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  2. HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte a cláusula HAVING MAX e HAVING MIN para mais detalhes.

Tipos de dados retornados

INT64

Exemplos

SELECT BIT_OR(x) as bit_or FROM UNNEST([0xF001, 0x00A1]) as x;

+--------+
| bit_or |
+--------+
| 61601  |
+--------+

BIT_XOR

BIT_XOR([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descrição

Executa uma operação bit a bit XOR em expression e retorna o resultado.

Tipos de argumentos compatíveis

  • INT64

Cláusulas opcionais

As cláusulas são aplicadas na seguinte ordem:

  1. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  2. HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte a cláusula HAVING MAX e HAVING MIN para mais detalhes.

Tipos de dados retornados

INT64

Exemplos

SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 5678    |
+---------+
SELECT BIT_XOR(DISTINCT x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+

COUNT

1. COUNT(*)

2. COUNT([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descrição

  1. Retorna o número de linhas na entrada.
  2. Retorna o número de linhas com expression avaliado para qualquer valor diferente de NULL.

Tipos de argumentos compatíveis

expression pode ser qualquer tipo de dados. Se DISTINCT estiver presente, expression só poderá ser um tipo de dados agrupável.

Cláusulas opcionais

As cláusulas são aplicadas na seguinte ordem:

  1. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  2. HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte a cláusula HAVING MAX e HAVING MIN para mais detalhes.

Tipos de dados retornados

INT64

Exemplos

SELECT
  COUNT(*) AS count_star,
  COUNT(DISTINCT x) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;

+------------+--------------+
| count_star | count_dist_x |
+------------+--------------+
| 4          | 3            |
+------------+--------------+
SELECT COUNT(*) AS count_star, COUNT(x) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;

+------------+---------+
| count_star | count_x |
+------------+---------+
| 5          | 4       |
+------------+---------+

COUNTIF

COUNTIF([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descrição

Retorna a contagem de valores TRUE para expression. Retorna 0 se houver zero linhas de entrada ou se expression for avaliado como FALSE ou NULL para todas as linhas.

Tipos de argumentos compatíveis

BOOL

Cláusulas opcionais

As cláusulas são aplicadas na seguinte ordem:

  1. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  2. HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte a cláusula HAVING MAX e HAVING MIN para mais detalhes.

Tipos de dados retornados

INT64

Exemplos

SELECT COUNTIF(x<0) AS num_negative, COUNTIF(x>0) AS num_positive
FROM UNNEST([5, -2, 3, 6, -10, -7, 4, 0]) AS x;

+--------------+--------------+
| num_negative | num_positive |
+--------------+--------------+
| 3            | 4            |
+--------------+--------------+

LOGICAL_AND

LOGICAL_AND(expression [HAVING {MAX | MIN} expression2])

Descrição

Retorna a lógica AND de todas as expressões diferentes de NULL. Retorna NULL, se não houver nenhuma linha de entrada ou expression for avaliado como NULL para todas as linhas.

Tipos de argumentos compatíveis

BOOL

Cláusula opcional

HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte Cláusula HAVING MAX e HAVING MIN para detalhes.

Tipos de dados retornados

BOOL

Exemplos

SELECT LOGICAL_AND(x) AS logical_and FROM UNNEST([true, false, true]) AS x;

+-------------+
| logical_and |
+-------------+
| false       |
+-------------+

LOGICAL_OR

LOGICAL_OR(expression [HAVING {MAX | MIN} expression2])

Descrição

Retorna a lógica OR de todas as expressões diferentes de NULL. Retorna NULL, se não houver nenhuma linha de entrada ou expression for avaliado como NULL para todas as linhas.

Tipos de argumentos compatíveis

BOOL

Cláusula opcional

HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte Cláusula HAVING MAX e HAVING MIN para detalhes.

Tipos de dados retornados

BOOL

Exemplos

SELECT LOGICAL_OR(x) AS logical_or FROM UNNEST([true, false, true]) AS x;

+------------+
| logical_or |
+------------+
| true       |
+------------+

MAX

MAX(expression [HAVING {MAX | MIN} expression2])

Descrição

Retorna o valor máximo de expressões não NULL. Retorna NULL, se não houver nenhuma linha de entrada ou expression for avaliado como NULL para todas as linhas. Retorna NaN se a entrada contiver um NaN.

Tipos de argumentos compatíveis

Qualquer tipo de dado, exceto: ARRAY STRUCT

Cláusula opcional

HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte Cláusula HAVING MAX e HAVING MIN para detalhes.

Tipos de dados retornados

Igual ao tipo de dados usado como os valores da entrada.

Exemplos

SELECT MAX(x) AS max
FROM UNNEST([8, 37, 4, 55]) AS x;

+-----+
| max |
+-----+
| 55  |
+-----+

MIN

MIN(expression [HAVING {MAX | MIN} expression2])

Descrição

Retorna o valor mínimo de expressões não NULL. Retorna NULL, se não houver nenhuma linha de entrada ou expression for avaliado como NULL para todas as linhas. Retorna NaN se a entrada contiver um NaN.

Tipos de argumentos compatíveis

Qualquer tipo de dado, exceto: ARRAY STRUCT

Cláusula opcional

HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte Cláusula HAVING MAX e HAVING MIN para detalhes.

Tipos de dados retornados

Igual ao tipo de dados usado como os valores da entrada.

Exemplos

SELECT MIN(x) AS min
FROM UNNEST([8, 37, 4, 55]) AS x;

+-----+
| min |
+-----+
| 4   |
+-----+

STRING_AGG

STRING_AGG([DISTINCT] expression [, delimiter] [HAVING {MAX | MIN} expression2])

Descrição

A concatenação de valores não nulos retorna um valor STRING ou BYTES.

Se um delimiter for especificado, os valores concatenados serão separados por esse delimitador; caso contrário, uma vírgula será usada como um delimitador.

Tipos de argumentos compatíveis

STRING BYTES

Cláusulas opcionais

As cláusulas são aplicadas na seguinte ordem:

  1. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  2. HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte a cláusula HAVING MAX e HAVING MIN para mais detalhes.

Ordem dos elementos na saída

A ordem dos elementos na saída não é determinista, o que significa que você pode receber um resultado diferente cada vez que usar essa função.

Tipos de dados retornados

STRING BYTES

Exemplos

SELECT STRING_AGG(fruit) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+------------------------+
| string_agg             |
+------------------------+
| apple,pear,banana,pear |
+------------------------+
SELECT STRING_AGG(fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+------------------------------+
| string_agg                   |
+------------------------------+
| apple & pear & banana & pear |
+------------------------------+
SELECT STRING_AGG(DISTINCT fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+-----------------------+
| string_agg            |
+-----------------------+
| apple & pear & banana |
+-----------------------+

SUM

SUM([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descrição

Retorna a soma de valores não nulos.

Se a expressão for um valor de ponto flutuante, a soma não será determinista. Isso significa que o resultado pode ser diferente sempre que essa função for usada.

Tipos de argumentos compatíveis

Quaisquer tipos de dados numéricos compatíveis.

Cláusulas opcionais

As cláusulas são aplicadas na seguinte ordem:

  1. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  2. HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte a cláusula HAVING MAX e HAVING MIN para mais detalhes.

Tipos de dados retornados

INPUTINT64NUMERICFLOAT64
OUTPUTINT64NUMERICFLOAT64

Casos especiais:

Retorna NULL, se a entrada contiver apenas NULLs.

Retorna NULL, se a entrada não contiver linhas.

Retorna Inf, se a entrada contiver Inf.

Retorna -Inf se a entrada contiver -Inf.

Retorna NaN se a entrada contiver um NaN.

Retorna NaN se a entrada contiver uma combinação de Inf e -Inf.

Exemplos

SELECT SUM(x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 25  |
+-----+
SELECT SUM(DISTINCT x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 15  |
+-----+
SELECT SUM(x) AS sum
FROM UNNEST([]) AS x;

+------+
| sum  |
+------+
| NULL |
+------+

Cláusulas comuns

Cláusula HAVING MAX e HAVING MIN

A maioria das funções de agregação é compatível com duas cláusulas opcionais chamadas HAVING MAX e HAVING MIN, que restringem o conjunto de linhas que uma função agrega a linhas que têm um valor máximo ou mínimo em uma coluna específica. A sintaxe geralmente tem esta aparência:

aggregate_function(expression1 [HAVING {MAX | MIN} expression2])
  • HAVING MAX: restringe o conjunto de linhas que a função agrega àquelas com um valor para expression2 igual ao valor máximo para expression2 no grupo. O valor máximo é igual ao resultado de MAX(expression2).
  • HAVING MIN: restringe o conjunto de linhas que a função agrega àquelas com um valor para expression2 igual ao valor mínimo para expression2 no grupo. O valor mínimo é igual ao resultado de MIN(expression2).

Essas cláusulas ignoram os valores NULL ao calcular o valor máximo ou mínimo, a menos que expression2 seja avaliado como NULL para todas as linhas.

Essas cláusulas não são compatíveis com os tipos de dados a seguir: ARRAY STRUCT

Exemplo

Neste exemplo, a chuva média é retornada para o ano mais recente, 2001.

WITH Precipitation AS
 (SELECT 2001 as year, 'spring' as season, 9 as inches UNION ALL
  SELECT 2001, 'winter', 1 UNION ALL
  SELECT 2000, 'fall', 3 UNION ALL
  SELECT 2000, 'summer', 5 UNION ALL
  SELECT 2000, 'spring', 7 UNION ALL
  SELECT 2000, 'winter', 2)
SELECT AVG(inches HAVING MAX year) as average FROM Precipitation

+---------+
| average |
+---------+
| 5       |
+---------+

Primeiro, a consulta recebe as linhas com o valor máximo na coluna year. Há dois:

+------+--------+--------+
| year | season | inches |
+------+--------+--------+
| 2001 | spring | 9      |
| 2001 | winter | 1      |
+------+--------+--------+

Por fim, a consulta faz uma média dos valores na coluna inches (9 e 1) com este resultado:

+---------+
| average |
+---------+
| 5       |
+---------+

Funções de agregação estatística

O Cloud Spanner SQL é compatível com as seguintes funções de agregação estatística.

STDDEV_SAMP

STDDEV_SAMP([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descrição

Retorna o desvio padrão polarizado de amostra dos valores. O resultado retornado está entre 0 e +Inf.

Todos os tipos numéricos são aceitos. Se a entrada for NUMERIC, a agregação interna será estável com a saída final convertida em uma FLOAT64. Caso contrário, a entrada é convertida em FLOAT64 antes da agregação, resultando em um resultado potencialmente instável.

Essa função ignora as entradas NULL. Se houver menos de duas entradas diferentes de NULL, ela retornará NULL.

Cláusulas opcionais

As cláusulas são aplicadas na seguinte ordem:

  1. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  2. HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte a cláusula HAVING MAX e HAVING MIN para mais detalhes.

Tipo de dados retornados

FLOAT64

STDDEV

STDDEV([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descrição

Um sinônimo de STDDEV_SAMP.

VAR_SAMP

VAR_SAMP([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descrição

Retorna a variância não polarizada de amostra dos valores. O resultado retornado está entre 0 e +Inf.

Todos os tipos numéricos são aceitos. Se a entrada for NUMERIC, a agregação interna será estável com a saída final convertida em uma FLOAT64. Caso contrário, a entrada é convertida em FLOAT64 antes da agregação, resultando em um resultado potencialmente instável.

Essa função ignora as entradas NULL. Se houver menos de duas entradas diferentes de NULL, ela retornará NULL.

Cláusulas opcionais

As cláusulas são aplicadas na seguinte ordem:

  1. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  2. HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte a cláusula HAVING MAX e HAVING MIN para mais detalhes.

Tipo de dados retornados

FLOAT64

VARIANCE

VARIANCE([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descrição

Um sinônimo de VAR_SAMP.

Funções de conversão

O Cloud Spanner SQL é compatível com as seguintes funções de conversão. Essas conversões de tipo de dados são explícitas, mas algumas podem acontecer implicitamente. Saiba mais sobre conversões implícitas e explícitas aqui.

Visão geral do CAST

CAST(expression AS typename)

Descrição

Usa-se a sintaxe de cast em uma consulta para indicar que é preciso converter o tipo do resultado de uma expressão em algum outro tipo.

Ao usar o CAST, uma consulta pode falhar se o Cloud Spanner SQL não conseguir executar a transmissão. Para proteger as consultas contra esses tipos de erros, use SAFE_CAST.

Os casts entre tipos compatíveis que não são mapeados com sucesso do valor original para o domínio de destino produzem erros de tempo de execução. Por exemplo, o cast de BYTES para STRING em que a sequência de bytes não é um UTF-8 válido resulta em um erro de ambiente de execução.

Exemplos

A consulta a seguir resulta em "true" se x for 1, "false" para qualquer outro valor que não seja NULL e NULL se x for NULL.

CAST(x=1 AS STRING)

CAST COMO ARRAY

CAST(expression AS ARRAY<element_type>)

Descrição

O Cloud Spanner SQL é compatível com cast para ARRAY. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • ARRAY

Regras de conversão

De Até Regras ao fazer o cast x
ARRAY ARRAY Precisa ser exatamente do mesmo tipo ARRAY.

CAST COMO BOOL

CAST(expression AS BOOL)

Descrição

O Cloud Spanner SQL é compatível com transmissão para BOOL. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • INT64
  • BOOL
  • STRING

Regras de conversão

De Até Regras ao fazer o cast x
INT64 BOOL Retorna FALSE se x for 0. Caso contrário, retorna TRUE.
STRING BOOL Retorna TRUE se x for "true" e FALSE se x for "false"
Todos os outros valores de x são inválidos e lançam um erro em vez de fazer o cast para BOOL.
As STRINGs não diferenciam maiúsculas de minúsculas durante a conversão para BOOL.

CAST COMO BYTES

CAST(expression AS BYTES)

Descrição

O Cloud Spanner SQL é compatível com cast para BYTES. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • BYTES
  • STRING

Regras de conversão

De Até Regras ao fazer o cast x
STRING BYTES O cast das STRINGs para BYTES é feito usando a codificação UTF-8. Por exemplo, a STRING "©", após o cast para BYTES, torna-se uma sequência de dois bytes com os valores hexadecimais C2 e A9.

CAST COMO DATE

CAST(expression AS DATE)

Descrição

O Cloud Spanner SQL é compatível com cast para DATE. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • STRING
  • TIMESTAMP

Regras de conversão

De Até Regras ao fazer o cast x
STRING DATE No caso do cast da string para data, a string precisa seguir o formato literal data compatível, independentemente do fuso horário. Se a expressão da string for inválida ou representar uma data fora do intervalo mínimo/máximo aceito, ocorrerá um erro.
TIMESTAMP DATE O cast de um carimbo de data/hora para uma data trunca efetivamente o carimbo de data/hora a partir do fuso horário padrão.

CAST COMO FLOAT64

CAST(expression AS FLOAT64)

Descrição

O Cloud Spanner SQL é compatível com cast para tipos de ponto flutuante. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • INT64
  • FLOAT64
  • NUMERIC
  • STRING

Regras de conversão

De Até Regras ao fazer o cast x
INT64 FLOAT64 Retorna um valor de ponto flutuante próximo, mas possivelmente não exato.
NUMERIC FLOAT64 NUMERIC será convertido no número de ponto flutuante mais próximo com uma possível perda de precisão.
STRING FLOAT64 Retorna x como um valor de ponto flutuante, interpretando-o como tendo a mesma forma de um literal de ponto flutuante válido. Também é compatível com casts de "[+,-]inf" para [,-]Infinity, "[+,-]infinity" para [,-]Infinity e "[+,-]nan" para NaN. As conversões não diferenciam maiúsculas de minúsculas.

CAST COMO INT64

CAST(expression AS INT64)

Descrição

O SQL do Cloud Spanner é compatível com cast para tipos com números inteiros. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • INT64
  • FLOAT64
  • NUMERIC
  • BOOL
  • STRING

Regras de conversão

De Até Regras ao fazer o cast x
FLOAT64 INT64 Retorna o valor inteiro mais próximo.
Casos em que haja metades, como 1,5 ou -0,5, são arredondados para metade de zero.
BOOL INT64 Retorna 1 se x for TRUE. Caso contrário, retorna 0.
STRING INT64 Uma string hexadecimal pode ser convertida em um número inteiro. Por exemplo, de 0x123 para 291 ou -0x123 para -291.

Exemplos

Se não estiver trabalhando com strings hex (0x123), faça o cast dessas strings como inteiras:

SELECT '0x123' as hex_value, CAST('0x123' as INT64) as hex_to_int;

+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| 0x123     | 291        |
+-----------+------------+
SELECT '-0x123' as hex_value, CAST('-0x123' as INT64) as hex_to_int;

+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| -0x123    | -291       |
+-----------+------------+

CAST COMO NUMERIC

CAST(expression AS NUMERIC)

Descrição

O Cloud Spanner SQL é compatível com cast para NUMERIC. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • INT64
  • FLOAT64
  • NUMERIC
  • STRING

Regras de conversão

De Até Regras ao fazer o cast x
FLOAT64 NUMERIC O número de ponto flutuante será arredondado para metade de zero. Fazer cast de NaN, +inf ou -inf retornará um erro. Fazer cast de um valor fora do intervalo de NUMERIC retornará um erro de estouro.
STRING NUMERIC O literal numérico contido em STRING não pode exceder a precisão máxima ou o intervalo do tipo NUMERIC, ou ocorrerá um erro. Se o número de dígitos depois da vírgula decimal exceder nove, o valor resultante NUMERIC será arredondado para metade de zero para ter nove dígitos depois da vírgula decimal.

CAST COMO STRING

CAST(expression AS STRING)

Descrição

O Cloud Spanner SQL é compatível com cast para STRING. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • INT64
  • FLOAT64
  • NUMERIC
  • BOOL
  • BYTES
  • DATE
  • TIMESTAMP
  • STRING

Regras de conversão

De Até Regras ao fazer o cast x
FLOAT64 STRING Retorna uma representação de string aproximada.
BOOL STRING Retorna "true" se x for TRUE. Caso contrário, retorna "false".
BYTES STRING Retorna x interpretado como uma STRING UTF-8.
Por exemplo, o literal BYTES b'\xc2\xa9', após o cast para STRING, é interpretado como UTF-8 e se torna o caractere unicode “©”.
Um erro ocorre se x não for um UTF-8 válido.
DATE STRING O cast de um tipo de data para uma string segue o formato YYYY-MM-DD, independentemente do fuso horário.
TIMESTAMP STRING No cast de tipos de carimbo de data/hora para string, o carimbo é interpretado com o fuso horário padrão, America/Los_Angeles. O número de dígitos de subsegundos produzidos depende do número de zeros à direita na parte de subsegundo. A função CAST truncará nenhum, três ou seis dígitos.

Exemplos

CAST(CURRENT_DATE() AS STRING) AS current_date

+---------------+
| current_date  |
+---------------+
| 2021-03-09    |
+---------------+

CAST COMO STRUCT

CAST(expression AS STRUCT)

Descrição

O Cloud Spanner SQL é compatível com cast para STRUCT. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • STRUCT

Regras de conversão

De Até Regras ao fazer o cast x
STRUCT STRUCT Permitido, se as condições a seguir forem atendidas:
  1. Os dois STRUCTs têm o mesmo número de campos.
  2. Os tipos originais de campos STRUCT podem passar por cast explicitamente para os tipos de campos de destino STRUCT correspondentes (definidos pela ordem e não pelo nome do campo).

CAST COMO TIMESTAMP

CAST(expression AS TIMESTAMP)

Descrição

O Cloud Spanner SQL é compatível com cast para TIMESTAMP. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • STRING
  • TIMESTAMP

Regras de conversão

De Até Regras ao fazer o cast x
STRING TIMESTAMP No cast de string para carimbo de data/hora, string_expression precisa seguir os formatos compatíveis de literal do carimbo de data/hora. Caso contrário, ocorrerá um erro no ambiente de execução. O próprio string_expression pode conter um fuso horário.
Se houver um fuso horário no string_expression, esse fuso horário será usado para conversão. Caso contrário, será usado o fuso horário padrão, América/Los_Angeles. Se a string tiver menos de seis dígitos, ela será ampliada implicitamente.
Um erro será produzido se a string_expression for inválida, tiver mais de seis dígitos de subsegundos (isto é, uma precisão maior que microssegundos) ou representar um horário fora do intervalo aceito do carimbo de data/hora.
DATE TIMESTAMP O cast de uma data para um timestamp interpreta date_expression a partir da meia-noite (início do dia) no fuso horário padrão America/Los_Angeles.

SAFE_CAST

SAFE_CAST(expression AS typename)

Descrição

Ao usar o CAST, uma consulta pode falhar se o Cloud Spanner SQL não conseguir executar a transmissão. Por exemplo, a consulta a seguir gera um erro:

SELECT CAST("apple" AS INT64) AS not_a_number;

Caso queira proteger as consultas contra esses tipos de erros, use SAFE_CAST. SAFE_CAST é idêntico a CAST, mas retorna NULL em vez de gerar um erro.

SELECT SAFE_CAST("apple" AS INT64) AS not_a_number;

+--------------+
| not_a_number |
+--------------+
| NULL         |
+--------------+

Se você estiver fazendo o cast de bytes para strings, também poderá usar a função SAFE_CONVERT_BYTES_TO_STRING. Todos os caracteres UTF-8 inválidos são substituídos pelo caractere de substituição unicode, U+FFFD. Consulte SAFE_CONVERT_BYTES_TO_STRING para mais informações.

Outras funções de conversão

Saiba mais sobre essas funções de conversão em outras partes da documentação:

Função de conversão De Até
ARRAY_TO_STRING ARRAY STRING
DATA Vários tipos de dados DATE
FROM_BASE32 STRING BYTES
FROM_BASE64 STRING BYTES
FROM_HEX STRING BYTES
PARSE_DATE STRING DATE
PARSE_TIMESTAMP STRING TIMESTAMP
SAFE_CONVERT_BYTES_TO_STRING BYTES STRING
STRING TIMESTAMP STRING
TIMESTAMP Vários tipos de dados TIMESTAMP
TO_BASE32 BYTES STRING
TO_BASE64 BYTES STRING
TO_HEX BYTES STRING

Funções matemáticas

Todas as funções matemáticas têm os seguintes comportamentos:

  • Elas retornam NULL se algum dos parâmetros de entrada for NULL.
  • Elas retornam NaN se algum dos argumentos for NaN.

ABS

ABS(X)

Descrição

Calcula o valor absoluto. Retornará um erro caso o argumento seja um número inteiro e o valor de saída não possa ser representado como o mesmo tipo. Isso acontece apenas com o maior valor negativo da entrada, sem representação positiva.

X ABS(X)
25 25
-25 25
+inf +inf
-inf +inf

Tipo de dados retornados

INPUTINT64NUMERICFLOAT64
OUTPUTINT64NUMERICFLOAT64

SIGN

SIGN(X)

Descrição

Retorna -1, 0 ou +1 para argumentos negativo, zero e positivo, respectivamente. Para argumentos de ponto flutuante, essa função não faz distinção entre zero positivo e negativo.

X SIGN(X)
25 +1
0 0
-25 -1
NaN NaN

Tipo de dados retornados

INPUTINT64NUMERICFLOAT64
OUTPUTINT64NUMERICFLOAT64

IS_INF

IS_INF(X)

Descrição

Retornará TRUE se o valor for um infinito positivo ou negativo.

Retorna FALSE para entradas NUMERIC, já que NUMERIC não pode ser INF.

X IS_INF(X)
+inf TRUE
-inf TRUE
25 FALSE

IS_NAN

IS_NAN(X)

Descrição

Retorna TRUE se o valor for NaN.

Retorna FALSE para entradas NUMERIC, já que NUMERIC não pode ser NaN.

X IS_NAN(X)
NaN TRUE
25 FALSE

IEEE_DIVIDE

IEEE_DIVIDE(X, Y)

Descrição

Divide X por Y e nunca falha. Ela retorna FLOAT64. Ao contrário do operador de divisão (/), não gera erros de divisão por zero ou estouro.

X S IEEE_DIVIDE(X, Y)
20.0 4.0 5.0
0.0 25.0 0.0
25.0 0.0 +inf
-25.0 0.0 -inf
0.0 0.0 NaN
0.0 NaN NaN
NaN 0.0 NaN
+inf +inf NaN
-inf -inf NaN

SQRT

SQRT(X)

Descrição

Calcula a raiz quadrada de X. Gera um erro se X for menor que 0.

X SQRT(X)
25.0 5.0
+inf +inf
X < 0 Erro

Tipo de dados retornados

NUMERIC não é aceito diretamente como uma entrada. Primeiro, é necessário transmitir NUMERIC explicitamente para FLOAT64. A saída será FLOAT64.

POW

POW(X, Y)

Descrição

Retorna o valor de X elevado à potência de Y. Se o resultado estourar e não for representável, a função retornará um valor zero.

X S POW(X, Y)
2,0 3.0 8.0
1.0 Qualquer valor, incluindo NaN 1.0
Qualquer valor, incluindo NaN 0 1,0
-1,0 +inf 1.0
-1,0 -inf 1,0
ABS(X) < 1 -inf +inf
ABS(X) > 1 -inf 0.0
ABS(X) < 1 +inf 0.0
ABS(X) > 1 +inf +inf
-inf Y < 0 0.0
-inf Y > 0 -inf se Y for um número inteiro ímpar, +inf caso contrário
+inf Y < 0 0
+inf Y > 0 +inf
Valor finito < 0 Não inteiro Erro
0 Valor finito < 0 Erro

Tipo de dados retornados

O tipo de dados de retorno é determinado pelos tipos de argumento com a tabela a seguir.

INPUTINT64NUMERICFLOAT64
INT64FLOAT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

POWER

POWER(X, Y)

Descrição

Sinônimo de POW(X, Y).

EXP

EXP(X)

Descrição

Calcula e à potência de X, também chamada de função exponencial natural. Se ocorrer um estouro negativo, essa função retornará zero. Um erro será gerado se o resultado estourar.

X EXP(X)
0.0 1.0
+inf +inf
-inf 0.0

Tipo de dados retornados

NUMERIC não é aceito diretamente como uma entrada. Primeiro, é necessário transmitir explicitamente a entrada NUMERIC para FLOAT64. A saída será FLOAT64.

LN

LN(X)

Descrição

Calcula o logaritmo natural de X. Um erro será gerado se X for menor ou igual a zero.

X LN(X)
1.0 0.0
+inf +inf
X < 0 Erro

Tipo de dados retornados

NUMERIC não é aceito diretamente como uma entrada. Primeiro, é necessário transmitir explicitamente a entrada NUMERIC para FLOAT64. A saída será FLOAT64.

LOG

LOG(X [, Y])

Descrição

Se apenas X estiver presente, LOG será um sinônimo de LN. Se Y também estiver presente, LOG calculará o logaritmo de X na base Y.

X Y LOG(X, Y)
100,0 10,0 2,0
-inf Qualquer valor NaN
Qualquer valor +inf NaN
+inf 0,0 Y < 1,0 -inf
+inf Y>1,0 +inf
X <= 0 Qualquer valor Erro
Qualquer valor Y <= 0 Erro
Qualquer valor 1.0 Erro

Tipo de dados retornados

NUMERIC não é aceito diretamente como uma entrada. Primeiro, é necessário transmitir explicitamente a entrada NUMERIC para FLOAT64. A saída será FLOAT64.

LOG10

LOG10(X)

Descrição

Semelhante a LOG, mas calcula o logaritmo de base 10.

X LOG10(X)
100,0 2,0
-inf NaN
+inf NaN
X <= 0 Erro

Tipo de dados retornados

NUMERIC não é aceito diretamente como uma entrada. Primeiro, é necessário transmitir explicitamente a entrada NUMERIC para FLOAT64. A saída será FLOAT64.

GREATEST

GREATEST(X1,...,XN)

Descrição

Retorna o maior valor entre X1,…, XN de acordo com a comparação <. Se alguma parte de X1,...,XN for NULL, o valor de retorno será NULL.

X1,...,XN GREATEST(X1,...,XN)
3,5,1 5

Tipos de dados retornados

Tipo de dados dos valores de entrada.

LEAST

LEAST(X1,...,XN)

Descrição

Retorna o menor valor entre X1,…, XN de acordo com a comparação >. Se alguma parte de X1,...,XN for NULL, o valor de retorno será NULL.

X1,...,XN LEAST(X1,...,XN)
3,5,1 1

Tipos de dados retornados

Tipo de dados dos valores de entrada.

DIV

DIV(X, Y)

Descrição

Retorna o resultado da divisão de inteiros de X por Y. Divisão por zero retorna um erro. Divisão por -1 pode estourar. Se ambas as entradas forem NUMERIC e o resultado for o excesso, ele retornará um erro numeric overflow.

X S DIV(X, Y)
20 4 5
0 20 0
20 0 Erro

Tipo de dados retornados

O tipo de dados de retorno é determinado pelos tipos de argumento com a tabela a seguir.

INPUTINT64NUMERIC
INT64INT64NUMERIC
NUMERICNUMERICNUMERIC

SAFE_DIVIDE

SAFE_DIVIDE(X, Y)

Descrição

Equivalente ao operador de divisão (X / Y), mas retorna NULL se ocorrer um erro como o de divisão por zero.

XSSAFE_DIVIDE(X, Y)
2045
0200
200NULL

Tipo de dados retornados

INPUTINT64NUMERICFLOAT64
INT64FLOAT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

SAFE_MULTIPLY

SAFE_MULTIPLY(X, Y)

Descrição

Equivalente ao operador de multiplicação (*), mas retorna NULL se ocorrer um estouro.

XSSAFE_MULTIPLY(X, Y)
20480

Tipo de dados retornados

INPUTINT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

SAFE_NEGATE

SAFE_NEGATE(X)

Descrição

Equivalente ao operador de menos unário (-), mas retorna NULL se ocorrer um estouro.

XSAFE_NEGATE(X)
+1-1
-1+1
00

Tipo de dados retornados

INPUTINT64NUMERICFLOAT64
OUTPUTINT64NUMERICFLOAT64

SAFE_ADD

SAFE_ADD(X, Y)

Descrição

Equivalente ao operador de adição (+), mas retorna NULL se ocorrer um estouro.

XSSAFE_ADD(X, Y)
549

Tipo de dados retornados

INPUTINT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

SAFE_SUBTRACT

SAFE_SUBTRACT(X, Y)

Descrição

Retorna o resultado de Y subtraído de X. Equivalente ao operador de subtração (-), mas retorna NULL se ocorrer um estouro.

XSSAFE_SUBTRACT(X; Y)
541

Tipo de dados retornados

INPUTINT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

MOD

MOD(X, Y)

Descrição

Função módulo: retorna o restante da divisão de X por Y. O valor retornado tem o mesmo sinal que X. Um erro será gerado se Y for 0.

X S MOD(X, Y)
25 12 1
25 0 Erro

Tipo de dados retornados

O tipo de dados de retorno é determinado pelos tipos de argumento com a tabela a seguir.

INPUTINT64NUMERIC
INT64INT64NUMERIC
NUMERICNUMERICNUMERIC

ROUND

ROUND(X [, N])

Descrição

Se apenas X estiver presente, ROUND arredondará X para o inteiro mais próximo. Se N estiver presente, ROUND arredondará X para N casas decimais após a vírgula decimal. Se N for negativo, ROUND arredondará os dígitos à esquerda da vírgula decimal. Casos em que haja metades são arredondados para longe de zero. Gera um erro se ocorrer um estouro.

X ROUND(X)
2,0 2,0
2,3 2,0
2,8 3.0
2,5 3.0
-2,3 -2,0
-2,8 -3,0
-2,5 -3,0
0 0
+inf +inf
-inf -inf
NaN NaN

Tipo de dados retornados

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

TRUNC

TRUNC(X [, N])

Descrição

Se apenas X estiver presente, TRUNC arredondará X para o inteiro mais próximo cujo valor absoluto não seja maior que o valor absoluto de X. Se N também estiver presente, TRUNC se comportará como ROUND(X, N), mas sempre arredondará para zero e nunca haverá estouro.

X TRUNC(X)
2,0 2,0
2,3 2,0
2.8 2,0
2,5 2,0
-2,3 -2,0
-2,8 -2,0
-2,5 -2,0
0 0
+inf +inf
-inf -inf
NaN NaN

Tipo de dados retornados

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

CEIL

CEIL(X)

Descrição

Retorna o menor valor integral que não seja inferior a X.

X CEIL(X)
2,0 2,0
2.3 3.0
2.8 3.0
2,5 3.0
-2,3 -2,0
-2,8 -2,0
-2,5 -2,0
0 0
+inf +inf
-inf -inf
NaN NaN

Tipo de dados retornados

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

CEILING

CEILING(X)

Descrição

Sinônimo de CEIL(X)

FLOOR

FLOOR(X)

Descrição

Retorna o maior valor integral que não seja maior que X.

X FLOOR(X)
2,0 2,0
2,3 2,0
2.8 2,0
2,5 2,0
-2,3 -3,0
-2,8 -3,0
-2,5 -3,0
0 0
+inf +inf
-inf -inf
NaN NaN

Tipo de dados retornados

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

COS

COS(X)

Descrição

Calcula o cosseno de X onde X é especificado em radianos. Nunca falha.

X COS(X)
+inf NaN
-inf NaN
NaN NaN

COSH

COSH(X)

Descrição

Calcula o cosseno hiperbólico de X onde X é especificado em radianos. Gera um erro se ocorrer um estouro.

Se X for NUMERIC, a saída será FLOAT64.

X COSH(X)
+inf +inf
-inf +inf
NaN NaN

ACOS

ACOS(X)

Descrição

Calcula o valor principal do cosseno inverso de X. O valor retornado está no intervalo [0,π]. Um erro será gerado se X for um valor fora do intervalo [-1, 1].

Se X for NUMERIC, a saída será FLOAT64.

X ACOS(X)
+inf NaN
-inf NaN
NaN NaN
X < -1 Erro
X > 1 Erro

ACOSH

ACOSH(X)

Descrição

Calcula o cosseno hiperbólico inverso de X. Se X for um valor menor que 1, gera um erro.

Se X for NUMERIC, a saída será FLOAT64.

X ACOSH(X)
+inf +inf
-inf NaN
NaN NaN
X < 1 Erro

SIN

SIN(X)

Descrição

Calcula o seno de X onde X é especificado em radianos. Nunca falha.

X SIN(X)
+inf NaN
-inf NaN
NaN NaN

SINH

SINH(X)

Descrição

Calcula o seno hiperbólico de X, onde X é especificado em radianos. Gera um erro se ocorrer um estouro.

Se X for NUMERIC, a saída será FLOAT64.

X SINH(X)
+inf +inf
-inf -inf
NaN NaN

ASIN

ASIN(X)

Descrição

Calcula o valor principal do seno inverso de X. O valor retornado está no intervalo [-π/2, π/2]. Um erro será gerado se X estiver fora do intervalo [-1, 1].

Se X for NUMERIC, a saída será FLOAT64.

X ASIN(X)
+inf NaN
-inf NaN
NaN NaN
X < -1 Erro
X > 1 Erro

ASINH

ASINH(X)

Descrição

Calcula o seno hiperbólico inverso de X. Não falha.

Se X for NUMERIC, a saída será FLOAT64.

X ASINH(X)
+inf +inf
-inf -inf
NaN NaN

TAN

TAN(X)

Descrição

Calcula a tangente de X onde X é especificado em radianos. Gera um erro se ocorrer um estouro.

X TAN(X)
+inf NaN
-inf NaN
NaN NaN

TANH

TANH(X)

Descrição

Calcula a tangente hiperbólica de X, onde X é especificado em radianos. Não falha.

Se X for NUMERIC, a saída será FLOAT64.

X TANH(X)
+inf 1.0
-inf -1,0
NaN NaN

ATAN

ATAN(X)

Descrição

Calcula o valor principal da tangente inversa de X. O valor retornado está no intervalo [-π/2, π/2]. Não falha.

Se X for NUMERIC, a saída será FLOAT64.

X ATAN(X)
+inf π/2
-inf -π/2
NaN NaN

ATANH

ATANH(X)

Descrição

Calcula a tangente hiperbólica inversa de X. Gera um erro se X estiver fora do intervalo [-1, 1].

Se X for NUMERIC, a saída será FLOAT64.

X ATANH(X)
+inf NaN
-inf NaN
NaN NaN
X < -1 Erro
X > 1 Erro

ATAN2

ATAN2(X, Y)

Descrição

Calcula o valor principal da tangente inversa de Y/X utilizando os sinais dos dois argumentos para determinar o quadrante. O valor de retorno está no intervalo [-π, π].

Se X for NUMERIC, a saída será FLOAT64.

X S ATAN2(x;y)
NaN Qualquer valor NaN
Qualquer valor NaN NaN
0.0 0.0 0.0
Valor finito positivo -inf π
Valor finito negativo -inf
Valor finito +inf 0.0
+inf Valor finito π/2
-inf Valor finito -π/2
+inf -inf ¾π
-inf -inf -¾π
+inf +inf π/4
-inf +inf -π/4

Funções de hash

FARM_FINGERPRINT

FARM_FINGERPRINT(value)

Descrição

Calcula a impressão digital da entrada STRING ou BYTES usando a função Fingerprint64 da biblioteca de código aberto FarmHash. A saída dessa função para uma entrada em particular nunca muda.

Tipo de retorno

INT64

Exemplos

WITH example AS (
  SELECT 1 AS x, "foo" AS y, true AS z UNION ALL
  SELECT 2 AS x, "apple" AS y, false AS z UNION ALL
  SELECT 3 AS x, "" AS y, true AS z
)
SELECT
  *,
  FARM_FINGERPRINT(CONCAT(CAST(x AS STRING), y, CAST(z AS STRING)))
    AS row_fingerprint
FROM example;
+---+-------+-------+----------------------+
| x | y     | z     | row_fingerprint      |
+---+-------+-------+----------------------+
| 1 | foo   | true  | -1541654101129638711 |
| 2 | apple | false | 2794438866806483259  |
| 3 |       | true  | -4880158226897771312 |
+---+-------+-------+----------------------+

SHA1

SHA1(input)

Descrição

Calcula o hash da entrada usando o algoritmo SHA-1. A entrada pode ser STRING ou BYTES. A versão da string trata a entrada como uma matriz de bytes.

Essa função retorna 20 bytes.

Tipo de retorno

BYTES

Exemplo

SELECT SHA1("Hello World") as sha1;

-- Note that the result of SHA1 is of type BYTES, displayed as a base64-encoded string.
+------------------------------+
| sha1                         |
+------------------------------+
| Ck1VqNd45QIvq3AZd8XYQLvEhtA= |
+------------------------------+

SHA256

SHA256(input)

Descrição

Calcula o hash da entrada usando o algoritmo SHA-256. A entrada pode ser STRING ou BYTES. A versão da string trata a entrada como uma matriz de bytes.

Essa função retorna 32 bytes.

Tipo de retorno

BYTES

Exemplo

SELECT SHA256("Hello World") as sha256;

SHA512

SHA512(input)

Descrição

Calcula o hash da entrada usando o algoritmo SHA-512. A entrada pode ser STRING ou BYTES. A versão da string trata a entrada como uma matriz de bytes.

Essa função retorna 64 bytes.

Tipo de retorno

BYTES

Exemplo

SELECT SHA512("Hello World") as sha512;

Funções de string

Essas funções de string funcionam em dois valores diferentes: tipos de dados STRING e BYTES. Os valores STRING precisam ter a codificação UTF-8 bem formada.

As funções que retornam valores de posição, como STRPOS, codificam essas posições como INT64. O valor 1 indica o primeiro caractere (ou byte), 2 indica o segundo, e assim por diante. O valor 0 indica um índice inválido. Ao trabalhar em tipos STRING, as posições retornadas indicam as posições dos caracteres.

Todas as comparações de string são feitas byte a byte, sem levar em conta a equivalência canônica Unicode.

BYTE_LENGTH

BYTE_LENGTH(value)

Descrição

Retorna o comprimento do valor STRING ou BYTES em BYTES, independentemente do tipo de valor ser STRING ou BYTES.

Tipo de retorno

INT64

Exemplos

WITH example AS
  (SELECT "абвгд" AS characters, b"абвгд" AS bytes)

SELECT
  characters,
  BYTE_LENGTH(characters) AS string_example,
  bytes,
  BYTE_LENGTH(bytes) AS bytes_example
FROM example;

+------------+----------------+-------+---------------+
| characters | string_example | bytes | bytes_example |
+------------+----------------+-------+---------------+
| абвгд      | 10             | абвгд | 10            |
+------------+----------------+-------+---------------+

CHAR_LENGTH

CHAR_LENGTH(value)

Descrição

Retorna o comprimento da STRING em caracteres.

Tipo de retorno

INT64

Exemplos

WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  CHAR_LENGTH(characters) AS char_length_example
FROM example;

+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд      |                   5 |
+------------+---------------------+

CHARACTER_LENGTH

CHARACTER_LENGTH(value)

Descrição

Sinônimo para CHAR_LENGTH.

Tipo de retorno

INT64

Exemplos

WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  CHARACTER_LENGTH(characters) AS char_length_example
FROM example;

+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд      |                   5 |
+------------+---------------------+

CODE_POINTS_TO_BYTES

CODE_POINTS_TO_BYTES(ascii_values)

Descrição

Usa uma matriz de pontos de código (em inglês) ASCII estendidos (ARRAY de INT64) e retorna BYTES.

Para converter de BYTES para uma matriz de pontos de código, consulte TO_CODE_POINTS.

Tipo de retorno

BYTES

Exemplos

Veja a seguir um exemplo básico usando CODE_POINTS_TO_BYTES.

SELECT CODE_POINTS_TO_BYTES([65, 98, 67, 100]) AS bytes;

-- Note that the result of CODE_POINTS_TO_BYTES is of type BYTES, displayed as a base64-encoded string.
-- In BYTES format, b'AbCd' is the result.
+----------+
| bytes    |
+----------+
| QWJDZA== |
+----------+

O exemplo a seguir utiliza um algoritmo ROT13 (rotacionar 13 posições) para codificar uma string.

SELECT CODE_POINTS_TO_BYTES(ARRAY(
  (SELECT
      CASE
        WHEN chr BETWEEN b'a' AND b'z'
          THEN TO_CODE_POINTS(b'a')[offset(0)] +
            MOD(code + 13 - TO_CODE_POINTS(b'a')[offset(0)],26)
        WHEN chr BETWEEN b'A' AND b'Z'
          THEN TO_CODE_POINTS(b'A')[offset(0)] +
            MOD(code + 13 - TO_CODE_POINTS(b'A')[offset(0)],26)
        ELSE code
      END
   FROM
     (SELECT code, CODE_POINTS_TO_BYTES(ARRAY[code]) AS chr
      FROM UNNEST(TO_CODE_POINTS(input)) AS code WITH OFFSET
      ORDER BY OFFSET)
   ))) AS encoded_string
FROM UNNEST(ARRAY['Test String!']) AS input;

-- Note that the result of CODE_POINTS_TO_BYTES is of type BYTES, displayed as a base64-encoded string.
-- In BYTES format, b'Grfg Fgevat!' is the result.
+------------------+
| encoded_string   |
+------------------+
| R3JmZyBGZ2V2YXQh |
+------------------+

CODE_POINTS_TO_STRING

CODE_POINTS_TO_STRING(value)

Descrição

Usa uma matriz de pontos de código (em inglês) Unicode (ARRAY de INT64) e retorna um STRING. Se um ponto de código for 0, não será retornado um caractere para ele na STRING.

Para converter uma string em uma matriz de pontos de código, consulte TO_CODE_POINTS.

Tipo de retorno

STRING

Exemplos

Veja a seguir exemplos básicos usando CODE_POINTS_TO_STRING.

SELECT CODE_POINTS_TO_STRING([65, 255, 513, 1024]) AS string;

+--------+
| string |
+--------+
| AÿȁЀ   |
+--------+
SELECT CODE_POINTS_TO_STRING([97, 0, 0xF9B5]) AS string;

+--------+
| string |
+--------+
| a例    |
+--------+
SELECT CODE_POINTS_TO_STRING([65, 255, NULL, 1024]) AS string;

+--------+
| string |
+--------+
| NULL   |
+--------+

O exemplo a seguir calcula a frequência das letras em um grupo de palavras.

WITH Words AS (
  SELECT word
  FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word
)
SELECT
  CODE_POINTS_TO_STRING([code_point]) AS letter,
  COUNT(*) AS letter_count
FROM Words,
  UNNEST(TO_CODE_POINTS(word)) AS code_point
GROUP BY 1
ORDER BY 2 DESC;

+--------+--------------+
| letter | letter_count |
+--------+--------------+
| a      | 5            |
| f      | 3            |
| r      | 2            |
| b      | 2            |
| l      | 2            |
| o      | 2            |
| g      | 1            |
| z      | 1            |
| e      | 1            |
| m      | 1            |
| i      | 1            |
+--------+--------------+

CONCAT

CONCAT(value1[, ...])

Descrição

Concatena um ou mais valores STRING ou BYTE em um único resultado.

A função retornará NULL se algum argumento de entrada for NULL.

Tipo de retorno

STRING ou BYTES

Exemplos

SELECT CONCAT("T.P.", " ", "Bar") as author;

+---------------------+
| author              |
+---------------------+
| T.P. Bar            |
+---------------------+

With Employees AS
  (SELECT
    "John" AS first_name,
    "Doe" AS last_name
  UNION ALL
  SELECT
    "Jane" AS first_name,
    "Smith" AS last_name
  UNION ALL
  SELECT
    "Joe" AS first_name,
    "Jackson" AS last_name)

SELECT
  CONCAT(first_name, " ", last_name)
  AS full_name
FROM Employees;

+---------------------+
| full_name           |
+---------------------+
| John Doe            |
| Jane Smith          |
| Joe Jackson         |
+---------------------+

ENDS_WITH

ENDS_WITH(value1, value2)

Descrição

Utiliza dois valores STRING ou BYTES. Retorna TRUE se o segundo valor for um sufixo do primeiro.

Tipo de retorno

BOOL

Exemplos

WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  ENDS_WITH(item, "e") as example
FROM items;

+---------+
| example |
+---------+
|    True |
|   False |
|    True |
+---------+

FORMAT

O Cloud Spanner SQL é compatível com FORMAT() para formatação de strings. Essa função é semelhante à função C printf. Ela produz uma STRING a partir de uma string de formato que contém zero ou mais especificadores de formato e uma lista de comprimentos variáveis de argumentos extras que correspondem aos especificadores. Veja alguns exemplos:

Descrição Instrução Resultado
Inteiro simples FORMAT("%d", 10) 10
Inteiro com preenchimento vazio à esquerda FORMAT("|%10d|", 11) |           11|
Inteiro com preenchimento com zero à esquerda FORMAT("+%010d+", 12) +0000000012+
Inteiro com vírgulas FORMAT("%'d", 123456789) 123,456,789
STRING FORMAT("-%s-", 'abcd efg') -abcd efg-
FLOAT64 FORMAT("%f %E", 1.1, 2.2) 1.100000 2.200000E+00
DATA FORMAT("%t", date "2015-09-01") 2015-09-01
TIMESTAMP FORMAT("%t", timestamp "2015-09-01 12:34:56 America/Los_Angeles") 2015‑09‑01 19:34:56+00

A função FORMAT() não oferece formato totalmente personalizável para todos os tipos e valores, nem formato sensível à localidade.

Se o formato personalizado for necessário para um tipo, primeiro formate-o usando as funções específicas do tipo, como FORMAT_DATE() ou FORMAT_TIMESTAMP(). Exemplo:

SELECT FORMAT("date: %s!", FORMAT_DATE("%B %d, %Y", date '2015-01-02'));

Retorna

date: January 02, 2015!

Sintaxe

A sintaxe FORMAT() usa uma lista de strings de formato e comprimentos variáveis dos argumentos e produz um resultado STRING:

FORMAT(format_string, ...)

A expressão format_string pode conter zero ou mais especificadores de formato. Cada especificador de formato é introduzido pelo símbolo % e precisa ser mapeado para um ou mais dos argumentos restantes. Em geral, esse é um mapeamento um para um, exceto quando o especificador * está presente. Por exemplo, %.*i realiza o mapeamento para dois argumentos: um de comprimento e um de número inteiro com sinal. Se o número de argumentos relacionados com os especificadores de formato não for o mesmo número de argumentos, ocorrerá um erro.

Especificadores de formatos compatíveis

O especificador de formato da função FORMAT() segue este protótipo:

%[flags][width][.precision]specifier

Os especificadores de formatos compatíveis são identificados na tabela a seguir. Os desvios de printf() são identificados em itálico.

Especificador Descrição Exemplos Tipos
d ou i Decimal inteiro. 392 INT64
o Octal 610
INT64*
x Inteiro hexadecimal 7fa
INT64*
X Inteiro hexadecimal (maiúsculas) 7FA
INT64*
f Notação decimal, em [-](parte inteira).(parte fracionária) para valores finitos e em minúsculas para valores não finitos 392.650000
inf
nan
NUMERIC
FLOAT64
F Notação decimal, em [-](parte inteira).(parte fracionária) para valores finitos e em maiúsculas para valores não finitos 392.650000
INF
NAN
NUMERIC
FLOAT64
e Notação científica (mantissa/expoente), minúsculas 3,926500e+02
inf
nan
NUMERIC
FLOAT64
E Notação científica (mantissa/expoente), maiúsculas 3,926500E+02
INF
NAN
NUMERIC
FLOAT64
g Notação decimal ou notação científica, dependendo do expoente do valor de entrada e da precisão especificada. Minúscula. Consulte Comportamento de%g e %G para detalhes. 392,65
3,9265e+07
inf
nan
NUMERIC
FLOAT64
G Notação decimal ou notação científica, dependendo do expoente do valor de entrada e da precisão especificada. Maiúscula. Consulte Comportamento de%g e %G para detalhes. 392.65
3.9265E+07
INF
NAN
NUMERIC
FLOAT64
s String de caracteres amostra STRING
t Retorna uma string para impressão que representa o valor. Geralmente é semelhante a fazer o cast do argumento para STRING. Consulte Comportamento de %t e %T. amostra
2014‑01‑01
<any>
T Produz uma string que é uma constante válida do Cloud Spanner SQL, com um tipo semelhante ao do valor (talvez mais amplo ou string). Consulte Comportamento de %t e %T. 'amostra'
amostra b'bytes '
1234
2.3
data '2014‑01‑01'
<any>
% '%%' produz um único '%' % n/a

* Os especificadores %o, %x e %X geram um erro se valores negativos forem usados.

O formato do especificador também pode conter os subespecificadores identificados acima no protótipo do especificador.

Esses subespecificadores precisam seguir estas especificações.

Sinalizações
Sinalizações Descrição
- Justificada à esquerda dentro da largura do campo. O padrão é a justificação à direita (informe-se sobre subespecificador de largura).
+ Força o resultado a ser precedido por um sinal de mais ou menos (+ ou -), mesmo para números positivos. Por padrão, apenas os números negativos são precedidos por um sinal -
<space> Se nenhum sinal for gravado, um espaço em branco será inserido antes do valor
#
  • Para "%o", "%x" e "%X", essa sinalização significa preceder valores diferentes de zero com 0, 0x ou 0X, respectivamente.
  • Para "%f", "%F", "%e" e "%E", essa sinalização significa adicionar o ponto decimal mesmo quando não houver parte fracionária, a menos que o valor não seja finito.
  • Para "%g" e "%G", essa sinalização significa adicionar o ponto decimal mesmo quando não houver parte fracionária, a menos que o valor seja não finito e nunca remova os zeros à direita do ponto decimal.
0 Quando o preenchimento é especificado, coloca zeros (0) à esquerda do número, em vez de espaços (consulte sobre subespecificador de largura)
'

Formata inteiros usando o caractere de agrupamento adequado. Exemplo:

  • FORMAT("%'d", 12345678) retorna 12,345,678.
  • FORMAT("%'x", 12345678) retorna bc:614e
  • FORMAT("%'o", 55555) retorna 15,4403
  • Essa sinalização só é relevante para valores decimais, hexadecimais e octais.

As sinalizações podem ser especificadas em qualquer ordem. Sinalizações duplicadas não são um erro. As sinalizações são ignoradas quando não são relevantes para algum tipo de elemento.

Largura
Largura Descrição
<número> O número mínimo de caracteres a ser impresso. Se o valor a ser impresso for mais curto do que esse número, o resultado será preenchido com espaços em branco. O valor não é truncado mesmo que o resultado seja maior
* A largura não é especificada na string de formato, mas como um argumento de valor inteiro complementar precedendo o argumento que precisa ser formatado
Precisão
Precisão Descrição
.<number>
  • Para especificadores inteiros "%d", "%i", "%o", "%u", "%x" e "%X": a precisão especifica o número mínimo de dígitos a serem gravados. Se o valor a ser gravado for menor que esse número, o resultado será preenchido com zeros à direita. O valor não será truncado mesmo que o resultado seja maior. Uma precisão de 0 significa que nenhum caractere é gravado para o valor 0.
  • Para os especificadores "%a", "%A", "%e", "%E", "%f" e "%F": esse é o número de dígitos a serem impressos após o ponto decimal. O valor padrão é 6.
  • Para especificadores "%g" e "%G": este é o número de dígitos significativos a serem impressos, antes da remoção dos zeros à direita após o ponto decimal. O valor padrão é 6.
.* A precisão não é especificada na string de formato, mas como um argumento de valor inteiro extra precedendo o argumento que precisa ser formatado.

Comportamento de %g e %G

Os especificadores de formato %g e %G escolhem a notação decimal (como os especificadores %f e %F) ou a notação científica (como os especificadores %e e %E), dependendo do expoente do valor de entrada e da precisão especificada.

Deixe p representar a precisão especificada (o padrão é 6; mas será 1 se a precisão especificada for menor que 1). O valor de entrada é convertido primeiro em notação científica com precisão = (p - 1). Se a parte x do expoente resultante for menor que -4 ou menor que p, a notação científica com precisão = (p - 1) será usada. Caso contrário, a notação decimal com precisão = (p - 1 - x) é usada.

A menos que a sinalização # esteja presente, os zeros à direita após a vírgula decimal serão removidos, e a vírgula decimal também será removida se não houver um dígito depois dela.

Comportamento de %t e %T

Os especificadores de formato %t e %T são definidos para todos os tipos. A largura, a precisão e as sinalizações agem da mesma forma que para %s: a largura é a largura mínima e a STRING será preenchida com esse tamanho. Precisão é a largura máxima do conteúdo a ser exibido e a STRING será truncada com esse tamanho, antes de preencher a largura.

O especificador %t é sempre uma forma legível do valor.

O especificador %T é sempre um literal SQL válido de um tipo semelhante, como um tipo numérico mais amplo. O literal não inclui casts ou um nome de tipo, exceto no caso especial de valores de ponto flutuante não finitos.

O STRING é formatado da seguinte forma:

Tipo %t %T
NULL de qualquer tipo NULL NULL
INT64
123 123
NUMERIC 123.0 (sempre com .0)NUMERIC "123,0"
FLOAT64 123.0 (sempre com .0)
123e+10
inf
-inf
NaN
123.0 (sempre com .0)
123e+10
CAST("inf" AS <type>)
CAST("-inf" AS <type>)
CAST("nan" AS <type>)
STRING valor da string sem aspas literal da string com aspas
BYTES bytes escapados sem aspas
por exemplo, abc\x01\x02
bytes literais com aspas
por exemplo, b"abc\x01\x02"
DATA 2011-02-03 DATE "2011-02-03"
TIMESTAMP 2011-02-03 04:05:06+00 TIMESTAMP "2011-02-03 04:05:06+00"
ARRAY [valor, valor, ...]
onde os valores são formatados com %t
[valor, valor, ...]
onde os valores são formatados com %T

Condições de erro

Se um especificador de formato for inválido ou não compatível com o tipo de argumento relacionado, ou forem fornecidos o número ou os argumentos incorretos, ocorrerá um erro. Por exemplo, as seguintes expressões <format_string> são inválidas:

FORMAT('%s', 1)
FORMAT('%')

Tratamento do argumento NULL

Uma string de formato NULL resulta em uma saída STRING NULL. Outros argumentos são ignorados, neste caso.

Em geral, a função produz um valor NULL se um argumento NULL estiver presente. Por exemplo, FORMAT('%i', NULL_expression) produz um NULL STRING como saída.

No entanto, há algumas exceções: se o especificador de formato for %t ou %T (que produzem STRINGs que efetivamente correspondem à semântica do valor literal e CAST), um valor NULL produzirá 'NULL' (sem as aspas) na STRING do resultado. Por exemplo, a função:

FORMAT('00-%t-00', NULL_expression);

Retorna

00-NULL-00

Outras regras semânticas

Os valores FLOAT64 podem ser +/-inf ou NaN. Quando um argumento tem um desses valores, o resultado dos especificadores de formato %f, %F, %e, %E, %g, %G e %t são inf, -inf ou nan (ou o mesmo em maiúsculas), conforme apropriado. Isso é consistente com a forma como o Cloud Spanner SQL faz o cast desses valores para STRING. Para %T, o Cloud Spanner SQL retorna strings entre aspas para valores FLOAT64 que não têm representações literais que não sejam de string.

FROM_BASE32

FROM_BASE32(string_expr)

Descrição

Converte no formato BYTES a entrada codificada em base32 string_expr. Para converter BYTES em uma STRING codificada em base32, use TO_BASE32.

Tipo de retorno

BYTES

Exemplo

SELECT FROM_BASE32('MFRGGZDF74======') AS byte_data;

-- Note that the result of FROM_BASE32 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| YWJjZGX/  |
+-----------+

FROM_BASE64

FROM_BASE64(string_expr)

Descrição

Converte no formato BYTES a entrada string_expr codificada em base64. Para converter BYTES em uma STRING codificada em base64, use TO_BASE64.

Há várias codificações base64 em uso comum que variam exatamente no alfabeto de 65 caracteres ASCII usado para codificar os 64 dígitos e o espaçamento. Consulte RFC 4648 para detalhes. Essa função espera o alfabeto [A-Za-z0-9+/=].

Tipo de retorno

BYTES

Exemplo

SELECT FROM_BASE64('/+A=') AS byte_data;

-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| /+A=      |
+-----------+

Para trabalhar com uma codificação usando um alfabeto base64 diferente, talvez seja necessário escrever FROM_BASE64 com a função REPLACE. Por exemplo, a codificação segura de nome de arquivo e URL base64url usada com frequência na programação da Web usa -_= como os últimos caracteres em vez de +/=. Para decodificar uma string codificada em base64url, substitua + e / por - e _, respectivamente.

SELECT FROM_BASE64(REPLACE(REPLACE("_-A=", "-", "+"), "_", "/")) AS binary;

-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string.
+--------+
| binary |
+--------+
| /+A=   |
+--------+

FROM_HEX

FROM_HEX(string)

Descrição

Converte um STRING codificado em hexadecimal em formato BYTES. Retorna um erro se a STRING de entrada contiver caracteres fora do intervalo (0..9, A..F, a..f). Não importa se os caracteres estão em maiúsculas ou minúsculas. Se a entrada STRING tiver um número ímpar de caracteres, a função age como se a entrada tivesse um 0 à esquerda. Para converter BYTES em uma STRING codificada em hexadecimal, use TO_HEX.

Tipo de retorno

BYTES

Exemplo

WITH Input AS (
  SELECT '00010203aaeeefff' AS hex_str UNION ALL
  SELECT '0AF' UNION ALL
  SELECT '666f6f626172'
)
SELECT hex_str, FROM_HEX(hex_str) AS bytes_str
FROM Input;

-- Note that the result of FROM_HEX is of type BYTES, displayed as a base64-encoded string.
+------------------+--------------+
| hex_str          | bytes_str    |
+------------------+--------------+
| 0AF              | AAECA6ru7/8= |
| 00010203aaeeefff | AK8=         |
| 666f6f626172     | Zm9vYmFy     |
+------------------+--------------+

LENGTH

LENGTH(value)

Descrição

Retorna o comprimento do valor STRING ou BYTES. O valor retornado está em caracteres para argumentos STRING e em bytes para o argumento BYTES.

Tipo de retorno

INT64

Exemplos


WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  LENGTH(characters) AS string_example,
  LENGTH(CAST(characters AS BYTES)) AS bytes_example
FROM example;

+------------+----------------+---------------+
| characters | string_example | bytes_example |
+------------+----------------+---------------+
| абвгд      |              5 |            10 |
+------------+----------------+---------------+

LPAD

LPAD(original_value, return_length[, pattern])

Descrição

Retorna um valor STRING ou BYTES que consiste em original_value precedido por pattern. O return_length é um INT64 que especifica o comprimento do valor retornado. Se original_value for do tipo BYTES, return_length será o número de bytes. Se original_value for do tipo STRING, return_length será o número de caracteres.

O valor padrão de pattern é um espaço em branco.

Tanto original_value quanto pattern precisam ser do mesmo tipo de dados.

Se return_length for menor ou igual ao comprimento de original_value, essa função retornará o valor original_value, truncado para o valor de return_length. Por exemplo, LPAD("hello world", 7); retorna "hello w".

Se original_value, return_length ou pattern for NULL, essa função retornará NULL.

Essa função retornará um erro se:

  • return_length for negativo;
  • pattern estiver vazio.

Tipo de retorno

STRING ou BYTES

Exemplos

SELECT t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
  STRUCT('abc' AS t, 5 AS len),
  ('abc', 2),
  ('例子', 4)
]);

+------+-----+----------+
| t    | len | LPAD     |
|------|-----|----------|
| abc  | 5   | "  abc"  |
| abc  | 2   | "ab"     |
| 例子  | 4   | "  例子" |
+------+-----+----------+
SELECT t, len, pattern, FORMAT("%T", LPAD(t, len, pattern)) AS LPAD FROM UNNEST([
  STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
  ('abc', 5, '-'),
  ('例子', 5, '中文')
]);

+------+-----+---------+--------------+
| t    | len | pattern | LPAD         |
|------|-----|---------|--------------|
| abc  | 8   | def     | "defdeabc"   |
| abc  | 5   | -       | "--abc"      |
| 例子  | 5   | 中文    | "中文中例子"   |
+------+-----+---------+--------------+
SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
  STRUCT(b'abc' AS t, 5 AS len),
  (b'abc', 2),
  (b'\xab\xcd\xef', 4)
]);

+-----------------+-----+------------------+
| t               | len | LPAD             |
|-----------------|-----|------------------|
| b"abc"          | 5   | b"  abc"         |
| b"abc"          | 2   | b"ab"            |
| b"\xab\xcd\xef" | 4   | b" \xab\xcd\xef" |
+-----------------+-----+------------------+
SELECT
  FORMAT("%T", t) AS t,
  len,
  FORMAT("%T", pattern) AS pattern,
  FORMAT("%T", LPAD(t, len, pattern)) AS LPAD
FROM UNNEST([
  STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
  (b'abc', 5, b'-'),
  (b'\xab\xcd\xef', 5, b'\x00')
]);

+-----------------+-----+---------+-------------------------+
| t               | len | pattern | LPAD                    |
|-----------------|-----|---------|-------------------------|
| b"abc"          | 8   | b"def"  | b"defdeabc"             |
| b"abc"          | 5   | b"-"    | b"--abc"                |
| b"\xab\xcd\xef" | 5   | b"\x00" | b"\x00\x00\xab\xcd\xef" |
+-----------------+-----+---------+-------------------------+

LOWER

LOWER(value)

Descrição

Para argumentos STRING, retorna a string original com todos os caracteres alfabéticos em minúsculas. O mapeamento entre maiúsculas e minúsculas é feito de acordo com o banco de dados de caracteres Unicode. Os mapeamentos específicos do idioma não são considerados.

Para argumentos BYTES, o argumento é tratado como texto ASCII, com todos os bytes superiores a 127 intactos.

Tipo de retorno

STRING ou BYTES

Exemplos


WITH items AS
  (SELECT
    "FOO" as item
  UNION ALL
  SELECT
    "BAR" as item
  UNION ALL
  SELECT
    "BAZ" as item)

SELECT
  LOWER(item) AS example
FROM items;

+---------+
| example |
+---------+
| foo     |
| bar     |
| baz     |
+---------+

LTRIM

LTRIM(value1[, value2])

Descrição

Idêntico a TRIM, mas remove apenas os caracteres iniciais.

Tipo de retorno

STRING ou BYTES

Exemplos

WITH items AS
  (SELECT "   apple   " as item
  UNION ALL
  SELECT "   banana   " as item
  UNION ALL
  SELECT "   orange   " as item)

SELECT
  CONCAT("#", LTRIM(item), "#") as example
FROM items;

+-------------+
| example     |
+-------------+
| #apple   #  |
| #banana   # |
| #orange   # |
+-------------+
WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  LTRIM(item, "*") as example
FROM items;

+-----------+
| example   |
+-----------+
| apple***  |
| banana*** |
| orange*** |
+-----------+
WITH items AS
  (SELECT "xxxapplexxx" as item
  UNION ALL
  SELECT "yyybananayyy" as item
  UNION ALL
  SELECT "zzzorangezzz" as item
  UNION ALL
  SELECT "xyzpearxyz" as item)
SELECT
  LTRIM(item, "xyz") as example
FROM items;

+-----------+
| example   |
+-----------+
| applexxx  |
| bananayyy |
| orangezzz |
| pearxyz   |
+-----------+

REGEXP_CONTAINS

REGEXP_CONTAINS(value, regexp)

Descrição

Retorna TRUE se value for uma correspondência parcial para a expressão regular, regexp.

Se o argumento regexp for inválido, a função retornará um erro.

É possível pesquisar uma correspondência total usando ^ (início do texto) e $ (fim do texto). Devido à precedência do operador de expressão regular, é recomendável usar parênteses em torno de tudo entre ^ e $.

Tipo de retorno

BOOL

Exemplos

SELECT
  email,
  REGEXP_CONTAINS(email, r"@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+") AS is_valid
FROM
  (SELECT
    ["foo@example.com", "bar@example.org", "www.example.net"]
    AS addresses),
  UNNEST(addresses) AS email;

+-----------------+----------+
| email           | is_valid |
+-----------------+----------+
| foo@example.com | true     |
| bar@example.org | true     |
| www.example.net | false    |
+-----------------+----------+

# Performs a full match, using ^ and $. Due to regular expression operator
# precedence, it is good practice to use parentheses around everything between ^
# and $.
SELECT
  email,
  REGEXP_CONTAINS(email, r"^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$")
    AS valid_email_address,
  REGEXP_CONTAINS(email, r"^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$")
    AS without_parentheses
FROM
  (SELECT
    ["a@foo.com", "a@foo.computer", "b@bar.org", "!b@bar.org", "c@buz.net"]
    AS addresses),
  UNNEST(addresses) AS email;

+----------------+---------------------+---------------------+
| email          | valid_email_address | without_parentheses |
+----------------+---------------------+---------------------+
| a@foo.com      | true                | true                |
| a@foo.computer | false               | true                |
| b@bar.org      | true                | true                |
| !b@bar.org     | false               | true                |
| c@buz.net      | false               | false               |
+----------------+---------------------+---------------------+

REGEXP_EXTRACT

REGEXP_EXTRACT(value, regexp)

Descrição

Retorna a primeira substring em value que corresponde à expressão regular, regexp. Retorna NULL se não houver correspondência.

Se a expressão regular contiver um grupo de captura, a função retornará a substring que corresponde a esse grupo. Se a expressão não contiver um grupo de captura, a função retornará toda a substring correspondente.

Retorna um erro se:

  • a expressão regular for inválida;
  • a expressão regular tiver mais de um grupo de captura;

Tipo de retorno

STRING ou BYTES

Exemplos

WITH email_addresses AS
  (SELECT "foo@example.com" as email
  UNION ALL
  SELECT "bar@example.org" as email
  UNION ALL
  SELECT "baz@example.net" as email)

SELECT
  REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+")
  AS user_name
FROM email_addresses;

+-----------+
| user_name |
+-----------+
| foo       |
| bar       |
| baz       |
+-----------+
WITH email_addresses AS
  (SELECT "foo@example.com" as email
  UNION ALL
  SELECT "bar@example.org" as email
  UNION ALL
  SELECT "baz@example.net" as email)

SELECT
  REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.([a-zA-Z0-9-.]+$)")
  AS top_level_domain
FROM email_addresses;

+------------------+
| top_level_domain |
+------------------+
| com              |
| org              |
| net              |
+------------------+

REGEXP_EXTRACT_ALL

REGEXP_EXTRACT_ALL(value, regexp)

Descrição

Retorna uma matriz de todas as substrings de value que correspondem à expressão regular, regexp.

A função REGEXP_EXTRACT_ALL retorna apenas as correspondências não sobrepostas. Por exemplo, o uso dessa função para extrair ana de banana retorna apenas uma substring, não duas.

Tipo de retorno

Uma ARRAY de STRINGs ou BYTES.

Exemplos

WITH code_markdown AS
  (SELECT "Try `function(x)` or `function(y)`" as code)

SELECT
  REGEXP_EXTRACT_ALL(code, "`(.+?)`") AS example
FROM code_markdown;

+----------------------------+
| example                    |
+----------------------------+
| [function(x), function(y)] |
+----------------------------+

REGEXP_REPLACE

REGEXP_REPLACE(value, regexp, replacement)

Descrição

Retorna uma STRING em que todas as substrings de value que correspondem à expressão regular regexp são substituídas por replacement.

Use dígitos de escape com barra invertida (\1 a \9) no argumento replacement para inserir um texto correspondente ao grupo entre parênteses no padrão regexp. Use \0 para indicar o texto correspondente inteiro.

A função REGEXP_REPLACE substitui apenas as correspondências não sobrepostas. Por exemplo, substituir ana em banana resulta em apenas uma substituição, não duas.

Se o argumento regexp não for uma expressão regular válida, essa função retornará um erro.

Tipo de retorno

STRING ou BYTES

Exemplos

WITH markdown AS
  (SELECT "# Heading" as heading
  UNION ALL
  SELECT "# Another heading" as heading)

SELECT
  REGEXP_REPLACE(heading, r"^# ([a-zA-Z0-9\s]+$)", "<h1>\\1</h1>")
  AS html
FROM markdown;

+--------------------------+
| html                     |
+--------------------------+
| <h1>Heading</h1>         |
| <h1>Another heading</h1> |
+--------------------------+

REPLACE

REPLACE(original_value, from_value, to_value)

Descrição

Substitui todas as ocorrências de from_value por to_value em original_value. Se from_value estiver vazio, nenhuma substituição será feita.

Tipo de retorno

STRING ou BYTES

Exemplos

WITH desserts AS
  (SELECT "apple pie" as dessert
  UNION ALL
  SELECT "blackberry pie" as dessert
  UNION ALL
  SELECT "cherry pie" as dessert)

SELECT
  REPLACE (dessert, "pie", "cobbler") as example
FROM desserts;

+--------------------+
| example            |
+--------------------+
| apple cobbler      |
| blackberry cobbler |
| cherry cobbler     |
+--------------------+

REPEAT

REPEAT(original_value, repetitions)

Descrição

Retorna um valor STRING ou BYTES que consiste em original_value repetido. O parâmetro repetitions especifica o número de vezes para repetir original_value. Retorna NULL se original_value ou repetitions forem NULL.

Essa função retornará um erro se o valor repetitions for negativo.

Tipo de retorno

STRING ou BYTES

Exemplos

SELECT t, n, REPEAT(t, n) AS REPEAT FROM UNNEST([
  STRUCT('abc' AS t, 3 AS n),
  ('例子', 2),
  ('abc', null),
  (null, 3)
]);

+------+------+-----------+
| t    | n    | REPEAT    |
|------|------|-----------|
| abc  | 3    | abcabcabc |
| 例子 | 2    | 例子例子  |
| abc  | NULL | NULL      |
| NULL | 3    | NULL      |
+------+------+-----------+

INVERSO

REVERSE(value)

Descrição

Retorna o inverso de entrada STRING ou BYTES.

Tipo de retorno

STRING ou BYTES

Exemplos

WITH example AS (
  SELECT "foo" AS sample_string, b"bar" AS sample_bytes UNION ALL
  SELECT "абвгд" AS sample_string, b"123" AS sample_bytes
)
SELECT
  sample_string,
  REVERSE(sample_string) AS reverse_string,
  sample_bytes,
  REVERSE(sample_bytes) AS reverse_bytes
FROM example;

+---------------+----------------+--------------+---------------+
| sample_string | reverse_string | sample_bytes | reverse_bytes |
+---------------+----------------+--------------+---------------+
| foo           | oof            | bar          | rab           |
| абвгд         | дгвба          | 123          | 321           |
+---------------+----------------+--------------+---------------+

RPAD

RPAD(original_value, return_length[, pattern])

Descrição

Retorna um valor STRING ou BYTES que consiste em original_value anexado a pattern. O parâmetro return_length é um INT64 que especifica o comprimento do valor retornado. Se original_value for BYTES, return_length é o número de bytes. Se original_value for STRING, return_length será o número de caracteres.

O valor padrão de pattern é um espaço em branco.

Tanto original_value quanto pattern precisam ser do mesmo tipo de dados.

Se return_length for menor ou igual ao comprimento de original_value, essa função retornará o valor original_value, truncado para o valor de return_length. Por exemplo, RPAD("hello world", 7); retorna "hello w".

Se original_value, return_length ou pattern for NULL, essa função retornará NULL.

Essa função retornará um erro se:

  • return_length for negativo;
  • pattern estiver vazio.

Tipo de retorno

STRING ou BYTES

Exemplos

SELECT t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
  STRUCT('abc' AS t, 5 AS len),
  ('abc', 2),
  ('例子', 4)
]);

+------+-----+----------+
| t    | len | RPAD     |
|------|-----|----------|
| abc  | 5   | "abc  "  |
| abc  | 2   | "ab"     |
| 例子  | 4   | "例子  " |
+------+-----+----------+
SELECT t, len, pattern, FORMAT("%T", RPAD(t, len, pattern)) AS RPAD FROM UNNEST([
  STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
  ('abc', 5, '-'),
  ('例子', 5, '中文')
]);

+------+-----+---------+--------------+
| t    | len | pattern | RPAD         |
|------|-----|---------|--------------|
| abc  | 8   | def     | "abcdefde"   |
| abc  | 5   | -       | "abc--"      |
| 例子  | 5   | 中文     | "例子中文中"  |
+------+-----+---------+--------------+
SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
  STRUCT(b'abc' AS t, 5 AS len),
  (b'abc', 2),
  (b'\xab\xcd\xef', 4)
]);

+-----------------+-----+------------------+
| t               | len | RPAD             |
|-----------------|-----|------------------|
| b"abc"          | 5   | b"abc  "         |
| b"abc"          | 2   | b"ab"            |
| b"\xab\xcd\xef" | 4   | b"\xab\xcd\xef " |
+-----------------+-----+------------------+
SELECT
  FORMAT("%T", t) AS t,
  len,
  FORMAT("%T", pattern) AS pattern,
  FORMAT("%T", RPAD(t, len, pattern)) AS RPAD
FROM UNNEST([
  STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
  (b'abc', 5, b'-'),
  (b'\xab\xcd\xef', 5, b'\x00')
]);

+-----------------+-----+---------+-------------------------+
| t               | len | pattern | RPAD                    |
|-----------------|-----|---------|-------------------------|
| b"abc"          | 8   | b"def"  | b"abcdefde"             |
| b"abc"          | 5   | b"-"    | b"abc--"                |
| b"\xab\xcd\xef" | 5   | b"\x00" | b"\xab\xcd\xef\x00\x00" |
+-----------------+-----+---------+-------------------------+

RTRIM

RTRIM(value1[, value2])

Descrição

Idêntico a TRIM, mas remove somente caracteres finais.

Tipo de retorno

STRING ou BYTES

Exemplos

WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  RTRIM(item, "*") as example
FROM items;

+-----------+
| example   |
+-----------+
| ***apple  |
| ***banana |
| ***orange |
+-----------+
WITH items AS
  (SELECT "applexxx" as item
  UNION ALL
  SELECT "bananayyy" as item
  UNION ALL
  SELECT "orangezzz" as item
  UNION ALL
  SELECT "pearxyz" as item)

SELECT
  RTRIM(item, "xyz") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
| pear    |
+---------+

SAFE_CONVERT_BYTES_TO_STRING

SAFE_CONVERT_BYTES_TO_STRING(value)

Descrição

Converte uma sequência de BYTES em um STRING. Todos os caracteres UTF-8 inválidos são substituídos pelo caractere de substituição Unicode, U+FFFD.

Tipo de retorno

STRING

Exemplos

A declaração a seguir retorna o caractere de substituição Unicode �.

SELECT SAFE_CONVERT_BYTES_TO_STRING(b'\xc2') as safe_convert;

SPLIT

SPLIT(value[, delimiter])

Descrição

Divide value usando o argumento delimiter.

Para STRING, o delimitador padrão é a vírgula ,.

Para BYTES, especifique um delimitador.

A divisão em um delimitador vazio produz uma matriz de caracteres UTF-8 para valores STRING, e uma matriz de BYTES para valores BYTES.

A divisão de uma STRING vazia retorna uma ARRAY com uma única STRING vazia.

Tipo de retorno

ARRAY do tipo STRING ou ARRAY do tipo BYTES

Exemplos

WITH letters AS
  (SELECT "" as letter_group
  UNION ALL
  SELECT "a" as letter_group
  UNION ALL
  SELECT "b c d" as letter_group)

SELECT SPLIT(letter_group, " ") as example
FROM letters;

+----------------------+
| example              |
+----------------------+
| []                   |
| [a]                  |
| [b, c, d]            |
+----------------------+

STARTS_WITH

STARTS_WITH(value1, value2)

Descrição

Utiliza dois valores STRING ou BYTES. Retorna TRUE se o segundo valor for um prefixo do primeiro.

Tipo de retorno

BOOL

Exemplos

WITH items AS
  (SELECT "foo" as item
  UNION ALL
  SELECT "bar" as item
  UNION ALL
  SELECT "baz" as item)

SELECT
  STARTS_WITH(item, "b") as example
FROM items;

+---------+
| example |
+---------+
|   False |
|    True |
|    True |
+---------+

STRPOS

STRPOS(value1, value2)

Descrição

Utiliza dois valores STRING ou BYTES. Retorna o índice com base em 1 da primeira ocorrência de value2 em value1. Retorna 0 se value2 não for encontrado.

Tipo de retorno

INT64

Exemplos

WITH email_addresses AS
  (SELECT
    "foo@example.com" AS email_address
  UNION ALL
  SELECT
    "foobar@example.com" AS email_address
  UNION ALL
  SELECT
    "foobarbaz@example.com" AS email_address
  UNION ALL
  SELECT
    "quxexample.com" AS email_address)

SELECT
  STRPOS(email_address, "@") AS example
FROM email_addresses;

+---------+
| example |
+---------+
|       4 |
|       7 |
|      10 |
|       0 |
+---------+

SUBSTR

SUBSTR(value, position[, length])

Descrição

Retorna uma substring do valor STRING ou BYTES fornecido. O argumento position é um número inteiro que especifica a posição inicial da substring, com posição = 1 indicando o primeiro caractere ou byte. O argumento length é o número máximo de caracteres para argumentos STRING ou de bytes para argumentos BYTES.

Se position for negativo, a função contará a partir do final de value, com -1 indicando o último caractere.

Se position for uma posição fora do lado esquerdo de STRING (position = 0 ou position < -LENGTH(value)), a função começará a partir da posição = 1. Se length exceder o comprimento de value, a função retornará menos que length caracteres.

Se length for menor que zero, a função retornará um erro.

Tipo de retorno

STRING ou BYTES

Exemplos

WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  SUBSTR(item, 2) as example
FROM items;

+---------+
| example |
+---------+
| pple    |
| anana   |
| range   |
+---------+
WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  SUBSTR(item, 2, 2) as example
FROM items;

+---------+
| example |
+---------+
| pp      |
| an      |
| ra      |
+---------+
WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  SUBSTR(item, -2) as example
FROM items;

+---------+
| example |
+---------+
| le      |
| na      |
| ge      |
+---------+

TO_BASE32

TO_BASE32(bytes_expr)

Descrição

Converte uma sequência de BYTES em um STRING codificado em base32. Para converter uma STRING codificada em base32 em BYTES, use FROM_BASE32.

Tipo de retorno

STRING

Exemplo

SELECT TO_BASE32(b'abcde\xFF') AS base32_string;

+------------------+
| base32_string    |
+------------------+
| MFRGGZDF74====== |
+------------------+

TO_BASE64

TO_BASE64(bytes_expr)

Descrição

Converte uma sequência de BYTES em um STRING codificado em base64. Para converter uma STRING codificada em base64 em BYTES, use FROM_BASE64.

Há várias codificações base64 em uso comum que variam exatamente no alfabeto de 65 caracteres ASCII usado para codificar os 64 dígitos e o espaçamento. Consulte RFC 4648 para detalhes. Esta função adiciona espaçamento e usa o alfabeto [A-Za-z0-9+/=].

Tipo de retorno

STRING

Exemplo

SELECT TO_BASE64(b'\377\340') AS base64_string;

+---------------+
| base64_string |
+---------------+
| /+A=          |
+---------------+

Para trabalhar com uma codificação usando um alfabeto base64 diferente, talvez seja necessário escrever TO_BASE64 com a função REPLACE. Por exemplo, a codificação segura de nome de arquivo e URL base64url usada com frequência na programação da Web usa -_= como os últimos caracteres em vez de +/=. Para codificar uma string codificada em base64url, substitua - e _ por + e /, respectivamente.

SELECT REPLACE(REPLACE(TO_BASE64(b"\377\340"), "+", "-"), "/", "_") as websafe_base64;

+----------------+
| websafe_base64 |
+----------------+
| _-A=           |
+----------------+

TO_CODE_POINTS

TO_CODE_POINTS(value)

Descrição

Usa um valor e retorna uma matriz de INT64.

  • Se value for uma STRING, cada elemento na matriz retornada representará um ponto de código (em inglês). Cada ponto de código está dentro do intervalo de [0, 0xD7FF] e [0xE000, 0x10FFFF].
  • Se value for BYTES, cada elemento na matriz será um valor de caractere ASCII estendido no intervalo de [0, 255].

Para converter uma matriz de pontos de código em STRING ou em BYTES, consulte CODE_POINTS_TO_STRING ou CODE_POINTS_TO_BYTES.

Tipo de retorno

ARRAY de INT64

Exemplos

O exemplo a seguir mostra os pontos de código de cada elemento em uma matriz de palavras.

SELECT word, TO_CODE_POINTS(word) AS code_points
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word;

+---------+------------------------------------+
| word    | code_points                        |
+---------+------------------------------------+
| foo     | [102, 111, 111]                    |
| bar     | [98, 97, 114]                      |
| baz     | [98, 97, 122]                      |
| giraffe | [103, 105, 114, 97, 102, 102, 101] |
| llama   | [108, 108, 97, 109, 97]            |
+---------+------------------------------------+

O exemplo a seguir converte as representações de inteiros de BYTES para os valores de caracteres ASCII correspondentes.

SELECT word, TO_CODE_POINTS(word) AS bytes_value_as_integer
FROM UNNEST([b'\x00\x01\x10\xff', b'\x66\x6f\x6f']) AS word;

+------------------+------------------------+
| word             | bytes_value_as_integer |
+------------------+------------------------+
| \x00\x01\x10\xff | [0, 1, 16, 255]        |
| foo              | [102, 111, 111]        |
+------------------+------------------------+

O exemplo a seguir demonstra a diferença entre os resultados BYTES e STRING.

SELECT TO_CODE_POINTS(b'Ā') AS b_result, TO_CODE_POINTS('Ā') AS s_result;

+------------+----------+
| b_result   | s_result |
+------------+----------+
| [196, 128] | [256]    |
+------------+----------+

O caractere Ā é representado como uma sequência Unicode de dois bytes. Como resultado, a versão BYTES de TO_CODE_POINTS retorna uma matriz com dois elementos, enquanto a versão STRING retorna uma matriz com um único elemento.

TO_HEX

TO_HEX(bytes)

Descrição

Converte uma sequência de BYTES em uma STRING hexadecimal. Converte cada byte na STRING como dois caracteres hexadecimais no intervalo (0..9, a..f). Para converter um STRING codificado em hexadecimal em BYTES, use FROM_HEX.

Tipo de retorno

STRING

Exemplo

WITH Input AS (
  SELECT b'\x00\x01\x02\x03\xAA\xEE\xEF\xFF' AS byte_str UNION ALL
  SELECT b'foobar'
)
SELECT byte_str, TO_HEX(byte_str) AS hex_str
FROM Input;

+----------------------------------+------------------+
| byte_string                      | hex_string       |
+----------------------------------+------------------+
| \x00\x01\x02\x03\xaa\xee\xef\xff | 00010203aaeeefff |
| foobar                           | 666f6f626172     |
+----------------------------------+------------------+

TRIM

TRIM(value1[, value2])

Descrição

Remove todos os caracteres iniciais e finais correspondentes a value2. Se value2 não for especificado, todos os caracteres de espaço em branco iniciais e finais (conforme definido pelo padrão Unicode) são removidos. Se o primeiro argumento for do tipo BYTES, o segundo argumento será obrigatório.

Se value2 tiver mais de um caractere ou byte, a função removerá todos os bytes ou caracteres iniciais ou finais contidos em value2.

Tipo de retorno

STRING ou BYTES

Exemplos

WITH items AS
  (SELECT "   apple   " as item
  UNION ALL
  SELECT "   banana   " as item
  UNION ALL
  SELECT "   orange   " as item)

SELECT
  CONCAT("#", TRIM(item), "#") as example
FROM items;

+----------+
| example  |
+----------+
| #apple#  |
| #banana# |
| #orange# |
+----------+
WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  TRIM(item, "*") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
+---------+
WITH items AS
  (SELECT "xxxapplexxx" as item
  UNION ALL
  SELECT "yyybananayyy" as item
  UNION ALL
  SELECT "zzzorangezzz" as item
  UNION ALL
  SELECT "xyzpearxyz" as item)

SELECT
  TRIM(item, "xyz") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
| pear    |
+---------+

UPPER

UPPER(value)

Descrição

Para argumentos STRING, retorna a string original com todos os caracteres alfabéticos em maiúsculas. O mapeamento entre maiúsculas e minúsculas é feito de acordo com o banco de dados de caracteres Unicode. Os mapeamentos específicos do idioma não são considerados.

Para argumentos BYTES, o argumento é tratado como texto ASCII, com todos os bytes superiores a 127 intactos.

Tipo de retorno

STRING ou BYTES

Exemplos

WITH items AS
  (SELECT
    "foo" as item
  UNION ALL
  SELECT
    "bar" as item
  UNION ALL
  SELECT
    "baz" as item)

SELECT
  UPPER(item) AS example
FROM items;

+---------+
| example |
+---------+
| FOO     |
| BAR     |
| BAZ     |
+---------+

Funções JSON

O Cloud Spanner SQL é compatível com funções que ajudam você a recuperar dados armazenados nas strings formatadas como JSON e funções que ajudam a transformar dados em strings formatadas como JSON.

Visão geral da função

As seguintes funções usam aspas duplas para escapar caracteres JSONPath inválidos: "a.b".

Esse comportamento é consistente com o padrão ANSI.

Função JSON Descrição Tipo de retorno
JSON_QUERY Extrai um valor JSON (como uma matriz ou objeto) ou um valor escalar formatado em JSON (como uma string, número inteiro ou valor booleano). STRING formatado em JSON
JSON_VALUE Extrai um valor escalar. Um valor escalar pode representar uma string, um inteiro ou um booleano. Remove as aspas externas e desfaz o escape dos valores. Retorna um SQL NULL se um valor não escalar for selecionado. STRING

JSON_QUERY

JSON_QUERY(json_string_expr, json_path)

Descrição

Extrai um valor JSON (como uma matriz ou objeto) ou um valor escalar formatado em JSON (como uma string, número inteiro ou valor booleano). Se uma chave JSON usa caracteres JSONPath inválidos, é possível inserir caracteres de escape usando aspas duplas.

  • json_string_expr: uma string formatada em JSON. Exemplo:

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path: o JSONpath. Identifica os valores que você quer obter da string formatada em JSON. Se json_path retornar um null JSON, isso será convertido em um NULL SQL.

Se você quiser incluir valores não escalares, como matrizes na extração, use JSON_QUERY. Se quiser apenas extrair valores escalares, como strings, números inteiros e valores booleanos, use JSON_VALUE.

Tipo de retorno

Um STRING formatado em JSON

Exemplos

SELECT JSON_QUERY(json_text, '$') AS json_text_string
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-----------------------------------------------------------+
| json_text_string                                          |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}}                  |
| {"class":{"students":[]}}                                 |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_QUERY(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-----------------+
| first_student   |
+-----------------+
| {"name":"Jane"} |
| NULL            |
| {"name":"John"} |
+-----------------+
SELECT JSON_QUERY(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| NULL              |
| "Jamie"           |
+-------------------+
SELECT JSON_QUERY(json_text, '$.class."students"') AS student_names
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+------------------------------------+
| student_names                      |
+------------------------------------+
| [{"name":"Jane"}]                  |
| []                                 |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+

JSON_VALUE

JSON_VALUE(json_string_expr, json_path)

Descrição

Extrai um valor escalar e o retorna como uma string. Um valor escalar pode representar uma string, um inteiro ou um booleano. Remove as aspas mais externas e desfaz o escape para os valores de retorno. Se uma chave JSON usa caracteres JSONPath inválidos, é possível inserir caracteres de escape usando aspas duplas.

  • json_string_expr: uma string formatada em JSON. Exemplo:

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path: o JSONpath. Identifica os valores que você quer obter da string formatada em JSON. Se json_path retorna um null JSON ou um valor não escalar (em outras palavras, se json_path se refere a um objeto ou uma matriz), um NULL SQL é retornado.

Se quiser apenas extrair valores escalares, como strings, números inteiros e valores booleanos, use JSON_VALUE. Se você quiser incluir valores não escalares, como matrizes na extração, use JSON_QUERY.

Tipo de retorno

STRING

Exemplos

SELECT JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.name') AS json_name,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.name') AS scalar_name,
  JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.age') AS json_age,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;

+-----------+-------------+----------+------------+
| json_name | scalar_name | json_age | scalar_age |
+-----------+-------------+----------+------------+
| "Jakob"   | Jakob       | "6"      | 6          |
+-----------+-------------+----------+------------+

SELECT JSON_QUERY('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_query,
  JSON_VALUE('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_value;

+--------------------+------------+
| json_query         | json_value |
+--------------------+------------+
| ["apple","banana"] | NULL       |
+--------------------+------------+

Quando a chave JSON usar caracteres inválidos do JSONPath, use aspas duplas para inserir caracteres de escape. Exemplo:

SELECT JSON_VALUE('{"a.b": {"c": "world"}}', '$."a.b".c') AS hello;

+-------+
| hello |
+-------+
| world |
+-------+

JSONPath

A maioria das funções JSON transmitem em um parâmetro json_string_expr e json_path. O parâmetro json_string_expr transmite uma string formatada em JSON, e o parâmetro json_path identifica os valores que você quer obter da string formatada em JSON.

É necessário que o parâmetro json_string_expr seja uma string JSON formatada dessa forma:

{"class" : {"students" : [{"name" : "Jane"}]}}

O parâmetro json_path é construído usando o formato JSONPath. Como parte desse formato, é necessário que o parâmetro inicie com o símbolo $, que indica o nível mais externo da string formatada em JSON. É possível identificar valores filho usando pontos. Se o objeto JSON for uma matriz, use colchetes para especificar o índice da matriz. Se as chaves tiverem $, pontos ou colchetes, consulte sobre como inserir caracteres de escape para cada função JSON.

JSONPath Descrição Exemplo Resultado usando o json_string_expr acima
$ Objeto ou elemento raiz "$" {"class":{"students":[{"name":"Jane"}]}}
. Operador filho "$.class.students" [{"name":"Jane"}]
[] Operador subscrito "$.class.students[0]" {"name":"Jane"}

Uma função JSON retornará NULL se o parâmetro json_path não corresponder a um valor em json_string_expr. Se o valor selecionado para uma função escalar não for escalar, como um objeto ou uma matriz, a função retornará NULL.

Se o JSONPath for inválido, a função gera um erro.

Funções de matriz

ARRAY

ARRAY(subquery)

Descrição

A função ARRAY retorna uma ARRAY com um elemento para cada linha em uma subconsulta.

Se subquery produzir uma tabela SQL, a tabela precisará ter exatamente uma coluna. Cada elemento na ARRAY de saída é o valor da coluna única de uma linha na tabela.

Se subquery produzir uma tabela de valores, cada elemento na ARRAY de saída será toda a linha correspondente da tabela de valores.

Restrições

  • As subconsultas não são ordenadas, portanto, não há garantia de que os elementos da ARRAY de saída preservem qualquer ordem na tabela de origem para a subconsulta. No entanto, se a subconsulta incluir uma cláusula ORDER BY, a função ARRAY retornará um ARRAY que honre essa cláusula.
  • Se a subconsulta retornar mais de uma coluna, a função ARRAY retornará um erro.
  • Se a subconsulta retornar uma coluna de tipo ARRAY ou linhas de tipo ARRAY, a função ARRAY vai retornar um erro: Cloud Spanner SQL não é compatível com ARRAYs com elementos do tipo ARRAY.
  • Se a subconsulta não retornar nenhuma linha, a função ARRAY retornará uma ARRAY vazia. Ela nunca retorna uma NULL ARRAY.

Tipo de retorno

ARRAY

Exemplos

SELECT ARRAY
  (SELECT 1 UNION ALL
   SELECT 2 UNION ALL
   SELECT 3) AS new_array;

+-----------+
| new_array |
+-----------+
| [1, 2, 3] |
+-----------+

Para construir um ARRAY a partir de uma subconsulta que contenha várias colunas, altere a subconsulta para usar SELECT AS STRUCT. Agora a função ARRAY retornará ARRAY de STRUCTs. A ARRAY conterá um STRUCT para cada linha na subconsulta e cada um destes STRUCTs conterá um campo para cada coluna nessa linha.

SELECT
  ARRAY
    (SELECT AS STRUCT 1, 2, 3
     UNION ALL SELECT AS STRUCT 4, 5, 6) AS new_array;

+------------------------+
| new_array              |
+------------------------+
| [{1, 2, 3}, {4, 5, 6}] |
+------------------------+

Da mesma forma, para construir um ARRAY a partir de uma subconsulta que contenha um ou mais ARRAYs, altere a subconsulta para usar SELECT AS STRUCT.

SELECT ARRAY
  (SELECT AS STRUCT [1, 2, 3] UNION ALL
   SELECT AS STRUCT [4, 5, 6]) AS new_array;

+----------------------------+
| new_array                  |
+----------------------------+
| [{[1, 2, 3]}, {[4, 5, 6]}] |
+----------------------------+

ARRAY_CONCAT

ARRAY_CONCAT(array_expression_1 [, array_expression_n])

Descrição

Concatena uma ou mais matrizes com o mesmo tipo de elemento em uma única matriz.

Tipo de retorno

ARRAY

Exemplos

SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;

+--------------------------------------------------+
| count_to_six                                     |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6]                               |
+--------------------------------------------------+

ARRAY_LENGTH

ARRAY_LENGTH(array_expression)

Descrição

Retorna o tamanho da matriz. Retorna 0 para uma matriz vazia. Retorna NULL se array_expression for NULL.

Tipo de retorno

INT64

Exemplos

WITH items AS
  (SELECT ["coffee", NULL, "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)
SELECT ARRAY_TO_STRING(list, ', ', 'NULL'), ARRAY_LENGTH(list) AS size
FROM items
ORDER BY size DESC;

+---------------------------------+------+
| list                            | size |
+---------------------------------+------+
| [coffee, NULL, milk]            | 3    |
| [cake, pie]                     | 2    |
+---------------------------------+------+

ARRAY_TO_STRING

ARRAY_TO_STRING(array_expression, delimiter[, null_text])

Descrição

Retorna uma concatenação dos elementos em array_expression como um STRING. O valor de array_expression pode ser uma matriz de tipos de dados BYTES ou STRING.

Se o parâmetro null_text for usado, a função substituirá todos os valores NULL na matriz pelo valor de null_text.

Se o parâmetro null_text não for usado, a função omitirá o valor NULL e seu delimitador anterior.

Exemplos

WITH items AS
  (SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie", NULL] as list)

SELECT ARRAY_TO_STRING(list, '--') AS text
FROM items;

+--------------------------------+
| text                           |
+--------------------------------+
| coffee--tea--milk              |
| cake--pie                      |
+--------------------------------+
WITH items AS
  (SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie", NULL] as list)

SELECT ARRAY_TO_STRING(list, '--', 'MISSING') AS text
FROM items;

+--------------------------------+
| text                           |
+--------------------------------+
| coffee--tea--milk              |
| cake--pie--MISSING             |
+--------------------------------+

GENERATE_ARRAY

GENERATE_ARRAY(start_expression, end_expression[, step_expression])

Descrição

Retorna uma matriz de valores. Os parâmetros start_expression e end_expression determinam o início e o fim inclusivos da matriz.

A função GENERATE_ARRAY aceita estes tipos de dados como entradas:

  • INT64
  • NUMERIC
  • FLOAT64

O parâmetro step_expression determina o incremento usado para gerar valores de matriz. O valor padrão desse parâmetro é 1.

Essa função retornará um erro se step_expression for definido como 0 ou se alguma entrada for NaN.

Se qualquer argumento for NULL, a função retornará uma matriz NULL.

Tipo de dados retornados

ARRAY

Exemplos

O seguinte retorna uma matriz de números inteiros, com uma etapa padrão de 1.

SELECT GENERATE_ARRAY(1, 5) AS example_array;

+-----------------+
| example_array   |
+-----------------+
| [1, 2, 3, 4, 5] |
+-----------------+

O seguinte retorna uma matriz usando um tamanho do passo especificado pelo usuário.

SELECT GENERATE_ARRAY(0, 10, 3) AS example_array;

+---------------+
| example_array |
+---------------+
| [0, 3, 6, 9]  |
+---------------+

O seguinte retorna uma matriz usando um valor negativo -3 para o tamanho do passo.

SELECT GENERATE_ARRAY(10, 0, -3) AS example_array;

+---------------+
| example_array |
+---------------+
| [10, 7, 4, 1] |
+---------------+

O seguinte retorna uma matriz usando o mesmo valor para start_expression e end_expression.

SELECT GENERATE_ARRAY(4, 4, 10) AS example_array;

+---------------+
| example_array |
+---------------+
| [4]           |
+---------------+

O seguinte retorna uma matriz vazia, porque start_expression é maior que end_expression, e o valor step_expression é positivo.

SELECT GENERATE_ARRAY(10, 0, 3) AS example_array;

+---------------+
| example_array |
+---------------+
| []            |
+---------------+

O seguinte retorna uma matriz NULL porque end_expression é NULL.

SELECT GENERATE_ARRAY(5, NULL, 1) AS example_array;

+---------------+
| example_array |
+---------------+
| NULL          |
+---------------+

O seguinte retorna várias matrizes.

SELECT GENERATE_ARRAY(start, 5) AS example_array
FROM UNNEST([3, 4, 5]) AS start;

+---------------+
| example_array |
+---------------+
| [3, 4, 5]     |
| [4, 5]        |
| [5]           |
+---------------+

GENERATE_DATE_ARRAY

GENERATE_DATE_ARRAY(start_date, end_date[, INTERVAL INT64_expr date_part])

Descrição

Retorna uma matriz de datas. Os parâmetros start_date e end_date determinam o início e o fim inclusivos da matriz.

A função GENERATE_DATE_ARRAY aceita estes tipos de dados como entradas:

  • start_date precisa ser uma DATE
  • end_date precisa ser uma DATE
  • INT64_expr precisa ser um INT64
  • date_part precisa ser DAY, WEEK, MONTH, QUARTER ou YEAR.

O parâmetro INT64_expr determina o incremento usado para gerar datas. O valor padrão dele é de 1 dia.

Essa função retornará um erro se INT64_expr for definido como 0.

Tipo de dados retornados

Um ARRAY contendo 0 ou mais valores DATE.

Exemplos

O seguinte retorna uma matriz de datas, com uma etapa padrão de 1.

SELECT GENERATE_DATE_ARRAY('2016-10-05', '2016-10-08') AS example;

+--------------------------------------------------+
| example                                          |
+--------------------------------------------------+
| [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] |
+--------------------------------------------------+

O seguinte retorna uma matriz usando um tamanho do passo especificado pelo usuário.

SELECT GENERATE_DATE_ARRAY(
 '2016-10-05', '2016-10-09', INTERVAL 2 DAY) AS example;

+--------------------------------------+
| example                              |
+--------------------------------------+
| [2016-10-05, 2016-10-07, 2016-10-09] |
+--------------------------------------+

O seguinte retorna uma matriz usando um valor negativo -3 para o tamanho do passo.

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-01', INTERVAL -3 DAY) AS example;

+--------------------------+
| example                  |
+--------------------------+
| [2016-10-05, 2016-10-02] |
+--------------------------+

O seguinte retorna uma matriz usando o mesmo valor para start_date e end_date.

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-05', INTERVAL 8 DAY) AS example;

+--------------+
| example      |
+--------------+
| [2016-10-05] |
+--------------+

O seguinte retorna uma matriz vazia, porque start_date é maior que end_date, e o valor step é positivo.

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-01', INTERVAL 1 DAY) AS example;

+---------+
| example |
+---------+
| []      |
+---------+

O seguinte retorna uma matriz NULL, porque uma das entradas é NULL.

SELECT GENERATE_DATE_ARRAY('2016-10-05', NULL) AS example;

+---------+
| example |
+---------+
| NULL    |
+---------+

O seguinte retorna uma matriz de datas, usando MONTH como o intervalo date_part:

SELECT GENERATE_DATE_ARRAY('2016-01-01',
  '2016-12-31', INTERVAL 2 MONTH) AS example;

+--------------------------------------------------------------------------+
| example                                                                  |
+--------------------------------------------------------------------------+
| [2016-01-01, 2016-03-01, 2016-05-01, 2016-07-01, 2016-09-01, 2016-11-01] |
+--------------------------------------------------------------------------+

O seguinte usa datas não constantes para gerar uma matriz.

SELECT GENERATE_DATE_ARRAY(date_start, date_end, INTERVAL 1 WEEK) AS date_range
FROM (
  SELECT DATE '2016-01-01' AS date_start, DATE '2016-01-31' AS date_end
  UNION ALL SELECT DATE "2016-04-01", DATE "2016-04-30"
  UNION ALL SELECT DATE "2016-07-01", DATE "2016-07-31"
  UNION ALL SELECT DATE "2016-10-01", DATE "2016-10-31"
) AS items;

+--------------------------------------------------------------+
| date_range                                                   |
+--------------------------------------------------------------+
| [2016-01-01, 2016-01-08, 2016-01-15, 2016-01-22, 2016-01-29] |
| [2016-04-01, 2016-04-08, 2016-04-15, 2016-04-22, 2016-04-29] |
| [2016-07-01, 2016-07-08, 2016-07-15, 2016-07-22, 2016-07-29] |
| [2016-10-01, 2016-10-08, 2016-10-15, 2016-10-22, 2016-10-29] |
+--------------------------------------------------------------+

OFFSET e ORDINAL

array_expression[OFFSET(zero_based_offset)]
array_expression[ORDINAL(one_based_offset)]

Descrição

Acessa um elemento ARRAY por posição e retorna o elemento. OFFSET significa que a numeração começa em zero e ORDINAL, que a numeração começa em 1.

Uma matriz pode ser interpretada como sendo baseada em 0 ou em 1. Quando acessar um elemento de matriz, preceda a posição da matriz com OFFSET ou ORDINAL, respectivamente. Não há comportamento padrão.

OFFSET e ORDINAL geram um erro se o índice estiver fora do intervalo.

Tipo de retorno

Varia de acordo com os elementos em ARRAY.

Exemplos

WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)

SELECT list, list[OFFSET(1)] as offset_1, list[ORDINAL(1)] as ordinal_1
FROM items;

+----------------------------------+-----------+-----------+
| list                             | offset_1  | ordinal_1 |
+----------------------------------+-----------+-----------+
| [apples, bananas, pears, grapes] | bananas   | apples    |
| [coffee, tea, milk]              | tea       | coffee    |
| [cake, pie]                      | pie       | cake      |
+----------------------------------+-----------+-----------+

ARRAY_REVERSE

ARRAY_REVERSE(value)

Descrição

Retorna o ARRAY de entrada com elementos na ordem inversa.

Tipo de retorno

ARRAY

Exemplos

WITH example AS (
  SELECT [1, 2, 3] AS arr UNION ALL
  SELECT [4, 5] AS arr UNION ALL
  SELECT [] AS arr
)
SELECT
  arr,
  ARRAY_REVERSE(arr) AS reverse_arr
FROM example;

+-----------+-------------+
| arr       | reverse_arr |
+-----------+-------------+
| [1, 2, 3] | [3, 2, 1]   |
| [4, 5]    | [5, 4]      |
| []        | []          |
+-----------+-------------+

ARRAY_IS_DISTINCT

ARRAY_IS_DISTINCT(value)

Descrição

Retorna verdadeiro se a matriz não contiver elementos repetidos, usando a mesma lógica de comparação de igualdade de SELECT DISTINCT.

Tipo de retorno

BOOL

Exemplos

WITH example AS (
  SELECT [1, 2, 3] AS arr UNION ALL
  SELECT [1, 1, 1] AS arr UNION ALL
  SELECT [1, 2, NULL] AS arr UNION ALL
  SELECT [1, 1, NULL] AS arr UNION ALL
  SELECT [1, NULL, NULL] AS arr UNION ALL
  SELECT [] AS arr UNION ALL
  SELECT CAST(NULL AS ARRAY<INT64>) AS arr
)
SELECT
  arr,
  ARRAY_IS_DISTINCT(arr) as is_distinct
FROM example;

+-----------------+-------------+
| arr             | is_distinct |
+-----------------+-------------+
| [1, 2, 3]       | true        |
| [1, 1, 1]       | false       |
| [1, 2, NULL]    | true        |
| [1, 1, NULL]    | false       |
| [1, NULL, NULL] | false       |
| []              | true        |
| NULL            | NULL        |
+-----------------+-------------+

SAFE_OFFSET e SAFE_ORDINAL

array_expression[SAFE_OFFSET(zero_based_offset)]
array_expression[SAFE_ORDINAL(one_based_offset)]

Descrição

Idênticos a OFFSET e ORDINAL, exceto por retornar NULL se o índice estiver fora do intervalo.

Tipo de retorno

Varia de acordo com os elementos em ARRAY.

Exemplo

WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)

SELECT list,
  list[SAFE_OFFSET(3)] as safe_offset_3,
  list[SAFE_ORDINAL(3)] as safe_ordinal_3
FROM items;

+----------------------------------+---------------+----------------+
| list                             | safe_offset_3 | safe_ordinal_3 |
+----------------------------------+---------------+----------------+
| [apples, bananas, pears, grapes] | grapes        | pears          |
| [coffee, tea, milk]              | NULL          | milk           |
| [cake, pie]                      | NULL          | NULL           |
+----------------------------------+---------------+----------------+

Funções Date

O Cloud Spanner SQL é compatível com as seguintes funções DATE.

CURRENT_DATE

CURRENT_DATE([time_zone])

Descrição

Retorna a data atual a partir do fuso horário especificado ou padrão. Os parênteses são opcionais quando a função é chamada sem argumentos.

Essa função aceita um parâmetro time_zone opcional. Esse parâmetro é uma string que representa o fuso horário a ser usado. Se nenhum fuso horário for especificado, será usado o padrão America/Los_Angeles. Para informações sobre como especificar um fuso horário, consulte Definições de fuso horário.

Se o parâmetro time_zone for avaliado como NULL, essa função retornará NULL.

Tipo de dados retornados

DATE

Exemplo

SELECT CURRENT_DATE() as the_date;

+--------------+
| the_date     |
+--------------+
| 2016-12-25   |
+--------------+

Quando uma coluna chamada current_date está presente, o nome da coluna e a chamada de função sem parênteses são ambíguos. Para garantir a chamada da função, adicione parênteses. Para garantir o nome da coluna, qualifique-o com a variável de intervalo dele. Por exemplo, a consulta a seguir selecionará a função na coluna the_date e a coluna da tabela na coluna current_date.

WITH t AS (SELECT 'column value' AS `current_date`)
SELECT current_date() AS the_date, t.current_date FROM t;

+------------+--------------+
| the_date   | current_date |
+------------+--------------+
| 2016-12-25 | column value |
+------------+--------------+

EXTRACT

EXTRACT(part FROM date_expression)

Descrição

Retorna o valor correspondente à parte da data especificada. O part precisa ser um dos seguintes:

  • DAYOFWEEK: retorna valores no intervalo [1,7] com domingo como primeiro dia da semana.
  • DAY
  • DAYOFYEAR
  • WEEK: retorna o número da semana da data no intervalo [0, 53]. As semanas começam no domingo. Os dias anteriores ao primeiro domingo do ano estão na semana 0.
  • ISOWEEK: retorna o número da semana ISO 8601 de date_expression. As ISOWEEKs começam na segunda-feira. Os valores de retorno estão no intervalo [1, 53]. O primeiro ISOWEEK de cada ano ISO começa na segunda-feira anterior à primeira quinta-feira do ano do calendário gregoriano.
  • MONTH
  • QUARTER: retorna valores na faixa [1,4].
  • YEAR
  • ISOYEAR: retorna o ano da numeração da semana ISO 8601, que é o ano do calendário gregoriano que contém a quinta-feira da semana à qual date_expression pertence.

Tipo de dados retornados

INT64

Exemplos

No exemplo a seguir, EXTRACT retorna um valor correspondente à parte da data DAY.

SELECT EXTRACT(DAY FROM DATE '2013-12-25') as the_day;

+---------+
| the_day |
+---------+
| 25      |
+---------+

No exemplo a seguir, EXTRACT retorna valores correspondentes a diferentes partes de data de uma coluna de datas próximas ao final do ano.

SELECT
  date,
  EXTRACT(ISOYEAR FROM date) AS isoyear,
  EXTRACT(ISOWEEK FROM date) AS isoweek,
  EXTRACT(YEAR FROM date) AS year,
  EXTRACT(WEEK FROM date) AS week
FROM UNNEST(GENERATE_DATE_ARRAY('2015-12-23', '2016-01-09')) AS date
ORDER BY date;
+------------+---------+---------+------+------+
| date       | isoyear | isoweek | year | week |
+------------+---------+---------+------+------+
| 2015-12-23 | 2015    | 52      | 2015 | 51   |
| 2015-12-24 | 2015    | 52      | 2015 | 51   |
| 2015-12-25 | 2015    | 52      | 2015 | 51   |
| 2015-12-26 | 2015    | 52      | 2015 | 51   |
| 2015-12-27 | 2015    | 52      | 2015 | 52   |
| 2015-12-28 | 2015    | 53      | 2015 | 52   |
| 2015-12-29 | 2015    | 53      | 2015 | 52   |
| 2015-12-30 | 2015    | 53      | 2015 | 52   |
| 2015-12-31 | 2015    | 53      | 2015 | 52   |
| 2016-01-01 | 2015    | 53      | 2016 | 0    |
| 2016-01-02 | 2015    | 53      | 2016 | 0    |
| 2016-01-03 | 2015    | 53      | 2016 | 1    |
| 2016-01-04 | 2016    | 1       | 2016 | 1    |
| 2016-01-05 | 2016    | 1       | 2016 | 1    |
| 2016-01-06 | 2016    | 1       | 2016 | 1    |
| 2016-01-07 | 2016    | 1       | 2016 | 1    |
| 2016-01-08 | 2016    | 1       | 2016 | 1    |
| 2016-01-09 | 2016    | 1       | 2016 | 1    |
+------------+---------+---------+------+------+

DATE

1. DATE(year, month, day)
2. DATE(timestamp_expression[, timezone])

Descrição

  1. Cria uma DATE com os valores INT64 que representam o ano, o mês e o dia.
  2. Extrai a DATE de uma expressão TIMESTAMP. Aceita um parâmetro opcional para especificar um fuso horário. Se nenhum fuso horário for especificado, será usado o padrão America/Los_Angeles.

Tipos de dados retornados

DATE

Exemplo

SELECT
  DATE(2016, 12, 25) as date_ymd,
  DATE(TIMESTAMP "2016-12-25 05:30:00+07", "America/Los_Angeles") as date_tstz;

+------------+------------+
| date_ymd   | date_tstz  |
+------------+------------+
| 2016-12-25 | 2016-12-24 |
+------------+------------+

DATE_ADD

DATE_ADD(date_expression, INTERVAL int64_expression date_part)

Descrição

Adiciona um intervalo de tempo especificado a uma DATE.

DATE_ADD aceita os seguintes valores date_part:

  • DAY
  • WEEK. Equivalente a 7 DAYs.
  • MONTH
  • QUARTER
  • YEAR

Será necessário um tratamento especial para as partes MONTH, QUARTER e YEAR quando a data for o último dia do mês ou estiver perto dele. Se o mês resultante tiver menos dias do que a data original, o dia resultante será o último dia do mês novo.

Tipo de dados retornados

DATE

Exemplo

SELECT DATE_ADD(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_later;

+--------------------+
| five_days_later    |
+--------------------+
| 2008-12-30         |
+--------------------+

DATE_SUB

DATE_SUB(date_expression, INTERVAL int64_expression date_part)

Descrição

Subtrai um intervalo de tempo especificado de uma DATE.

DATE_SUB aceita os seguintes valores date_part:

  • DAY
  • WEEK. Equivalente a 7 DAYs.
  • MONTH
  • QUARTER
  • YEAR

Será necessário um tratamento especial para as partes MONTH, QUARTER e YEAR quando a data for o último dia do mês ou estiver perto dele. Se o mês resultante tiver menos dias do que a data original, o dia resultante será o último dia do mês novo.

Tipo de dados retornados

DATE

Exemplo

SELECT DATE_SUB(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_ago;

+---------------+
| five_days_ago |
+---------------+
| 2008-12-20    |
+---------------+

DATE_DIFF

DATE_DIFF(date_expression_a, date_expression_b, date_part)

Descrição

Retorna o número de intervalos date_part especificados inteiros entre dois objetos DATE (date_expression_a - date_expression_b). Se o primeiro DATE for anterior ao segundo, a saída é negativa.

DATE_DIFF aceita os seguintes valores date_part:

  • DAY
  • WEEK: esta parte da data começa no domingo.
  • ISOWEEK: usa limites da semana ISO 8601. As semanas ISO começam na segunda-feira.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: usa o limite de ano de numeração da semana ISO 8601. O limite do ano ISO é a segunda-feira da primeira semana que contém a quinta-feira pertencente ao ano do calendário gregoriano correspondente.

Tipo de dados retornados

INT64

Exemplo

SELECT DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY) as days_diff;

+-----------+
| days_diff |
+-----------+
| 559       |
+-----------+
SELECT
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', DAY) as days_diff,
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', WEEK) as weeks_diff;

+-----------+------------+
| days_diff | weeks_diff |
+-----------+------------+
| 1         | 1          |
+-----------+------------+

O exemplo acima mostra o resultado de DATE_DIFF para dois dias consecutivos. DATE_DIFF com a parte da data WEEK retorna 1 porque DATE_DIFF conta o número de limites da parte da data nesse intervalo de datas. Cada WEEK começa no domingo, então há um limite da parte da data entre sábado, 2017-10-14 e domingo, 2017-10-15.

No exemplo a seguir, mostraremos o resultado de DATE_DIFF para duas datas em anos diferentes. DATE_DIFF com a parte da data YEAR retorna 3 porque conta o número de limites do ano do calendário gregoriano entre as duas datas. DATE_DIFF com a parte da data ISOYEAR retorna 2 porque a segunda data pertence ao ano ISO 2015. A primeira quinta-feira do ano-calendário de 2015 foi 2015-01-01, então o ano ISO 2015 começa na segunda-feira anterior, 2014-12-29.

SELECT
  DATE_DIFF('2017-12-30', '2014-12-30', YEAR) AS year_diff,
  DATE_DIFF('2017-12-30', '2014-12-30', ISOYEAR) AS isoyear_diff;

+-----------+--------------+
| year_diff | isoyear_diff |
+-----------+--------------+
| 3         | 2            |
+-----------+--------------+

O exemplo a seguir mostra o resultado de DATE_DIFF para dois dias consecutivos. A primeira data cai em uma segunda-feira e a segunda data cai em um domingo. DATE_DIFF com a parte da data WEEK retorna 0 porque esta parte da data usa semanas que começam no domingo. DATE_DIFF com a parte da data ISOWEEK retorna 1 porque as semanas ISO começam na segunda-feira.

SELECT
  DATE_DIFF('2017-12-18', '2017-12-17', WEEK) AS week_diff,
  DATE_DIFF('2017-12-18', '2017-12-17', ISOWEEK) AS isoweek_diff;

+-----------+--------------+
| week_diff | isoweek_diff |
+-----------+--------------+
| 0         | 1            |
+-----------+--------------+

DATE_TRUNC

DATE_TRUNC(date_expression, date_part)

Descrição

Trunca a data com a granularidade especificada.

DATE_TRUNC aceita os seguintes valores para date_part:

  • DAY
  • WEEK
  • ISOWEEK: trunca date_expression no limite da semana ISO 8601 anterior. ISOWEEKs começam na segunda-feira. A primeira ISOWEEK de cada ano ISO contém a primeira quinta-feira do ano do calendário gregoriano correspondente. Qualquer date_expression anterior a esta vai truncar na segunda-feira anterior.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: trunca date_expression no limite do ano de numeração da semana ISO 8601 anterior. O limite do ano ISO é a segunda-feira da primeira semana que contém a quinta-feira pertencente ao ano do calendário gregoriano correspondente.

Tipo de dados retornados

DATE

Exemplos

SELECT DATE_TRUNC(DATE '2008-12-25', MONTH) as month;

+------------+
| month      |
+------------+
| 2008-12-01 |
+------------+

No exemplo a seguir, o date_expression original está no ano letivo gregoriano de 2015. No entanto, DATE_TRUNC com a parte da data ISOYEAR trunca a date_expression no início do ano ISO, não no ano do calendário gregoriano. A primeira quinta-feira do ano-calendário de 2015 foi 2015-01-01, então o ano ISO 2015 começa na segunda-feira anterior, 2014-12-29. Portanto, o limite do ano ISO anterior a date_expression 2015-06-15 é 2014-12-29.

SELECT
  DATE_TRUNC('2015-06-15', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM DATE '2015-06-15') AS isoyear_number;

+------------------+----------------+
| isoyear_boundary | isoyear_number |
+------------------+----------------+
| 2014-12-29       | 2015           |
+------------------+----------------+

DATE_FROM_UNIX_DATE

DATE_FROM_UNIX_DATE(int64_expression)

Descrição

Interpreta int64_expression como o número de dias desde 01-01-1970.

Tipo de dados retornados

DATE

Exemplo

SELECT DATE_FROM_UNIX_DATE(14238) as date_from_epoch;

+-----------------+
| date_from_epoch |
+-----------------+
| 2008-12-25      |
+-----------------+

FORMAT_DATE

FORMAT_DATE(format_string, date_expr)

Descrição

Formata date_expr de acordo com a format_string especificada.

Consulte Elementos de formatos compatíveis com DATE para ver uma lista de elementos de formato aceitos por essa função.

Tipo de dados retornados

STRING

Exemplos

SELECT FORMAT_DATE("%x", DATE "2008-12-25") as US_format;

+------------+
| US_format  |
+------------+
| 12/25/08   |
+------------+
SELECT FORMAT_DATE("%b-%d-%Y", DATE "2008-12-25") AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec-25-2008 |
+-------------+
SELECT FORMAT_DATE("%b %Y", DATE "2008-12-25") AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec 2008    |
+-------------+

PARSE_DATE

PARSE_DATE(format_string, date_string)

Descrição

Converte uma representação de string de data em um objeto DATE.

format_string contém os elementos de formato que definem como date_string é formatado. Cada elemento em date_string precisa ter um elemento correspondente em format_string. O local de cada elemento em format_string precisa corresponder à localização de cada elemento em date_string.

-- This works because elements on both sides match.
SELECT PARSE_DATE("%A %b %e %Y", "Thursday Dec 25 2008")

-- This doesn't work because the year element is in different locations.
SELECT PARSE_DATE("%Y %A %b %e", "Thursday Dec 25 2008")

-- This doesn't work because one of the year elements is missing.
SELECT PARSE_DATE("%A %b %e", "Thursday Dec 25 2008")

-- This works because %F can find all matching elements in date_string.
SELECT PARSE_DATE("%F", "2000-12-30")

A string de formato é totalmente compatível com a maioria dos elementos de formato, exceto %a, %A, %g, %G, %j, %u, %U, %V, %w e %W.

Ao usar PARSE_DATE, tenha em mente os seguintes itens:

  • Campos não especificados. Qualquer campo não especificado é inicializado em 1970-01-01.
  • Nomes que não diferenciam maiúsculas e minúsculas. Nomes, como Monday, February e outros, não diferenciam maiúsculas de minúsculas.
  • Espaço em branco. Um ou mais espaços em branco consecutivos na string de formato correspondem a zero ou mais espaços em branco consecutivos na string de data. Além disso, espaços em branco à esquerda e à direita na string de data sempre são permitidos, mesmo que não estejam na string de formato.
  • Precedência de formato. Quando dois (ou mais) elementos de formato têm informações sobrepostas (por exemplo, %F e %Y afetam o ano), o último geralmente substitui os anteriores.

Tipo de dados retornados

DATE

Exemplos

Neste exemplo, uma string formatada em MM/DD/YY é convertida em um objeto DATE:

SELECT PARSE_DATE("%x", "12/25/08") as parsed;

+------------+
| parsed     |
+------------+
| 2008-12-25 |
+------------+

Neste exemplo, uma string formatada em YYYYMMDD é convertida em um objeto DATE:

SELECT PARSE_DATE("%Y%m%d", "20081225") as parsed;

+------------+
| parsed     |
+------------+
| 2008-12-25 |
+------------+

UNIX_DATE

UNIX_DATE(date_expression)

Descrição

Retorna o número de dias desde 1970-01-01.

Tipo de dados retornados

INT64

Exemplo

SELECT UNIX_DATE(DATE "2008-12-25") as days_from_epoch;

+-----------------+
| days_from_epoch |
+-----------------+
| 14238           |
+-----------------+

Elementos de formatos compatíveis com DATE

Salvo indicação em contrário, as funções DATE que usam strings de formato aceitam os seguintes elementos:

Elemento de formato Descrição Exemplo
%A O nome completo do dia da semana. Quarta-feira
%a O nome abreviado do dia da semana. Qua
%B O nome completo do mês. January
%b ou %h O nome abreviado do mês. Jan
%C O século (um ano dividido por 100 e truncado como um inteiro) como um número decimal (00-99). 20
%D A data no formato %m/%d/%y. 01/20/21
%d O dia do mês como número decimal (01-31). 20
%e O dia do mês como número decimal (1-31). Dígitos únicos são precedidos por um espaço. 20
%F A data no formato %Y-%m-%d. 2021-01-20
%G O ano ISO 8601 com o século como número decimal. Cada ano ISO começa na segunda-feira antes da primeira quinta-feira do ano calendário gregoriano. Observe que %G e %Y podem produzir resultados diferentes próximos aos limites do ano gregoriano, em que o ano gregoriano e o ano ISO podem divergir. 2021
%g O ano ISO 8601 sem o século como número decimal (00-99). Cada ano ISO começa na segunda-feira antes da primeira quinta-feira do ano calendário gregoriano. Observe que %g e %y podem produzir resultados diferentes próximos aos limites do ano gregoriano, em que o ano gregoriano e o ano ISO podem divergir. 21
%j O dia do ano como número decimal (001-366). 020
%m O mês como número decimal (01-12). 01
%n Um caractere de nova linha.
%t Um caractere de tabulação.
%U O número da semana do ano (domingo como o primeiro dia da semana) como número decimal (00-53). 03
%u O dia da semana (segunda-feira como o primeiro dia da semana) como número decimal (1-7). 3
%V O número da semana ISO 8601 do ano (segunda-feira como o primeiro dia da semana) como um número decimal (01–53). Se a semana que tem 1 de janeiro tiver quatro ou mais dias no ano novo, então será a semana 1. Caso contrário, será a semana 53 do ano anterior e a semana seguinte será a semana 1. 03
%W O número da semana do ano (segunda-feira como o primeiro dia da semana) como número decimal (00-53). 03
%w O dia da semana (domingo como o primeiro dia da semana) como número decimal (0-6). 3
%x A representação de data no formato MM/DD/YY. 01/20/21
%Y O ano com o século como número decimal. 2021
%y O ano sem o século como número decimal (00-99), com um zero opcional à esquerda. Pode ser misturado com %C. Se %C não for especificado, os anos 00-68 são os 2000, enquanto os anos 69-99 são os 1900. 21
%E4Y Anos com quatro caracteres (0001 ... 9999). %Y produz quantos caracteres forem necessários para processar totalmente o ano. 2021

Funções de carimbo de data/hora

O Cloud Spanner SQL é compatível com as seguintes funções de TIMESTAMP.

OBSERVAÇÃO: essas funções retornam um erro de tempo execução, se ocorrer um estouro. Os valores dos resultados são limitados até os valores mínimo e máximo da data e do carimbo de data/hora definidos.

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

Descrição

CURRENT_TIMESTAMP() produz um valor TIMESTAMP que é contínuo, não ambíguo, tem exatamente 60 segundos por minuto e não repete valores durante o segundo. O uso de parênteses é opcional.

Essa função trata os segundos bissextos distribuindo-os em um intervalo de 20 horas em torno do ajuste inserido.

Tipos de entradas compatíveis

Não aplicável

Tipo de dados de resultado

TIMESTAMP

Exemplos

SELECT CURRENT_TIMESTAMP() as now;

+--------------------------------+
| now                            |
+--------------------------------+
| 2020-06-02T23:58:40.347847393Z |
+--------------------------------+

Quando uma coluna chamada current_timestamp está presente, o nome da coluna e a chamada de função sem parênteses são ambíguos. Para garantir a chamada da função, adicione parênteses. Para garantir o nome da coluna, qualifique-o com a variável de intervalo dele. Por exemplo, a consulta a seguir selecionará a função na coluna now e a coluna da tabela na coluna current_timestamp.

WITH t AS (SELECT 'column value' AS `current_timestamp`)
SELECT current_timestamp() AS now, t.current_timestamp FROM t;

+--------------------------------+-------------------+
| now                            | current_timestamp |
+--------------------------------+-------------------+
| 2020-06-02T23:58:40.347847393Z | column value      |
+--------------------------------+-------------------+

EXTRACT

EXTRACT(part FROM timestamp_expression [AT TIME ZONE timezone])

Descrição

Retorna um valor que corresponde ao part especificado de um timestamp_expression fornecido. Essa função aceita um parâmetro timezone opcional. Para informações sobre como especificar um fuso horário, consulte Definições de fuso horário.

Os valores part permitidos são:

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAYOFWEEK
  • DAY
  • DAYOFYEAR
  • WEEK: retorna o número da semana da data no intervalo [0, 53]. As semanas começam no domingo. Os dias anteriores ao primeiro domingo do ano estão na semana 0.
  • ISOWEEK: retorna o número da semana ISO 8601 de datetime_expression. As ISOWEEKs começam na segunda-feira. Os valores de retorno estão no intervalo [1, 53]. O primeiro ISOWEEK de cada ano ISO começa na segunda-feira anterior à primeira quinta-feira do ano do calendário gregoriano.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: retorna o ano da numeração da semana ISO 8601, que é o ano do calendário gregoriano que contém a quinta-feira da semana à qual date_expression pertence.
  • DATE

Os valores retornados truncam períodos menores de tempo. Por exemplo, ao extrair segundos, EXTRACT trunca os valores de milissegundos e microssegundos.

Tipo de dados retornados

INT64, exceto quando:

  • part é DATE, retorna um DATEobjeto.

Exemplos

No exemplo a seguir, EXTRACT retorna um valor correspondente à parte de hora de DAY.

WITH Input AS (SELECT TIMESTAMP("2008-12-25 05:30:00+00") AS timestamp_value)
SELECT
  EXTRACT(DAY FROM timestamp_value AT TIME ZONE "UTC") AS the_day_utc,
  EXTRACT(DAY FROM timestamp_value AT TIME ZONE "America/Los_Angeles") AS the_day_california
FROM Input

+-------------+--------------------+
| the_day_utc | the_day_california |
+-------------+--------------------+
| 25          | 24                 |
+-------------+--------------------+

No exemplo a seguir, EXTRACT retorna valores correspondentes a diferentes partes da hora de uma coluna de carimbos de data/hora.

WITH Timestamps AS (
  SELECT TIMESTAMP("2005-01-03 12:34:56+00") AS timestamp_value UNION ALL
  SELECT TIMESTAMP("2007-12-31 12:00:00+00") UNION ALL
  SELECT TIMESTAMP("2009-01-01 12:00:00+00") UNION ALL
  SELECT TIMESTAMP("2009-12-31 12:00:00+00") UNION ALL
  SELECT TIMESTAMP("2017-01-02 12:00:00+00") UNION ALL
  SELECT TIMESTAMP("2017-05-26 12:00:00+00")
)
SELECT
  timestamp_value,
  EXTRACT(ISOYEAR FROM timestamp_value) AS isoyear,
  EXTRACT(ISOWEEK FROM timestamp_value) AS isoweek,
  EXTRACT(YEAR FROM timestamp_value) AS year,
  EXTRACT(WEEK FROM timestamp_value) AS week
FROM Timestamps
ORDER BY timestamp_value;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+---------+---------+------+------+
| timestamp_value        | isoyear | isoweek | year | week |
+------------------------+---------+---------+------+------+
| 2005-01-03T12:34:56Z   | 2005    | 1       | 2005 | 1    |
| 2007-12-31T12:00:00Z   | 2008    | 1       | 2007 | 52   |
| 2009-01-01T12:00:00Z   | 2009    | 1       | 2009 | 0    |
| 2009-12-31T12:00:00Z   | 2009    | 53      | 2009 | 52   |
| 2017-01-02T12:00:00Z   | 2017    | 1       | 2017 | 1    |
| 2017-05-26T12:00:00Z   | 2017    | 21      | 2017 | 21   |
+------------------------+---------+---------+------+------+

STRING

STRING(timestamp_expression[, timezone])

Descrição

Converte um timestamp_expression em um tipo de dados STRING. Aceita um parâmetro opcional para especificar um fuso horário. Para informações sobre como especificar um fuso horário, consulte Definições de fuso horário.

Tipo de dados retornados

STRING

Exemplo

SELECT STRING(TIMESTAMP "2008-12-25 15:30:00+00", "UTC") AS string;

+-------------------------------+
| string                        |
+-------------------------------+
| 2008-12-25 15:30:00+00        |
+-------------------------------+

TIMESTAMP

TIMESTAMP(string_expression[, timezone])
TIMESTAMP(date_expression[, timezone])

Descrição

  • string_expression[, timezone]: converte uma expressão STRING em um tipo de dados TIMESTAMP. string_expression precisa incluir um literal de carimbo de data e hora. Se string_expression incluir um fuso horário no literal de carimbo data/hora, não inclua um argumento timezone explícito.
  • date_expression[, timezone]: converte um objeto DATE em um tipo de dados TIMESTAMP.

Esta função é compatível com um parâmetro opcional para especificar um fuso horário. Se nenhum fuso horário for especificado, será usado o padrão America/Los_Angeles.

Tipos de dados retornados

TIMESTAMP

Exemplos

SELECT TIMESTAMP("2008-12-25 15:30:00+00") AS timestamp_str;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+----------------------+
| timestamp_str        |
+----------------------+
| 2008-12-25T15:30:00Z |
+----------------------+
SELECT TIMESTAMP("2008-12-25 15:30:00", "America/Los_Angeles") AS timestamp_str;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+----------------------+
| timestamp_str        |
+----------------------+
| 2008-12-25T23:30:00Z |
+----------------------+
SELECT TIMESTAMP("2008-12-25 15:30:00 UTC") AS timestamp_str;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+----------------------+
| timestamp_str        |
+----------------------+
| 2008-12-25T15:30:00Z |
+----------------------+
SELECT TIMESTAMP(DATE "2008-12-25") AS timestamp_date;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+----------------------+
| timestamp_date       |
+----------------------+
| 2008-12-25T08:00:00Z |
+----------------------+

TIMESTAMP_ADD

TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression date_part)

Descrição

Adiciona unidades int64_expression de date_part ao carimbo de data/hora, independentemente de qualquer fuso horário.

TIMESTAMP_ADD aceita os seguintes valores para date_part:

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR: equivalente a 60 MINUTEs
  • DAY. Equivalente a 24 HOURs.

Tipos de dados retornados

TIMESTAMP

Exemplo

SELECT
  TIMESTAMP("2008-12-25 15:30:00+00") AS original,
  TIMESTAMP_ADD(TIMESTAMP "2008-12-25 15:30:00+00", INTERVAL 10 MINUTE) AS later;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+------------------------+
| original               | later                  |
+------------------------+------------------------+
| 2008-12-25T15:30:00Z   | 2008-12-25T15:40:00Z   |
+------------------------+------------------------+

TIMESTAMP_SUB

TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression date_part)

Descrição

Subtrai unidades int64_expression de date_part do carimbo de data/hora, independentemente de qualquer fuso horário.

TIMESTAMP_SUB aceita os seguintes valores para date_part:

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR: equivalente a 60 MINUTEs
  • DAY. Equivalente a 24 HOURs.

Tipo de dados retornados

TIMESTAMP

Exemplo

SELECT
  TIMESTAMP("2008-12-25 15:30:00+00") AS original,
  TIMESTAMP_SUB(TIMESTAMP "2008-12-25 15:30:00+00", INTERVAL 10 MINUTE) AS earlier;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+------------------------+
| original               | earlier                |
+------------------------+------------------------+
| 2008-12-25T15:30:00Z   | 2008-12-25T15:20:00Z   |
+------------------------+------------------------+

TIMESTAMP_DIFF

TIMESTAMP_DIFF(timestamp_expression_a, timestamp_expression_b, date_part)

Descrição

Retorna o número de intervalos date_part especificados inteiros entre dois objetos TIMESTAMP (timestamp_expression_a - timestamp_expression_b). Se o primeiro TIMESTAMP for anterior ao segundo, a saída será negativa. Lança um erro se o cálculo estourar o tipo de resultado, como se a diferença em nanossegundos entre os dois objetos TIMESTAMP estourasse um valor INT64.

TIMESTAMP_DIFF aceita os seguintes valores para date_part:

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR: equivalente a 60 MINUTEs
  • DAY. Equivalente a 24 HOURs.

Tipo de dados retornados

INT64

Exemplo

SELECT
  TIMESTAMP("2010-07-07 10:20:00+00") AS later_timestamp,
  TIMESTAMP("2008-12-25 15:30:00+00") AS earlier_timestamp,
  TIMESTAMP_DIFF(TIMESTAMP "2010-07-07 10:20:00+00", TIMESTAMP "2008-12-25 15:30:00+00", HOUR) AS hours;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+------------------------+-------+
| later_timestamp        | earlier_timestamp      | hours |
+------------------------+------------------------+-------+
| 2010-07-07T10:20:00Z   | 2008-12-25T15:30:00Z   | 13410 |
+------------------------+------------------------+-------+

No exemplo a seguir, o primeiro carimbo de data/hora ocorre antes do segundo, resultando em uma saída negativa.

SELECT TIMESTAMP_DIFF(TIMESTAMP "2018-08-14", TIMESTAMP "2018-10-14", DAY);

+---------------+
| negative_diff |
+---------------+
| -61           |
+---------------+

Neste exemplo, o resultado é 0 porque somente o número de intervalos HOUR especificados inteiros é incluído.

SELECT TIMESTAMP_DIFF("2001-02-01 01:00:00", "2001-02-01 00:00:01", HOUR)

+---------------+
| negative_diff |
+---------------+
| 0             |
+---------------+

TIMESTAMP_TRUNC

TIMESTAMP_TRUNC(timestamp_expression, date_part[, timezone])

Descrição

Trunca um carimbo de data/hora para a granularidade de date_part.

TIMESTAMP_TRUNC aceita os seguintes valores para date_part:

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • ISOWEEK: trunca timestamp_expression no limite da semana ISO 8601 anterior. ISOWEEKs começam na segunda-feira. A primeira ISOWEEK de cada ano ISO contém a primeira quinta-feira do ano do calendário gregoriano correspondente. Qualquer date_expression anterior a esta vai truncar na segunda-feira anterior.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: trunca timestamp_expression no limite do ano de numeração da semana ISO 8601 anterior. O limite do ano ISO é a segunda-feira da primeira semana que contém a quinta-feira pertencente ao ano do calendário gregoriano correspondente.

A função TIMESTAMP_TRUNC é compatível com um parâmetro timezone opcional. Esse parâmetro se aplica aos seguintes date_parts:

  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • ISOWEEK
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR

Use este parâmetro se quiser usar um fuso horário diferente do fuso horário padrão America/Los_Angeles, como parte da operação truncada.

Ao truncar um TIMESTAMP para MINUTE ou HOUR, TIMESTAMP_TRUNC determina o tempo civil do TIMESTAMP no fuso horário especificado (ou padrão) e subtrai os minutos e segundos (ao truncar para HOUR) ou os segundos (ao truncar para MINUTE) a partir desse TIMESTAMP. Na maioria dos casos, esse procedimento fornece resultados intuitivos, exceto próximo a transições de horário de verão que não são alinhadas por hora.

Tipo de dados retornados

TIMESTAMP

Exemplos

SELECT
  TIMESTAMP_TRUNC(TIMESTAMP "2008-12-25 15:30:00+00", DAY, "UTC") AS utc,
  TIMESTAMP_TRUNC(TIMESTAMP "2008-12-25 15:30:00+00", DAY, "America/Los_Angeles") AS la;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+------------------------+
| utc                    | la                     |
+------------------------+------------------------+
| 2008-12-25T00:00:00Z   | 2008-12-25T08:00:00Z   |
+------------------------+------------------------+

No exemplo a seguir, o timestamp_expression original está no ano letivo gregoriano de 2015. No entanto, TIMESTAMP_TRUNC com a parte da data ISOYEAR trunca o timestamp_expression para o início do ano ISO, não o ano calendário gregoriano. A primeira quinta-feira do ano-calendário de 2015 foi 2015-01-01, então o ano ISO 2015 começa na segunda-feira anterior, 2014-12-29. Portanto, o limite do ano ISO anterior a timestamp_expression 2015-06-15 00:00:00 + 00 é 2014-12-29.

SELECT
  TIMESTAMP_TRUNC("2015-06-15 00:00:00+00", ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM TIMESTAMP "2015-06-15 00:00:00+00") AS isoyear_number;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+----------------+
| parsed                 | isoyear_number |
+------------------------+----------------+
| 2014-12-29T08:00:00Z   | 2015           |
+------------------------+----------------+

FORMAT_TIMESTAMP

FORMAT_TIMESTAMP(format_string, timestamp[, timezone])

Descrição

Formata um carimbo de data e hora de acordo com o format_string especificado.

Consulte Elementos de formatos compatíveis de TIMESTAMP para ver uma lista de elementos de formato aceitos por essa função.

Tipo de dados retornados

STRING

Exemplo

SELECT FORMAT_TIMESTAMP("%c", TIMESTAMP "2008-12-25 15:30:00+00", "UTC") AS formatted;

+--------------------------+
| formatted                |
+--------------------------+
| Thu Dec 25 15:30:00 2008 |
+--------------------------+
SELECT FORMAT_TIMESTAMP("%b-%d-%Y", TIMESTAMP "2008-12-25 15:30:00+00") AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec-25-2008 |
+-------------+
SELECT FORMAT_TIMESTAMP("%b %Y", TIMESTAMP "2008-12-25 15:30:00+00")
  AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec 2008    |
+-------------+

PARSE_TIMESTAMP

PARSE_TIMESTAMP(format_string, timestamp_string[, timezone])

Descrição

Converte uma representação de string de um carimbo de data/hora em um objeto TIMESTAMP.

format_string contém os elementos de formato que definem como timestamp_string é formatado. Cada elemento em timestamp_string precisa ter um elemento correspondente em format_string. O local de cada elemento em format_string precisa corresponder à localização de cada elemento em timestamp_string.

-- This works because elements on both sides match.
SELECT PARSE_TIMESTAMP("%a %b %e %I:%M:%S %Y", "Thu Dec 25 07:30:00 2008")

-- This doesn't work because the year element is in different locations.
SELECT PARSE_TIMESTAMP("%a %b %e %Y %I:%M:%S", "Thu Dec 25 07:30:00 2008")

-- This doesn't work because one of the year elements is missing.
SELECT PARSE_TIMESTAMP("%a %b %e %I:%M:%S", "Thu Dec 25 07:30:00 2008")

-- This works because %c can find all matching elements in timestamp_string.
SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008")

A string de formato é totalmente compatível com a maioria dos elementos de formato, exceto %a, %A, %g, %G, %j, %P. %u, %U, %V, %w e %W.

Ao usar PARSE_TIMESTAMP, tenha em mente os seguintes itens:

  • Campos não especificados. Qualquer campo não especificado é inicializado em 1970-01-01 00:00:00.0. Esse valor de inicialização usa o fuso horário especificado pelo argumento de fuso horário da função, se houver. Caso contrário, o valor de inicialização usa o fuso horário padrão, America/Los_Angeles. Por exemplo, se o ano não for especificado, o padrão será 1970 e assim por diante.
  • Nomes que não diferenciam maiúsculas e minúsculas. Nomes, como Monday, February e outros, não diferenciam maiúsculas de minúsculas.
  • Espaço em branco. Um ou mais espaços em branco consecutivos na string de formato correspondem a zero ou mais espaços em branco consecutivos na string de carimbo de data/hora. Além disso, espaços em branco à esquerda e à direita na string de carimbo de data/hora sempre são permitidos, mesmo que não estejam na string de formato.
  • Precedência de formato. Quando dois (ou mais) elementos de formato têm informações sobrepostas (por exemplo, %F e %Y afetam o ano), o último geralmente modifica os anteriores, com algumas exceções (consulte as descrições de %s, %C e %y).

Tipo de dados retornados

TIMESTAMP

Exemplo

SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008") AS parsed;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+
| parsed                 |
+------------------------+
| 2008-12-25T15:30:00Z   |
+------------------------+

TIMESTAMP_SECONDS

TIMESTAMP_SECONDS(int64_expression)

Descrição

Interpreta int64_expression como o número de segundos desde 1970-01-01 00:00:00 UTC.

Tipo de dados retornados

TIMESTAMP

Exemplo

SELECT TIMESTAMP_SECONDS(1230219000) AS timestamp_value;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+
| timestamp_value        |
+------------------------+
| 2008-12-25T15:30:00Z   |
+------------------------+

TIMESTAMP_MILLIS

TIMESTAMP_MILLIS(int64_expression)

Descrição

Interpreta int64_expression como o número de milissegundos desde 1970-01-01 00:00:00 UTC.

Tipo de dados retornados

TIMESTAMP

Exemplo

SELECT TIMESTAMP_MILLIS(1230219000000) AS timestamp_value;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+
| timestamp_value        |
+------------------------+
| 2008-12-25T15:30:00Z   |
+------------------------+

TIMESTAMP_MICROS

TIMESTAMP_MICROS(int64_expression)

Descrição

Interpreta int64_expression como o número de microssegundos desde 1970-01-01 00:00:00 UTC.

Tipo de dados retornados

TIMESTAMP

Exemplo

SELECT TIMESTAMP_MICROS(1230219000000000) AS timestamp_value;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+
| timestamp_value        |
+------------------------+
| 2008-12-25T15:30:00Z   |
+------------------------+

UNIX_SECONDS

UNIX_SECONDS(timestamp_expression)

Descrição

Retorna o número de segundos desde 01/01/1970 00:00:00 UTC. Trunca níveis mais elevados de precisão.

Tipo de dados retornados

INT64

Exemplo

SELECT UNIX_SECONDS(TIMESTAMP "2008-12-25 15:30:00+00") AS seconds;

+------------+
| seconds    |
+------------+
| 1230219000 |
+------------+

UNIX_MILLIS

UNIX_MILLIS(timestamp_expression)

Descrição

Retorna o número de milissegundos desde 1970-01-01 00:00:00 UTC. Trunca níveis maiores de precisão.

Tipo de dados retornados

INT64

Exemplo

SELECT UNIX_MILLIS(TIMESTAMP "2008-12-25 15:30:00+00") AS millis;

+---------------+
| millis        |
+---------------+
| 1230219000000 |
+---------------+

UNIX_MICROS

UNIX_MICROS(timestamp_expression)

Descrição

Retorna o número de milissegundos desde 1970-01-01 00:00:00 UTC. Trunca níveis maiores de precisão.

Tipo de dados retornados

INT64

Exemplo

SELECT UNIX_MICROS(TIMESTAMP "2008-12-25 15:30:00+00") AS micros;

+------------------+
| micros           |
+------------------+
| 1230219000000000 |
+------------------+

PENDING_COMMIT_TIMESTAMP

PENDING_COMMIT_TIMESTAMP()

Descrição

Use a função PENDING_COMMIT_TIMESTAMP() em uma instrução DML INSERT ou UPDATE para gravar o carimbo de data/hora de confirmação pendente, ou seja, o carimbo de data/hora de confirmação da gravação quando confirmar, em uma coluna de tipo TIMESTAMP.

O SQL do Cloud Spanner seleciona o carimbo de data/hora de confirmação quando a transação é confirmada. A função PENDING_COMMIT_TIMESTAMP só pode ser usada como um valor para INSERT ou UPDATE de uma coluna com tipo apropriado. Ela não pode ser usada em SELECT ou como a entrada para qualquer outra expressão escalar.

Tipo de dados retornados

TIMESTAMP

Exemplo

A instrução DML a seguir atualiza a coluna LastUpdated na tabela Singers com o carimbo de data/hora de confirmação.

UPDATE Performances SET LastUpdated = PENDING_COMMIT_TIMESTAMP()
   WHERE SingerId=1 AND VenueId=2 AND EventDate="2015-10-21"

Elementos de formatos compatíveis de TIMESTAMP

Salvo indicação em contrário, as funções TIMESTAMP que usam strings de formato aceitam os elementos a seguir:

Elemento de formato Descrição Exemplo
%A O nome completo do dia da semana. Quarta-feira
%a O nome abreviado do dia da semana. Qua
%B O nome completo do mês. January
%b ou %h O nome abreviado do mês. Jan
%C O século (um ano dividido por 100 e truncado como um inteiro) como um número decimal (00-99). 20
%c A representação de data e hora no formato %a %b %e %T %Y. Qua Jan 20 16:47:00 2021
%D A data no formato %m/%d/%y. 01/20/21
%d O dia do mês como número decimal (01-31). 20
%e O dia do mês como número decimal (1-31). Dígitos únicos são precedidos por um espaço. 20
%F A data no formato %Y-%m-%d. 2021-01-20
%G O ano ISO 8601 com o século como número decimal. Cada ano ISO começa na segunda-feira antes da primeira quinta-feira do ano calendário gregoriano. Observe que %G e %Y podem produzir resultados diferentes próximos aos limites do ano gregoriano, em que o ano gregoriano e o ano ISO podem divergir. 2021
%g O ano ISO 8601 sem o século como número decimal (00-99). Cada ano ISO começa na segunda-feira antes da primeira quinta-feira do ano calendário gregoriano. Observe que %g e %y podem produzir resultados diferentes próximos aos limites do ano gregoriano, em que o ano gregoriano e o ano ISO podem divergir. 21
%H A hora em um relógio de 24 horas como número decimal (00-23). 16
%I A hora em um relógio de 12 horas como número decimal (01-12). 04
%j O dia do ano como número decimal (001-366). 020
%k A hora em um relógio de 24 horas como número decimal (0-23). Dígitos únicos são precedidos por um espaço. 16
%l A hora em um relógio de 12 horas como número decimal (1-12). Dígitos únicos são precedidos por um espaço. 11
%M O minuto como número decimal (00-59). 47
%m O mês como número decimal (01-12). 01
%n Um caractere de nova linha.
%P am ou pm. da manhã
%p AM ou PM. AM
%Q O trimestre como um número decimal (de 1–4). 1
%R A hora no formato %H:%M. 16:47
%r A hora em um relógio de 12 horas usando a notação AM/PM. 04:47:00 PM
%S O segundo como número decimal (00-60). 00
%s O número de segundos desde 1970-01-01 00:00:00 UTC. Sempre modifica todos os outros elementos de formato, seja qual for o local onde %s aparece na string. Se vários elementos %s aparecerem, o último terá precedência. 1611179220
%T A hora no formato %H:%M:%S. 16:47:00
%t Um caractere de tabulação.
%U O número da semana do ano (domingo como o primeiro dia da semana) como número decimal (00-53). 03
%u O dia da semana (segunda-feira como o primeiro dia da semana) como número decimal (1-7). 3
%V O número da semana ISO 8601 do ano (segunda-feira como o primeiro dia da semana) como um número decimal (01–53). Se a semana que tem 1 de janeiro tiver quatro ou mais dias no ano novo, então será a semana 1. Caso contrário, será a semana 53 do ano anterior e a semana seguinte será a semana 1. 03
%W O número da semana do ano (segunda-feira como o primeiro dia da semana) como número decimal (00-53). 03
%w O dia da semana (domingo como o primeiro dia da semana) como número decimal (0-6). 3
%X A representação da hora no formato HH:MM:SS. 16:47:00
%x A representação de data no formato MM/DD/YY. 01/20/21
%Y O ano com o século como número decimal. 2021
%y O ano sem o século como número decimal (00-99), com um zero opcional à esquerda. Pode ser misturado com %C. Se %C não for especificado, os anos 00-68 são os 2000, enquanto os anos 69-99 são os 1900. 21
%Z O nome do fuso horário. UTC-5
%z O deslocamento do meridiano de origem no formato +HHMM ou -HHMM conforme o caso, com valores positivos representando locais a leste de Greenwich. -0500
%% Um único caractere %. %
%Ez Fuso horário numérico compatível com RFC 3339 (+HH:MM ou -HH:MM). -05:00
%E#S Segundos com # dígitos de precisão fracionária. 00.000
%E*S Segundos com precisão fracionária total (um literal '*'). 00
%E4Y Anos com quatro caracteres (0001 ... 9999). %Y produz quantos caracteres forem necessários para processar o ano totalmente. 2021

Definições de fuso horário

Determinadas funções de data e carimbo de data/hora permitem substituir o fuso horário padrão e especificar um diferente. É possível especificar um fuso horário por meio do nome do fuso horário (por exemplo, America/Los_Angeles) ou pelo deslocamento de fuso horário de UTC (por exemplo, -08).

Se você escolher usar um ajuste de fuso horário, use este formato:

(+|-)H[H][:M[M]]

Os carimbos data/hora são equivalentes porque o deslocamento do fuso horário para America/Los_Angeles é -08 para a data e a hora especificadas.

SELECT UNIX_MILLIS(TIMESTAMP "2008-12-25 15:30:00 America/Los_Angeles") as millis;
SELECT UNIX_MILLIS(TIMESTAMP "2008-12-25 15:30:00-08:00") as millis;

Funções de rede

NET.IP_FROM_STRING

NET.IP_FROM_STRING(addr_str)

Descrição

Converte um endereço IPv4 ou IPv6 do formato de texto (STRING) para o formato binário (BYTES) na ordem de bytes da rede.

Esta função é compatível com os seguintes formatos para addr_str:

  • IPv4: formato "dotted-quad" (quádruplo com ponto). Por exemplo, 10.1.2.3
  • IPv6: formato separado por dois-pontos. Por exemplo, 1234:5678:90ab:cdef:1234:5678:90ab:cdef. Para mais exemplos, consulte Arquitetura de endereçamento de IP versão 6 (em inglês).

Essa função não é compatível com a notação CIDR, como 10.1.2.3/32.

Se ela receber uma entrada NULL, retornará NULL. Se a entrada for considerada inválida, ocorrerá um erro OUT_OF_RANGE.

Tipo de dados retornados

BYTES

Exemplo

SELECT
  addr_str, FORMAT("%T", NET.IP_FROM_STRING(addr_str)) AS ip_from_string
FROM UNNEST([
  '48.49.50.51',
  '::1',
  '3031:3233:3435:3637:3839:4041:4243:4445',
  '::ffff:192.0.2.128'
]) AS addr_str;
addr_str ip_from_string
48.49.50.51 b"0123"
::1 b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01"
3031:3233:3435:3637:3839:4041:4243:4445 b"0123456789@ABCDE"
::ffff:192.0.2.128 b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80"

NET.SAFE_IP_FROM_STRING

NET.SAFE_IP_FROM_STRING(addr_str)

Descrição

Semelhante a NET.IP_FROM_STRING, mas retorna NULL em vez de gerar um erro se a entrada for inválida.

Tipo de dados retornados

BYTES

Exemplo

SELECT
  addr_str,
  FORMAT("%T", NET.SAFE_IP_FROM_STRING(addr_str)) AS safe_ip_from_string
FROM UNNEST([
  '48.49.50.51',
  '::1',
  '3031:3233:3435:3637:3839:4041:4243:4445',
  '::ffff:192.0.2.128',
  '48.49.50.51/32',
  '48.49.50',
  '::wxyz'
]) AS addr_str;
addr_str safe_ip_from_string
48.49.50.51 b"0123"
::1 b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01"
3031:3233:3435:3637:3839:4041:4243:4445 b"0123456789@ABCDE"
::ffff:192.0.2.128 b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80"
48.49.50.51/32 NULL
48.49.50 NULL
::wxyz NULL

NET.IP_TO_STRING

NET.IP_TO_STRING(addr_bin)

Descrição Converte um endereço IPv4 ou IPv6 do formato binário (BYTES) na ordem de bytes da rede para o formato de texto (STRING).

Se a entrada for de 4 bytes, essa função retornará um endereço IPv4 como uma STRING. Se a entrada for de 16 bytes, ela retornará um endereço IPv6 como uma STRING.

Se receber uma entrada NULL, essa função retornará NULL. Se a entrada tiver um comprimento diferente de 4 ou 16, ocorrerá um erro OUT_OF_RANGE.

Tipo de dados retornados

STRING

Exemplo

SELECT FORMAT("%T", x) AS addr_bin, NET.IP_TO_STRING(x) AS ip_to_string
FROM UNNEST([
  b"0123",
  b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01",
  b"0123456789@ABCDE",
  b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80"
]) AS x;
addr_bin ip_to_string
b"0123" 48.49.50.51
b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01" ::1
b"0123456789@ABCDE" 3031:3233:3435:3637:3839:4041:4243:4445
b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80" ::ffff:192.0.2.128

NET.IP_NET_MASK

NET.IP_NET_MASK(num_output_bytes, prefix_length)

Descrição

Retorna uma máscara de rede: uma sequência de bytes com comprimento igual a num_output_bytes, em que os primeiros prefix_length bits são definidos como um e os outros bits são definidos como zero. num_output_bytes e prefix_length são INT64. Esta função gera um erro se num_output_bytes não for 4 (para IPv4) ou 16 (para IPv6). Ele também gera um erro se prefix_length for negativo ou maior que 8 * num_output_bytes.

Tipo de dados retornados

BYTES

Exemplo

SELECT x, y, FORMAT("%T", NET.IP_NET_MASK(x, y)) AS ip_net_mask
FROM UNNEST([
  STRUCT(4 as x, 0 as y),
  (4, 20),
  (4, 32),
  (16, 0),
  (16, 1),
  (16, 128)
]);
x y ip_net_mask
4 0 b"\x00\x00\x00\x00"
4 20 b"\xff\xff\xf0\x00"
4 32 b"\xff\xff\xff\xff"
16 0 b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00"
16 1 b"\x80\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00"
16 128 b"\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff"

NET.IP_TRUNC

NET.IP_TRUNC(addr_bin, prefix_length)

Descrição Obtém addr_bin, um endereço IPv4 ou IPv6 no formato binário (BYTES) na ordem de bytes da rede e retorna um endereço de sub-rede no mesmo formato. O resultado tem o mesmo comprimento que addr_bin, onde os primeiros prefix_length bits são iguais aos addr_bin e os bits restantes são 0

Esta função gera um erro se LENGTH(addr_bin) não for 4 ou 16, ou se prefix_len for negativo ou maior que LENGTH(addr_bin) * 8.

Tipo de dados retornados

BYTES

Exemplo

SELECT
  FORMAT("%T", x) as addr_bin, prefix_length,
  FORMAT("%T", NET.IP_TRUNC(x, prefix_length)) AS ip_trunc
FROM UNNEST([
  STRUCT(b"\xAA\xBB\xCC\xDD" as x, 0 as prefix_length),
  (b"\xAA\xBB\xCC\xDD", 11), (b"\xAA\xBB\xCC\xDD", 12),
  (b"\xAA\xBB\xCC\xDD", 24), (b"\xAA\xBB\xCC\xDD", 32),
  (b'0123456789@ABCDE', 80)
]);
addr_bin prefix_length ip_trunc
b"\xaa\xbb\xcc\xdd" 0 b"\x00\x00\x00\x00"
b"\xaa\xbb\xcc\xdd" 11 b"\xaa\xa0\x00\x00"
b"\xaa\xbb\xcc\xdd" 12 b"\xaa\xb0\x00\x00"
b"\xaa\xbb\xcc\xdd" 24 b"\xaa\xbb\xcc\x00"
b"\xaa\xbb\xcc\xdd" 32 b"\xaa\xbb\xcc\xdd"
b"0123456789@ABCDE" 80 b"0123456789\x00\x00\x00\x00\x00\x00"

NET.IPV4_FROM_INT64

NET.IPV4_FROM_INT64(integer_value)

Descrição

Converte um endereço IPv4 do formato inteiro para binário (BYTES) na ordem de bytes da rede. Na entrada do inteiro, o bit menos significativo do endereço IP é armazenado no bit menos significativo do inteiro, independentemente da arquitetura do host ou do cliente. Por exemplo, 1 significa 0.0.0.1 e 0x1FF significa 0.0.1.255.

Essa função verifica se todos os 32 bits mais significativos são 0, ou se todos os 33 bits mais significativos são 1 (estendido por sinal de um inteiro de 32 bits). Em outras palavras, a entrada precisa estar no intervalo [-0x80000000, 0xFFFFFFFF]. Caso contrário, esta função gerará um erro.

Essa função não aceita IPv6.

Tipo de dados retornados

BYTES

Exemplo

SELECT x, x_hex, FORMAT("%T", NET.IPV4_FROM_INT64(x)) AS ipv4_from_int64
FROM (
  SELECT CAST(x_hex AS INT64) x, x_hex
  FROM UNNEST(["0x0", "0xABCDEF", "0xFFFFFFFF", "-0x1", "-0x2"]) AS x_hex
);
x x_hex ipv4_from_int64
0 0x0 b"\x00\x00\x00\x00"
11259375 0xABCDEF b"\x00\xab\xcd\xef"
4294967295 0xFFFFFFFF b"\xff\xff\xff\xff"
-1 -0x1 b"\xff\xff\xff\xff"
-2 -0x2 b"\xff\xff\xff\xfe"

NET.IPV4_TO_INT64

NET.IPV4_TO_INT64(addr_bin)

Descrição

Converte um endereço IPv4 do formato binário (BYTES) na ordem de bytes da rede para inteiro. Na saída do número inteiro, o bit menos significativo do endereço IP é armazenado no bit menos significativo do número inteiro, independentemente da arquitetura do host ou do cliente. Por exemplo, 1 significa 0.0.0.1, e 0x1FF significa 0.0.1.255. A saída está no intervalo [0, 0xFFFFFFFF].

Se o comprimento de entrada não for 4, esta função gerará um erro.

Essa função não aceita IPv6.

Tipo de dados retornados

INT64

Exemplo

SELECT
  FORMAT("%T", x) AS addr_bin,
  FORMAT("0x%X", NET.IPV4_TO_INT64(x)) AS ipv4_to_int64
FROM
UNNEST([b"\x00\x00\x00\x00", b"\x00\xab\xcd\xef", b"\xff\xff\xff\xff"]) AS x;
addr_bin ipv4_to_int64
b"\x00\x00\x00\x00" 0x0
b"\x00\xab\xcd\xef" 0xABCDEF
b"\xff\xff\xff\xff" 0xFFFFFFFF

NET.HOST

NET.HOST(url)

Descrição

Toma uma URL como uma STRING e retorna o host como uma STRING. Para os melhores resultados, os valores de URL precisam obedecer ao formato conforme definido pelo RFC 3986. Se não estiverem em conformidade com a formatação RFC 3986, a função analisará a entrada da melhor maneira possível e retornará um resultado relevante. Se não for possível analisar a entrada, será retornado NULL.

Nota: a função não realiza a normalização.

Tipo de dados retornados

STRING

Exemplo

SELECT
  FORMAT("%T", input) AS input,
  description,
  FORMAT("%T", NET.HOST(input)) AS host,
  FORMAT("%T", NET.PUBLIC_SUFFIX(input)) AS suffix,
  FORMAT("%T", NET.REG_DOMAIN(input)) AS domain
FROM (
  SELECT "" AS input, "invalid input" AS description
  UNION ALL SELECT "http://abc.xyz", "standard URL"
  UNION ALL SELECT "//user:password@a.b:80/path?query",
                   "standard URL with relative scheme, port, path and query, but no public suffix"
  UNION ALL SELECT "https://[::1]:80", "standard URL with IPv6 host"
  UNION ALL SELECT "http://例子.卷筒纸.中国", "standard URL with internationalized domain name"
  UNION ALL SELECT "    www.Example.Co.UK    ",
                   "non-standard URL with spaces, upper case letters, and without scheme"
  UNION ALL SELECT "mailto:?to=&subject=&body=", "URI rather than URL--unsupported"
);
entrada descrição host sufixo domínio
"" entrada inválida NULL NULL NULL
"http://abc.xyz" URL padrão "abc.xyz" "xyz" "abc.xyz"
"//user:password@a.b:80/path?query" URL padrão com esquema, porta, caminho e consulta relativos, mas sem sufixo público "a.b" NULL NULL
"https://[::1]:80" URL padrão com host IPv6 "[::1]" NULL NULL
"http://例子.卷筒纸.中国" URL padrão com nome de domínio internacionalizado "例子.卷筒纸.中国" "中国" "卷筒纸.中国"
"    www.Example.Co.UK    " URL não padrão com espaços, letras maiúsculas e sem esquema "www.Example.Co.UK" "Co.UK" "Example.Co.UK"
"mailto:?to=&subject=&body=" URI, e não URL, não compatível "mailto" NULL NULL

NET.PUBLIC_SUFFIX

NET.PUBLIC_SUFFIX(url)

Descrição

Toma uma URL como uma STRING e retorna o sufixo público (como com, org ou net) como uma STRING. Um sufixo público é um domínio da ICANN registrado em publicsuffix.org. Para os melhores resultados, os valores de URL precisam obedecer ao formato conforme definido pelo RFC 3986. Se não estiverem em conformidade com a formatação RFC 3986, a função analisará a entrada da melhor maneira possível e retornará um resultado relevante.

A função retornará NULL, se qualquer uma das seguintes situações for verdadeira:

  • Não é possível analisar o host com a entrada.
  • O host analisado contém pontos adjacentes no meio (não à esquerda ou direita).
  • O host analisado não contém um sufixo público.

Antes de pesquisar o sufixo público, essa função normalizará temporariamente o host convertendo letras em inglês em maiúsculas para minúsculas e codificando todos os caracteres não ASCII com Punycode. Depois, ela retornará o sufixo público como parte do host original em vez do host normalizado:

Observação: a função não realiza a normalização Unicode.

Observação: os dados de sufixo público em publicsuffix.org também contêm domínios privados. Essa função ignora os domínios privados.

Observação: os dados do sufixo público podem mudar ao longo do tempo. Por conseguinte, a entrada que produz um resultado NULL no momento pode produzir um valor não NULL no futuro.

Tipo de dados retornados

STRING

Exemplo

SELECT
  FORMAT("%T", input) AS input,
  description,
  FORMAT("%T", NET.HOST(input)) AS host,
  FORMAT("%T", NET.PUBLIC_SUFFIX(input)) AS suffix,
  FORMAT("%T", NET.REG_DOMAIN(input)) AS domain
FROM (
  SELECT "" AS input, "invalid input" AS description
  UNION ALL SELECT "http://abc.xyz", "standard URL"
  UNION ALL SELECT "//user:password@a.b:80/path?query",
                   "standard URL with relative scheme, port, path and query, but no public suffix"
  UNION ALL SELECT "https://[::1]:80", "standard URL with IPv6 host"
  UNION ALL SELECT "http://例子.卷筒纸.中国", "standard URL with internationalized domain name"
  UNION ALL SELECT "    www.Example.Co.UK    ",
                   "non-standard URL with spaces, upper case letters, and without scheme"
  UNION ALL SELECT "mailto:?to=&subject=&body=", "URI rather than URL--unsupported"
);
entrada descrição host sufixo domínio
"" entrada inválida NULL NULL NULL
"http://abc.xyz" URL padrão "abc.xyz" "xyz" "abc.xyz"
"//user:password@a.b:80/path?query" URL padrão com esquema, porta, caminho e consulta relativos, mas sem sufixo público "a.b" NULL NULL
"https://[::1]:80" URL padrão com host IPv6 "[::1]" NULL NULL
"http://例子.卷筒纸.中国" URL padrão com nome de domínio internacionalizado "例子.卷筒纸.中国" "中国" "卷筒纸.中国"
"    www.Example.Co.UK    " URL não padrão com espaços, letras maiúsculas e sem esquema "www.Example.Co.UK" "Co.UK" "Example.Co.UK
"mailto:?to=&subject=&body=" URI, e não URL, não compatível "mailto" NULL NULL

NET.REG_DOMAIN

NET.REG_DOMAIN(url)

Descrição

Toma uma URL como uma STRING e retorna o domínio registrado ou registrável (o sufixo público mais um rótulo precedente), como uma STRING. Para os melhores resultados, os valores de URL precisam obedecer ao formato conforme definido pelo RFC 3986. Se não estiverem em conformidade com a formatação RFC 3986, a função analisará a entrada da melhor maneira possível e retornará um resultado relevante.

A função retornará NULL, se qualquer uma das seguintes situações for verdadeira:

  • Não é possível analisar o host com a entrada.
  • O host analisado contém pontos adjacentes no meio (não à esquerda ou direita).
  • O host analisado não contém um sufixo público.
  • O host analisado contém somente um sufixo público sem rótulo precedente.

Antes de pesquisar o sufixo público, essa função normalizará temporariamente o host convertendo letras em inglês em maiúsculas para minúsculas e codificando todos os caracteres não ASCII com Punycode. Depois, ela retornará o domínio registrado ou registrável como parte do host original em vez do host normalizado.

Observação: a função não realiza a normalização Unicode.

Observação: os dados de sufixo público em publicsuffix.org também contêm domínios privados. Essa função não trata um domínio privado como um sufixo público. Por exemplo, se "us.com" for um domínio privado nos dados de sufixo público, NET.REG_DOMAIN ("foo.us.com") retornará "us.com" (o sufixo público "com" mais o rótulo anterior "us ") em vez de "foo.us.com" (o domínio privado "us.com" mais o rótulo anterior "foo").

Observação: os dados do sufixo público podem mudar ao longo do tempo. Por conseguinte, a entrada que produz um resultado NULL no momento pode produzir um valor não NULL no futuro.

Tipo de dados retornados

STRING

Exemplo

SELECT
  FORMAT("%T", input) AS input,
  description,
  FORMAT("%T", NET.HOST(input)) AS host,
  FORMAT("%T", NET.PUBLIC_SUFFIX(input)) AS suffix,
  FORMAT("%T", NET.REG_DOMAIN(input)) AS domain
FROM (
  SELECT "" AS input, "invalid input" AS description
  UNION ALL SELECT "http://abc.xyz", "standard URL"
  UNION ALL SELECT "//user:password@a.b:80/path?query",
                   "standard URL with relative scheme, port, path and query, but no public suffix"
  UNION ALL SELECT "https://[::1]:80", "standard URL with IPv6 host"
  UNION ALL SELECT "http://例子.卷筒纸.中国", "standard URL with internationalized domain name"
  UNION ALL SELECT "    www.Example.Co.UK    ",
                   "non-standard URL with spaces, upper case letters, and without scheme"
  UNION ALL SELECT "mailto:?to=&subject=&body=", "URI rather than URL--unsupported"
);
entrada descrição host sufixo domínio
"" entrada inválida NULL NULL NULL
"http://abc.xyz" URL padrão "abc.xyz" "xyz" "abc.xyz"
"//user:password@a.b:80/path?query" URL padrão com esquema, porta, caminho e consulta relativos, mas sem sufixo público "a.b" NULL NULL
"https://[::1]:80" URL padrão com host IPv6 "[::1]" NULL NULL
"http://例子.卷筒纸.中国" URL padrão com nome de domínio internacionalizado "例子.卷筒纸.中国" "中国" "卷筒纸.中国"
"    www.Example.Co.UK    " URL não padrão com espaços, letras maiúsculas e sem esquema "www.Example.Co.UK" "Co.UK" "Example.Co.UK"
"mailto:?to=&subject=&body=" URI, e não URL, não compatível "mailto" NULL NULL

Operadores

Os operadores são representados por caracteres especiais ou palavras-chave. Eles não usam a sintaxe de chamada de função. Um operador manipula qualquer número de entradas de dados, também chamados de operandos, e retorna um resultado.

Convenções comuns:

  • A menos que especificado em contrário, todos os operadores retornam NULL quando um dos operandos é NULL.
  • Todos os operadores geram um erro se o resultado do cálculo estourar.
  • Para todas as operações de ponto flutuante, +/-inf e NaN só poderão ser retornadas se um dos operandos for +/-inf ou NaN. Em outros casos, um erro é retornado.

A tabela a seguir lista todos os operadores do Cloud Spanner SQL da precedência mais alta à mais baixa, isto é, na ordem em que são avaliados em uma declaração.

Ordem de precedência Operador Tipos de dados de entrada Nome Arity do operador
1 . STRUCT
Operador de acesso ao campo do membro Binário
  [ ] ARRAY Posição da matriz. Precisa ser usado com OFFSET ou ORDINAL. Consulte Funções Array . Binário
2 + Todos os tipos numéricos Mais unário. Unário
  - Todos os tipos numéricos Unário menos Unário
  ~ Inteiro ou BYTES Bit a bit NOT Unário
3 * Todos os tipos numéricos Multiplicação Binário
  / Todos os tipos numéricos Divisão Binário
  || STRING, BYTES ou ARRAY<T> Operador de concatenação Binário
4 + Todos os tipos numéricos Adição Binário
  - Todos os tipos numéricos Subtração Binário
5 << Inteiro ou BYTES Bit a bit deslocado para a esquerda Binário
  >> Inteiro ou BYTES Bit a bit deslocado para a direita Binário
6 & Inteiro ou BYTES Bit a bit and Binário
7 ^ Inteiro ou BYTES Bit a bit xor Binário
8 | Inteiro ou BYTES Bit a bit or Binário
9 (operadores de comparação) = Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados. Igual Binário
  < Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados. Menos de Binário
  > Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados. Maior que Binário
  <= Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados. Menor que ou igual a Binário
  >= Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados. Maior que ou igual a Binário
  !=, <> Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados. Diferente Binário
  [NOT] LIKE STRING e byte O valor [não] corresponde ao padrão especificado Binário
  [NOT] BETWEEN Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados. O valor [não] está dentro do intervalo especificado Binário
  [NOT] IN Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados. O valor [não] está no grupo de valores especificados Binário
  IS [NOT] NULL Todos O valor [não] é NULL Unário
  IS [NOT] TRUE BOOL O valor [não] é TRUE. Unário
  IS [NOT] FALSE BOOL O valor [não] é FALSE. Unário
10 NOT BOOL Lógico NOT Unário
11 AND BOOL Lógico AND Binário
12 OR BOOL Lógico OR Binário

Os operadores com a mesma precedência são associativos da esquerda, ou seja, são agrupados da esquerda para a direita. Por exemplo, a expressão:

x AND y AND z

é interpretada como

( ( x AND y ) AND z )

A expressão:

x * y / z

é interpretada como:

( ( x * y ) / z )

Todos os operadores de comparação têm a mesma prioridade, mas eles não são associativos. Portanto, é preciso usar parênteses para resolver ambiguidade. Por exemplo:

(x < y) IS FALSE

Operadores de acesso ao elemento

Operador Sintaxe Tipos de dados de entrada Tipo de dados de resultado Descrição
. expression.fieldname1... STRUCT
Tipo T armazenado em nomedocampo1 Operador de ponto. Pode ser usado para acessar campos aninhados, como expressão.nomedocampo1.nomedocampo2...
[ ] matriz_expressão [posição_palavra-chave (int_expressão ) ] Consulte Funções ARRAY Tipo T armazenado em ARRAY position_keyword pode ser OFFSET ou ORDINAL. Consulte Funções ARRAY para saber mais sobre as duas funções que usam esse operador.

Operadores aritméticos

Todos os operadores aritméticos aceitam a entrada do tipo numérico T, também presente no tipo de resultado, a menos que indicado de outra forma na descrição abaixo:

Nome Sintaxe
Adição X + Y
Subtração X - Y
Multiplicação X * Y
Divisão X / Y
Mais unário. + X
Unário menos - X

NOTA: as operações de divisão por zero retornam um erro. Para retornar um resultado diferente, considere as funções IEEE_DIVIDE ou SAFE_DIVIDE.

Tipos de resultados para adição, subtração e multiplicação:

INPUTINT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

Tipos de resultados para divisão:

INPUTINT64NUMERICFLOAT64
INT64FLOAT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

Tipos de resultados para Unary Plus:

INPUTINT64NUMERICFLOAT64
OUTPUTINT64NUMERICFLOAT64

Tipos de resultados para Unário menos:

INPUTINT64NUMERICFLOAT64
OUTPUTINT64NUMERICFLOAT64

Operadores bit a bit

Todos os operadores bit a bit retornam o mesmo tipo e comprimento do primeiro operando.

Nome Sintaxe Tipo de dados de entrada Descrição
Bit a bit not ~ X Inteiro ou BYTES Executa a negação lógica em cada bit, formando o complemento dos números um do valor binário.
Bit a bit or X | Y X: número inteiro ou BYTES
Y: o mesmo tipo que X
Toma dois padrões de bits de comprimento igual e executa a operação lógica OR inclusiva em cada par dos bits correspondentes. Esse operador gerará um erro se X e Y forem BYTES de comprimentos diferentes.
Bit a bit xor X ^ Y X: número inteiro ou BYTES
Y: o mesmo tipo que X
Toma dois padrões de bits de comprimento igual e executa a operação lógica OR exclusiva em cada par dos bits correspondentes. Esse operador gerará um erro se X e Y forem BYTES de comprimentos diferentes.
Bit a bit and X & Y X: número inteiro ou BYTES
Y: o mesmo tipo que X
Toma dois padrões de bits de comprimento igual e executa a operação lógica AND em cada par dos bits correspondentes. Esse operador gerará um erro se X e Y forem BYTES de comprimentos diferentes.
Deslocado para a esquerda X << Y X: número inteiro ou BYTES
Y: INT64
Desloca o primeiro operando X para a esquerda. Esse operador retorna 0 ou uma sequência de bytes de b'\x00' se o segundo operando, Y, for maior ou igual ao comprimento do bit do primeiro operando, X, (por exemplo, 64, se X tiver o tipo INT64). Esse operador gerará um erro se Y for negativo.
Deslocado para a direita X >> Y X: número inteiro ou BYTES
Y: INT64
Desloca o primeiro operando X para a direita. Esse operador não faz a extensão de bit de sinal com um tipo com sinal. Isso significa que ele preenche com 0 os bits vagos à esquerda. Esse operador retorna 0 ou uma sequência de bytes de b'\x00' se o segundo operando, Y, for maior ou igual ao comprimento do bit do primeiro operando, X, (por exemplo, 64, se X tiver o tipo INT64). Esse operador gerará um erro se Y for negativo.

Operadores lógicos

O Cloud Spanner SQL é compatível com os operadores lógicos AND, OR e NOT. Os operadores lógicos permitem apenas entradas BOOL ou NULL e usam lógica de três valores (em inglês) para produzir um resultado. O resultado pode ser TRUE, FALSE ou NULL:

x y x AND y x OR y
TRUE TRUE TRUE TRUE
TRUE FALSE FALSE TRUE
TRUE NULL NULL TRUE
FALSE TRUE FALSE TRUE
FALSE FALSE FALSE FALSE
FALSE NULL FALSE NULL
NULL TRUE NULL TRUE
NULL FALSE FALSE NULL
NULL NULL NULL NULL
x NOT X
TRUE FALSE
FALSE TRUE
NULL NULL

Por exemplo

Os exemplos nesta seção fazem referência a uma tabela chamada entry_table:

+-------+
| entry |
+-------+
| a     |
| b     |
| c     |
| NULL  |
+-------+
SELECT 'a' FROM entry_table WHERE entry = 'a'

-- a => 'a' = 'a' => TRUE
-- b => 'b' = 'a' => FALSE
-- NULL => NULL = 'a' => NULL

+-------+
| entry |
+-------+
| a     |
+-------+
SELECT entry FROM entry_table WHERE NOT (entry = 'a')

-- a => NOT('a' = 'a') => NOT(TRUE) => FALSE
-- b => NOT('b' = 'a') => NOT(FALSE) => TRUE
-- NULL => NOT(NULL = 'a') => NOT(NULL) => NULL

+-------+
| entry |
+-------+
| b     |
| c     |
+-------+
SELECT entry FROM entry_table WHERE entry IS NULL

-- a => 'a' IS NULL => FALSE
-- b => 'b' IS NULL => FALSE
-- NULL => NULL IS NULL => TRUE

+-------+
| entry |
+-------+
| NULL  |
+-------+

Operadores de comparação

As comparações sempre retornam BOOL. Elas geralmente exigem que os dois operandos sejam do mesmo tipo. Se os operandos forem de tipos diferentes e o Cloud Spanner SQL puder converter os valores desses tipos em um tipo comum sem perda de precisão, ele geralmente os coagirá a esse tipo comum para a comparação. O Cloud Spanner SQL geralmente coage literais ao tipo não literal, quando o primeiro tipo está presente. Os tipos de dados comparáveis são definidos na seção Tipos de dados.

STRUCTs aceitam apenas quatro operadores de comparação: igual (=), diferente (!= e <>) e IN.

As regras a seguir são aplicadas na comparação desses tipos de dados:

  • FLOAT64: todas as comparações com NaN retornam FALSE, exceto != e <>, que retornam TRUE.
  • BOOL: FALSE é menor que TRUE.
  • STRING: as strings são comparadas ponto de código a ponto de código. Isso significa que strings canonicamente equivalentes somente serão comparadas como iguais se tiverem sido normalizadas primeiro.
  • NULL: A convenção é válida aqui: qualquer operação com uma entrada NULL retorna NULL.
Nome Sintaxe Descrição
Menor que X < Y Retorna TRUE, se X for menor que Y.
Menor que ou igual a X <= Y Retorna TRUE, se X for menor que ou igual a Y.
Maior que X > Y Retorna TRUE, se X for maior que Y.
Maior que ou igual a X >= Y Retorna TRUE, se X for maior que ou igual a Y.
Igual X = Y Retorna TRUE, se X for igual a Y.
Diferente X != Y
X <> Y
Retorna TRUE, se X não for igual a Y.
BETWEEN X [NOT] BETWEEN Y AND Z Retorna TRUE, se X [não] estiver dentro do intervalo especificado. O resultado de "X BETWEEN Y AND Z" é equivalente a "Y <= X AND X <= Z", mas X é avaliado somente uma vez no primeiro.
LIKE X [NOT] LIKE Y Verifica se a STRING no primeiro operando, X, corresponde a um padrão especificado pelo segundo operando, Y. As expressões podem conter estes caracteres:
  • Um sinal de porcentagem "%" corresponde a qualquer número de caracteres ou bytes.
  • Um sublinhado "_" corresponde a um único caractere ou byte.
  • É possível inserir um caractere de escape em "\", "_" ou "%" usando duas barras invertidas. Por exemplo, "\\%". Se você estiver usando strings brutas, basta uma barra invertida. Por exemplo, r"\%";
IN Várias - veja abaixo Retorna FALSE se o operando à direita estiver vazio. Retorna NULL se o operando à esquerda for NULL. Retorna TRUE ou NULL, nunca FALSE, se o operando à direita contiver NULL. Os argumentos em qualquer lado do operador IN são expressões gerais. Nenhum operando precisa ser um literal, embora seja mais comum usar um literal à direita. X é avaliado apenas uma vez.

Quando a igualdade dos valores com tipo de dados STRUCT é testada, é possível que um ou mais campos sejam NULL. Nesses casos:

  • Se todos os valores de campo não NULL forem iguais, a comparação retornará NULL.
  • Se qualquer valor de campo não NULL for diferente, a comparação retornará FALSE.

A tabela a seguir demonstra como os tipos de dados STRUCT são comparados quando têm campos com valores NULL.

Struct1 Struct2 Struct1 = Struct2
STRUCT(1, NULL) STRUCT(1, NULL) NULL
STRUCT(1, NULL) STRUCT(2, NULL) FALSE
STRUCT(1,2) STRUCT(1, NULL) NULL

Operadores IN

O operador IN aceita estas sintaxes:

x [NOT] IN (y, z, ... ) # Requires at least one element
x [NOT] IN (<subquery>)
x [NOT] IN UNNEST(<array expression>) # analysis error if the expression
                                      # does not return an ARRAY type.

Os argumentos em qualquer lado do operador IN são expressões gerais. É comum o uso de literais na expressão do lado direito. No entanto, isso não é obrigatório.

A semântica de:

x IN (y, z, ...)

é definida como equivalente a

(x = y) OR (x = z) OR ...

e os formatos de subconsulta e matriz são definidos de maneira semelhante.

x NOT IN ...

é equivalente a:

NOT(x IN ...)

O formato UNNEST trata uma varredura de matriz como UNNEST na cláusula FROM:

x [NOT] IN UNNEST(<array expression>)

Esse formato é usado muitas vezes com parâmetros ARRAY. Por exemplo:

x IN UNNEST(@array_parameter)

Observação: um ARRAY NULL será tratado de maneira equivalente a um ARRAY vazio.

Consulte o tópico Matrizes para mais informações sobre como usar essa sintaxe.

Quando o operador IN é usado, aplica-se a seguinte semântica:

  • IN com uma expressão vazia do lado direito é sempre FALSE.
  • IN com uma expressão NULL do lado esquerdo e uma expressão não vazia do lado direito é sempre NULL.
  • IN com NULL na lista IN retorna somente TRUE ou NULL, nunca FALSE.
  • NULL IN (NULL) retorna NULL.
  • IN UNNEST(<NULL array>) retorna FALSE (não NULL).
  • NOT IN com NULL na lista IN retorna somente FALSE ou NULL, nunca TRUE.

IN pode ser usado com chaves de várias partes usando a sintaxe struct do construtor. Exemplo:

(Key1, Key2) IN ( (12,34), (56,78) )
(Key1, Key2) IN ( SELECT (table.a, table.b) FROM table )

Para mais informações sobre essa sintaxe, consulte a seção Tipo struct do tópico Tipos de dados.

Operadores IS

Os operadores IS retornam TRUE ou FALSE para a condição que estão testando. Eles nunca retornam NULL, mesmo para entradas NULL, ao contrário das funções IS_INF e IS_NAN, definidas em Funções matemáticas. Se NOT estiver presente, o valor de saída BOOL será invertido.

Sintaxe da função Tipo de dados de entrada Tipo de dados de resultado Descrição

X IS [NOT] NULL
Qualquer tipo de valor BOOL Retorna TRUE se o operando X for avaliado como NULL. Caso contrário, retorna FALSE.

X IS [NOT] TRUE
BOOL BOOL Retorna TRUE, se o operando BOOL for avaliado como TRUE. Caso contrário, retorna FALSE.

X IS [NOT] FALSE
BOOL BOOL Retorna TRUE se o operando BOOL for avaliado como FALSE. Caso contrário, retorna FALSE.

Operador de concatenação

O operador de concatenação combina vários valores em um.

Sintaxe da função Tipo de dados de entrada Tipo de dados de resultado

STRING || STRING [ || ... ]
STRING STRING

BYTES || BYTES [ || ... ]
BYTES STRING

ARRAY<T> || ARRAY<T> [ || ... ]
ARRAY<T> ARRAY<T>

Expressões condicionais

As expressões condicionais impõem restrições na ordem de avaliação das entradas. Em essência, elas são avaliadas da esquerda para a direita, com curto-circuito, e somente avaliam o valor da saída escolhida. Por outro lado, todas as entradas das funções regulares são avaliadas antes de chamar a função. O curto-circuito em expressões condicionais pode ser explorado para o tratamento de erros ou ajuste do desempenho.

expr CASE

CASE expr
  WHEN expr_to_match THEN result
  [ ... ]
  [ ELSE else_result ]
END

Descrição

Compara expr com expr_to_match de cada cláusula WHEN consecutiva e retorna o primeiro resultado em que essa comparação retorna verdadeiro. As cláusulas WHEN restantes e else_result não são avaliadas. Se a comparação expr = expr_to_match retorna falso ou NULL para todas as cláusulas WHEN, retorna else_result se estiver presente; se não estiver presente, retorna NULL.

expr e expr_to_match podem ser de qualquer tipo. Eles precisam ser implicitamente coercíveis para um supertipo comum. As comparações de igualdade são feitas em valores forçados. Pode haver vários tipos de result. As expressões result e else_result precisam ser coercíveis a um supertipo comum.

Tipo de dados retornados

Supertipo de result[, ...] e else_result.

Exemplo

WITH Numbers AS
 (SELECT 90 as A, 2 as B UNION ALL
  SELECT 50, 8 UNION ALL
  SELECT 60, 6 UNION ALL
  SELECT 50, 10)
SELECT A, B,
  CASE A
    WHEN 90 THEN 'red'
    WHEN 50 THEN 'blue'
    ELSE 'green'
  END
  AS result
FROM Numbers

+------------------+
| A  | B  | result |
+------------------+
| 90 | 2  | red    |
| 50 | 8  | blue   |
| 60 | 6  | green  |
| 50 | 10 | blue   |
+------------------+

CASE

CASE
  WHEN condition THEN result
  [ ... ]
  [ ELSE else_result ]
  END

Descrição

Avalia a condição de cada cláusula WHEN sucessiva e retorna o primeiro resultado em que a condição é verdadeira. As cláusulas WHEN e else_result restantes não são avaliadas. Se todas as condições forem falsas ou NULL, retornará else_result se presente; se não estiver presente, retornará NULL.

condition precisa ser uma expressão booleana. Pode haver vários tipos de result. As expressões result e else_result precisam ser implicitamente coercíveis a um supertipo comum.

Tipo de dados retornados

Supertipo de result[, ...] e else_result.

Exemplo

WITH Numbers AS
 (SELECT 90 as A, 2 as B UNION ALL
  SELECT 50, 6 UNION ALL
  SELECT 20, 10)
SELECT A, B,
  CASE
    WHEN A > 60 THEN 'red'
    WHEN A > 30 THEN 'blue'
    ELSE 'green'
  END
  AS result
FROM Numbers

+------------------+
| A  | B  | result |
+------------------+
| 90 | 2  | red    |
| 50 | 6  | blue   |
| 20 | 10 | green  |
+------------------+

COALESCE

COALESCE(expr[, ...])

Descrição

Retorna o valor da primeira expressão não nula. As expressões restantes não são avaliadas. Uma expressão de entrada pode ser de qualquer tipo. Pode haver vários tipos de expressão de entrada. Todas as expressões de entrada precisam ser implicitamente coercíveis para um supertipo comum.

Tipo de dados retornados

Supertipo de expr[, ...].

Exemplos

SELECT COALESCE('A', 'B', 'C') as result

+--------+
| result |
+--------+
| A      |
+--------+
SELECT COALESCE(NULL, 'B', 'C') as result

+--------+
| result |
+--------+
| B      |
+--------+

IF

IF(expr, true_result, else_result)

Descrição

Se expr for verdadeiro, retornará true_result, caso contrário, retornará else_result. else_result não será avaliado se expr for verdadeiro. true_result não será avaliado se expr for falso ou NULL.

expr precisa ser uma expressão booleana. true_result e else_result precisam ser coercíveis a um supertipo comum.

Tipo de dados retornados

Supertipo de true_result e else_result.

Exemplo

WITH Numbers AS
 (SELECT 10 as A, 20 as B UNION ALL
  SELECT 50, 30 UNION ALL
  SELECT 60, 60)
SELECT
  A, B,
  IF( A<B, 'true', 'false') as result
FROM Numbers

+------------------+
| A  | B  | result |
+------------------+
| 10 | 20 | true   |
| 50 | 30 | false  |
| 60 | 60 | false  |
+------------------+

IFNULL

IFNULL(expr, null_result)

Descrição

Se expr for NULL, retornará null_result. Caso contrário, retorna expr. Se expr não for NULL, null_result não será avaliado.

expr e null_result podem ser de qualquer tipo e precisam ser implicitamente coercíveis para um supertipo comum. Sinônimo de COALESCE(expr, null_result).

Tipo de dados retornados

Supertipo de expr ou null_result.

Exemplos

SELECT IFNULL(NULL, 0) as result

+--------+
| result |
+--------+
| 0      |
+--------+
SELECT IFNULL(10, 0) as result

+--------+
| result |
+--------+
| 10     |
+--------+

NULLIF

NULLIF(expr, expr_to_match)

Descrição

Retornará NULL se expr = expr_to_match for verdadeiro. Caso contrário, retornará expr.

expr e expr_to_match precisam ser implicitamente coercíveis para um supertipo comum e precisam ser comparáveis.

NULLIF não é compatível com tipos STRUCT.

Tipo de dados retornados

Supertipo de expr e expr_to_match.

Exemplo

SELECT NULLIF(0, 0) as result

+--------+
| result |
+--------+
| NULL   |
+--------+
SELECT NULLIF(10, 0) as result

+--------+
| result |
+--------+
| 10     |
+--------+

Subconsultas de expressão

Há quatro tipos de subconsultas de expressão, isto é, subconsultas que são usadas como expressões. As subconsultas de expressão retornam NULL ou um único valor, ao contrário de uma coluna ou tabela, e precisam estar entre parênteses. Consulte Subconsultas para ver uma discussão mais completa sobre subconsultas.

Tipo de subconsulta Tipo de dados de resultado Descrição
Escalar Qualquer tipo T Uma subconsulta entre parênteses dentro de uma expressão (por exemplo, na lista SELECT ou na cláusula WHERE) é interpretada como uma subconsulta escalar. A lista SELECT em uma subconsulta escalar precisa ter exatamente um campo. Se a subconsulta retornar exatamente uma linha, esse único valor será o resultado da subconsulta escalar. Se a subconsulta retornar zero linhas, o valor da subconsulta escalar será NULL. Se a subconsulta retornar mais de uma linha, a consulta falhará com um erro de ambiente de execução. Quando a subconsulta é escrita com SELECT AS STRUCT, ela pode incluir várias colunas, e o valor retornado é o STRUCT criado. Selecionar várias colunas sem usar SELECT AS é um erro.
ARRAY ARRAY Pode usar SELECT AS STRUCT para criar matrizes de estruturas e, por outro lado, selecionar várias colunas sem usar SELECT AS é um erro. Retorna um ARRAY vazio se a subconsulta não retornar linha alguma. Nunca retorna um ARRAY NULL.
IN BOOL Ocorre em uma expressão seguindo o operador IN. A subconsulta precisa produzir uma única coluna cujo tipo é compatível em igualdade com a expressão do lado esquerdo do operador IN. Retorna FALSE, se a subconsulta não retornar linha alguma. x IN () é equivalente a x IN (value, value, ...). Veja o operador IN em Operadores de comparação para a semântica completa.
EXISTS BOOL Retorna TRUE, se a subconsulta produzir uma ou mais linhas. Retorna FALSE, se a subconsulta não produzir linha alguma. Nunca retorna NULL. Ao contrário de todas as outras subconsultas de expressão, não há regras sobre a lista de colunas. Qualquer número de colunas pode ser selecionado. Isso não afetará o resultado da consulta.

Exemplos

Os seguintes exemplos de subconsultas de expressão presumem que t.int_array tem tipo ARRAY<INT64>.

Tipo Subconsulta Tipo de dados de resultado Notas
Escalar (SELECT COUNT(*) FROM t.int_array) INT64  
(SELECT DISTINCT i FROM t.int_array i) INT64, possivelmente erro de tempo de execução  
(SELECT i FROM t.int_array i WHERE i=5) INT64, possivelmente erro de tempo de execução  
(SELECT ARRAY_AGG(i) FROM t.int_array i) ARRAY Usa a função de agregação ARRAY_AGG para retornar uma ARRAY.
(SELECT 'xxx' a) STRING  
(SELECT 'xxx' a, 123 b) Erro Retorna um erro porque há mais de uma coluna
(SELECT AS STRUCT 'xxx' a, 123 b) STRUCT  
(SELECT AS STRUCT 'xxx' a) STRUCT  
ARRAY ARRAY(SELECT COUNT(*) FROM t.int_array) ARRAY de tamanho 1  
ARRAY(SELECT x FROM t) ARRAY  
ARRAY(SELECT 5 a, COUNT(*) b FROM t.int_array) Erro Retorna um erro porque há mais de uma coluna
ARRAY(SELECT AS STRUCT 5 a, COUNT(*) b FROM t.int_array) ARRAY  
ARRAY(SELECT AS STRUCT i FROM t.int_array i) ARRAY Cria uma ARRAY de STRUCTs de um campo
ARRAY(SELECT AS STRUCT 1 x, 2, 3 x) ARRAY Retorna uma ARRAY de STRUCTs com campos anônimos ou duplicados.
ARRAY(SELECT AS TypeName SUM(x) a, SUM(y) b, SUM(z) c from t) array<NomeDoTipo> Seleção de um tipo nomeado. Supõe que NomeDoTipo é um tipo de STRUCT com os campos a,b,c.
STRUCT (SELECT AS STRUCT 1 x, 2, 3 x) STRUCT Cria um STRUCT com campos anônimos ou duplicados.
EXISTS EXISTS(SELECT x,y,z FROM table WHERE y=z) BOOL  
NOT EXISTS(SELECT x,y,z FROM table WHERE y=z) BOOL  
IN x IN (SELECT y FROM table WHERE z) BOOL  
x NOT IN (SELECT y FROM table WHERE z) BOOL  

Funções de depuração

O Cloud Spanner SQL é compatível com as seguintes funções de depuração.

ERROR

ERROR(error_message)

Descrição

Retorna um erro. O argumento error_message é uma STRING.

O Cloud Spanner SQL trata ERROR da mesma maneira como uma expressão qualquer que pode resultar em um erro: não há garantia especial de ordem de avaliação.

Tipos de dados retornados

O Cloud Spanner SQL infere o tipo de retorno em contexto.

Exemplos

No exemplo a seguir, a consulta retornará uma mensagem de erro se o valor da linha não corresponder a um dos dois valores definidos.

SELECT
  CASE
    WHEN value = 'foo' THEN 'Value is foo.'
    WHEN value = 'bar' THEN 'Value is bar.'
    ELSE ERROR(concat('Found unexpected value: ', value))
  END AS new_value
FROM (
  SELECT 'foo' AS value UNION ALL
  SELECT 'bar' AS value UNION ALL
  SELECT 'baz' AS value);

Found unexpected value: baz

No exemplo a seguir, o Cloud Spanner SQL pode avaliar a função ERROR antes ou depois da condição x > 0, porque o Cloud Spanner SQL geralmente não fornece garantias de ordem entre as condições da cláusula WHERE e não há garantias especiais para a função ERROR.

SELECT *
FROM (SELECT -1 AS x)
WHERE x > 0 AND ERROR('Example error');

No próximo exemplo, a cláusula WHERE avalia uma condição IF, que garante que o Cloud Spanner SQL avalie a função ERROR apenas se a condição falhar.

SELECT *
FROM (SELECT -1 AS x)
WHERE IF(x > 0, true, ERROR(FORMAT('Error: x must be positive but is %t', x)));'

Error: x must be positive but is -1