Veja nesta página as expressões do BigQuery, incluindo funções e operadores.
Regras de chamada de função
A menos que seja indicado de outra forma na descrição da função, as regras a seguir aplicam-se a todas as funções:
- Nas funções que aceitam tipos numéricos, se um operando for um ponto flutuante e o outro for um outro tipo numérico, ambos serão convertidos para FLOAT64 antes que a função seja avaliada.
- Se um operando for
NULL
, o resultado seráNULL
, com exceção do operador IS. - Nas funções que fazem diferenciação de fuso horário (conforme indicado na descrição da função), se um fuso horário não for especificado, o padrão UTC será usado.
Prefixo SAFE.
Sintaxe:
SAFE.function_name()
Descrição
Se você iniciar uma função com o prefixo SAFE.
, ela retornará NULL
em vez de um erro. O prefixo SAFE.
apenas evita erros da função prefixada: ele não evita erros que ocorrem durante a avaliação de expressões de argumentos. O prefixo SAFE.
apenas evita erros que ocorrem devido ao valor das entradas da função, como erros de "valor fora do intervalo"; outros erros, como erros internos ou do sistema, ainda podem ocorrer. Se a função não retornar um erro, SAFE.
não terá efeito na saída. Se a função nunca retornar um erro, como RAND
, SAFE.
não tem efeito.
Operadores, como +
e =
, não são compatíveis com o prefixo SAFE.
. Para evitar erros de uma operação de divisão, use SAFE_DIVIDE. Alguns operadores, como IN
, ARRAY
e UNNEST
, são parecidos com funções, mas não são compatíveis com o prefixo SAFE.
. As funções CAST
e EXTRACT
também não são compatíveis com o prefixo SAFE.
. Para evitar erros de cast, use SAFE_CAST.
Exemplo
No seguinte exemplo, o primeiro uso da função SUBSTR
normalmente retorna um erro, porque a função não é compatível com argumentos de comprimento com valores negativos. No entanto, o prefixo SAFE.
faz com que a função retorne NULL
. O segundo uso da função SUBSTR
fornece a saída esperada: o prefixo SAFE.
não tem efeito.
SELECT SAFE.SUBSTR('foo', 0, -2) AS safe_output UNION ALL
SELECT SAFE.SUBSTR('bar', 0, 2) AS safe_output;
+-------------+
| safe_output |
+-------------+
| NULL |
| ba |
+-------------+
Funções compatíveis
O BigQuery é compatível com o uso do prefixo SAFE.
com a maioria das funções escalares que podem gerar erros, incluindo funções STRING, funções matemáticas, funções DATE, funções DATETIME e funções TIMESTAMP. O BigQuery não é compatível com o uso do prefixo SAFE.
com funções agregadas, analíticas ou definidas pelo usuário.
Como chamar funções definidas pelo usuário (UDFs) persistentes
Depois de criar uma UDF persistente, é possível chamá-la como qualquer outra função, prefixada com o nome do conjunto de dados em que foi definida como prefixo.
Sintaxe
[`project_name`].dataset_name.function_name([parameter_value[, ...]])
Para chamar uma UDF em um projeto diferente do projeto que você está usando para executar a consulta, project_name
é obrigatório.
Exemplos
O seguinte exemplo cria uma UDF chamada multiply_by_three
e a chama do mesmo projeto.
CREATE FUNCTION my_dataset.multiply_by_three(x INT64) AS (x * 3);
SELECT my_dataset.multiply_by_three(5) AS result; -- returns 15
O exemplo a seguir chama uma UDF persistente de outro projeto.
CREATE `other_project`.other_dataset.other_function(x INT64, y INT64)
AS (x * y * 2);
SELECT `other_project`.other_dataset.other_function(3, 4); --returns 24
Regras de conversão
A "conversão" inclui, entre outros tipos, cast e coerção.
- Cast é conversão explícita e usa a função
CAST()
. - Coerção é conversão implícita, realizada automaticamente pelo BigQuery nas condições descritas abaixo.
- Há um terceiro grupo de funções de conversão que tem seus próprios nomes de função, como
UNIX_DATE()
.
A tabela a seguir resume todas as possibilidades de CAST
e coerção dos tipos de dados do BigQuery. A coluna "Coerção para" se aplica a todas as expressões de um
determinado tipo de dados (por exemplo, uma
coluna), mas literais
e parâmetros também podem ser forçados. Consulte Coerção de literal e Coerção de parâmetro para mais detalhes.
Do tipo | CAST para | Coerção para |
---|---|---|
INT64 | BOOL INT64 NUMERIC FLOAT64 STRING |
FLOAT64 NUMERIC |
NUMERIC | INT64 NUMERIC FLOAT64 STRING |
FLOAT64 |
FLOAT64 | INT64 NUMERIC FLOAT64 STRING |
|
BOOL | BOOL INT64 STRING |
|
STRING | BOOL INT64 NUMERIC FLOAT64 STRING BYTES DATE DATETIME TIME TIMESTAMP |
|
BYTES | BYTES STRING |
|
DATE | DATE DATETIME STRING TIMESTAMP |
|
DATETIME | DATE DATETIME STRING TIME TIMESTAMP |
|
TIME | STRING TIME |
|
TIMESTAMP | DATE DATETIME STRING TIME TIMESTAMP |
|
ARRAY | ARRAY | |
STRUCT | STRUCT |
Cast
Sintaxe:
CAST(expr AS typename)
Usa-se a sintaxe de cast em uma consulta para indicar que é preciso converter o tipo do resultado de uma expressão em algum outro tipo.
Exemplo:
CAST(x=1 AS STRING)
Isso resulta em "true"
se x
for 1
, "false"
para qualquer outro valor que não seja NULL
e NULL
se x
for NULL
.
Os casts entre tipos compatíveis que não são mapeados com sucesso do valor original para o domínio de destino produzem erros de ambiente de execução. Por exemplo, o cast de BYTES para STRING em que a sequência de bytes não é um UTF-8 válido resulta em um erro de ambiente de execução.
Quando realizar o cast de uma expressão x
dos tipos a seguir, essas regras se aplicam:
De | Até | Regras ao fazer o cast x |
---|---|---|
INT64 | FLOAT64 | Retorna um valor aproximado de FLOAT64, mas potencialmente inexato. |
INT64 | BOOL | Retorna FALSE se x for 0 . Caso contrário, retorna TRUE . |
NUMERIC | Ponto flutuante | NUMERIC será convertido no número de ponto flutuante mais próximo com uma possível perda de precisão. |
FLOAT64 | INT64 | Retorna o valor mais próximo de INT64. Casos em que haja metades, como 1,5 ou -0,5, são arredondados para longe de zero. |
FLOAT64 | STRING | Retorna uma representação de string aproximada. |
FLOAT64 | NUMERIC | Se o número de ponto flutuante tiver mais de nove dígitos depois do ponto decimal, ele será arredondado para metade de zero. Fazer cast de NaN , +inf ou -inf retornará um erro. Fazer cast de um valor fora do intervalo de NUMERIC retornará um erro de estouro.
|
BOOL | INT64 | Retorna 1 se x for TRUE . Caso contrário, retorna 0 . |
BOOL | STRING | Retorna "true" se x for TRUE . Caso contrário, retorna "false" . |
STRING | FLOAT64 | Retorna x como um valor de FLOAT64, interpretando-o como tendo a mesma forma de um FLOAT64 literal válido.Também aceita casts de "inf" , "+inf" , "-inf" , e "nan" .As conversões não diferenciam maiúsculas de minúsculas. |
STRING | NUMERIC | O literal numérico contido em STRING não pode exceder a precisão máxima ou o intervalo do tipo NUMERIC , ou ocorrerá um erro. Se o número de dígitos depois do ponto decimal exceder nove, o valor resultando NUMERIC será arredondado para metade de zero para ter nove dígitos depois do ponto decimal.
|
STRING | BOOL | Retorna TRUE se x for "true" e FALSE se x for "false" Todos os outros valores de x são inválidos e lançam um erro em vez de fazer o cast para BOOL.As STRINGs não diferenciam maiúsculas de minúsculas durante a conversão para BOOL. |
STRING | BYTES | O cast das STRINGs para BYTES é feito usando a codificação UTF-8. Por exemplo, a STRING "©", após o cast para BYTES, torna-se uma sequência de dois bytes com os valores hexadecimais C2 e A9. |
BYTES | STRING | Retorna x interpretado como uma STRING UTF-8.Por exemplo, o literal BYTES b'\xc2\xa9' , após o cast para STRING, é interpretado como UTF-8 e se torna o caractere unicode “©”.Um erro ocorre se x não é um UTF-8 válido. |
ARRAY | ARRAY | Precisa ser exatamente do mesmo tipo ARRAY. |
STRUCT | STRUCT | Permitido, se as condições a seguir forem atendidas:
|
Cast seguro
Quando CAST
é usado, pode ocorrer falha na consulta se o BigQuery não conseguir executar o cast. Por exemplo, a consulta a seguir gera um erro:
SELECT CAST("apple" AS INT64) AS not_a_number;
Caso queira proteger as consultas contra esses tipos de erros, use SAFE_CAST
. SAFE_CAST
é idêntico a CAST
, exceto que retorna NULL em vez de gerar um erro.
SELECT SAFE_CAST("apple" AS INT64) AS not_a_number;
+--------------+
| not_a_number |
+--------------+
| NULL |
+--------------+
Se você estiver fazendo o cast de bytes para strings, também poderá usar a função SAFE_CONVERT_BYTES_TO_STRING
. Todos os caracteres UTF-8 inválidos são substituídos pelo caractere de substituição unicode, U+FFFD
. Consulte SAFE_CONVERT_BYTES_TO_STRING para mais informações.
Cast de strings hexadecimais em inteiros
Se você estiver trabalhando com strings hexadecimais (0x123
), faça o cast dessas strings como
números inteiros:
SELECT '0x123' as hex_value, CAST('0x123' as INT64) as hex_to_int;
+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| 0x123 | 291 |
+-----------+------------+
SELECT '-0x123' as hex_value, CAST('-0x123' as INT64) as hex_to_int;
+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| -0x123 | -291 |
+-----------+------------+
Cast de tipos de horário
O BigQuery é compatível com o cast de tipos de horário de/para strings da maneira a seguir:
CAST(time_expression AS STRING)
CAST(string_expression AS TIME)
O cast de um tipo de horário para uma string segue o formato HH:MM:SS
, não importa qual seja o
fuso horário. Ao fazer o cast da string para o horário, a string precisa estar em conformidade com
o formato literal do horário compatível, independentemente do fuso horário. Se a
expressão da string for inválida ou representar um horário fora do
intervalo mínimo/máximo aceito, ocorrerá um erro.
Cast de tipos de data
O BigQuery é compatível com o cast de tipos de data para/de strings da maneira a seguir:
CAST(date_expression AS STRING)
CAST(string_expression AS DATE)
O cast de um tipo de data para uma string segue o formato YYYY-MM-DD
, independentemente do fuso horário. No caso do cast da string para a data, a string precisa seguir
o formato literal da data compatível, independentemente do fuso horário. Se a
expressão da string for inválida ou representar uma data fora do
intervalo mínimo/máximo aceito, ocorrerá um erro.
Cast de tipos de data e hora
O BigQuery é compatível com o cast de tipos de data e hora de/para strings da maneira a seguir:
CAST(datetime_expression AS STRING)
CAST(string_expression AS DATETIME)
O cast de um tipo de data e hora para uma string segue o formato YYYY-MM-DD HH:MM:SS
, não importa qual seja
o fuso horário. No cast da string para a data e hora, a
string precisa estar em conformidade com o formato literal de data e hora compatível, independentemente
do fuso horário. Se a expressão da string for inválida ou representar uma data e hora que
está fora do intervalo mínimo/máximo aceito, ocorrerá um erro.
Cast de tipos de carimbo de data/hora
O BigQuery é compatível com o cast de tipos de carimbo de data/hora para/de strings da maneira a seguir:
CAST(timestamp_expression AS STRING)
CAST(string_expression AS TIMESTAMP)
No cast de tipos de carimbo de data/hora para string, o carimbo é interpretado com o fuso horário padrão, que é o UTC. O número de dígitos de subsegundos produzidos depende do número de zeros à direita na parte de subsegundo. A função CAST truncará nenhum, três ou seis dígitos.
No cast da string para um carimbo de data/hora, string_expression
precisa seguir os
formatos literais do carimbo de data/hora compatível. Caso contrário, ocorrerá um erro no ambiente
de execução. O próprio string_expression
pode conter os time_zone
. Consulte fusos horários.
Se houver um fuso horário no string_expression
, ele será usado para conversão. Caso contrário, será usado o fuso horário padrão, UTC.
Se a string tiver menos de seis dígitos, ela será ampliada implicitamente.
Um erro será produzido se string_expression
for inválido, tiver mais de seis
dígitos de subsegundos (isto é, precisão maior do que microssegundos) ou representar um
horário fora do intervalo aceito do carimbo de data/hora.
Cast entre tipos de data e hora, data e carimbo de data/hora
O BigQuery é compatível com o cast entre tipos de data e hora, data e carimbo de data/hora, como exibido na tabela de regras de conversão.
CAST(date_expression AS TIMESTAMP)
CAST(timestamp_expression AS DATE)
O cast de uma data para um carimbo de data/hora interpreta date_expression
a partir da meia-noite
(início do dia) no fuso horário UTC padrão. O cast
de um carimbo de data/hora para uma data trunca efetivamente o carimbo de data/hora a partir do fuso
horário padrão.
CAST(datetime_expression AS TIMESTAMP)
CAST(timestamp_expression AS DATETIME)
O cast de uma data e hora para um carimbo de data/hora interpreta datetime_expression
a partir
da meia-noite (início do dia) no fuso horário UTC padrão.
Coerção
Se necessário, o BigQuery força o tipo de resultado de uma expressão para outro tipo, para corresponder às assinaturas da função. Por exemplo, se a função func() for definida para ter um único argumento do tipo INT64 e uma expressão for usada como um argumento que tenha um tipo de resultado FLOAT64, então o resultado da expressão será forçado para o tipo INT64 antes de func() ser calculado.
Coerção de literal
O BigQuery é compatível com as seguintes coerções de literal:
Tipo de dados de entrada | Tipo de dados de resultado | Notas |
---|---|---|
Literal STRING | DATE DATETIME TIME TIMESTAMP |
A coerção de literal é necessária quando o tipo de literal real é diferente do
tipo esperado pela função em questão. Por exemplo, se a função func()
tiver um argumento DATE e, em seguida, a expressão func("2014-09-27")
for válida porque o literal STRING "2014-09-27"
for forçado para DATE.
A conversão de literal é avaliada no momento da análise. Se o literal da entrada não for convertido com sucesso para o tipo de destino, ocorrerá um erro.
Observação: os literais de string não são forçados para tipos numéricos.
Coerção de parâmetro
O BigQuery é compatível com as seguintes coerções de parâmetro:
Tipo de dados de entrada | Tipo de dados de resultado |
---|---|
Parâmetro STRING |
Se o valor do parâmetro não puder ser forçado com sucesso para o tipo de destino, ocorrerá um erro.
Funções de conversão adicionais
O BigQuery oferece as seguintes funções adicionais de conversão:
Funções de agregação
A função de agregação resume as linhas de um grupo em um único valor. COUNT
, MIN
e MAX
são exemplos de funções de agregação.
SELECT COUNT(*) as total_count, COUNT(fruit) as non_null_count,
MIN(fruit) as min, MAX(fruit) as max
FROM (SELECT NULL as fruit UNION ALL
SELECT "apple" as fruit UNION ALL
SELECT "pear" as fruit UNION ALL
SELECT "orange" as fruit)
+-------------+----------------+-------+------+
| total_count | non_null_count | min | max |
+-------------+----------------+-------+------+
| 4 | 3 | apple | pear |
+-------------+----------------+-------+------+
Quando usados em conjunto com uma cláusula GROUP BY
, os grupos resumidos normalmente têm pelo menos uma linha. Quando o SELECT
associado não tem cláusula GROUP BY
ou quando determinados modificadores de função de agregação filtram linhas do grupo para serem resumidos, é possível que a função de agregação precise resumir um grupo vazio. Nesse caso, as funções COUNT
e COUNTIF
retornam 0
, enquanto todas as outras funções agregadas retornam NULL
.
As seções a seguir descrevem as funções de agregação compatíveis com o BigQuery.
ANY_VALUE
ANY_VALUE(expression) [OVER (...)]
Descrição
Retorna expression
para alguma linha escolhida do grupo. A escolha da linha é um processo
não determinístico e não aleatório. Retorna NULL
quando a entrada não produz
linhas. Retorna NULL
quando expression
é NULL
para todas as linhas no grupo.
ANY_VALUE
se comporta como se RESPECT NULLS
fosse especificado;
as linhas em que expression
é NULL
são consideradas e podem ser selecionadas.
Tipos de argumentos compatíveis
Qualquer um
Cláusula opcional
OVER
: especifica uma janela. Consulte Funções analíticas.
Tipos de dados retornados
Corresponde aos tipos de dados de entrada.
Exemplos
SELECT ANY_VALUE(fruit) as any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;
+-----------+
| any_value |
+-----------+
| apple |
+-----------+
SELECT
fruit,
ANY_VALUE(fruit) OVER (ORDER BY LENGTH(fruit) ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;
+--------+-----------+
| fruit | any_value |
+--------+-----------+
| pear | pear |
| apple | pear |
| banana | apple |
+--------+-----------+
ARRAY_AGG
ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS]
[ORDER BY key [{ASC|DESC}] [, ... ]] [LIMIT n])
[OVER (...)]
Descrição
Retorna um ARRAY de valores expression
.
Tipos de argumentos compatíveis
Todos os tipos de dados, exceto ARRAY.
Cláusulas opcionais
As cláusulas são aplicadas na seguinte ordem:
OVER
: especifica uma janela. Consulte Funções analíticas. No momento, esta cláusula é incompatível com todas as outras cláusulas emARRAY_AGG()
.DISTINCT
: cada valor distinto deexpression
é agregado apenas uma vez no resultado.IGNORE NULLS
ouRESPECT NULLS
: seIGNORE NULLS
for especificado, os valoresNULL
serão excluídos do resultado. SeRESPECT NULLS
ou se nenhum for especificado, os valoresNULL
serão incluídos no resultado. Um erro será gerado se uma matriz no resultado da consulta final contiver um elementoNULL
.ORDER BY
: especifica a ordem dos valores.- A direção padrão de cada chave de classificação é
ASC
. - NULLs: no contexto da cláusula
ORDER BY
, NULLs são os valores mínimos possíveis; isto é, NULLs aparecem primeiro em listagensASC
e, por último, em listagensDESC
. - Tipos de dados de ponto flutuante: consulte Semântica de ponto flutuante para ler sobre classificação e agrupamento.
- Se
DISTINCT
também for especificado, a chave de classificação deverá ser igual aexpression
. - Se
ORDER BY
não for especificado, a ordem dos elementos na matriz de saída será não determinística, o que significa que você poderá receber um resultado diferente sempre que usar essa função.
- A direção padrão de cada chave de classificação é
LIMIT
: especifica o número máximo de entradasexpression
no resultado. O limiten
precisa ser uma constante INT64.
Tipos de dados retornados
ARRAY
Se houver zero linhas de entrada, essa função retornará NULL
.
Exemplos
SELECT FORMAT("%T", ARRAY_AGG(x)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
+-------------------------+
| array_agg |
+-------------------------+
| [2, 1, -2, 3, -2, 1, 2] |
+-------------------------+
SELECT FORMAT("%T", ARRAY_AGG(DISTINCT x)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
+---------------+
| array_agg |
+---------------+
| [2, 1, -2, 3] |
+---------------+
SELECT FORMAT("%T", ARRAY_AGG(x IGNORE NULLS)) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;
+-------------------+
| array_agg |
+-------------------+
| [1, -2, 3, -2, 1] |
+-------------------+
SELECT FORMAT("%T", ARRAY_AGG(x ORDER BY ABS(x))) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
+-------------------------+
| array_agg |
+-------------------------+
| [1, 1, 2, -2, -2, 2, 3] |
+-------------------------+
SELECT FORMAT("%T", ARRAY_AGG(x LIMIT 5)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
+-------------------+
| array_agg |
+-------------------+
| [2, 1, -2, 3, -2] |
+-------------------+
SELECT FORMAT("%T", ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY x LIMIT 2)) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;
+-----------+
| array_agg |
+-----------+
| [-2, 1] |
+-----------+
SELECT
x,
FORMAT("%T", ARRAY_AGG(x) OVER (ORDER BY ABS(x))) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
+----+-------------------------+
| x | array_agg |
+----+-------------------------+
| 1 | [1, 1] |
| 1 | [1, 1] |
| 2 | [1, 1, 2, -2, -2, 2] |
| -2 | [1, 1, 2, -2, -2, 2] |
| -2 | [1, 1, 2, -2, -2, 2] |
| 2 | [1, 1, 2, -2, -2, 2] |
| 3 | [1, 1, 2, -2, -2, 2, 3] |
+----+-------------------------+
ARRAY_CONCAT_AGG
ARRAY_CONCAT_AGG(expression [ORDER BY key [{ASC|DESC}] [, ... ]] [LIMIT n])
Descrição
Concatena elementos de expression
do tipo ARRAY, retornando um único ARRAY como resultado. Essa função ignora matrizes de entrada NULL, mas respeita os elementos NULL em matrizes de entrada diferentes de NULL. No entanto, um erro será gerado se houver um elemento NULL em uma matriz no resultado da consulta final.
Tipos de argumentos compatíveis
ARRAY
Cláusulas opcionais
As cláusulas são aplicadas na seguinte ordem:
ORDER BY
: especifica a ordem dos valores.- A direção padrão de cada chave de classificação é
ASC
. - A ordem da matriz não é compatível e, portanto, a chave de classificação não pode ser igual a
expression
. - NULLs: no contexto da cláusula
ORDER BY
, NULLs são os valores mínimos possíveis; isto é, NULLs aparecem primeiro em listagensASC
e, por último, em listagensDESC
. - Tipos de dados de ponto flutuante: consulte Semântica de ponto flutuante para ler sobre classificação e agrupamento.
- Se
ORDER BY
não for especificado, a ordem dos elementos na matriz de saída será não determinística, o que significa que você poderá receber um resultado diferente sempre que usar essa função.
- A direção padrão de cada chave de classificação é
LIMIT
: especifica o número máximo de entradasexpression
no resultado. O limite é aplicado ao número de matrizes de entrada, e não ao número de elementos nas matrizes. Uma matriz vazia conta como 1. Uma matriz NULL não é contada. O limiten
precisa ser uma constante INT64.
Tipos de dados retornados
ARRAY
Retorna NULL
se não houver nenhuma linha de entrada ou expression
for avaliado como NULL para todas as linhas.
Exemplos
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x)) AS array_concat_agg FROM (
SELECT [NULL, 1, 2, 3, 4] AS x
UNION ALL SELECT NULL
UNION ALL SELECT [5, 6]
UNION ALL SELECT [7, 8, 9]
);
+-----------------------------------+
| array_concat_agg |
+-----------------------------------+
| [NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9] |
+-----------------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x))) AS array_concat_agg FROM (
SELECT [1, 2, 3, 4] AS x
UNION ALL SELECT [5, 6]
UNION ALL SELECT [7, 8, 9]
);
+-----------------------------------+
| array_concat_agg |
+-----------------------------------+
| [5, 6, 7, 8, 9, 1, 2, 3, 4] |
+-----------------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x LIMIT 2)) AS array_concat_agg FROM (
SELECT [1, 2, 3, 4] AS x
UNION ALL SELECT [5, 6]
UNION ALL SELECT [7, 8, 9]
);
+--------------------------+
| array_concat_agg |
+--------------------------+
| [1, 2, 3, 4, 5, 6] |
+--------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x) LIMIT 2)) AS array_concat_agg FROM (
SELECT [1, 2, 3, 4] AS x
UNION ALL SELECT [5, 6]
UNION ALL SELECT [7, 8, 9]
);
+------------------+
| array_concat_agg |
+------------------+
| [5, 6, 7, 8, 9] |
+------------------+
AVG
AVG([DISTINCT] expression) [OVER (...)]
Descrição
Retorna a média de valores de entrada NULL
, ou NaN
se a entrada tiver um NaN
.
Tipos de argumentos compatíveis
Qualquer tipo de entrada numérica, como INT64. Para tipos de entrada de ponto flutuante, o resultado retornado não é determinista. Isso significa que o resultado pode ser diferente sempre que essa função for usada.
Cláusulas opcionais
As cláusulas são aplicadas na seguinte ordem:
OVER
: especifica uma janela. Consulte Funções analíticas. No momento, esta cláusula é incompatível com todas as outras cláusulas emAVG()
.DISTINCT
: cada valor distinto deexpression
é agregado apenas uma vez no resultado.
Tipos de dados retornados
- NUMERIC caso o tipo de entrada seja NUMERIC.
- FLOAT64
Exemplos
SELECT AVG(x) as avg
FROM UNNEST([0, 2, 4, 4, 5]) as x;
+-----+
| avg |
+-----+
| 3 |
+-----+
SELECT AVG(DISTINCT x) AS avg
FROM UNNEST([0, 2, 4, 4, 5]) AS x;
+------+
| avg |
+------+
| 2.75 |
+------+
SELECT
x,
AVG(x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg
FROM UNNEST([0, 2, NULL, 4, 4, 5]) AS x;
+------+------+
| x | avg |
+------+------+
| NULL | NULL |
| 0 | 0 |
| 2 | 1 |
| 4 | 3 |
| 4 | 4 |
| 5 | 4.5 |
+------+------+
BIT_AND
BIT_AND(expression)
Descrição
Executa uma operação bit a bit AND em expression
e retorna o resultado.
Tipos de argumentos compatíveis
- INT64
Tipos de dados retornados
INT64
Exemplos
SELECT BIT_AND(x) as bit_and FROM UNNEST([0xF001, 0x00A1]) as x;
+---------+
| bit_and |
+---------+
| 1 |
+---------+
BIT_OR
BIT_OR(expression)
Descrição
Executa uma operação bit a bit OR em expression
e retorna o resultado.
Tipos de argumentos compatíveis
- INT64
Tipos de dados retornados
INT64
Exemplos
SELECT BIT_OR(x) as bit_or FROM UNNEST([0xF001, 0x00A1]) as x;
+--------+
| bit_or |
+--------+
| 61601 |
+--------+
BIT_XOR
BIT_XOR([DISTINCT] expression)
Descrição
Executa uma operação bit a bit XOR em expression
e retorna o resultado.
Tipos de argumentos compatíveis
- INT64
Cláusula opcional
DISTINCT
: cada valor distinto de expression
é agregado apenas uma vez no resultado.
Tipos de dados retornados
INT64
Exemplos
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([5678, 1234]) AS x;
+---------+
| bit_xor |
+---------+
| 4860 |
+---------+
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;
+---------+
| bit_xor |
+---------+
| 5678 |
+---------+
SELECT BIT_XOR(DISTINCT x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;
+---------+
| bit_xor |
+---------+
| 4860 |
+---------+
COUNT
1.
COUNT(*) [OVER (...)]
2.
COUNT([DISTINCT] expression) [OVER (...)]
Descrição
- Retorna o número de linhas na entrada.
- Retorna o número de linhas com
expression
avaliado para qualquer valor diferente deNULL
.
Tipos de argumentos compatíveis
expression
pode ser qualquer tipo de dado.
Cláusulas opcionais
As cláusulas são aplicadas na seguinte ordem:
OVER
: especifica uma janela. Consulte Funções analíticas.DISTINCT
: cada valor distinto deexpression
é agregado apenas uma vez no resultado.
Tipos de dados retornados
INT64
Exemplos
SELECT
COUNT(*) AS count_star,
COUNT(DISTINCT x) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;
+------------+--------------+
| count_star | count_dist_x |
+------------+--------------+
| 4 | 3 |
+------------+--------------+
SELECT
x,
COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
COUNT(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;
+------+------------+--------------+
| x | count_star | count_dist_x |
+------+------------+--------------+
| 1 | 3 | 2 |
| 4 | 3 | 2 |
| 4 | 3 | 2 |
| 5 | 1 | 1 |
+------+------------+--------------+
SELECT
x,
COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
COUNT(x) OVER (PARTITION BY MOD(x, 3)) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;
+------+------------+---------+
| x | count_star | count_x |
+------+------------+---------+
| NULL | 1 | 0 |
| 1 | 3 | 3 |
| 4 | 3 | 3 |
| 4 | 3 | 3 |
| 5 | 1 | 1 |
+------+------------+---------+
COUNTIF
COUNTIF(expression) [OVER (...)]
Descrição
Retorna a contagem de valores TRUE
para expression
. Retorna 0
se houver
zero linhas de entrada ou se expression
for avaliado como FALSE
ou NULL
para todas as linhas.
Tipos de argumentos compatíveis
BOOL
Cláusula opcional
OVER
: especifica uma janela. Consulte Funções analíticas.
Tipos de dados retornados
INT64
Exemplos
SELECT COUNTIF(x<0) AS num_negative, COUNTIF(x>0) AS num_positive
FROM UNNEST([5, -2, 3, 6, -10, -7, 4, 0]) AS x;
+--------------+--------------+
| num_negative | num_positive |
+--------------+--------------+
| 3 | 4 |
+--------------+--------------+
SELECT
x,
COUNTIF(x<0) OVER (ORDER BY ABS(x) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS num_negative
FROM UNNEST([5, -2, 3, 6, -10, NULL, -7, 4, 0]) AS x;
+------+--------------+
| x | num_negative |
+------+--------------+
| NULL | 0 |
| 0 | 1 |
| -2 | 1 |
| 3 | 1 |
| 4 | 0 |
| 5 | 0 |
| 6 | 1 |
| -7 | 2 |
| -10 | 2 |
+------+--------------+
LOGICAL_AND
LOGICAL_AND(expression)
Descrição
Retorna a lógica AND de todas as expressões diferentes de NULL
. Retorna NULL
, se não houver nenhuma linha de entrada ou expression
for avaliado como NULL
para todas as linhas.
Tipos de argumentos compatíveis
BOOL
Tipos de dados retornados
BOOL
Exemplos
SELECT LOGICAL_AND(x) AS logical_and FROM UNNEST([true, false, true]) AS x;
+-------------+
| logical_and |
+-------------+
| false |
+-------------+
LOGICAL_OR
LOGICAL_OR(expression)
Descrição
Retorna a lógica OR de todas as expressões diferentes de NULL
. Retorna NULL
, se não houver nenhuma linha de entrada ou expression
for avaliado como NULL
para todas as linhas.
Tipos de argumentos compatíveis
BOOL
Tipos de dados retornados
BOOL
Exemplos
SELECT LOGICAL_OR(x) AS logical_or FROM UNNEST([true, false, true]) AS x;
+------------+
| logical_or |
+------------+
| true |
+------------+
MAX
MAX(expression) [OVER (...)]
Descrição
Retorna o valor máximo de expressões não NULL
. Retorna NULL
, se não houver nenhuma linha de entrada ou expression
for avaliado como NULL
para todas as linhas.
Retorna NaN
se a entrada contiver um NaN
.
Tipos de argumentos compatíveis
Qualquer tipo de dado, exceto: ARRAY
STRUCT
GEOGRAPHY
Cláusula opcional
OVER
: especifica uma janela. Consulte Funções analíticas.
Tipos de dados retornados
Igual ao tipo de dados usado como os valores da entrada.
Exemplos
SELECT MAX(x) AS max
FROM UNNEST([8, 37, 4, 55]) AS x;
+-----+
| max |
+-----+
| 55 |
+-----+
SELECT x, MAX(x) OVER (PARTITION BY MOD(x, 2)) AS max
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;
+------+------+
| x | max |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8 | 8 |
| 4 | 8 |
| 37 | 55 |
| 55 | 55 |
+------+------+
MIN
MIN(expression) [OVER (...)]
Descrição
Retorna o valor mínimo de expressões não NULL
. Retorna NULL
, se não houver nenhuma linha de entrada ou expression
for avaliado como NULL
para todas as linhas.
Retorna NaN
se a entrada contiver um NaN
.
Tipos de argumentos compatíveis
Qualquer tipo de dado, exceto: ARRAY
STRUCT
GEOGRAPHY
Cláusula opcional
OVER
: especifica uma janela. Consulte Funções analíticas.
Tipos de dados retornados
Igual ao tipo de dados usado como os valores da entrada.
Exemplos
SELECT MIN(x) AS min
FROM UNNEST([8, 37, 4, 55]) AS x;
+-----+
| min |
+-----+
| 4 |
+-----+
SELECT x, MIN(x) OVER (PARTITION BY MOD(x, 2)) AS min
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;
+------+------+
| x | min |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8 | 4 |
| 4 | 4 |
| 37 | 37 |
| 55 | 37 |
+------+------+
STRING_AGG
STRING_AGG([DISTINCT] expression [, delimiter] [ORDER BY key [{ASC|DESC}] [, ... ]] [LIMIT n])
[OVER (...)]
Descrição
A concatenação de valores não nulos retorna um valor STRING ou BYTES.
Se um delimiter
for especificado, os valores concatenados serão separados por esse delimitador; caso contrário, uma vírgula será usada como um delimitador.
Tipos de argumentos compatíveis
STRING BYTES
Cláusulas opcionais
As cláusulas são aplicadas na seguinte ordem:
OVER
: especifica uma janela. Consulte Funções analíticas. No momento, esta cláusula é incompatível com todas as outras cláusulas emSTRING_AGG()
.DISTINCT
: cada valor distinto deexpression
é agregado apenas uma vez no resultado.ORDER BY
: especifica a ordem dos valores.- A direção padrão de cada chave de classificação é
ASC
. - NULLs: no contexto da cláusula
ORDER BY
, NULLs são os valores mínimos possíveis; isto é, NULLs aparecem primeiro em listagensASC
e, por último, em listagensDESC
. - Tipos de dados de ponto flutuante: consulte Semântica de ponto flutuante para ler sobre classificação e agrupamento.
- Se
DISTINCT
também for especificado, a chave de classificação deverá ser igual aexpression
. - Se
ORDER BY
não for especificado, a ordem dos elementos na matriz de saída será não determinística, o que significa que você poderá receber um resultado diferente sempre que usar essa função.
- A direção padrão de cada chave de classificação é
LIMIT
: especifica o número máximo de entradasexpression
no resultado. O limite é aplicado ao número de strings de entrada, e não ao número de caracteres ou bytes nas entradas. Uma string vazia conta como 1. Uma string NULL não é contada. O limiten
precisa ser uma constante INT64.
Tipos de dados retornados
STRING BYTES
Exemplos
SELECT STRING_AGG(fruit) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;
+------------------------+
| string_agg |
+------------------------+
| apple,pear,banana,pear |
+------------------------+
SELECT STRING_AGG(fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+------------------------------+
| string_agg |
+------------------------------+
| apple & pear & banana & pear |
+------------------------------+
SELECT STRING_AGG(DISTINCT fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+-----------------------+
| string_agg |
+-----------------------+
| apple & pear & banana |
+-----------------------+
SELECT STRING_AGG(fruit, " & " ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+------------------------------+
| string_agg |
+------------------------------+
| pear & pear & apple & banana |
+------------------------------+
SELECT STRING_AGG(fruit, " & " LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+--------------+
| string_agg |
+--------------+
| apple & pear |
+--------------+
SELECT STRING_AGG(DISTINCT fruit, " & " ORDER BY fruit DESC LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+---------------+
| string_agg |
+---------------+
| pear & banana |
+---------------+
SELECT
fruit,
STRING_AGG(fruit, " & ") OVER (ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;
+--------+------------------------------+
| fruit | string_agg |
+--------+------------------------------+
| NULL | NULL |
| pear | pear & pear |
| pear | pear & pear |
| apple | pear & pear & apple |
| banana | pear & pear & apple & banana |
+--------+------------------------------+
SUM
SUM([DISTINCT] expression) [OVER (...)]
Descrição
Retorna a soma de valores não nulos.
Se a expressão for um valor de ponto flutuante, a soma não será determinista. Isso significa que o resultado pode ser diferente sempre que essa função for usada.
Tipos de argumentos compatíveis
Quaisquer tipos de dados numéricos compatíveis.
Cláusulas opcionais
As cláusulas são aplicadas na seguinte ordem:
OVER
: especifica uma janela. Consulte Funções analíticas.DISTINCT
: cada valor distinto deexpression
é agregado apenas uma vez no resultado.
Tipos de dados retornados
- Retorna INT64, se a entrada for um número inteiro.
- Retorna NUMERIC, se o tipo de entrada for NUMERIC.
- Retorna FLOAT64, se a entrada for um valor de ponto flutuante.
Retorna NULL
, se a entrada contiver apenas NULL
s.
Retorna NULL
, se a entrada não contiver linhas.
Retorna Inf
se a entrada contiver Inf
.
Retorna -Inf
se a entrada contiver -Inf
.
Retorna NaN
se a entrada contiver um NaN
.
Retorna NaN
se a entrada contiver uma combinação de Inf
e -Inf
.
Exemplos
SELECT SUM(x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
+-----+
| sum |
+-----+
| 25 |
+-----+
SELECT SUM(DISTINCT x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
+-----+
| sum |
+-----+
| 15 |
+-----+
SELECT
x,
SUM(x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
+---+-----+
| x | sum |
+---+-----+
| 3 | 6 |
| 3 | 6 |
| 1 | 10 |
| 4 | 10 |
| 4 | 10 |
| 1 | 10 |
| 2 | 9 |
| 5 | 9 |
| 2 | 9 |
+---+-----+
SELECT
x,
SUM(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
+---+-----+
| x | sum |
+---+-----+
| 3 | 3 |
| 3 | 3 |
| 1 | 5 |
| 4 | 5 |
| 4 | 5 |
| 1 | 5 |
| 2 | 7 |
| 5 | 7 |
| 2 | 7 |
+---+-----+
SELECT SUM(x) AS sum
FROM UNNEST([]) AS x;
+------+
| sum |
+------+
| NULL |
+------+
Funções de agregação estatística
O BigQuery é compatível com as funções de agregação estatística a seguir.
CORR
CORR(X1, X2) [OVER (...)]
Descrição
Retorna o coeficiente de correlação de Pearson de um grupo de pares de números. O primeiro número de cada par é a variável dependente e o segundo número é a variável independente.
O resultado retornado está entre -1
e 1
. Um resultado igual a 0
indica que não há correlação.
Essa função ignora pares de entrada que tenham um ou mais valores NULL. Se houver menos de dois pares de entrada sem valores NULL, a função retornará NULL.
Tipos de entradas compatíveis
FLOAT64
Cláusula opcional
OVER
: especifica uma janela. Consulte Funções analíticas.
Tipo de dados retornados
FLOAT64
COVAR_POP
COVAR_POP(X1, X2) [OVER (...)]
Descrição
Retorna a covariância da população de pares de números. O primeiro número é a variável dependente e o segundo é a variável independente. O resultado retornado está entre -Inf
e +Inf
.
Essa função ignora pares de entrada que tenham um ou mais valores NULL. Se não houver um par de entrada sem valores NULL, esta função retornará NULL. Se houver exatamente um par de entrada sem valores NULL, esta função retornará 0.
Tipos de entradas compatíveis
FLOAT64
Cláusula opcional
OVER
: especifica uma janela. Consulte Funções analíticas.
Tipo de dados retornados
FLOAT64
COVAR_SAMP
COVAR_SAMP(X1, X2) [OVER (...)]
Descrição
Retorna a covariância de amostra de um conjunto de pares de números. O primeiro número é a variável dependente e o segundo é a variável independente. O resultado retornado está entre -Inf
e +Inf
.
Essa função ignora pares de entrada que tenham um ou mais valores NULL. Se houver menos de dois pares de entrada sem valores NULL, a função retornará NULL.
Tipos de entradas compatíveis
FLOAT64
Cláusula opcional
OVER
: especifica uma janela. Consulte Funções analíticas.
Tipo de dados retornados
FLOAT64
STDDEV_POP
STDDEV_POP([DISTINCT] expression) [OVER (...)]
Descrição
Retorna o desvio padrão polarizado da população dos valores. O resultado retornado está entre 0
e +Inf
.
Essa função ignora as entradas NULL. Se todas as entradas forem ignoradas, ela retornará NULL.
Mas se ela receber uma única entrada diferente de NULL, retornará 0
.
Tipos de entradas compatíveis
FLOAT64
Cláusulas opcionais
As cláusulas são aplicadas na seguinte ordem:
OVER
: especifica uma janela. Consulte Funções analíticas. No momento, esta cláusula é incompatível com todas as outras cláusulas emSTDDEV_POP()
.DISTINCT
: cada valor distinto deexpression
é agregado apenas uma vez no resultado.
Tipo de dados retornados
FLOAT64
STDDEV_SAMP
STDDEV_SAMP([DISTINCT] expression) [OVER (...)]
Descrição
Retorna o desvio padrão polarizado de amostra dos valores. O resultado retornado está entre 0
e +Inf
.
Essa função ignora as entradas NULL. Se houver menos de duas entradas diferentes de NULL, ela retornará NULL.
Tipos de entradas compatíveis
FLOAT64
Cláusulas opcionais
As cláusulas são aplicadas na seguinte ordem:
OVER
: especifica uma janela. Consulte Funções analíticas. No momento, esta cláusula é incompatível com todas as outras cláusulas emSTDDEV_SAMP()
.DISTINCT
: cada valor distinto deexpression
é agregado apenas uma vez no resultado.
Tipo de dados retornados
FLOAT64
STDDEV
STDDEV([DISTINCT] expression) [OVER (...)]
Descrição
Um sinônimo de STDDEV_SAMP.
VAR_POP
VAR_POP([DISTINCT] expression) [OVER (...)]
Descrição
Retorna a variância polarizada da população dos valores. O resultado retornado está entre 0
e +Inf
.
Essa função ignora as entradas NULL. Se todas as entradas forem ignoradas, ela retornará NULL.
Mas se ela receber uma única entrada diferente de NULL, retornará 0
.
Tipos de entradas compatíveis
FLOAT64
Cláusulas opcionais
As cláusulas são aplicadas na seguinte ordem:
OVER
: especifica uma janela. Consulte Funções analíticas. No momento, esta cláusula é incompatível com todas as outras cláusulas emVAR_POP()
.DISTINCT
: cada valor distinto deexpression
é agregado apenas uma vez no resultado.
Tipo de dados retornados
FLOAT64
VAR_SAMP
VAR_SAMP([DISTINCT] expression) [OVER (...)]
Descrição
Retorna a variância não polarizada de amostra dos valores. O resultado retornado está entre 0
e +Inf
.
Essa função ignora as entradas NULL. Se houver menos de duas entradas diferentes de NULL, ela retornará NULL.
Tipos de entradas compatíveis
FLOAT64
Cláusulas opcionais
As cláusulas são aplicadas na seguinte ordem:
OVER
: especifica uma janela. Consulte Funções analíticas. No momento, esta cláusula é incompatível com todas as outras cláusulas emVAR_SAMP()
.DISTINCT
: cada valor distinto deexpression
é agregado apenas uma vez no resultado.
Tipo de dados retornados
FLOAT64
VARIANCE
VARIANCE([DISTINCT] expression) [OVER (...)]
Descrição
Um sinônimo de VAR_SAMP.
Funções de agregação aproximada
As funções de agregação aproximadas são escaláveis no que diz respeito ao uso e tempo da memória. No entanto, elas produzem resultados aproximados em vez de exatos. Essas funções
normalmente exigem menos memória do que a função de agregação exata,
como COUNT(DISTINCT ...)
, mas também apresentam incerteza estatística.
Isso torna a agregação aproximada apropriada para streamings grandes de dados para
os quais o uso de memória linear é impraticável, bem como para dados que
já são aproximados.
As funções de agregação aproximadas nesta seção funcionam diretamente nos dados de entrada, em vez de uma estimativa intermediária dos dados. Essas funções não permitem que os usuários especifiquem a precisão da estimativa com sketches. Se quiser especificar a precisão com sketches, consulte:
- Funções do HyperLogLog++ para estimar a cardinalidade.
APPROX_COUNT_DISTINCT
APPROX_COUNT_DISTINCT(expression)
Descrição
Retorna o resultado aproximado para COUNT(DISTINCT expression)
. O valor retornado é uma estimativa estatística e não necessariamente o valor real.
Essa função é menos exata que COUNT(DISTINCT expression)
, mas funciona melhor com entradas muito grandes.
Tipos de argumentos compatíveis
Qualquer tipo de dado, exceto:
ARRAY
STRUCT
Tipos de dados retornados
INT64
Exemplos
SELECT APPROX_COUNT_DISTINCT(x) as approx_distinct
FROM UNNEST([0, 1, 1, 2, 3, 5]) as x;
+-----------------+
| approx_distinct |
+-----------------+
| 5 |
+-----------------+
APPROX_QUANTIL
APPROX_QUANTILES([DISTINCT] expression, number [{IGNORE|RESPECT} NULLS])
Descrição
Retorna os limites aproximados para um grupo de valores expression
, sendo que number
representa quantos quantis serão criados. Essa função retorna uma matriz de elementos number
+ 1, em que o primeiro elemento é o mínimo aproximado e o último elemento é o máximo aproximado.
Tipos de argumentos compatíveis
expression
pode ser qualquer tipo de dado compatível, exceto:
ARRAY
STRUCT
number
precisa ser INT64.
Cláusulas opcionais
As cláusulas são aplicadas na seguinte ordem:
DISTINCT
: cada valor distinto deexpression
é agregado apenas uma vez no resultado.IGNORE NULLS
ouRESPECT NULLS
: seIGNORE NULLS
for especificado, os valoresNULL
serão excluídos do resultado. SeRESPECT NULLS
ou se nenhum for especificado, os valoresNULL
serão incluídos no resultado. Um erro será gerado se uma matriz no resultado da consulta final contiver um elementoNULL
.
Tipos de dados retornados
Um ARRAY do tipo especificado pelo parâmetro expression
.
Retorna NULL
se não houver nenhuma linha de entrada ou expression
for avaliado como NULL para todas as linhas.
Exemplos
SELECT APPROX_QUANTILES(x, 2) AS approx_quantiles
FROM UNNEST([1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;
+------------------+
| approx_quantiles |
+------------------+
| [1, 5, 10] |
+------------------+
SELECT APPROX_QUANTILES(x, 100)[OFFSET(90)] AS percentile_90
FROM UNNEST([1, 2, 3, 4, 5, 6, 7, 8, 9, 10]) AS x;
+---------------+
| percentile_90 |
+---------------+
| 9 |
+---------------+
SELECT APPROX_QUANTILES(DISTINCT x, 2) AS approx_quantiles
FROM UNNEST([1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;
+------------------+
| approx_quantiles |
+------------------+
| [1, 6, 10] |
+------------------+
SELECT FORMAT("%T", APPROX_QUANTILES(x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;
+------------------+
| approx_quantiles |
+------------------+
| [NULL, 4, 10] |
+------------------+
SELECT FORMAT("%T", APPROX_QUANTILES(DISTINCT x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;
+------------------+
| approx_quantiles |
+------------------+
| [NULL, 6, 10] |
+------------------+
APPROX_TOP_COUNT
APPROX_TOP_COUNT(expression, number)
Descrição
Retorna os elementos principais aproximados de expression
. O parâmetro number
especifica o número de elementos retornados.
Tipos de argumentos compatíveis
expression
pode ser de qualquer tipo de dado compatível com a cláusula GROUP BY
.
number
precisa ser INT64.
Tipos de dados retornados
Uma ARRAY do tipo STRUCT.
O STRUCT contém dois campos. O primeiro campo (chamado value
) contém um valor de entrada. O segundo campo (chamado count
) contém um INT64 que especifica o número de vezes que o valor foi retornado.
Retorna NULL
, se houver linhas de entrada igual a zero.
Exemplos
SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST(["apple", "apple", "pear", "pear", "pear", "banana"]) as x;
+-------------------------+
| approx_top_count |
+-------------------------+
| [{pear, 3}, {apple, 2}] |
+-------------------------+
Tratamento de NULL
APPROX_TOP_COUNT não ignora NULLs na entrada. Exemplo:
SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST([NULL, "pear", "pear", "pear", "apple", NULL]) as x;
+------------------------+
| approx_top_count |
+------------------------+
| [{pear, 3}, {NULL, 2}] |
+------------------------+
APPROX_TOP_SUM
APPROX_TOP_SUM(expression, weight, number)
Descrição
Retorna os elementos principais aproximados de expression
, com base na soma de um weight
atribuído. O parâmetro number
especifica o número de elementos retornados.
Se a entrada weight
for negativa ou NaN
, esta função retornará um erro.
Tipos de argumentos compatíveis
expression
pode ser de qualquer tipo de dado compatível com a cláusula GROUP BY
.
weight
precisa atender a uma das seguintes condições:
- INT64
- FLOAT64
number
precisa ser INT64.
Tipos de dados retornados
Uma ARRAY do tipo STRUCT.
O STRUCT contém dois campos: value
e sum
.
O campo value
contém o valor da expressão de entrada. O campo sum
é do mesmo tipo que weight
e é a soma aproximada do peso de entrada associado ao campo value
.
Retorna NULL
, se houver linhas de entrada igual a zero.
Exemplos
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([
STRUCT("apple" AS x, 3 AS weight),
("pear", 2),
("apple", 0),
("banana", 5),
("pear", 4)
]);
+--------------------------+
| approx_top_sum |
+--------------------------+
| [{pear, 6}, {banana, 5}] |
+--------------------------+
Tratamento de NULL
APPROX_TOP_SUM não ignora valores NULL para os parâmetros expression
e weight
.
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, NULL AS weight), ("pear", 0), ("pear", NULL)]);
+----------------------------+
| approx_top_sum |
+----------------------------+
| [{pear, 0}, {apple, NULL}] |
+----------------------------+
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, 2)]);
+-------------------------+
| approx_top_sum |
+-------------------------+
| [{NULL, 2}, {apple, 0}] |
+-------------------------+
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, NULL)]);
+----------------------------+
| approx_top_sum |
+----------------------------+
| [{apple, 0}, {NULL, NULL}] |
+----------------------------+
Funções HyperLogLog ++
O algoritmo do HyperLogLog ++ (HLL ++) estima a cardinalidade (em inglês) dos sketches. Se você não quiser trabalhar com sketches e não necessitar de precisão personalizada, considere usar funções de agregação aproximadas com precisão definida pelo sistema.
As funções do HLL ++ são funções de agregação aproximadas.
A agregação aproximada normalmente requer menos
memória do que a função de agregação exata,
como COUNT(DISTINCT)
, mas também apresenta incerteza estatística.
Isso torna as funções HLL ++ apropriadas para grandes streamings de dados para
os quais o uso de memória linear é impraticável, bem como para dados
já aproximados.
O BigQuery é compatível com as seguintes funções do HLL++:
HLL_COUNT.INIT
HLL_COUNT.INIT(input [, precision])
Descrição
Uma função de agregação que usa um ou mais valores input
e os agrega em um
sketch de HLL++ (em inglês). Cada sketch é representado usando o tipo de dados BYTES
. Em seguida, é possível mesclar os sketches usando HLL_COUNT.MERGE
ou HLL_COUNT.MERGE_PARTIAL
. Se nenhuma mesclagem for necessária, será possível extrair do rascunho a contagem final de valores distintos usando HLL_COUNT.EXTRACT
.
Essa função é compatível com um parâmetro opcional, precision
. Ele define a precisão da estimativa ao custo da memória adicional necessária para processar os sketches ou armazená-los no disco. A tabela a seguir mostra os valores de precisão permitidos, o tamanho máximo do sketch por grupo e o intervalo de confiança (CI, na sigla em inglês) de precisões típicas:
Precisão | Tamanho máx. do sketch (KiB) | CI de 65% | CI de 95% | CI de 99% |
---|---|---|---|---|
10 | 1 | ±1,63% | ±3,25% | ±6,50% |
11 | 2 | ±1,15% | ±2,30% | ±4,60% |
12 | 4 | ±0,81% | ±1,63% | ±3,25% |
13 | 8 | ±0,57% | ±1,15% | ±1,72% |
14 | 16 | ±0,41% | ±0,81% | ±1,22% |
15 (padrão) | 32 | ±0,29% | ±0,57% | ±0,86% |
16 | 64 | ±0,20% | ±0,41% | ±0,61% |
17 | 128 | ±0,14% | ±0,29% | ±0,43% |
18 | 256 | ±0,10% | ±0,20% | ±0,41% |
19 | 512 | ±0,07% | ±0,14% | ±0,29% |
20 | 1024 | ±0,05% | ±0,10% | ±0,20% |
21 | 2048 | ±0,04% | ±0,07% | ±0,14% |
22 | 4096 | ±0,03% | ±0,05% | ±0,10% |
23 | 8192 | ±0,02% | ±0,04% | ±0,07% |
24 | 16384 | ±0,01% | ±0,03% | ±0,05% |
Se a entrada for NULL, essa função retornará NULL.
Para mais informações, consulte HyperLogLog in Practice: Algorithmic Engineering of a State of The Art Cardinality Estimation Algorithm (em inglês).
Tipos de entradas compatíveis
INT64, NUMERIC, STRING e BYTES
Tipo de retorno
BYTES
Exemplo
SELECT
HLL_COUNT.INIT(respondent) AS respondents_hll,
flavor,
country
FROM UNNEST([
STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
(1, "Chocolate", "CH"),
(2, "Chocolate", "US"),
(2, "Strawberry", "US")])
GROUP BY flavor, country;
HLL_COUNT.MERGE
HLL_COUNT.MERGE(sketch)
Descrição
Uma função de agregação que retorna a cardinalidade de vários sketches de conjunto do HLL ++ calculando a união deles.
Cada sketch
precisa ter a mesma precisão e ser inicializado no mesmo tipo.
As tentativas de mesclar sketches com precisões ou tipos diferentes resultam em erro. Por exemplo, não é possível mesclar um sketch inicializado nos dados de INT64 com um inicializado nos dados de STRING.
Essa função ignora os valores NULL ao mesclar sketches. Se a mesclagem acontecer em linhas de zero ou somente em valores NULL, a função retornará 0
.
Tipos de entradas compatíveis
BYTES
Tipo de retorno
INT64
Exemplo
SELECT HLL_COUNT.MERGE(respondents_hll) AS num_respondents, flavor
FROM (
SELECT
HLL_COUNT.INIT(respondent) AS respondents_hll,
flavor,
country
FROM UNNEST([
STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
(1, "Chocolate", "CH"),
(2, "Chocolate", "US"),
(2, "Strawberry", "US")])
GROUP BY flavor, country)
GROUP BY flavor;
HLL_COUNT.MERGE_PARTIAL
HLL_COUNT.MERGE_PARTIAL(sketch)
Descrição
Uma função de agregação que usa uma ou mais entradas
sketch
do HLL ++ (em inglês)
e as mescla em um novo sketch.
Essa função retornará NULL, se não houver entrada ou se todas elas forem NULL.
Tipos de entradas compatíveis
BYTES
Tipo de retorno
BYTES
Exemplo
SELECT HLL_COUNT.MERGE_PARTIAL(respondents_hll) AS num_respondents, flavor
FROM (
SELECT
HLL_COUNT.INIT(respondent) AS respondents_hll,
flavor,
country
FROM UNNEST([
STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
(1, "Chocolate", "CH"),
(2, "Chocolate", "US"),
(2, "Strawberry", "US")])
GROUP BY flavor, country)
GROUP BY flavor;
HLL_COUNT.EXTRACT
HLL_COUNT.EXTRACT(sketch)
Descrição
Uma função escalar que extrai uma estimativa da cardinalidade de um único sketch do HLL++ (em inglês).
Se sketch
for NULL, essa função retornará uma estimativa de cardinalidade de 0
.
Tipos de entradas compatíveis
BYTES
Tipo de retorno
INT64
Exemplo
SELECT
flavor,
country,
HLL_COUNT.EXTRACT(respondents_hll) AS num_respondents
FROM (
SELECT
HLL_COUNT.INIT(respondent) AS respondents_hll,
flavor,
country
FROM UNNEST([
STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
(1, "Chocolate", "CH"),
(2, "Chocolate", "US"),
(2, "Strawberry", "US")])
GROUP BY flavor, country);
+------------+---------+-----------------+
| flavor | country | num_respondents |
+------------+---------+-----------------+
| Vanilla | CH | 1 |
| Chocolate | CH | 1 |
| Chocolate | US | 1 |
| Strawberry | US | 1 |
+------------+---------+-----------------+
Sobre o algoritmo HLL++
O algoritmo HLL++ (em inglês) é uma versão melhorada do algoritmo HLL (em inglês) que estima com mais precisão cardinalidades muito pequenas ou grandes. O algoritmo HLL++ inclui uma representação esparsa e de função de hash de 64-bits que reduz os requisitos de memória de estimativas cardinais pequenas e de correções de tendência empírica de estimativas cardinais pequenas.
Sobre sketches
Um sketch é um resumo de um streaming grande de dados. É possível extrair estatísticas de um sketch para estimar estatísticas particulares de dados originais ou mesclar sketches para resumir vários streamings de dados. Um sketch tem os seguintes recursos:
- Ela compacta dados brutos em uma representação de memória fixa.
- É assintoticamente menor que a entrada.
- É a forma serializada de uma estrutura de dados sub-linear na memória.
- Normalmente, requer menos memória do que a entrada usada para criá-lo.
Os sketches permitem a integração com outros sistemas. Por exemplo, é possível
criar sketches em aplicativos externos, como o Cloud Dataflow ou o
Apache Spark (em inglês) e consumi-los no BigQuery, ou vice-versa. Os sketches também permitem a criação de agregações intermediárias para
funções não aditivas, como COUNT(DISTINCT)
.
Funções de numeração
As seções a seguir descrevem as funções de numeração compatíveis com o BigQuery. As funções de numeração são um subconjunto de funções analíticas. Para uma explicação de como são funções analíticas, consulte Conceitos da função analítica. Para uma descrição de como são funções de numeração, consulte os Conceitos da função de numeração.
Requisitos da cláusula OVER
:
PARTITION BY
: opcional.ORDER BY
: obrigatório, exceto paraROW_NUMBER()
.window_frame_clause
: não permitido
RANK
Descrição
Retorna a classificação de ordinal (baseada em 1) de cada linha dentro da partição ordenada.
Todas as linhas dos pares recebem o mesmo valor da classificação. A linha ou o grupo de linhas de pares seguintes recebem um valor da classificação que aumenta baseado no número de pares com o valor da classificação anterior, em vez de DENSE_RANK
, que sempre aumenta em 1.
Tipos de argumentos compatíveis
INT64
DENSE_RANK
Descrição
Retorna a classificação de ordinal (baseada em 1) de cada linha dentro da partição da janela. Todas as linhas de pares recebem o mesmo valor da classificação. O valor seguinte é incrementado em um.
Tipos de argumentos compatíveis
INT64
PERCENT_RANK
Descrição
Retorna a classificação do percentil de uma linha definida como (RK-1)/(NR-1), na qual RK é o RANK
da linha e NR é o número de linhas na partição.
Retorna 0 se NR = 1.
Tipos de argumentos compatíveis
FLOAT64
CUME_DIST
Descrição
Retorna a classificação relativa de uma linha definida como NP/NR. NP é definido como o número de linhas que precedem ou são pares da linha atual. NR é o número de linhas da partição.
Tipos de argumentos compatíveis
FLOAT64
NTILE
NTILE(constant_integer_expression)
Descrição
Essa função divide as linhas em intervalos constant_integer_expression
com base na ordenação de linhas e retorna o número do intervalo com base em 1 que é atribuído a cada linha. O número de linhas nos intervalos pode diferir em 1, no máximo.
Os valores restantes, isto é, o número de linhas divididas por intervalos, são distribuídos um para cada intervalo, começando com o intervalo 1. Se constant_integer_expression
for avaliado como NULL, 0 ou negativo, um erro será fornecido.
Tipos de argumentos compatíveis
INT64
ROW_NUMBER
Descrição
Não requer a cláusula ORDER BY
. Retorna o ordinal da linha sequencial (baseado em 1) de cada linha de cada partição ordenada. Se a cláusula ORDER BY
não for especificada, o resultado será não determinístico.
Tipos de argumentos compatíveis
INT64
Funções de bit
O BigQuery é compatível com as funções Bit a seguir.
BIT_COUNT
BIT_COUNT(expression)
Descrição
A entrada, expression
, precisa ser um número inteiro ou BYTES.
Retorna o número de bits que estão definidos na entrada expression
.
No caso de números inteiros com sinal, isso significa o número de bits na forma de complemento de dois.
Tipo de dados retornados
INT64
Exemplo
SELECT a, BIT_COUNT(a) AS a_bits, FORMAT("%T", b) as b, BIT_COUNT(b) AS b_bits
FROM UNNEST([
STRUCT(0 AS a, b'' AS b), (0, b'\x00'), (5, b'\x05'), (8, b'\x00\x08'),
(0xFFFF, b'\xFF\xFF'), (-2, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFE'),
(-1, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF'),
(NULL, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF')
]) AS x;
+-------+--------+---------------------------------------------+--------+
| a | a_bits | b | b_bits |
+-------+--------+---------------------------------------------+--------+
| 0 | 0 | b"" | 0 |
| 0 | 0 | b"\x00" | 0 |
| 5 | 2 | b"\x05" | 2 |
| 8 | 1 | b"\x00\x08" | 1 |
| 65535 | 16 | b"\xff\xff" | 16 |
| -2 | 63 | b"\xff\xff\xff\xff\xff\xff\xff\xfe" | 63 |
| -1 | 64 | b"\xff\xff\xff\xff\xff\xff\xff\xff" | 64 |
| NULL | NULL | b"\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff" | 80 |
+-------+--------+---------------------------------------------+--------+
Funções matemáticas
Todas as funções matemáticas têm os seguintes comportamentos:
- Elas retornam
NULL
se algum dos parâmetros de entrada forNULL
. - Elas retornam
NaN
se algum dos argumentos forNaN
.
ABS
ABS(X)
Descrição
Calcula o valor absoluto. Retornará um erro caso o argumento seja um número inteiro e o valor de saída não possa ser representado como o mesmo tipo. Isso acontece apenas com o maior valor negativo da entrada, sem representação positiva. Retorna +inf
para um argumento +/-inf
.
SIGN
SIGN(X)
Descrição
Retorna -1, 0 ou +1 para argumentos negativo, zero e positivo, respectivamente.
Para argumentos de ponto flutuante, essa função não faz distinção entre zero positivo e negativo. Retorna NaN
para um argumento NaN
.
IS_INF
IS_INF(X)
Descrição
Retornará TRUE
se o valor for um infinito positivo ou negativo. Retorna NULL
para entradas NULL
.
IS_NAN
IS_NAN(X)
Descrição
Retorna TRUE
se o valor for NaN
. Retorna NULL
para entradas NULL
.
IEEE_DIVIDE
IEEE_DIVIDE(X, Y)
Descrição
Divide X por Y e nunca falha. Ela retorna FLOAT64
. Ao contrário do operador de divisão (/), não gera erros de divisão por zero ou estouro.
Casos especiais:
- Se o resultado estourar, retorna
+/-inf
. - Se Y=0 e X=0, retorna
NaN
. - Se Y=0 e X!=0, retorna
+/-inf
. - Se X=
+/-inf
e Y=+/-inf
, retornaNaN
.
O comportamento de IEEE_DIVIDE
é ilustrado com mais detalhes na tabela abaixo.
Casos especiais de IEEE_DIVIDE
A tabela a seguir lista os casos especiais de IEEE_DIVIDE
.
Tipos de dados do numerador (X) | Tipos de dados do denominador (Y) | Valor do resultado |
---|---|---|
Qualquer um, exceto 0 | 0 | +/-inf |
0 | 0 | NaN |
0 | NaN |
NaN |
NaN |
0 | NaN |
+/-inf |
+/-inf |
NaN |
RAND
RAND()
Descrição
Gera um valor pseudoaleatório do tipo FLOAT64 no intervalo de [0, 1), incluindo 0 e excluindo 1.
SQRT
SQRT(X)
Descrição
Calcula a raiz quadrada de X. Gera um erro se X for menor que 0. Retorna +inf
se X for +inf
.
POW
POW(X, Y)
Descrição
Retorna o valor de X elevado à potência de Y. Se o resultado estourar e não for representável, a função retornará um valor zero. Retornará um erro se uma das seguintes condições for verdadeira:
- X é um valor finito menor que 0, e Y é um número não inteiro
- X é 0, e Y é um valor finito menor que 0
O comportamento de POW()
é ilustrado com mais detalhes na tabela abaixo.
POWER
POWER(X, Y)
Descrição
Sinônimo de POW()
.
Casos especiais para POW(X, Y)
e POWER(X, Y)
Veja a seguir casos especiais para POW(X, Y)
e POWER(X, Y)
.
X | Y | POW(X, Y) ou POWER(X, Y) |
---|---|---|
1,0 | Qualquer valor, incluindo NaN |
1,0 |
qualquer um, incluindo NaN |
0 | 1,0 |
-1,0 | +/-inf |
1,0 |
ABS(X) < 1 | -inf |
+inf |
ABS(X) > 1 | -inf |
0 |
ABS(X) < 1 | +inf |
0 |
ABS(X) > 1 | +inf |
+inf |
-inf |
Y < 0 | 0 |
-inf |
Y > 0 | -inf se Y for um número inteiro ímpar, +inf caso contrário |
+inf |
Y < 0 | 0 |
+inf |
Y > 0 | +inf |
EXP
EXP(X)
Descrição
Calcula e à potência de X, também chamada de função exponencial natural. Se ocorrer um estouro negativo, essa função retornará zero. Um erro será gerado se o resultado estourar. Se X for +/-inf
, essa função retorna +inf
ou 0.
LN
LN(X)
Descrição
Calcula o logaritmo natural de X. Um erro será gerado se X for menor ou igual a zero. Se X for +inf
, essa função retorna +inf
.
LOG
LOG(X [, Y])
Descrição
Se apenas X estiver presente, LOG
será um sinônimo de LN
. Se Y também estiver presente, LOG
calculará o logaritmo de X na base Y. A função gera um erro nos seguintes casos:
- X é menor ou igual a zero
- Y é 1,0
- Y é menor ou igual a zero.
O comportamento de LOG(X, Y)
é ilustrado com mais detalhes na tabela abaixo.
Casos especiais de LOG(X, Y)
X | Y | LOG(X, Y) |
---|---|---|
-inf |
Qualquer valor | NaN |
Qualquer valor | +inf |
NaN |
+inf |
0,0 Y < 1,0 | -inf |
+inf |
Y > 1,0 | +inf |
LOG10
LOG10(X)
Descrição
Semelhante a LOG
, mas calcula o logaritmo de base 10.
GREATEST
GREATEST(X1,...,XN)
Descrição
Retorna NULL
se alguma das entradas for NULL
. Caso contrário, retorna NaN
se alguma das entradas for NaN
. Caso contrário, retorna o maior valor entre X1,…, XN de acordo com a comparação.
LEAST
LEAST(X1,...,XN)
Descrição
Retorna NULL
se alguma das entradas for NULL
. Retorna NaN
se alguma das entradas for NaN
. Caso contrário, retorna o menor valor entre X1,…, XN de acordo com a comparação.
DIV
DIV(X, Y)
Descrição
Retorna o resultado da divisão de inteiros de X por Y. Divisão por zero retorna um erro. Divisão por -1 pode estourar. Consulte a tabela abaixo para ver possíveis tipos de resultados.
SAFE_DIVIDE
SAFE_DIVIDE(X, Y)
Descrição
Equivalente ao operador de divisão (/
), mas retorna NULL
se ocorrer um erro como o de divisão por zero.
SAFE_MULTIPLY
SAFE_MULTIPLY(X, Y)
Descrição
Equivalente ao operador de multiplicação (*
), mas retorna NULL
se ocorrer um estouro.
SAFE_NEGATE
SAFE_NEGATE(X)
Descrição
Equivalente ao operador de menos unário (-
), mas retorna NULL
se ocorrer um estouro.
SAFE_ADD
SAFE_ADD(X, Y)
Descrição
Equivalente ao operador de adição (+
), mas retorna NULL
se ocorrer um estouro.
SAFE_SUBTRACT
SAFE_SUBTRACT(X, Y)
Descrição
Equivalente ao operador de subtração (-
), mas retorna NULL
se ocorrer um estouro.
MOD
MOD(X, Y)
Descrição
Função módulo: retorna o restante da divisão de X por Y. O valor retornado tem o mesmo sinal que X. Um erro será gerado se Y for 0. Consulte a tabela abaixo para ver possíveis tipos de resultados.
ROUND
ROUND(X [, N])
Descrição
Se apenas X estiver presente, ROUND
arredondará X para o inteiro mais próximo. Se N estiver presente, ROUND
arredondará X para N casas decimais após a vírgula decimal. Se N for negativo, ROUND
arredondará os dígitos à esquerda da vírgula decimal. Casos em que haja metades são arredondados para longe de zero. Gera um erro se ocorrer um estouro.
TRUNC
TRUNC(X [, N])
Descrição
Se apenas X estiver presente, TRUNC
arredondará X para o inteiro mais próximo cujo valor absoluto não seja maior que o valor absoluto de X. Se N também estiver presente, TRUNC
se comportará como ROUND(X, N)
, mas sempre arredondará para zero e nunca haverá estouro.
CEIL
CEIL(X)
Descrição
Retorna o menor valor integral (com o tipo FLOAT64) que não é inferior a X.
CEILING
CEILING(X)
Descrição
Sinônimo de CEIL(X)
FLOOR
FLOOR(X)
Descrição
Retorna o maior valor integral (com o tipo FLOAT64) que não é superior a X.
Exemplo de comportamento da função de arredondamento
Exemplo de comportamento de funções de arredondamento do BigQuery:
Entrada "X" | ROUND(X) | TRUNC(X) | CEIL(X) | FLOOR(X) |
---|---|---|---|---|
2,0 | 2,0 | 2,0 | 2,0 | 2,0 |
2,3 | 2,0 | 2,0 | 3,0 | 2,0 |
2,8 | 3,0 | 2,0 | 3,0 | 2,0 |
2,5 | 3,0 | 2,0 | 3,0 | 2,0 |
-2,3 | -2,0 | -2,0 | -2,0 | -3,0 |
-2,8 | -3,0 | -2,0 | -2,0 | -3,0 |
-2,5 | -3,0 | -2,0 | -2,0 | -3,0 |
0 | 0 | 0 | 0 | 0 |
+/-inf |
+/-inf |
+/-inf |
+/-inf |
+/-inf |
NaN |
NaN |
NaN |
NaN |
NaN |
COS
COS(X)
Descrição
Calcula o cosseno de X onde X é especificado em radianos. Nunca falha.
COSH
COSH(X)
Descrição
Calcula o cosseno hiperbólico de X onde X é especificado em radianos. Gera um erro se ocorrer um estouro.
ACOS
ACOS(X)
Descrição
Calcula o valor principal do cosseno inverso de X. O valor retornado está no intervalo [0,π]. Um erro será gerado se X for um valor fora do intervalo [-1, 1].
ACOSH
ACOSH(X)
Descrição
Calcula o cosseno hiperbólico inverso de X. Se X for um valor menor que 1, gera um erro.
SIN
SIN(X)
Descrição
Calcula o seno de X onde X é especificado em radianos. Nunca falha.
SINH
SINH(X)
Descrição
Calcula o seno hiperbólico de X, onde X é especificado em radianos. Gera um erro se ocorrer um estouro.
ASIN
ASIN(X)
Descrição
Calcula o valor principal do seno inverso de X. O valor retornado está no intervalo [-π/2, π/2]. Um erro será gerado se X estiver fora do intervalo [-1, 1].
ASINH
ASINH(X)
Descrição
Calcula o seno hiperbólico inverso de X. Não falha.
TAN
TAN(X)
Descrição
Calcula a tangente de X onde X é especificado em radianos. Gera um erro se ocorrer um estouro.
TANH
TANH(X)
Descrição
Calcula a tangente hiperbólica de X, onde X é especificado em radianos. Não falha.
ATAN
ATAN(X)
Descrição
Calcula o valor principal da tangente inversa de X. O valor retornado está no intervalo [-π/2, π/2]. Não falha.
ATANH
ATANH(X)
Descrição
Calcula a tangente hiperbólica inversa de X. Gera um erro se X estiver fora do intervalo [-1, 1].
ATAN2
ATAN2(Y, X)
Descrição
Calcula o valor principal da tangente inversa de Y/X usando os sinais dos dois argumentos para determinar o quadrante. O valor de retorno está no intervalo [-π, π]. O comportamento dessa função é ilustrado com mais detalhes na tabela abaixo.
Casos especiais de ATAN2()
Y | X | ATAN2(Y, X) |
---|---|---|
NaN |
Qualquer valor | NaN |
Qualquer valor | NaN |
NaN |
0 | 0 | 0, π ou -π, dependendo do sinal de X e Y |
Valor finito | -inf |
π ou -π, dependendo do sinal de Y |
Valor finito | +inf |
0 |
+/-inf |
Valor finito | π/2 ou π/2, dependendo do sinal de Y |
+/-inf |
-inf |
¾π ou -¾π, dependendo do sinal de Y |
+/-inf |
+inf |
π/4 ou -π/4, dependendo do sinal de Y |
Casos especiais de funções trigonométricas e hiperbólicas de arredondamento
X | COS(X) | COSH(X) | ACOS(X) | ACOSH(X) | SIN(X) | SINH(X) | ASIN(X) | ASINH(X) | TAN(X) | TANH(X) | ATAN(X) | ATANH(X) |
---|---|---|---|---|---|---|---|---|---|---|---|---|
+/-inf |
NaN |
=+inf |
NaN |
=+inf |
NaN |
=+inf |
NaN |
=+inf |
NaN |
=+1,0 | π/2 | NaN |
-inf |
NaN |
=+inf |
NaN |
NaN |
NaN |
-inf |
NaN |
-inf |
NaN |
-1,0 | -π/2 | NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
RANGE_BUCKET
RANGE_BUCKET(point, boundaries_array)
Descrição
RANGE_BUCKET
verifica uma matriz classificada e retorna a posição 0 do limite superior do ponto. Isso pode ser útil se você precisar agrupar seus dados para criar partições, histogramas, regras definidas pelo negócio e muito mais.
RANGE_BUCKET
segue as seguintes regras:
Se o ponto existir na matriz, o índice do próximo valor maior será retornado.
RANGE_BUCKET(20, [0, 10, 20, 30, 40]) -- 3 is return value RANGE_BUCKET(20, [0, 10, 20, 20, 40, 40]) -- 4 is return value
Se o ponto não existir na matriz, mas ficar entre dois valores, o índice do valor maior será retornado.
RANGE_BUCKET(25, [0, 10, 20, 30, 40]) -- 3 is return value
Se o ponto for menor que o primeiro valor na matriz, será retornado 0.
RANGE_BUCKET(-10, [5, 10, 20, 30, 40]) -- 0 is return value
Se o ponto for maior ou igual ao último valor na matriz, o comprimento da matriz será retornado.
RANGE_BUCKET(80, [0, 10, 20, 30, 40]) -- 5 is return value
Se a matriz estiver vazia, será retornado 0.
RANGE_BUCKET(80, []) -- 0 is return value
Se o ponto for
NULL
ouNaN
, será retornadoNULL
.RANGE_BUCKET(NULL, [0, 10, 20, 30, 40]) -- NULL is return value
O tipo de dados do ponto e da matriz deve ser compatível.
RANGE_BUCKET('a', ['a', 'b', 'c', 'd']) -- 1 is return value RANGE_BUCKET(1.2, [1, 1.2, 1.4, 1.6]) -- 2 is return value RANGE_BUCKET(1.2, [1, 2, 4, 6]) -- execution failure
A falha na execução ocorre quando:
A matriz tem um valor
NaN
ouNULL
.RANGE_BUCKET(80, [NULL, 10, 20, 30, 40]) -- execution failure
A matriz não é classificada em ordem crescente.
RANGE_BUCKET(30, [10, 30, 20, 40, 50]) -- execution failure
Parâmetros
point
: um valor genérico.boundaries_array
: uma matriz genérica de valores.
Valor de retorno
INT64
Exemplos
Em uma tabela chamada students
, verifique quantos registros existiriam em cada intervalo de age_group
, com base na idade de um aluno:
- age_group 0 (idade <10 anos)
- age_group 1 (idade >= 10, idade <20)
- age_group 2 (idade >= 20, idade <30)
- age_group 3 (idade >= 30)
WITH students AS
(
SELECT 9 AS age UNION ALL
SELECT 20 AS age UNION ALL
SELECT 25 AS age UNION ALL
SELECT 31 AS age UNION ALL
SELECT 32 AS age UNION ALL
SELECT 33 AS age
)
SELECT RANGE_BUCKET(age, [10, 20, 30]) AS age_group, COUNT(*) AS count
FROM students
GROUP BY 1
+--------------+-------+
| age_group | count |
+--------------+-------+
| 0 | 1 |
| 2 | 2 |
| 3 | 3 |
+--------------+-------+
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 sobre as funções de navegação, consulte Conceitos da função de navegação.
FIRST_VALUE
FIRST_VALUE (value_expression [{RESPECT | IGNORE} NULLS])
Descrição
Retorna o valor de value_expression
para a primeira linha no frame da janela atual.
Essa função inclui valores NULL
no cálculo, a menos que IGNORE NULLS
esteja presente. Se IGNORE NULLS
estiver presente, a função excluirá valores NULL
do cálculo.
Tipos de argumentos compatíveis
value_expression
pode ser qualquer tipo de dados que uma expressão pode retornar.
Tipo de dados retornados
ANY
Exemplos
O exemplo a seguir calcula o tempo mais rápido de cada divisão.
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
FORMAT_TIMESTAMP('%X', finish_time) AS finish_time,
division,
FORMAT_TIMESTAMP('%X', fastest_time) AS fastest_time,
TIMESTAMP_DIFF(finish_time, fastest_time, SECOND) AS delta_in_seconds
FROM (
SELECT name,
finish_time,
division,
FIRST_VALUE(finish_time)
OVER (PARTITION BY division ORDER BY finish_time ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS fastest_time
FROM finishers);
+-----------------+-------------+----------+--------------+------------------+
| name | finish_time | division | fastest_time | delta_in_seconds |
+-----------------+-------------+----------+--------------+------------------+
| Carly Forte | 03:08:58 | F25-29 | 03:08:58 | 0 |
| Sophia Liu | 02:51:45 | F30-34 | 02:51:45 | 0 |
| Nikki Leith | 02:59:01 | F30-34 | 02:51:45 | 436 |
| Jen Edwards | 03:06:36 | F30-34 | 02:51:45 | 891 |
| Meghan Lederer | 03:07:41 | F30-34 | 02:51:45 | 956 |
| Lauren Reasoner | 03:10:14 | F30-34 | 02:51:45 | 1109 |
| Lisa Stelzner | 02:54:11 | F35-39 | 02:54:11 | 0 |
| Lauren Matthews | 03:01:17 | F35-39 | 02:54:11 | 426 |
| Desiree Berry | 03:05:42 | F35-39 | 02:54:11 | 691 |
| Suzy Slane | 03:06:24 | F35-39 | 02:54:11 | 733 |
+-----------------+-------------+----------+--------------+------------------+
LAST_VALUE
LAST_VALUE (value_expression [{RESPECT | IGNORE} NULLS])
Descrição
Retorna o valor de value_expression
para a última linha no frame da janela atual.
Essa função inclui valores NULL
no cálculo, a menos que IGNORE NULLS
esteja presente. Se IGNORE NULLS
estiver presente, a função excluirá valores NULL
do cálculo.
Tipos de argumentos compatíveis
value_expression
pode ser qualquer tipo de dados que uma expressão pode retornar.
Tipo de dados retornados
ANY
Exemplos
O exemplo a seguir calcula o tempo mais lento de cada divisão.
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
FORMAT_TIMESTAMP('%X', finish_time) AS finish_time,
division,
FORMAT_TIMESTAMP('%X', slowest_time) AS slowest_time,
TIMESTAMP_DIFF(slowest_time, finish_time, SECOND) AS delta_in_seconds
FROM (
SELECT name,
finish_time,
division,
LAST_VALUE(finish_time)
OVER (PARTITION BY division ORDER BY finish_time ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS slowest_time
FROM finishers);
+-----------------+-------------+----------+--------------+------------------+
| name | finish_time | division | slowest_time | delta_in_seconds |
+-----------------+-------------+----------+--------------+------------------+
| Carly Forte | 03:08:58 | F25-29 | 03:08:58 | 0 |
| Sophia Liu | 02:51:45 | F30-34 | 03:10:14 | 1109 |
| Nikki Leith | 02:59:01 | F30-34 | 03:10:14 | 673 |
| Jen Edwards | 03:06:36 | F30-34 | 03:10:14 | 218 |
| Meghan Lederer | 03:07:41 | F30-34 | 03:10:14 | 153 |
| Lauren Reasoner | 03:10:14 | F30-34 | 03:10:14 | 0 |
| Lisa Stelzner | 02:54:11 | F35-39 | 03:06:24 | 733 |
| Lauren Matthews | 03:01:17 | F35-39 | 03:06:24 | 307 |
| Desiree Berry | 03:05:42 | F35-39 | 03:06:24 | 42 |
| Suzy Slane | 03:06:24 | F35-39 | 03:06:24 | 0 |
+-----------------+-------------+----------+--------------+------------------+
NTH_VALUE
NTH_VALUE (value_expression, constant_integer_expression [{RESPECT | IGNORE} NULLS])
Descrição
Retorna o valor de value_expression
na linha N do frame da janela atual, em que N é definido por constant_integer_expression
. Retorna NULL, se não houver linha.
Essa função inclui valores NULL
no cálculo, a menos que IGNORE NULLS
esteja presente. Se IGNORE NULLS
estiver presente, a função excluirá valores NULL
do cálculo.
Tipos de argumentos compatíveis
value_expression
pode ser qualquer tipo de dados que possa ser retornado de uma expressão.constant_integer_expression
pode ser qualquer expressão constante que retorna um número inteiro.
Tipo de dados retornados
ANY
Exemplos
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
FORMAT_TIMESTAMP('%X', finish_time) AS finish_time,
division,
FORMAT_TIMESTAMP('%X', fastest_time) AS fastest_time,
FORMAT_TIMESTAMP('%X', second_fastest) AS second_fastest
FROM (
SELECT name,
finish_time,
division,finishers,
FIRST_VALUE(finish_time)
OVER w1 AS fastest_time,
NTH_VALUE(finish_time, 2)
OVER w1 as second_fastest
FROM finishers
WINDOW w1 AS (
PARTITION BY division ORDER BY finish_time ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING));
+-----------------+-------------+----------+--------------+----------------+
| name | finish_time | division | fastest_time | second_fastest |
+-----------------+-------------+----------+--------------+----------------+
| Carly Forte | 03:08:58 | F25-29 | 03:08:58 | NULL |
| Sophia Liu | 02:51:45 | F30-34 | 02:51:45 | 02:59:01 |
| Nikki Leith | 02:59:01 | F30-34 | 02:51:45 | 02:59:01 |
| Jen Edwards | 03:06:36 | F30-34 | 02:51:45 | 02:59:01 |
| Meghan Lederer | 03:07:41 | F30-34 | 02:51:45 | 02:59:01 |
| Lauren Reasoner | 03:10:14 | F30-34 | 02:51:45 | 02:59:01 |
| Lisa Stelzner | 02:54:11 | F35-39 | 02:54:11 | 03:01:17 |
| Lauren Matthews | 03:01:17 | F35-39 | 02:54:11 | 03:01:17 |
| Desiree Berry | 03:05:42 | F35-39 | 02:54:11 | 03:01:17 |
| Suzy Slane | 03:06:24 | F35-39 | 02:54:11 | 03:01:17 |
+-----------------+-------------+----------+--------------+----------------+
LEAD
LEAD (value_expression[, offset [, default_expression]])
Descrição
Retorna o valor de value_expression
em uma linha subsequente. Alterar o valor offset
altera qual linha subsequente é retornada; o valor padrão é 1
, indicando a próxima linha no frame da janela. Um erro ocorre se offset
for NULL ou um valor negativo.
O default_expression
opcional é usado se não houver uma linha no frame da janela no deslocamento especificado. Essa expressão precisa ser uma expressão constante e seu tipo precisa ser implicitamente coercível para o tipo de value_expression
. Se não for especificado, a default_expression
padrão será NULL.
Tipos de argumentos compatíveis
value_expression
pode ser qualquer tipo de dados que possa ser retornado de uma expressão.offset
precisa ser um parâmetro ou literal inteiro não negativo.default_expression
precisa ser compatível com o tipo de expressão de valor.
Tipo de dados retornados
ANY
Exemplos
O exemplo a seguir ilustra uma utilização básica da função LEAD
.
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
finish_time,
division,
LEAD(name)
OVER (PARTITION BY division ORDER BY finish_time ASC) AS followed_by
FROM finishers;
+-----------------+-------------+----------+-----------------+
| name | finish_time | division | followed_by |
+-----------------+-------------+----------+-----------------+
| Carly Forte | 03:08:58 | F25-29 | NULL |
| Sophia Liu | 02:51:45 | F30-34 | Nikki Leith |
| Nikki Leith | 02:59:01 | F30-34 | Jen Edwards |
| Jen Edwards | 03:06:36 | F30-34 | Meghan Lederer |
| Meghan Lederer | 03:07:41 | F30-34 | Lauren Reasoner |
| Lauren Reasoner | 03:10:14 | F30-34 | NULL |
| Lisa Stelzner | 02:54:11 | F35-39 | Lauren Matthews |
| Lauren Matthews | 03:01:17 | F35-39 | Desiree Berry |
| Desiree Berry | 03:05:42 | F35-39 | Suzy Slane |
| Suzy Slane | 03:06:24 | F35-39 | NULL |
+-----------------+-------------+----------+-----------------+
Este próximo exemplo usa o parâmetro offset
opcional.
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
finish_time,
division,
LEAD(name, 2)
OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_back
FROM finishers;
+-----------------+-------------+----------+------------------+
| name | finish_time | division | two_runners_back |
+-----------------+-------------+----------+------------------+
| Carly Forte | 03:08:58 | F25-29 | NULL |
| Sophia Liu | 02:51:45 | F30-34 | Jen Edwards |
| Nikki Leith | 02:59:01 | F30-34 | Meghan Lederer |
| Jen Edwards | 03:06:36 | F30-34 | Lauren Reasoner |
| Meghan Lederer | 03:07:41 | F30-34 | NULL |
| Lauren Reasoner | 03:10:14 | F30-34 | NULL |
| Lisa Stelzner | 02:54:11 | F35-39 | Desiree Berry |
| Lauren Matthews | 03:01:17 | F35-39 | Suzy Slane |
| Desiree Berry | 03:05:42 | F35-39 | NULL |
| Suzy Slane | 03:06:24 | F35-39 | NULL |
+-----------------+-------------+----------+------------------+
O exemplo a seguir substitui os valores NULL por um valor padrão.
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
finish_time,
division,
LEAD(name, 2, 'Nobody')
OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_back
FROM finishers;
+-----------------+-------------+----------+------------------+
| name | finish_time | division | two_runners_back |
+-----------------+-------------+----------+------------------+
| Carly Forte | 03:08:58 | F25-29 | Nobody |
| Sophia Liu | 02:51:45 | F30-34 | Jen Edwards |
| Nikki Leith | 02:59:01 | F30-34 | Meghan Lederer |
| Jen Edwards | 03:06:36 | F30-34 | Lauren Reasoner |
| Meghan Lederer | 03:07:41 | F30-34 | Nobody |
| Lauren Reasoner | 03:10:14 | F30-34 | Nobody |
| Lisa Stelzner | 02:54:11 | F35-39 | Desiree Berry |
| Lauren Matthews | 03:01:17 | F35-39 | Suzy Slane |
| Desiree Berry | 03:05:42 | F35-39 | Nobody |
| Suzy Slane | 03:06:24 | F35-39 | Nobody |
+-----------------+-------------+----------+------------------+
LAG
LAG (value_expression[, offset [, default_expression]])
Descrição
Retorna o valor de value_expression
em uma linha anterior. Alterar o valor de offset
altera a linha anterior que é retornada; o valor padrão é 1
, indicando a linha anterior no frame da janela. Um erro ocorre se offset
for NULL ou um valor negativo.
O default_expression
opcional é usado se não houver uma linha no frame da janela no deslocamento especificado. Essa expressão precisa ser uma expressão constante e seu tipo precisa ser implicitamente coercível para o tipo de value_expression
. Se não for especificado, a default_expression
padrão será NULL.
Tipos de argumentos compatíveis
value_expression
pode ser qualquer tipo de dados que possa ser retornado de uma expressão.offset
precisa ser um parâmetro ou literal inteiro não negativo.default_expression
precisa ser compatível com o tipo de expressão de valor.
Tipo de dados retornados
ANY
Exemplos
O exemplo a seguir ilustra uma utilização básica da função LAG
.
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
finish_time,
division,
LAG(name)
OVER (PARTITION BY division ORDER BY finish_time ASC) AS preceding_runner
FROM finishers;
+-----------------+-------------+----------+------------------+
| name | finish_time | division | preceding_runner |
+-----------------+-------------+----------+------------------+
| Carly Forte | 03:08:58 | F25-29 | NULL |
| Sophia Liu | 02:51:45 | F30-34 | NULL |
| Nikki Leith | 02:59:01 | F30-34 | Sophia Liu |
| Jen Edwards | 03:06:36 | F30-34 | Nikki Leith |
| Meghan Lederer | 03:07:41 | F30-34 | Jen Edwards |
| Lauren Reasoner | 03:10:14 | F30-34 | Meghan Lederer |
| Lisa Stelzner | 02:54:11 | F35-39 | NULL |
| Lauren Matthews | 03:01:17 | F35-39 | Lisa Stelzner |
| Desiree Berry | 03:05:42 | F35-39 | Lauren Matthews |
| Suzy Slane | 03:06:24 | F35-39 | Desiree Berry |
+-----------------+-------------+----------+------------------+
Este próximo exemplo usa o parâmetro offset
opcional.
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
finish_time,
division,
LAG(name, 2)
OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_ahead
FROM finishers;
+-----------------+-------------+----------+-------------------+
| name | finish_time | division | two_runners_ahead |
+-----------------+-------------+----------+-------------------+
| Carly Forte | 03:08:58 | F25-29 | NULL |
| Sophia Liu | 02:51:45 | F30-34 | NULL |
| Nikki Leith | 02:59:01 | F30-34 | NULL |
| Jen Edwards | 03:06:36 | F30-34 | Sophia Liu |
| Meghan Lederer | 03:07:41 | F30-34 | Nikki Leith |
| Lauren Reasoner | 03:10:14 | F30-34 | Jen Edwards |
| Lisa Stelzner | 02:54:11 | F35-39 | NULL |
| Lauren Matthews | 03:01:17 | F35-39 | NULL |
| Desiree Berry | 03:05:42 | F35-39 | Lisa Stelzner |
| Suzy Slane | 03:06:24 | F35-39 | Lauren Matthews |
+-----------------+-------------+----------+-------------------+
O exemplo a seguir substitui os valores NULL por um valor padrão.
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
finish_time,
division,
LAG(name, 2, 'Nobody')
OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_ahead
FROM finishers;
+-----------------+-------------+----------+-------------------+
| name | finish_time | division | two_runners_ahead |
+-----------------+-------------+----------+-------------------+
| Carly Forte | 03:08:58 | F25-29 | Nobody |
| Sophia Liu | 02:51:45 | F30-34 | Nobody |
| Nikki Leith | 02:59:01 | F30-34 | Nobody |
| Jen Edwards | 03:06:36 | F30-34 | Sophia Liu |
| Meghan Lederer | 03:07:41 | F30-34 | Nikki Leith |
| Lauren Reasoner | 03:10:14 | F30-34 | Jen Edwards |
| Lisa Stelzner | 02:54:11 | F35-39 | Nobody |
| Lauren Matthews | 03:01:17 | F35-39 | Nobody |
| Desiree Berry | 03:05:42 | F35-39 | Lisa Stelzner |
| Suzy Slane | 03:06:24 | F35-39 | Lauren Matthews |
+-----------------+-------------+----------+-------------------+
PERCENTILE_CONT
PERCENTILE_CONT (value_expression, percentile [{RESPECT | IGNORE} NULLS])
Descrição
Calcula o valor do percentil especificado para a expressão de valor, com interpolação linear.
Esta função ignora valores NULL se RESPECT NULLS
estiver ausente. Se RESPECT
NULLS
estiver presente:
- A interpolação entre dois valores
NULL
retornaNULL
. - A interpolação entre um valor
NULL
e um valor nãoNULL
retorna o valor nãoNULL
.
Tipos de argumentos compatíveis
value_expression
é uma expressão numérica.percentile
é umFLOAT64
literal no intervalo[0, 1]
.
Tipo de dados retornados
FLOAT64
Exemplos
O exemplo a seguir calcula o valor de alguns percentis em uma coluna de valores, ignorando os nulos.
SELECT
PERCENTILE_CONT(x, 0) OVER() AS min,
PERCENTILE_CONT(x, 0.01) OVER() AS percentile1,
PERCENTILE_CONT(x, 0.5) OVER() AS median,
PERCENTILE_CONT(x, 0.9) OVER() AS percentile90,
PERCENTILE_CONT(x, 1) OVER() AS max
FROM UNNEST([0, 3, NULL, 1, 2]) AS x LIMIT 1;
+-----+-------------+--------+--------------+-----+
| min | percentile1 | median | percentile90 | max |
+-----+-------------+--------+--------------+-----+
| 0 | 0.03 | 1.5 | 2.7 | 3 |
+-----+-------------+--------+--------------+-----+
O exemplo a seguir calcula o valor de alguns percentis em uma coluna de valores, respeitando os nulos.
SELECT
PERCENTILE_CONT(x, 0 RESPECT NULLS) OVER() AS min,
PERCENTILE_CONT(x, 0.01 RESPECT NULLS) OVER() AS percentile1,
PERCENTILE_CONT(x, 0.5 RESPECT NULLS) OVER() AS median,
PERCENTILE_CONT(x, 0.9 RESPECT NULLS) OVER() AS percentile90,
PERCENTILE_CONT(x, 1 RESPECT NULLS) OVER() AS max
FROM UNNEST([0, 3, NULL, 1, 2]) AS x LIMIT 1;
+------+-------------+--------+--------------+-----+
| min | percentile1 | median | percentile90 | max |
+------+-------------+--------+--------------+-----+
| NULL | 0 | 1 | 2.6 | 3 |
+------+-------------+--------+--------------+-----+
PERCENTILE_DISC
PERCENTILE_DISC (value_expression, percentile [{RESPECT | IGNORE} NULLS])
Descrição
Calcula o valor de percentil especificado para um value_expression
discreto. O valor retornado é o primeiro valor classificado de value_expression
com a distribuição cumulativa maior ou igual ao valor percentile
especificado.
Esta função ignora valores NULL
a menos que RESPECT NULLS
esteja presente.
Tipos de argumentos compatíveis
value_expression
pode ser qualquer tipo ordenado.percentile
é umFLOAT64
literal no intervalo[0, 1]
.
Tipo de dados retornados
ANY
Exemplos
O exemplo a seguir calcula o valor de alguns percentis em uma coluna de valores, ignorando os nulos.
SELECT
x,
PERCENTILE_DISC(x, 0) OVER() AS min,
PERCENTILE_DISC(x, 0.5) OVER() AS median,
PERCENTILE_DISC(x, 1) OVER() AS max
FROM UNNEST(['c', NULL, 'b', 'a']) AS x;
+------+-----+--------+-----+
| x | min | median | max |
+------+-----+--------+-----+
| c | a | b | c |
| NULL | a | b | c |
| b | a | b | c |
| a | a | b | c |
+------+-----+--------+-----+
O exemplo a seguir calcula o valor de alguns percentis em uma coluna de valores, respeitando os nulos.
SELECT
x,
PERCENTILE_DISC(x, 0 RESPECT NULLS) OVER() AS min,
PERCENTILE_DISC(x, 0.5 RESPECT NULLS) OVER() AS median,
PERCENTILE_DISC(x, 1 RESPECT NULLS) OVER() AS max
FROM UNNEST(['c', NULL, 'b', 'a']) AS x;
+------+------+--------+-----+
| x | min | median | max |
+------+------+--------+-----+
| c | NULL | a | c |
| NULL | NULL | a | c |
| b | NULL | a | c |
| a | NULL | a | c |
+------+------+--------+-----+
Funções de agregação analítica
As seções a seguir descrevem as funções analíticas de agregação compatíveis com o BigQuery. Para uma explicação de como são funções analíticas, consulte Conceitos da função analítica. Para uma explicação de como são funções analíticas de agregação, consulte Conceitos da função analítica de agregação.
O BigQuery é compatível com estas funções de agregação como funções analíticas:
- ANY_VALUE
- ARRAY_AGG
- AVG
- CORR
- COUNT
- COUNTIF
- COVAR_POP
- COVAR_SAMP
- MAX
- MIN
- ST_CLUSTERDBSCAN
- 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 seDISTINCT
estiver presente.window_frame_clause
: opcional. Não permitido seDISTINCT
estiver presente.
Exemplo:
COUNT(*) OVER (ROWS UNBOUNDED PRECEDING)
SUM(DISTINCT x) OVER ()
Funções de hash
FARM_FINGERPRINT
FARM_FINGERPRINT(value)
Descrição
Calcula a impressão digital da entrada STRING ou BYTES usando a função Fingerprint64
da biblioteca de código aberto FarmHash. A saída dessa função para uma entrada em particular nunca muda.
Tipo de retorno
INT64
Exemplos
WITH example AS (
SELECT 1 AS x, "foo" AS y, true AS z UNION ALL
SELECT 2 AS x, "apple" AS y, false AS z UNION ALL
SELECT 3 AS x, "" AS y, true AS z
)
SELECT
*,
FARM_FINGERPRINT(CONCAT(CAST(x AS STRING), y, CAST(z AS STRING)))
AS row_fingerprint
FROM example;
+---+-------+-------+----------------------+
| x | y | z | row_fingerprint |
+---+-------+-------+----------------------+
| 1 | foo | true | -1541654101129638711 |
| 2 | apple | false | 2794438866806483259 |
| 3 | | true | -4880158226897771312 |
+---+-------+-------+----------------------+
MD5
MD5(input)
Descrição
Calcula o hash da entrada usando o algoritmo MD5. A entrada pode ser STRING ou BYTES. A versão da string trata a entrada como uma matriz de bytes.
Essa função retorna 16 bytes.
Tipo de retorno
BYTES
Exemplo
SELECT MD5("Hello World") as md5;
-- Note that the result of MD5 is of type BYTES, displayed as a base64-encoded string.
+--------------------------+
| md5 |
+--------------------------+
| sQqNsWTgdUEFt6mb5y4/5Q== |
+--------------------------+
SHA1
SHA1(input)
Descrição
Calcula o hash da entrada usando o algoritmo SHA-1. A entrada pode ser STRING ou BYTES. A versão da string trata a entrada como uma matriz de bytes.
Essa função retorna 20 bytes.
Tipo de retorno
BYTES
Exemplo
SELECT SHA1("Hello World") as sha1;
-- Note that the result of SHA1 is of type BYTES, displayed as a base64-encoded string.
+------------------------------+
| sha1 |
+------------------------------+
| Ck1VqNd45QIvq3AZd8XYQLvEhtA= |
+------------------------------+
SHA256
SHA256(input)
Descrição
Calcula o hash da entrada usando o algoritmo SHA-256. A entrada pode ser STRING ou BYTES. A versão da string trata a entrada como uma matriz de bytes.
Essa função retorna 32 bytes.
Tipo de retorno
BYTES
Exemplo
SELECT SHA256("Hello World") as sha256;
SHA512
SHA512(input)
Descrição
Calcula o hash da entrada usando o algoritmo SHA-512. A entrada pode ser STRING ou BYTES. A versão da string trata a entrada como uma matriz de bytes.
Essa função retorna 64 bytes.
Tipo de retorno
BYTES
Exemplo
SELECT SHA512("Hello World") as sha512;
Funções de string
Essas funções de string funcionam em dois valores diferentes:
tipos de dados STRING
e BYTES
. Os valores STRING
precisam ter a codificação UTF-8 bem formada.
As funções que retornam valores de posição, como STRPOS,
codificam essas posições como INT64
. O valor 1
indica o primeiro caractere (ou byte), 2
indica o segundo, e assim por diante.
O valor 0
indica um índice inválido. Ao trabalhar em tipos STRING
, as
posições retornadas indicam as posições dos caracteres.
Todas as comparações de string são feitas byte a byte, sem levar em conta a equivalência canônica Unicode.
BYTE_LENGTH
BYTE_LENGTH(value)
Descrição
Retorna o comprimento do valor STRING
ou BYTES
em BYTES
,
independentemente do tipo de valor ser STRING
ou BYTES
.
Tipo de retorno
INT64
Exemplos
WITH example AS
(SELECT "абвгд" AS characters, b"абвгд" AS bytes)
SELECT
characters,
BYTE_LENGTH(characters) AS string_example,
bytes,
BYTE_LENGTH(bytes) AS bytes_example
FROM example;
+------------+----------------+-------+---------------+
| characters | string_example | bytes | bytes_example |
+------------+----------------+-------+---------------+
| абвгд | 10 | абвгд | 10 |
+------------+----------------+-------+---------------+
CHAR_LENGTH
CHAR_LENGTH(value)
Descrição
Retorna o comprimento da STRING
em caracteres.
Tipo de retorno
INT64
Exemplos
WITH example AS
(SELECT "абвгд" AS characters)
SELECT
characters,
CHAR_LENGTH(characters) AS char_length_example
FROM example;
+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд | 5 |
+------------+---------------------+
CHARACTER_LENGTH
CHARACTER_LENGTH(value)
Descrição
Sinônimo para CHAR_LENGTH.
Tipo de retorno
INT64
Exemplos
WITH example AS
(SELECT "абвгд" AS characters)
SELECT
characters,
CHARACTER_LENGTH(characters) AS char_length_example
FROM example;
+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд | 5 |
+------------+---------------------+
CODE_POINTS_TO_BYTES
CODE_POINTS_TO_BYTES(ascii_values)
Descrição
Usa uma matriz de pontos de código (em inglês)
ASCII estendidos (ARRAY
de INT64
) e retorna BYTES
.
Para converter de BYTES
para uma matriz de pontos de código, consulte
TO_CODE_POINTS.
Tipo de retorno
BYTES
Exemplos
Veja a seguir um exemplo básico usando CODE_POINTS_TO_BYTES
.
SELECT CODE_POINTS_TO_BYTES([65, 98, 67, 100]) AS bytes;
+-------+
| bytes |
+-------+
| AbCd |
+-------+
O exemplo a seguir utiliza um algoritmo ROT13 (rotacionar 13 posições) para codificar uma string.
SELECT CODE_POINTS_TO_BYTES(ARRAY_AGG(
(SELECT
CASE
WHEN chr BETWEEN b'a' and b'z'
THEN TO_CODE_POINTS(b'a')[offset(0)] +
MOD(code+13-TO_CODE_POINTS(b'a')[offset(0)],26)
WHEN chr BETWEEN b'A' and b'Z'
THEN TO_CODE_POINTS(b'A')[offset(0)] +
MOD(code+13-TO_CODE_POINTS(b'A')[offset(0)],26)
ELSE code
END
FROM
(SELECT code, CODE_POINTS_TO_BYTES([code]) chr)
) ORDER BY OFFSET)) AS encoded_string
FROM UNNEST(TO_CODE_POINTS(b'Test String!')) code WITH OFFSET;
+----------------+
| encoded_string |
+----------------+
| Grfg Fgevat! |
+----------------+
CODE_POINTS_TO_STRING
CODE_POINTS_TO_STRING(value)
Descrição
Usa uma matriz de pontos de código (em inglês)
Unicode (ARRAY
de INT64
) e
retorna um STRING
.
Para converter uma string em uma matriz de pontos de código, consulte TO_CODE_POINTS.
Tipo de retorno
STRING
Exemplo
Veja a seguir um exemplo básico usando CODE_POINTS_TO_STRING
.
SELECT CODE_POINTS_TO_STRING([65, 255, 513, 1024]) AS string;
+--------+
| string |
+--------+
| AÿȁЀ |
+--------+
O exemplo a seguir calcula a frequência das letras em um grupo de palavras.
WITH Words AS (
SELECT word
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word
)
SELECT
CODE_POINTS_TO_STRING([code_point]) AS letter,
COUNT(*) AS letter_count
FROM Words,
UNNEST(TO_CODE_POINTS(word)) AS code_point
GROUP BY 1
ORDER BY 2 DESC;
+--------+--------------+
| letter | letter_count |
+--------+--------------+
| a | 5 |
| f | 3 |
| r | 2 |
| b | 2 |
| l | 2 |
| o | 2 |
| g | 1 |
| z | 1 |
| e | 1 |
| m | 1 |
| i | 1 |
+--------+--------------+
CONCAT
CONCAT(value1[, ...])
Descrição
Concatena um ou mais valores em um único resultado. Todos os valores precisam ser BYTES
ou tipos de dados que podem ser convertidos em STRING
.
A função retornará NULL
se algum argumento de entrada for NULL
.
Tipo de retorno
STRING
ou BYTES
Exemplos
SELECT CONCAT("T.P.", " ", "Bar") as author;
+---------------------+
| author |
+---------------------+
| T.P. Bar |
+---------------------+
SELECT CONCAT("Summer", " ", 1923) as release_date;
+---------------------+
| release_date |
+---------------------+
| Summer 1923 |
+---------------------+
With Employees AS
(SELECT
"John" AS first_name,
"Doe" AS last_name
UNION ALL
SELECT
"Jane" AS first_name,
"Smith" AS last_name
UNION ALL
SELECT
"Joe" AS first_name,
"Jackson" AS last_name)
SELECT
CONCAT(first_name, " ", last_name)
AS full_name
FROM Employees;
+---------------------+
| full_name |
+---------------------+
| John Doe |
| Jane Smith |
| Joe Jackson |
+---------------------+
ENDS_WITH
ENDS_WITH(value1, value2)
Descrição
Utiliza dois valores STRING
ou BYTES
. Retorna TRUE
se o segundo
valor for um sufixo do primeiro.
Tipo de retorno
BOOL
Exemplos
WITH items AS
(SELECT "apple" as item
UNION ALL
SELECT "banana" as item
UNION ALL
SELECT "orange" as item)
SELECT
ENDS_WITH(item, "e") as example
FROM items;
+---------+
| example |
+---------+
| True |
| False |
| True |
+---------+
FORMAT
O BigQuery é compatível com a função FORMAT()
para formatação de strings. Essa função é semelhante à função C printf
. Ela produz uma STRING
a partir de uma
string de formato que contém zero ou mais especificadores de formato e uma
lista de comprimentos variáveis de argumentos extras que correspondem aos especificadores.
Veja alguns exemplos:
Descrição | Instrução | Resultado |
---|---|---|
Inteiro simples | FORMAT("%d", 10) | 10 |
Inteiro com preenchimento vazio à esquerda | FORMAT("|%10d|", 11) | | 11| |
Inteiro com preenchimento com zero à esquerda | FORMAT("+%010d+", 12) | +0000000012+ |
Inteiro com vírgulas | FORMAT("%'d", 123456789) | 123,456,789 |
STRING | FORMAT("-%s-", 'abcd efg') | -abcd efg- |
FLOAT64 | FORMAT("%f %E", 1.1, 2.2) | 1.100000 2.200000E+00 |
DATE | FORMAT("%t", date "2015-09-01") | 2015-09-01 |
TIMESTAMP | FORMAT("%t", timestamp "2015-09-01 12:34:56 America/Los_Angeles") | 2015‑09‑01 19:34:56+00 |
A função FORMAT()
não oferece formato totalmente personalizável para todos os tipos e valores, nem formato sensível à localidade.
Se o formato personalizado for necessário para um tipo, primeiro formate-o usando as funções específicas do tipo, como FORMAT_DATE()
ou FORMAT_TIMESTAMP()
.
Exemplo:
SELECT FORMAT("date: %s!", FORMAT_DATE("%B %d, %Y", date '2015-01-02'));
Retorna
date: January 02, 2015!
Sintaxe
A sintaxe FORMAT()
usa uma lista de strings de formato e comprimentos variáveis dos
argumentos e produz um resultado STRING
:
FORMAT(format_string, ...)
A expressão format_string
pode conter zero ou mais especificadores de formato.
Cada especificador de formato é introduzido pelo símbolo %
e precisa ser mapeado para um ou mais dos argumentos restantes. Em geral, esse é um mapeamento um para um, exceto quando o especificador *
está presente. Por exemplo, %.*i
realiza o mapeamento para dois argumentos: um de comprimento e um de número inteiro com sinal. Se o número de argumentos relacionados com os especificadores de formato não for o mesmo número de argumentos, ocorrerá um erro.
Especificadores de formatos compatíveis
O especificador de formato da função FORMAT()
segue este protótipo:
%[flags][width][.precision]specifier
Os especificadores de formatos compatíveis são identificados na tabela a seguir. Os desvios de printf() são identificados em itálico.
Especificador | Descrição | Exemplos | Tipos |
d ou i |
Decimal inteiro. | 392 |
INT64 |
o |
Octal | 610 |
INT64* |
x |
Inteiro hexadecimal | 7fa |
INT64* |
X |
Inteiro hexadecimal (maiúsculas) | 7FA |
INT64* |
f |
Notação decimal, em [-](parte inteira).(parte fracionária) para valores finitos e em minúsculas para valores não finitos | 392.650000 inf nan |
NUMERIC FLOAT64 |
F |
Notação decimal, em [-](parte inteira).(parte fracionária) para valores finitos e em maiúsculas para valores não finitos | 392.650000 INF NAN |
NUMERIC FLOAT64 |
e |
Notação científica (mantissa/expoente), minúsculas | 3,926500e+02 inf nan |
NUMERIC FLOAT64 |
E |
Notação científica (mantissa/expoente), maiúsculas | 3,926500E+02 INF NAN |
NUMERIC FLOAT64 |
g |
Notação decimal ou notação científica, dependendo do expoente do valor de entrada e da precisão especificada. Minúscula. Consulte Comportamento de%g e %G para detalhes. | 392,65 3,9265e+07 inf nan |
FLOAT64 |
G |
Notação decimal ou científica, dependendo do expoente do valor de entrada e da precisão especificada. Maiúscula. Consulte Comportamento de%g e %G para detalhes. |
392.65 3.9265E+07 INF NAN |
FLOAT64 |
s |
String de caracteres | amostra | STRING |
t |
Retorna uma string para impressão que representa o valor. Geralmente é
semelhante a fazer o cast do argumento para STRING .
Consulte Comportamento de %t e %T.
|
amostra 2014‑01‑01 |
<any> |
T |
Produz uma string que é uma constante válida do BigQuery, com um tipo semelhante ao do valor (talvez mais amplo ou string). Consulte Comportamento de %t e %T. |
'amostra' b'amostra de bytes' 1234 2.3 data '2014‑01‑01' |
<any> |
% |
'%%' produz um único '%' | % | n/a |
* Os especificadores %o
, %x
e %X
geram um erro se
valores negativos forem usados.
O formato do especificador também pode conter os subespecificadores identificados acima no protótipo do especificador.
Esses subespecificadores precisam seguir estas especificações.
Sinalizações
Sinalizações | Descrição |
- |
Justificada à esquerda dentro da largura do campo. O padrão é a justificação à direita (informe-se sobre subespecificador de largura). |
+ |
Força o resultado a ser precedido por um sinal de mais ou menos (+ ou - ), mesmo para números positivos. Por padrão, apenas os números negativos são precedidos por um sinal - |
<space> | Se nenhum sinal for gravado, um espaço em branco será inserido antes do valor |
# |
|
0 |
Quando o preenchimento é especificado, coloca zeros (0) à esquerda do número, em vez de espaços (informe-se sobre subespecificador de largura). |
' |
Formata inteiros usando o caractere de agrupamento adequado. Exemplo:
Essa sinalização só é relevante para valores decimais, hexadecimais e octais. |
As sinalizações podem ser especificadas em qualquer ordem. Sinalizações duplicadas não são um erro. As sinalizações são ignoradas quando não são relevantes para algum tipo de elemento.
Largura
Largura | Descrição |
<número> | O número mínimo de caracteres a ser impresso. Se o valor a ser impresso for mais curto do que esse número, o resultado será preenchido com espaços em branco. O valor não é truncado mesmo que o resultado seja maior. |
* |
A largura não é especificada na string de formato, mas como um argumento de valor inteiro complementar precedendo o argumento que precisa ser formatado. |
Precisão
Precisão | Descrição |
. <number> |
|
.* |
A precisão não é especificada na string de formato, mas como um argumento de valor inteiro complementar precedendo o argumento que precisa ser formatado. |
Comportamento de %g e %G
Os especificadores de formato %g
e %G
escolhem a notação decimal (como
os especificadores %f
e %F
) ou a notação científica (como os especificadores %e
e
%E
), dependendo do expoente do valor de entrada e da precisão
especificada.
Deixe p representar a precisão especificada (o padrão é 6; mas será 1 se a precisão especificada for menor que 1). O valor de entrada é convertido primeiro em notação científica com precisão = (p - 1). Se a parte x do expoente resultante for menor que -4 ou menor que p, a notação científica com precisão = (p - 1) será usada. Caso contrário, a notação decimal com precisão = (p - 1 - x) é usada.
A menos que a sinalização #
esteja presente, os zeros à direita após a vírgula decimal
serão removidos, e a vírgula decimal também será removida se não houver um dígito depois
dela.
Comportamento de %t e %T
Os especificadores de formato %t
e %T
são definidos para todos os tipos. A
largura, a precisão e as sinalizações agem da mesma forma
que para %s
: a largura é a largura mínima e a STRING
será
preenchida com esse tamanho. Precisão é a largura máxima
do conteúdo a ser exibido e a STRING
será truncada com esse tamanho, antes de
preencher a largura.
O especificador %t
é sempre uma forma legível do valor.
O especificador %T
é sempre um literal SQL válido de um tipo semelhante, como um
tipo numérico mais amplo.
O literal não inclui casts ou um nome de tipo, exceto no caso especial
de valores de ponto flutuante não finitos.
O STRING
é formatado da seguinte forma:
Tipo | %t | %T |
NULL de qualquer tipo |
NULL |
NULL |
INT64 |
123 | 123 |
NUMERIC | 123.0 (sempre com .0) | NUMERIC "123,0" |
FLOAT64 |
123.0 (sempre com .0) 123e+10 inf -inf NaN
|
123.0 (sempre com .0) 123e+10 CAST("inf" AS <type>) CAST("-inf" AS <type>) CAST("nan" AS <type>) |
STRING | valor da string sem aspas | literal da string com aspas |
BYTES |
bytes escapados sem aspas por exemplo, abc\x01\x02 |
bytes literais com aspas por exemplo, b"abc\x01\x02" |
DATE | 2011-02-03 | DATE "2011-02-03" |
TIMESTAMP | 2011-02-03 04:05:06+00 | TIMESTAMP "2011-02-03 04:05:06+00" |
ARRAY | [valor, valor, ...] onde os valores são formatados com %t |
[valor, valor, ...] onde os valores são formatados com %T |
STRUCT | (valor, valor, ...) onde os campos são formatados com %t |
(valor, valor, ...) em que os campos são formatados com %T Casos especiais: Nenhum campo: STRUCT() Um campo: STRUCT(value) |
Condições de erro
Se um especificador de formato for inválido ou não compatível com o tipo de argumento relacionado, ou forem fornecidos o número ou os argumentos incorretos, ocorrerá um erro. Por exemplo, as seguintes expressões <format_string>
são inválidas:
FORMAT('%s', 1)
FORMAT('%')
Tratamento do argumento NULL
Uma string de formato NULL
resulta em uma saída STRING
NULL
. Outros argumentos
são ignorados, neste caso.
Em geral, a função produz um valor NULL
se um argumento NULL
estiver presente.
Por exemplo, FORMAT('%i', NULL_expression)
produz um NULL STRING
como
saída.
No entanto, há algumas exceções: se o especificador de formato for %t ou %T
(que produzem STRING
s que efetivamente correspondem à semântica do valor literal e
CAST), um valor NULL
produzirá 'NULL' (sem as aspas) na STRING
do resultado.
Por exemplo, a função:
FORMAT('00-%t-00', NULL_expression);
Retorna
00-NULL-00
Outras regras semânticas
Os valores FLOAT64
podem ser +/-inf
ou NaN
.
Quando um argumento tem um desses valores, o resultado dos especificadores de formato
%f
, %F
, %e
, %E
, %g
, %G
e %t
são inf
, -inf
ou nan
(ou o mesmo em maiúsculas), conforme apropriado. Isso é consistente com a forma como
o BigQuery faz o cast desses valores para STRING
. Para %T
,
o BigQuery retorna strings entre aspas para
valores FLOAT64
que não têm representações literais
que não sejam de string.
FROM_BASE32
FROM_BASE32(string_expr)
Descrição
Converte no formato BYTES
a entrada codificada em base32 string_expr
. Para converter
BYTES
em uma STRING
codificada em base32, use TO_BASE32.
Tipo de retorno
BYTES
Exemplo
SELECT FROM_BASE32('MFRGGZDF74======') AS byte_data;
-- Note that the result of FROM_BASE32 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| YWJjZGX/ |
+-----------+
FROM_BASE64
FROM_BASE64(string_expr)
Descrição
Converte no formato BYTES
a entrada
string_expr
codificada em base64. Para converter
BYTES
em uma STRING
codificada em base64,
use TO_BASE64.
Tipo de retorno
BYTES
Exemplo
SELECT FROM_BASE64('3q2+7w==') AS byte_data;
-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| 3q2+7w== |
+-----------+
FROM_HEX
FROM_HEX(string)
Descrição
Converte um STRING
codificado em hexadecimal em formato BYTES
. Retorna um erro
se a STRING
de entrada contiver caracteres fora do intervalo
(0..9, A..F, a..f)
. Não importa se os caracteres estão em maiúsculas ou minúsculas. Se a
entrada STRING
tiver um número ímpar de caracteres, a função age como se a
entrada tivesse um 0
à esquerda. Para converter BYTES
em uma STRING
codificada em hexadecimal,
use TO_HEX.
Tipo de retorno
BYTES
Exemplo
WITH Input AS (
SELECT '00010203aaeeefff' AS hex_str UNION ALL
SELECT '0AF' UNION ALL
SELECT '666f6f626172'
)
SELECT hex_str, FROM_HEX(hex_str) AS bytes_str
FROM Input;
-- Note that the result of FROM_HEX is of type BYTES, displayed as a base64-encoded string.
+------------------+--------------+
| hex_str | bytes_str |
+------------------+--------------+
| 0AF | AAECA6ru7/8= |
| 00010203aaeeefff | AK8= |
| 666f6f626172 | Zm9vYmFy |
+------------------+--------------+
LENGTH
LENGTH(value)
Descrição
Retorna o comprimento do valor STRING
ou BYTES
. O valor
retornado está em caracteres para argumentos STRING
e em bytes para o argumento
BYTES
.
Tipo de retorno
INT64
Exemplos
WITH example AS
(SELECT "абвгд" AS characters)
SELECT
characters,
LENGTH(characters) AS string_example,
LENGTH(CAST(characters AS BYTES)) AS bytes_example
FROM example;
+------------+----------------+---------------+
| characters | string_example | bytes_example |
+------------+----------------+---------------+
| абвгд | 5 | 10 |
+------------+----------------+---------------+
LPAD
LPAD(original_value, return_length[, pattern])
Descrição
Retorna um valor STRING
ou BYTES
que consiste em original_value
precedido
por pattern
. O return_length
é um INT64
que
especifica o comprimento do valor retornado. Se original_value
for do tipo
BYTES
, return_length
será o número de bytes. Se original_value
for
do tipo STRING
, return_length
será o número de caracteres.
O valor padrão de pattern
é um espaço em branco.
Tanto original_value
quanto pattern
precisam ser do mesmo tipo de dados.
Se return_length
for menor ou igual ao comprimento de original_value
, essa função retornará o valor original_value
, truncado para o valor de return_length
. Por exemplo, LPAD("hello world", 7);
retorna "hello w"
.
Se original_value
, return_length
ou pattern
for NULL
, essa função
retornará NULL
.
Essa função retornará um erro se:
return_length
for negativo;pattern
estiver vazio.
Tipo de retorno
STRING
ou BYTES
Exemplos
SELECT t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
STRUCT('abc' AS t, 5 AS len),
('abc', 2),
('例子', 4)
]);
+------+-----+----------+
| t | len | LPAD |
|------|-----|----------|
| abc | 5 | " abc" |
| abc | 2 | "ab" |
| 例子 | 4 | " 例子" |
+------+-----+----------+
SELECT t, len, pattern, FORMAT("%T", LPAD(t, len, pattern)) AS LPAD FROM UNNEST([
STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
('abc', 5, '-'),
('例子', 5, '中文')
]);
+------+-----+---------+--------------+
| t | len | pattern | LPAD |
|------|-----|---------|--------------|
| abc | 8 | def | "defdeabc" |
| abc | 5 | - | "--abc" |
| 例子 | 5 | 中文 | "中文中例子" |
+------+-----+---------+--------------+
SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
STRUCT(b'abc' AS t, 5 AS len),
(b'abc', 2),
(b'\xab\xcd\xef', 4)
]);
+-----------------+-----+------------------+
| t | len | LPAD |
|-----------------|-----|------------------|
| b"abc" | 5 | b" abc" |
| b"abc" | 2 | b"ab" |
| b"\xab\xcd\xef" | 4 | b" \xab\xcd\xef" |
+-----------------+-----+------------------+
SELECT
FORMAT("%T", t) AS t,
len,
FORMAT("%T", pattern) AS pattern,
FORMAT("%T", LPAD(t, len, pattern)) AS LPAD
FROM UNNEST([
STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
(b'abc', 5, b'-'),
(b'\xab\xcd\xef', 5, b'\x00')
]);
+-----------------+-----+---------+-------------------------+
| t | len | pattern | LPAD |
|-----------------|-----|---------|-------------------------|
| b"abc" | 8 | b"def" | b"defdeabc" |
| b"abc" | 5 | b"-" | b"--abc" |
| b"\xab\xcd\xef" | 5 | b"\x00" | b"\x00\x00\xab\xcd\xef" |
+-----------------+-----+---------+-------------------------+
LOWER
LOWER(value)
Descrição
Para argumentos STRING
, retorna a string original com todos os caracteres
alfabéticos em minúsculas. O mapeamento entre maiúsculas e minúsculas é feito
de acordo com o
banco de dados de caracteres Unicode (em inglês).
Os mapeamentos específicos do idioma não são considerados.
Para argumentos BYTES
, o argumento é tratado como texto ASCII, com todos os
bytes superiores a 127 intactos.
Tipo de retorno
STRING
ou BYTES
Exemplos
WITH items AS
(SELECT
"FOO" as item
UNION ALL
SELECT
"BAR" as item
UNION ALL
SELECT
"BAZ" as item)
SELECT
LOWER(item) AS example
FROM items;
+---------+
| example |
+---------+
| foo |
| bar |
| baz |
+---------+
LTRIM
LTRIM(value1[, value2])
Descrição
Idêntico a TRIM, mas remove apenas os caracteres iniciais.
Tipo de retorno
STRING
ou BYTES
Exemplos
WITH items AS
(SELECT " apple " as item
UNION ALL
SELECT " banana " as item
UNION ALL
SELECT " orange " as item)
SELECT
CONCAT("#", LTRIM(item), "#") as example
FROM items;
+-------------+
| example |
+-------------+
| #apple # |
| #banana # |
| #orange # |
+-------------+
WITH items AS
(SELECT "***apple***" as item
UNION ALL
SELECT "***banana***" as item
UNION ALL
SELECT "***orange***" as item)
SELECT
LTRIM(item, "*") as example
FROM items;
+-----------+
| example |
+-----------+
| apple*** |
| banana*** |
| orange*** |
+-----------+
WITH items AS
(SELECT "xxxapplexxx" as item
UNION ALL
SELECT "yyybananayyy" as item
UNION ALL
SELECT "zzzorangezzz" as item
UNION ALL
SELECT "xyzpearxyz" as item)
SELECT
LTRIM(item, "xyz") as example
FROM items;
+-----------+
| example |
+-----------+
| applexxx |
| bananayyy |
| orangezzz |
| pearxyz |
+-----------+
NORMALIZE
NORMALIZE(value[, normalization_mode])
Descrição
Usa uma string de valor e retorna como uma string normalizada.
A normalização (em inglês) é utilizada para garantir que duas strings sejam equivalentes. Em geral, a normalização é usada em situações em que duas strings são renderizadas da mesma maneira na tela, mas têm diferentes pontos de código Unicode.
NORMALIZE
é compatível com quatro modos de normalização opcionais:
Valor | Nome | Descrição |
---|---|---|
NFC | Composição canônica do Formulário de normalização | Decompõe e recompõe caracteres por equivalência canônica. |
NFKC | Composição de Compatibilidade do Formulário de Normalização | Decompõe caracteres por compatibilidade e os recompõe por equivalência canônica. |
NFD | Decomposição canônica do Formulário de normalização | Decompõe caracteres por equivalência canônica. Vários caracteres de combinação são organizados em uma ordem específica. |
NFKD | Decomposição de compatibilidade do Formulário de normalização | Decompõe caracteres por compatibilidade. Vários caracteres de combinação são organizados em uma ordem específica. |
O modo de normalização padrão é NFC
.
Tipo de retorno
STRING
Exemplos
SELECT a, b, a = b as normalized
FROM (SELECT NORMALIZE('\u00ea') as a, NORMALIZE('\u0065\u0302') as b)
AS normalize_example;
+---+---+------------+
| a | b | normalized |
+---+---+------------+
| ê | ê | true |
+---+---+------------+
O exemplo a seguir normaliza diferentes caracteres de espaços.
WITH EquivalentNames AS (
SELECT name
FROM UNNEST([
'Jane\u2004Doe',
'John\u2004Smith',
'Jane\u2005Doe',
'Jane\u2006Doe',
'John Smith']) AS name
)
SELECT
NORMALIZE(name, NFKC) AS normalized_name,
COUNT(*) AS name_count
FROM EquivalentNames
GROUP BY 1;
+-----------------+------------+
| normalized_name | name_count |
+-----------------+------------+
| John Smith | 2 |
| Jane Doe | 3 |
+-----------------+------------+
NORMALIZE_AND_CASEFOLD
NORMALIZE_AND_CASEFOLD(value[, normalization_mode])
Descrição
Usa uma STRING
, uma value
e executa as mesmas ações que
NORMALIZE
, assim como realiza
casefold (em inglês) de
operações indiferentes a maiúsculas.
NORMALIZE_AND_CASEFOLD
é compatível com quatro modos de normalização opcionais:
Valor | Nome | Descrição |
---|---|---|
NFC | Composição canônica do Formulário de normalização | Decompõe e recompõe caracteres por equivalência canônica. |
NFKC | Composição de Compatibilidade do Formulário de Normalização | Decompõe caracteres por compatibilidade e os recompõe por equivalência canônica. |
NFD | Decomposição canônica do Formulário de normalização | Decompõe caracteres por equivalência canônica. Vários caracteres de combinação são organizados em uma ordem específica. |
NFKD | Decomposição de compatibilidade do Formulário de normalização | Decompõe caracteres por compatibilidade. Vários caracteres de combinação são organizados em uma ordem específica. |
O modo de normalização padrão é NFC
.
Tipo de retorno
STRING
Exemplo
WITH Strings AS (
SELECT '\u2168' AS a, 'IX' AS b UNION ALL
SELECT '\u0041\u030A', '\u00C5'
)
SELECT a, b,
NORMALIZE_AND_CASEFOLD(a, NFD)=NORMALIZE_AND_CASEFOLD(b, NFD) AS nfd,
NORMALIZE_AND_CASEFOLD(a, NFC)=NORMALIZE_AND_CASEFOLD(b, NFC) AS nfc,
NORMALIZE_AND_CASEFOLD(a, NFKD)=NORMALIZE_AND_CASEFOLD(b, NFKD) AS nkfd,
NORMALIZE_AND_CASEFOLD(a, NFKC)=NORMALIZE_AND_CASEFOLD(b, NFKC) AS nkfc
FROM Strings;
+---+----+-------+-------+------+------+
| a | b | nfd | nfc | nkfd | nkfc |
+---+----+-------+-------+------+------+
| Ⅸ | IX | false | false | true | true |
| Å | Å | true | true | true | true |
+---+----+-------+-------+------+------+
REGEXP_CONTAINS
REGEXP_CONTAINS(value, regexp)
Descrição
Retorna TRUE
se value
for uma correspondência parcial para a expressão regular,
regexp
.
Se o argumento regexp
for inválido, a função retornará um erro.
É possível pesquisar uma correspondência total usando ^
(início do texto) e $
(fim do texto). Devido à precedência do operador de expressão regular, é recomendável usar parênteses em torno de tudo entre ^
e $
.
Tipo de retorno
BOOL
Exemplos
SELECT
email,
REGEXP_CONTAINS(email, r"@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+") AS is_valid
FROM
(SELECT
["foo@example.com", "bar@example.org", "www.example.net"]
AS addresses),
UNNEST(addresses) AS email;
+-----------------+----------+
| email | is_valid |
+-----------------+----------+
| foo@example.com | true |
| bar@example.org | true |
| www.example.net | false |
+-----------------+----------+
# Performs a full match, using ^ and $. Due to regular expression operator
# precedence, it is good practice to use parentheses around everything between ^
# and $.
SELECT
email,
REGEXP_CONTAINS(email, r"^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$")
AS valid_email_address,
REGEXP_CONTAINS(email, r"^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$")
AS without_parentheses
FROM
(SELECT
["a@foo.com", "a@foo.computer", "b@bar.org", "!b@bar.org", "c@buz.net"]
AS addresses),
UNNEST(addresses) AS email;
+----------------+---------------------+---------------------+
| email | valid_email_address | without_parentheses |
+----------------+---------------------+---------------------+
| a@foo.com | true | true |
| a@foo.computer | false | true |
| b@bar.org | true | true |
| !b@bar.org | false | true |
| c@buz.net | false | false |
+----------------+---------------------+---------------------+
REGEXP_EXTRACT
REGEXP_EXTRACT(value, regexp)
Descrição
Retorna a primeira substring em value
que corresponde à expressão regular, regexp
. Retorna NULL
se não houver correspondência.
Se a expressão regular contiver um grupo de captura, a função retornará a substring que corresponde a esse grupo. Se a expressão não contiver um grupo de captura, a função retornará toda a substring correspondente.
Retorna um erro se:
- a expressão regular for inválida;
- a expressão regular tiver mais de um grupo de captura.
Tipo de retorno
STRING
ou BYTES
Exemplos
WITH email_addresses AS
(SELECT "foo@example.com" as email
UNION ALL
SELECT "bar@example.org" as email
UNION ALL
SELECT "baz@example.net" as email)
SELECT
REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+")
AS user_name
FROM email_addresses;
+-----------+
| user_name |
+-----------+
| foo |
| bar |
| baz |
+-----------+
WITH email_addresses AS
(SELECT "foo@example.com" as email
UNION ALL
SELECT "bar@example.org" as email
UNION ALL
SELECT "baz@example.net" as email)
SELECT
REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.([a-zA-Z0-9-.]+$)")
AS top_level_domain
FROM email_addresses;
+------------------+
| top_level_domain |
+------------------+
| com |
| org |
| net |
+------------------+
REGEXP_EXTRACT_ALL
REGEXP_EXTRACT_ALL(value, regexp)
Descrição
Retorna uma matriz de todas as substrings de value
que correspondem à expressão regular, regexp
.
A função REGEXP_EXTRACT_ALL
retorna apenas as correspondências não sobrepostas. Por exemplo, o uso dessa função para extrair ana
de banana
retorna apenas uma substring, não duas.
Tipo de retorno
Uma ARRAY
de STRING
s ou BYTES
.
Exemplos
WITH code_markdown AS
(SELECT "Try `function(x)` or `function(y)`" as code)
SELECT
REGEXP_EXTRACT_ALL(code, "`(.+?)`") AS example
FROM code_markdown;
+----------------------------+
| example |
+----------------------------+
| [function(x), function(y)] |
+----------------------------+
REGEXP_REPLACE
REGEXP_REPLACE(value, regexp, replacement)
Descrição
Retorna uma STRING
em que todas as substrings de value
que
correspondem à expressão regular regexp
são substituídas por replacement
.
Use dígitos de escape com barra invertida (\1 a \9) no argumento replacement
para inserir um texto correspondente ao grupo entre parênteses no padrão regexp
. Use \0 para indicar o texto correspondente inteiro.
A função REGEXP_REPLACE
substitui apenas as correspondências não sobrepostas. Por exemplo, substituir ana
em banana
resulta em apenas uma substituição, não duas.
Se o argumento regexp
não for uma expressão regular válida, essa função retornará um erro.
Tipo de retorno
STRING
ou BYTES
Exemplos
WITH markdown AS
(SELECT "# Heading" as heading
UNION ALL
SELECT "# Another heading" as heading)
SELECT
REGEXP_REPLACE(heading, r"^# ([a-zA-Z0-9\s]+$)", "<h1>\\1</h1>")
AS html
FROM markdown;
+--------------------------+
| html |
+--------------------------+
| <h1>Heading</h1> |
| <h1>Another heading</h1> |
+--------------------------+
REPLACE
REPLACE(original_value, from_value, to_value)
Descrição
Substitui todas as ocorrências de from_value
por to_value
em original_value
.
Se from_value
estiver vazio, nenhuma substituição será feita.
Tipo de retorno
STRING
ou BYTES
Exemplos
WITH desserts AS
(SELECT "apple pie" as dessert
UNION ALL
SELECT "blackberry pie" as dessert
UNION ALL
SELECT "cherry pie" as dessert)
SELECT
REPLACE (dessert, "pie", "cobbler") as example
FROM desserts;
+--------------------+
| example |
+--------------------+
| apple cobbler |
| blackberry cobbler |
| cherry cobbler |
+--------------------+
REPEAT
REPEAT(original_value, repetitions)
Descrição
Retorna um valor STRING
ou BYTES
que consiste em original_value
repetido.
O parâmetro repetitions
especifica o número de vezes para repetir
original_value
. Retorna NULL
se original_value
ou repetitions
forem NULL
.
Essa função retornará um erro se o valor repetitions
for negativo.
Tipo de retorno
STRING
ou BYTES
Exemplos
SELECT t, n, REPEAT(t, n) AS REPEAT FROM UNNEST([
STRUCT('abc' AS t, 3 AS n),
('例子', 2),
('abc', null),
(null, 3)
]);
+------+------+-----------+
| t | n | REPEAT |
|------|------|-----------|
| abc | 3 | abcabcabc |
| 例子 | 2 | 例子例子 |
| abc | NULL | NULL |
| NULL | 3 | NULL |
+------+------+-----------+
REVERSE
REVERSE(value)
Descrição
Retorna o inverso de entrada STRING
ou BYTES
.
Tipo de retorno
STRING
ou BYTES
Exemplos
WITH example AS (
SELECT "foo" AS sample_string, b"bar" AS sample_bytes UNION ALL
SELECT "абвгд" AS sample_string, b"123" AS sample_bytes
)
SELECT
sample_string,
REVERSE(sample_string) AS reverse_string,
sample_bytes,
REVERSE(sample_bytes) AS reverse_bytes
FROM example;
+---------------+----------------+--------------+---------------+
| sample_string | reverse_string | sample_bytes | reverse_bytes |
+---------------+----------------+--------------+---------------+
| foo | oof | bar | rab |
| абвгд | дгвба | 123 | 321 |
+---------------+----------------+--------------+---------------+
RPAD
RPAD(original_value, return_length[, pattern])
Descrição
Retorna um valor STRING
ou BYTES
que consiste em original_value
anexado
a pattern
. O parâmetro return_length
é um
INT64
que especifica o comprimento do
valor retornado. Se original_value
for BYTES
,
return_length
é o número de bytes. Se original_value
for STRING
,
return_length
será o número de caracteres.
O valor padrão de pattern
é um espaço em branco.
Tanto original_value
quanto pattern
precisam ser do mesmo tipo de dados.
Se return_length
for menor ou igual ao comprimento de original_value
, essa função retornará o valor original_value
, truncado para o valor de return_length
. Por exemplo, RPAD("hello world", 7);
retorna "hello w"
.
Se original_value
, return_length
ou pattern
for NULL
, essa função
retornará NULL
.
Essa função retornará um erro se:
return_length
for negativo;pattern
estiver vazio.
Tipo de retorno
STRING
ou BYTES
Exemplos
SELECT t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
STRUCT('abc' AS t, 5 AS len),
('abc', 2),
('例子', 4)
]);
+------+-----+----------+
| t | len | RPAD |
|------|-----|----------|
| abc | 5 | "abc " |
| abc | 2 | "ab" |
| 例子 | 4 | "例子 " |
+------+-----+----------+
SELECT t, len, pattern, FORMAT("%T", RPAD(t, len, pattern)) AS RPAD FROM UNNEST([
STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
('abc', 5, '-'),
('例子', 5, '中文')
]);
+------+-----+---------+--------------+
| t | len | pattern | RPAD |
|------|-----|---------|--------------|
| abc | 8 | def | "abcdefde" |
| abc | 5 | - | "abc--" |
| 例子 | 5 | 中文 | "例子中文中" |
+------+-----+---------+--------------+
SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
STRUCT(b'abc' AS t, 5 AS len),
(b'abc', 2),
(b'\xab\xcd\xef', 4)
]);
+-----------------+-----+------------------+
| t | len | RPAD |
|-----------------|-----|------------------|
| b"abc" | 5 | b"abc " |
| b"abc" | 2 | b"ab" |
| b"\xab\xcd\xef" | 4 | b"\xab\xcd\xef " |
+-----------------+-----+------------------+
SELECT
FORMAT("%T", t) AS t,
len,
FORMAT("%T", pattern) AS pattern,
FORMAT("%T", RPAD(t, len, pattern)) AS RPAD
FROM UNNEST([
STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
(b'abc', 5, b'-'),
(b'\xab\xcd\xef', 5, b'\x00')
]);
+-----------------+-----+---------+-------------------------+
| t | len | pattern | RPAD |
|-----------------|-----|---------|-------------------------|
| b"abc" | 8 | b"def" | b"abcdefde" |
| b"abc" | 5 | b"-" | b"abc--" |
| b"\xab\xcd\xef" | 5 | b"\x00" | b"\xab\xcd\xef\x00\x00" |
+-----------------+-----+---------+-------------------------+
RTRIM
RTRIM(value1[, value2])
Descrição
Idêntico a TRIM, mas remove somente caracteres finais.
Tipo de retorno
STRING
ou BYTES
Exemplos
WITH items AS
(SELECT "***apple***" as item
UNION ALL
SELECT "***banana***" as item
UNION ALL
SELECT "***orange***" as item)
SELECT
RTRIM(item, "*") as example
FROM items;
+-----------+
| example |
+-----------+
| ***apple |
| ***banana |
| ***orange |
+-----------+
WITH items AS
(SELECT "applexxx" as item
UNION ALL
SELECT "bananayyy" as item
UNION ALL
SELECT "orangezzz" as item
UNION ALL
SELECT "pearxyz" as item)
SELECT
RTRIM(item, "xyz") as example
FROM items;
+---------+
| example |
+---------+
| apple |
| banana |
| orange |
| pear |
+---------+
SAFE_CONVERT_BYTES_TO_STRING
SAFE_CONVERT_BYTES_TO_STRING(value)
Descrição
Converte uma sequência de BYTES
em um STRING
. Todos os caracteres UTF-8 inválidos são
substituídos pelo caractere de substituição Unicode, U+FFFD
.
Tipo de retorno
STRING
Exemplos
A declaração a seguir retorna o caractere de substituição Unicode �.
SELECT SAFE_CONVERT_BYTES_TO_STRING(b'\xc2') as safe_convert;
SPLIT
SPLIT(value[, delimiter])
Descrição
Divide value
usando o argumento delimiter
.
Para STRING
, o delimitador padrão é a vírgula ,
.
Para BYTES
, especifique um delimitador.
A divisão em um delimitador vazio produz uma matriz de caracteres UTF-8 para
valores STRING
, e uma matriz de BYTES
para valores BYTES
.
A divisão de uma STRING
vazia retorna uma
ARRAY
com uma única STRING
vazia.
Tipo de retorno
ARRAY
do tipo STRING
ou
ARRAY
do tipo BYTES
Exemplos
WITH letters AS
(SELECT "" as letter_group
UNION ALL
SELECT "a" as letter_group
UNION ALL
SELECT "b c d" as letter_group)
SELECT SPLIT(letter_group, " ") as example
FROM letters;
+----------------------+
| example |
+----------------------+
| [] |
| [a] |
| [b, c, d] |
+----------------------+
STARTS_WITH
STARTS_WITH(value1, value2)
Descrição
Utiliza dois valores STRING
ou BYTES
. Retorna TRUE
se o segundo valor for um
prefixo do primeiro.
Tipo de retorno
BOOL
Exemplos
WITH items AS
(SELECT "foo" as item
UNION ALL
SELECT "bar" as item
UNION ALL
SELECT "baz" as item)
SELECT
STARTS_WITH(item, "b") as example
FROM items;
+---------+
| example |
+---------+
| False |
| True |
| True |
+---------+
STRPOS
STRPOS(string, substring)
Descrição
Retorna o índice com base em 1 da primeira ocorrência de substring
em string
. Retorna 0
se substring
não for encontrado.
Tipo de retorno
INT64
Exemplos
WITH email_addresses AS
(SELECT
"foo@example.com" AS email_address
UNION ALL
SELECT
"foobar@example.com" AS email_address
UNION ALL
SELECT
"foobarbaz@example.com" AS email_address
UNION ALL
SELECT
"quxexample.com" AS email_address)
SELECT
STRPOS(email_address, "@") AS example
FROM email_addresses;
+---------+
| example |
+---------+
| 4 |
| 7 |
| 10 |
| 0 |
+---------+
SUBSTR
SUBSTR(value, position[, length])
Descrição
Retorna uma substring do valor STRING
ou BYTES
fornecido. O
argumento position
é um número inteiro que especifica a posição inicial da
substring, com posição = 1 indicando o primeiro caractere ou byte. O
argumento length
é o número máximo de caracteres para argumentos STRING
ou de bytes para argumentos BYTES
.
Se position
for negativo, a função contará a partir do final de value
, com -1 indicando o último caractere.
Se position
for uma posição fora do lado esquerdo de
STRING
(position
= 0 ou position
< -LENGTH(value)
), a função
começará a partir da posição = 1. Se length
exceder o comprimento de value
,
a função retornará menos que length
caracteres.
Se length
for menor que zero, a função retornará um erro.
Tipo de retorno
STRING
ou BYTES
Exemplos
WITH items AS
(SELECT "apple" as item
UNION ALL
SELECT "banana" as item
UNION ALL
SELECT "orange" as item)
SELECT
SUBSTR(item, 2) as example
FROM items;
+---------+
| example |
+---------+
| pple |
| anana |
| range |
+---------+
WITH items AS
(SELECT "apple" as item
UNION ALL
SELECT "banana" as item
UNION ALL
SELECT "orange" as item)
SELECT
SUBSTR(item, 2, 2) as example
FROM items;
+---------+
| example |
+---------+
| pp |
| an |
| ra |
+---------+
WITH items AS
(SELECT "apple" as item
UNION ALL
SELECT "banana" as item
UNION ALL
SELECT "orange" as item)
SELECT
SUBSTR(item, -2) as example
FROM items;
+---------+
| example |
+---------+
| le |
| na |
| ge |
+---------+
TO_BASE32
TO_BASE32(bytes_expr)
Descrição
Converte uma sequência de BYTES
em um STRING
codificado em base32. Para converter uma
STRING
codificado em base32 em BYTES
, use FROM_BASE32.
Tipo de retorno
STRING
Exemplo
SELECT TO_BASE32(b'abcde\xFF') AS base32_string;
+------------------+
| base32_string |
+------------------+
| MFRGGZDF74====== |
+------------------+
TO_BASE64
TO_BASE64(bytes_expr)
Descrição
Converte uma sequência de BYTES
em um STRING
codificado em base64. Para converter uma
STRING
codificada em base64 em BYTES
, use FROM_BASE64.
Tipo de retorno
STRING
Exemplo
SELECT TO_BASE64(b'\xde\xad\xbe\xef') AS base64_string;
+---------------+
| base64_string |
+---------------+
| 3q2+7w== |
+---------------+
TO_CODE_POINTS
TO_CODE_POINTS(value)
Descrição
Usa um valor e retorna uma matriz de
INT64
.
- Se
value
for umaSTRING
, cada elemento na matriz retornada representará um ponto de código (em inglês). Cada ponto de código está dentro do intervalo de [0, 0xD7FF] e [0xE000, 0x10FFFF]. - Se
value
forBYTES
, cada elemento na matriz será um valor de caractere ASCII estendido no intervalo de [0, 255].
Para converter uma matriz de pontos de código em STRING
ou em BYTES
, consulte
CODE_POINTS_TO_STRING ou
CODE_POINTS_TO_BYTES.
Tipo de retorno
ARRAY
de INT64
Exemplos
O exemplo a seguir mostra os pontos de código de cada elemento em uma matriz de palavras.
SELECT word, TO_CODE_POINTS(word) AS code_points
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word;
+---------+------------------------------------+
| word | code_points |
+---------+------------------------------------+
| foo | [102, 111, 111] |
| bar | [98, 97, 114] |
| baz | [98, 97, 122] |
| giraffe | [103, 105, 114, 97, 102, 102, 101] |
| llama | [108, 108, 97, 109, 97] |
+---------+------------------------------------+
O exemplo a seguir converte as representações de inteiros de BYTES
para os
valores de caracteres ASCII correspondentes.
SELECT word, TO_CODE_POINTS(word) AS bytes_value_as_integer
FROM UNNEST([b'\x00\x01\x10\xff', b'\x66\x6f\x6f']) AS word;
+------------------+------------------------+
| word | bytes_value_as_integer |
+------------------+------------------------+
| \x00\x01\x10\xff | [0, 1, 16, 255] |
| foo | [102, 111, 111] |
+------------------+------------------------+
O exemplo a seguir demonstra a diferença entre os resultados BYTES
e
STRING
.
SELECT TO_CODE_POINTS(b'Ā') AS b_result, TO_CODE_POINTS('Ā') AS s_result;
+------------+----------+
| b_result | s_result |
+------------+----------+
| [196, 128] | [256] |
+------------+----------+
O caractere Ā é representado como uma sequência Unicode de dois bytes. Como
resultado, a versão BYTES
de TO_CODE_POINTS
retorna uma matriz com dois
elementos, enquanto a versão STRING
retorna uma matriz com um único elemento.
TO_HEX
TO_HEX(bytes)
Descrição
Converte uma sequência de BYTES
em uma STRING
hexadecimal. Converte cada byte
na STRING
como dois caracteres hexadecimais no intervalo
(0..9, a..f)
. Para converter um STRING
codificado em hexadecimal
em BYTES
, use FROM_HEX.
Tipo de retorno
STRING
Exemplo
WITH Input AS (
SELECT b'\x00\x01\x02\x03\xAA\xEE\xEF\xFF' AS byte_str UNION ALL
SELECT b'foobar'
)
SELECT byte_str, TO_HEX(byte_str) AS hex_str
FROM Input;
+----------------------------------+------------------+
| byte_string | hex_string |
+----------------------------------+------------------+
| \x00\x01\x02\x03\xaa\xee\xef\xff | 00010203aaeeefff |
| foobar | 666f6f626172 |
+----------------------------------+------------------+
TRIM
TRIM(value1[, value2])
Descrição
Remove todos os caracteres iniciais e finais correspondentes a value2
. Se value2
não for especificado, todos os caracteres de espaço em branco iniciais e finais (conforme definido pelo padrão Unicode) são removidos. Se o primeiro argumento for do tipo
BYTES
, o segundo argumento será obrigatório.
Se value2
tiver mais de um caractere ou byte, a função removerá todos os bytes ou caracteres iniciais ou finais contidos em value2
.
Tipo de retorno
STRING
ou BYTES
Exemplos
WITH items AS
(SELECT " apple " as item
UNION ALL
SELECT " banana " as item
UNION ALL
SELECT " orange " as item)
SELECT
CONCAT("#", TRIM(item), "#") as example
FROM items;
+----------+
| example |
+----------+
| #apple# |
| #banana# |
| #orange# |
+----------+
WITH items AS
(SELECT "***apple***" as item
UNION ALL
SELECT "***banana***" as item
UNION ALL
SELECT "***orange***" as item)
SELECT
TRIM(item, "*") as example
FROM items;
+---------+
| example |
+---------+
| apple |
| banana |
| orange |
+---------+
WITH items AS
(SELECT "xxxapplexxx" as item
UNION ALL
SELECT "yyybananayyy" as item
UNION ALL
SELECT "zzzorangezzz" as item
UNION ALL
SELECT "xyzpearxyz" as item)
SELECT
TRIM(item, "xyz") as example
FROM items;
+---------+
| example |
+---------+
| apple |
| banana |
| orange |
| pear |
+---------+
UPPER
UPPER(value)
Descrição
Para argumentos STRING
, retorna a string original com todos os caracteres alfabéticos
em maiúsculas. O mapeamento entre maiúsculas e minúsculas é feito
de acordo com o
banco de dados de caracteres Unicode (em inglês).
Os mapeamentos específicos do idioma não são considerados.
Para argumentos BYTES
, o argumento é tratado como texto ASCII, com todos os
bytes superiores a 127 intactos.
Tipo de retorno
STRING
ou BYTES
Exemplos
WITH items AS
(SELECT
"foo" as item
UNION ALL
SELECT
"bar" as item
UNION ALL
SELECT
"baz" as item)
SELECT
UPPER(item) AS example
FROM items;
+---------+
| example |
+---------+
| FOO |
| BAR |
| BAZ |
+---------+
Funções JSON
O BigQuery é compatível com funções que ajudam você a recuperar dados armazenados nas strings formatadas como JSON e funções que ajudam a transformar dados em strings formatadas como JSON.
JSON_EXTRACT ou JSON_EXTRACT_SCALAR
JSON_EXTRACT(json_string_expr,
json_path_string_literal)
, que retorna valores JSON como STRINGs.
JSON_EXTRACT_SCALAR(json_string_expr,
json_path_string_literal)
, que retorna valores JSON escalares como STRINGs.
Descrição
Extrai valores JSON ou valores escalares JSON como strings.
json_string_expr
: uma string formatada em JSON. Exemplo:{"class" : {"students" : [{"name" : "Jane"}]}}
json_path_string_literal
: formato JSONpath. Identifica os valores que você quer obter da string formatada em JSON. Sejson_path_string_literal
retornar umnull
JSON, isso será convertido em umNULL
SQL.
Quando a chave JSON usar caracteres inválidos do JSONPath, é possível inserir caracteres de escape usando aspas simples e colchetes.
Tipo de retorno
STRING
s
Exemplos
SELECT JSON_EXTRACT(json_text, '$') AS json_text_string
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-----------------------------------------------------------+
| json_text_string |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}} |
| {"class":{"students":[]}} |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_EXTRACT(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-----------------+
| first_student |
+-----------------+
| {"name":"Jane"} |
| NULL |
| {"name":"John"} |
+-----------------+
SELECT JSON_EXTRACT(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-------------------+
| second_student |
+-------------------+
| NULL |
| NULL |
| NULL |
| "Jamie" |
+-------------------+
SELECT JSON_EXTRACT(json_text, "$.class['students']") AS student_names
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+------------------------------------+
| student_names |
+------------------------------------+
| [{"name":"Jane"}] |
| [] |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
SELECT JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.name') as json_name,
JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.name') as scalar_name,
JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.age') as json_age,
JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.age') as scalar;
+-----------+-------------+----------+--------+
| json_name | scalar_name | json_age | scalar |
+-----------+-------------+----------+--------+
| "Jakob" | Jakob | "6" | 6 |
+-----------+-------------+----------+--------+
Quando a chave JSON usar caracteres inválidos do JSONPath, use aspas simples e
colchetes para inserir caracteres de escape, [' ']
. Exemplo:
SELECT JSON_EXTRACT_SCALAR('{"a.b": {"c": "world"}}', "$['a.b'].c") as hello;
+-------+
| hello |
+-------+
| world |
+-------+
JSON_EXTRACT_ARRAY
JSON_EXTRACT_ARRAY(json_string_expr[, json_path_string_literal])
Descrição
Extrai uma matriz de uma string formatada em JSON.
json_string_expr
: uma string formatada em JSON. Exemplo:{"class" : {"students" : [{"name" : "Jane"}]}}
json_path_string_literal
: formato JSONpath. Identifica os valores que você quer obter da string formatada em JSON. Se esse parâmetro opcional não for fornecido, o símbolo$
do JSONPath será aplicado, o que significa que toda a string formatada em JSON será analisada.
Quando a chave JSON usar caracteres inválidos do JSONPath, é possível inserir caracteres de escape usando aspas simples e colchetes.
Tipo de retorno
ARRAY<STRING>
Exemplos
Isso extrai os itens em uma string formatada em JSON para uma matriz de string:
SELECT JSON_EXTRACT_ARRAY('[1,2,3]') as string_array
+----------------+
| string_array |
+----------------+
| ['1','2','3'] |
+----------------+
Isso extrai uma matriz de strings e a converte em uma matriz de inteiros:
SELECT ARRAY(
SELECT CAST(integer_element as INT64)
FROM UNNEST(
JSON_EXTRACT_ARRAY('[1,2,3]','$')
) AS integer_element
) AS integer_array
+---------------+
| integer_array |
+---------------+
| [1,2,3] |
+---------------+
Isso extrai os valores de string de uma string formatada em JSON para uma matriz:
-- Don't strip the double quotes
SELECT JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]', '$') as string_array
+--------------------------------------+
| string_array |
+--------------------------------------+
| ['"apples"','"oranges"','"grapes"'] |
+--------------------------------------+
-- Strip the double quotes
SELECT ARRAY(
SELECT JSON_EXTRACT_SCALAR(string_element, '$')
FROM UNNEST(JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array
+---------------------------------+
| string_array |
+---------------------------------+
| ['apples', 'oranges', 'grapes'] |
+---------------------------------+
Isso extrai apenas os itens em fruit
para uma matriz:
SELECT JSON_EXTRACT_ARRAY(
'{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}],"vegetables":[{"lettuce":7,"kale": 8}}',
'$.fruit'
) as string_array
+----------------------------------------------------------------------+
| string_array |
+----------------------------------------------------------------------+
| ['{"apples" : 5, "oranges" : 10}' , '{"apples" : 2, "oranges" : 4}'] |
+----------------------------------------------------------------------+
Eles são equivalentes:
JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') as string_array
JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') as string_array
-- The queries above produce this result:
+---------------------------------------+
| string_array |
+---------------------------------------+
| [""apples"", ""oranges"", ""grapes""] |
+---------------------------------------+
Quando a chave JSON usar caracteres inválidos do JSONPath, use aspas simples e
colchetes para inserir caracteres de escape, [' ']
. Exemplo:
SELECT JSON_EXTRACT_ARRAY('{"a.b": {"c": "world"}}', "$['a.b'].c") as hello;
+---------+
| hello |
+---------+
| [world] |
+---------+
Estes exemplos mostram como solicitações inválidas e matrizes vazias são tratadas:
- Se um JSONPath for inválido, um erro será gerado.
- Se uma string formatada em JSON for inválida, a saída será NULL.
- Não há problema em haver matrizes vazias na string formatada em JSON.
-- An error is thrown if you provide an invalid JSONPath.
JSON_EXTRACT_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') as result
-- If a key is not specified when a key is expected, the result is NULL.
JSON_EXTRACT_ARRAY('{"a":"foo"}','$') as result
+--------+
| result |
+--------+
| NULL |
+--------+
-- If a key that does not exist is specified, the result is NULL.
JSON_EXTRACT_ARRAY('{"a":"foo"}','$.b') as result
+--------+
| result |
+--------+
| NULL |
+--------+
-- Empty arrays in JSON-formatted strings are supported.
JSON_EXTRACT_ARRAY('{"a":"foo","b":[]}','$.b') as result
+--------+
| result |
+--------+
| [] |
+--------+
JSON_QUERY ou JSON_VALUE
JSON_QUERY(json_string_expr, json_path_string_literal)
, que retorna valores JSON como STRINGs.
JSON_VALUE(json_string_expr, json_path_string_literal)
, que retorna valores JSON escalares como STRINGs.
Descrição
Extrai valores JSON ou valores escalares JSON como strings.
json_string_expr
: uma string formatada em JSON. Exemplo:{"class" : {"students" : [{"name" : "Jane"}]}}
json_path_string_literal
: formato JSONpath. Identifica os valores que você quer obter da string formatada em JSON. Sejson_path_string_literal
retornar umnull
JSON, isso será convertido em umNULL
SQL.
Quando a chave JSON usar caracteres inválidos do JSONPath, use aspas duplas para inserir caracteres de escape.
Exemplos
SELECT JSON_QUERY(json_text, '$') AS json_text_string
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-----------------------------------------------------------+
| json_text_string |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}} |
| {"class":{"students":[]}} |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_QUERY(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-----------------+
| first_student |
+-----------------+
| {"name":"Jane"} |
| NULL |
| {"name":"John"} |
+-----------------+
SELECT JSON_QUERY(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-------------------+
| second_student |
+-------------------+
| NULL |
| NULL |
| NULL |
| "Jamie" |
+-------------------+
SELECT JSON_QUERY(json_text, '$.class."students"') AS student_names
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+------------------------------------+
| student_names |
+------------------------------------+
| [{"name":"Jane"}] |
| [] |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
SELECT JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.name') as json_name,
JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.name') as scalar_name,
JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.age') as json_age,
JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.age') as scalar;
+-----------+-------------+----------+--------+
| json_name | scalar_name | json_age | scalar |
+-----------+-------------+----------+--------+
| "Jakob" | Jakob | "6" | 6 |
+-----------+-------------+----------+--------+
Quando a chave JSON usar caracteres inválidos do JSONPath, use aspas duplas para inserir caracteres de escape. Exemplo:
SELECT JSON_VALUE('{"a.b": {"c": "world"}}', '$."a.b".c') as hello;
+-------+
| hello |
+-------+
| world |
+-------+
TO_JSON_STRING
TO_JSON_STRING(value[, pretty_print])
Descrição
Retorna uma representação de string formatada em JSON de value
. Essa função aceita um parâmetro pretty_print
opcional. Se pretty_print
estiver presente, o valor retornado será formatado para facilitar a leitura.
Tipo de dados de entrada | Valor retornado |
---|---|
NULL de qualquer tipo | null |
BOOL | true ou false . |
INT64 | O mesmo que -1 0 12345678901 9007199254740992 -9007199254740992 "9007199254740993"
|
NUMERIC | O mesmo que -1 0 "9007199254740993" "123.56"
|
FLOAT64 | +/-inf e NaN são representadas como Infinity , -Infinity e NaN , respectivamente.Caso contrário, é igual a |
STRING | Valor de string entre aspas, com escape de acordo com o padrão JSON.
Especificamente, " , \ e os caracteres de controle de U+0000 a U+001F são escapados. |
BYTES | Valor de escape de base64 RFC 4648 entre aspas. Exemplo:
|
DATE | Data entre aspas. Exemplo: "2017-03-06"
|
TIMESTAMP | Data/hora ISO 8601 entre aspas, em que T separa a data e a hora e Zulu/UTC representa o fuso horário. Exemplo: "2017-03-06T12:34:56.789012Z"
|
DATETIME | Data/hora ISO 8601 entre aspas, em que T separa a data e a hora. Por exemplo: "2017-03-06T12:34:56.789012"
|
TIME | Hora ISO 8601 entre aspas. Exemplo: "12:34:56.789012" |
ARRAY |
[ elem1, elem2, ... ] Onde cada elem é formatado de acordo com o tipo de elemento. A matriz vazia é representada como |
STRUCT | {"field_name1":field_value1,"field_name2":field_value2,...}
Onde cada { "field_name1": field_value1, "field_name2": field_value2, ... } Onde cada Campos com nomes duplicados podem resultar em JSON não analisáveis. Campos anônimos são representados com Nomes de campos UTF-8 inválidos podem resultar em JSON não analisáveis. Valores de string são escapados de acordo com o padrão JSON. Especificamente, |
Tipo de retorno
Representação do valor de string JSON.
Exemplos
Converta linhas em uma tabela para JSON.
WITH Input AS (
SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
t,
TO_JSON_STRING(t) AS json_row
FROM Input AS t;
A consulta acima apresenta o resultado a seguir:
+-----------------------------------+-------------------------------------------------------+
| t | json_row |
+-----------------------------------+-------------------------------------------------------+
| {[1, 2], foo, {true, 2017-04-05}} | {"x":[1,2],"y":"foo","s":{"a":true,"b":"2017-04-05"}} |
| {NULL, , {false, 0001-01-01}} | {"x":null,"y":"","s":{"a":false,"b":"0001-01-01"}} |
| {[3], bar, {NULL, 2016-12-05}} | {"x":[3],"y":"bar","s":{"a":null,"b":"2016-12-05"}} |
+-----------------------------------+-------------------------------------------------------+
Converta linhas em uma tabela para JSON com formatação.
WITH Input AS (
SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
TO_JSON_STRING(t, true) AS json_row
FROM Input AS t;
A consulta acima apresenta o resultado a seguir:
+-----------------------+
| json_row |
+-----------------------+
| { |
| "x": [ |
| 1, |
| 2 |
| ], |
| "y": "foo", |
| "s": { |
| "a": true, |
| "b": "2017-04-05" |
| } |
|} |
| { |
| "x": null, |
| "y": "", |
| "s": { |
| "a": false, |
| "b": "0001-01-01" |
| } |
|} |
| { |
| "x": [ |
| 3 |
| ], |
| "y": "bar", |
| "s": { |
| "a": null, |
| "b": "2016-12-05" |
| } |
|} |
+-----------------------+
Formato JSONPath
A maioria das funções JSON transmitem em um parâmetro json_string_expr
e json_path_string_literal
. O parâmetro json_string_expr
transmite uma string formatada em JSON, e o parâmetro json_path_string_literal
identifica os valores que você quer obter da string formatada em JSON.
É necessário que o parâmetro json_string_expr
seja uma string JSON formatada dessa forma:
{"class" : {"students" : [{"name" : "Jane"}]}}
O parâmetro json_path_string_literal
é construído usando o formato JSONPath. Como parte desse formato, é necessário que o parâmetro inicie com o símbolo $
, que indica o nível mais externo da string formatada em JSON. É possível identificar valores filho usando pontos. Se o objeto JSON for uma matriz, use colchetes para especificar o índice da matriz. Se as chaves tiverem $
, pontos ou colchetes, consulte sobre como inserir caracteres de escape para cada função JSON.
JSONPath | Descrição | Exemplo | Resultado usando o json_string_expr acima |
---|---|---|---|
$ | Objeto ou elemento raiz | "$" | {"class":{"students":[{"name":"Jane"}]}} |
. | Operador filho | "$.class.students" | [{"name":"Jane"}] |
[] | Operador subscrito | "$.class.students[0]" | {"name":"Jane"} |
Uma função JSON retornará NULL
se o parâmetro json_path_string_literal
não corresponder a um valor em json_string_expr
. Se o valor selecionado para uma função escalar não for escalar, como um objeto ou uma matriz, a função retornará NULL
.
Se o JSONPath for inválido, a função gera um erro.
Funções de matriz
ARRAY
ARRAY(subquery)
Descrição
A função ARRAY
retorna uma ARRAY
com um elemento para cada linha em uma subconsulta.
Se subquery
produzir uma tabela SQL, a tabela precisará ter exatamente uma coluna. Cada elemento na ARRAY
de saída é o valor da coluna única de uma linha na tabela.
Se subquery
produzir uma tabela de valores, cada elemento na ARRAY
de saída será toda a linha correspondente da tabela de valores.
Restrições
- As subconsultas não são ordenadas, portanto, não há garantia de que os elementos da
ARRAY
de saída preservem qualquer ordem na tabela de origem para a subconsulta. No entanto, se a subconsulta incluir uma cláusulaORDER BY
, a funçãoARRAY
retornará umARRAY
que honre essa cláusula. - Se a subconsulta retornar mais de uma coluna, a função
ARRAY
retornará um erro. - Se a subconsulta retornar uma coluna de tipo
ARRAY
ou linhas de tipoARRAY
, a funçãoARRAY
retornará um erro: o BigQuery não é compatível comARRAY
s com elementos do tipoARRAY
. - Se a subconsulta não retornar nenhuma linha, a função
ARRAY
retornará umaARRAY
vazia. Ela nunca retorna umaNULL
ARRAY
.
Tipo de retorno
ARRAY
Exemplos
SELECT ARRAY
(SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3) AS new_array;
+-----------+
| new_array |
+-----------+
| [1, 2, 3] |
+-----------+
Para construir um ARRAY
a partir de uma subconsulta que contenha várias colunas, altere a subconsulta para usar SELECT AS STRUCT
. Agora a função ARRAY
retornará ARRAY
de STRUCT
s. A ARRAY
conterá um STRUCT
para cada linha na subconsulta e cada um destes STRUCT
s conterá um campo para cada coluna nessa linha.
SELECT
ARRAY
(SELECT AS STRUCT 1, 2, 3
UNION ALL SELECT AS STRUCT 4, 5, 6) AS new_array;
+------------------------+
| new_array |
+------------------------+
| [{1, 2, 3}, {4, 5, 6}] |
+------------------------+
Da mesma forma, para construir um ARRAY
a partir de uma subconsulta que contenha um ou mais ARRAY
s, altere a subconsulta para usar SELECT AS STRUCT
.
SELECT ARRAY
(SELECT AS STRUCT [1, 2, 3] UNION ALL
SELECT AS STRUCT [4, 5, 6]) AS new_array;
+----------------------------+
| new_array |
+----------------------------+
| [{[1, 2, 3]}, {[4, 5, 6]}] |
+----------------------------+
ARRAY_CONCAT
ARRAY_CONCAT(array_expression_1 [, array_expression_n])
Descrição
Concatena uma ou mais matrizes com o mesmo tipo de elemento em uma única matriz.
Tipo de retorno
ARRAY
Exemplos
SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;
+--------------------------------------------------+
| count_to_six |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6] |
+--------------------------------------------------+
ARRAY_LENGTH
ARRAY_LENGTH(array_expression)
Descrição
Retorna o tamanho da matriz. Retorna 0 para uma matriz vazia. Retorna NULL
se array_expression
for NULL
.
Tipo de retorno
INT64
Exemplos
WITH items AS
(SELECT ["coffee", NULL, "milk" ] as list
UNION ALL
SELECT ["cake", "pie"] as list)
SELECT list, ARRAY_LENGTH(list) AS size
FROM items
ORDER BY size DESC;
+---------------------------------+------+
| list | size |
+---------------------------------+------+
| [coffee, NULL, milk] | 3 |
| [cake, pie] | 2 |
+---------------------------------+------+
ARRAY_TO_STRING
ARRAY_TO_STRING(array_expression, delimiter[, null_text])
Descrição
Retorna uma concatenação dos elementos em array_expression
como um STRING. O valor de array_expression
pode ser uma matriz de tipos de dados BYTES ou STRING.
Se o parâmetro null_text
for usado, a função substituirá todos os valores NULL
na matriz pelo valor de null_text
.
Se o parâmetro null_text
não for usado, a função omitirá o valor NULL
e seu delimitador anterior.
Exemplos
WITH items AS
(SELECT ["coffee", "tea", "milk" ] as list
UNION ALL
SELECT ["cake", "pie", NULL] as list)
SELECT ARRAY_TO_STRING(list, '--') AS text
FROM items;
+--------------------------------+
| text |
+--------------------------------+
| coffee--tea--milk |
| cake--pie |
+--------------------------------+
WITH items AS
(SELECT ["coffee", "tea", "milk" ] as list
UNION ALL
SELECT ["cake", "pie", NULL] as list)
SELECT ARRAY_TO_STRING(list, '--', 'MISSING') AS text
FROM items;
+--------------------------------+
| text |
+--------------------------------+
| coffee--tea--milk |
| cake--pie--MISSING |
+--------------------------------+
GENERATE_ARRAY
GENERATE_ARRAY(start_expression, end_expression[, step_expression])
Descrição
Retorna uma matriz de valores. Os parâmetros start_expression
e end_expression
determinam o início e o fim inclusivos da matriz.
A função GENERATE_ARRAY
aceita estes tipos de dados como entradas:
- INT64
- NUMERIC
- FLOAT64
O parâmetro step_expression
determina o incremento usado para gerar valores de matriz. O valor padrão desse parâmetro é 1
.
Essa função retornará um erro se step_expression
for definido como 0 ou se alguma entrada for NaN
.
Se qualquer argumento for NULL
, a função retornará uma matriz NULL
.
Tipo de dados retornados
ARRAY
Exemplos
O seguinte retorna uma matriz de números inteiros, com uma etapa padrão de 1.
SELECT GENERATE_ARRAY(1, 5) AS example_array;
+-----------------+
| example_array |
+-----------------+
| [1, 2, 3, 4, 5] |
+-----------------+
O seguinte retorna uma matriz usando um tamanho do passo especificado pelo usuário.
SELECT GENERATE_ARRAY(0, 10, 3) AS example_array;
+---------------+
| example_array |
+---------------+
| [0, 3, 6, 9] |
+---------------+
O seguinte retorna uma matriz usando um valor negativo -3
para o tamanho do passo.
SELECT GENERATE_ARRAY(10, 0, -3) AS example_array;
+---------------+
| example_array |
+---------------+
| [10, 7, 4, 1] |
+---------------+
O seguinte retorna uma matriz usando o mesmo valor para start_expression
e end_expression
.
SELECT GENERATE_ARRAY(4, 4, 10) AS example_array;
+---------------+
| example_array |
+---------------+
| [4] |
+---------------+
O seguinte retorna uma matriz vazia, porque start_expression
é maior que end_expression
, e o valor step_expression
é positivo.
SELECT GENERATE_ARRAY(10, 0, 3) AS example_array;
+---------------+
| example_array |
+---------------+
| [] |
+---------------+
O seguinte retorna uma matriz NULL
porque end_expression
é NULL
.
SELECT GENERATE_ARRAY(5, NULL, 1) AS example_array;
+---------------+
| example_array |
+---------------+
| NULL |
+---------------+
O seguinte retorna várias matrizes.
SELECT GENERATE_ARRAY(start, 5) AS example_array
FROM UNNEST([3, 4, 5]) AS start;
+---------------+
| example_array |
+---------------+
| [3, 4, 5] |
| [4, 5] |
| [5] |
+---------------+
GENERATE_DATE_ARRAY
GENERATE_DATE_ARRAY(start_date, end_date[, INTERVAL INT64_expr date_part])
Descrição
Retorna uma matriz de datas. Os parâmetros start_date
e end_date
determinam o início e o fim inclusivos da matriz.
A função GENERATE_DATE_ARRAY
aceita estes tipos de dados como entradas:
start_date
precisa ser uma DATEend_date
precisa ser uma DATEINT64_expr
precisa ser um INT64date_part
precisa ser DAY, WEEK, MONTH, QUARTER ou YEAR.
O parâmetro INT64_expr
determina o incremento usado para gerar datas. O valor padrão dele é de 1 dia.
Essa função retornará um erro se INT64_expr
for definido como 0.
Tipo de dados retornados
Um ARRAY contendo 0 ou mais valores DATE.
Exemplos
O seguinte retorna uma matriz de datas, com uma etapa padrão de 1.
SELECT GENERATE_DATE_ARRAY('2016-10-05', '2016-10-08') AS example;
+--------------------------------------------------+
| example |
+--------------------------------------------------+
| [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] |
+--------------------------------------------------+
O seguinte retorna uma matriz usando um tamanho do passo especificado pelo usuário.
SELECT GENERATE_DATE_ARRAY(
'2016-10-05', '2016-10-09', INTERVAL 2 DAY) AS example;
+--------------------------------------+
| example |
+--------------------------------------+
| [2016-10-05, 2016-10-07, 2016-10-09] |
+--------------------------------------+
O seguinte retorna uma matriz usando um valor negativo -3
para o tamanho do passo.
SELECT GENERATE_DATE_ARRAY('2016-10-05',
'2016-10-01', INTERVAL -3 DAY) AS example;
+--------------------------+
| example |
+--------------------------+
| [2016-10-05, 2016-10-02] |
+--------------------------+
O seguinte retorna uma matriz usando o mesmo valor para start_date
e end_date
.
SELECT GENERATE_DATE_ARRAY('2016-10-05',
'2016-10-05', INTERVAL 8 DAY) AS example;
+--------------+
| example |
+--------------+
| [2016-10-05] |
+--------------+
O seguinte retorna uma matriz vazia, porque start_date
é maior que end_date
, e o valor step
é positivo.
SELECT GENERATE_DATE_ARRAY('2016-10-05',
'2016-10-01', INTERVAL 1 DAY) AS example;
+---------+
| example |
+---------+
| [] |
+---------+
O seguinte retorna uma matriz NULL
, porque uma das entradas é
NULL
.
SELECT GENERATE_DATE_ARRAY('2016-10-05', NULL) AS example;
+---------+
| example |
+---------+
| NULL |
+---------+
O seguinte retorna uma matriz de datas, usando MONTH como o intervalo date_part
:
SELECT GENERATE_DATE_ARRAY('2016-01-01',
'2016-12-31', INTERVAL 2 MONTH) AS example;
+--------------------------------------------------------------------------+
| example |
+--------------------------------------------------------------------------+
| [2016-01-01, 2016-03-01, 2016-05-01, 2016-07-01, 2016-09-01, 2016-11-01] |
+--------------------------------------------------------------------------+
O seguinte usa datas não constantes para gerar uma matriz.
SELECT GENERATE_DATE_ARRAY(date_start, date_end, INTERVAL 1 WEEK) AS date_range
FROM (
SELECT DATE '2016-01-01' AS date_start, DATE '2016-01-31' AS date_end
UNION ALL SELECT DATE "2016-04-01", DATE "2016-04-30"
UNION ALL SELECT DATE "2016-07-01", DATE "2016-07-31"
UNION ALL SELECT DATE "2016-10-01", DATE "2016-10-31"
) AS items;
+--------------------------------------------------------------+
| date_range |
+--------------------------------------------------------------+
| [2016-01-01, 2016-01-08, 2016-01-15, 2016-01-22, 2016-01-29] |
| [2016-04-01, 2016-04-08, 2016-04-15, 2016-04-22, 2016-04-29] |
| [2016-07-01, 2016-07-08, 2016-07-15, 2016-07-22, 2016-07-29] |
| [2016-10-01, 2016-10-08, 2016-10-15, 2016-10-22, 2016-10-29] |
+--------------------------------------------------------------+
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 inclusivos de ARRAY
.
A função GENERATE_TIMESTAMP_ARRAY
aceita estes tipos de dados como entradas:
start_timestamp
:TIMESTAMP
end_timestamp
:TIMESTAMP
step_expression
:INT64
- Os valores
date_part
permitidos são:MICROSECOND
,MILLISECOND
,SECOND
,MINUTE
,HOUR
ouDAY
.
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 um ARRAY
de TIMESTAMP
s em intervalos de 1 dia.
SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-07 00:00:00',
INTERVAL 1 DAY) AS timestamp_array;
+--------------------------------------------------------------------------+
| timestamp_array |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-06 00:00:00+00, 2016-10-07 00:00:00+00] |
+--------------------------------------------------------------------------+
O exemplo a seguir retorna uma ARRAY
de TIMESTAMP
s 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 um 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 um ARRAY
vazio, 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 ARRAY
s de TIMESTAMP
s a partir 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.
Esta função aceita um parâmetro time_zone
opcional. Esse parâmetro é uma string que representa o fuso horário a ser usado. Se nenhum fuso horário for especificado, será usado o padrão 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
, essa 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. O part
precisa ser um dos seguintes:
DAYOFWEEK
: retorna valores no intervalo [1,7] com domingo como primeiro dia da semana.DAY
DAYOFYEAR
WEEK
: retorna o número da semana da data no intervalo [0, 53]. As semanas começam no domingo. Os dias anteriores ao primeiro domingo do ano estão na semana 0.WEEK(<WEEKDAY>)
: retorna o número da semana da data no intervalo [0, 53]. As semanas começam emWEEKDAY
. As datas anteriores ao primeiroWEEKDAY
do ano estão na semana 0. Os valores válidos paraWEEKDAY
sãoSUNDAY
,MONDAY
,TUESDAY
,WEDNESDAY
,THURSDAY
,FRIDAY
eSATURDAY
.ISOWEEK
: retorna o número da semana ISO 8601 dedate_expression
. AsISOWEEK
s começam na segunda-feira. Os valores de retorno estão no intervalo [1, 53]. O primeiroISOWEEK
de cada ano ISO começa na segunda-feira anterior à primeira quinta-feira do ano do calendário gregoriano.MONTH
QUARTER
: retorna valores na faixa [1,4].YEAR
ISOYEAR
: retorna o ano da numeração da semana ISO 8601, que é o ano do calendário gregoriano que contém a quinta-feira da semana à qualdate_expression
pertence.
Tipo de dados retornados
INT64
Exemplos
No exemplo a seguir, EXTRACT
retorna um valor correspondente à
parte da data DAY
.
SELECT EXTRACT(DAY FROM DATE '2013-12-25') as the_day;
+---------+
| the_day |
+---------+
| 25 |
+---------+
No exemplo a seguir, EXTRACT
retorna valores correspondentes a diferentes
partes de data de uma coluna de datas próximas ao final do ano.
SELECT
date,
EXTRACT(ISOYEAR FROM date) AS isoyear,
EXTRACT(ISOWEEK FROM date) AS isoweek,
EXTRACT(YEAR FROM date) AS year,
EXTRACT(WEEK FROM date) AS week
FROM UNNEST(GENERATE_DATE_ARRAY('2015-12-23', '2016-01-09')) AS date
ORDER BY date;
+------------+---------+---------+------+------+
| date | isoyear | isoweek | year | week |
+------------+---------+---------+------+------+
| 2015-12-23 | 2015 | 52 | 2015 | 51 |
| 2015-12-24 | 2015 | 52 | 2015 | 51 |
| 2015-12-25 | 2015 | 52 | 2015 | 51 |
| 2015-12-26 | 2015 | 52 | 2015 | 51 |
| 2015-12-27 | 2015 | 52 | 2015 | 52 |
| 2015-12-28 | 2015 | 53 | 2015 | 52 |
| 2015-12-29 | 2015 | 53 | 2015 | 52 |
| 2015-12-30 | 2015 | 53 | 2015 | 52 |
| 2015-12-31 | 2015 | 53 | 2015 | 52 |
| 2016-01-01 | 2015 | 53 | 2016 | 0 |
| 2016-01-02 | 2015 | 53 | 2016 | 0 |
| 2016-01-03 | 2015 | 53 | 2016 | 1 |
| 2016-01-04 | 2016 | 1 | 2016 | 1 |
| 2016-01-05 | 2016 | 1 | 2016 | 1 |
| 2016-01-06 | 2016 | 1 | 2016 | 1 |
| 2016-01-07 | 2016 | 1 | 2016 | 1 |
| 2016-01-08 | 2016 | 1 | 2016 | 1 |
| 2016-01-09 | 2016 | 1 | 2016 | 1 |
+------------+---------+---------+------+------+
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])
3. DATE(datetime_expression)
Descrição
- Cria uma DATE com os valores INT64 que representam o ano, o mês e o dia.
- Extrai a DATE de uma expressão TIMESTAMP. Aceita um parâmetro opcional para especificar um fuso horário. Se nenhum fuso horário for especificado, será usado o padrão UTC.
- Extrai a DATE de uma expressão DATETIME.
Tipo de dados retornados
DATE
Exemplo
SELECT
DATE(2016, 12, 25) as date_ymd,
DATE(DATETIME "2016-12-25 23:59:59") as date_dt,
DATE(TIMESTAMP "2016-12-25 05:30:00+07", "America/Los_Angeles") as date_tstz;
+------------+------------+------------+
| date_ymd | date_dt | date_tstz |
+------------+------------+------------+
| 2016-12-25 | 2016-12-25 | 2016-12-24 |
+------------+------------+------------+
DATE_ADD
DATE_ADD(date_expression, INTERVAL int64_expression date_part)
Descrição
Adiciona um intervalo de tempo especificado a uma DATE.
DATE_ADD
aceita os seguintes valores date_part
:
DAY
WEEK
. Equivalente a 7DAY
s.MONTH
QUARTER
YEAR
Será necessário um tratamento especial para as partes MONTH, QUARTER e YEAR quando a data for o último dia do mês ou estiver perto dele. Se o mês resultante tiver menos dias do que a data original, o dia resultante será o último dia do mês novo.
Tipo de dados retornados
DATE
Exemplo
SELECT DATE_ADD(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_later;
+--------------------+
| five_days_later |
+--------------------+
| 2008-12-30 |
+--------------------+
DATE_SUB
DATE_SUB(date_expression, INTERVAL int64_expression date_part)
Descrição
Subtrai um intervalo de tempo especificado de uma DATE.
DATE_SUB
aceita os seguintes valores date_part
:
DAY
WEEK
. Equivalente a 7DAY
s.MONTH
QUARTER
YEAR
Será necessário um tratamento especial para as partes MONTH, QUARTER e YEAR quando a data for o último dia do mês ou estiver perto dele. Se o mês resultante tiver menos dias do que a data original, o dia resultante será o último dia do mês novo.
Tipo de dados retornados
DATE
Exemplo
SELECT DATE_SUB(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_ago;
+---------------+
| five_days_ago |
+---------------+
| 2008-12-20 |
+---------------+
DATE_DIFF
DATE_DIFF(date_expression_a, date_expression_b, date_part)
Descrição
Retorna o número de intervalos date_part
especificados inteiros entre dois objetos DATE
(date_expression_a
- date_expression_b
).
Se o primeiro DATE
for anterior ao segundo,
a saída é negativa.
DATE_DIFF
aceita os seguintes valores date_part
:
DAY
WEEK
: esta parte da data começa no domingo.WEEK(<WEEKDAY>)
: esta parte da data começa emWEEKDAY
. Os valores válidos paraWEEKDAY
sãoSUNDAY
,MONDAY
,TUESDAY
,WEDNESDAY
,THURSDAY
,FRIDAY
eSATURDAY
.ISOWEEK
: usa limites da semana ISO 8601. As semanas ISO começam na segunda-feira.MONTH
QUARTER
YEAR
ISOYEAR
: usa o limite de ano de numeração da semana ISO 8601. O limite do ano ISO é a segunda-feira da primeira semana que contém a quinta-feira pertencente ao ano do calendário gregoriano correspondente.
Tipo de dados retornados
INT64
Exemplo
SELECT DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY) as days_diff;
+-----------+
| days_diff |
+-----------+
| 559 |
+-----------+
SELECT
DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', DAY) as days_diff,
DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', WEEK) as weeks_diff;
+-----------+------------+
| days_diff | weeks_diff |
+-----------+------------+
| 1 | 1 |
+-----------+------------+
O exemplo acima mostra o resultado de DATE_DIFF
para dois dias consecutivos.
DATE_DIFF
com a parte da data WEEK
retorna 1 porque DATE_DIFF
conta o número de limites da parte da data nesse intervalo de datas. Cada WEEK
começa no domingo, então há um limite da parte da data entre sábado, 2017-10-14 e domingo, 2017-10-15.
No exemplo a seguir, mostraremos o resultado de DATE_DIFF
para duas datas em anos diferentes. DATE_DIFF
com a parte da data YEAR
retorna 3 porque conta o número de limites do ano do calendário gregoriano entre as duas datas. DATE_DIFF
com a parte da data ISOYEAR
retorna 2 porque a segunda data pertence ao ano ISO 2015. A primeira quinta-feira do ano-calendário de 2015 foi 2015-01-01, então o ano ISO 2015 começa na segunda-feira anterior, 2014-12-29.
SELECT
DATE_DIFF('2017-12-30', '2014-12-30', YEAR) AS year_diff,
DATE_DIFF('2017-12-30', '2014-12-30', ISOYEAR) AS isoyear_diff;
+-----------+--------------+
| year_diff | isoyear_diff |
+-----------+--------------+
| 3 | 2 |
+-----------+--------------+
O exemplo a seguir mostra o resultado de DATE_DIFF
para dois dias consecutivos. A primeira data cai em uma segunda-feira e a segunda data cai em um domingo. DATE_DIFF
com a parte da data WEEK
retorna 0 porque esta parte da data
usa semanas que começam no domingo. DATE_DIFF
com a parte da data 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 para date_part
:
DAY
WEEK
WEEK(<WEEKDAY>)
: truncadate_expression
no limite da semana anterior, em que as semanas começam emWEEKDAY
. Os valores válidos paraWEEKDAY
sãoSUNDAY
,MONDAY
,TUESDAY
,WEDNESDAY
,THURSDAY
,FRIDAY
eSATURDAY
.ISOWEEK
: truncadate_expression
no limite da semana ISO 8601 anterior.ISOWEEK
s começam na segunda-feira. A primeiraISOWEEK
de cada ano ISO contém a primeira quinta-feira do ano do calendário gregoriano correspondente. Qualquerdate_expression
anterior a esta vai truncar na segunda-feira anterior.MONTH
QUARTER
YEAR
ISOYEAR
: truncadate_expression
no limite do ano de numeração da semana ISO 8601 anterior. O limite do ano ISO é a segunda-feira da primeira semana que contém a quinta-feira pertencente ao ano do calendário gregoriano correspondente.
Tipo de dados retornados
DATE
Exemplos
SELECT DATE_TRUNC(DATE '2008-12-25', MONTH) as month;
+------------+
| month |
+------------+
| 2008-12-01 |
+------------+
No exemplo a seguir, a data original cai em um domingo. Como date_part
é WEEK(MONDAY)
, DATE_TRUNC
retorna a DATE
da 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 de 2015. No entanto, DATE_TRUNC
com a parte da data ISOYEAR
trunca a date_expression
no início do ano ISO, não no ano do calendário gregoriano. A primeira quinta-feira do ano-calendário de 2015 foi 2015-01-01, então o ano ISO 2015 começa na segunda-feira anterior, 2014-12-29.
Portanto, o limite do ano ISO anterior a date_expression
2015-06-15 é 2014-12-29.
SELECT
DATE_TRUNC('2015-06-15', ISOYEAR) AS isoyear_boundary,
EXTRACT(ISOYEAR FROM DATE '2015-06-15') AS isoyear_number;
+------------------+----------------+
| isoyear_boundary | isoyear_number |
+------------------+----------------+
| 2014-12-29 | 2015 |
+------------------+----------------+
DATE_FROM_UNIX_DATE
DATE_FROM_UNIX_DATE(int64_expression)
Descrição
Interpreta int64_expression
como o número de dias desde 01-01-1970.
Tipo de dados retornados
DATE
Exemplo
SELECT DATE_FROM_UNIX_DATE(14238) as date_from_epoch;
+-----------------+
| date_from_epoch |
+-----------------+
| 2008-12-25 |
+-----------------+
FORMAT_DATE
FORMAT_DATE(format_string, date_expr)
Descrição
Formata date_expr
de acordo com a format_string
especificada.
Consulte Elementos de formatos compatíveis com DATE para ver uma lista de elementos de formato aceitos por essa função.
Tipo de dados retornados
STRING
Exemplos
SELECT FORMAT_DATE("%x", DATE "2008-12-25") as US_format;
+------------+
| US_format |
+------------+
| 12/25/08 |
+------------+
SELECT FORMAT_DATE("%b-%d-%Y", DATE "2008-12-25") AS formatted;
+-------------+
| formatted |
+-------------+
| Dec-25-2008 |
+-------------+
SELECT FORMAT_DATE("%b %Y", DATE "2008-12-25") AS formatted;
+-------------+
| formatted |
+-------------+
| Dec 2008 |
+-------------+
PARSE_DATE
PARSE_DATE(format_string, date_string)
Descrição
Converte uma representação de string de data em um
objeto DATE
.
format_string
contém os elementos de formato
que definem como date_string
é formatado. Cada elemento em
date_string
precisa ter um elemento correspondente em format_string
. O
local de cada elemento em format_string
precisa corresponder à localização de
cada elemento em date_string
.
-- This works because elements on both sides match.
SELECT PARSE_DATE("%A %b %e %Y", "Thursday Dec 25 2008")
-- This doesn't work because the year element is in different locations.
SELECT PARSE_DATE("%Y %A %b %e", "Thursday Dec 25 2008")
-- This doesn't work because one of the year elements is missing.
SELECT PARSE_DATE("%A %b %e", "Thursday Dec 25 2008")
-- This works because %F can find all matching elements in date_string.
SELECT PARSE_DATE("%F", "2000-12-30")
A string de formato é totalmente compatível com a maioria dos elementos de formato, exceto
%Q
, %a
, %A
, %g
%G
, %j
, %u
, %U
, %V
, %w
e %W
.
Ao usar PARSE_DATE
, tenha em mente os seguintes itens:
- Campos não especificados. Qualquer campo não especificado é inicializado em
1970-01-01
. - Nomes que não diferenciam maiúsculas e minúsculas. Nomes, como
Monday
,February
e outros, não diferenciam maiúsculas de minúsculas. - Espaço em branco. Um ou mais espaços em branco consecutivos na string de formato correspondem a zero ou mais espaços em branco consecutivos na string de data. Além disso, espaços em branco à esquerda e à direita na string de data sempre são permitidos, mesmo que não estejam na string de formato.
- Precedência de formato. Quando dois (ou mais) elementos de formato têm informações sobrepostas (por exemplo,
%F
e%Y
afetam o ano), o último geralmente substitui os anteriores.
Tipo de dados retornados
DATE
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 1970-01-01.
Tipo de dados retornados
INT64
Exemplo
SELECT UNIX_DATE(DATE "2008-12-25") as days_from_epoch;
+-----------------+
| days_from_epoch |
+-----------------+
| 14238 |
+-----------------+
Elementos de formatos compatíveis com DATE
Salvo indicação em contrário, as funções DATE que usam strings de formato aceitam os seguintes elementos:
Elemento de formato | Descrição |
%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 um 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. Cada ano ISO começa na segunda-feira antes da primeira quinta-feira do ano calendário gregoriano. Observe que %G e %Y podem produzir resultados diferentes próximos aos limites do ano gregoriano, em que o ano gregoriano e o ano ISO podem divergir. |
%g | O ano ISO 8601 sem o século como número decimal (00-99). Cada ano ISO começa na segunda-feira antes da primeira quinta-feira do ano calendário gregoriano. Observe que %g e %y podem produzir resultados diferentes próximos aos limites do ano gregoriano, em que o ano gregoriano e o ano ISO podem divergir. |
%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. |
%Q | O trimestre como um número decimal (de 1–4). |
%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 ISO 8601 do ano (segunda-feira como o primeiro dia da semana) como um número decimal (01–53). Se a semana que tem 1 de janeiro tiver quatro ou mais dias no ano novo, então será a semana 1. Caso contrário, será a semana 53 do ano anterior e a semana seguinte será a semana 1. |
%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 misturado com %C. Se %C não for especificado, os anos 00-68 são os 2000, enquanto os anos 69-99 são os 1900. |
%E4Y | Anos com quatro caracteres (0001 ... 9999). %Y produz quantos caracteres forem necessários para processar totalmente o ano. |
Funções datetime
O BigQuery é compatível com as funções DATETIME
a seguir.
Todas as saídas são formatadas automaticamente de acordo com o ISO 8601,
separando a data e a hora com um T
.
CURRENT_DATETIME
CURRENT_DATETIME([timezone])
Descrição
Retorna a hora atual como um objeto DATETIME
.
Esta função aceita um parâmetro timezone
opcional.
Para informações sobre como especificar um fuso horário, consulte
Definições de fuso horário.
Tipo de dados retornados
DATETIME
Exemplo
SELECT CURRENT_DATETIME() as now;
+----------------------------+
| now |
+----------------------------+
| 2016-05-19T10: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
- Cria um objeto
DATETIME
usando valores INT64 que representam o ano, mês, dia, hora, minuto e segundo. - Cria um objeto
DATETIME
usando um objeto DATE e um objeto opcional TIME. - 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-25T05:30:00 | 2008-12-24T21:30:00 |
+---------------------+---------------------+
EXTRACT
EXTRACT(part FROM datetime_expression)
Descrição
Retorna um valor que corresponde ao
part
especificado de um datetime_expression
fornecido.
Os valores 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 no domingo. Os dias anteriores ao primeiro domingo do ano estão na semana 0.WEEK(<WEEKDAY>)
: retorna o número da semana dedatetime_expression
no intervalo [0, 53]. As semanas começam emWEEKDAY
.datetime
s antes da primeiraWEEKDAY
do ano são na semana zero. Os valores válidos paraWEEKDAY
sãoSUNDAY
,MONDAY
,TUESDAY
,WEDNESDAY
,THURSDAY
,FRIDAY
eSATURDAY
.ISOWEEK
: retorna o número da semana ISO 8601 dedatetime_expression
. AsISOWEEK
s começam na segunda-feira. Os valores de retorno estão no intervalo [1, 53]. O primeiroISOWEEK
de cada ano ISO começa na segunda-feira anterior à primeira quinta-feira do ano do calendário gregoriano.MONTH
QUARTER
YEAR
ISOYEAR
: retorna o ano da numeração da semana ISO 8601, que é o ano do calendário gregoriano que contém a quinta-feira da semana à qualdate_expression
pertence.DATE
TIME
Os valores retornados truncam períodos menores de tempo. Por exemplo, ao extrair segundos, EXTRACT
trunca os valores de milissegundos e microssegundos.
Tipo de dados retornados
INT64
, exceto nos seguintes casos:
- Se
part
forDATE
, retornará um objetoDATE
. - Se
part
forTIME
, retornará um objetoTIME
.
Exemplos
No exemplo a seguir, EXTRACT
retorna um valor correspondente à parte de hora de HOUR
.
SELECT EXTRACT(HOUR FROM DATETIME(2008, 12, 25, 15, 30, 00)) as hour;
+------------------+
| hour |
+------------------+
| 15 |
+------------------+
No exemplo a seguir, EXTRACT
retorna valores correspondentes a diferentes
partes da hora de uma coluna de datas e horas.
WITH Datetimes AS (
SELECT DATETIME '2005-01-03 12:34:56' AS datetime UNION ALL
SELECT DATETIME '2007-12-31' UNION ALL
SELECT DATETIME '2009-01-01' UNION ALL
SELECT DATETIME '2009-12-31' UNION ALL
SELECT DATETIME '2017-01-02' UNION ALL
SELECT DATETIME '2017-05-26'
)
SELECT
datetime,
EXTRACT(ISOYEAR FROM datetime) AS isoyear,
EXTRACT(ISOWEEK FROM datetime) AS isoweek,
EXTRACT(YEAR FROM datetime) AS year,
EXTRACT(WEEK FROM datetime) AS week
FROM Datetimes
ORDER BY datetime;
+---------------------+---------+---------+------+------+
| datetime | isoyear | isoweek | year | week |
+---------------------+---------+---------+------+------+
| 2005-01-03T12:34:56 | 2005 | 1 | 2005 | 1 |
| 2007-12-31T00:00:00 | 2008 | 1 | 2007 | 52 |
| 2009-01-01T00:00:00 | 2009 | 1 | 2009 | 0 |
| 2009-12-31T00:00:00 | 2009 | 53 | 2009 | 52 |
| 2017-01-02T00:00:00 | 2017 | 1 | 2017 | 1 |
| 2017-05-26T00:00:00 | 2017 | 21 | 2017 | 21 |
+---------------------+---------+---------+------+------+
No exemplo a seguir, datetime_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 DATETIME(TIMESTAMP "2017-11-05 00:00:00+00", "UTC") AS datetime)
SELECT
datetime,
EXTRACT(WEEK(SUNDAY) FROM datetime) AS week_sunday,
EXTRACT(WEEK(MONDAY) FROM datetime) AS week_monday
FROM table;
+---------------------+-------------+---------------+
| datetime | week_sunday | week_monday |
+---------------------+-------------+---------------+
| 2017-11-05T00:00:00 | 45 | 44 |
+---------------------+-------------+---------------+
DATETIME_ADD
DATETIME_ADD(datetime_expression, INTERVAL int64_expression part)
Descrição
Adiciona unidades int64_expression
de part
ao objeto DATETIME
.
DATETIME_ADD
aceita os seguintes valores para part
:
MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
DAY
WEEK
. Equivalente a 7DAY
s.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 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-25T15:30:00 | 2008-12-25T15:40:00 |
+-----------------------------+------------------------+
DATETIME_SUB
DATETIME_SUB(datetime_expression, INTERVAL int64_expression part)
Descrição
Subtrai unidades int64_expression
de part
do DATETIME
.
DATETIME_SUB
aceita os seguintes valores para part
:
MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
DAY
WEEK
. Equivalente a 7DAY
s.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 o DATETIME
original, 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-25T15:30:00 | 2008-12-25T15:20:00 |
+-----------------------------+------------------------+
DATETIME_DIFF
DATETIME_DIFF(datetime_expression_a, datetime_expression_b, part)
Descrição
Retorna o número de intervalos part
inteiros especificados entre dois
objetos DATETIME
(datetime_expression_a
- datetime_expression_b
).
Se o primeiro DATETIME
for anterior ao segundo,
a saída será negativa. 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 para part
:
MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
DAY
WEEK
: esta parte da data começa no domingo.WEEK(<WEEKDAY>)
: esta parte da data começa emWEEKDAY
. Os valores válidos paraWEEKDAY
sãoSUNDAY
,MONDAY
,TUESDAY
,WEDNESDAY
,THURSDAY
,FRIDAY
eSATURDAY
.ISOWEEK
: usa limites da semana ISO 8601. As semanas ISO começam na segunda-feira.MONTH
QUARTER
YEAR
ISOYEAR
: usa o limite de ano de numeração da semana ISO 8601. O limite do ano ISO é a segunda-feira da primeira semana que contém a quinta-feira pertencente ao ano do calendário gregoriano correspondente.
Tipo de dados retornados
INT64
Exemplo
SELECT
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-07T10:20:00 | 2008-12-25T15: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 DATETIME
s 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 DATETIME
s. 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
s. 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 em uma 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 para part
:
MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
DAY
WEEK
WEEK(<WEEKDAY>)
: truncadatetime_expression
no limite da semana anterior, em que as semanas começam emWEEKDAY
. Os valores válidos paraWEEKDAY
sãoSUNDAY
,MONDAY
,TUESDAY
,WEDNESDAY
,THURSDAY
,FRIDAY
eSATURDAY
.ISOWEEK
: truncadatetime_expression
no limite da semana ISO 8601 anterior.ISOWEEK
s começam na segunda-feira. A primeiraISOWEEK
de cada ano ISO contém a primeira quinta-feira do ano do calendário gregoriano correspondente. Qualquerdate_expression
anterior a esta vai truncar na segunda-feira anterior.MONTH
QUARTER
YEAR
ISOYEAR
: truncadatetime_expression
no limite do ano de numeração da semana ISO 8601 anterior. O limite do ano ISO é a segunda-feira da primeira semana que contém a quinta-feira pertencente ao ano do calendário gregoriano correspondente.
Tipo de dados retornados
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-25T15:30:00 | 2008-12-25T00:00:00 |
+----------------------------+------------------------+
No exemplo a seguir, o DATETIME
original cai em um domingo. Como part
é WEEK(MONDAY)
, DATE_TRUNC
retorna o DATETIME
da segunda-feira anterior.
SELECT
datetime AS original,
DATETIME_TRUNC(datetime, WEEK(MONDAY)) AS truncated
FROM (SELECT DATETIME(TIMESTAMP "2017-11-05 00:00:00+00", "UTC") AS datetime);
+---------------------+---------------------+
| original | truncated |
+---------------------+---------------------+
| 2017-11-05T00:00:00 | 2017-10-30T00:00:00 |
+---------------------+---------------------+
No exemplo a seguir, o datetime_expression
original está no ano letivo gregoriano de 2015. No entanto, DATETIME_TRUNC
com a parte da data ISOYEAR
trunca a datetime_expression
no início do ano ISO, não no ano do calendário gregoriano. A primeira quinta-feira do ano-calendário de 2015 foi 2015-01-01, então o ano ISO 2015 começa na segunda-feira anterior, 2014-12-29.
Portanto, o limite do ano ISO anterior a 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-29T00: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
Exemplos
SELECT
FORMAT_DATETIME("%c", DATETIME "2008-12-25 15:30:00")
AS formatted;
+--------------------------+
| formatted |
+--------------------------+
| Thu Dec 25 15:30:00 2008 |
+--------------------------+
SELECT
FORMAT_DATETIME("%b-%d-%Y", DATETIME "2008-12-25 15:30:00")
AS formatted;
+-------------+
| formatted |
+-------------+
| Dec-25-2008 |
+-------------+
SELECT
FORMAT_DATETIME("%b %Y", DATETIME "2008-12-25 15:30:00")
AS formatted;
+-------------+
| formatted |
+-------------+
| Dec 2008 |
+-------------+
PARSE_DATETIME
PARSE_DATETIME(format_string, datetime_string)
Descrição
Converte uma representação de string de um datetime em um
objeto DATETIME
.
format_string
contém os elementos de formato
que definem como datetime_string
é formatado. Cada elemento em
datetime_string
precisa ter um elemento correspondente em format_string
. O
local de cada elemento em format_string
precisa corresponder à localização de
cada elemento em datetime_string
.
-- This works because elements on both sides match.
SELECT PARSE_DATETIME("%a %b %e %I:%M:%S %Y", "Thu Dec 25 07:30:00 2008")
-- This doesn't work because the year element is in different locations.
SELECT PARSE_DATETIME("%a %b %e %Y %I:%M:%S", "Thu Dec 25 07:30:00 2008")
-- This doesn't work because one of the year elements is missing.
SELECT PARSE_DATETIME("%a %b %e %I:%M:%S %Y", "Thu Dec 25 07:30:00 2008")
-- This works because %c can find all matching elements in datetime_string.
SELECT PARSE_DATETIME("%c", "Thu Dec 25 07:30:00 2008")
A string de formato é totalmente compatível com a maioria dos elementos de formato, exceto
%Q
, %a
, %A
,
%g
, %G
, %j
, %P
, %u
, %U
, %V
, %w
e %W
.
PARSE_DATETIME
analisa string
de acordo com as seguintes regras:
- 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
. - Nomes que não diferenciam maiúsculas e minúsculas. Os nomes, como
Monday
eFebruary
, 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
. Espaços em branco à esquerda e à direita na stringDATETIME
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. Por exemplo,
%F
e%Y
afetam o ano, de modo que o elemento anterior modifica o último. Consulte as descrições de%s
,%C
e%y
em elementos de formato compatíveis para DATETIME.
Tipo de dados retornados
DATETIME
Exemplos
Os exemplos a seguir analisam um literal STRING
como
DATETIME
.
SELECT PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '1998-10-18 13:45:55') AS datetime;
+---------------------+
| datetime |
+---------------------+
| 1998-10-18T13:45:55 |
+---------------------+
SELECT PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', '8/30/2018 2:23:38 PM') AS datetime
+---------------------+
| datetime |
+---------------------+
| 2018-08-30T14:23:38 |
+---------------------+
O exemplo a seguir analisa um literal STRING
que contém uma data em um formato de linguagem natural como DATETIME
.
SELECT PARSE_DATETIME('%A, %B %e, %Y','Wednesday, December 19, 2018')
AS datetime;
+---------------------+
| datetime |
+---------------------+
| 2018-12-19T00:00:00 |
+---------------------+
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 um 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. Cada ano ISO começa na segunda-feira antes da primeira quinta-feira do ano calendário gregoriano. Observe que %G e %Y podem produzir resultados diferentes próximos aos limites do ano gregoriano, em que o ano gregoriano e o ano ISO podem divergir. |
%g | O ano ISO 8601 sem o século como número decimal (00–99). Cada ano ISO começa na segunda-feira antes da primeira quinta-feira do ano calendário gregoriano. Observe que %g e %y podem produzir resultados diferentes próximos aos limites do ano gregoriano, em que o ano gregoriano e o ano ISO podem divergir. |
%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. |
%Q | O trimestre como um número decimal (de 1–4). |
%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, seja qual for o local 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 ISO 8601 do ano (segunda-feira como o primeiro dia da semana) como um número decimal (01–53). Se a semana que tem 1 de janeiro tiver quatro ou mais dias no ano novo, então será a semana 1. Caso contrário, será a semana 53 do ano anterior e a semana seguinte será a semana 1. |
%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 misturado com %C. Se %C não for especificado, os anos 00-68 são os 2000, enquanto os anos 69-99 são os 1900. |
%% | 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([timezone])
Descrição
Retorna a hora atual como um objeto TIME
.
Esta função aceita um parâmetro timezone
opcional.
Para informações sobre como especificar um fuso horário, consulte
Definições de fuso horário.
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
- Cria um objeto
TIME
usando valoresINT64
que representam a hora, o minuto e o segundo. - Cria um objeto
TIME
usando um objetoTIMESTAMP
. Aceita um parâmetro opcional para especificar um fuso horário. Se nenhum fuso horário for especificado, será usado o padrão UTC. - Cria um objeto
TIME
usando um objetoDATETIME
.
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 |
+----------+
EXTRACT
EXTRACT(part FROM time_expression)
Descrição
Retorna um valor que corresponde ao part
especificado de um
time_expression
fornecido.
Os valores part
permitidos são:
MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
Os valores retornados truncam períodos menores de tempo. Por exemplo, ao extrair segundos, EXTRACT
trunca os valores de milissegundos e microssegundos.
Tipo de dados retornados
INT64
Exemplo
No exemplo a seguir, EXTRACT
retorna um valor correspondente à parte de hora de HOUR
.
SELECT EXTRACT(HOUR FROM TIME "15:30:00") as hour;
+------------------+
| hour |
+------------------+
| 15 |
+------------------+
TIME_ADD
TIME_ADD(time_expression, INTERVAL int64_expression part)
Descrição
Adiciona unidades int64_expression
de part
ao objeto TIME
.
TIME_ADD
aceita os seguintes valores para 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 int64_expression part)
Descrição
Subtrai unidades int64_expression
de part
do objeto TIME
.
TIME_SUB
aceita os seguintes valores para 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_a, time_expression_b, part)
Descrição
Retorna o número de intervalos part
inteiros especificados entre dois
objetos TIME
(time_expression_a
- time_expression_b
). Se o primeiro
TIME
for anterior ao segundo, a saída será negativa. Lança um erro
se o cálculo estoura o tipo de resultado, como se a diferença em
microssegundos
entre os dois objetos TIME
estourasse um
valor INT64
.
TIME_DIFF
aceita os seguintes valores para 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)
<