Funções e operadores do SQL padrão

• Regras de chamada de função
• Prefixo SAFE.
• Regras de conversão
  • Cast
    • Cast seguro
    • Cast de strings hexadecimais em inteiros
    • Cast de tipos de data
    • Cast de tipos de carimbo de data/hora
    • Cast entre tipos de data e carimbo de data/hora
  • Coerção
    • Coerção de literal
    • Coerção de parâmetro
  • Funções de conversão adicionais
• Funções de agregação
  • ANY_VALUE
  • ARRAY_AGG
  • ARRAY_CONCAT_AGG
  • AVG
  • BIT_AND
  • BIT_OR
  • BIT_XOR
  • COUNT
  • COUNTIF
  • LOGICAL_AND
  • LOGICAL_OR
  • MAX
  • MIN
  • STRING_AGG
  • SUM
• Funções de agregação estatística
  • CORR
  • COVAR_POP
  • COVAR_SAMP
  • STDDEV_POP
  • STDDEV_SAMP
  • STDDEV
  • VAR_POP
  • VAR_SAMP
  • VARIANCE
• Funções de agregação aproximada
  • APPROX_COUNT_DISTINCT
  • APPROX_QUANTILES
  • APPROX_TOP_COUNT
  • APPROX_TOP_SUM
• Funções HyperLogLog++
  • HLL_COUNT.INIT
  • HLL_COUNT.MERGE
  • HLL_COUNT.MERGE_PARTIAL
  • HLL_COUNT.EXTRACT
• Funções de numeração
  • RANK
  • DENSE_RANK
  • PERCENT_RANK
  • CUME_DIST
  • NTILE
  • ROW_NUMBER
• Funções Bit
  • BIT_COUNT
• Funções matemáticas
  • ABS
  • SIGN
  • IS_INF
  • IS_NAN
  • IEEE_DIVIDE
    • Casos especiais de IEEE_DIVIDE
  • RAND
  • SQRT
  • POW
  • POWER
    • Casos especiais de POW(X, Y) e POWER(X, Y)
  • EXP
  • LN
  • LOG
    • Casos especiais de LOG(X, Y)
  • LOG10
  • GREATEST
  • LEAST
  • DIV
  • SAFE_DIVIDE
  • MOD
  • ROUND
  • TRUNC
  • CEIL
  • CEILING
  • FLOOR
    • Exemplo de comportamento da função de arredondamento
  • COS
  • COSH
  • ACOS
  • ACOSH
  • SIN
  • SINH
  • ASIN
  • ASINH
  • TAN
  • TANH
  • ATAN
  • ATANH
  • ATAN2
    • Casos especiais de ATAN2()
    • Casos especiais de funções trigonométricas e hiperbólicas de arredondamento
• Funções de navegação
  • FIRST_VALUE
  • LAST_VALUE
  • NTH_VALUE
  • LEAD
  • LAG
  • PERCENTILE_CONT
  • PERCENTILE_DISC
• Funções de agregação analítica
• Funções de hash
  • FARM_FINGERPRINT
  • MD5
  • SHA1
  • SHA256
  • SHA512
• Funções de string
  • BYTE_LENGTH
  • CHAR_LENGTH
  • CHARACTER_LENGTH
  • CODE_POINTS_TO_BYTES
  • CODE_POINTS_TO_STRING
  • CONCAT
  • ENDS_WITH
  • FORMAT
    • Sintaxe
    • Especificadores de formatos compatíveis
      • Sinalizações
      • Largura
      • Precisão
    • Comportamento de %t e %T
    • Condições de erro
    • Tratamento do argumento NULL
    • Outras regras semânticas
  • FROM_BASE32
  • FROM_BASE64
  • FROM_HEX
  • LENGTH
  • LPAD
  • LOWER
  • LTRIM
  • NORMALIZE
  • NORMALIZE_AND_CASEFOLD
  • REGEXP_CONTAINS
  • REGEXP_EXTRACT
  • REGEXP_EXTRACT_ALL
  • REGEXP_REPLACE
  • REPLACE
  • REPEAT
  • REVERSE
  • RPAD
  • RTRIM
  • SAFE_CONVERT_BYTES_TO_STRING
  • SPLIT
  • STARTS_WITH
  • STRPOS
  • SUBSTR
  • TO_BASE32
  • TO_BASE64
  • TO_CODE_POINTS
  • TO_HEX
  • TRIM
  • UPPER
• Funções JSON
  • JSON_EXTRACT ou JSON_EXTRACT_SCALAR
  • TO_JSON_STRING
• Funções de matriz
  • ARRAY
  • ARRAY_CONCAT
  • ARRAY_LENGTH
  • ARRAY_TO_STRING
  • GENERATE_ARRAY
  • GENERATE_DATE_ARRAY
  • GENERATE_TIMESTAMP_ARRAY
  • OFFSET e ORDINAL
  • ARRAY_REVERSE
  • SAFE_OFFSET e SAFE_ORDINAL
• Funções de data
  • CURRENT_DATE
  • EXTRACT
  • DATE
  • DATE_ADD
  • DATE_SUB
  • DATE_DIFF
  • DATE_TRUNC
  • DATE_FROM_UNIX_DATE
  • FORMAT_DATE
  • PARSE_DATE
  • UNIX_DATE
  • Elementos de formatos compatíveis de DATE
• Funções DateTime
  • CURRENT_DATETIME
  • DATETIME
  • DATETIME_ADD
  • DATETIME_SUB
  • DATETIME_DIFF
  • DATETIME_TRUNC
  • FORMAT_DATETIME
  • PARSE_DATETIME
  • Elementos de formatos compatíveis de DATETIME
• Funções de hora
  • CURRENT_TIME
  • TIME
  • TIME_ADD
  • TIME_SUB
  • TIME_DIFF
  • TIME_TRUNC
  • FORMAT_TIME
  • PARSE_TIME
  • Elementos de formatos compatíveis de TIME
• Funções de carimbo de data e hora
  • CURRENT_TIMESTAMP
  • EXTRACT
  • STRING
  • TIMESTAMP
  • TIMESTAMP_ADD
  • TIMESTAMP_SUB
  • TIMESTAMP_DIFF
  • TIMESTAMP_TRUNC
  • FORMAT_TIMESTAMP
  • PARSE_TIMESTAMP
  • TIMESTAMP_SECONDS
  • TIMESTAMP_MILLIS
  • TIMESTAMP_MICROS
  • UNIX_SECONDS
  • UNIX_MILLIS
  • UNIX_MICROS
  • Elementos de formatos compatíveis de TIMESTAMP
  • Definições de fuso horário
• Funções de segurança
  • SESSION_USER
• Funções de rede
  • NET.IP_FROM_STRING
  • NET.SAFE_IP_FROM_STRING
  • NET.IP_TO_STRING
  • NET.IP_NET_MASK
  • NET.IP_TRUNC
  • NET.IPV4_FROM_INT64
  • NET.IPV4_TO_INT64
  • NET.HOST
  • NET.PUBLIC_SUFFIX
  • NET.REG_DOMAIN
• Operadores
  • Operadores de acesso ao elemento
  • Operadores aritméticos
  • Operadores bit a bit
  • Operadores lógicos
  • Operadores de comparação
  • Operadores IN
  • Operadores IS
• Expressões condicionais
• Subconsultas de expressão
• Funções de depuração
  • ERROR

Nesta página, explicamos as expressões do BigQuery, incluindo as funções e os operadores.

Regras de chamada de função

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

• Para funções que aceitam tipos numéricos, se um operando for um ponto flutuante e o outro for um tipo numérico, ambos serão convertidos para FLOAT64 antes que a função seja avaliada.

• Se um operando for NULL, o resultado será NULL, com exceção do operador IS.

• Para funções que fazem diferenciação do fuso horário (conforme indicado na descrição da função), o fuso horário padrão, UTC, será usado se um não for especificado.

Prefixo SAFE.

Sintaxe:

SAFE.function_name()

Descrição

Se você começar uma função com o prefixo SAFE., ele retornará NULL em vez de um erro. O prefixo SAFE. só evita erros da própria função prefixada, e não os ocorridos durante a avaliação de expressões de argumentos. O prefixo SAFE. só evita erros ocorridos por causa do valor das entradas de função, como os do tipo "valor fora do intervalo". Outros erros, como os internos ou de sistema, ainda podem ocorrer. Se a função não retornar um erro, SAFE. não afetará a saída. Se a função jamais retornar um erro, como RAND, SAFE. não entrará em vigor.

O prefixo SAFE. não é aceito por operadores como + e =. Para evitar erros de uma operação de divisão, use SAFE_DIVIDE. O prefixo SAFE. não é compatível com alguns operadores, como IN, ARRAY e UNNEST, apesar de eles parecerem funções. As funções CAST e EXTRACT também não aceitam o prefixo SAFE.. Para evitar erros de cast, use SAFE_CAST.

Exemplo

No exemplo a seguir, o primeiro uso da função SUBSTR normalmente retornaria um erro porque a função não aceita argumentos de comprimento com valores negativos. No entanto, o prefixo SAFE. faz a função retornar NULL. O segundo uso da função SUBSTR fornece a saída esperada: o prefixo SAFE. não entra em vigor.

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 aceita o uso do prefixo SAFE. com a maioria das funções escalares que podem gerar erros, inclusive funções STRING, matemáticas, DATE, DATETIME e TIMESTAMP. O BigQuery não aceita o uso do prefixo SAFE. com funções agregadas, analíticas ou definidas pelo usuário.

Regras de conversão

A "conversão" inclui, sem limitação, cast e coerção.

• "Cast" é a conversão explícita e usa a função CAST().
• "Coerção" é a conversão implícita realizada automaticamente pelo BigQuery nas condições descritas abaixo.
• Há um terceiro grupo de funções de conversão com seus próprios nomes, 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" aplica todas as expressões de um determinado tipo de dados (como 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 ver 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)

Cast syntax is used in a query to indicate that the result type of an expression should be converted to some other type.

Exemplo:

CAST(x=1 AS STRING)

O resultado será "true" se x for 1, "false" para outro valor não 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 tempo de execução. Por exemplo, o cast de BYTES para STRING em que a sequência de bytes não é um UTF-8 válido resulta em um erro de tempo de execução.

Quando é feito o cast de uma expressão x dos tipos a seguir, estas regras são aplicadas:

De	Para	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 um 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 literal FLOAT64 válido. Também é compatível com casts de "inf", "+inf", "-inf" e "nan". As conversões são indiferentes a minúsculas.
STRING	NUMERIC	É preciso que o literal numérico contido na STRING não exceda a precisão máxima ou o intervalo do tipo NUMERIC. Caso contrário, ocorrerá um erro. Se o número de dígitos depois do ponto decimal exceder nove, o valor NUMERIC resultante 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 geram 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	As STRINGs são convertidas em BYTES usando a codificação UTF-8. Por exemplo, a STRING "©", quando convertida em BYTES, torna-se uma sequência de dois bytes com os valores hexadecimais C2 e A9.
BYTES	STRING	Retorna x, interpretado como STRING UTF-8. Por exemplo, o literal BYTES b'\xc2\xa9', quando convertido em STRING, é interpretado como UTF-8 e se torna o caractere unicode "©". Ocorrerá um erro, se x não for um UTF-8 válido.
ARRAY	ARRAY	Precisa ser exatamente do mesmo tipo ARRAY.
STRUCT	STRUCT	Permitido, se as seguintes condições forem atendidas: Os dois STRUCTs têm o mesmo número de campos. Os tipos originais de campos STRUCT são convertidos explicitamente 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 estiver fazendo o cast de bytes em strings, você também poderá usar a função SAFE_CONVERT_BYTES_TO_STRING. Todo caractere UTF-8 inválido é substituído pelo caractere unicode U+FFFD. Consulte SAFE_CONVERT_BYTES_TO_STRING para mais informações.

Cast de strings hexadecimais em inteiros

No caso das strings hexadecimais (0x123), faça o cast delas 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 data

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

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 compatível, ocorrerá um erro.

Cast de tipos de carimbo de data/hora

O BigQuery é compatível com o cast de tipos de timestamp para/de strings da seguinte maneira:

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

No cast de tipos de timestamp para string, o timestamp é interpretado com o fuso horário UTC padrão. 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 de string para timestamp, string_expression precisa seguir os formatos compatíveis com literal de timestamp. Caso contrário, ocorrerá um erro de tempo de execução. O próprio string_expression pode conter um time_zone. Consulte fusos horários. Se houver um fuso horário em string_expression, ele será usado para a conversão. Caso contrário, o fuso horário UTC padrão será usado. Se a string tiver menos de seis dígitos, ela será ampliada implicitamente.

Ocorrerá um erro se string_expression for inválido, tiver mais de seis dígitos de subsegundos (isto é, se a precisão for maior que microssegundos) ou representar uma hora fora do intervalo do carimbo de data/hora compatível.

Cast entre tipos de data e carimbo de data/hora

O BigQuery é compatível com cast entre tipos de data e timestamp da seguinte maneira:

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

O cast de uma data para um timestamp interpreta date_expression a partir da meia-noite (início do dia) no fuso horário UTC padrão. O cast de um timestamp para uma data trunca efetivamente o timestamp a partir do fuso horário 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 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, a expressão func("2014-09-27") será válida porque o literal STRING "2014-09-27" é 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 DATE

• funções DATETIME

• funções TIME

• funções TIMESTAMP

Funções de agregação

A função de agregação realiza um cálculo em um grupo de valores. 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 UNNEST([NULL, "apple", "pear", "orange"]) as fruit;

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

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 um valor da entrada ou NULL, se houver zero linhas de entrada. O valor retornado não é determinista. Isso significa que o resultado pode ser diferente sempre que essa função é usada.

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 uma ARRAY de valores de 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, essa cláusula é incompatível com todas as outras cláusulas em ARRAY_AGG().

2. DISTINCT: cada valor distinto de expression é agregado somente uma vez ao resultado.

3. IGNORE NULLS ou RESPECT NULLS: se IGNORE NULLS for especificado, os valores NULL serão excluídos do resultado. Se RESPECT NULLS for especificado, ou se nenhum for, os valores NULL serão incluídos no resultado (no entanto, um erro será gerado, se uma matriz do 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 o mínimo valor possível. Ou seja, os NULLs aparecem em primeiro lugar nas classificações ASC e em último nas classificações DESC.
   • Tipos de dados de ponto flutuante: consulte Semântica de ponto flutuante para saber sobre ordenação e agrupamento.
   • Se DISTINCT também for especificado, a chave de classificação terá que ser a mesma de expression.
   • Se ORDER BY não for especificada, a ordem dos elementos na matriz de saída não será determinista. Isso significa que o resultado poderá ser diferente sempre que essa função for usada.

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([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

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

SELECT FORMAT("%T", ARRAY_AGG(DISTINCT x)) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

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

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([NULL, 1, -2, 3, -2, 1, NULL]) AS x;
+-------------------------------+
| array_agg                     |
+-------------------------------+
| [NULL, NULL, 1, 1, -2, -2, 3] |
+-------------------------------+

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

+----------------------+
| array_agg            |
+----------------------+
| [NULL, 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([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

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

ARRAY_CONCAT_AGG

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

Descrição

Concatena elementos da expression do tipo ARRAY e retorna 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 ordenação da matriz não é compatível. Sendo assim, a chave de classificação não pode ser a mesma de expression.
   • NULLs: no contexto da cláusula ORDER BY, NULLs são o mínimo valor possível. Ou seja, os NULLs aparecem em primeiro lugar nas classificações ASC e em último nas classificações DESC.
   • Tipos de dados de ponto flutuante: consulte Semântica de ponto flutuante para saber sobre ordenação e agrupamento.
   • Se ORDER BY não for especificada, a ordem dos elementos na matriz de saída não será determinista. Isso significa que o resultado poderá ser diferente sempre que essa função for usada.

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 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 houver linhas de entrada igual a zero ou expression for avaliada como NULL em 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 [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                  |
+-----------------------------------+
| [5, 6, 7, 8, 9, NULL, 1, 2, 3, 4] |
+-----------------------------------+

SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x LIMIT 2)) 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] |
+--------------------------+

SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x) LIMIT 2)) 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 |
+------------------+
| [5, 6, 7, 8, 9]  |
+------------------+

AVG

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

Descrição

Retorna a média de valores de entrada diferentes de 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, essa cláusula é incompatível com todas as outras cláusulas em AVG().

2. DISTINCT: cada valor distinto de expression é agregado somente uma vez ao resultado.

Tipos de dados retornados

• NUMERIC caso o tipo de entrada seja NUMERIC
• FLOAT64

Exemplos

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

+-----+
| avg |
+-----+
| 3   |
+-----+

SELECT AVG(DISTINCT x) AS avg
FROM UNNEST([0, 2, NULL, 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

Realiza 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

Realiza 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

Realiza 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 somente uma vez ao 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 avaliada para qualquer valor diferente de NULL.

Tipos de argumentos compatíveis

expression pode ser qualquer tipo de dados.

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 somente uma vez ao resultado.

Tipos de dados retornados

INT64

Exemplos

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

+------------+---------+--------------+
| count_star | count_x | count_dist_x |
+------------+---------+--------------+
| 5          | 4       | 3            |
+------------+---------+--------------+

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

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

COUNTIF

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

Descrição

Retorna a contagem de valores TRUE de expression. Retorna 0, se houver linhas de entrada igual a zero ou expression for avaliada como FALSE em 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, NULL, -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 houver linhas de entrada igual a zero ou expression for avaliada como NULL em 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 houver linhas de entrada igual a zero ou expression for avaliada como NULL em 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 houver linhas de entrada igual a zero ou expression for avaliada como NULL em todas as linhas. Retorna NaN, se a entrada contiver um NaN.

Tipos de argumentos compatíveis

Qualquer tipo de dados, exceto: STRUCT ARRAY

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, NULL, 37, 4, NULL, 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 houver linhas de entrada igual a zero ou expression for avaliada como NULL em todas as linhas. Retorna NaN, se a entrada contiver um NaN.

Tipos de argumentos compatíveis

Qualquer tipo de dados, exceto: STRUCT ARRAY

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, NULL, 37, 4, NULL, 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 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, essa cláusula é incompatível com todas as outras cláusulas em STRING_AGG().

2. DISTINCT: cada valor distinto de expression é agregado somente uma vez ao 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 o mínimo valor possível. Ou seja, os NULLs aparecem em primeiro lugar nas classificações ASC e em último nas classificações DESC.
   • Tipos de dados de ponto flutuante: consulte Semântica de ponto flutuante para saber sobre ordenação e agrupamento.
   • Se DISTINCT também for especificado, a chave de classificação terá que ser a mesma de expression.
   • Se ORDER BY não for especificada, a ordem dos elementos na matriz de saída não será determinista. Isso significa que o resultado poderá ser diferente sempre que essa função for usada.

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 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", NULL, "pear", "banana", "pear"]) AS fruit;

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

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

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

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

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

SELECT STRING_AGG(fruit, " & " LIMIT 2) AS string_agg
FROM UNNEST(["apple", NULL, "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", NULL, "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 somente uma vez ao 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 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   |
+---+-----+

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 contenham 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 contenham um ou mais valores NULL. Se não houver um par de entrada sem valores NULL, a função retornará NULL. Se houver exatamente um par de entrada sem valores NULL, a 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 grupo 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 contenham 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, essa cláusula é incompatível com todas as outras cláusulas em STDDEV_POP().

2. DISTINCT: cada valor distinto de expression é agregado somente uma vez ao 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, essa cláusula é incompatível com todas as outras cláusulas em STDDEV_SAMP().

2. DISTINCT: cada valor distinto de expression é agregado somente uma vez ao resultado.

Tipo de dados retornados

FLOAT64

STDDEV

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

Descrição

Um alias 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, essa cláusula é incompatível com todas as outras cláusulas em VAR_POP().

2. DISTINCT: cada valor distinto de expression é agregado somente uma vez ao 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, essa cláusula é incompatível com todas as outras cláusulas em VAR_SAMP().

2. DISTINCT: cada valor distinto de expression é agregado somente uma vez ao resultado.

Tipo de dados retornados

FLOAT64

VARIANCE

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

Descrição

Um alias de VAR_SAMP.

Funções de agregação aproximada

As funções de agregação aproximada são escalonáveis no que diz respeito ao uso e ao tempo da memória. No entanto, elas produzem resultados aproximados, em vez de exatos. Para mais informações, consulte Agregação aproximada.

APPROX_COUNT_DISTINCT

APPROX_COUNT_DISTINCT(expression)

Descrição

Retorna o resultado aproximado de 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 enormes.

Tipos de argumentos compatíveis

Todos os tipos de dados, 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_QUANTILES

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

Descrição

Retorna os limites aproximados de um grupo de valores expression, no qual number representa o número de quantis a serem criados. Essa função retorna uma matriz de number + 1 elementos, no qual 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 somente uma vez ao resultado.

2. IGNORE NULLS ou RESPECT NULLS: se IGNORE NULLS for especificado ou se nenhum for, os valores NULL serão excluídos do resultado. Se RESPECT NULLS for especificado, os valores NULL serão incluídos no resultado (no entanto, um erro será gerado se uma matriz do resultado da consulta final contiver um elemento NULL).

Tipos de dados retornados

Um ARRAY do tipo especificado pelo parâmetro expression.

Retorna NULL, se houver linhas de entrada igual a zero ou expression for avaliada como NULL em todas as linhas.

Exemplos

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

+------------------+
| approx_quantiles |
+------------------+
| [1, 5, 10]       |
+------------------+

SELECT APPROX_QUANTILES(DISTINCT x, 2) AS approx_quantiles
FROM UNNEST([NULL, NULL, 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 superiores aproximados da expression. O parâmetro number especifica o número de elementos retornados.

Tipos de argumentos compatíveis

expression pode ser quaisquer tipos de dados compatíveis com a cláusula GROUP BY.

number precisa ser INT64.

Tipos de dados retornados

Um ARRAY do tipo STRUCT que contém dois campos. O primeiro campo contém um valor input. O segundo campo contém um INT64 especificando o número de vezes que input 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. Por 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 superiores 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, essa função retornará um erro.

Tipos de argumentos compatíveis

expression pode ser quaisquer tipos de dados compatíveis com a cláusula GROUP BY.

weight precisa ser um dos itens a seguir:

• INT64
• FLOAT64

number precisa ser INT64.

Tipos de dados retornados

Um ARRAY do tipo STRUCT. O STRUCT contém dois campos: value e sum. O campo value contém o valor da expressão da entrada. O campo sum tem o mesmo tipo de weight. Ele é a soma aproximada da ponderação de entrada associada 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 os valores NULL dos 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 BigQuery aceita as funções de agregação aproximada a seguir com o uso do algoritmo HyperLogLog++. Para uma explicação de como as funções de agregação aproximada funcionam, consulte Agregação aproximada.

HLL_COUNT.INIT

HLL_COUNT.INIT(input [, precision])

Descrição

Uma função escalar que utiliza um ou mais valores input e os agrega em um esboço HyperLogLog++. Cada esboço é representado pelo tipo de dado BYTES. Os esboços podem ser mesclados com o uso de HLL_COUNT.MERGE ou HLL_COUNT.MERGE_PARTIAL. Se nenhuma mesclagem for necessária, você pode extrair a contagem final de valores distintos por meio de HLL_COUNT.EXTRACT.

Um input pode ser:

• INT64
• STRING
• BYTES

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 esboços ou armazená-los no disco. A tabela a seguir mostra os valores de precisão permitidos, o tamanho máximo do esboço 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 esboço (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.

Tipo de entrada compatível

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 esboços definidos por HyperLogLog++ pelo cálculo da união.

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

Essa função ignora os valores NULL ao mesclar esboços. 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 utiliza uma ou mais entradas HyperLogLog++ sketch e as mescla em um novo esboço.

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 esboço do HyperLogLog++.

Se sketch for NULL, ela 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               |
+------------+---------+-----------------+

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 de constant_integer_expression com base na ordenação de linhas. Ela retorna o número do intervalo baseado 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, ocorrerá um erro.

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 estiver especificada, o resultado será não determinista.

Tipos de argumentos compatíveis

INT64

Funções 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 definidos na entrada expression. No caso de números inteiros, 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:

• Retornam NULL se algum dos parâmetros de entrada é NULL.
• Retornam NaN se algum dos argumentos é 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

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

IEEE_DIVIDE

IEEE_DIVIDE(X, Y)

Descrição

Essa funçã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 ocorrer um estouro do resultado, retornará +/-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. Se X for inferior a 0, será gerado um erro. Caso X seja +inf, a função retornará +inf.

POW

POW(X, Y)

Descrição

Retorna o valor de X elevado à potência de Y. Se o resultado não for representável e ocorrer um estouro negativo, a função retornará o 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 de POW(X, Y) e POWER(X, Y)

Estes são os casos especiais de 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. Caso contrário, +inf
+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. Se ocorrer um estouro do resultado, será gerado um erro. Se X for +/-inf, a função retornará +inf ou 0.

LN

LN(X)

Descrição

Calcula o logaritmo natural de X. Se X for menor ou igual a 0, será gerado um erro. Se X for +inf, a função retornará +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

Retornará NULL, se alguma entrada for NULL. Caso contrário, retornará NaN, se alguma entrada for NaN. Caso contrário, retornará o maior valor entre X1,...,XN de acordo com a comparação <.

LEAST

LEAST(X1,...,XN)

Descrição

Retornará NULL, se alguma entrada for NULL. Retornará NaN, se alguma entrada for NaN. Caso contrário, retornará o menor valor entre X1,...,XN de acordo com a comparação >.

DIV

DIV(X, Y)

Descrição

Retornará o resultado da divisão de inteiro de X por Y. A divisão por zero retorna um erro. Pode ocorrer um estouro na divisão por -1. 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 retornará NULL se ocorrer um erro como o erro de divisão por zero.

MOD

MOD(X, Y)

Descrição

Função de módulo: retorna o restante da divisão de X por Y. O valor retornado tem o mesmo sinal de 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á dígitos à esquerda da vírgula decimal. Casos em que haja metades são arredondados para longe de zero. Se ocorrer um estouro, será gerado um erro.

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. Nunca falha.

COSH

COSH(X)

Descrição

Calcula o cosseno hiperbólico de X. Se ocorrer um estouro, será gerado um erro.

ACOS

ACOS(X)

Descrição

Calcula o valor principal do arco cosseno de X. O valor de retorno está no intervalo [0,]. Se X for um valor finito fora do intervalo [-1, 1], será gerado um erro.

ACOSH

ACOSH(X)

Descrição

Calcula o cosseno hiperbólico inverso de X. Se X for um valor finito menor que 1, será gerado um erro.

SIN

SIN(X)

Descrição

Calcula o seno de X. Nunca falha.

SINH

SINH(X)

Descrição

Calcula o seno hiperbólico de X. Se ocorrer um estouro, será gerado um erro.

ASIN

ASIN(X)

Descrição

Calcula o valor principal do arco seno de X. O valor de retorno está no intervalo [-π/2,π/2]. Se X for um valor finito fora do intervalo [-1, 1], será gerado um erro.

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. Se ocorrer um estouro, será gerado um erro.

TANH

TANH(X)

Descrição

Calcula a tangente hiperbólica de X. Não falha.

ATAN

ATAN(X)

Descrição

Calcula o valor principal do arco tangente de X. O valor de retorno está no intervalo [-π/2,π/2]. Não falha.

ATANH

ATANH(X)

Descrição

Calcula a tangente hiperbólica inversa de X. Se o valor absoluto de X for maior ou igual a 1, será gerado um erro.

ATAN2

ATAN2(Y, X)

Descrição

Calcula o valor principal do arco tangente de Y/X utilizando 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

Funções de navegação

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 de como são 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 da primeira linha no frame da janela atual.

Esta função inclui valores NULL no cálculo, a menos que IGNORE NULLS esteja presente. Se IGNORE NULLS estiver presente, a função excluirá os 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 da última linha no frame da janela atual.

Esta função inclui valores NULL no cálculo, a menos que IGNORE NULLS esteja presente. Se IGNORE NULLS estiver presente, a função excluirá os 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 Nth da moldura da janela atual, na qual Nth é definida por constant_integer_expression. Retorna NULL, se não houver linha.

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

Tipos de argumentos compatíveis

• value_expression pode ser qualquer tipo de dados que pode 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 de offset altera qual linha subsequente é retornada. O valor padrão é 1, indicando a próxima linha no frame da janela. Se offset for NULL ou um valor negativo, ocorrerá um erro.

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

Tipos de argumentos compatíveis

• value_expression pode ser qualquer tipo de dados que pode ser retornado de uma expressão.
• offset precisa ser um literal inteiro não negativo ou parâmetro.
• 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            |
+-----------------+-------------+----------+-----------------+

O exemplo a seguir 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 qual linha precedente é retornada. O valor padrão é 1, indicando a linha anterior no frame da janela. Se offset for NULL ou um valor negativo, ocorrerá um erro.

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

Tipos de argumentos compatíveis

• value_expression pode ser qualquer tipo de dados que pode ser retornado de uma expressão.
• offset precisa ser um literal inteiro não negativo ou parâmetro.
• 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    |
+-----------------+-------------+----------+------------------+

O exemplo a seguir 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.

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

• A interpolação entre os 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 literal DOUBLE no intervalo [0, 1].

Tipo de dados retornados

DOUBLE

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 do percentil especificado para uma value_expression discreta. O valor retornado é o primeiro valor classificado de value_expression com distribuição cumulativa maior ou igual ao valor do percentile dado.

Essa função ignora os valores NULL, a não ser que RESPECT NULLS esteja presente.

Tipos de argumentos compatíveis

• value_expression pode ser qualquer tipo ordenável.
• percentile é um literal DOUBLE 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:

• ANY_VALUE

• ARRAY_AGG

• AVG

• CORR

• COUNT

• COUNTIF

• COVAR_POP

• COVAR_SAMP

• MAX

• MIN

• STDDEV_POP

• STDDEV_SAMP

• STRING_AGG

• SUM

• VAR_POP

• VAR_SAMP

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 FarmHash de código aberto. 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;

+-------------------------------------------------+
| md5                                             |
+-------------------------------------------------+
| \xb1\n\x8d\xb1d\xe0uA\x05\xb7\xa9\x9b\xe7.?\xe5 |
+-------------------------------------------------+

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;

+-----------------------------------------------------------+
| sha1                                                      |
+-----------------------------------------------------------+
| \nMU\xa8\xd7x\xe5\x02/\xabp\x19w\xc5\xd8@\xbb\xc4\x86\xd0 |
+-----------------------------------------------------------+

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 funcionam com dois valores diferentes: tipos de dados STRING e BYTES. Os valores de STRING precisam ser UTF-8 bem formado.

As funções que retornam valores de posição, como STRPOS, codificam essas posições como INT64. O valor de 1 indica o primeiro caractere (ou byte), 2 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 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 de 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

Toma uma matriz de pontos de código 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

Toma uma série de pontos de código Unicode (ARRAY de INT64) e retorna STRING.

Para converter de uma string para 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.

Tipo de retorno

STRING ou BYTES

Exemplos

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

Toma dois valores. 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 o formato das strings. Essa função é semelhante à printf do C. Ela produz uma STRING de uma string de formato que contém zero ou mais especificadores de formato, juntamente com uma lista de comprimentos variáveis de argumentos adicionais que correspondem aos especificadores. Veja alguns exemplos:

Descrição	Declaraçã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+
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() . Por exemplo:

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

Retorna

date: January 02, 2015!

Sintaxe

A sintaxe FORMAT() toma 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 é mapeado para dois argumentos: de comprimento e de 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. As extensões de printf() são identificadas 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	Ponto flutuante decimal, minúsculas	392,65 inf NaN	NUMERIC FLOAT64
F	Ponto flutuante decimal, maiúsculas	392,65 inf NAN	NUMERIC FLOAT64
e	Notação científica (mantissa/expoente), minúsculas	3,9265e+2 inf NaN	NUMERIC FLOAT64
E	Notação científica (mantissa/expoente), maiúsculas	3,9265E+2 inf NAN	NUMERIC FLOAT64
g	Usa a menor representação, %e ou %f	392,65	FLOAT64
G	Usa a menor representação, %E ou %F	392,65	FLOAT64
s	String de caracteres	amostra	STRING
t	Retorna uma string para impressão que representa o valor. Normalmente, é semelhante ao cast do argumento em STRING. Consulte a seção %t abaixo.	amostra 2014‑01‑01	<qualquer>
T	Produz uma string que é uma constante válida do BigQuery, com um tipo semelhante ao do valor (talvez mais amplo ou string). Consulte a seção %T abaixo.	'amostra' b'bytes amostra' 1234 2,3 data '2014‑01‑01'	<qualquer>
%	'%%' produz um único '%'	%	n/d

^*Os especificadores o, x e X gerarão um erro caso sejam usados valores negativos.

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
-	Justificado à esquerda com a largura do campo. O padrão é a justificação à direita (consulte sobre subespecificador de largura)
+	Força a preceder o resultado com um sinal de mais ou menos (+ ou -), incluindo números positivos. Por padrão, apenas os números negativos são precedidos por um sinal -
<espaço>	Se nenhum sinal for gravado, um espaço em branco será inserido antes do valor
#	Usado com os especificadores o, x ou X. Precede valores diferentes de zero com 0, 0x ou 0X, respectivamente
0	Quando o preenchimento é especificado, coloca zeros (0) à esquerda do número, em vez de espaços (consulte sobre subespecificador de largura)
'	Formata inteiros usando o caractere de agrupamento adequado Por 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. As 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 adicional precedendo o argumento que precisa ser formatado

Precisão

Precisão	Descrição
.<número>	Para os especificadores inteiros (d, i, o, u, X, 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 depois do ponto decimal (por padrão, é 6)
.*	A precisão não é especificada na string de formato, mas como um argumento de valor inteiro adicional precedendo o argumento que precisa ser formatado

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: width é a largura mínima e STRING será preenchido com esse tamanho. precision é a largura máxima do conteúdo a ser exibido e a STRING será truncada com esse tamanho, antes de preencher a largura.

%t sempre será uma forma legível do valor.

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

A STRING é formatada como a seguir:

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 <tipo>) CAST("-inf" AS <tipo>) CAST("nan" AS <tipo>)
STRING	valor da string sem aspas	literal da string com aspas
BYTES	bytes com caractere de escape sem aspas por exemplo, abc\x01\x02	literal de bytes 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, ...] no qual os valores são formatados com %t	[valor, valor, ...] no qual os valores são formatados com %T
STRUCT	(valor, valor, ...) no qual os campos são formatados com %t	(valor, valor, ...) no qual os campos são formatados com %T Casos especiais: Campos com zero: STRUCT() Um campo: STRUCT(valor)

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 expressões <format_string> a seguir são inválidas:

FORMAT('%s', 1)

FORMAT('%')

Tratamento do argumento NULL

Uma string de formato NULL resulta em uma STRING de saída 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 uma STRING NULL 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 do literal e CAST), um valor NULL produzirá 'NULL' (sem as aspas) na STRING de resultado. Por exemplo, a função:

FORMAT('00-%t-00', <NULL expression>);

Retorna

00-NULL-00

Regras semânticas adicionais

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), como apropriado. Isto é consistente com a forma como o BigQuery faz o cast desses valores para STRING. Para %T, o BigQuery retorna strings com aspas dos valores FLOAT64 sem representações de literal que não sejam strings.

FROM_BASE32

FROM_BASE32(string_expr)

Descrição

Converte a entrada string_expr codificada como base32 no formato BYTES. Para converter BYTES em uma string codificada em base32, use TO_BASE32().

Tipo de retorno

BYTES

Exemplo

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

+-----------+
| byte_data |
+-----------+
| abcde\xff |
+-----------+

FROM_BASE64

FROM_BASE64(string_expr)

Descrição

Converte a entrada string_expr codificada como base64 para o formato BYTES. Para converter BYTES para uma STRING codificada como base64, use TO_BASE64.

Tipo de retorno

BYTES

Exemplo

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

+------------------+
| byte_data        |
+------------------+
| \xde\xad\xbe\xef |
+------------------+

FROM_HEX

FROM_HEX(string)

Descrição

Converte um STRING codificado em hexadecimal em formato BYTES. Retornará 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. Para converter BYTES para uma STRING codificada como 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;
+------------------+----------------------------------+
| hex_str          | bytes_str                        |
+------------------+----------------------------------+
| 0AF              | \x00\xaf                         |
| 00010203aaeeefff | \x00\x01\x02\x03\xaa\xee\xef\xff |
| 666f6f626172     | foobar                           |
+------------------+----------------------------------+

LENGTH

LENGTH(value)

Descrição

Retorna o comprimento do valor. 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 que consiste em original_value precedido por pattern. O return_length é um INT64 que especifica o comprimento do valor retornado. Se original_value for BYTES, return_length será 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.

O tipo de dados de original_value e pattern precisa ser o mesmo.

Se return_length for menor que ou igual ao comprimento original_value, a 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, a 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	padrão	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	padrão	LPAD
b"abc"	8	b"def"	b"defdeabc"
b"abc"	5	b"-"	b"--abc"
b"\xab\xcd\xef"	5	b"\x00"	b"\x00\x00\xab\xcd\xef"

LOWER

LOWER(value)

Descrição

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

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

Tipo de retorno

STRING ou BYTES

Exemplos

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

SELECT
  LOWER(item) AS example
FROM items;

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

LTRIM

LTRIM(value1[, value2])

Descrição

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

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

Toma uma STRING, value e retorna como uma string normalizada.

A normalização é utilizada para garantir que duas strings sejam equivalentes. Em geral, a normalização é usada em situações em que duas strings renderizam 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

Utiliza uma STRING, value, e realiza as mesmas ações de NORMALIZE, bem como casefold em operações que não diferenciam maiúsculas e minú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, regex)

Descrição

Retorna TRUE se value for uma correspondência parcial da expressão regular regex. Você pode pesquisar uma correspondência total usando ^ (início do texto) e $ (fim do texto).

Se o argumento regex for inválido, a função retorna um erro.

Nota: o BigQuery oferece suporte a expressões regulares usando a biblioteca re2. Consulte a documentação correspondente para conhecer a sintaxe da expressão regular.

Tipo de retorno

BOOL

Exemplos

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

+-----------------+----------+
| email           | is_valid |
+-----------------+----------+
| [e-mail] | true     |
| [e-mail] | true     |
| www.example.net | false    |
+-----------------+----------+

# Performs a full match, using ^ and $.
SELECT
  email,
  REGEXP_CONTAINS(email, r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$")
    AS valid_email_address
FROM
  (SELECT
    ["[e-mail]", "[e-mail]", "www.example.net"]
    AS addresses),
  UNNEST(addresses) AS email;

+-----------------+---------------------+
| email           | valid_email_address |
+-----------------+---------------------+
| [e-mail] | true                |
| [e-mail] | true                |
| www.example.net | false               |
+-----------------+---------------------+

REGEXP_EXTRACT

REGEXP_EXTRACT(value, regex)

Descrição

Retorna a primeira substring em value que corresponde à expressão regular regex. 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 ao grupo de captura. 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.

Nota: o BigQuery oferece suporte a expressões regulares usando a biblioteca re2. Consulte a documentação correspondente para conhecer a sintaxe da expressão regular.

Tipo de retorno

STRING ou BYTES

Exemplos

WITH email_addresses AS
  (SELECT "[e-mail]" as email
  UNION ALL
  SELECT "[e-mail]" as email
  UNION ALL
  SELECT "[e-mail]" 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 "[e-mail]" as email
  UNION ALL
  SELECT "[e-mail]" as email
  UNION ALL
  SELECT "[e-mail]" 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, regex)

Descrição

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

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 somente uma substring, não duas.

Nota: o BigQuery oferece suporte a expressões regulares usando a biblioteca re2. Consulte a documentação correspondente para conhecer a sintaxe da expressão regular.

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, regex, replacement)

Descrição

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

Use dígitos entre barras invertidas (\1 a \9) no argumento replacement para inserir texto correspondente ao grupo entre parênteses no padrão regex. Use \0 para indicar o texto correspondente inteiro.

Nota: insira um caractere de escape para adicionar uma barra invertida na expressão regular. Por exemplo, SELECT REGEXP_REPLACE("abc", "b(.)", "X\\1"); retorna aXc.

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 regex não for uma expressão regular válida, essa função retornará um erro.

Nota: o BigQuery oferece suporte a expressões regulares usando a biblioteca re2. Consulte a documentação correspondente para conhecer a sintaxe da expressão regular.

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á realizada.

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 que consiste em original_value, repetido. O parâmetro repetitions especifica o número de repetições de original_value. Retorna NULL se original_value ou repetitions forem NULL.

Essa função retornará um erro se o valor de 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 STRING ou BYTES da entrada.

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 que consiste em original_value anexado a pattern. O return_length é um INT64 que especifica o comprimento do valor retornado. Se original_value for BYTES, return_length será 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.

O tipo de dados de original_value e pattern precisa ser o mesmo.

Se return_length for menor que ou igual ao comprimento original_value, a 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, a 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	padrão	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	padrão	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 apenas os caracteres à direita.

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 para uma string. Qualquer caractere UTF-8 inválido será substituído pelo caractere 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 em 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 "a b c d" as letter_group
  UNION ALL
  SELECT "e f g h" as letter_group
  UNION ALL
  SELECT "i j k l" as letter_group)

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

+----------------------+
| example              |
+----------------------+
| [a, b, c, d]         |
| [e, f, g, h]         |
| [i, j, k, l]         |
+----------------------+

STARTS_WITH

STARTS_WITH(value1, value2)

Descrição

Toma dois valores. 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 baseado em 1 da primeira ocorrência de substring em string. Retorna 0 se a substring não for encontrada.

Tipo de retorno

INT64

Exemplos

WITH email_addresses AS
  (SELECT
    "[e-mail]" AS email_address
  UNION ALL
  SELECT
    "[e-mail]" AS email_address
  UNION ALL
  SELECT
    "[e-mail]" 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 fornecido. O argumento position é um inteiro. Ele especifica a posição inicial da substring, com a posição = 1 indicando o primeiro caractere ou byte. O argumento length é o número máximo de caracteres de STRING ou de bytes para argumentos BYTES.

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

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

Se length for inferior a 0, 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 uma STRING codificada como base32. Para converter essa STRING 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 para uma STRING codificada como base64. Para converter essa STRING para 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

Toma um valor e retorna uma matriz de INT64.

• Se value for uma STRING, cada elemento na matriz retornada representará um ponto de código. Cada ponto de código fica no intervalo de [0, 0xD7FF] e [0xE000, 0x10FFFF].
• Se value for BYTES, cada elemento da matriz será um valor de caractere ASCII estendido no intervalo de [0, 255].

Para converter de uma matriz de pontos de código em STRING ou 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 uma 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       |
+----------------------------------+------------------+
| foobar                           | 666f6f626172     |
| \x00\x01\x02\x03\xaa\xee\xef\xff | 00010203aaeeefff |
+----------------------------------+------------------+

TRIM

TRIM(value1[, value2])

Descrição

Remove todos os caracteres à direita e à esquerda que correspondem a value2. Se value2 não for especificado, todos os espaços em branco à esquerda e à direita, conforme definido pelo padrão Unicode, serão removidos. Se o primeiro argumento for do tipo BYTES, o segundo argumento será necessário.

Se value2 contiver mais de um caractere ou byte, a função removerá todos os que estiverem à direita ou à esquerda contidos em value2.

Tipo de retorno

STRING ou BYTES

Exemplos

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

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

+----------+
| example  |
+----------+
| #apple#  |
| #banana# |
| #orange# |
+----------+

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

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

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

WITH items AS
  (SELECT "xxxapplexxx" as item
  UNION ALL
  SELECT "yyybananayyy" as item
  UNION ALL
  SELECT "zzzorangezzz" as item
  UNION ALL
  SELECT "xyzpearxyz" as item)

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

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

UPPER

UPPER(value)

Descrição

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

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

Tipo de retorno

STRING ou BYTES

Exemplos

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

SELECT
  UPPER(item) AS example
FROM items;

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

Funções JSON

O 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

O parâmetro json_string_expr precisa ser uma string formatada como JSON. Por exemplo:

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

O parâmetro json_path_string_literal identifica o valor ou os valores que você deseja receber da string formatada como JSON. Crie esse parâmetro usando o formato JSONPath. O parâmetro que é parte desse formato precisa iniciar com o símbolo $, que indica o nível mais externo da string formatada como JSON. Identifique os valores filho usando uma notação de ponto ou colchete. Se o objeto JSON for uma matriz, use colchetes para especificar o índice da matriz.

JSONPath	Descrição
$	Objeto ou elemento raiz
. ou []	Operador filho
[]	Operador subscrito

Ambas as funções retornam NULL, se o parâmetro json_path_string_literal não corresponder a um valor de json_string_expr. Se o valor selecionado de JSON_EXTRACT_SCALAR não for escalar, como um objeto ou matriz, a função retornará NULL.

Se o JSONPath for inválido, essas funções gerarão um erro.

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

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

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

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;

A consulta acima apresenta o seguinte resultado:

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

A consulta acima apresenta o seguinte resultado:

+-----------------+
| 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": "Jamie"}]}}'
  ]) AS json_text;

A consulta acima apresenta o seguinte resultado:

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| {"first":"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;

A consulta acima apresenta o seguinte resultado:

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

A consulta acima apresenta o seguinte resultado:

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

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 é compatível com o 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	Igual a CAST(value AS STRING) quando value está no intervalo de [-2^53, 2^53], que é o intervalo de números inteiros que pode ser representado 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. Por exemplo: -1 0 12345678901 9007199254740992 -9007199254740992 "9007199254740993" 9007199254740993 é maior que 2^53. Dessa forma, ele é representado como uma string entre aspas.
NUMERIC	Igual a CAST(value AS STRING) quando value está no intervalo de [-2^53, 2^53] 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 representados como Infinity, -Infinity e NaN, respectivamente. Caso contrário, é o mesmo que 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. Por exemplo: "R29vZ2xl" é a representação base64 de bytes b"Google"
DATE	Data entre aspas. Por 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. Por 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. Por exemplo: "12:34:56.789012"
ARRAY	[elem1,elem2,...], em que 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 ARRAY ou STRUCT não vazio, os elementos serão recuados no nível apropriado. A estrutura vazia é representada como {}. Os campos com nomes duplicados podem resultar em JSON não analisável. Os campos anônimos são representados com "". Os nomes de campos UTF-8 inválidos podem resultar em JSON não analisável. Os 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 seguinte resultado:

+-----------------------------------+-------------------------------------------------------+
| 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 seguinte resultado:

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

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 a subquery produzir uma tabela SQL padrão, 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 a 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á uma ARRAY que honra essa cláusula.
• Se a subconsulta retornar mais de uma coluna, a função ARRAY retornará um erro.
• Se a subconsulta retornar uma coluna tipada ARRAY ou linhas tipadas ARRAY, a função ARRAY retornará um erro: o BigQuery não aceita ARRAYs com elementos do tipo ARRAY.
• Se a subconsulta retornar zero linhas, a função ARRAY retornará uma ARRAY vazia. Nunca retornará 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 uma ARRAY de uma subconsulta que contenha várias colunas, altere a subconsulta para usar SELECT AS STRUCT. Agora, a função ARRAY retornará uma 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 uma ARRAY de uma subconsulta que contenha um ou mais ARRAYs, altere a subconsulta para usar SELECT AS STRUCT.

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

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

ARRAY_CONCAT

ARRAY_CONCAT(array_expression_1 [, array_expression_n])

Descrição

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

Tipo de retorno

ARRAY

Exemplos

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

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

ARRAY_LENGTH

ARRAY_LENGTH(array_expression)

Descrição

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

Tipo de retorno

INT64

Exemplos

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

SELECT list, ARRAY_LENGTH(list) AS size
FROM items
ORDER BY size DESC;

+---------------------------------+------+
| list                            | size |
+---------------------------------+------+
| [apples, bananas, NULL, grapes] | 4    |
| [coffee, tea, milk]             | 3    |
| [cake, pie]                     | 2    |
+---------------------------------+------+

ARRAY_TO_STRING

ARRAY_TO_STRING(array_expression, delimiter[, null_text])

Descrição

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

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

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

Exemplos

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

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

+--------------------------------+
| text                           |
+--------------------------------+
| apples--bananas--pears--grapes |
| coffee--tea--milk              |
| cake--pie                      |
+--------------------------------+

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

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

+--------------------------------+
| text                           |
+--------------------------------+
| apples--bananas--pears--grapes |
| coffee--tea--milk              |
| cake--pie--MISSING             |
+--------------------------------+

GENERATE_ARRAY

GENERATE_ARRAY(start_expression, end_expression[, step_expression])

Descrição

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

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

• INT64
• NUMERIC
• FLOAT64

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

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

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

Tipo de dados retornados

ARRAY

Exemplos

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

SELECT GENERATE_ARRAY(1, 5) AS example_array;

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

O seguinte retorna uma matriz usando um tamanho de etapa especificada pelo usuário.

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

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

O seguinte retorna uma matriz usando um valor negativo -3 para o tamanho da etapa.

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

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

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

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

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

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

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

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

O seguinte retorna uma matriz NULL porque end_expression é NULL.

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

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

O seguinte retorna várias matrizes.

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

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

GENERATE_DATE_ARRAY

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

Descrição

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

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

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

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

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

Tipo de dados retornados

Um ARRAY contendo 0 ou mais valores DATE.

Exemplos

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

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

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

O seguinte retorna uma matriz usando um tamanho de etapa especificada pelo usuário.

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

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

O seguinte retorna uma matriz usando um valor negativo -3 para o tamanho da etapa.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

GENERATE_TIMESTAMP_ARRAY

GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp,
                         INTERVAL step_expression date_part)

Descrição

Retorna uma ARRAY de TIMESTAMPS separada por um determinado intervalo. Os parâmetros start_timestamp e end_timestamp determinam os limites inferior e superior inclusos do ARRAY.

A função GENERATE_TIMESTAMP_ARRAY aceita os seguintes tipos de dados como entradas:

• start_timestamp: TIMESTAMP
• end_timestamp: TIMESTAMP
• step_expression: INT64
• valores date_part permitidos: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR ou DAY

O parâmetro step_expression determina o incremento usado para gerar carimbos de data/hora.

Tipo de dados retornados

Uma ARRAY contendo 0 ou mais valores de TIMESTAMP.

Exemplos

O exemplo a seguir retorna uma ARRAY de TIMESTAMPs em intervalos de 1 segundo.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-05 00:00:02',
                                INTERVAL 1 SECOND) AS timestamp_array;

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-05 00:00:01+00, 2016-10-05 00:00:02+00] |
+--------------------------------------------------------------------------+

O exemplo a seguir retorna uma ARRAY de TIMESTAMPS com um intervalo negativo.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00', '2016-10-01 00:00:00',
                                INTERVAL -2 DAY) AS timestamp_array;

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-06 00:00:00+00, 2016-10-04 00:00:00+00, 2016-10-02 00:00:00+00] |
+--------------------------------------------------------------------------+

O exemplo a seguir retorna uma ARRAY com um único elemento, porque start_timestamp e end_timestamp têm o mesmo valor.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-05 00:00:00',
                                INTERVAL 1 HOUR) AS timestamp_array;

+--------------------------+
| timestamp_array          |
+--------------------------+
| [2016-10-05 00:00:00+00] |
+--------------------------+

O exemplo a seguir retorna uma ARRAY vazia, porque start_timestamp é posterior a end_timestamp.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00', '2016-10-05 00:00:00',
                                INTERVAL 1 HOUR) AS timestamp_array;

+-----------------+
| timestamp_array |
+-----------------+
| []              |
+-----------------+

O exemplo a seguir retorna uma ARRAY nula, porque uma das entradas é NULL.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', NULL, INTERVAL 1 HOUR)
  AS timestamp_array;

+-----------------+
| timestamp_array |
+-----------------+
| NULL            |
+-----------------+

O exemplo a seguir gera ARRAYs de TIMESTAMPs de colunas que contêm valores para start_timestamp e end_timestamp.

SELECT GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp, INTERVAL 1 HOUR)
  AS timestamp_array
FROM
  (SELECT
    TIMESTAMP '2016-10-05 00:00:00' AS start_timestamp,
    TIMESTAMP '2016-10-05 02:00:00' AS end_timestamp
   UNION ALL
   SELECT
    TIMESTAMP '2016-10-05 12:00:00' AS start_timestamp,
    TIMESTAMP '2016-10-05 14:00:00' AS end_timestamp
   UNION ALL
   SELECT
    TIMESTAMP '2016-10-05 23:59:00' AS start_timestamp,
    TIMESTAMP '2016-10-06 01:59:00' AS end_timestamp);

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-05 01:00:00+00, 2016-10-05 02:00:00+00] |
| [2016-10-05 12:00:00+00, 2016-10-05 13:00:00+00, 2016-10-05 14:00:00+00] |
| [2016-10-05 23:59:00+00, 2016-10-06 00:59:00+00, 2016-10-06 01:59:00+00] |
+--------------------------------------------------------------------------+

OFFSET e ORDINAL

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

Descrição

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

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

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

Tipo de retorno

Varia de acordo com os elementos em ARRAY.

Exemplos

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

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

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

ARRAY_REVERSE

ARRAY_REVERSE(value)

Descrição

Retorna o ARRAY de entrada com elementos na ordem inversa.

Tipo de retorno

ARRAY

Exemplos

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

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

SAFE_OFFSET e SAFE_ORDINAL

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

Descrição

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

Tipo de retorno

Varia de acordo com os elementos em ARRAY.

Exemplo

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

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

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

Funções de data

O BigQuery é compatível com as seguintes funções DATE.

CURRENT_DATE

CURRENT_DATE([time_zone])

Descrição

Retorna a data atual a partir do fuso horário especificado ou padrão.

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

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

Tipo de dados retornados

DATE

Exemplo

SELECT CURRENT_DATE() as the_date;

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

EXTRACT

EXTRACT(part FROM date_expression)

Descrição

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

• DAYOFWEEK: retorna valores no intervalo [1,7] com domingo como primeiro dia da semana.
• DAY
• DAYOFYEAR

• WEEK: retorna o número da semana da data no intervalo [0, 53]. As semanas começam com o domingo, e as datas anteriores ao primeiro domingo do ano estão na semana 0.

• WEEK(<WEEKDAY>): retorna o número da semana da data no intervalo [0, 53]. As semanas começam em WEEKDAY. As datas anteriores ao primeiro WEEKDAY do ano estão na semana 0. Os valores válidos para WEEKDAY são SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY e SATURDAY.

• ISOWEEK: retorna o número da semana ISO 8601 do date_expression. ISOWEEKs começam na segunda-feira. Os valores de retorno estão no intervalo [1, 53]. O primeiro ISOWEEK de cada ano ISO começa na segunda-feira antes da primeira quinta-feira do ano do calendário gregoriano.
• MONTH
• QUARTER: retorna valores no intervalo [1,4].
• YEAR
• ISOYEAR: retorna o ano de numeração da semana ISO 8601, que é o ano do calendário gregoriano que contém a quinta-feira da semana a que date_expression pertence.

Tipo de dados retornados

INT64

Exemplos

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

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

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

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

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

No exemplo a seguir, date_expression cai em um domingo. EXTRACT calcula a primeira coluna usando semanas que começam no domingo e calcula a segunda coluna usando semanas que começam na segunda-feira.

WITH table AS (SELECT DATE('2017-11-05') AS date)
SELECT
  date,
  EXTRACT(WEEK(SUNDAY) FROM date) AS week_sunday,
  EXTRACT(WEEK(MONDAY) FROM date) AS week_monday FROM table;

+------------+-------------+-------------+
| date       | week_sunday | week_monday |
+------------+-------------+-------------+
| 2017-11-05 | 45          | 44          |
+------------+-------------+-------------+

DATE

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

Descrição

1. Cria DATE com os valores INT64 que representam o ano, o mês e o dia.
2. Converte timestamp_expression em um tipo de dados DATE. Aceita um parâmetro opcional para especificar um fuso horário. Se nenhum fuso horário for especificado, será usado o padrão UTC.

Tipo de dados retornados

DATE

Exemplo

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

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

DATE_ADD

DATE_ADD(date_expression, INTERVAL INT64_expr date_part)

Descrição

Adiciona um intervalo de tempo especificado a DATE.

DATE_ADD aceita os seguintes valores date_part:

• DAY
• WEEK: equivalente a 7 DAYs
• MONTH
• QUARTER
• YEAR

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

Tipo de dados retornados

DATE

Exemplo

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

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

DATE_SUB

DATE_SUB(date_expression, INTERVAL INT64_expr date_part)

Descrição

Subtrai um intervalo de tempo especificado de uma DATE.

DATE_SUB aceita os seguintes valores date_part:

• DAY
• WEEK: equivalente a 7 DAYs
• MONTH
• QUARTER
• YEAR

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

Tipo de dados retornados

DATE

Exemplo

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

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

DATE_DIFF

DATE_DIFF(date_expression, date_expression, date_part)

Descrição

Retorna o número de limites de date_part entre as duas date_expressions. Se a primeira data ocorrer antes da segunda data, o resultado não será positivo.

DATE_DIFF aceita os seguintes valores date_part:

• DAY
• WEEK: esta parte da data começa no domingo.
• WEEK(<WEEKDAY>): esta parte da data começa em WEEKDAY. Os valores válidos de WEEKDAY são SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY e SATURDAY.
• ISOWEEK: usa limites da semana ISO 8601. As semanas ISO começam na segunda-feira.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: usa o limite de ano de numeração da semana ISO 8601. O limite do ano ISO é a segunda-feira da primeira semana. A quinta-feira dessa mesma semana pertence ao ano do calendário gregoriano correspondente.

Tipo de dados retornados

INT64

Exemplo

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

+-----------+
| days_diff |
+-----------+
| 559       |
+-----------+

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

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

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

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

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

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

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

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

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

DATE_TRUNC

DATE_TRUNC(date_expression, date_part)

Descrição

Trunca a data com a granularidade especificada.

DATE_TRUNC aceita os seguintes valores de date_part:

• DAY
• WEEK
• WEEK(<WEEKDAY>): trunca date_expression no limite da semana anterior, em que semanas começam em WEEKDAY. Os valores válidos para WEEKDAY são SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY e SATURDAY.
• ISOWEEK: trunca date_expression com o limite da semana ISO 8601 anterior. ISOWEEKs começam na segunda-feira. A primeira ISOWEEK de cada ano ISO contém a primeira quinta-feira do ano do calendário gregoriano correspondente. Qualquer date_expression anterior a esta será truncado com a segunda-feira anterior.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: trunca date_expression no limite de ano de numeração da semana ISO 8601 anterior. O limite do ano ISO é a segunda-feira da primeira semana. A quinta-feira dessa mesma semana pertence ao ano do calendário gregoriano correspondente.

Tipo de dados retornados

DATE

Exemplos

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

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

No exemplo a seguir, a data original cai em um domingo. Como a date_part é WEEK(MONDAY), DATE_TRUNC retorna a DATE para a segunda-feira anterior.

SELECT date AS original, DATE_TRUNC(date, WEEK(MONDAY)) AS truncated
FROM (SELECT DATE('2017-11-05') AS date);

+------------+------------+
| original   | truncated  |
+------------+------------+
| 2017-11-05 | 2017-10-30 |
+------------+------------+

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

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

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

DATE_FROM_UNIX_DATE

DATE_FROM_UNIX_DATE(INT64_expression)

Descrição

Interpreta INT64_expression como sendo o número de dias desde 01/01/1970.

Tipo de dados retornados

DATE

Exemplo

SELECT DATE_FROM_UNIX_DATE(14238) as date_from_epoch;

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

FORMAT_DATE

FORMAT_DATE(format_string, date_expr)

Descrição

Formata date_expr de acordo com a format_string especificada.

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

Tipo de dados retornados

STRING

Exemplo

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

+------------+
| US_format  |
+------------+
| 12/25/08   |
+------------+

PARSE_DATE

PARSE_DATE(format_string, date_string)

Descrição

Usa format_string e uma representação de string de uma data para retornar um objeto DATE.

Ao usar PARSE_DATE, tenha em mente os seguintes itens:

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

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

Tipo de dados retornados

DATE

Exemplo

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

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

UNIX_DATE

UNIX_DATE(date_expression)

Descrição

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

Tipo de dados retornados

INT64

Exemplo

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

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

Elementos de formatos compatíveis com DATE

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

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

Funções DateTime

O BigQuery é compatível com as seguintes funções DATETIME.

CURRENT_DATETIME

CURRENT_DATETIME([timezone])

Descrição

Retorna a hora atual como um objeto DATETIME.

Essa função é compatível com o parâmetro timezone opcional. Para informações sobre como especificar um fuso horário, consulte Definições de fuso horário.

Tipo de dados retornados

DATETIME

Exemplo

SELECT CURRENT_DATETIME() as now;

+----------------------------+
| now                        |
+----------------------------+
| 2016-05-19 10:38:47.046465 |
+----------------------------+

DATETIME

1. DATETIME(year, month, day, hour, minute, second)
2. DATETIME(date_expression, time_expression)
3. DATETIME(timestamp_expression [, timezone])

Descrição

1. Cria um objeto DATETIME usando valores INT64 que representam o ano, mês, dia, hora, minuto e segundo.
2. Cria um objeto DATETIME usando um objetos DATE e TIME.
3. Cria um objeto DATETIME usando um objeto TIMESTAMP. Aceita um parâmetro opcional para especificar um fuso horário. Se nenhum fuso horário for especificado, será usado o padrão UTC.

Tipo de dados retornados

DATETIME

Exemplo

SELECT
  DATETIME(2008, 12, 25, 05, 30, 00) as datetime_ymdhms,
  DATETIME(TIMESTAMP "2008-12-25 05:30:00+00", "America/Los_Angeles") as datetime_tstz;

+---------------------+---------------------+
| datetime_ymdhms     | datetime_tstz       |
+---------------------+---------------------+
| 2008-12-25 05:30:00 | 2008-12-24 21:30:00 |
+---------------------+---------------------+

DATETIME_ADD

DATETIME_ADD(datetime_expression, INTERVAL INT64_expr part)

Descrição

Adiciona unidades INT64_expr de part ao objeto DATETIME.

DATETIME_ADD aceita os seguintes valores de part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK. Equivalente a 7 DAYs.
• MONTH
• QUARTER
• YEAR

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

Tipo de dados retornados

DATETIME

Exemplo

SELECT
  DATETIME "2008-12-25 15:30:00" as original_date,
  DATETIME_ADD(DATETIME "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as later;

+-----------------------------+------------------------+
| original_date               | later                  |
+-----------------------------+------------------------+
| 2008-12-25 15:30:00         | 2008-12-25 15:40:00    |
+-----------------------------+------------------------+

DATETIME_SUB

DATETIME_SUB(datetime_expression, INTERVAL INT64_expr part)

Descrição

Subtrai unidades INT64_expr de part do DATETIME.

DATETIME_SUB aceita os seguintes valores de part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK. Equivalente a 7 DAYs.
• MONTH
• QUARTER
• YEAR

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

Tipo de dados retornados

DATETIME

Exemplo

SELECT
  DATETIME "2008-12-25 15:30:00" as original_date,
  DATETIME_SUB(DATETIME "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as earlier;

+-----------------------------+------------------------+
| original_date               | earlier                |
+-----------------------------+------------------------+
| 2008-12-25 15:30:00         | 2008-12-25 15:20:00    |
+-----------------------------+------------------------+

DATETIME_DIFF

DATETIME_DIFF(datetime_expression, datetime_expression, part)

Descrição

Retorna o número de limites de part entre as duas datetime_expressions. Se a primeira DATETIME ocorrer antes da segunda DATETIME, o resultado não será positivo. Lança um erro se o cálculo estoura o tipo de resultado, como se a diferença em microssegundos entre os dois objetos DATETIME estourasse um valor INT64.

DATETIME_DIFF aceita os seguintes valores de part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK: esta parte da data começa no domingo.
• WEEK(<WEEKDAY>): esta parte da data começa em WEEKDAY. Os valores válidos de WEEKDAY são SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY e SATURDAY.
• ISOWEEK: usa limites da semana ISO 8601. As semanas ISO começam na segunda-feira.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: usa o limite de ano de numeração da semana ISO 8601. O limite do ano ISO é a segunda-feira da primeira semana. A quinta-feira dessa mesma semana pertence ao ano do calendário gregoriano correspondente.

Tipo de dados retornados

INT64

Exemplo

SELECT
  DATETIME "2010-07-07 10:20:00" as first_datetime,
  DATETIME "2008-12-25 15:30:00" as second_datetime,
  DATETIME_DIFF(DATETIME "2010-07-07 10:20:00",
    DATETIME "2008-12-25 15:30:00", DAY) as difference;

+----------------------------+------------------------+------------------------+
| first_datetime             | second_datetime        | difference             |
+----------------------------+------------------------+------------------------+
| 2010-07-07 10:20:00        | 2008-12-25 15:30:00    | 559                    |
+----------------------------+------------------------+------------------------+

SELECT
  DATETIME_DIFF(DATETIME '2017-10-15 00:00:00',
    DATETIME '2017-10-14 00:00:00', DAY) as days_diff,
  DATETIME_DIFF(DATETIME '2017-10-15 00:00:00',
    DATETIME '2017-10-14 00:00:00', WEEK) as weeks_diff;

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

O exemplo acima mostra o resultado de DATETIME_DIFF para dois DATETIMEs que estão separados por 24 horas. DATETIME_DIFF com a parte WEEK retorna 1 porque DATETIME_DIFF conta o número de limites das partes nesse intervalo de DATETIMEs. Cada WEEK começa no domingo, então há um limite de parte entre sábado, 2017-10-14 00:00:00 e domingo, 2017-10-15 00:00:00.

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

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

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

O exemplo a seguir mostra o resultado de DATETIME_DIFF para dois dias consecutivos. A primeira data cai numa segunda-feira e a segunda data cai em um domingo. DATETIME_DIFF com a parte da data WEEK retorna 0 porque esta parte do tempo usa semanas que começam no domingo. DATETIME_DIFF com a parte da data WEEK(MONDAY) retorna 1. DATETIME_DIFF com a parte da data ISOWEEK também retorna 1 porque as semanas ISO começam na segunda-feira.

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

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

DATETIME_TRUNC

DATETIME_TRUNC(datetime_expression, part)

Descrição

Trunca um objeto DATETIME para a granularidade de part.

DATETIME_TRUNC aceita os seguintes valores de part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• WEEK(<WEEKDAY>): trunca datetime_expression para o limite da semana anterior, em que as semanas começam em WEEKDAY. Os valores válidos para WEEKDAY são SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY e SATURDAY.
• ISOWEEK: trunca datetime_expression no limite da semana ISO 8601 anterior. ISOWEEKs começam na segunda-feira. A primeira ISOWEEK de cada ano ISO contém a primeira quinta-feira do ano do calendário gregoriano correspondente. Qualquer date_expression anterior a esta truncará para a segunda-feira anterior.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: trunca datetime_expression no limite de ano de numeração da semana ISO 8601 anterior. O limite do ano ISO é a segunda-feira da primeira semana. A quinta-feira dessa mesma semana pertence ao ano do calendário gregoriano correspondente.

Tipo de dados retornados

DATETIME

Exemplos

SELECT
  DATETIME "2008-12-25 15:30:00" as original,
  DATETIME_TRUNC(DATETIME "2008-12-25 15:30:00", DAY) as truncated;

+----------------------------+------------------------+
| original                   | truncated              |
+----------------------------+------------------------+
| 2008-12-25 15:30:00        | 2008-12-25 00:00:00    |
+----------------------------+------------------------+

No exemplo a seguir, o DATETIME original cai em um domingo. Como part é WEEK(MONDAY), DATE_TRUNC retorna o DATETIME para a segunda-feira anterior.

SELECT
 datetime AS original,
 DATETIME_TRUNC(datetime, WEEK(MONDAY)) AS truncated
FROM (SELECT DATETIME(TIMESTAMP '2017-11-05 00:00:00') AS datetime);

+---------------------+---------------------+
| original            | truncated           |
+---------------------+---------------------+
| 2017-11-05 00:00:00 | 2017-10-30 00:00:00 |
+---------------------+---------------------+

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

SELECT
  DATETIME_TRUNC('2015-06-15 00:00:00', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM DATETIME '2015-06-15 00:00:00') AS isoyear_number;

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

FORMAT_DATETIME

FORMAT_DATETIME(format_string, datetime_expression)

Descrição

Formata um objeto DATETIME de acordo com o format_string especificado. Consulte Elementos de formatos compatíveis de DATETIME para ver uma lista de elementos de formato aceitos por essa função.

Tipo de dados retornados

STRING

Exemplo

SELECT
  FORMAT_DATETIME("%c", DATETIME "2008-12-25 15:30:00")
  AS formatted;

PARSE_DATETIME

PARSE_DATETIME(format_string, string)

Descrição

Usa format_string e uma representação de string de timestamp para retornar um objeto TIMESTAMP. Consulte Elementos de formatos compatíveis de DATETIME para ver uma lista de elementos de formato aceitos por essa função.

Quando usar PARSE_DATETIME, tenha em mente os seguintes itens:

• Campos não especificados. Qualquer campo não especificado é inicializado em 1970-01-01 00:00:00.0. Por exemplo, se o ano não for especificado, o padrão será 1970 e assim por diante.
• Nomes que não diferenciam maiúsculas e minúsculas. Nomes, como Monday, February e outros, não diferenciam maiúsculas de minúsculas.
• Espaço em branco. Um ou mais espaços em branco consecutivos na string de formato correspondem a zero ou mais espaços em branco consecutivos na string DATETIME. Além disso, espaços em branco à esquerda e à direita na string DATETIME sempre são permitidos, mesmo que não estejam na string de formato.
• Precedência de formato. Quando dois ou mais elementos de formato têm informações de sobreposição (por exemplo, %F e %Y afetam o ano), o último geralmente modifica os anteriores, com algumas exceções. Consulte as descrições de %s, %C e %y.

Tipo de dados retornados

DATETIME

Elementos de formatos compatíveis de DATETIME

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

Elemento de formato	Descrição
%A	O nome completo do dia da semana.
%a	O nome abreviado do dia da semana.
%B	O nome completo do mês.
%b ou %h	O nome abreviado do mês.
%C	O século (um ano dividido por 100 e truncado para um inteiro) como número decimal (00-99).
%c	A representação de data e hora.
%D	A data no formato %m/%d/%y.
%d	O dia do mês como número decimal (01-31).
%e	O dia do mês como número decimal (1-31). Dígitos únicos são precedidos por um espaço.
%F	A data no formato %Y-%m-%d.
%G	O ano ISO 8601 com o século como número decimal.
%g	O ano ISO 8601 sem o século como número decimal (00-99).
%H	A hora em um relógio de 24 horas como número decimal (00-23).
%I	A hora em um relógio de 12 horas como número decimal (01-12).
%j	O dia do ano como número decimal (001-366).
%k	A hora em um relógio de 24 horas como número decimal (0-23). Dígitos únicos são precedidos por um espaço.
%l	A hora em um relógio de 12 horas como número decimal (1-12). Dígitos únicos são precedidos por um espaço.
%M	O minuto como número decimal (00-59).
%m	O mês como número decimal (01-12).
%n	Um caractere de nova linha.
%P	am ou pm
%p	AM ou PM.
%R	A hora no formato %H:%M.
%r	A hora em um relógio de 12 horas usando a notação AM/PM.
%S	O segundo como número decimal (00-60).
%s	O número de segundos desde 1970-01-01 00:00:00. Sempre modifica todos os outros elementos de formato, independentemente de onde %s aparece na string. Se vários elementos %s aparecerem, o último terá precedência.
%T	A hora no formato %H:%M:%S.
%t	Um caractere de tabulação.
%U	O número da semana do ano (domingo como o primeiro dia da semana) como número decimal (00-53).
%u	O dia da semana (segunda-feira como o primeiro dia da semana) como número decimal (1-7).
%V	O número da semana do ano (segunda-feira como o primeiro dia da semana) como número decimal (01-53). Se a semana que tem 1 de janeiro tiver quatro ou mais dias no ano novo, então será a semana 1. Caso contrário, será a semana 53 do ano anterior e a semana seguinte será a semana 1.
%W	O número da semana do ano (segunda-feira como o primeiro dia da semana) como número decimal (00-53).
%w	O dia da semana (domingo como o primeiro dia da semana) como número decimal (0-6).
%X	A representação da hora no formato HH:MM:SS.
%x	A representação de data no formato MM/DD/YY.
%Y	O ano com o século como número decimal.
%y	O ano sem o século como número decimal (00-99), com um zero opcional à esquerda. Pode ser combinado com %C. Se %C não for especificado, os anos 00-68 serão 2000s e os anos 69-99 serão 1900s.
%%	Um único caractere %.
%E#S	Segundos com # dígitos de precisão fracionária.
%E*S	Segundos com precisão fracionária total (um literal '*').
%E4Y	Anos com quatro caracteres (0001 ... 9999). %Y produz quantos caracteres forem necessários para processar o ano totalmente.

Funções de hora

O BigQuery é compatível com as seguintes funções TIME.

CURRENT_TIME

CURRENT_TIME()

Descrição

Retorna a hora atual como um objeto de TIME.

Tipo de dados retornados

TIME

Exemplo

SELECT CURRENT_TIME() as now;

+----------------------------+
| now                        |
+----------------------------+
| 15:31:38.776361            |
+----------------------------+

TIME

1. TIME(hour, minute, second)
2. TIME(timestamp, [timezone])
3. TIME(datetime)

Descrição

1. Cria um objeto TIME usando valores INT64 que representam a hora, o minuto e o segundo.
2. Cria um objeto TIME usando um objeto TIMESTAMP. Aceita um parâmetro opcional para especificar um fuso horário. Se nenhum fuso horário for especificado, será usado o padrão UTC.
3. Cria um objeto TIME usando um objeto DATETIME.

Tipo de dados retornados

TIME

Exemplo

SELECT
  TIME(15, 30, 00) as time_hms,
  TIME(TIMESTAMP "2008-12-25 15:30:00+08", "America/Los_Angeles") as time_tstz;
+----------+-----------+
| time_hms | time_tstz |
+----------+-----------+
| 15:30:00 | 23:30:00  |
+----------+-----------+

SELECT
  TIME(DATETIME "2008-12-25 15:30:00.000000") AS time_dt;
+----------+
| time_dt  |
+----------+
| 15:30:00 |
+----------+

TIME_ADD

TIME_ADD(time_expression, INTERVAL INT64_expr part)

Descrição

Adiciona unidades INT64_expr de part ao objeto TIME.

TIME_ADD aceita os seguintes valores de part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR

Essa função ajusta automaticamente os valores quando recaem fora do limite de 00:00:00 a 24:00:00. Por exemplo, se você adicionar uma hora a 23:30:00, o valor retornado será 00:30:00 .

Tipos de dados retornados

TIME

Exemplo

SELECT
  TIME "15:30:00" as original_time,
  TIME_ADD(TIME "15:30:00", INTERVAL 10 MINUTE) as later;

+-----------------------------+------------------------+
| original_time               | later                  |
+-----------------------------+------------------------+
| 15:30:00                    | 15:40:00               |
+-----------------------------+------------------------+

TIME_SUB

TIME_SUB(time_expression, INTERVAL INT_expr part)

Descrição

Subtrai as unidades INT64_expr de part do objeto TIME.

TIME_SUB aceita os seguintes valores de part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR

Essa função ajusta automaticamente os valores quando recaem fora do limite de 00:00:00 a 24:00:00. Por exemplo, se você subtrair uma hora de 00:30:00, o valor retornado será 23:30:00 .

Tipo de dados retornados

TIME

Exemplo

SELECT
  TIME "15:30:00" as original_date,
  TIME_SUB(TIME "15:30:00", INTERVAL 10 MINUTE) as earlier;

+-----------------------------+------------------------+
| original_date                | earlier                |
+-----------------------------+------------------------+
| 15:30:00                    | 15:20:00               |
+-----------------------------+------------------------+

TIME_DIFF

TIME_DIFF(time_expression, time_expression, part)

Descrição

Retorna o número de intervalos part inteiros especificados entre dois objetos TIME. Lança um erro se o cálculo estourar o tipo de resultado, como se a diferença em microssegundos entre os dois objetos de tempo estourasse um valor INT64.

TIME_DIFF aceita os seguintes valores de part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR

Tipo de dados retornados

INT64

Exemplo

SELECT
  TIME "15:30:00" as first_time,
  TIME "14:35:00" as second_time,
  TIME_DIFF(TIME "15:30:00", TIME "14:35:00", MINUTE) as difference;

+----------------------------+------------------------+------------------------+
| first_time                 | second_time            | difference             |
+----------------------------+------------------------+------------------------+
| 15:30:00                   | 14:35:00               | 55                     |
+----------------------------+------------------------+------------------------+

TIME_TRUNC

TIME_TRUNC(time_expression, part)

Descrição

Trunca um objeto TIME para a granularidade de part.

TIME_TRUNC aceita os seguintes valores de part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR

Tipo de dados retornados

TIME

Exemplo

SELECT
  TIME "15:30:00" as original,
  TIME_TRUNC(TIME "15:30:00", HOUR) as truncated;

+----------------------------+------------------------+
| original                   | truncated              |
+----------------------------+------------------------+
| 15:30:00                   | 15:00:00               |
+----------------------------+------------------------+

FORMAT_TIME

FORMAT_TIME(format_string, time_object)

Descrição Formata um objeto TIME de acordo com o format_string especificado. Consulte Elementos de formatos compatíveis de TIME para ver uma lista de elementos de formato aceitos por essa função.

Tipo de dados retornados

STRING

Exemplo

SELECT FORMAT_TIME("%R", TIME "15:30:00") as formatted_time;

+----------------+
| formatted_time |
+----------------+
| 15:30          |
+----------------+

PARSE_TIME

PARSE_TIME(format_string, string)

Descrição

Usa um format_string e uma string para retornar um objeto TIME. Consulte Elementos de formatos compatíveis de TIME para ver uma lista de elementos de formato aceitos por essa função.

Ao usar PARSE_TIME, tenha em mente os seguintes itens:

• Campos não especificados. Qualquer campo não especificado é inicializado em 00:00:00.0. Por exemplo, se seconds não for especificado, o padrão será 00 e assim por diante.
• Espaço em branco. Um ou mais espaços em branco consecutivos na string de formato correspondem a zero ou mais espaços em branco consecutivos na string TIME. Além disso, espaços em branco à esquerda e à direita na string TIME sempre são permitidos, mesmo que não estejam na string de formato.
• Precedência de formato. Quando dois ou mais elementos de formato têm informações de sobreposição, o último geralmente modifica os anteriores.

Tipo de dados retornados

TIME

Exemplo

SELECT PARSE_TIME("%H", "15") as parsed_time;

+-------------+
| parsed_time |
+-------------+
| 15:00:00    |
+-------------+

Elementos de formatos compatíveis de TIME

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

Elemento de formato	Descrição
%H	A hora em um relógio de 24 horas como número decimal (00-23).
%I	A hora em um relógio de 12 horas como número decimal (01-12).
%j	O dia do ano como número decimal (001-366).
%k	A hora em um relógio de 24 horas como número decimal (0-23). Dígitos únicos são precedidos por um espaço.
%l	A hora em um relógio de 12 horas como número decimal (1-12). Dígitos únicos são precedidos por um espaço.
%M	O minuto como número decimal (00-59).
%n	Um caractere de nova linha.
%P	am ou pm
%p	AM ou PM.
%R	A hora no formato %H:%M.
%r	A hora em um relógio de 12 horas usando a notação AM/PM.
%S	O segundo como número decimal (00-60).
%T	A hora no formato %H:%M:%S.
%t	Um caractere de tabulação.
%X	A representação da hora no formato HH:MM:SS.
%%	Um único caractere %.
%E#S	Segundos com # dígitos de precisão fracionária.
%E*S	Segundos com precisão fracionária total (um literal '*').

Funções de carimbo de data e hora

O BigQuery é compatível com as seguintes funções TIMESTAMP.

NOTA: essas funções retornam um erro de execução, se ocorrer um estouro. Os valores dos resultados são limitados até os valores mínimo e máximo da data e do timestamp definidos.

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

Descrição

O uso de parênteses é opcional. Essa função trata os segundos bissextos distribuindo-os em um intervalo de 20 horas em torno do ajuste inserido. CURRENT_TIMESTAMP() produz um valor TIMESTAMP contínuo, não ambíguo, tem exatamente 60 segundos por minuto e não repete os valores em um segundo bissexto.

Tipos de entradas compatíveis

Não aplicável

Tipo de dados de resultado

TIMESTAMP

Exemplo

SELECT CURRENT_TIMESTAMP() as now;

+-------------------------------+
| now                           |
+-------------------------------+
| 2016-05-16 18:12:47.145482+00 |
+-------------------------------+

EXTRACT

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

Descrição

Retorna um valor INT64 correspondente a part especificado de uma timestamp_expression fornecida.

Os valores de part permitidos são:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAYOFWEEK
• DAY
• DAYOFYEAR

• WEEK: retorna o número da semana da data no intervalo [0, 53]. As semanas começam com o domingo, e as datas anteriores ao primeiro domingo do ano estão na semana 0.

• WEEK(<WEEKDAY>): retorna o número da semana do timestamp_expression no intervalo [0, 53]. As semanas começam em WEEKDAY. datetimes anteriores ao primeiro WEEKDAY do ano estão na semana 0. Os valores válidos para WEEKDAY são SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY e SATURDAY.

• ISOWEEK: retorna o número da semana ISO 8601 do datetime_expression. ISOWEEKs começam na segunda-feira. Os valores de retorno estão no intervalo [1, 53]. O primeiro ISOWEEK de cada ano ISO começa na segunda-feira antes da primeira quinta-feira do ano do calendário gregoriano.

• MONTH
• QUARTER
• YEAR
• ISOYEAR: retorna o ano de numeração da semana ISO 8601, que é o ano do calendário gregoriano que contém a quinta-feira da semana a que date_expression pertence.
• DATE
• DATETIME
• TIME

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

Para informações sobre como especificar um fuso horário, consulte Definições de fuso horário.

Tipo de dados retornados

Geralmente, INT64 Retorna DATE se part for DATE.

Exemplos

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

SELECT EXTRACT(DAY
  FROM TIMESTAMP "2008-12-25 15:30:00" AT TIME ZONE "America/Los_Angeles")
  AS the_day;

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

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

WITH Timestamps AS (
  SELECT TIMESTAMP '2005-01-03 12:34:56' AS timestamp UNION ALL
  SELECT TIMESTAMP '2007-12-31' UNION ALL
  SELECT TIMESTAMP '2009-01-01' UNION ALL
  SELECT TIMESTAMP '2009-12-31' UNION ALL
  SELECT TIMESTAMP '2017-01-02' UNION ALL
  SELECT TIMESTAMP '2017-05-26'
)
SELECT
  timestamp,
  EXTRACT(ISOYEAR FROM timestamp) AS isoyear,
  EXTRACT(ISOWEEK FROM timestamp) AS isoweek,
  EXTRACT(YEAR FROM timestamp) AS year,
  EXTRACT(WEEK FROM timestamp) AS week
FROM Timestamps
ORDER BY timestamp;
+------------------------+---------+---------+------+------+
| timestamp              | isoyear | isoweek | year | week |
+------------------------+---------+---------+------+------+
| 2005-01-03 12:34:56+00 | 2005    | 1       | 2005 | 1    |
| 2007-12-31 00:00:00+00 | 2008    | 1       | 2007 | 52   |
| 2009-01-01 00:00:00+00 | 2009    | 1       | 2009 | 0    |
| 2009-12-31 00:00:00+00 | 2009    | 53      | 2009 | 52   |
| 2017-01-02 00:00:00+00 | 2017    | 1       | 2017 | 1    |
| 2017-05-26 00:00:00+00 | 2017    | 21      | 2017 | 21   |
+------------------------+---------+---------+------+------+

No exemplo a seguir, timestamp_expression cai em um domingo. EXTRACT calcula a primeira coluna usando semanas que começam no domingo e calcula a segunda coluna usando semanas que começam na segunda-feira.

WITH table AS (SELECT TIMESTAMP('2017-11-05 00:00:00') AS timestamp)
SELECT
  timestamp,
  EXTRACT(WEEK(SUNDAY) FROM timestamp) AS week_sunday,
  EXTRACT(WEEK(MONDAY) FROM timestamp) AS week_monday
FROM table;

+------------------------+-------------+---------------+
| timestamp              | week_sunday | week_monday |
+------------------------+-------------+---------------+
| 2017-11-05 00:00:00+00 | 45          | 44            |
+------------------------+-------------+---------------+

STRING

STRING(timestamp_expression[, timezone])

Descrição

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

Tipo de dados retornados

STRING

Exemplo

SELECT STRING(TIMESTAMP "2008-12-25 15:30:00", "America/Los_Angeles") as string;

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

TIMESTAMP

1. TIMESTAMP(string_expression[, timezone])
2. TIMESTAMP(date_expression[, timezone])
3. TIMESTAMP(datetime_expression[, timezone])

Descrição

1. Converte uma expressão STRING em um tipo de dados TIMESTAMP.

2. Converte um objeto DATA para um tipo de dados TIMESTAMP.

3. Converte um objeto DATETIME para um tipo de dados TIMESTAMP.

Essa função aceita um parâmetro opcional para especificar um fuso horário. Se nenhum fuso horário for especificado, será usado o padrão UTC.

Tipo de dados retornados

TIMESTAMP

Exemplo

SELECT
  CAST(TIMESTAMP("2008-12-25 15:30:00", "America/Los_Angeles") AS STRING) AS timestamp_str,
  CAST(TIMESTAMP(DATE "2008-12-25", "America/Los_Angeles") AS STRING) AS timestamp_date,
  CAST(TIMESTAMP(DATETIME "2008-12-25 15:30:00", "America/Los_Angeles") AS STRING) AS timestamp_datetime;

+------------------------+------------------------+------------------------+
| timestamp_str          | timestamp_date         | timestamp_datetime     |
+------------------------+------------------------+------------------------+
| 2008-12-25 23:30:00+00 | 2008-12-25 08:00:00+00 | 2008-12-25 23:30:00+00 |
+------------------------+------------------------+------------------------+

TIMESTAMP_ADD

TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression date_part)

Descrição

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

TIMESTAMP_ADD aceita os seguintes valores de date_part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR: equivalente a 60 MINUTEs

Tipos de dados retornados

TIMESTAMP

Exemplo

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

+------------------------+------------------------+
| original               | later                  |
+------------------------+------------------------+
| 2008-12-25 15:30:00+00 | 2008-12-25 15:40:00+00 |
+------------------------+------------------------+

TIMESTAMP_SUB

TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression date_part)

Descrição

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

TIMESTAMP_SUB aceita os seguintes valores de date_part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR: equivalente a 60 MINUTEs

Tipo de dados retornados

TIMESTAMP

Exemplo

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

+------------------------+------------------------+
| original               | earlier                |
+------------------------+------------------------+
| 2008-12-25 15:30:00+00 | 2008-12-25 15:20:00+00 |
+------------------------+------------------------+

TIMESTAMP_DIFF

TIMESTAMP_DIFF(timestamp_expression, timestamp_expression, date_part)

Descrição

Retorna o número de intervalos date_part inteiros especificados entre dois carimbos de data/hora. O primeiro timestamp_expression representa a data posterior. Se o primeiro timestamp_expression for anterior ao segundo timestamp_expression, a saída será negativa. Ocorrerá um erro se o cálculo estourar o tipo de resultado, como se a diferença em microssegundos entre os dois carimbos de data/hora estourasse um valor INT64.

TIMESTAMP_DIFF aceita os seguintes valores de date_part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR: equivalente a 60 MINUTEs

Tipo de dados retornados

INT64

Exemplo

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

+------------------------+------------------------+-------+
| later_timestamp        | earlier_timestamp      | hours |
+------------------------+------------------------+-------+
| 2010-07-07 10:20:00+00 | 2008-12-25 15:30:00+00 | 13410 |
+------------------------+------------------------+-------+

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

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

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

TIMESTAMP_TRUNC

TIMESTAMP_TRUNC(timestamp_expression, date_part[, time_zone])

Descrição

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

TIMESTAMP_TRUNC aceita os seguintes valores de date_part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• WEEK(<WEEKDAY>): trunca timestamp_expression para o limite da semana anterior, onde as semanas começam em WEEKDAY. Os valores válidos para WEEKDAY são SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY e SATURDAY.
• ISOWEEK: trunca timestamp_expression no limite da semana ISO 8601 anterior. ISOWEEKs começam na segunda-feira. A primeira ISOWEEK de cada ano ISO contém a primeira quinta-feira do ano do calendário gregoriano correspondente. Qualquer date_expression anterior a esta truncará para a segunda-feira anterior.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: trunca timestamp_expression no limite de ano de numeração da semana ISO 8601 anterior. O limite do ano ISO é a segunda-feira da primeira semana. A quinta-feira dessa mesma semana pertence ao ano do calendário gregoriano correspondente.

A função TIMESTAMP_TRUNC aceita um parâmetro time_zone opcional. Este parâmetro aplica-se às seguintes date_parts:

• MINUTE
• HOUR
• DAY
• WEEK
• WEEK(<WEEKDAY>)
• MONTH
• QUARTER
• YEAR

Use esse parâmetro para um fuso horário diferente do UTC padrão, como parte da operação de truncamento.

Tipo de dados retornados

TIMESTAMP

Exemplos

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

+------------------------+------------------------+
| utc                    | la                     |
+------------------------+------------------------+
| 2008-12-25 00:00:00+00 | 2008-12-25 08:00:00+00 |
+------------------------+------------------------+

No exemplo a seguir, timestamp_expression tem um deslocamento de fuso horário de +12. A primeira coluna mostra a timestamp_expression em UTC. A segunda coluna mostra a saída de TIMESTAMP_TRUNC usando semanas que começam na segunda-feira. Como a timestamp_expression cai em um domingo em UTC, TIMESTAMP_TRUNC trunca para a segunda-feira anterior. A terceira coluna mostra a mesma função com o argumento de Definição de fuso horário opcional 'Pacific/Auckland'. Aqui a função trunca timestamp_expression usando o horário de verão da Nova Zelândia, caindo em uma segunda-feira.

SELECT
  timestamp,
  TIMESTAMP_TRUNC(timestamp, WEEK(MONDAY)) AS utc_truncated,
  TIMESTAMP_TRUNC(timestamp, WEEK(MONDAY), 'Pacific/Auckland') AS nzdt_truncated
FROM (SELECT TIMESTAMP('2017-11-06 00:00:00+12') AS timestamp);

+------------------------+------------------------+------------------------+
| timestamp              | utc_truncated          | nzdt_truncated         |
+------------------------+------------------------+------------------------+
| 2017-11-05 12:00:00+00 | 2017-10-30 07:00:00+00 | 2017-11-05 11:00:00+00 |
+------------------------+------------------------+------------------------+

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

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

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

FORMAT_TIMESTAMP

FORMAT_TIMESTAMP(format_string, timestamp[, time_zone])

Descrição

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

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

Tipo de dados retornados

STRING

Exemplo

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

+--------------------------+
| formatted                |
+--------------------------+
| Thu Dec 25 07:30:00 2008 |
+--------------------------+

PARSE_TIMESTAMP

PARSE_TIMESTAMP(format_string, string[, time_zone])

Descrição

Usa format_string e uma representação de string de timestamp para retornar um objeto TIMESTAMP.

Ao usar PARSE_TIMESTAMP, tenha em mente os seguintes itens:

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

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

Tipo de dados retornados

TIMESTAMP

Exemplo

SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008", "America/Los_Angeles") as parsed;

+-------------------------+
| parsed                  |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

TIMESTAMP_SECONDS

TIMESTAMP_SECONDS(int64_expression)

Descrição

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

Tipo de dados retornados

TIMESTAMP

Exemplo

SELECT TIMESTAMP_SECONDS(1230219000) as timestamp;

+-------------------------+
| timestamp               |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

TIMESTAMP_MILLIS

TIMESTAMP_MILLIS(int64_expression)

Descrição

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

Tipo de dados retornados

TIMESTAMP

Exemplo

SELECT TIMESTAMP_MILLIS(1230219000000) as timestamp;

+-------------------------+
| timestamp               |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

TIMESTAMP_MICROS

TIMESTAMP_MICROS(int64_expression)

Descrição

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

Tipo de dados retornados

TIMESTAMP

Exemplo

SELECT TIMESTAMP_MICROS(1230219000000000) as timestamp;

+-------------------------+
| timestamp               |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

UNIX_SECONDS

UNIX_SECONDS(timestamp_expression)

Descrição

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

Tipo de dados retornados

INT64

Exemplo

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

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

UNIX_MILLIS

UNIX_MILLIS(timestamp_expression)

Descrição

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

Tipo de dados retornados

INT64

Exemplo

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

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

UNIX_MICROS

UNIX_MICROS(timestamp_expression)

Descrição

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

Tipo de dados retornados

INT64

Exemplo

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

+------------------+
| micros           |
+------------------+
| 1230219000000000 |
+------------------+

Elementos de formatos compatíveis de TIMESTAMP

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

Elemento de formato	Descrição
%A	O nome completo do dia da semana.
%a	O nome abreviado do dia da semana.
%B	O nome completo do mês.
%b ou %h	O nome abreviado do mês.
%C	O século (um ano dividido por 100 e truncado para um inteiro) como número decimal (00-99).
%c	A representação de data e hora.
%D	A data no formato %m/%d/%y.
%d	O dia do mês como número decimal (01-31).
%e	O dia do mês como número decimal (1-31). Dígitos únicos são precedidos por um espaço.
%F	A data no formato %Y-%m-%d.
%G	O ano ISO 8601 com o século como número decimal.
%g	O ano ISO 8601 sem o século como número decimal (00-99).
%H	A hora em um relógio de 24 horas como número decimal (00-23).
%I	A hora em um relógio de 12 horas como número decimal (01-12).
%j	O dia do ano como número decimal (001-366).
%k	A hora em um relógio de 24 horas como número decimal (0-23). Dígitos únicos são precedidos por um espaço.
%l	A hora em um relógio de 12 horas como número decimal (1-12). Dígitos únicos são precedidos por um espaço.
%M	O minuto como número decimal (00-59).
%m	O mês como número decimal (01-12).
%n	Um caractere de nova linha.
%P	am ou pm
%p	AM ou PM.
%R	A hora no formato %H:%M.
%r	A hora em um relógio de 12 horas usando a notação AM/PM.
%S	O segundo como número decimal (00-60).
%s	O número de segundos desde 1970-01-01 00:00:00 UTC. Sempre modifica todos os outros elementos de formato, independentemente de onde %s aparece na string. Se vários elementos %s aparecerem, o último terá precedência.
%T	A hora no formato %H:%M:%S.
%t	Um caractere de tabulação.
%U	O número da semana do ano (domingo como o primeiro dia da semana) como número decimal (00-53).
%u	O dia da semana (segunda-feira como o primeiro dia da semana) como número decimal (1-7).
%V	O número da semana do ano (segunda-feira como o primeiro dia da semana) como número decimal (01-53). Se a semana que tem 1 de janeiro tiver quatro ou mais dias no ano novo, então será a semana 1. Caso contrário, será a semana 53 do ano anterior e a semana seguinte será a semana 1.
%W	O número da semana do ano (segunda-feira como o primeiro dia da semana) como número decimal (00-53).
%w	O dia da semana (domingo como o primeiro dia da semana) como número decimal (0-6).
%X	A representação da hora no formato HH:MM:SS.
%x	A representação de data no formato MM/DD/YY.
%Y	O ano com o século como número decimal.
%y	O ano sem o século como número decimal (00-99), com um zero opcional à esquerda. Pode ser combinado com %C. Se %C não for especificado, os anos 00-68 serão 2000s e os anos 69-99 serão 1900s.
%Z	O nome do fuso horário.
%z	O deslocamento do meridiano de origem no formato +HHMM ou -HHMM conforme o caso, com valores positivos representando locais a leste de Greenwich.
%%	Um único caractere %.
%Ez	Fuso horário numérico compatível com RFC 3339 (+HH:MM ou -HH:MM).
%E#S	Segundos com # dígitos de precisão fracionária.
%E*S	Segundos com precisão fracionária total (um literal '*').
%E4Y	Anos com quatro caracteres (0001 ... 9999). %Y produz quantos caracteres forem necessários para processar o ano totalmente.

Definições de fuso horário

Determinadas funções de data e timestamp permitem substituir o fuso horário padrão e especificar um diferente. Você pode especificar um fuso horário fornecendo o deslocamento UTC com o seguinte formato:

(+|-)H[H][:M[M]]

Por exemplo:

-08:00

Funções de segurança

O BigQuery é compatível com as seguintes funções de segurança:

SESSION_USER

SESSION_USER()

Descrição

Retorna o endereço de email do usuário que está executando a consulta.

Tipo de dados retornados

STRING

Exemplo

SELECT SESSION_USER() as user;

+----------------------+
| user                 |
+----------------------+
| [e-mail]     |
+----------------------+

Funções de rede

NET.IP_FROM_STRING

NET.IP_FROM_STRING(addr_str)

Descrição

Converte um endereço IPv4 ou IPv6 do formato de texto (STRING) para o formato binário (BYTES) na ordem de bytes da rede.

Essa função é compatível com os seguintes formatos de addr_str:

• IPv4: formato "dotted-quad" (quádruplo com ponto). Por exemplo: 10.1.2.3.
• IPv6: formato separado por dois-pontos. Por exemplo: 1234:5678:90ab:cdef:1234:5678:90ab:cdef. Para mais exemplos, consulte IP Version 6 Addressing Architecture.

Essa função não é compatível com a notação CIDR, como 10.1.2.3/32.

Se ela receber uma entrada NULL, retornará NULL. Se a entrada for considerada inválida, ocorrerá um erro OUT_OF_RANGE.

Tipo de dados retornados

BYTES

Exemplo

SELECT
  addr_str, FORMAT("%T", NET.IP_FROM_STRING(addr_str)) AS ip_from_string
FROM UNNEST([
  '48.49.50.51',
  '::1',
  '3031:3233:3435:3637:3839:4041:4243:4445',
  '::ffff:192.0.2.128'
]) AS addr_str;

addr_str	ip_from_string
48.49.50.51	b"0123"
::1	b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01"
3031:3233:3435:3637:3839:4041:4243:4445	b"0123456789@ABCDE"
::ffff:192.0.2.128	b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80"

NET.SAFE_IP_FROM_STRING

NET.SAFE_IP_FROM_STRING(addr_str)

Descrição

Semelhante a NET.IP_FROM_STRING, mas retorna NULL em vez de gerar um erro se a entrada for inválida.

Tipo de dados retornados

BYTES

Exemplo

SELECT
  addr_str,
  FORMAT("%T", NET.SAFE_IP_FROM_STRING(addr_str)) AS safe_ip_from_string
FROM UNNEST([
  '48.49.50.51',
  '::1',
  '3031:3233:3435:3637:3839:4041:4243:4445',
  '::ffff:192.0.2.128',
  '48.49.50.51/32',
  '48.49.50',
  '::wxyz'
]) AS addr_str;

addr_str	safe_ip_from_string
48.49.50.51	b"0123"
::1	b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01"
3031:3233:3435:3637:3839:4041:4243:4445	b"0123456789@ABCDE"
::ffff:192.0.2.128	b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80"
48.49.50.51/32	NULL
48.49.50	NULL
::wxyz	NULL

NET.IP_TO_STRING

NET.IP_TO_STRING(addr_bin)

Descrição Converte um endereço IPv4 ou IPv6 do formato binário (BYTES) na ordem de bytes da rede para o formato de texto (STRING).

Se a entrada for de 4 bytes, essa função retornará um endereço IPv4 como uma STRING. Se a entrada for de 16 bytes, ela retornará um endereço IPv6 como uma STRING.

Se receber uma entrada NULL, essa função retornará NULL. Se a entrada tiver um comprimento diferente de 4 ou 16, ocorrerá um erro OUT_OF_RANGE.

Tipo de dados retornados

STRING

Exemplo

SELECT FORMAT("%T", x) AS addr_bin, NET.IP_TO_STRING(x) AS ip_to_string
FROM UNNEST([
  b"0123",
  b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01",
  b"0123456789@ABCDE",
  b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80"
]) AS x;

addr_bin	ip_to_string
b"0123"	48.49.50.51
b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01"	::1
b"0123456789@ABCDE"	3031:3233:3435:3637:3839:4041:4243:4445
b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80"	::ffff:192.0.2.128

NET.IP_NET_MASK

NET.IP_NET_MASK(num_output_bytes, prefix_length)

Descrição

Retorna uma máscara de rede, isto é, uma sequência de bytes com comprimento igual a num_output_bytes, no qual os primeiros bits prefix_length são definidos para 1. Os outros bits são definidos para 0. num_output_bytes e prefix_length são INT64. Essa função gera um erro, se num_output_bytes não for 4 (para IPv4) ou 16 (para IPv6). Ela também gera um erro, se prefix_length for negativo ou maior que 8 * num_output_bytes.

Tipo de dados retornados

BYTES

Exemplo

SELECT x, y, FORMAT("%T", NET.IP_NET_MASK(x, y)) AS ip_net_mask
FROM UNNEST([
  STRUCT(4 as x, 0 as y),
  (4, 20),
  (4, 32),
  (16, 0),
  (16, 1),
  (16, 128)
]);

x	y	ip_net_mask
4	0	b"\x00\x00\x00\x00"
4	20	b"\xff\xff\xf0\x00"
4	32	b"\xff\xff\xff\xff"
16	0	b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00"
16	1	b"\x80\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00"
16	128	b"\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff"

NET.IP_TRUNC

NET.IP_TRUNC(addr_bin, prefix_length)

Descrição Utiliza addr_bin, um endereço IPv4 ou IPv6 em formato binário (BYTES) na ordem de bytes da rede, e retorna um endereço de sub-rede no mesmo formato. O resultado tem o mesmo comprimento de addr_bin, no qual os primeiros bits prefix_length são iguais aos de addr_bin e os bits restantes são 0.

Essa função gera um erro, se LENGTH(addr_bin) não for 4 ou 16, ou se prefix_len for negativo ou maior que LENGTH(addr_bin) * 8.

Tipo de dados retornados

BYTES

Exemplo

SELECT
  FORMAT("%T", x) as addr_bin, prefix_length,
  FORMAT("%T", NET.IP_TRUNC(x, prefix_length)) AS ip_trunc
FROM UNNEST([
  STRUCT(b"\xAA\xBB\xCC\xDD" as x, 0 as prefix_length),
  (b"\xAA\xBB\xCC\xDD", 11), (b"\xAA\xBB\xCC\xDD", 12),
  (b"\xAA\xBB\xCC\xDD", 24), (b"\xAA\xBB\xCC\xDD", 32),
  (b'0123456789@ABCDE', 80)
]);

addr_bin	prefix_length	ip_trunc
b"\xaa\xbb\xcc\xdd"	0	b"\x00\x00\x00\x00"
b"\xaa\xbb\xcc\xdd"	11	b"\xaa\xa0\x00\x00"
b"\xaa\xbb\xcc\xdd"	12	b"\xaa\xb0\x00\x00"
b"\xaa\xbb\xcc\xdd"	24	b"\xaa\xbb\xcc\x00"
b"\xaa\xbb\xcc\xdd"	32	b"\xaa\xbb\xcc\xdd"
b"0123456789@ABCDE"	80	b"0123456789\x00\x00\x00\x00\x00\x00"

NET.IPV4_FROM_INT64

NET.IPV4_FROM_INT64(integer_value)

Descrição

Converte um endereço IPv4 do formato inteiro para binário (BYTES) na ordem de bytes da rede. Na entrada do inteiro, o bit menos significativo do endereço IP é armazenado no bit menos significativo do inteiro, independentemente da arquitetura do host ou do cliente. Por exemplo, 1 significa 0.0.0.1, e 0x1FF significa 0.0.1.255.

Essa função verifica se todos os 32 bits mais significativos são 0, ou se todos os 33 bits mais significativos são 1 (estendido por sinal de um inteiro de 32 bits). Em outras palavras, a entrada precisa estar no intervalo [-0x80000000, 0xFFFFFFFF]. Caso contrário, a função gera um erro.

Essa função não aceita IPv6.

Tipo de dados retornados

BYTES

Exemplo

SELECT x, x_hex, FORMAT("%T", NET.IPV4_FROM_INT64(x)) AS ipv4_from_int64
FROM (
  SELECT CAST(x_hex AS INT64) x, x_hex
  FROM UNNEST(["0x0", "0xABCDEF", "0xFFFFFFFF", "-0x1", "-0x2"]) AS x_hex
);

x	x_hex	ipv4_from_int64
0	0x0	b"\x00\x00\x00\x00"
11259375	0xABCDEF	b"\x00\xab\xcd\xef"
4294967295	0xFFFFFFFF	b"\xff\xff\xff\xff"
-1	-0x1	b"\xff\xff\xff\xff"
-2	-0x2	b"\xff\xff\xff\xfe"

NET.IPV4_TO_INT64

NET.IPV4_TO_INT64(addr_bin)

Descrição

Converte um endereço IPv4 do formato binário (BYTES) na ordem de bytes da rede para inteiro. Na saída do inteiro, o bit menos significativo do endereço IP é armazenado no bit menos significativo do inteiro, independentemente da arquitetura do host ou do cliente. Por exemplo, 1 significa 0.0.0.1, e 0x1FF significa 0.0.1.255. A saída está no intervalo [0, 0xFFFFFFFF].

Se o comprimento da entrada não for 4, a função gerará um erro.

Essa função não aceita IPv6.

Tipo de dados retornados

INT64

Exemplo

SELECT
  FORMAT("%T", x) AS addr_bin,
  FORMAT("0x%X", NET.IPV4_TO_INT64(x)) AS ipv4_to_int64
FROM
UNNEST([b"\x00\x00\x00\x00", b"\x00\xab\xcd\xef", b"\xff\xff\xff\xff"]) AS x;

addr_bin	ipv4_to_int64
b"\x00\x00\x00\x00"	0x0
b"\x00\xab\xcd\xef"	0xABCDEF
b"\xff\xff\xff\xff"	0xFFFFFFFF

NET.HOST

NET.HOST(url)

Descrição

Toma um URL como STRING e retorna o host com STRING. Para ter os melhores resultados, os valores do URL precisam estar de acordo com o formato definido por RFC 3986. Se não estiverem em conformidade com a formatação RFC 3986, a função analisará a entrada da melhor maneira possível e retornará um resultado relevante. Se não for possível analisar a entrada, será retornado NULL.

Nota: a função não realiza a normalização.

Tipo de dados retornados

STRING

Exemplo

SELECT
  FORMAT("%T", input) AS input,
  description,
  FORMAT("%T", NET.HOST(input)) AS host,
  FORMAT("%T", NET.PUBLIC_SUFFIX(input)) AS suffix,
  FORMAT("%T", NET.REG_DOMAIN(input)) AS domain
FROM (
  SELECT "" AS input, "invalid input" AS description
  UNION ALL SELECT "http://abc.xyz", "standard URL"
  UNION ALL SELECT "//user:password@a.b:80/path?query",
                   "standard URL with relative scheme, port, path and query, but no public suffix"
  UNION ALL SELECT "https://[::1]:80", "standard URL with IPv6 host"
  UNION ALL SELECT "http://例子.卷筒纸.中国", "standard URL with internationalized domain name"
  UNION ALL SELECT "    www.Google.Co.UK    ",
                   "non-standard URL with spaces, upper case letters, and without scheme"
  UNION ALL SELECT "mailto:?to=&subject=&body=", "URI rather than URL--unsupported"
);

entrada	descrição	host	sufixo	domínio
""	entrada inválida	NULL	NULL	NULL
"http://abc.xyz"	URL padrão	"abc.xyz"	"xyz"	"abc.xyz"
"//user:password@a.b:80/path?query"	URL padrão com esquema, porta, caminho e consulta relativos, mas sem sufixo público	"a.b"	NULL	NULL
"https://[::1]:80"	URL padrão com host IPv6	"[::1]"	NULL	NULL
"http://例子.卷筒纸.中国"	URL padrão com nome de domínio internacionalizado	"例子.卷筒纸.中国"	"中国"	"卷筒纸.中国"
"    www.Google.Co.UK    "	URL não padrão com espaços, letras maiúsculas e sem esquema	"www.Google.Co.UK"	"Co.UK"	"Google.Co.UK"
"mailto:?to=&subject=&body="	URI, e não URL, não compatível	"mailto"	NULL	NULL

NET.PUBLIC_SUFFIX

NET.PUBLIC_SUFFIX(url)

Descrição

Utiliza um URL como STRING e retorna o sufixo público (como com, org ou net) como STRING. Um sufixo público é um domínio ICANN registrado em publicuffix.org. Para os melhores resultados, os valores de URL precisam obedecer ao formato conforme definido pelo RFC 3986. Se não estiverem em conformidade com a formatação RFC 3986, a função analisará a entrada da melhor maneira possível e retornará um resultado relevante.

A função retornará NULL, se qualquer uma das seguintes situações for verdadeira:

• Não é possível analisar o host com a entrada.
• O host analisado contém pontos adjacentes no meio (não à esquerda ou direita).
• O host analisado não contém um sufixo público.

Antes de pesquisar o sufixo público, essa função normalizará temporariamente o host convertendo letras em inglês em maiúsculas para minúsculas e codificando todos os caracteres não ASCII com Punycode. Depois, ela retornará o sufixo público como parte do host original em vez do host normalizado:

Observação: a função não realiza a normalização Unicode.

Observação: os dados do sufixo público em publicuffix.org também contém domínios privados. Essa função ignora os domínios privados.

Observação: os dados do sufixo público podem mudar ao longo do tempo. Por conseguinte, a entrada que produz um resultado NULL no momento pode produzir um valor não NULL no futuro.

Tipo de dados retornados

STRING

Exemplo

SELECT
  FORMAT("%T", input) AS input,
  description,
  FORMAT("%T", NET.HOST(input)) AS host,
  FORMAT("%T", NET.PUBLIC_SUFFIX(input)) AS suffix,
  FORMAT("%T", NET.REG_DOMAIN(input)) AS domain
FROM (
  SELECT "" AS input, "invalid input" AS description
  UNION ALL SELECT "http://abc.xyz", "standard URL"
  UNION ALL SELECT "//user:password@a.b:80/path?query",
                   "standard URL with relative scheme, port, path and query, but no public suffix"
  UNION ALL SELECT "https://[::1]:80", "standard URL with IPv6 host"
  UNION ALL SELECT "http://例子.卷筒纸.中国", "standard URL with internationalized domain name"
  UNION ALL SELECT "    www.Google.Co.UK    ",
                   "non-standard URL with spaces, upper case letters, and without scheme"
  UNION ALL SELECT "mailto:?to=&subject=&body=", "URI rather than URL--unsupported"
);

entrada	descrição	host	sufixo	domínio
""	entrada inválida	NULL	NULL	NULL
"http://abc.xyz"	URL padrão	"abc.xyz"	"xyz"	"abc.xyz"
"//user:password@a.b:80/path?query"	URL padrão com esquema, porta, caminho e consulta relativos, mas sem sufixo público	"a.b"	NULL	NULL
"https://[::1]:80"	URL padrão com host IPv6	"[::1]"	NULL	NULL
"http://例子.卷筒纸.中国"	URL padrão com nome de domínio internacionalizado	"例子.卷筒纸.中国"	"中国"	"卷筒纸.中国"
"    www.Google.Co.UK    "	URL não padrão com espaços, letras maiúsculas e sem esquema	"www.Google.Co.UK"	"Co.UK"	"Google.Co.UK"
"mailto:?to=&subject=&body="	URI, e não URL, não compatível	"mailto"	NULL	NULL

NET.REG_DOMAIN

NET.REG_DOMAIN(url)

Descrição

Utiliza um URL como STRING e retorna o domínio registrado ou registrável (o sufixo público mais um rótulo precedente), como STRING. Para conseguir os melhores resultados, os valores de URL precisam obedecer ao formato definido por RFC 3986. Se não estiverem em conformidade com a formatação RFC 3986, a função analisará a entrada da melhor maneira possível e retornará um resultado relevante.

A função retornará NULL, se qualquer uma das seguintes situações for verdadeira:

• Não é possível analisar o host com a entrada.
• O host analisado contém pontos adjacentes no meio (não à esquerda ou direita).
• O host analisado não contém um sufixo público.
• O host analisado contém somente um sufixo público sem rótulo precedente.

Antes de pesquisar o sufixo público, essa função normalizará temporariamente o host convertendo letras em inglês em maiúsculas para minúsculas e codificando todos os caracteres não ASCII com Punycode. Depois, ela retornará o domínio registrado ou registrável como parte do host original em vez do host normalizado.

Observação: a função não realiza a normalização Unicode.

Observação: os dados do sufixo público em publicuffix.org também contém domínios privados. Essa função não trata um domínio privado como um sufixo público. Por exemplo, se "us.com" for um domínio privado nos dados de sufixo público, NET.REG_DOMAIN ("foo.us.com") retornará "us.com" (o sufixo público "com" mais o rótulo anterior "us ") em vez de "foo.us.com" (o domínio privado "us.com" mais o rótulo anterior "foo").

Observação: os dados do sufixo público podem mudar ao longo do tempo. Por conseguinte, a entrada que produz um resultado NULL no momento pode produzir um valor não NULL no futuro.

Tipo de dados retornados

STRING

Exemplo

SELECT
  FORMAT("%T", input) AS input,
  description,
  FORMAT("%T", NET.HOST(input)) AS host,
  FORMAT("%T", NET.PUBLIC_SUFFIX(input)) AS suffix,
  FORMAT("%T", NET.REG_DOMAIN(input)) AS domain
FROM (
  SELECT "" AS input, "invalid input" AS description
  UNION ALL SELECT "http://abc.xyz", "standard URL"
  UNION ALL SELECT "//user:password@a.b:80/path?query",
                   "standard URL with relative scheme, port, path and query, but no public suffix"
  UNION ALL SELECT "https://[::1]:80", "standard URL with IPv6 host"
  UNION ALL SELECT "http://例子.卷筒纸.中国", "standard URL with internationalized domain name"
  UNION ALL SELECT "    www.Google.Co.UK    ",
                   "non-standard URL with spaces, upper case letters, and without scheme"
  UNION ALL SELECT "mailto:?to=&subject=&body=", "URI rather than URL--unsupported"
);

entrada	descrição	host	sufixo	domínio
""	entrada inválida	NULL	NULL	NULL
"http://abc.xyz"	URL padrão	"abc.xyz"	"xyz"	"abc.xyz"
"//user:password@a.b:80/path?query"	URL padrão com esquema, porta, caminho e consulta relativos, mas sem sufixo público	"a.b"	NULL	NULL
"https://[::1]:80"	URL padrão com host IPv6	"[::1]"	NULL	NULL
"http://例子.卷筒纸.中国"	URL padrão com nome de domínio internacionalizado	"例子.卷筒纸.中国"	"中国"	"卷筒纸.中国"
"    www.Google.Co.UK    "	URL não padrão com espaços, letras maiúsculas e sem esquema	"www.Google.Co.UK"	"Co.UK"	"Google.Co.UK"
"mailto:?to=&subject=&body="	URI, e não URL, não compatível	"mailto"	NULL	NULL

Operadores

Os operadores são representados por caracteres especiais ou palavras-chave. Eles não usam a sintaxe de chamada de função. Um operador manipula qualquer número de entradas de dados, também chamados de operandos, e retorna um resultado.

Convenções comuns:

• A menos que especificado em contrário, todos os operadores retornam NULL quando um dos operandos é NULL.
• Todos os operadores geram um erro se o resultado do cálculo estourar.
• Para todas as operações de ponto flutuante, +/-inf e NaN só são retornados se um dos operandos for +/-inf ou NaN. Em outros casos, é retornado um erro.

A tabela a seguir lista todos os operadores do BigQuery da precedência mais alta à mais baixa, isto é, na ordem em que são avaliados em uma declaração.

Ordem de precedência	Operador	Tipos de dados de entrada	Nome	Arity do operador
1	.	STRUCT	Operador de acesso ao campo do membro	Binário
	[ ]	ARRAY	Posição da matriz. Precisa ser usado com OFFSET ou ORDINAL. Consulte Funções ARRAY.	Binário
2	-	Todos os tipos numéricos	Unário menos	Unário
	~	Inteiro ou BYTES	Bit a bit not	Unário
3	*	Todos os tipos numéricos	Multiplicação	Binário
	/	Todos os tipos numéricos	Divisão	Binário
4	+	Todos os tipos numéricos	Adição	Binário
	-	Todos os tipos numéricos	Subtração	Binário
5	<<	Inteiro ou BYTES	Bit a bit deslocado para a esquerda	Binário
	>>	Inteiro ou BYTES	Bit a bit deslocado para a direita	Binário
6	&	Inteiro ou BYTES	Bit a bit and	Binário
7	^	Inteiro ou BYTES	Bit a bit xor	Binário
8	|	Inteiro ou BYTES	Bit a bit or	Binário
9 (operadores de comparação)	=	Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados.	Igual	Binário
	<	Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados.	Menor que	Binário
	>	Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados.	Maior que	Binário
	<=	Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados.	Menor que ou igual a	Binário
	>=	Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados.	Maior que ou igual a	Binário
	!=, <>	Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados.	Diferente	Binário
	[NOT] LIKE	STRING e byte	O valor [não] corresponde ao padrão especificado	Binário
	[NOT] BETWEEN	Qualquer tipo comparável. Para ver a lista, consulte Tipos de dados.	O valor [não] está dentro do intervalo especificado	Binário
	[NOT] IN	Qualquer tipo comparável. Para ver a lista, consulte Tipos de dados.	O valor [não] está no grupo de valores especificados	Binário
	IS [NOT] NULL	Todos	O valor [não] é NULL	Unário
	IS [NOT] TRUE	BOOL	O valor [não] é TRUE.	Unário
	IS [NOT] FALSE	BOOL	O valor [não] é FALSE.	Unário
10	NOT	BOOL	Lógico NOT	Unário
11	AND	BOOL	Lógico AND	Binário
12	OR	BOOL	Lógico OR	Binário

Os operadores com a mesma precedência são associativos da esquerda, ou seja, são agrupados da esquerda para a direita. Por exemplo, a expressão:

x AND y AND z

é interpretada como

( ( x AND y ) AND z )

A expressão:

x * y / z

é interpretada como

( ( x * y ) / z )

Todos os operadores de comparação têm a mesma prioridade e são agrupados usando a associatividade da esquerda. No entanto, os operadores de comparação não são associativos. Como resultado, é recomendável usar parênteses para melhorar a legibilidade e garantir que as expressões sejam resolvidas conforme desejado. Por exemplo:

(x < y) IS FALSE

é recomendado em vez de:

x < y IS FALSE

Operadores de acesso ao elemento

Operador	Sintaxe	Tipos de dados de entrada	Tipo de dados de resultado	Descrição
.	expressão.nomedocampo1...	STRUCT	Tipo T armazenado em nomedocampo1	Operador de ponto. Pode ser usado para acessar campos aninhados, como expressão.nomedocampo1.nomedocampo2...
[ ]	matriz_expressão [posição_palavra-chave (int_expressão ) ]	Consulte Funções ARRAY	Tipo T armazenado em ARRAY	posição_palavra-chave pode ser OFFSET ou ORDINAL. Consulte Funções ARRAY para conhecer as duas funções que usam esse operador.

Operadores aritméticos

Todos os operadores aritméticos aceitam a entrada do tipo numérico T, também presente no tipo de resultado, a menos que indicado de outra forma na descrição abaixo:

Nome	Sintaxe
Adição	X + Y
Subtração	X - Y
Multiplicação	X * Y
Divisão	X / Y
Unário menos	- X

NOTA: as operações de divisão por zero retornam um erro. Para retornar um resultado diferente, considere as funções IEEE_DIVIDE ou SAFE_DIVIDE.

Tipos de resultados para adição e multiplicação:

	INT64	NUMERIC	FLOAT64
INT64	INT64	NUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64

Tipos de resultados para subtração:

	INT64	NUMERIC	FLOAT64
INT64	INT64	NUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64

Tipos de resultados para divisão:

	INT64	NUMERIC	FLOAT64
INT64	FLOAT64	NUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64

Tipos de resultados para Unário menos:

Tipo de dados de entrada	Tipo de dados de resultado
INT64	INT64
NUMERIC	NUMERIC
FLOAT64	FLOAT64

Operadores bit a bit

Todos os operadores bit a bit retornam o mesmo tipo e comprimento do primeiro operando.

Nome	Sintaxe	Tipo de dados de entrada	Descrição
Bit a bit not	~ X	Inteiro ou BYTES	Executa a negação lógica em cada bit, formando o complemento dos números um do valor binário.
Bit a bit or	X | Y	X: inteiro ou BYTES Y: mesmo tipo de X	Toma dois padrões de bits de comprimento igual e executa a operação lógica OR inclusiva em cada par dos bits correspondentes. Esse operador gerará um erro se X e Y forem BYTES de comprimentos diferentes.
Bit a bit xor	X ^ Y	X: inteiro ou BYTES Y: mesmo tipo de X	Toma dois padrões de bits de comprimento igual e executa a operação lógica OR exclusiva em cada par dos bits correspondentes. Esse operador gerará um erro se X e Y forem BYTES de comprimentos diferentes.
Bit a bit and	X & Y	X: inteiro ou BYTES Y: mesmo tipo de X	Toma dois padrões de bits de comprimento igual e executa a operação lógica AND em cada par dos bits correspondentes. Esse operador gerará um erro se X e Y forem BYTES de comprimentos diferentes.
Deslocado para a esquerda	X << Y	X: inteiro ou BYTES Y: INT64	Desloca o primeiro operando X para a esquerda. Esse operador retorna 0 ou uma sequência de bytes de b'\x00' se o segundo operando Y for maior que ou igual ao comprimento do bit do primeiro operando X (por exemplo, 64, se X tiver o tipo INT64). Esse operador gerará um erro se Y for negativo.
Deslocado para a direita	X >> Y	X: inteiro ou BYTES Y: INT64	Desloca o primeiro operando X para a direita. Esse operador não faz a extensão de bit de sinal com um tipo com sinal. Isso significa que ele preenche com 0 os bits vagos à esquerda. Esse operador retorna 0 ou uma sequência de bytes de b'\x00' se o segundo operando Y for maior que ou igual ao comprimento do bit do primeiro operando X (por exemplo, 64, se X tiver o tipo INT64). Esse operador gerará um erro se Y for negativo.

Operadores lógicos

Todos os operadores lógicos permitem apenas a entrada BOOL.

Nome	Sintaxe	Descrição
Lógico NOT	NOT X	Retorna FALSE se a entrada for TRUE. Retorna TRUE se a entrada for FALSE. Caso contrário, retorna NULL.
Lógico AND	X AND Y	Retorna FALSE se pelo menos uma entrada for FALSE. Retorna TRUE se X e Y forem TRUE. Caso contrário, retorna NULL.
Lógico OR	X OR Y	Retorna FALSE se X e Y forem FALSE. Retorna TRUE se pelo menos uma entrada for TRUE. Caso contrário, retorna NULL.

Operadores de comparação

As comparações sempre retornam BOOL. Geralmente, é necessário que os operandos sejam do mesmo tipo. Se os operandos forem de tipos diferentes e o BigQuery puder converter os valores desses tipos em um tipo comum sem perda de precisão, ele geralmente os coagirá a esse tipo comum para a comparação. O BigQuery geralmente coage literais ao tipo não literal, quando o primeiro tipo está presente. Os tipos de dados comparáveis são definidos na seção Tipos de dados.

STRUCTs aceitam somente 4 operadores de comparação: igual (=), diferente (!= e <>) e IN.

As regras a seguir são aplicadas na comparação desses tipos de dados.

• FLOAT64: todas as comparações com NaN retornam FALSE, exceto != e <>, que retornam TRUE.
• BOOL: FALSE é menor que TRUE.
• STRING: as strings são comparadas ponto de código a ponto de código. Isso significa que strings canonicamente equivalentes somente serão comparadas como iguais se tiverem sido normalizadas primeiro.
• NULL: A convenção é válida aqui: qualquer operação com uma entrada NULL retorna NULL.

Nome	Sintaxe	Descrição
Menor que	X < Y	Retorna TRUE, se X for menor que Y.
Menor que ou igual a	X <= Y	Retorna TRUE, se X for menor que ou igual a Y.
Maior que	X > Y	Retorna TRUE, se X for maior que Y.
Maior que ou igual a	X >= Y	Retorna TRUE, se X for maior que ou igual a Y.
Igual	X = Y	Retorna TRUE, se X for igual a Y.
Diferente	X != Y X <> Y	Retorna TRUE, se X não for igual a Y.
BETWEEN	X [NOT] BETWEEN Y AND Z	Retorna TRUE, se X [não] estiver dentro do intervalo especificado. O resultado de "X BETWEEN Y AND Z" é equivalente a "Y <= X AND X <= Z", mas X é avaliado somente uma vez no primeiro.
LIKE	X [NOT] LIKE Y	Verifica se a STRING no primeiro operando X corresponde a um padrão especificado pelo segundo operando Y. As expressões podem conter estes caracteres: Um sinal de porcentagem "%" corresponde a qualquer número de caracteres ou bytes. Um sublinhado "_" corresponde a um único caractere ou byte. Você pode inserir um caractere de escape em "\", "_" ou "%" usando duas barras invertidas. Por exemplo, "\\%". Se você estiver usando strings brutas, basta uma barra invertida. Por exemplo, r"\%".
IN	Várias - veja abaixo	Retorna FALSE, se o operando à direita estiver vazio. Retorna NULL, se o operando à esquerda for NULL. Retorna TRUE ou NULL, nunca FALSE, se o operando à direita contiver NULL. Os argumentos em qualquer lado de IN são expressões gerais. Nenhum operando precisa ser um literal, embora seja mais comum usar um literal à direita. X é avaliado apenas uma vez.

Quando a igualdade dos valores com tipo de dados STRUCT é testada, é possível que um ou mais campos sejam NULL. Nesses casos:

• Se todos os valores de campo não NULL forem iguais, a comparação retornará NULL.
• Se qualquer valor de campo não NULL for diferente, a comparação retornará FALSE.

A tabela a seguir demonstra como os tipos de dados STRUCT são comparados quando têm campos com valores NULL.

Struct1	Struct2	Struct1 = Struct2
STRUCT(1, NULL)	STRUCT(1, NULL)	NULL
STRUCT(1, NULL)	STRUCT(2, NULL)	FALSE
STRUCT(1,2)	STRUCT(1, NULL)	NULL

Operadores IN

O operador IN aceita estas sintaxes:

x [NOT] IN (y, z, ... ) # Requires at least one element
x [NOT] IN (<subquery>)
x [NOT] IN UNNEST(<array expression>) # analysis error if the expression
                                      # does not return an ARRAY type.

Os argumentos em qualquer lado do operador IN são expressões gerais. É comum o uso de literais na expressão do lado direito. No entanto, isso não é obrigatório.

A semântica de:

x IN (y, z, ...)

é definida como equivalente a

(x = y) OR (x = z) OR ...

e os formatos de subconsulta e matriz são definidos de maneira semelhante.

x NOT IN ...

é equivalente a:

NOT(x IN ...)

O formato UNNEST trata uma verificação da matriz como UNNEST na cláusula FROM:

x [NOT] IN UNNEST(<array expression>)

Esse formato é usado muitas vezes com parâmetros ARRAY. Por exemplo:

x IN UNNEST(@array_parameter)

Observação: uma ARRAY NULL será tratada de maneira equivalente a uma ARRAY vazia.

Consulte o tópico Matrizes para mais informações sobre como usar essa sintaxe.

Quando o operador IN é usado, aplica-se a seguinte semântica:

• IN com uma expressão vazia do lado direito é sempre FALSE.
• IN com uma expressão NULL do lado esquerdo e uma expressão não vazia do lado direito é sempre NULL.
• IN com um NULL na lista IN retorna somente TRUE ou NULL, nunca FALSE.
• NULL IN (NULL) retorna NULL.
• IN UNNEST(<NULL array>) retorna FALSE (não NULL).

IN pode ser usado com chaves de várias partes usando a sintaxe struct do construtor. Por exemplo:

(Key1, Key2) IN ( (12,34), (56,78) )
(Key1, Key2) IN ( SELECT (table.a, table.b) FROM table )

Para mais informações sobre essa sintaxe, consulte a seção Tipo struct do tópico Tipos de dados.

Operadores IS

Os operadores IS retornam TRUE ou FALSE para a condição que estão testando. Eles nunca retornam NULL, mesmo para entradas NULL, ao contrário das funções IS_INF e IS_NAN, definidas em Funções matemáticas. Se NOT estiver presente, o valor da saída BOOL será invertido.

Sintaxe da função	Tipo de dados de entrada	Tipo de dados de resultado	Descrição
X IS [NOT] NULL	Qualquer tipo de valor	BOOL	Retorna TRUE se o operando X for avaliado como NULL. Caso contrário, retorna FALSE.
X IS [NOT] TRUE	BOOL	BOOL	Retorna TRUE, se o operando BOOL for avaliado como TRUE. Caso contrário, retorna FALSE.
X IS [NOT] FALSE	BOOL	BOOL	Retorna TRUE, se o operando BOOL for avaliado como FALSE. Caso contrário, retorna FALSE.

Expressões condicionais

As expressões condicionais impõem restrições na ordem de avaliação das entradas. Em essência, elas são avaliadas da esquerda para a direita, com curto-circuito, e somente avaliam o valor da saída escolhida. Por outro lado, todas as entradas das funções regulares são avaliadas antes de chamar a função. O curto-circuito em expressões condicionais pode ser explorado para o tratamento de erros ou ajuste do desempenho.

Sintaxe	Tipos de dados de entrada	Tipo de dados de resultado	Descrição
CASE expr WHEN value THEN result [WHEN ...] [ELSE else_result] END	expr and value: Any type	result e else_result: supertipo de tipos de entrada.	Compara expr ao valor de cada cláusula WHEN sucessiva e retorna o primeiro resultado quando a comparação retorna verdadeira. As cláusulas WHEN e else_result restantes não são avaliadas. Se a comparação expr = value retornar falsa ou NULL para todas as cláusulas WHEN, retornará else_result, se presente. Se não presente, retornará NULL. As expressões expr e value precisam ser coercíveis implicitamente para um supertipo comum. As comparações de igualdade são realizadas em valores coercíveis. As expressões result e else_result precisam ser coercíveis a um supertipo comum.
CASE WHEN cond1 THEN result [WHEN cond2...] [ELSE else_result] END	cond: BOOL	result e else_result: supertipo de tipos de entrada.	Avalia a condição cond de cada cláusula WHEN sucessiva e retorna o primeiro resultado quando a condição é verdadeira. As cláusulas WHEN restantes e else_result não são avaliadas. Se todas as condições forem falsas ou NULL, retornará else_result, se presente. Se não presente, retornará NULL. As expressões result e else_result precisam ser coercíveis implicitamente para um supertipo comum.
COALESCE(expr1, ..., exprN)	Qualquer tipo	Supertipo de tipos de entrada	Retorna o valor da primeira expressão não nula. As expressões restantes não são avaliadas. Todas as expressões de entrada precisam ser coercíveis implicitamente para um supertipo comum.
IF(cond, true_result, else_result)	cond: BOOL	true_result e else_result: qualquer tipo.	Se cond for verdadeiro, retorna true_result. Caso contrário, retorna else_result. else_result não é avaliado se cond for verdadeiro. true_result não será avaliado se cond for falso ou NULL. true_result e else_result precisam ser coercíveis para um supertipo comum.
IFNULL(expr, null_result)	Qualquer tipo	Qualquer tipo ou supertipo de tipos de entrada.	Se expr for NULL, retorna null_result . Caso contrário, retorna expr. Se expr não for NULL, null_result não será avaliado. expr e null_result precisam ser coercíveis implicitamente para um supertipo comum. Sinônimo de COALESCE(expr, null_result).
NULLIF(expression, expression_to_match)	Qualquer tipo T ou subtipo de T	Qualquer tipo T ou subtipo de T	Retorna NULL se expression = expression_to_match for verdadeiro. Caso contrário, retorna expression. expression e expression_to_match precisam ser coercíveis implicitamente para um supertipo comum. A comparação de igualdade é realizada em valores coercíveis.

Subconsultas de expressão

Há quatro tipos de subconsultas de expressão, isto é, subconsultas que são usadas como expressões. As subconsultas de expressão retornam NULL ou um único valor, ao contrário de uma coluna ou tabela, e precisam estar entre parênteses. Consulte Subconsultas para ver uma discussão mais completa sobre subconsultas.

Tipo de subconsulta	Tipo de dados de resultado	Descrição
Escalar	Qualquer tipo T	Uma subconsulta entre parênteses dentro de uma expressão (por exemplo, na lista SELECT ou na cláusula WHERE) é interpretada como uma subconsulta escalar. A lista SELECT em uma subconsulta escalar precisa ter exatamente um campo. Se a subconsulta retornar exatamente uma linha, esse único valor será o resultado da subconsulta escalar. Se retornar zero linhas, o valor da subconsulta escalar será NULL. Se a subconsulta retornar mais de uma linha, ocorrerá falha da consulta com um erro de tempo de execução. Quando a subconsulta é gravada com SELECT AS STRUCT, é possível incluir várias colunas e o valor retornado será o STRUCT criado. A seleção de várias colunas sem o uso de SELECT AS é um erro.
ARRAY	ARRAY	Pode usar SELECT AS STRUCT para criar matrizes de structs. Ao contrário, a seleção de várias colunas sem o uso de SELECT AS é um erro. Retorna uma ARRAY vazia, se a subconsulta retornar zero linhas. Nunca retorna uma ARRAY NULL.
IN	BOOL	Ocorre em uma expressão seguindo o operador IN. A subconsulta precisa produzir uma única coluna cujo tipo é compatível em igualdade com a expressão do lado esquerdo do operador IN. Retorna FALSE, se a subconsulta retornar zero linhas. x IN () é equivalente a x IN (value, value, ...) Para conhecer a semântica completa, consulte informações sobre o operador IN em Operadores de comparação.
EXISTS	BOOL	Retorna TRUE, se a subconsulta produzir uma ou mais linhas. Retorna FALSE, se a subconsulta produzir zero linhas. Nunca retorna NULL. Ao contrário de todas as outras subconsultas da expressão, não há regras sobre a lista de colunas. Qualquer número de colunas pode ser selecionado. Isso não afetará o resultado da consulta.

Exemplos

Os exemplos de subconsultas de expressão a seguir supõem que t.int_array tem o tipo ARRAY<INT64>.

Tipo	Subconsulta	Tipo de dados de resultado	Notas
Escalar	(SELECT COUNT(*) FROM t.int_array)	INT64
	(SELECT DISTINCT i FROM t.int_array i)	INT64, possivelmente erro de tempo de execução
	(SELECT i FROM t.int_array i WHERE i=5)	INT64, possivelmente erro de tempo de execução
	(SELECT ARRAY_AGG(i) FROM t.int_array i)	ARRAY	Usa a função de agregação ARRAY_AGG para retornar uma ARRAY.
	(SELECT 'xxx' a)	STRING
	(SELECT 'xxx' a, 123 b)	Erro	Retorna um erro porque há mais de uma coluna
	(SELECT AS STRUCT 'xxx' a, 123 b)	STRUCT
	(SELECT AS STRUCT 'xxx' a)	STRUCT
ARRAY	ARRAY(SELECT COUNT(*) FROM t.int_array)	ARRAY de tamanho 1
	ARRAY(SELECT x FROM t)	ARRAY
	ARRAY(SELECT 5 a, COUNT(*) b FROM t.int_array)	Erro	Retorna um erro porque há mais de uma coluna
	ARRAY(SELECT AS STRUCT 5 a, COUNT(*) b FROM t.int_array)	ARRAY
	ARRAY(SELECT AS STRUCT i FROM t.int_array i)	ARRAY	Cria uma ARRAY de STRUCTs de um campo
	ARRAY(SELECT AS STRUCT 1 x, 2, 3 x)	ARRAY	Retorna uma ARRAY de STRUCTs com campos anônimos ou duplicados.
	ARRAY(SELECT AS TypeName SUM(x) a, SUM(y) b, SUM(z) c from t)	array<NomeDoTipo>	Seleção de um tipo nomeado. Supõe que NomeDoTipo é um tipo de STRUCT com os campos a,b,c.
STRUCT	(SELECT AS STRUCT 1 x, 2, 3 x)	STRUCT	Cria um STRUCT com campos anônimos ou duplicados.
EXISTS	EXISTS(SELECT x,y,z FROM table WHERE y=z)	BOOL
	NOT EXISTS(SELECT x,y,z FROM table WHERE y=z)	BOOL
IN	x IN (SELECT y FROM table WHERE z)	BOOL
	x NOT IN (SELECT y FROM table WHERE z)	BOOL

Funções de depuração

O BigQuery aceita as funções de depuração a seguir.

ERROR

ERROR(error_message)

Descrição

Retorna um erro. O argumento error_message é um STRING.

O BigQuery trata ERROR da mesma maneira como uma expressão qualquer que pode resultar em um erro: não há garantia especial de ordem de avaliação.

Tipo de dados retornados

O BigQuery infere o tipo de retorno em contexto.

Exemplos

No exemplo a seguir, a consulta retornará uma mensagem de erro se o valor da linha não corresponder a um dos dois valores definidos.

SELECT
  CASE
    WHEN value = 'foo' THEN 'Value is foo.'
    WHEN value = 'bar' THEN 'Value is bar.'
    ELSE ERROR(concat('Found unexpected value: ', value))
  END AS new_value
FROM (
  SELECT 'foo' AS value UNION ALL
  SELECT 'bar' AS value UNION ALL
  SELECT 'baz' AS value);

Found unexpected value: baz

No exemplo a seguir, o BigQuery pode avaliar a função ERROR antes ou depois da condição x > 0, porque normalmente não oferece garantias de ordenação entre as condições de cláusula WHERE e não há garantias especiais para a função ERROR.

SELECT *
FROM (SELECT -1 AS x)
WHERE x > 0 AND ERROR('Example error');

No próximo exemplo, a cláusula WHERE avalia uma condição IF, que garante que o BigQuery avaliará apenas a função ERROR se a condição falhar.

SELECT *
FROM (SELECT -1 AS x)
WHERE IF(x > 0, true, ERROR(FORMAT('Error: x must be positive but is %t', x)));'

Error: x must be positive but is -1