Expressões, funções e operadores no SQL padrão

Veja nesta página as expressões do BigQuery, incluindo funções e 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.
  • Nas funções que fazem diferenciação de fuso horário (conforme indicado na descrição da função), se um fuso horário não for especificado, o padrão UTC será usado.

Prefixo SAFE.

Sintaxe:

SAFE.function_name()

Descrição

Se você iniciar uma função 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          |
+-------------+

Funções compatíveis

O BigQuery é compatível com o uso do prefixo SAFE. com a maioria das funções escalares que podem gerar erros, incluindo funções STRING, funções matemáticas, funções DATE, funções DATETIME e funções TIMESTAMP. O BigQuery não é compatível com o uso do prefixo SAFE. com funções agregadas, analíticas ou definidas pelo usuário.

Como chamar funções definidas pelo usuário (UDFs) persistentes

Depois de criar uma UDF persistente, é possível chamá-la como qualquer outra função, prefixada com o nome do conjunto de dados em que foi definida como prefixo.

Sintaxe

[`project_name`].dataset_name.function_name([parameter_value[, ...]])

Para chamar uma UDF em um projeto diferente do projeto que você está usando para executar a consulta, project_name é obrigatório.

Exemplos

O seguinte exemplo cria uma UDF chamada multiply_by_three e a chama do mesmo projeto.

CREATE FUNCTION my_dataset.multiply_by_three(x INT64) AS (x * 3);

SELECT my_dataset.multiply_by_three(5) AS result; -- returns 15

O exemplo a seguir chama uma UDF persistente de outro projeto.


CREATE `other_project`.other_dataset.other_function(x INT64, y INT64)
  AS (x * y * 2);

SELECT `other_project`.other_dataset.other_function(3, 4); --returns 24

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 é conversão implícita, realizada automaticamente pelo BigQuery nas condições descritas abaixo.
  • Há um terceiro grupo de funções de conversão que tem seus próprios nomes de função, como UNIX_DATE().

A tabela a seguir resume todas as possibilidades de CAST e coerção dos tipos de dados do BigQuery. A coluna "Coerção para" se aplica a todas as expressões de um determinado tipo de dados (por exemplo, uma coluna), mas literais e parâmetros também podem ser forçados. Consulte Coerção de literal e Coerção de parâmetro para mais detalhes.

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

Cast

Sintaxe:

CAST(expr AS typename)

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.

Exemplo:

CAST(x=1 AS STRING)

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

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 ambiente 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.

Quando realizar o cast de uma expressão x dos tipos a seguir, essas regras se aplicam:

De Até Regras ao fazer o cast x
INT64 FLOAT64 Retorna um valor aproximado de FLOAT64, mas potencialmente inexato.
INT64 BOOL Retorna FALSE se x for 0. Caso contrário, retorna TRUE.
NUMERIC Ponto flutuante NUMERIC será convertido no número de ponto flutuante mais próximo com uma possível perda de precisão.
FLOAT64 INT64 Retorna o valor mais próximo de INT64.
Casos em que haja metades, como 1,5 ou -0,5, são arredondados para longe de zero.
FLOAT64 STRING Retorna uma representação de string aproximada.
FLOAT64 NUMERIC Se o número de ponto flutuante tiver mais de nove dígitos depois do ponto decimal, ele 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.
BOOL INT64 Retorna 1 se x for TRUE. Caso contrário, retorna 0.
BOOL STRING Retorna "true" se x for TRUE. Caso contrário, retorna "false".
STRING FLOAT64 Retorna x como um valor de FLOAT64, interpretando-o como tendo a mesma forma de um FLOAT64 literal válido.
Também aceita casts de "inf", "+inf", "-inf", e "nan".
As conversões não diferenciam maiúsculas de minúsculas.
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 do ponto decimal exceder nove, o valor resultando NUMERIC será arredondado para metade de zero para ter nove dígitos depois do ponto decimal.
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.
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.
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 é um UTF-8 válido.
ARRAY ARRAY Precisa ser exatamente do mesmo tipo ARRAY.
STRUCT STRUCT Permitido, se as condições a seguir forem atendidas:
  1. Os dois STRUCTs têm o mesmo número de campos.
  2. Executa-se explicitamente o cast de tipos originais de campos STRUCT para os tipos de campos de destino STRUCT correspondentes (definidos pela ordem e não pelo nome do campo).

Cast seguro

Quando CAST é usado, pode ocorrer falha na consulta se o BigQuery não conseguir executar o cast. 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, exceto que 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.

Cast de strings hexadecimais em inteiros

Se você estiver trabalhando com strings hexadecimais (0x123), faça o cast dessas strings como números inteiros:

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 de tipos de horário

O BigQuery é compatível com o cast de tipos de horário de/para strings da maneira a seguir:

CAST(time_expression AS STRING)
CAST(string_expression AS TIME)

O cast de um tipo de horário para uma string segue o formato HH:MM:SS, não importa qual seja o fuso horário. Ao fazer o cast da string para o horário, a string precisa estar em conformidade com o formato literal do horário compatível, independentemente do fuso horário. Se a expressão da string for inválida ou representar um horário fora do intervalo mínimo/máximo aceito, ocorrerá um erro.

Cast de tipos de data

O BigQuery é compatível com o cast de tipos de data para/de strings da maneira a seguir:

CAST(date_expression AS STRING)
CAST(string_expression AS DATE)

O cast de um tipo de data para uma string segue o formato YYYY-MM-DD, independentemente do fuso horário. No caso do cast da string para a data, a string precisa seguir o formato literal da 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.

Cast de tipos de data e hora

O BigQuery é compatível com o cast de tipos de data e hora de/para strings da maneira a seguir:

CAST(datetime_expression AS STRING)
CAST(string_expression AS DATETIME)

O cast de um tipo de data e hora para uma string segue o formato YYYY-MM-DD HH:MM:SS, não importa qual seja o fuso horário. No cast da string para a data e hora, a string precisa estar em conformidade com o formato literal de data e hora compatível, independentemente do fuso horário. Se a expressão da string for inválida ou representar uma data e hora que está fora do intervalo mínimo/máximo aceito, ocorrerá um erro.

Cast de tipos de carimbo de data/hora

O BigQuery é compatível com o cast de tipos de carimbo de data/hora para/de strings da maneira a seguir:

CAST(timestamp_expression AS STRING)
CAST(string_expression AS TIMESTAMP)

No cast de tipos de carimbo de data/hora para string, o carimbo é interpretado com o fuso horário padrão, que é o UTC. 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.

No cast da string para um carimbo de data/hora, string_expression precisa seguir os formatos literais do carimbo de data/hora compatível. Caso contrário, ocorrerá um erro no ambiente de execução. O próprio string_expression pode conter os time_zone. Consulte fusos horários. Se houver um fuso horário no string_expression, ele será usado para conversão. Caso contrário, será usado o fuso horário padrão, UTC. Se a string tiver menos de seis dígitos, ela será ampliada implicitamente.

Um erro será produzido se string_expression for inválido, tiver mais de seis dígitos de subsegundos (isto é, precisão maior do que microssegundos) ou representar um horário fora do intervalo aceito do carimbo de data/hora.

Cast entre tipos de data e hora, data e carimbo de data/hora

O BigQuery é compatível com o cast entre tipos de data e hora, data e carimbo de data/hora, como exibido na tabela de regras de conversão.

CAST(date_expression AS TIMESTAMP)
CAST(timestamp_expression AS DATE)

O cast de uma data para um carimbo de data/hora interpreta date_expression a partir da meia-noite (início do dia) no fuso horário UTC padrão. 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(datetime_expression AS TIMESTAMP)
CAST(timestamp_expression AS DATETIME)

O cast de uma data e hora para um carimbo de data/hora interpreta datetime_expression a partir da meia-noite (início do dia) no fuso horário UTC padrão.

Coerção

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

Coerção de literal

O BigQuery é compatível com as seguintes coerções de literal:

Tipo de dados de entrada Tipo de dados de resultado Notas
Literal STRING DATE
DATETIME
TIME
TIMESTAMP

A coerção de literal é necessária quando o tipo de literal real é diferente do tipo esperado pela função em questão. Por exemplo, se a função func() tiver um argumento DATE e, em seguida, a expressão func("2014-09-27") for válida porque o literal STRING "2014-09-27" for forçado para DATE.

A conversão de literal é avaliada no momento da análise. Se o literal da entrada não for convertido com sucesso para o tipo de destino, ocorrerá um erro.

Observação: os literais de string não são forçados para tipos numéricos.

Coerção de parâmetro

O BigQuery é compatível com as seguintes coerções de parâmetro:

Tipo de dados de entrada Tipo de dados de resultado
Parâmetro STRING

Se o valor do parâmetro não puder ser forçado com sucesso para o tipo de destino, ocorrerá um erro.

Funções de conversão adicionais

O BigQuery oferece as seguintes funções adicionais de conversão:

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 normalmente têm pelo menos uma linha. Quando o SELECT associado não tem cláusula GROUP BY ou quando determinados modificadores de função de agregação filtram linhas do grupo para serem resumidos, é 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 BigQuery.

ANY_VALUE

ANY_VALUE(expression)  [OVER (...)]

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 RESPECT NULLS fosse especificado; as linhas em que expression é NULL são consideradas e podem ser selecionadas.

Tipos de argumentos compatíveis

Qualquer um

Cláusula opcional

OVER: especifica uma janela. Consulte Funções analíticas.

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     |
+-----------+
SELECT
  fruit,
  ANY_VALUE(fruit) OVER (ORDER BY LENGTH(fruit) ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;

+--------+-----------+
| fruit  | any_value |
+--------+-----------+
| pear   | pear      |
| apple  | pear      |
| banana | apple     |
+--------+-----------+

ARRAY_AGG

ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS]
          [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n])
[OVER (...)]

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. OVER: especifica uma janela. Consulte Funções analíticas. No momento, esta cláusula é incompatível com todas as outras cláusulas em ARRAY_AGG().
  2. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  3. IGNORE NULLS ou RESPECT NULLS: se IGNORE NULLS for especificado, os valores NULL serão excluídos do resultado. Se RESPECT NULLS ou se nenhum for especificado, os valores NULL serão incluídos no resultado. Um erro será gerado se uma matriz no resultado da consulta final contiver um elemento NULL.
  4. ORDER BY: especifica a ordem dos valores.
    • A direção padrão de cada chave de classificação é ASC.
    • NULLs: no contexto da cláusula ORDER BY, NULLs são os valores mínimos possíveis; isto é, NULLs aparecem primeiro em listagens ASC e, por último, em listagens DESC.
    • Tipos de dados de ponto flutuante: consulte Semântica de ponto flutuante para ler sobre classificação e agrupamento.
    • Se DISTINCT também for especificado, a chave de classificação deverá ser igual a expression.
    • Se ORDER BY não for especificado, a ordem dos elementos na matriz de saída será não determinística, o que significa que você poderá receber um resultado diferente sempre que usar essa função.
  5. LIMIT: especifica o número máximo de entradas expression no resultado. O limite n precisa ser uma constante INT64.

Tipos de dados retornados

ARRAY

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

Exemplos

SELECT FORMAT("%T", 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 FORMAT("%T", 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 FORMAT("%T", 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] |
+-------------------+
SELECT FORMAT("%T", ARRAY_AGG(x ORDER BY ABS(x))) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+-------------------------+
| array_agg               |
+-------------------------+
| [1, 1, 2, -2, -2, 2, 3] |
+-------------------------+
SELECT FORMAT("%T", ARRAY_AGG(x LIMIT 5)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+-------------------+
| array_agg         |
+-------------------+
| [2, 1, -2, 3, -2] |
+-------------------+
SELECT FORMAT("%T", ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY x LIMIT 2)) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

+-----------+
| array_agg |
+-----------+
| [-2, 1]   |
+-----------+
SELECT
  x,
  FORMAT("%T", ARRAY_AGG(x) OVER (ORDER BY ABS(x))) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

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

ARRAY_CONCAT_AGG

ARRAY_CONCAT_AGG(expression  [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n])

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. No entanto, um erro será gerado se houver um elemento NULL em uma matriz no resultado da consulta final.

Tipos de argumentos compatíveis

ARRAY

Cláusulas opcionais

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

  1. ORDER BY: especifica a ordem dos valores.
    • A direção padrão de cada chave de classificação é ASC.
    • A ordem da matriz não é compatível e, portanto, a chave de classificação não pode ser igual a expression.
    • NULLs: no contexto da cláusula ORDER BY, NULLs são os valores mínimos possíveis; isto é, NULLs aparecem primeiro em listagens ASC e, por último, em listagens DESC.
    • Tipos de dados de ponto flutuante: consulte Semântica de ponto flutuante para ler sobre classificação e agrupamento.
    • Se ORDER BY não for especificado, a ordem dos elementos na matriz de saída será não determinística, o que significa que você poderá receber um resultado diferente sempre que usar essa função.
  2. LIMIT: especifica o número máximo de entradas expression no resultado. O limite é aplicado ao número de matrizes de entrada, e não ao número de elementos nas matrizes. Uma matriz vazia conta como 1. Uma matriz NULL não é contada. O limite n precisa ser uma constante INT64.

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 FORMAT("%T", 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] |
+-----------------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x))) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+-----------------------------------+
| array_concat_agg                  |
+-----------------------------------+
| [5, 6, 7, 8, 9, 1, 2, 3, 4]       |
+-----------------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x LIMIT 2)) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+--------------------------+
| array_concat_agg         |
+--------------------------+
| [1, 2, 3, 4, 5, 6]       |
+--------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x) LIMIT 2)) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+------------------+
| array_concat_agg |
+------------------+
| [5, 6, 7, 8, 9]  |
+------------------+

AVG

AVG([DISTINCT] expression)  [OVER (...)]

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. OVER: especifica uma janela. Consulte Funções analíticas. No momento, esta cláusula é incompatível com todas as outras cláusulas em AVG().
  2. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.

Tipos de dados retornados

  • NUMERIC caso o tipo de entrada seja NUMERIC.
  • FLOAT64

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 |
+------+
SELECT
  x,
  AVG(x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg
FROM UNNEST([0, 2, NULL, 4, 4, 5]) AS x;

+------+------+
| x    | avg  |
+------+------+
| NULL | NULL |
| 0    | 0    |
| 2    | 1    |
| 4    | 3    |
| 4    | 4    |
| 5    | 4.5  |
+------+------+

BIT_AND

BIT_AND(expression)

Descrição

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

Tipos de argumentos compatíveis

  • INT64

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(expression)

Descrição

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

Tipos de argumentos compatíveis

  • INT64

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)

Descrição

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

Tipos de argumentos compatíveis

  • INT64

Cláusula opcional

DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.

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(*) [OVER (...)]

2. COUNT([DISTINCT] expression) [OVER (...)]

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 dado.

Cláusulas opcionais

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

  1. OVER: especifica uma janela. Consulte Funções analíticas.
  2. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.

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
  x,
  COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
  COUNT(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;

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

+------+------------+---------+
| x    | count_star | count_x |
+------+------------+---------+
| NULL | 1          | 0       |
| 1    | 3          | 3       |
| 4    | 3          | 3       |
| 4    | 3          | 3       |
| 5    | 1          | 1       |
+------+------------+---------+

COUNTIF

COUNTIF(expression)  [OVER (...)]

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áusula opcional

OVER: especifica uma janela. Consulte Funções analíticas.

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            |
+--------------+--------------+
SELECT
  x,
  COUNTIF(x<0) OVER (ORDER BY ABS(x) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS num_negative
FROM UNNEST([5, -2, 3, 6, -10, NULL, -7, 4, 0]) AS x;

+------+--------------+
| x    | num_negative |
+------+--------------+
| NULL | 0            |
| 0    | 1            |
| -2   | 1            |
| 3    | 1            |
| 4    | 0            |
| 5    | 0            |
| 6    | 1            |
| -7   | 2            |
| -10  | 2            |
+------+--------------+

LOGICAL_AND

LOGICAL_AND(expression)

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

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)

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

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)  [OVER (...)]

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 GEOGRAPHY

Cláusula opcional

OVER: especifica uma janela. Consulte Funções analíticas.

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  |
+-----+
SELECT x, MAX(x) OVER (PARTITION BY MOD(x, 2)) AS max
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

+------+------+
| x    | max  |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8    | 8    |
| 4    | 8    |
| 37   | 55   |
| 55   | 55   |
+------+------+

MIN

MIN(expression)  [OVER (...)]

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 GEOGRAPHY

Cláusula opcional

OVER: especifica uma janela. Consulte Funções analíticas.

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   |
+-----+
SELECT x, MIN(x) OVER (PARTITION BY MOD(x, 2)) AS min
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

+------+------+
| x    | min  |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8    | 4    |
| 4    | 4    |
| 37   | 37   |
| 55   | 37   |
+------+------+

STRING_AGG

STRING_AGG([DISTINCT] expression [, delimiter]  [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n])
[OVER (...)]

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. OVER: especifica uma janela. Consulte Funções analíticas. No momento, esta cláusula é incompatível com todas as outras cláusulas em STRING_AGG().
  2. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  3. ORDER BY: especifica a ordem dos valores.
    • A direção padrão de cada chave de classificação é ASC.
    • NULLs: no contexto da cláusula ORDER BY, NULLs são os valores mínimos possíveis; isto é, NULLs aparecem primeiro em listagens ASC e, por último, em listagens DESC.
    • Tipos de dados de ponto flutuante: consulte Semântica de ponto flutuante para ler sobre classificação e agrupamento.
    • Se DISTINCT também for especificado, a chave de classificação deverá ser igual a expression.
    • Se ORDER BY não for especificado, a ordem dos elementos na matriz de saída será não determinística, o que significa que você poderá receber um resultado diferente sempre que usar essa função.
  4. LIMIT: especifica o número máximo de entradas expression no resultado. O limite é aplicado ao número de strings de entrada, e não ao número de caracteres ou bytes nas entradas. Uma string vazia conta como 1. Uma string NULL não é contada. O limite n precisa ser uma constante INT64.

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 |
+-----------------------+
SELECT STRING_AGG(fruit, " & " ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

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

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

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

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

SUM

SUM([DISTINCT] expression)  [OVER (...)]

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. OVER: especifica uma janela. Consulte Funções analíticas.
  2. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.

Tipos de dados retornados

  • Retorna INT64, se a entrada for um número inteiro.
  • Retorna NUMERIC, se o tipo de entrada for NUMERIC.
  • Retorna FLOAT64, se a entrada for um valor de ponto flutuante.

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
  x,
  SUM(x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+---+-----+
| x | sum |
+---+-----+
| 3 | 6   |
| 3 | 6   |
| 1 | 10  |
| 4 | 10  |
| 4 | 10  |
| 1 | 10  |
| 2 | 9   |
| 5 | 9   |
| 2 | 9   |
+---+-----+
SELECT
  x,
  SUM(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

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

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

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

O BigQuery é compatível com as funções de agregação estatística a seguir.

CORR

CORR(X1, X2)  [OVER (...)]

Descrição

Retorna o coeficiente de correlação de Pearson de um grupo de pares de números. O primeiro número de cada par é a variável dependente e o segundo número é a variável independente. O resultado retornado está entre -1 e 1. Um resultado igual a 0 indica que não há correlação.

Essa função ignora pares de entrada que tenham um ou mais valores NULL. Se houver menos de dois pares de entrada sem valores NULL, a função retornará NULL.

Tipos de entradas compatíveis

FLOAT64

Cláusula opcional

OVER: especifica uma janela. Consulte Funções analíticas.

Tipo de dados retornados

FLOAT64

COVAR_POP

COVAR_POP(X1, X2)  [OVER (...)]

Descrição

Retorna a covariância da população de pares de números. O primeiro número é a variável dependente e o segundo é a variável independente. O resultado retornado está entre -Inf e +Inf.

Essa função ignora pares de entrada que tenham um ou mais valores NULL. Se não houver um par de entrada sem valores NULL, esta função retornará NULL. Se houver exatamente um par de entrada sem valores NULL, esta função retornará 0.

Tipos de entradas compatíveis

FLOAT64

Cláusula opcional

OVER: especifica uma janela. Consulte Funções analíticas.

Tipo de dados retornados

FLOAT64

COVAR_SAMP

COVAR_SAMP(X1, X2)  [OVER (...)]

Descrição

Retorna a covariância de amostra de um conjunto de pares de números. O primeiro número é a variável dependente e o segundo é a variável independente. O resultado retornado está entre -Inf e +Inf.

Essa função ignora pares de entrada que tenham um ou mais valores NULL. Se houver menos de dois pares de entrada sem valores NULL, a função retornará NULL.

Tipos de entradas compatíveis

FLOAT64

Cláusula opcional

OVER: especifica uma janela. Consulte Funções analíticas.

Tipo de dados retornados

FLOAT64

STDDEV_POP

STDDEV_POP([DISTINCT] expression)  [OVER (...)]

Descrição

Retorna o desvio padrão polarizado da população dos valores. O resultado retornado está entre 0 e +Inf.

Essa função ignora as entradas NULL. Se todas as entradas forem ignoradas, ela retornará NULL.

Mas se ela receber uma única entrada diferente de NULL, retornará 0.

Tipos de entradas compatíveis

FLOAT64

Cláusulas opcionais

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

  1. OVER: especifica uma janela. Consulte Funções analíticas. No momento, esta cláusula é incompatível com todas as outras cláusulas em STDDEV_POP().
  2. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.

Tipo de dados retornados

FLOAT64

STDDEV_SAMP

STDDEV_SAMP([DISTINCT] expression)  [OVER (...)]

Descrição

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

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

Tipos de entradas compatíveis

FLOAT64

Cláusulas opcionais

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

  1. OVER: especifica uma janela. Consulte Funções analíticas. No momento, esta cláusula é incompatível com todas as outras cláusulas em STDDEV_SAMP().
  2. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.

Tipo de dados retornados

FLOAT64

STDDEV

STDDEV([DISTINCT] expression)  [OVER (...)]

Descrição

Um sinônimo de STDDEV_SAMP.

VAR_POP

VAR_POP([DISTINCT] expression)  [OVER (...)]

Descrição

Retorna a variância polarizada da população dos valores. O resultado retornado está entre 0 e +Inf.

Essa função ignora as entradas NULL. Se todas as entradas forem ignoradas, ela retornará NULL.

Mas se ela receber uma única entrada diferente de NULL, retornará 0.

Tipos de entradas compatíveis

FLOAT64

Cláusulas opcionais

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

  1. OVER: especifica uma janela. Consulte Funções analíticas. No momento, esta cláusula é incompatível com todas as outras cláusulas em VAR_POP().
  2. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.

Tipo de dados retornados

FLOAT64

VAR_SAMP

VAR_SAMP([DISTINCT] expression)  [OVER (...)]

Descrição

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

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

Tipos de entradas compatíveis

FLOAT64

Cláusulas opcionais

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

  1. OVER: especifica uma janela. Consulte Funções analíticas. No momento, esta cláusula é incompatível com todas as outras cláusulas em VAR_SAMP().
  2. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.

Tipo de dados retornados

FLOAT64

VARIANCE

VARIANCE([DISTINCT] expression)  [OVER (...)]

Descrição

Um sinônimo de VAR_SAMP.

Funções de agregação aproximada

As funções de agregação aproximadas são escaláveis no que diz respeito ao uso e tempo da memória. No entanto, elas produzem resultados aproximados em vez de exatos. Essas funções normalmente exigem menos memória do que a função de agregação exata, como COUNT(DISTINCT ...), mas também apresentam incerteza estatística. Isso torna a agregação aproximada apropriada para streamings grandes de dados para os quais o uso de memória linear é impraticável, bem como para dados que já são aproximados.

As funções de agregação aproximadas nesta seção funcionam diretamente nos dados de entrada, em vez de uma estimativa intermediária dos dados. Essas funções não permitem que os usuários especifiquem a precisão da estimativa com sketches. Se quiser especificar a precisão com sketches, consulte:

APPROX_COUNT_DISTINCT

APPROX_COUNT_DISTINCT(expression)

Descrição

Retorna o resultado aproximado para COUNT(DISTINCT expression). O valor retornado é uma estimativa estatística e não necessariamente o valor real.

Essa função é menos exata que COUNT(DISTINCT expression), mas funciona melhor com entradas muito grandes.

Tipos de argumentos compatíveis

Qualquer tipo de dado, exceto: ARRAY STRUCT

Tipos de dados retornados

INT64

Exemplos

SELECT APPROX_COUNT_DISTINCT(x) as approx_distinct
FROM UNNEST([0, 1, 1, 2, 3, 5]) as x;

+-----------------+
| approx_distinct |
+-----------------+
| 5               |
+-----------------+

APPROX_QUANTIL

APPROX_QUANTILES([DISTINCT] expression, number [{IGNORE|RESPECT} NULLS])

Descrição

Retorna os limites aproximados para um grupo de valores expression, sendo que number representa quantos quantis serão criados. Essa função retorna uma matriz de elementos number + 1, em que o primeiro elemento é o mínimo aproximado e o último elemento é o máximo aproximado.

Tipos de argumentos compatíveis

expression pode ser qualquer tipo de dado compatível, exceto: ARRAY STRUCT

number precisa ser 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. IGNORE NULLS ou RESPECT NULLS: se IGNORE NULLS for especificado, os valores NULL serão excluídos do resultado. Se RESPECT NULLS ou se nenhum for especificado, os valores NULL serão incluídos no resultado. Um erro será gerado se uma matriz no resultado da consulta final contiver um elemento NULL.

Tipos de dados retornados

Um ARRAY do tipo especificado pelo parâmetro expression.

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

Exemplos

SELECT APPROX_QUANTILES(x, 2) AS approx_quantiles
FROM UNNEST([1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [1, 5, 10]       |
+------------------+
SELECT APPROX_QUANTILES(x, 100)[OFFSET(90)] AS percentile_90
FROM UNNEST([1, 2, 3, 4, 5, 6, 7, 8, 9, 10]) AS x;

+---------------+
| percentile_90 |
+---------------+
| 9             |
+---------------+
SELECT APPROX_QUANTILES(DISTINCT x, 2) AS approx_quantiles
FROM UNNEST([1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [1, 6, 10]       |
+------------------+
SELECT FORMAT("%T", APPROX_QUANTILES(x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [NULL, 4, 10]    |
+------------------+
SELECT FORMAT("%T", APPROX_QUANTILES(DISTINCT x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [NULL, 6, 10]    |
+------------------+

APPROX_TOP_COUNT

APPROX_TOP_COUNT(expression, number)

Descrição

Retorna os elementos principais aproximados de expression. O parâmetro number especifica o número de elementos retornados.

Tipos de argumentos compatíveis

expression pode ser de qualquer tipo de dado compatível com a cláusula GROUP BY.

number precisa ser INT64.

Tipos de dados retornados

Uma ARRAY do tipo STRUCT. O STRUCT contém dois campos. O primeiro campo (chamado value) contém um valor de entrada. O segundo campo (chamado count) contém um INT64 que especifica o número de vezes que o valor foi retornado.

Retorna NULL, se houver linhas de entrada igual a zero.

Exemplos

SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST(["apple", "apple", "pear", "pear", "pear", "banana"]) as x;

+-------------------------+
| approx_top_count        |
+-------------------------+
| [{pear, 3}, {apple, 2}] |
+-------------------------+

Tratamento de NULL

APPROX_TOP_COUNT não ignora NULLs na entrada. Exemplo:

SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST([NULL, "pear", "pear", "pear", "apple", NULL]) as x;

+------------------------+
| approx_top_count       |
+------------------------+
| [{pear, 3}, {NULL, 2}] |
+------------------------+

APPROX_TOP_SUM

APPROX_TOP_SUM(expression, weight, number)

Descrição

Retorna os elementos principais aproximados de expression, com base na soma de um weight atribuído. O parâmetro number especifica o número de elementos retornados.

Se a entrada weight for negativa ou NaN, esta função retornará um erro.

Tipos de argumentos compatíveis

expression pode ser de qualquer tipo de dado compatível com a cláusula GROUP BY.

weight precisa atender a uma das seguintes condições:

  • INT64
  • FLOAT64

number precisa ser INT64.

Tipos de dados retornados

Uma ARRAY do tipo STRUCT. O STRUCT contém dois campos: value e sum. O campo value contém o valor da expressão de entrada. O campo sum é do mesmo tipo que weight e é a soma aproximada do peso de entrada associado ao campo value.

Retorna NULL, se houver linhas de entrada igual a zero.

Exemplos

SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([
  STRUCT("apple" AS x, 3 AS weight),
  ("pear", 2),
  ("apple", 0),
  ("banana", 5),
  ("pear", 4)
]);

+--------------------------+
| approx_top_sum           |
+--------------------------+
| [{pear, 6}, {banana, 5}] |
+--------------------------+

Tratamento de NULL

APPROX_TOP_SUM não ignora valores NULL para os parâmetros expression e weight.

SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, NULL AS weight), ("pear", 0), ("pear", NULL)]);

+----------------------------+
| approx_top_sum             |
+----------------------------+
| [{pear, 0}, {apple, NULL}] |
+----------------------------+
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, 2)]);

+-------------------------+
| approx_top_sum          |
+-------------------------+
| [{NULL, 2}, {apple, 0}] |
+-------------------------+
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, NULL)]);

+----------------------------+
| approx_top_sum             |
+----------------------------+
| [{apple, 0}, {NULL, NULL}] |
+----------------------------+

Funções HyperLogLog ++

O algoritmo do HyperLogLog ++ (HLL ++) estima a cardinalidade (em inglês) dos sketches. Se você não quiser trabalhar com sketches e não necessitar de precisão personalizada, considere usar funções de agregação aproximadas com precisão definida pelo sistema.

As funções do HLL ++ são funções de agregação aproximadas. A agregação aproximada normalmente requer menos memória do que a função de agregação exata, como COUNT(DISTINCT), mas também apresenta incerteza estatística. Isso torna as funções HLL ++ apropriadas para grandes streamings de dados para os quais o uso de memória linear é impraticável, bem como para dados já aproximados.

O BigQuery é compatível com as seguintes funções do HLL++:

HLL_COUNT.INIT

HLL_COUNT.INIT(input [, precision])

Descrição

Uma função de agregação que usa um ou mais valores input e os agrega em um sketch de HLL++ (em inglês). Cada sketch é representado usando o tipo de dados BYTES. Em seguida, é possível mesclar os sketches usando HLL_COUNT.MERGE ou HLL_COUNT.MERGE_PARTIAL. Se nenhuma mesclagem for necessária, será possível extrair do rascunho a contagem final de valores distintos usando HLL_COUNT.EXTRACT.

Essa função é compatível com um parâmetro opcional, precision. Ele define a precisão da estimativa ao custo da memória adicional necessária para processar os sketches ou armazená-los no disco. A tabela a seguir mostra os valores de precisão permitidos, o tamanho máximo do sketch por grupo e o intervalo de confiança (CI, na sigla em inglês) de precisões típicas:

Precisão Tamanho máx. do sketch (KiB) CI de 65% CI de 95% CI de 99%
10 1 ±1,63% ±3,25% ±6,50%
11 2 ±1,15% ±2,30% ±4,60%
12 4 ±0,81% ±1,63% ±3,25%
13 8 ±0,57% ±1,15% ±1,72%
14 16 ±0,41% ±0,81% ±1,22%
15 (padrão) 32 ±0,29% ±0,57% ±0,86%
16 64 ±0,20% ±0,41% ±0,61%
17 128 ±0,14% ±0,29% ±0,43%
18 256 ±0,10% ±0,20% ±0,41%
19 512 ±0,07% ±0,14% ±0,29%
20 1024 ±0,05% ±0,10% ±0,20%
21 2048 ±0,04% ±0,07% ±0,14%
22 4096 ±0,03% ±0,05% ±0,10%
23 8192 ±0,02% ±0,04% ±0,07%
24 16384 ±0,01% ±0,03% ±0,05%

Se a entrada for NULL, essa função retornará NULL.

Para mais informações, consulte HyperLogLog in Practice: Algorithmic Engineering of a State of The Art Cardinality Estimation Algorithm (em inglês).

Tipos de entradas compatíveis

INT64, NUMERIC, STRING e BYTES

Tipo de retorno

BYTES

Exemplo

SELECT
  HLL_COUNT.INIT(respondent) AS respondents_hll,
  flavor,
  country
FROM UNNEST([
  STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
  (1, "Chocolate", "CH"),
  (2, "Chocolate", "US"),
  (2, "Strawberry", "US")])
GROUP BY flavor, country;

HLL_COUNT.MERGE

HLL_COUNT.MERGE(sketch)

Descrição

Uma função de agregação que retorna a cardinalidade de vários sketches de conjunto do HLL ++ calculando a união deles.

Cada sketch precisa ter a mesma precisão e ser inicializado no mesmo tipo. As tentativas de mesclar sketches com precisões ou tipos diferentes resultam em erro. Por exemplo, não é possível mesclar um sketch inicializado nos dados de INT64 com um inicializado nos dados de STRING.

Essa função ignora os valores NULL ao mesclar sketches. Se a mesclagem acontecer em linhas de zero ou somente em valores NULL, a função retornará 0.

Tipos de entradas compatíveis

BYTES

Tipo de retorno

INT64

Exemplo

SELECT HLL_COUNT.MERGE(respondents_hll) AS num_respondents, flavor
FROM (
  SELECT
    HLL_COUNT.INIT(respondent) AS respondents_hll,
    flavor,
    country
  FROM UNNEST([
    STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
    (1, "Chocolate", "CH"),
    (2, "Chocolate", "US"),
    (2, "Strawberry", "US")])
  GROUP BY flavor, country)
GROUP BY flavor;

HLL_COUNT.MERGE_PARTIAL

HLL_COUNT.MERGE_PARTIAL(sketch)

Descrição

Uma função de agregação que usa uma ou mais entradas sketch do HLL ++ (em inglês) e as mescla em um novo sketch.

Essa função retornará NULL, se não houver entrada ou se todas elas forem NULL.

Tipos de entradas compatíveis

BYTES

Tipo de retorno

BYTES

Exemplo

SELECT HLL_COUNT.MERGE_PARTIAL(respondents_hll) AS num_respondents, flavor
FROM (
  SELECT
    HLL_COUNT.INIT(respondent) AS respondents_hll,
    flavor,
    country
  FROM UNNEST([
    STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
    (1, "Chocolate", "CH"),
    (2, "Chocolate", "US"),
    (2, "Strawberry", "US")])
  GROUP BY flavor, country)
GROUP BY flavor;

HLL_COUNT.EXTRACT

HLL_COUNT.EXTRACT(sketch)

Descrição

Uma função escalar que extrai uma estimativa da cardinalidade de um único sketch do HLL++ (em inglês).

Se sketch for NULL, essa função retornará uma estimativa de cardinalidade de 0.

Tipos de entradas compatíveis

BYTES

Tipo de retorno

INT64

Exemplo

SELECT
  flavor,
  country,
  HLL_COUNT.EXTRACT(respondents_hll) AS num_respondents
FROM (
  SELECT
    HLL_COUNT.INIT(respondent) AS respondents_hll,
    flavor,
    country
  FROM UNNEST([
    STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
    (1, "Chocolate", "CH"),
    (2, "Chocolate", "US"),
    (2, "Strawberry", "US")])
  GROUP BY flavor, country);

+------------+---------+-----------------+
| flavor     | country | num_respondents |
+------------+---------+-----------------+
| Vanilla    | CH      | 1               |
| Chocolate  | CH      | 1               |
| Chocolate  | US      | 1               |
| Strawberry | US      | 1               |
+------------+---------+-----------------+

Sobre o algoritmo HLL++

O algoritmo HLL++ (em inglês) é uma versão melhorada do algoritmo HLL (em inglês) que estima com mais precisão cardinalidades muito pequenas ou grandes. O algoritmo HLL++ inclui uma representação esparsa e de função de hash de 64-bits que reduz os requisitos de memória de estimativas cardinais pequenas e de correções de tendência empírica de estimativas cardinais pequenas.

Sobre sketches

Um sketch é um resumo de um streaming grande de dados. É possível extrair estatísticas de um sketch para estimar estatísticas particulares de dados originais ou mesclar sketches para resumir vários streamings de dados. Um sketch tem os seguintes recursos:

  • Ela compacta dados brutos em uma representação de memória fixa.
  • É assintoticamente menor que a entrada.
  • É a forma serializada de uma estrutura de dados sub-linear na memória.
  • Normalmente, requer menos memória do que a entrada usada para criá-lo.

Os sketches permitem a integração com outros sistemas. Por exemplo, é possível criar sketches em aplicativos externos, como o Cloud Dataflow ou o Apache Spark (em inglês) e consumi-los no BigQuery, ou vice-versa. Os sketches também permitem a criação de agregações intermediárias para funções não aditivas, como COUNT(DISTINCT).

Funções de numeração

As seções a seguir descrevem as funções de numeração compatíveis com o BigQuery. As funções de numeração são um subconjunto de funções analíticas. Para uma explicação de como são funções analíticas, consulte Conceitos da função analítica. Para uma descrição de como são funções de numeração, consulte os Conceitos da função de numeração.

Requisitos da cláusula OVER:

  • PARTITION BY: opcional.
  • ORDER BY: obrigatório, exceto para ROW_NUMBER().
  • window_frame_clause: não permitido

RANK

Descrição

Retorna a classificação de ordinal (baseada em 1) de cada linha dentro da partição ordenada. Todas as linhas dos pares recebem o mesmo valor da classificação. A linha ou o grupo de linhas de pares seguintes recebem um valor da classificação que aumenta baseado no número de pares com o valor da classificação anterior, em vez de DENSE_RANK, que sempre aumenta em 1.

Tipos de argumentos compatíveis

INT64

DENSE_RANK

Descrição

Retorna a classificação de ordinal (baseada em 1) de cada linha dentro da partição da janela. Todas as linhas de pares recebem o mesmo valor da classificação. O valor seguinte é incrementado em um.

Tipos de argumentos compatíveis

INT64

PERCENT_RANK

Descrição

Retorna a classificação do percentil de uma linha definida como (RK-1)/(NR-1), na qual RK é o RANK da linha e NR é o número de linhas na partição. Retorna 0 se NR = 1.

Tipos de argumentos compatíveis

FLOAT64

CUME_DIST

Descrição

Retorna a classificação relativa de uma linha definida como NP/NR. NP é definido como o número de linhas que precedem ou são pares da linha atual. NR é o número de linhas da partição.

Tipos de argumentos compatíveis

FLOAT64

NTILE

NTILE(constant_integer_expression)

Descrição

Essa função divide as linhas em intervalos constant_integer_expression com base na ordenação de linhas e retorna o número do intervalo com base em 1 que é atribuído a cada linha. O número de linhas nos intervalos pode diferir em 1, no máximo. Os valores restantes, isto é, o número de linhas divididas por intervalos, são distribuídos um para cada intervalo, começando com o intervalo 1. Se constant_integer_expression for avaliado como NULL, 0 ou negativo, um erro será fornecido.

Tipos de argumentos compatíveis

INT64

ROW_NUMBER

Descrição

Não requer a cláusula ORDER BY. Retorna o ordinal da linha sequencial (baseado em 1) de cada linha de cada partição ordenada. Se a cláusula ORDER BY não for especificada, o resultado será não determinístico.

Tipos de argumentos compatíveis

INT64

Funções de bit

O BigQuery é compatível com as funções Bit a seguir.

BIT_COUNT

BIT_COUNT(expression)

Descrição

A entrada, expression, precisa ser um número inteiro ou BYTES.

Retorna o número de bits que estão definidos na entrada expression. No caso de números inteiros com sinal, isso significa o número de bits na forma de complemento de dois.

Tipo de dados retornados

INT64

Exemplo

SELECT a, BIT_COUNT(a) AS a_bits, FORMAT("%T", b) as b, BIT_COUNT(b) AS b_bits
FROM UNNEST([
  STRUCT(0 AS a, b'' AS b), (0, b'\x00'), (5, b'\x05'), (8, b'\x00\x08'),
  (0xFFFF, b'\xFF\xFF'), (-2, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFE'),
  (-1, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF'),
  (NULL, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF')
]) AS x;

+-------+--------+---------------------------------------------+--------+
| a     | a_bits | b                                           | b_bits |
+-------+--------+---------------------------------------------+--------+
| 0     | 0      | b""                                         | 0      |
| 0     | 0      | b"\x00"                                     | 0      |
| 5     | 2      | b"\x05"                                     | 2      |
| 8     | 1      | b"\x00\x08"                                 | 1      |
| 65535 | 16     | b"\xff\xff"                                 | 16     |
| -2    | 63     | b"\xff\xff\xff\xff\xff\xff\xff\xfe"         | 63     |
| -1    | 64     | b"\xff\xff\xff\xff\xff\xff\xff\xff"         | 64     |
| NULL  | NULL   | b"\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff" | 80     |
+-------+--------+---------------------------------------------+--------+

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. Retorna +inf para um argumento +/-inf.

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. Retorna NaN para um argumento NaN.

IS_INF

IS_INF(X)

Descrição

Retornará TRUE se o valor for um infinito positivo ou negativo. Retorna NULL para entradas NULL.

IS_NAN

IS_NAN(X)

Descrição

Retorna TRUE se o valor for NaN. Retorna NULL para entradas NULL.

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.

Casos especiais:

  • Se o resultado estourar, retorna +/-inf.
  • Se Y=0 e X=0, retorna NaN.
  • Se Y=0 e X!=0, retorna +/-inf.
  • Se X=+/-inf e Y=+/-inf, retorna NaN.

O comportamento de IEEE_DIVIDE é ilustrado com mais detalhes na tabela abaixo.

Casos especiais de IEEE_DIVIDE

A tabela a seguir lista os casos especiais de IEEE_DIVIDE.

Tipos de dados do numerador (X) Tipos de dados do denominador (Y) Valor do resultado
Qualquer um, exceto 0 0 +/-inf
0 0 NaN
0 NaN NaN
NaN 0 NaN
+/-inf +/-inf NaN

RAND

RAND()

Descrição

Gera um valor pseudoaleatório do tipo FLOAT64 no intervalo de [0, 1), incluindo 0 e excluindo 1.

SQRT

SQRT(X)

Descrição

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

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. Retornará um erro se uma das seguintes condições for verdadeira:

  • X é um valor finito menor que 0, e Y é um número não inteiro
  • X é 0, e Y é um valor finito menor que 0

O comportamento de POW() é ilustrado com mais detalhes na tabela abaixo.

POWER

POWER(X, Y)

Descrição

Sinônimo de POW().

Casos especiais para POW(X, Y) e POWER(X, Y)

Veja a seguir casos especiais para POW(X, Y) e POWER(X, Y).

X Y POW(X, Y) ou POWER(X, Y)
1,0 Qualquer valor, incluindo NaN 1,0
qualquer um, incluindo NaN 0 1,0
-1,0 +/-inf 1,0
ABS(X) < 1 -inf +inf
ABS(X) > 1 -inf 0
ABS(X) < 1 +inf 0
ABS(X) > 1 +inf +inf
-inf Y < 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

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. Se X for +/-inf, essa função retorna +inf ou 0.

LN

LN(X)

Descrição

Calcula o logaritmo natural de X. Um erro será gerado se X for menor ou igual a zero. Se X for +inf, essa função retorna +inf.

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. A função gera um erro nos seguintes casos:

  • X é menor ou igual a zero
  • Y é 1,0
  • Y é menor ou igual a zero.

O comportamento de LOG(X, Y) é ilustrado com mais detalhes na tabela abaixo.

Casos especiais de LOG(X, Y)

X Y LOG(X, Y)
-inf Qualquer valor NaN
Qualquer valor +inf NaN
+inf 0,0 Y < 1,0 -inf
+inf Y > 1,0 +inf

LOG10

LOG10(X)

Descrição

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

GREATEST

GREATEST(X1,...,XN)

Descrição

Retorna NULL se alguma das entradas for NULL. Caso contrário, retorna NaN se alguma das entradas for NaN. Caso contrário, retorna o maior valor entre X1,…, XN de acordo com a comparação.

LEAST

LEAST(X1,...,XN)

Descrição

Retorna NULL se alguma das entradas for NULL. Retorna NaN se alguma das entradas for NaN. Caso contrário, retorna o menor valor entre X1,…, XN de acordo com a comparação.

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. Consulte a tabela abaixo para ver possíveis tipos de resultados.

SAFE_DIVIDE

SAFE_DIVIDE(X, Y)

Descrição

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

SAFE_MULTIPLY

SAFE_MULTIPLY(X, Y)

Descrição

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

SAFE_NEGATE

SAFE_NEGATE(X)

Descrição

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

SAFE_ADD

SAFE_ADD(X, Y)

Descrição

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

SAFE_SUBTRACT

SAFE_SUBTRACT(X, Y)

Descrição

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

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. Consulte a tabela abaixo para ver possíveis tipos de resultados.

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.

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.

CEIL

CEIL(X)

Descrição

Retorna o menor valor integral (com o tipo FLOAT64) que não é inferior a X.

CEILING

CEILING(X)

Descrição

Sinônimo de CEIL(X)

FLOOR

FLOOR(X)

Descrição

Retorna o maior valor integral (com o tipo FLOAT64) que não é superior a X.

Exemplo de comportamento da função de arredondamento

Exemplo de comportamento de funções de arredondamento do BigQuery:

Entrada "X" ROUND(X) TRUNC(X) CEIL(X) FLOOR(X)
2,0 2,0 2,0 2,0 2,0
2,3 2,0 2,0 3,0 2,0
2,8 3,0 2,0 3,0 2,0
2,5 3,0 2,0 3,0 2,0
-2,3 -2,0 -2,0 -2,0 -3,0
-2,8 -3,0 -2,0 -2,0 -3,0
-2,5 -3,0 -2,0 -2,0 -3,0
0 0 0 0 0
+/-inf +/-inf +/-inf +/-inf +/-inf
NaN NaN NaN NaN NaN

COS

COS(X)

Descrição

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

COSH

COSH(X)

Descrição

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

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].

ACOSH

ACOSH(X)

Descrição

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

SIN

SIN(X)

Descrição

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

SINH

SINH(X)

Descrição

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

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].

ASINH

ASINH(X)

Descrição

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

TAN

TAN(X)

Descrição

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

TANH

TANH(X)

Descrição

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

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.

ATANH

ATANH(X)

Descrição

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

ATAN2

ATAN2(Y, X)

Descrição

Calcula o valor principal da tangente inversa de Y/X usando os sinais dos dois argumentos para determinar o quadrante. O valor de retorno está no intervalo [-π, π]. O comportamento dessa função é ilustrado com mais detalhes na tabela abaixo.

Casos especiais de ATAN2()

Y X ATAN2(Y, X)
NaN Qualquer valor NaN
Qualquer valor NaN NaN
0 0 0, π ou -π, dependendo do sinal de X e Y
Valor finito -inf π ou -π, dependendo do sinal de Y
Valor finito +inf 0
+/-inf Valor finito π/2 ou π/2, dependendo do sinal de Y
+/-inf -inf ¾π ou -¾π, dependendo do sinal de Y
+/-inf +inf π/4 ou -π/4, dependendo do sinal de Y

Casos especiais de funções trigonométricas e hiperbólicas de arredondamento

X COS(X) COSH(X) ACOS(X) ACOSH(X) SIN(X) SINH(X) ASIN(X) ASINH(X) TAN(X) TANH(X) ATAN(X) ATANH(X)
+/-inf NaN =+inf NaN =+inf NaN =+inf NaN =+inf NaN =+1,0 π/2 NaN
-inf NaN =+inf NaN NaN NaN -inf NaN -inf NaN -1,0 -π/2 NaN
NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN

RANGE_BUCKET

RANGE_BUCKET(point, boundaries_array)

Descrição

RANGE_BUCKET verifica uma matriz classificada e retorna a posição 0 do limite superior do ponto. Isso pode ser útil se você precisar agrupar seus dados para criar partições, histogramas, regras definidas pelo negócio e muito mais.

RANGE_BUCKET segue as seguintes regras:

  • Se o ponto existir na matriz, o índice do próximo valor maior será retornado.

    RANGE_BUCKET(20, [0, 10, 20, 30, 40]) -- 3 is return value
    RANGE_BUCKET(20, [0, 10, 20, 20, 40, 40]) -- 4 is return value
    
  • Se o ponto não existir na matriz, mas ficar entre dois valores, o índice do valor maior será retornado.

    RANGE_BUCKET(25, [0, 10, 20, 30, 40]) -- 3 is return value
    
  • Se o ponto for menor que o primeiro valor na matriz, será retornado 0.

    RANGE_BUCKET(-10, [5, 10, 20, 30, 40]) -- 0 is return value
    
  • Se o ponto for maior ou igual ao último valor na matriz, o comprimento da matriz será retornado.

    RANGE_BUCKET(80, [0, 10, 20, 30, 40]) -- 5 is return value
    
  • Se a matriz estiver vazia, será retornado 0.

    RANGE_BUCKET(80, []) -- 0 is return value
    
  • Se o ponto for NULL ou NaN, será retornado NULL.

    RANGE_BUCKET(NULL, [0, 10, 20, 30, 40]) -- NULL is return value
    
  • O tipo de dados do ponto e da matriz deve ser compatível.

    RANGE_BUCKET('a', ['a', 'b', 'c', 'd']) -- 1 is return value
    RANGE_BUCKET(1.2, [1, 1.2, 1.4, 1.6]) -- 2 is return value
    RANGE_BUCKET(1.2, [1, 2, 4, 6]) -- execution failure
    

A falha na execução ocorre quando:

  • A matriz tem um valor NaN ou NULL.

    RANGE_BUCKET(80, [NULL, 10, 20, 30, 40]) -- execution failure
    
  • A matriz não é classificada em ordem crescente.

    RANGE_BUCKET(30, [10, 30, 20, 40, 50]) -- execution failure
    

Parâmetros

  • point: um valor genérico.
  • boundaries_array: uma matriz genérica de valores.

Valor de retorno

INT64

Exemplos

Em uma tabela chamada students, verifique quantos registros existiriam em cada intervalo de age_group, com base na idade de um aluno:

  • age_group 0 (idade <10 anos)
  • age_group 1 (idade >= 10, idade <20)
  • age_group 2 (idade >= 20, idade <30)
  • age_group 3 (idade >= 30)
WITH students AS
(
  SELECT 9 AS age UNION ALL
  SELECT 20 AS age UNION ALL
  SELECT 25 AS age UNION ALL
  SELECT 31 AS age UNION ALL
  SELECT 32 AS age UNION ALL
  SELECT 33 AS age
)
SELECT RANGE_BUCKET(age, [10, 20, 30]) AS age_group, COUNT(*) AS count
FROM students
GROUP BY 1

+--------------+-------+
| age_group    | count |
+--------------+-------+
| 0            | 1     |
| 2            | 2     |
| 3            | 3     |
+--------------+-------+

As seções a seguir descrevem as funções de navegação compatíveis com o BigQuery. As funções de navegação são um subconjunto de funções analíticas. Para uma explicação de como são funções analíticas, consulte Conceitos da função analítica. Para uma explicação sobre as funções de navegação, consulte Conceitos da função de navegação.

FIRST_VALUE

FIRST_VALUE (value_expression [{RESPECT | IGNORE} NULLS])

Descrição

Retorna o valor de value_expression para a primeira linha no frame da janela atual.

Essa função inclui valores NULL no cálculo, a menos que IGNORE NULLS esteja presente. Se IGNORE NULLS estiver presente, a função excluirá valores NULL do cálculo.

Tipos de argumentos compatíveis

value_expression pode ser qualquer tipo de dados que uma expressão pode retornar.

Tipo de dados retornados

ANY

Exemplos

O exemplo a seguir calcula o tempo mais rápido de cada divisão.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  FORMAT_TIMESTAMP('%X', finish_time) AS finish_time,
  division,
  FORMAT_TIMESTAMP('%X', fastest_time) AS fastest_time,
  TIMESTAMP_DIFF(finish_time, fastest_time, SECOND) AS delta_in_seconds
FROM (
  SELECT name,
  finish_time,
  division,
  FIRST_VALUE(finish_time)
    OVER (PARTITION BY division ORDER BY finish_time ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS fastest_time
  FROM finishers);

+-----------------+-------------+----------+--------------+------------------+
| name            | finish_time | division | fastest_time | delta_in_seconds |
+-----------------+-------------+----------+--------------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | 03:08:58     | 0                |
| Sophia Liu      | 02:51:45    | F30-34   | 02:51:45     | 0                |
| Nikki Leith     | 02:59:01    | F30-34   | 02:51:45     | 436              |
| Jen Edwards     | 03:06:36    | F30-34   | 02:51:45     | 891              |
| Meghan Lederer  | 03:07:41    | F30-34   | 02:51:45     | 956              |
| Lauren Reasoner | 03:10:14    | F30-34   | 02:51:45     | 1109             |
| Lisa Stelzner   | 02:54:11    | F35-39   | 02:54:11     | 0                |
| Lauren Matthews | 03:01:17    | F35-39   | 02:54:11     | 426              |
| Desiree Berry   | 03:05:42    | F35-39   | 02:54:11     | 691              |
| Suzy Slane      | 03:06:24    | F35-39   | 02:54:11     | 733              |
+-----------------+-------------+----------+--------------+------------------+

LAST_VALUE

LAST_VALUE (value_expression [{RESPECT | IGNORE} NULLS])

Descrição

Retorna o valor de value_expression para a última linha no frame da janela atual.

Essa função inclui valores NULL no cálculo, a menos que IGNORE NULLS esteja presente. Se IGNORE NULLS estiver presente, a função excluirá valores NULL do cálculo.

Tipos de argumentos compatíveis

value_expression pode ser qualquer tipo de dados que uma expressão pode retornar.

Tipo de dados retornados

ANY

Exemplos

O exemplo a seguir calcula o tempo mais lento de cada divisão.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  FORMAT_TIMESTAMP('%X', finish_time) AS finish_time,
  division,
  FORMAT_TIMESTAMP('%X', slowest_time) AS slowest_time,
  TIMESTAMP_DIFF(slowest_time, finish_time, SECOND) AS delta_in_seconds
FROM (
  SELECT name,
  finish_time,
  division,
  LAST_VALUE(finish_time)
    OVER (PARTITION BY division ORDER BY finish_time ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS slowest_time
  FROM finishers);

+-----------------+-------------+----------+--------------+------------------+
| name            | finish_time | division | slowest_time | delta_in_seconds |
+-----------------+-------------+----------+--------------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | 03:08:58     | 0                |
| Sophia Liu      | 02:51:45    | F30-34   | 03:10:14     | 1109             |
| Nikki Leith     | 02:59:01    | F30-34   | 03:10:14     | 673              |
| Jen Edwards     | 03:06:36    | F30-34   | 03:10:14     | 218              |
| Meghan Lederer  | 03:07:41    | F30-34   | 03:10:14     | 153              |
| Lauren Reasoner | 03:10:14    | F30-34   | 03:10:14     | 0                |
| Lisa Stelzner   | 02:54:11    | F35-39   | 03:06:24     | 733              |
| Lauren Matthews | 03:01:17    | F35-39   | 03:06:24     | 307              |
| Desiree Berry   | 03:05:42    | F35-39   | 03:06:24     | 42               |
| Suzy Slane      | 03:06:24    | F35-39   | 03:06:24     | 0                |
+-----------------+-------------+----------+--------------+------------------+

NTH_VALUE

NTH_VALUE (value_expression, constant_integer_expression [{RESPECT | IGNORE} NULLS])

Descrição

Retorna o valor de value_expression na linha N do frame da janela atual, em que N é definido por constant_integer_expression. Retorna NULL, se não houver linha.

Essa função inclui valores NULL no cálculo, a menos que IGNORE NULLS esteja presente. Se IGNORE NULLS estiver presente, a função excluirá valores NULL do cálculo.

Tipos de argumentos compatíveis

  • value_expression pode ser qualquer tipo de dados que possa ser retornado de uma expressão.
  • constant_integer_expression pode ser qualquer expressão constante que retorna um número inteiro.

Tipo de dados retornados

ANY

Exemplos

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  FORMAT_TIMESTAMP('%X', finish_time) AS finish_time,
  division,
  FORMAT_TIMESTAMP('%X', fastest_time) AS fastest_time,
  FORMAT_TIMESTAMP('%X', second_fastest) AS second_fastest
FROM (
  SELECT name,
  finish_time,
  division,finishers,
  FIRST_VALUE(finish_time)
    OVER w1 AS fastest_time,
  NTH_VALUE(finish_time, 2)
    OVER w1 as second_fastest
  FROM finishers
  WINDOW w1 AS (
    PARTITION BY division ORDER BY finish_time ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING));

+-----------------+-------------+----------+--------------+----------------+
| name            | finish_time | division | fastest_time | second_fastest |
+-----------------+-------------+----------+--------------+----------------+
| Carly Forte     | 03:08:58    | F25-29   | 03:08:58     | NULL           |
| Sophia Liu      | 02:51:45    | F30-34   | 02:51:45     | 02:59:01       |
| Nikki Leith     | 02:59:01    | F30-34   | 02:51:45     | 02:59:01       |
| Jen Edwards     | 03:06:36    | F30-34   | 02:51:45     | 02:59:01       |
| Meghan Lederer  | 03:07:41    | F30-34   | 02:51:45     | 02:59:01       |
| Lauren Reasoner | 03:10:14    | F30-34   | 02:51:45     | 02:59:01       |
| Lisa Stelzner   | 02:54:11    | F35-39   | 02:54:11     | 03:01:17       |
| Lauren Matthews | 03:01:17    | F35-39   | 02:54:11     | 03:01:17       |
| Desiree Berry   | 03:05:42    | F35-39   | 02:54:11     | 03:01:17       |
| Suzy Slane      | 03:06:24    | F35-39   | 02:54:11     | 03:01:17       |
+-----------------+-------------+----------+--------------+----------------+

LEAD

LEAD (value_expression[, offset [, default_expression]])

Descrição

Retorna o valor de value_expression em uma linha subsequente. Alterar o valor offset altera qual linha subsequente é retornada; o valor padrão é 1, indicando a próxima linha no frame da janela. Um erro ocorre se offset for NULL ou um valor negativo.

O default_expression opcional é usado se não houver uma linha no frame da janela no deslocamento especificado. Essa expressão precisa ser uma expressão constante e seu tipo precisa ser implicitamente coercível para o tipo de value_expression. Se não for especificado, a default_expression padrão será NULL.

Tipos de argumentos compatíveis

  • value_expression pode ser qualquer tipo de dados que possa ser retornado de uma expressão.
  • offset precisa ser um parâmetro ou literal inteiro não negativo.
  • default_expression precisa ser compatível com o tipo de expressão de valor.

Tipo de dados retornados

ANY

Exemplos

O exemplo a seguir ilustra uma utilização básica da função LEAD.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LEAD(name)
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS followed_by
FROM finishers;

+-----------------+-------------+----------+-----------------+
| name            | finish_time | division | followed_by     |
+-----------------+-------------+----------+-----------------+
| Carly Forte     | 03:08:58    | F25-29   | NULL            |
| Sophia Liu      | 02:51:45    | F30-34   | Nikki Leith     |
| Nikki Leith     | 02:59:01    | F30-34   | Jen Edwards     |
| Jen Edwards     | 03:06:36    | F30-34   | Meghan Lederer  |
| Meghan Lederer  | 03:07:41    | F30-34   | Lauren Reasoner |
| Lauren Reasoner | 03:10:14    | F30-34   | NULL            |
| Lisa Stelzner   | 02:54:11    | F35-39   | Lauren Matthews |
| Lauren Matthews | 03:01:17    | F35-39   | Desiree Berry   |
| Desiree Berry   | 03:05:42    | F35-39   | Suzy Slane      |
| Suzy Slane      | 03:06:24    | F35-39   | NULL            |
+-----------------+-------------+----------+-----------------+

Este próximo exemplo usa o parâmetro offset opcional.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LEAD(name, 2)
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_back
FROM finishers;

+-----------------+-------------+----------+------------------+
| name            | finish_time | division | two_runners_back |
+-----------------+-------------+----------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | NULL             |
| Sophia Liu      | 02:51:45    | F30-34   | Jen Edwards      |
| Nikki Leith     | 02:59:01    | F30-34   | Meghan Lederer   |
| Jen Edwards     | 03:06:36    | F30-34   | Lauren Reasoner  |
| Meghan Lederer  | 03:07:41    | F30-34   | NULL             |
| Lauren Reasoner | 03:10:14    | F30-34   | NULL             |
| Lisa Stelzner   | 02:54:11    | F35-39   | Desiree Berry    |
| Lauren Matthews | 03:01:17    | F35-39   | Suzy Slane       |
| Desiree Berry   | 03:05:42    | F35-39   | NULL             |
| Suzy Slane      | 03:06:24    | F35-39   | NULL             |
+-----------------+-------------+----------+------------------+

O exemplo a seguir substitui os valores NULL por um valor padrão.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LEAD(name, 2, 'Nobody')
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_back
FROM finishers;

+-----------------+-------------+----------+------------------+
| name            | finish_time | division | two_runners_back |
+-----------------+-------------+----------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | Nobody           |
| Sophia Liu      | 02:51:45    | F30-34   | Jen Edwards      |
| Nikki Leith     | 02:59:01    | F30-34   | Meghan Lederer   |
| Jen Edwards     | 03:06:36    | F30-34   | Lauren Reasoner  |
| Meghan Lederer  | 03:07:41    | F30-34   | Nobody           |
| Lauren Reasoner | 03:10:14    | F30-34   | Nobody           |
| Lisa Stelzner   | 02:54:11    | F35-39   | Desiree Berry    |
| Lauren Matthews | 03:01:17    | F35-39   | Suzy Slane       |
| Desiree Berry   | 03:05:42    | F35-39   | Nobody           |
| Suzy Slane      | 03:06:24    | F35-39   | Nobody           |
+-----------------+-------------+----------+------------------+

LAG

LAG (value_expression[, offset [, default_expression]])

Descrição

Retorna o valor de value_expression em uma linha anterior. Alterar o valor de offset altera a linha anterior que é retornada; o valor padrão é 1, indicando a linha anterior no frame da janela. Um erro ocorre se offset for NULL ou um valor negativo.

O default_expression opcional é usado se não houver uma linha no frame da janela no deslocamento especificado. Essa expressão precisa ser uma expressão constante e seu tipo precisa ser implicitamente coercível para o tipo de value_expression. Se não for especificado, a default_expression padrão será NULL.

Tipos de argumentos compatíveis

  • value_expression pode ser qualquer tipo de dados que possa ser retornado de uma expressão.
  • offset precisa ser um parâmetro ou literal inteiro não negativo.
  • default_expression precisa ser compatível com o tipo de expressão de valor.

Tipo de dados retornados

ANY

Exemplos

O exemplo a seguir ilustra uma utilização básica da função LAG.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LAG(name)
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS preceding_runner
FROM finishers;

+-----------------+-------------+----------+------------------+
| name            | finish_time | division | preceding_runner |
+-----------------+-------------+----------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | NULL             |
| Sophia Liu      | 02:51:45    | F30-34   | NULL             |
| Nikki Leith     | 02:59:01    | F30-34   | Sophia Liu       |
| Jen Edwards     | 03:06:36    | F30-34   | Nikki Leith      |
| Meghan Lederer  | 03:07:41    | F30-34   | Jen Edwards      |
| Lauren Reasoner | 03:10:14    | F30-34   | Meghan Lederer   |
| Lisa Stelzner   | 02:54:11    | F35-39   | NULL             |
| Lauren Matthews | 03:01:17    | F35-39   | Lisa Stelzner    |
| Desiree Berry   | 03:05:42    | F35-39   | Lauren Matthews  |
| Suzy Slane      | 03:06:24    | F35-39   | Desiree Berry    |
+-----------------+-------------+----------+------------------+

Este próximo exemplo usa o parâmetro offset opcional.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LAG(name, 2)
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_ahead
FROM finishers;

+-----------------+-------------+----------+-------------------+
| name            | finish_time | division | two_runners_ahead |
+-----------------+-------------+----------+-------------------+
| Carly Forte     | 03:08:58    | F25-29   | NULL              |
| Sophia Liu      | 02:51:45    | F30-34   | NULL              |
| Nikki Leith     | 02:59:01    | F30-34   | NULL              |
| Jen Edwards     | 03:06:36    | F30-34   | Sophia Liu        |
| Meghan Lederer  | 03:07:41    | F30-34   | Nikki Leith       |
| Lauren Reasoner | 03:10:14    | F30-34   | Jen Edwards       |
| Lisa Stelzner   | 02:54:11    | F35-39   | NULL              |
| Lauren Matthews | 03:01:17    | F35-39   | NULL              |
| Desiree Berry   | 03:05:42    | F35-39   | Lisa Stelzner     |
| Suzy Slane      | 03:06:24    | F35-39   | Lauren Matthews   |
+-----------------+-------------+----------+-------------------+

O exemplo a seguir substitui os valores NULL por um valor padrão.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LAG(name, 2, 'Nobody')
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_ahead
FROM finishers;

+-----------------+-------------+----------+-------------------+
| name            | finish_time | division | two_runners_ahead |
+-----------------+-------------+----------+-------------------+
| Carly Forte     | 03:08:58    | F25-29   | Nobody            |
| Sophia Liu      | 02:51:45    | F30-34   | Nobody            |
| Nikki Leith     | 02:59:01    | F30-34   | Nobody            |
| Jen Edwards     | 03:06:36    | F30-34   | Sophia Liu        |
| Meghan Lederer  | 03:07:41    | F30-34   | Nikki Leith       |
| Lauren Reasoner | 03:10:14    | F30-34   | Jen Edwards       |
| Lisa Stelzner   | 02:54:11    | F35-39   | Nobody            |
| Lauren Matthews | 03:01:17    | F35-39   | Nobody            |
| Desiree Berry   | 03:05:42    | F35-39   | Lisa Stelzner     |
| Suzy Slane      | 03:06:24    | F35-39   | Lauren Matthews   |
+-----------------+-------------+----------+-------------------+

PERCENTILE_CONT

PERCENTILE_CONT (value_expression, percentile [{RESPECT | IGNORE} NULLS])

Descrição

Calcula o valor do percentil especificado para a expressão de valor, com interpolação linear.

Esta função ignora valores NULL se RESPECT NULLS estiver ausente. Se RESPECT NULLS estiver presente:

  • A interpolação entre dois valores NULL retorna NULL.
  • A interpolação entre um valor NULL e um valor não NULL retorna o valor não NULL.

Tipos de argumentos compatíveis

  • value_expression é uma expressão numérica.
  • percentile é um FLOAT64 literal no intervalo [0, 1].

Tipo de dados retornados

FLOAT64

Exemplos

O exemplo a seguir calcula o valor de alguns percentis em uma coluna de valores, ignorando os nulos.

SELECT
  PERCENTILE_CONT(x, 0) OVER() AS min,
  PERCENTILE_CONT(x, 0.01) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5) OVER() AS median,
  PERCENTILE_CONT(x, 0.9) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1) OVER() AS max
FROM UNNEST([0, 3, NULL, 1, 2]) AS x LIMIT 1;

+-----+-------------+--------+--------------+-----+
| min | percentile1 | median | percentile90 | max |
+-----+-------------+--------+--------------+-----+
| 0   | 0.03        | 1.5    | 2.7          | 3   |
+-----+-------------+--------+--------------+-----+

O exemplo a seguir calcula o valor de alguns percentis em uma coluna de valores, respeitando os nulos.

SELECT
  PERCENTILE_CONT(x, 0 RESPECT NULLS) OVER() AS min,
  PERCENTILE_CONT(x, 0.01 RESPECT NULLS) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5 RESPECT NULLS) OVER() AS median,
  PERCENTILE_CONT(x, 0.9 RESPECT NULLS) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1 RESPECT NULLS) OVER() AS max
FROM UNNEST([0, 3, NULL, 1, 2]) AS x LIMIT 1;

+------+-------------+--------+--------------+-----+
| min  | percentile1 | median | percentile90 | max |
+------+-------------+--------+--------------+-----+
| NULL | 0           | 1      | 2.6          | 3   |
+------+-------------+--------+--------------+-----+

PERCENTILE_DISC

PERCENTILE_DISC (value_expression, percentile [{RESPECT | IGNORE} NULLS])

Descrição

Calcula o valor de percentil especificado para um value_expression discreto. O valor retornado é o primeiro valor classificado de value_expression com a distribuição cumulativa maior ou igual ao valor percentile especificado.

Esta função ignora valores NULL a menos que RESPECT NULLS esteja presente.

Tipos de argumentos compatíveis

  • value_expression pode ser qualquer tipo ordenado.
  • percentile é um FLOAT64 literal no intervalo [0, 1].

Tipo de dados retornados

ANY

Exemplos

O exemplo a seguir calcula o valor de alguns percentis em uma coluna de valores, ignorando os nulos.

SELECT
  x,
  PERCENTILE_DISC(x, 0) OVER() AS min,
  PERCENTILE_DISC(x, 0.5) OVER() AS median,
  PERCENTILE_DISC(x, 1) OVER() AS max
FROM UNNEST(['c', NULL, 'b', 'a']) AS x;

+------+-----+--------+-----+
| x    | min | median | max |
+------+-----+--------+-----+
| c    | a   | b      | c   |
| NULL | a   | b      | c   |
| b    | a   | b      | c   |
| a    | a   | b      | c   |
+------+-----+--------+-----+

O exemplo a seguir calcula o valor de alguns percentis em uma coluna de valores, respeitando os nulos.

SELECT
  x,
  PERCENTILE_DISC(x, 0 RESPECT NULLS) OVER() AS min,
  PERCENTILE_DISC(x, 0.5 RESPECT NULLS) OVER() AS median,
  PERCENTILE_DISC(x, 1 RESPECT NULLS) OVER() AS max
FROM UNNEST(['c', NULL, 'b', 'a']) AS x;

+------+------+--------+-----+
| x    | min  | median | max |
+------+------+--------+-----+
| c    | NULL | a      | c   |
| NULL | NULL | a      | c   |
| b    | NULL | a      | c   |
| a    | NULL | a      | c   |
+------+------+--------+-----+

Funções de agregação analítica

As seções a seguir descrevem as funções analíticas de agregação compatíveis com o BigQuery. Para uma explicação de como são funções analíticas, consulte Conceitos da função analítica. Para uma explicação de como são funções analíticas de agregação, consulte Conceitos da função analítica de agregação.

O BigQuery é compatível com estas funções de agregação como funções analíticas:

Requisitos da cláusula OVER:

  • PARTITION BY: opcional.
  • ORDER BY: opcional. Não permitido se DISTINCT estiver presente.
  • window_frame_clause: opcional. Não permitido se DISTINCT estiver presente.

Exemplo:

COUNT(*) OVER (ROWS UNBOUNDED PRECEDING)
SUM(DISTINCT x) OVER ()

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 |
+---+-------+-------+----------------------+

MD5

MD5(input)

Descrição

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

Essa função retorna 16 bytes.

Tipo de retorno

BYTES

Exemplo

SELECT MD5("Hello World") as md5;

-- Note that the result of MD5 is of type BYTES, displayed as a base64-encoded string.
+--------------------------+
| md5                      |
+--------------------------+
| sQqNsWTgdUEFt6mb5y4/5Q== |
+--------------------------+

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;

+-------+
| bytes |
+-------+
| AbCd  |
+-------+

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

SELECT CODE_POINTS_TO_BYTES(ARRAY_AGG(
  (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([code]) chr)
  ) ORDER BY OFFSET)) AS encoded_string
FROM UNNEST(TO_CODE_POINTS(b'Test String!')) code WITH OFFSET;

+----------------+
| encoded_string |
+----------------+
| Grfg Fgevat!   |
+----------------+

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.

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

Tipo de retorno

STRING

Exemplo

Veja a seguir um exemplo básico usando CODE_POINTS_TO_STRING.

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

+--------+
| string |
+--------+
| AÿȁЀ   |
+--------+

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 em um único resultado. Todos os valores precisam ser BYTES ou tipos de dados que podem ser convertidos em STRING.

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            |
+---------------------+
SELECT CONCAT("Summer", " ", 1923) as release_date;

+---------------------+
| release_date        |
+---------------------+
| Summer 1923         |
+---------------------+

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 BigQuery é compatível com a função 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
DATE 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

FLOAT64
G Notação decimal ou 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

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 BigQuery, com um tipo semelhante ao do valor (talvez mais amplo ou string). Consulte Comportamento de %t e %T. 'amostra'
b'amostra de 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 (informe-se 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`, and `%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 complementar 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"
DATE 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
STRUCT (valor, valor, ...)
onde os campos são formatados com %t
(valor, valor, ...)
em que os campos são formatados com %T

Casos especiais:
Nenhum campo: STRUCT()
Um campo: STRUCT(value)

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 BigQuery faz o cast desses valores para STRING. Para %T, o BigQuery 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.

Tipo de retorno

BYTES

Exemplo

SELECT FROM_BASE64('3q2+7w==') AS byte_data;

-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| 3q2+7w==  |
+-----------+

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 (em inglês). 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   |
+-----------+

NORMALIZE

NORMALIZE(value[, normalization_mode])

Descrição

Usa uma string de valor e retorna como uma string normalizada.

A normalização (em inglês) é utilizada para garantir que duas strings sejam equivalentes. Em geral, a normalização é usada em situações em que duas strings são renderizadas da mesma maneira na tela, mas têm diferentes pontos de código Unicode.

NORMALIZE é compatível com quatro modos de normalização opcionais:

Valor Nome Descrição
NFC Composição canônica do Formulário de normalização Decompõe e recompõe caracteres por equivalência canônica.
NFKC Composição de Compatibilidade do Formulário de Normalização Decompõe caracteres por compatibilidade e os recompõe por equivalência canônica.
NFD Decomposição canônica do Formulário de normalização Decompõe caracteres por equivalência canônica. Vários caracteres de combinação são organizados em uma ordem específica.
NFKD Decomposição de compatibilidade do Formulário de normalização Decompõe caracteres por compatibilidade. Vários caracteres de combinação são organizados em uma ordem específica.

O modo de normalização padrão é NFC.

Tipo de retorno

STRING

Exemplos

SELECT a, b, a = b as normalized
FROM (SELECT NORMALIZE('\u00ea') as a, NORMALIZE('\u0065\u0302') as b)
AS normalize_example;

+---+---+------------+
| a | b | normalized |
+---+---+------------+
| ê | ê | true       |
+---+---+------------+

O exemplo a seguir normaliza diferentes caracteres de espaços.

WITH EquivalentNames AS (
  SELECT name
  FROM UNNEST([
      'Jane\u2004Doe',
      'John\u2004Smith',
      'Jane\u2005Doe',
      'Jane\u2006Doe',
      'John Smith']) AS name
)
SELECT
  NORMALIZE(name, NFKC) AS normalized_name,
  COUNT(*) AS name_count
FROM EquivalentNames
GROUP BY 1;

+-----------------+------------+
| normalized_name | name_count |
+-----------------+------------+
| John Smith      | 2          |
| Jane Doe        | 3          |
+-----------------+------------+

NORMALIZE_AND_CASEFOLD

NORMALIZE_AND_CASEFOLD(value[, normalization_mode])

Descrição

Usa uma STRING, uma value e executa as mesmas ações que NORMALIZE, assim como realiza casefold (em inglês) de operações indiferentes a maiúsculas.

NORMALIZE_AND_CASEFOLD é compatível com quatro modos de normalização opcionais:

Valor Nome Descrição
NFC Composição canônica do Formulário de normalização Decompõe e recompõe caracteres por equivalência canônica.
NFKC Composição de Compatibilidade do Formulário de Normalização Decompõe caracteres por compatibilidade e os recompõe por equivalência canônica.
NFD Decomposição canônica do Formulário de normalização Decompõe caracteres por equivalência canônica. Vários caracteres de combinação são organizados em uma ordem específica.
NFKD Decomposição de compatibilidade do Formulário de normalização Decompõe caracteres por compatibilidade. Vários caracteres de combinação são organizados em uma ordem específica.

O modo de normalização padrão é NFC.

Tipo de retorno

STRING

Exemplo

WITH Strings AS (
  SELECT '\u2168' AS a, 'IX' AS b UNION ALL
  SELECT '\u0041\u030A', '\u00C5'
)
SELECT a, b,
  NORMALIZE_AND_CASEFOLD(a, NFD)=NORMALIZE_AND_CASEFOLD(b, NFD) AS nfd,
  NORMALIZE_AND_CASEFOLD(a, NFC)=NORMALIZE_AND_CASEFOLD(b, NFC) AS nfc,
  NORMALIZE_AND_CASEFOLD(a, NFKD)=NORMALIZE_AND_CASEFOLD(b, NFKD) AS nkfd,
  NORMALIZE_AND_CASEFOLD(a, NFKC)=NORMALIZE_AND_CASEFOLD(b, NFKC) AS nkfc
FROM Strings;

+---+----+-------+-------+------+------+
| a | b  | nfd   | nfc   | nkfd | nkfc |
+---+----+-------+-------+------+------+
| Ⅸ | IX | false | false | true | true |
| Å | Å  | true  | true  | true | true |
+---+----+-------+-------+------+------+

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      |
+------+------+-----------+

REVERSE

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(string, substring)

Descrição

Retorna o índice com base em 1 da primeira ocorrência de substring em string. Retorna 0 se substring 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 codificado 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.

Tipo de retorno

STRING

Exemplo

SELECT TO_BASE64(b'\xde\xad\xbe\xef') AS base64_string;

+---------------+
| base64_string |
+---------------+
| 3q2+7w==      |
+---------------+

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 (em inglês). 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 BigQuery é 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.

JSON_EXTRACT ou JSON_EXTRACT_SCALAR

JSON_EXTRACT(json_string_expr, json_path_string_literal), que retorna valores JSON como STRINGs.

JSON_EXTRACT_SCALAR(json_string_expr, json_path_string_literal), que retorna valores JSON escalares como STRINGs.

Descrição

Extrai valores JSON ou valores escalares JSON como strings.

  • json_string_expr: uma string formatada em JSON. Exemplo:

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

Quando a chave JSON usar caracteres inválidos do JSONPath, é possível inserir caracteres de escape usando aspas simples e colchetes.

Tipo de retorno

STRINGs

Exemplos

SELECT JSON_EXTRACT(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_EXTRACT(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_EXTRACT(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_EXTRACT(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"}] |
+------------------------------------+
SELECT JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.name') as json_name,
  JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.name') as scalar_name,
  JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.age') as json_age,
  JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.age') as scalar;

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

Quando a chave JSON usar caracteres inválidos do JSONPath, use aspas simples e colchetes para inserir caracteres de escape, [' ']. Exemplo:

SELECT JSON_EXTRACT_SCALAR('{"a.b": {"c": "world"}}', "$['a.b'].c") as hello;

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

JSON_EXTRACT_ARRAY

JSON_EXTRACT_ARRAY(json_string_expr[, json_path_string_literal])

Descrição

Extrai uma matriz de uma string formatada em JSON.

  • json_string_expr: uma string formatada em JSON. Exemplo:

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path_string_literal: formato JSONpath. Identifica os valores que você quer obter da string formatada em JSON. Se esse parâmetro opcional não for fornecido, o símbolo $ do JSONPath será aplicado, o que significa que toda a string formatada em JSON será analisada.

Quando a chave JSON usar caracteres inválidos do JSONPath, é possível inserir caracteres de escape usando aspas simples e colchetes.

Tipo de retorno

ARRAY<STRING>

Exemplos

Isso extrai os itens em uma string formatada em JSON para uma matriz de string:

SELECT JSON_EXTRACT_ARRAY('[1,2,3]') as string_array

+----------------+
| string_array   |
+----------------+
| ['1','2','3']  |
+----------------+

Isso extrai uma matriz de strings e a converte em uma matriz de inteiros:

SELECT ARRAY(
  SELECT CAST(integer_element as INT64)
  FROM UNNEST(
    JSON_EXTRACT_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array

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

Isso extrai os valores de string de uma string formatada em JSON para uma matriz:

-- Don't strip the double quotes
SELECT JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]', '$') as string_array

+--------------------------------------+
| string_array                         |
+--------------------------------------+
| ['"apples"','"oranges"','"grapes"']  |
+--------------------------------------+

-- Strip the double quotes
SELECT ARRAY(
  SELECT JSON_EXTRACT_SCALAR(string_element, '$')
  FROM UNNEST(JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array

+---------------------------------+
|  string_array                   |
+---------------------------------+
| ['apples', 'oranges', 'grapes'] |
+---------------------------------+

Isso extrai apenas os itens em fruit para uma matriz:

SELECT JSON_EXTRACT_ARRAY(
  '{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}],"vegetables":[{"lettuce":7,"kale": 8}}',
  '$.fruit'
) as string_array

+----------------------------------------------------------------------+
| string_array                                                         |
+----------------------------------------------------------------------+
| ['{"apples" : 5, "oranges" : 10}' , '{"apples" : 2, "oranges" : 4}'] |
+----------------------------------------------------------------------+

Eles são equivalentes:

JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') as string_array

JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') as string_array

-- The queries above produce this result:
+---------------------------------------+
|  string_array                         |
+---------------------------------------+
| [""apples"", ""oranges"", ""grapes""] |
+---------------------------------------+

Quando a chave JSON usar caracteres inválidos do JSONPath, use aspas simples e colchetes para inserir caracteres de escape, [' ']. Exemplo:

SELECT JSON_EXTRACT_ARRAY('{"a.b": {"c": "world"}}', "$['a.b'].c") as hello;

+---------+
| hello   |
+---------+
| [world] |
+---------+

Estes exemplos mostram como solicitações inválidas e matrizes vazias são tratadas:

  • Se um JSONPath for inválido, um erro será gerado.
  • Se uma string formatada em JSON for inválida, a saída será NULL.
  • Não há problema em haver matrizes vazias na string formatada em JSON.
-- An error is thrown if you provide an invalid JSONPath.
JSON_EXTRACT_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') as result

-- If a key is not specified when a key is expected, the result is NULL.
JSON_EXTRACT_ARRAY('{"a":"foo"}','$') as result

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a key that does not exist is specified, the result is NULL.
JSON_EXTRACT_ARRAY('{"a":"foo"}','$.b') as result

+--------+
| result |
+--------+
| NULL   |
+--------+

-- Empty arrays in JSON-formatted strings are supported.
JSON_EXTRACT_ARRAY('{"a":"foo","b":[]}','$.b') as result

+--------+
| result |
+--------+
| []     |
+--------+

JSON_QUERY ou JSON_VALUE

JSON_QUERY(json_string_expr, json_path_string_literal), que retorna valores JSON como STRINGs.

JSON_VALUE(json_string_expr, json_path_string_literal), que retorna valores JSON escalares como STRINGs.

Descrição

Extrai valores JSON ou valores escalares JSON como strings.

  • json_string_expr: uma string formatada em JSON. Exemplo:

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

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

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"}] |
+------------------------------------+
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;

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

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 |
+-------+

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])

Descrição

Retorna uma representação de string formatada em JSON de value. Essa função aceita um parâmetro pretty_print opcional. Se pretty_print estiver presente, o valor retornado será formatado para facilitar a leitura.

Tipo de dados de entrada Valor retornado
NULL de qualquer tipo null
BOOL true ou false.
INT64

O mesmo que CAST(value AS STRING) quando value está no intervalo de [-253, 253], que é o intervalo de números inteiros que podem ser representados sem perdas como números de ponto flutuante de precisão dupla IEEE 754. Valores fora desse intervalo são representados como strings entre aspas. Exemplo:

-1
0
12345678901
9007199254740992
-9007199254740992
"9007199254740993"

9007199254740993 é maior que 253, por isso é representado como uma string entre aspas.

NUMERIC

O mesmo que CAST(value AS STRING) quando value está no intervalo de [-253, 253] e não tem parte fracionária. Valores fora desse intervalo são representados como strings entre aspas. Por exemplo:

-1
0
"9007199254740993"
"123.56"
FLOAT64 +/-inf e NaN são representadas como Infinity, -Infinity e NaN, respectivamente.

Caso contrário, é igual a CAST(value AS STRING).

STRING Valor de string entre aspas, com escape de acordo com o padrão JSON. Especificamente, ", \ e os caracteres de controle de U+0000 a U+001F são escapados.
BYTES

Valor de escape de base64 RFC 4648 entre aspas. Exemplo:

"R29vZ2xl" é a representação base64 de bytes b"Google"

DATE

Data entre aspas. Exemplo:

"2017-03-06"
TIMESTAMP

Data/hora ISO 8601 entre aspas, em que T separa a data e a hora e Zulu/UTC representa o fuso horário. Exemplo:

"2017-03-06T12:34:56.789012Z"
DATETIME

Data/hora ISO 8601 entre aspas, em que T separa a data e a hora. Por exemplo:

"2017-03-06T12:34:56.789012"
TIME

Hora ISO 8601 entre aspas. Exemplo:

"12:34:56.789012"
ARRAY

[elem1,elem2,...], onde cada elem é formatado de acordo com o tipo de elemento.

Exemplo com formatação:

[
  elem1,
  elem2,
  ...
]

Onde cada elem é formatado de acordo com o tipo de elemento. A matriz vazia é representada como [].

STRUCT {"field_name1":field_value1,"field_name2":field_value2,...}

Onde cada field_value é formatado de acordo com seu tipo.

Exemplo com formatação:

{
  "field_name1": field_value1,
  "field_name2": field_value2,
  ...
}

Onde cada field_value é formatado de acordo com seu tipo. Se um field_value for um STRUCT ou ARRAY não vazio, os elementos serão recuados para o nível apropriado. A estrutura vazia é representada como {}.

Campos com nomes duplicados podem resultar em JSON não analisáveis. Campos anônimos são representados com "".

Nomes de campos UTF-8 inválidos podem resultar em JSON não analisáveis. Valores de string são escapados de acordo com o padrão JSON. Especificamente, ", \ e os caracteres de controle de U+0000 a U+001F são escapados.

Tipo de retorno

Representação do valor de string JSON.

Exemplos

Converta linhas em uma tabela para JSON.

WITH Input AS (
  SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
  SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
  SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
  t,
  TO_JSON_STRING(t) AS json_row
FROM Input AS t;

A consulta acima apresenta o resultado a seguir:

+-----------------------------------+-------------------------------------------------------+
| t                                 | json_row                                              |
+-----------------------------------+-------------------------------------------------------+
| {[1, 2], foo, {true, 2017-04-05}} | {"x":[1,2],"y":"foo","s":{"a":true,"b":"2017-04-05"}} |
| {NULL, , {false, 0001-01-01}}     | {"x":null,"y":"","s":{"a":false,"b":"0001-01-01"}}    |
| {[3], bar, {NULL, 2016-12-05}}    | {"x":[3],"y":"bar","s":{"a":null,"b":"2016-12-05"}}   |
+-----------------------------------+-------------------------------------------------------+

Converta linhas em uma tabela para JSON com formatação.

WITH Input AS (
  SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
  SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
  SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
  TO_JSON_STRING(t, true) AS json_row
FROM Input AS t;

A consulta acima apresenta o resultado a seguir:

+-----------------------+
| json_row              |
+-----------------------+
| {                     |
|  "x": [               |
|    1,                 |
|    2                  |
|  ],                   |
|  "y": "foo",          |
|  "s": {               |
|    "a": true,         |
|    "b": "2017-04-05"  |
|  }                    |
|}                      |
| {                     |
|  "x": null,           |
|  "y": "",             |
|  "s": {               |
|    "a": false,        |
|    "b": "0001-01-01"  |
|  }                    |
|}                      |
| {                     |
|  "x": [               |
|    3                  |
|  ],                   |
|  "y": "bar",          |
|  "s": {               |
|    "a": null,         |
|    "b": "2016-12-05"  |
|  }                    |
|}                      |
+-----------------------+

Formato JSONPath

A maioria das funções JSON transmitem em um parâmetro json_string_expr e json_path_string_literal. O parâmetro json_string_expr transmite uma string formatada em JSON, e o parâmetro json_path_string_literal 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_string_literal é 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_string_literal 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 retornará um erro: o BigQuery 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