Sintaxe, funções e operadores do SQL legado
Neste documento, detalhamos a sintaxe, as funções e os operadores de consultas do SQL legado. A sintaxe de consulta de preferência para o BigQuery é o GoogleSQL. Para mais informações sobre o GoogleSQL, consulte Sintaxe de consulta do GoogleSQL.
Sintaxe das consultas
Observação: não há diferenciação entre letras maiúsculas e minúsculas nas palavras-chave. Neste documento, palavras-chave como
SELECT
estão em letras maiúsculas para fins ilustrativos.
Cláusula SELECT
A cláusula SELECT
especifica uma lista de expressões a serem calculadas. As expressões na cláusula
SELECT
podem conter nomes de campos, literais e
chamadas de função, incluindo funções de agregação
e de janela, e também combinações desses três itens. A lista de expressões é separada por vírgulas.
Cada expressão pode receber um alias por meio da adição de um espaço seguido de um identificador após a expressão. A palavra-chave opcional AS
pode ser adicionada entre a expressão e o alias para melhorar a legibilidade. Alias definidos em uma cláusula SELECT
podem ser referenciados nas cláusulas
GROUP BY
, HAVING
e ORDER BY
da consulta, mas
não nas FROM
, WHERE
ou OMIT RECORD IF
nem por
outras expressões na mesma cláusula SELECT
.
Observações:
-
Se você usar uma função agregada na cláusula
SELECT
, precisará usá-la em todas as expressões ou sua consulta precisa ter uma cláusulaGROUP BY
que inclua todos os campos não agregados na sua cláusulaSELECT
como chaves de agrupamento. Por exemplo:#legacySQL SELECT word, corpus, COUNT(word) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word, corpus; /* Succeeds because all non-aggregated fields are group keys. */
#legacySQL SELECT word, corpus, COUNT(word) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word; /* Fails because corpus is not aggregated nor is it a group key. */
-
Utilize colchetes para escapar palavras reservadas e usá-las como nome de campo e alias. Por exemplo, se você tiver uma coluna chamada
"partition", que é uma palavra reservada na sintaxe do BigQuery, as consultas que fizerem referência
a esse campo apresentarão falhas com mensagens de erro obscuras, a menos que você as escape com colchetes:
SELECT [partition] FROM ...
Exemplo
Neste exemplo, definimos aliases na cláusula SELECT
e, em seguida, referenciamos a um deles
na cláusula ORDER BY
. Não é possível mencionar a coluna word usando
word_alias na cláusula WHERE
. A referência tem que ser feita pelo nome. O alias
len também não fica visível na cláusula WHERE
. Ele será visível para uma
cláusula HAVING
.
#legacySQL SELECT word AS word_alias, LENGTH(word) AS len FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS 'th' ORDER BY len;
Modificador WITHIN para funções de agregação
aggregate_function WITHIN RECORD [ [ AS ] alias ]
A palavra-chave WITHIN
faz com que a função de agregação reúna valores repetidos dentro de cada registro. Para cada registro de entrada, será produzida exatamente uma saída agregada. Esse
tipo de agregação é denominado agregação em escopo. Como a agregação
com escopo produz uma saída para cada registro, as expressões não agregadas podem ser selecionadas ao lado de expressões
com escopo agregado sem o uso de uma cláusula GROUP BY
.
É mais comum você utilizar o escopo RECORD
ao usar agregação em escopo. Se você tem um esquema muito complexo aninhado e repetido, pode precisar executar agregações dentro de escopos de sub-registro. Isso pode ser feito pela substituição da palavra-chave RECORD
na sintaxe
acima pelo nome do node no esquema em que você quiser que a agregação seja realizada.
Para mais informações sobre esse comportamento avançado, consulte
Como lidar com dados.
Exemplo
Este exemplo executa uma agregação COUNT
em escopo e, em seguida, filtra e classifica os registros pelo valor agregado.
#legacySQL SELECT repository.url, COUNT(payload.pages.page_name) WITHIN RECORD AS page_count FROM [bigquery-public-data:samples.github_nested] HAVING page_count > 80 ORDER BY page_count DESC;
Cláusula FROM
FROM [project_name:]datasetId.tableId [ [ AS ] alias ] | (subquery) [ [ AS ] alias ] |JOIN
clause |FLATTEN
clause | table wildcard function
A cláusula FROM
especifica os dados de origem a serem consultados. As consultas do BigQuery podem ser executadas diretamente sobre tabelas, subconsultas, tabelas conjuntas e tabelas modificadas por operadores especiais descritos abaixo. As combinações destas origens de dados podem ser consultadas usando
a vírgula, que é o operador UNION ALL
no
BigQuery.
Tabelas de referência
Ao fazer referência a uma tabela, datasetId e datasetId precisam ser especificados. datasetId é opcional. Se o project_name não for especificado, o BigQuery usará o projeto atual como padrão. Se o nome do projeto incluir um traço, será preciso colocar a referência da tabela entre colchetes.
Exemplo
[my-dashed-project:dataset1.tableName]
As tabelas podem receber um alias por meio da adição de um espaço seguido de um identificador após o nome da tabela. É possível adicionar a palavra-chave opcional AS
entre o tableId e o alias para melhorar a leitura.
Ao fazer referência a colunas de uma tabela, você pode usar o nome de coluna simples ou prefixar o nome da coluna com o alias, se houver algum especificado, ou com o datasetId e datasetId, desde que não haja nenhum datasetId especificado. O project_name não pode ser incluído no prefixo da coluna porque o caractere de dois pontos não é permitido em nomes de campo.
Exemplos
Este exemplo faz referência a uma coluna sem prefixo de tabela.
#legacySQL SELECT word FROM [bigquery-public-data:samples.shakespeare];
Este exemplo tem datasetId e datasetId como prefixo do nome da coluna. Observe que o project_name não pode ser incluído aqui. Esse método só funcionará se o conjunto de dados estiver no projeto padrão atual.
#legacySQL SELECT samples.shakespeare.word FROM samples.shakespeare;
Este exemplo tem um alias de tabela como prefixo do nome da coluna.
#legacySQL SELECT t.word FROM [bigquery-public-data:samples.shakespeare] AS t;
Tabelas particionadas por intervalo de números inteiros
No entanto, o SQL legado é compatível com o uso de decoradores de tabela para lidar com uma partição específica em uma tabela particionada de intervalo de números inteiros. A chave para lidar com uma variação de partição é o início da variação.
O exemplo a seguir investiga a partição de variação que começa com 30.
#legacySQL SELECT * FROM dataset.table$30;
Não é possível usar SQL legado para consultar uma tabela particionada por intervalo de números inteiros inteira. Em vez disso, a consulta retorna um erro como este:
Querying tables partitioned on a field is not supported in Legacy SQL
Uso de subconsultas
Uma subconsulta é uma instrução SELECT
aninhada entre parênteses. As
expressões calculadas na cláusula SELECT
da subconsulta estão disponíveis para consulta externa,
assim como as colunas de uma tabela.
As subconsultas podem ser usadas para calcular agregações e outras expressões. A gama completa de operadores SQL está disponível na subconsulta. Isso significa que uma subconsulta pode conter outras, as subconsultas podem realizar associações e agrupar agregações, etc.
Vírgula como UNION ALL
Ao contrário do GoogleSQL, o SQL legado usa a vírgula como um operador UNION ALL
.
em vez de um operador CROSS JOIN
. Esse é um comportamento legado que evoluiu porque, historicamente,
o BigQuery não era compatível com CROSS JOIN
e usuários do BigQuery precisavam escrever
consultas UNION ALL
. No GoogleSQL, as consultas que executam uniões são particularmente
detalhadas. Usar a vírgula como operador de união permite que essas consultas sejam escritas de maneira muito mais eficiente. Por exemplo, esta consulta pode ser usada para executar uma única consulta em registros de vários dias.
#legacySQL SELECT FORMAT_UTC_USEC(event.timestamp_in_usec) AS time, request_url FROM [applogs.events_20120501], [applogs.events_20120502], [applogs.events_20120503] WHERE event.username = 'root' AND NOT event.source_ip.is_internal;
Consultas que unem um grande número de tabelas normalmente são executadas de maneira mais lenta que aquelas que processam a mesma quantidade de dados a partir de uma única tabela. A diferença de desempenho pode ser de até 50 ms por tabela adicional. Uma única consulta pode unir no máximo 1.000 tabelas.
Funções de caractere curinga de tabela
O termo função de caractere curinga de tabela refere-se a um tipo especial de função exclusiva para o BigQuery.
Essas funções são usadas na cláusula FROM
para corresponder a uma coleção de nomes de tabela
usando um dos vários tipos de filtros. Por exemplo, a função TABLE_DATE_RANGE
pode ser utilizada para consultar apenas um conjunto específico de tabelas diárias. Para mais informações sobre essas funções,
consulte Funções de caractere curinga de tabela.
Operador FLATTEN
(FLATTEN([project_name:]datasetId.tableId, field_to_be_flattened)) (FLATTEN((subquery), field_to_be_flattened))
Ao contrário dos sistemas típicos de processamento de SQL, o BigQuery foi projetado para lidar com dados repetidos. Por conta disso, os usuários do BigQuery às vezes precisam escrever consultas que manipulem a estrutura de registros repetidos. Uma maneira de fazer isso é usando o operador FLATTEN
.
FLATTEN
converte um nó no esquema de repetido para opcional. Dado um registro
com um ou mais valores para um campo repetido, FLATTEN
criará vários registros,
um para cada valor no campo repetido. Todos os outros campos selecionados a partir do registro são duplicados em cada novo registro de saída. FLATTEN
pode ser aplicado repetidamente para remover
vários níveis de repetição.
Para mais informações e exemplos, consulte Como lidar com dados.
Operador JOIN
O BigQuery é compatível com vários operadores JOIN
em cada cláusula FROM
.
As operações JOIN
subsequentes usam os resultados da operação JOIN
anterior como a entrada JOIN
esquerda. Os campos de qualquer entrada JOIN
anterior
podem ser usados como chaves nas cláusulas ON
dos operadores JOIN
subsequentes.
Tipos de JOIN
O BigQuery é compatível com operações INNER
, [FULL|RIGHT|LEFT] OUTER
e CROSS JOIN
. Se nada for especificado, o padrão será INNER
.
Operações CROSS JOIN
não permitem cláusulas ON
. CROSS JOIN
pode retornar uma grande quantidade de dados e pode resultar em uma consulta lenta e ineficiente ou em uma consulta
que excede o máximo permitido de recursos por consulta. Essas consultas apresentarão falha com um erro. Quando
possível, prefira consultas que não usam CROSS JOIN
. Por exemplo, CROSS JOIN
é frequentemente usado em locais em que funções de janela seriam
mais eficientes.
Modificador EACH
O modificador EACH
é uma dica que indica ao BigQuery que ele execute JOIN
usando várias partições. Isso é particularmente útil quando você sabe que ambos os lados do
JOIN
são grandes. O modificador EACH
não pode ser usado em
cláusulas CROSS JOIN
.
EACH
costumava ser incentivado em muitas ocasiões, mas não é mais o caso. Quando
possível, use JOIN
sem o modificador EACH
para melhor desempenho.
Use JOIN EACH
quando sua consulta falhar com uma mensagem de erro de recursos excedidos.
Semi-join e Anti-join
Além de ser compatível com JOIN
na cláusula FROM
, o BigQuery também é compatível com dois tipos de junções na cláusula WHERE
: semi-join e anti-semi-join. Uma
semi-join é especificada usando a palavra-chave IN
com uma subconsulta. Já anti-join, usando as
palavras-chave NOT IN
.
Exemplos
A consulta a seguir usa uma semi-join para encontrar ngrams em que a primeira palavra no ngram é também a segunda palavra em outro que tem "AND" como a terceira.
#legacySQL SELECT ngram FROM [bigquery-public-data:samples.trigrams] WHERE first IN (SELECT second FROM [bigquery-public-data:samples.trigrams] WHERE third = "AND") LIMIT 10;
A consulta a seguir usa uma semi-join para retornar o número de mulheres com mais de 50 anos que deram à luz nos dez estados com os maiores índices de nascimento.
#legacySQL SELECT mother_age, COUNT(mother_age) total FROM [bigquery-public-data:samples.natality] WHERE state IN (SELECT state FROM (SELECT state, COUNT(state) total FROM [bigquery-public-data:samples.natality] GROUP BY state ORDER BY total DESC LIMIT 10)) AND mother_age > 50 GROUP BY mother_age ORDER BY mother_age DESC
Para ver os números dos outros 40 estados, use uma anti-join. A consulta a seguir é
quase idêntica ao exemplo anterior, mas usa NOT IN
em vez de IN
para retornar o número de mulheres com mais de 50 anos que deram à luz nos 40 estados com o menor número de nascimentos.
#legacySQL SELECT mother_age, COUNT(mother_age) total FROM [bigquery-public-data:samples.natality] WHERE state NOT IN (SELECT state FROM (SELECT state, COUNT(state) total FROM [bigquery-public-data:samples.natality] GROUP BY state ORDER BY total DESC LIMIT 10)) AND mother_age > 50 GROUP BY mother_age ORDER BY mother_age DESC
Observações:
- O BigQuery não é compatível com semi-joins ou anti-semi-joins correlacionadas. A subconsulta não pode fazer referência a nenhum campo da consulta externa.
- A subconsulta usada em uma semi-join ou anti-semi-join precisa selecionar exatamente um campo.
-
Os tipos do campo selecionado e o campo que está sendo usado da consulta externa na cláusula
WHERE
precisam corresponder exatamente. O BigQuery não fará nenhum tipo de coerção para semi-joins ou anti-semi-joins.
Cláusula WHERE
A cláusula WHERE
, às vezes chamada de predicado, filtra registros produzidos pela cláusula FROM
usando uma expressão booleana. Múltiplas condições podem ser juntadas por cláusulas booleanas
AND
e OR
, opcionalmente delimitadas por um parênteses — () —
que as agrupa. Os campos listados em uma cláusula WHERE
não precisam ser selecionados na
cláusula SELECT
correspondente, e a expressão da WHERE
não pode
referenciar expressões calculadas na SELECT
da consulta à qual a
WHERE
pertence.
Observação: não é possível usar as funções de agregação na cláusula WHERE
. Use uma cláusula
HAVING
e uma consulta externa se for preciso filtrar a
saída de uma função de agregação.
Exemplo
O exemplo a seguir usa uma disjunção de expressões booleanas na cláusula WHERE
, as duas expressões unidas por um operador OR
. Um registro de entrada passará
pelo filtro WHERE
se qualquer uma das expressões retornar true
.
#legacySQL SELECT word FROM [bigquery-public-data:samples.shakespeare] WHERE (word CONTAINS 'prais' AND word CONTAINS 'ing') OR (word CONTAINS 'laugh' AND word CONTAINS 'ed');
Cláusula OMIT RECORD IF
A cláusula OMIT RECORD IF
é uma construção exclusiva do BigQuery. É particularmente útil para lidar com esquemas repetidos aninhados. É semelhante a uma cláusula WHERE
, mas diferente em dois aspectos importantes. Primeiro, ele usa uma condição de exclusão,
que significa que os registros são omitidos se a expressão retornar true
, mas mantidos se a
expressão retornar false
ou null
. Segundo, a cláusula OMIT RECORD IF
pode usar (e geralmente usa) funções de agregação com escopo na condição.
Além de filtrar os registros completos, OMIT...IF
pode especificar um escopo
mais restrito para filtrar apenas partes de um registro. Isso é feito usando o nome de um nó que não seja uma folha no seu
esquema, em vez de RECORD
na sua cláusula OMIT...IF
. Essa funcionalidade raramente é utilizada pelos usuários do BigQuery. Você encontra mais informações sobre esse comportamento avançado
em um link na documentação de WITHIN
acima.
Se você usa OMIT...IF
para excluir uma parte de um registro em um campo de repetição e a consulta
também seleciona outros campos de repetição independentes, o BigQuery omite
uma parte dos outros registros repetidos na consulta. Se você vir o erro
Cannot perform OMIT IF on repeated scope <scope> with independently repeating pass through field <field>,
recomendamos mudar para o GoogleSQL. Para mais informações sobre a migração das intruções
OMIT...IF
para o GoogleSQL, consulte
Migração
para o GoogleSQL.
Exemplo
Referindo-se ao exemplo usado para o modificador WITHIN
, OMIT RECORD IF
pode ser usado para realizar a mesma tarefa que WITHIN
e HAVING
foram
usadas para fazer nesse exemplo.
#legacySQL SELECT repository.url FROM [bigquery-public-data:samples.github_nested] OMIT RECORD IF COUNT(payload.pages.page_name) <= 80;
Cláusula GROUP BY
A cláusula GROUP BY
permite agrupar linhas que tenham os mesmos valores para um determinado campo ou conjunto de campos, para que você possa calcular agregações de campos relacionados. O agrupamento ocorre
após a filtragem realizada na cláusula WHERE
, mas antes que as expressões na
cláusula SELECT
sejam calculadas. Os resultados da expressão não podem ser usados como chaves de grupo
na cláusula GROUP BY
.
Exemplo
Esta consulta localiza as dez primeiras palavras mais comuns no conjunto de dados de amostra de trigramas.
Além de demonstrar o uso da cláusula GROUP BY
, ela demonstra como
índices posicionais podem ser usados em vez de nomes de campos nas
cláusulas GROUP BY
e ORDER BY
.
#legacySQL SELECT first, COUNT(ngram) FROM [bigquery-public-data:samples.trigrams] GROUP BY 1 ORDER BY 2 DESC LIMIT 10;
A agregação realizada usando uma cláusula GROUP BY
é chamada agregação
agrupada
. Ao contrário da agregação em escopo, a agregação agrupada é
comum na maioria dos sistemas de processamento SQL.
O modificador EACH
O modificador EACH
é uma dica que indica ao BigQuery que ele execute GROUP BY
usando várias partições. Ele é particularmente útil quando você sabe que o conjunto de dados contém um grande número de valores distintos para as chaves de grupo.
EACH
costumava ser incentivado em muitas ocasiões, mas não é mais o caso.
Usar GROUP BY
sem o modificador EACH
normalmente proporciona melhor desempenho.
Use GROUP EACH BY
quando sua consulta falhar com uma mensagem de erro de recursos excedidos.
A função ROLLUP
Quando a função ROLLUP
é usada, linhas extras que
representam agregações acumuladas são adicionadas pelo BigQuery ao final da consulta. Todos os campos listados após ROLLUP
precisam ser
incluídos em um único conjunto de parênteses. Nas linhas adicionadas por causa da função ROLLUP
,
NULL
indica as colunas para as quais a agregação é acumulada.
Exemplo
Esta consulta gera contagens por ano de nascimentos masculinos e femininos a partir do conjunto de dados de natalidade da amostra.
#legacySQL SELECT year, is_male, COUNT(1) as count FROM [bigquery-public-data:samples.natality] WHERE year >= 2000 AND year <= 2002 GROUP BY ROLLUP(year, is_male) ORDER BY year, is_male;
Estes são os resultados da consulta. Observe que há linhas em que uma ou ambas as chaves do grupo
são NULL
. Essas linhas são as linhas acumuladas.
+------+---------+----------+ | year | is_male | count | +------+---------+----------+ | NULL | NULL | 12122730 | | 2000 | NULL | 4063823 | | 2000 | false | 1984255 | | 2000 | true | 2079568 | | 2001 | NULL | 4031531 | | 2001 | false | 1970770 | | 2001 | true | 2060761 | | 2002 | NULL | 4027376 | | 2002 | false | 1966519 | | 2002 | true | 2060857 | +------+---------+----------+
Ao usar a função ROLLUP
, é possível usar GROUPING
para distinguir entre linhas que foram adicionadas por causa de ROLLUP
e linhas
que realmente têm um valor NULL
para a chave de grupo.
Exemplo
Esta consulta adiciona a função GROUPING
ao exemplo anterior para identificar melhor as linhas adicionadas por conta de ROLLUP
.
#legacySQL SELECT year, GROUPING(year) as rollup_year, is_male, GROUPING(is_male) as rollup_gender, COUNT(1) as count FROM [bigquery-public-data:samples.natality] WHERE year >= 2000 AND year <= 2002 GROUP BY ROLLUP(year, is_male) ORDER BY year, is_male;
Estes são o resultado que a nova consulta retorna.
+------+-------------+---------+---------------+----------+ | year | rollup_year | is_male | rollup_gender | count | +------+-------------+---------+---------------+----------+ | NULL | 1 | NULL | 1 | 12122730 | | 2000 | 0 | NULL | 1 | 4063823 | | 2000 | 0 | false | 0 | 1984255 | | 2000 | 0 | true | 0 | 2079568 | | 2001 | 0 | NULL | 1 | 4031531 | | 2001 | 0 | false | 0 | 1970770 | | 2001 | 0 | true | 0 | 2060761 | | 2002 | 0 | NULL | 1 | 4027376 | | 2002 | 0 | false | 0 | 1966519 | | 2002 | 0 | true | 0 | 2060857 | +------+-------------+---------+---------------+----------+
Observações:
-
Os campos não agregados na cláusula
SELECT
precisam ser listados na cláusulaGROUP BY
.#legacySQL SELECT word, corpus, COUNT(word) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word, corpus; /* Succeeds because all non-aggregated fields are group keys. */
#legacySQL SELECT word, corpus, COUNT(word) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word; /* Fails because corpus is not aggregated nor is it a group key. */
-
Expressões calculadas na cláusula
SELECT
não podem ser usadas na cláusulaGROUP BY
correspondente.#legacySQL SELECT word, corpus, COUNT(word) word_count FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word, corpus, word_count; /* Fails because word_count is not visible to this
GROUP BY
clause. */ - Não há compatibilidade com o agrupamento por valores flutuantes e duplos, porque a função de igualdade desses tipos não é bem definida.
-
Como o sistema é interativo, consultas que produzem um grande número de grupos podem apresentar falha. O
uso da função
TOP
em vez deGROUP BY
pode resolver alguns problemas de escalonamento.
Cláusula HAVING
A cláusula HAVING
se comporta exatamente como a cláusula WHERE
,
exceto que ela é avaliada após a cláusula SELECT
para que os resultados de todas
as expressões calculadas fiquem visíveis para a cláusula HAVING
. A cláusula HAVING só pode
se referir às saídas da SELECT
correspondente.
Exemplo
Esta consulta calcula as primeiras palavras mais comuns no conjunto de dados de exemplo ngram que contêm a letra "a" e ocorrem no máximo 10.000 vezes.
#legacySQL SELECT first, COUNT(ngram) ngram_count FROM [bigquery-public-data:samples.trigrams] GROUP BY 1 HAVING first contains "a" AND ngram_count < 10000 ORDER BY 2 DESC LIMIT 10;
Cláusula ORDER BY
A cláusula ORDER BY
classifica os resultados de uma consulta em ordem crescente
ou decrescente usando um ou mais campos-chave. Para classificar por múltiplos campos ou aliases, insira-os como uma lista separada por vírgulas. Os resultados são classificados nos campos na ordem em que estão listados.
Use os valores DESC
(decrescente) ou ASC
(crescente) para especificar a direção de classificação.
O padrão é ASC
. Uma direção de classificação diferente pode ser especificada para cada chave de classificação.
A cláusula ORDER BY
é avaliada após a cláusula SELECT
para que possa
referenciar a saída de qualquer expressão calculada em SELECT
. Se um campo receber
um alias na cláusula SELECT
, o alias precisará ser usado na cláusula
ORDER BY
.
Cláusula LIMIT
A cláusula LIMIT
limita o número de linhas no conjunto de resultados retornado. Como as consultas do BigQuery
operam regularmente em um número muito grande de linhas, LIMIT
é uma boa
maneira de evitar consultas de longa execução por meio do processamento de apenas um subconjunto das linhas.
Observações:
-
A cláusula
LIMIT
interromperá o processamento e retornará os resultados quando atender aos requisitos. Isso pode reduzir o tempo de processamento de algumas consultas, mas quando você especifica funções de agregação, como cláusulas COUNT ouORDER BY
, todo o conjunto de resultados ainda precisa ser processado antes de retornar resultados. A cláusulaLIMIT
é a última a ser avaliada. -
Uma consulta com uma cláusula
LIMIT
pode ainda ser não determinística se não houver nenhum operador nela que garanta a ordenação do conjunto de resultados de saída. Isso ocorre porque o BigQuery é executado com um grande número de trabalhos paralelos. A ordem na qual os jobs em paralelo retornam não é garantida. - A cláusula
LIMIT
não pode conter nenhuma função. Ela só precisa de uma constante numérica.
Gramática das consultas
Os detalhes de cláusulas individuais das instruções SELECT
do BigQuery estão descritos
acima. Nesta parte, apresentamos a gramática completa de instruções SELECT
em um formato compacto e com links para as seções individuais.
query: SELECT { * | field_path.* | expression } [ [ AS ] alias ] [ , ... ] [ FROM from_body [ WHERE bool_expression ] [ OMIT RECORD IF bool_expression] [ GROUP [ EACH ] BY [ ROLLUP ] { field_name_or_alias } [ , ... ] ] [ HAVING bool_expression ] [ ORDER BY field_name_or_alias [ { DESC | ASC } ] [, ... ] ] [ LIMIT n ] ]; from_body: { from_item [, ...] | # Warning: Comma means UNION ALL here from_item [ join_type ] JOIN [ EACH ] from_item [ ON join_predicate ] | (FLATTEN({ table_name | (query) }, field_name_or_alias)) | table_wildcard_function } from_item: { table_name | (query) } [ [ AS ] alias ] join_type: { INNER | [ FULL ] [ OUTER ] | RIGHT [ OUTER ] | LEFT [ OUTER ] | CROSS } join_predicate: field_from_one_side_of_the_join = field_from_the_other_side_of_the_join [ AND ...] expression: { literal_value | field_name_or_alias | function_call } bool_expression: { expression_which_results_in_a_boolean_value | bool_expression AND bool_expression | bool_expression OR bool_expression | NOT bool_expression }
Notação:
- Colchetes "[ ]" indicam cláusulas opcionais.
- Chaves "{ }" delimitam um conjunto de opções.
- Barra vertical "|" indica um OR lógico.
- Uma vírgula ou palavra-chave seguida por reticências entre colchetes "[, ... ]" indica que o item anterior pode se repetir em uma lista com o separador especificado.
- Parênteses "( )" indicam parênteses literais.
Funções e operadores compatíveis
A maioria das cláusulas de instrução SELECT
é compatível com funções. Os campos
referenciados em uma função não precisam ser listados em nenhuma cláusula SELECT
. Portanto, a consulta a seguir é válida, mesmo que o campo
clicks
não seja exibido diretamente:
#legacySQL SELECT country, SUM(clicks) FROM table GROUP BY country;
Funções de agregação | |
---|---|
AVG() |
Retorna a média dos valores para um grupo de linhas... |
BIT_AND() |
Retorna o resultado de uma operação AND bit a bit... |
BIT_OR() |
Retorna o resultado de uma operação OR bit a bit... |
BIT_XOR() |
Retorna o resultado de uma operação XOR bit a bit... |
CORR() |
Retorna o coeficiente de correlação de Pearson de um conjunto de pares de números. |
COUNT() |
Retorna o número total de valores... |
COUNT([DISTINCT]) |
Retorna o número total de valores não NULL... |
COVAR_POP() |
Calcula a covariância de população dos valores... |
COVAR_SAMP() |
Calcula a covariância de exemplo dos valores... |
EXACT_COUNT_DISTINCT() |
Retorna o número exato de valores não NULL distintos para o campo especificado. |
FIRST() |
Retorna o primeiro valor sequencial no escopo da função. |
GROUP_CONCAT() |
Concatena strings múltiplas em uma única string... |
GROUP_CONCAT_UNQUOTED() |
Concatena strings múltiplas em uma única string... não adicionará aspas duplas... |
LAST() |
Retorna o último valor sequencial... |
MAX() |
Retorna o valor máximo... |
MIN() |
Retorna o valor mínimo... |
NEST() |
Agrupa todos os valores no escopo de agregação atual em um campo repetido. |
NTH() |
Retorna o enésimo valor sequencial... |
QUANTILES() |
Calcula aproximado mínimo, máximo e quantis... |
STDDEV() |
Retorna o desvio padrão... |
STDDEV_POP() |
Calcula o desvio padrão da população... |
STDDEV_SAMP() |
Calcula o desvio padrão da amostra... |
SUM() |
Retorna a soma total dos valores... |
TOP() ... COUNT(*) |
Retorna os principais registros max_records por frequência. |
UNIQUE() |
Retorna o conjunto de valores não NULL exclusivos... |
VARIANCE() |
Calcula a variância dos valores... |
VAR_POP() |
Calcula a variância de população dos valores... |
VAR_SAMP() |
Calcula a variância de amostra dos valores... |
Operadores aritméticos | |
---|---|
+ |
Adição |
- |
Subtração |
* |
Multiplicação |
/ |
Divisão |
% |
Modulo |
Funções bit a bit | |
---|---|
& |
AND bit a bit |
| |
OR bit a bit |
^ |
XOR bit a bit |
<< |
Desloca para a esquerda bit a bit |
>> |
Desloca para a direita bit a bit |
~ |
NOT bit a bit |
BIT_COUNT() |
Retorna o número de bits... |
Funções de conversão | |
---|---|
BOOLEAN() |
Converte em booleano. |
BYTES() |
Converte em bytes. |
CAST(expr AS type) |
Converte expr em uma variável do tipo type . |
FLOAT() |
Converte em duplo. |
HEX_STRING() |
Converte em string hexadecimal. |
INTEGER() |
Converte em número inteiro. |
STRING() |
Converte em string. |
Funções de comparação | |
---|---|
expr1 = expr2 |
Retorna true se as expressões forem iguais. |
expr1 != expr2 expr1 <> expr2
|
Retorna true se as expressões não forem iguais. |
expr1 > expr2 |
Retorna true se expr1 for maior que expr2 . |
expr1 < expr2 |
Retorna true se expr1 for menor que expr2 . |
expr1 >= expr2 |
Retorna true se expr1 for maior ou igual a expr2 . |
expr1 <= expr2 |
Retorna true se expr1 for menor ou igual a expr2 . |
expr1 BETWEEN expr2 AND expr3 |
Retorna true se o valor de expr1
for entre expr2 e expr3 , inclusive. |
expr IS NULL |
Retorna true se expr for NULL. |
expr IN() |
Retorna true se expr corresponder a
expr1 , expr2 ou qualquer valor entre parênteses. |
COALESCE() |
Retorna o primeiro argumento que não é NULL. |
GREATEST() |
Retorna o maior parâmetro numeric_expr . |
IFNULL() |
Se o argumento não for NULL, retornará o argumento. |
IS_INF() |
Retorna true se o infinito for positivo ou negativo. |
IS_NAN() |
Retorna true se o argumento for NaN . |
IS_EXPLICITLY_DEFINED() |
obsoleto: use expr IS NOT NULL . |
LEAST() |
Retorna o menor parâmetro numeric_expr do argumento. |
NVL() |
Se expr não for nulo, retornará expr . Caso contrário, retornará null_default . |
Funções de data e hora | |
---|---|
CURRENT_DATE() |
Retorna a data atual no formato %Y-%m-%d . |
CURRENT_TIME() |
Retorna o horário atual do servidor no formato %H:%M:%S . |
CURRENT_TIMESTAMP() |
Retorna o horário atual do servidor no formato %Y-%m-%d %H:%M:%S . |
DATE() |
Retorna a data no formato %Y-%m-%d . |
DATE_ADD() |
Adiciona o intervalo especificado a um tipo de dados TIMESTAMP. |
DATEDIFF() |
Retorna o número de dias entre dois tipos de dados TIMESTAMP. |
DAY() |
Retorna o dia do mês como um número inteiro entre 1 e 31. |
DAYOFWEEK() |
Retorna o dia da semana como um número inteiro entre 1 (domingo) e 7 (sábado). |
DAYOFYEAR() |
Retorna o dia do ano como um número inteiro entre 1 e 366. |
FORMAT_UTC_USEC() |
Retorna um carimbo de data/hora UNIX no formato YYYY-MM-DD HH:MM:SS.uuuuuu . |
HOUR() |
Retorna a hora de um TIMESTAMP como um número inteiro entre 0 e 23. |
MINUTE() |
Retorna os minutos de um TIMESTAMP como um número inteiro entre 0 e 59. |
MONTH() |
Retorna o mês de um TIMESTAMP como um número inteiro entre 1 e 12. |
MSEC_TO_TIMESTAMP() |
Converte um carimbo de data/hora UNIX em milissegundos em um TIMESTAMP. |
NOW() |
Retorna o carimbo de data/hora UNIX atual em microssegundos. |
PARSE_UTC_USEC() |
Converte uma string de data em um carimbo de data/hora UNIX em microssegundos. |
QUARTER() |
Retorna o trimestre do ano de um TIMESTAMP como um número inteiro entre 1 e 4. |
SEC_TO_TIMESTAMP() |
Converte um carimbo de data/hora UNIX em segundos em um TIMESTAMP. |
SECOND() |
Retorna os segundos de um TIMESTAMP como um número inteiro entre 0 e 59. |
STRFTIME_UTC_USEC() |
Retorna uma string de data no formato date_format_str. |
TIME() |
Retorna um TIMESTAMP no formato %H:%M:%S . |
TIMESTAMP() |
Converte uma string de data em um TIMESTAMP. |
TIMESTAMP_TO_MSEC() |
Converte um TIMESTAMP em um carimbo de data/hora UNIX em milissegundos. |
TIMESTAMP_TO_SEC() |
Converte um TIMESTAMP em um carimbo de data/hora UNIX em segundos. |
TIMESTAMP_TO_USEC() |
Converte um TIMESTAMP em um carimbo de data/hora UNIX em microssegundos. |
USEC_TO_TIMESTAMP() |
Converte um carimbo de data/hora UNIX em microssegundos em um TIMESTAMP. |
UTC_USEC_TO_DAY() |
Desloca um carimbo de data/hora UNIX em microssegundos para o início do dia em que ele ocorre. |
UTC_USEC_TO_HOUR() |
Desloca um carimbo de data/hora UNIX em microssegundos para o início da hora em que ele ocorre. |
UTC_USEC_TO_MONTH() |
Desloca um carimbo de data/hora UNIX em microssegundos para o início do mês em que ele ocorre. |
UTC_USEC_TO_WEEK() |
Retorna um carimbo de data/hora UNIX em microssegundos que representa um dia na semana. |
UTC_USEC_TO_YEAR() |
Retorna um carimbo de data/hora UNIX em microssegundos que representa o ano. |
WEEK() |
Retorna a semana de um TIMESTAMP como um número inteiro entre 1 e 53. |
YEAR() |
Retorna o ano de um TIMESTAMP. |
Funções IP | |
---|---|
FORMAT_IP() |
Converte 32 bits menos significativos de integer_value em uma string de endereço IPv4 legível. |
PARSE_IP() |
Converte uma string representando o endereço IPv4 em um valor inteiro sem sinal. |
FORMAT_PACKED_IP() |
Retorna um endereço IP legível no formato
10.1.5.23 ou 2620:0:1009:1:216:36ff:feef:3f . |
PARSE_PACKED_IP() |
Retorna um endereço IP em BYTES. |
Funções JSON | |
---|---|
JSON_EXTRACT() |
Seleciona um valor de acordo com a expressão JSONPath e retorna uma string JSON. |
JSON_EXTRACT_SCALAR() |
Seleciona um valor de acordo com a expressão JSONPath e retorna um escalar JSON. |
Operadores lógicos | |
---|---|
expr AND expr |
Retorna true se ambas as expressões forem verdadeiras. |
expr OR expr |
Retorna true se uma ou as duas expressões forem verdadeiras. |
NOT expr |
Retorna true se a expressão for falsa. |
Funções matemáticas | |
---|---|
ABS() |
Retorna o valor absoluto do argumento. |
ACOS() |
Retorna o arco cosseno do argumento. |
ACOSH() |
Retorna o arco cosseno hiperbólico do argumento. |
ASIN() |
Retorna o arco seno do argumento. |
ASINH() |
Retorna o arco seno hiperbólico do argumento. |
ATAN() |
Retorna o arco tangente do argumento. |
ATANH() |
Retorna o arco tangente hiperbólico do argumento. |
ATAN2() |
Retorna o arco tangente dos dois argumentos. |
CEIL() |
Arredonda o argumento para cima até o número inteiro mais próximo e retorna o valor arredondado. |
COS() |
Retorna o cosseno do argumento. |
COSH() |
Retorna o cosseno hiperbólico do argumento. |
DEGREES() |
Converte de radianos em graus. |
EXP() |
Retorna e à potência do argumento. |
FLOOR() |
Arredonda o argumento para baixo para o número inteiro mais próximo. |
LN() LOG()
|
Retorna o logaritmo natural do argumento. |
LOG2() |
Retorna o logaritmo de Base 2 do argumento. |
LOG10() |
Retorna o logaritmo de Base 10 do argumento. |
PI() |
Retorna a constante π. |
POW() |
Retorna o primeiro argumento à potência do segundo argumento. |
RADIANS() |
Converte de graus em radianos. |
RAND() |
Retorna um valor flutuante aleatório no intervalo 0,0 <= valor < 1,0. |
ROUND() |
Arredonda o argumento para cima ou para baixo para o número inteiro mais próximo. |
SIN() |
Retorna o seno do argumento. |
SINH() |
Retorna o seno hiperbólico do argumento. |
SQRT() |
Retorna a raiz quadrada da expressão. |
TAN() |
Retorna a tangente do argumento. |
TANH() |
Retorna a tangente hiperbólica do argumento. |
Funções de expressão regular | |
---|---|
REGEXP_MATCH() |
Retornará true se o argumento corresponder à expressão regular. |
REGEXP_EXTRACT() |
Retorna a parte do argumento que corresponde ao grupo de captura dentro da expressão regular. |
REGEXP_REPLACE() |
Substitui uma substring que corresponde a uma expressão regular. |
Funções de string | |
---|---|
CONCAT() |
Retornará a concatenação de duas ou mais strings, ou NULL se qualquer um dos valores for NULL. |
expr CONTAINS 'str' |
Retorna true se expr contém o argumento de string especificado. |
INSTR() |
Retorna o índice baseado em 1 da primeira ocorrência de uma string. |
LEFT() |
Retorna os caracteres mais à esquerda de uma string. |
LENGTH() |
Retorna o comprimento da string. |
LOWER() |
Retorna a string original com todos os caracteres em letras minúsculas. |
LPAD() |
Insere caracteres à esquerda de uma string. |
LTRIM() |
Remove caracteres do lado esquerdo de uma string. |
REPLACE() |
Substitui todas as ocorrências de uma substring. |
RIGHT() |
Retorna os caracteres mais à direita de uma string. |
RPAD() |
Insere caracteres no lado direito de uma string. |
RTRIM() |
Remove os caracteres finais do lado direito de uma string. |
SPLIT() |
Divide uma string em substrings repetidas. |
SUBSTR() |
Retorna uma substring... |
UPPER() |
Retorna a string original com todos os caracteres em letras maiúsculas. |
Funções de caractere curinga de tabela | |
---|---|
TABLE_DATE_RANGE() |
Consulta várias tabelas diárias que abrangem um período. |
TABLE_DATE_RANGE_STRICT() |
Consulta várias tabelas diárias que abrangem um período, sem datas ausentes. |
TABLE_QUERY() |
Consulta tabelas com nomes que correspondem a um predicado especificado. |
Funções de URL | |
---|---|
HOST() |
Dado um URL, retorna o nome do host como uma string. |
DOMAIN() |
Dado um URL, retorna o domínio como uma string. |
TLD() |
Dado um URL, retorna o domínio de nível superior mais qualquer domínio de país no URL. |
Funções de janela | |
---|---|
AVG() COUNT(*) COUNT([DISTINCT]) MAX() MIN() STDDEV() SUM() |
A mesma operação das funções Aggregate correspondentes. Porém, elas são computadas em uma janela definida pela cláusula OVER. |
CUME_DIST() |
Retorna um duplo que indica a distribuição cumulativa de um valor em um grupo de valores... |
DENSE_RANK() |
Retorna a classificação do número inteiro de um valor em um grupo de valores. |
FIRST_VALUE() |
Retorna o primeiro valor do campo especificado na janela. |
LAG() |
Permite ler dados de uma linha anterior dentro de uma janela. |
LAST_VALUE() |
Retorna o último valor do campo especificado na janela. |
LEAD() |
Permite ler dados de uma linha seguinte dentro de uma janela. |
NTH_VALUE() |
Retorna o valor de <expr> na posição
<n> do frame da janela ... |
NTILE() |
Divide a janela no número especificado de intervalos. |
PERCENT_RANK() |
Retorna a classificação da linha atual, relativa às outras linhas da partição. |
PERCENTILE_CONT() |
Retorna um valor interpolado que estaria correlacionado ao argumento percentil com relação à janela... |
PERCENTILE_DISC() |
Retorna o valor mais próximo do percentil do argumento sobre a janela. |
RANK() |
Retorna a classificação do número inteiro de um valor em um grupo de valores. |
RATIO_TO_REPORT() |
Retorna a proporção de cada valor para a soma dos valores. |
ROW_NUMBER() |
Retorna o número da linha atual do resultado da consulta sobre a janela. |
Outras funções | |
---|---|
CASE WHEN ... THEN |
Use CASE para escolher entre duas ou mais expressões alternativas na consulta. |
CURRENT_USER() |
Retorna o endereço de e-mail do usuário que está executando a consulta. |
EVERY() |
Retornará true se o argumento for true para todas as entradas. |
FROM_BASE64() |
Converte a string de entrada codificada em base-64 em formato de BYTES. |
HASH() |
Calcula e retorna um valor de hash assinado de 64 bits. |
FARM_FINGERPRINT() |
Calcula e retorna um valor de impressão digital assinado de 64 bits. |
IF() |
Se o primeiro argumento for true, retornará o segundo argumento. Caso contrário, retornará o terceiro. |
POSITION() |
Retorna a posição sequencial baseada em 1 do argumento. |
SHA1() |
Retorna um hash SHA1 no formato BYTES. |
SOME() |
Retornará true se o argumento for true para pelo menos uma das entradas. |
TO_BASE64() |
Converte o argumento BYTES em uma string codificada em base-64. |
Funções de agregação
As funções de agregação retornam valores que representam resumos de conjuntos de dados maiores, o que torna essas funções especialmente úteis para a análise de registros. Uma função de agregação opera em um conjunto de valores e retorna um único valor por tabela, grupo ou escopo:
- Agregação de tabelas
Usa uma função de agregação para resumir todas as linhas de qualificação na tabela. Por exemplo:
SELECT COUNT(f1) FROM ds.Table;
- Agregação em grupo
Usa uma função de agregação e uma cláusula
GROUP BY
que especifica um campo não agregado para resumir as linhas por grupo. Exemplo:SELECT COUNT(f1) FROM ds.Table GROUP BY b1;
A função TOP representa um caso especializado de agregação em grupo.
- Agregação em escopo
Este recurso se aplica somente a tabelas que tenham campos aninhados.
Para agregar valores repetidos dentro de um escopo definido, são usadas uma função de agregação e a palavra-chaveWITHIN
. Por exemplo:SELECT COUNT(m1.f2) WITHIN RECORD FROM Table;
O escopo pode ser
RECORD
, que corresponde à linha inteira, ou um nó (campo repetido em uma linha). As funções de agregação operam nos valores dentro do escopo e retornam resultados agregados para cada registro ou nó.
Você pode aplicar uma restrição a uma função de agregação usando uma das seguintes opções:
-
Um alias em uma consulta de subseleção. A restrição é especificada na cláusula
WHERE
externa.#legacySQL SELECT corpus, count_corpus_words FROM (SELECT corpus, count(word) AS count_corpus_words FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus) AS sub_shakespeare WHERE count_corpus_words > 4000
-
Um alias em uma cláusula HAVING.
#legacySQL SELECT corpus, count(word) AS count_corpus_words FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus HAVING count_corpus_words > 4000;
Também é possível se referir a um alias nas cláusulas GROUP BY
ou ORDER BY
.
Sintaxe
Funções de agregação | |
---|---|
AVG() |
Retorna a média dos valores para um grupo de linhas... |
BIT_AND() |
Retorna o resultado de uma operação AND bit a bit... |
BIT_OR() |
Retorna o resultado de uma operação OR bit a bit... |
BIT_XOR() |
Retorna o resultado de uma operação XOR bit a bit... |
CORR() |
Retorna o coeficiente de correlação de Pearson de um conjunto de pares de números. |
COUNT() |
Retorna o número total de valores... |
COUNT([DISTINCT]) |
Retorna o número total de valores não NULL... |
COVAR_POP() |
Calcula a covariância de população dos valores... |
COVAR_SAMP() |
Calcula a covariância de exemplo dos valores... |
EXACT_COUNT_DISTINCT() |
Retorna o número exato de valores não NULL distintos para o campo especificado. |
FIRST() |
Retorna o primeiro valor sequencial no escopo da função. |
GROUP_CONCAT() |
Concatena strings múltiplas em uma única string... |
GROUP_CONCAT_UNQUOTED() |
Concatena strings múltiplas em uma única string... não adicionará aspas duplas... |
LAST() |
Retorna o último valor sequencial... |
MAX() |
Retorna o valor máximo... |
MIN() |
Retorna o valor mínimo... |
NEST() |
Agrupa todos os valores no escopo de agregação atual em um campo repetido. |
NTH() |
Retorna o enésimo valor sequencial... |
QUANTILES() |
Calcula aproximado mínimo, máximo e quantis... |
STDDEV() |
Retorna o desvio padrão... |
STDDEV_POP() |
Calcula o desvio padrão da população... |
STDDEV_SAMP() |
Calcula o desvio padrão da amostra... |
SUM() |
Retorna a soma total dos valores... |
TOP() ... COUNT(*) |
Retorna os principais registros max_records por frequência. |
UNIQUE() |
Retorna o conjunto de valores não NULL exclusivos... |
VARIANCE() |
Calcula a variância dos valores... |
VAR_POP() |
Calcula a variância de população dos valores... |
VAR_SAMP() |
Calcula a variância de amostra dos valores... |
AVG(numeric_expr)
- Retorna a média dos valores de um grupo de linhas computadas por
numeric_expr
. Linhas com valor NULL não estão incluídas no cálculo. BIT_AND(numeric_expr)
- Retorna o resultado de uma operação
AND
bit a bit entre cada instância denumeric_expr
em todas as linhas. Os valoresNULL
são ignorados. Essa função retornaNULL
se todas as instâncias denumeric_expr
forem avaliadas comoNULL
. BIT_OR(numeric_expr)
- Retorna o resultado de uma operação
OR
bit a bit entre cada instância denumeric_expr
em todas as linhas. Os valoresNULL
são ignorados. Essa função retornaNULL
se todas as instâncias denumeric_expr
forem avaliadas comoNULL
. BIT_XOR(numeric_expr)
- Retorna o resultado de uma operação
XOR
bit a bit entre cada instância denumeric_expr
em todas as linhas. Os valoresNULL
são ignorados. Essa função retornaNULL
se todas as instâncias denumeric_expr
forem avaliadas comoNULL
. CORR(numeric_expr, numeric_expr)
- Retorna o coeficiente de correlação de Pearson de um conjunto de pares de números.
COUNT(*)
- Retorna o número total de valores (NULL e não NULL) no escopo da função. A menos que você esteja usando
COUNT(*)
com a funçãoTOP
, é melhor especificar explicitamente o campo para contar. COUNT([DISTINCT] field [, n])
- Retorna o número total de valores não NULL no escopo da função.
A função retornará o número de valores distintos do campo especificado se você usar a palavra-chave
DISTINCT
. O valor retornado paraDISTINCT
é uma aproximação estatística e não é garantido que esteja exato.Use
EXACT_COUNT_DISTINCT()
para uma resposta exata.Se você precisar de mais precisão de
, poderá especificar um segundo parâmetro,COUNT(DISTINCT)
n
, que fornece o limite abaixo do qual os resultados exatos são garantidos. Por padrão,n
é 1000, mas se você der umn
maior, conseguirá resultados exatos paraCOUNT(DISTINCT)
até esse valor den
. No entanto, fornecer valores maiores den
reduzirá a escalabilidade desse operador e poderá aumentar substancialmente o tempo de execução da consulta ou fazer com que a consulta falhe.Para calcular o número exato de valores distintos, use EXACT_COUNT_DISTINCT. Ou, para uma abordagem mais escalonável, considere usar
GROUP EACH BY
no(s) campo(s) relevante(s) e, em seguida, aplicarCOUNT(*)
. A abordagemGROUP EACH BY
é mais escalonável, mas pode gerar uma leve penalidade de desempenho inicial. COVAR_POP(numeric_expr1, numeric_expr2)
- Calcula a covariância de preenchimento dos valores computados por
numeric_expr1
enumeric_expr2
. COVAR_SAMP(numeric_expr1, numeric_expr2)
- Calcula a covariância de amostra dos valores computados por
numeric_expr1
enumeric_expr2
. EXACT_COUNT_DISTINCT(field)
- Retorna o número exato de valores não NULL distintos para o campo especificado. Para melhor escalabilidade e desempenho, use COUNT (campo DISTINCT).
FIRST(expr)
- Retorna o primeiro valor sequencial no escopo da função.
GROUP_CONCAT('str' [, separator])
-
Concatena strings múltiplas em uma única string, em que cada valor é separado pelo parâmetro opcional
separator
. Seseparator
for omitido, o BigQuery retornará uma string separada por vírgula.Se uma string nos dados de origem contém um caractere de aspas duplas,
GROUP_CONCAT
retorna a string com as aspas duplas adicionadas. Por exemplo, a stringa"b
retornaria como"a""b"
. UseGROUP_CONCAT_UNQUOTED
se você preferir que essas strings não sejam retornadas com as aspas duplas adicionadas.Exemplo:
#legacySQL SELECT GROUP_CONCAT(x) FROM ( SELECT 'a"b' AS x), ( SELECT 'cd' AS x);
GROUP_CONCAT_UNQUOTED('str' [, separator])
-
Concatena strings múltiplas em uma única string, em que cada valor é separado pelo parâmetro opcional
separator
. Seseparator
for omitido, o BigQuery retornará uma string separada por vírgula.Ao contrário de
GROUP_CONCAT
, essa função não adicionará aspas duplas aos valores retornados que incluem um caractere de aspas duplas. Por exemplo, a stringa"b
retornaria comoa"b
.Exemplo:
#legacySQL SELECT GROUP_CONCAT_UNQUOTED(x) FROM ( SELECT 'a"b' AS x), ( SELECT 'cd' AS x);
LAST(field)
- Retorna o último valor sequencial no escopo da função.
MAX(field)
- Retorna o valor máximo no escopo da função.
MIN(field)
- Retorna o valor mínimo no escopo da função.
NEST(expr)
-
Agrupa todos os valores no escopo de agregação atual em um campo repetido. Por exemplo, a consulta
"SELECT x, NEST(y) FROM ... GROUP BY x"
retorna um registro de saída para cada valorx
distinto e contém um campo repetido para todos os valoresy
pareados comx
na entrada da consulta. A funçãoNEST
requer uma cláusulaGROUP BY
.O BigQuery nivela automaticamente os resultados da consulta. Portanto, se você usar a função
NEST
na consulta de nível superior, os resultados não conterão campos repetidos. Use a funçãoNEST
ao utilizar uma subseleção que produz resultados intermediários para uso imediato por parte da mesma consulta. NTH(n, field)
- Retorna o valor sequencial de
n
th no escopo da função, em quen
é uma constante. A funçãoNTH
começa a contar a partir de 1, por isso, zero não é aplicável. Se o escopo da função tiver menos den
valores, a função retornaráNULL
. QUANTILES(expr[, buckets])
-
Calcula o mínimo, o máximo e os quantis aproximados para a expressão de entrada. Os valores de entrada
NULL
são ignorados. Uma entrada vazia ou exclusivamenteNULL
resulta em saídaNULL
. O número de quantis calculados é controlado com o parâmetrobuckets
opcional, que inclui o mínimo e o máximo na contagem. Para calcular N-tiles aproximados, use N + 1buckets
. O valor padrão debuckets
é 100. Observação: o padrão de 100 não estima percentis. Para estimar os percentis, use 101buckets
pelo menos. Se especificado explicitamente,buckets
precisa ser pelo menos 2.O erro fracionário por quantil é epsilon = 1 /
buckets
, o que significa que o erro diminui à medida que o número de intervalos aumenta. Por exemplo:QUANTILES(<expr>, 2) # computes min and max with 50% error. QUANTILES(<expr>, 3) # computes min, median, and max with 33% error. QUANTILES(<expr>, 5) # computes quartiles with 25% error. QUANTILES(<expr>, 11) # computes deciles with 10% error. QUANTILES(<expr>, 21) # computes vigintiles with 5% error. QUANTILES(<expr>, 101) # computes percentiles with 1% error.
A função
NTH
pode ser usada para escolher um quantil particular, mas lembre-se queNTH
é baseado em 1, e queQUANTILES
retorna o mínimo ("0º" quantil) na primeira posição, e o máximo ("100º" percentil ou "Nº" Ntil) na última posição. Por exemplo,NTH(11, QUANTILES(expr, 21))
estima a mediana deexpr
, enquantoNTH(20, QUANTILES(expr, 21))
estima o 19º vigintil (95º percentil) deexpr
. Ambas as estimativas têm uma margem de erro de 5%.Para melhorar a precisão, use mais intervalos. Por exemplo, para reduzir a margem de erro para os cálculos anteriores de 5% para 0,1%, use 1001 intervalos em vez de 21 e ajuste o argumento para a função
NTH
de acordo com isso. Para calcular a mediana com erro de 0,1%, useNTH(501, QUANTILES(expr, 1001))
. Para o percentil 95 com 0,1% de erro, useNTH(951, QUANTILES(expr, 1001))
. STDDEV(numeric_expr)
- Retorna o desvio padrão dos valores computados por
numeric_expr
. Linhas com valor NULL não estão incluídas no cálculo. A funçãoSTDDEV
é um alias deSTDDEV_SAMP
. STDDEV_POP(numeric_expr)
- Calcula o desvio-padrão da população do valor computado por
numeric_expr
. UseSTDDEV_POP()
para calcular o desvio padrão de um conjunto de dados que engloba toda a população de interesse. Se o conjunto de dados compreender apenas uma amostra representativa da população, useSTDDEV_SAMP()
. Para mais informações sobre o desvio padrão de amostra em comparação com população, consulte Desvio padrão na Wikipédia. STDDEV_SAMP(numeric_expr)
- Calcula o desvio-padrão da amostra do valor computado por
numeric_expr
. UseSTDDEV_SAMP()
para calcular o desvio padrão de uma população inteira com base em uma amostra representativa da população. Se o conjunto de dados compreender toda a população, useSTDDEV_POP()
. Para mais informações sobre o desvio padrão de amostra em comparação com população, consulte Desvio padrão na Wikipédia. SUM(field)
- Retorna a soma total dos valores no escopo da função. Somente para uso com tipos de dados numéricos.
TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)
- Retorna os principais registros max_records por frequência. Consulte a descrição a seguir da função TOP para ver detalhes.
UNIQUE(expr)
- Retorna o conjunto de valores exclusivos e não NULL no escopo da função em uma ordem indefinida. Semelhante a uma cláusula
GROUP BY
grande sem a palavra-chaveEACH
, a consulta apresentará um erro "Recursos excedidos" se houver muitos valores distintos. No entanto, ao contrário deGROUP BY
, a funçãoUNIQUE
pode ser aplicada com agregação com escopo definido, permitindo operação eficiente em campos aninhados com um número limitado de valores. VARIANCE(numeric_expr)
- Calcula a variância dos valores calculados por
numeric_expr
. Linhas com valor NULL não estão incluídas no cálculo. A funçãoVARIANCE
é um alias deVAR_SAMP
. VAR_POP(numeric_expr)
- Calcula a variância da população dos valores computados por
numeric_expr
. Para mais informações sobre o desvio padrão de amostra em comparação com população, consulte Desvio padrão na Wikipédia. VAR_SAMP(numeric_expr)
- Calcula a variância de amostra dos valores computados por
numeric_expr
. Para mais informações sobre o desvio padrão de amostra em comparação com população, consulte Desvio padrão na Wikipédia.
Função TOP()
TOP é uma função alternativa à cláusula GROUP BY. Ela é usada como uma sintaxe simplificada para GROUP BY ... ORDER BY ... LIMIT ...
. Geralmente, a função TOP executa mais rápido do que a consulta ... GROUP BY ... ORDER BY ... LIMIT ...
completa, mas pode retornar somente resultados aproximados. Veja a seguir a sintaxe da função TOP:
TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)
Ao usar TOP em uma cláusula SELECT
, inclua COUNT(*)
como um dos campos.
Uma consulta que utiliza a função TOP() só pode retornar dois campos: o campo TOP e o valor COUNT(*).
field|alias
- O campo ou alias a ser retornado.
max_values
- [Opcional] O número máximo de resultados a serem retornados. O padrão é 20.
multiplier
- Um número inteiro positivo que aumenta os valores retornados por
COUNT(*)
pelo múltiplo especificado.
Exemplos de TOP()
-
Consultas de exemplo básicas que usam
TOP()
As consultas a seguir usam
TOP()
para retornar 10 linhas.Exemplo 1:
#legacySQL SELECT TOP(word, 10) as word, COUNT(*) as cnt FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th";
Exemplo 2:
#legacySQL SELECT word, left(word, 3) FROM (SELECT TOP(word, 10) AS word, COUNT(*) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th");
-
Comparar
TOP()
comGROUP BY...ORDER BY...LIMIT
A consulta retorna, em ordem, as dez palavras usadas mais frequentemente contendo "th" e o número de documentos em que as palavras foram usadas. A consulta
TOP
será executada muito mais rapidamente:Exemplo sem
TOP()
:#legacySQL SELECT word, COUNT(*) AS cnt FROM ds.Table WHERE word CONTAINS 'th' GROUP BY word ORDER BY cnt DESC LIMIT 10;
Exemplo com
TOP()
:#legacySQL SELECT TOP(word, 10), COUNT(*) FROM ds.Table WHERE word contains 'th';
-
Como usar o parâmetro
multiplier
.As consultas a seguir mostram como o parâmetro
multiplier
afeta o resultado da consulta. A primeira consulta retorna o número de nascimentos por mês em Wyoming. A segunda consulta usa o parâmetromultiplier
para multiplicar os valorescnt
por 100.Exemplo sem o parâmetro
multiplier
:#legacySQL SELECT TOP(month,3) as month, COUNT(*) as cnt FROM [bigquery-public-data:samples.natality] WHERE state = "WY";
Retorna:
+-------+-------+ | month | cnt | +-------+-------+ | 7 | 19594 | | 5 | 19038 | | 8 | 19030 | +-------+-------+
Exemplo com o parâmetro
multiplier
:#legacySQL SELECT TOP(month,3,100) as month, COUNT(*) as cnt FROM [bigquery-public-data:samples.natality] WHERE state = "WY";
Retorna:
+-------+---------+ | month | cnt | +-------+---------+ | 7 | 1959400 | | 5 | 1903800 | | 8 | 1903000 | +-------+---------+
Observação: é preciso incluir COUNT(*)
na cláusula SELECT
para usar TOP
.
Exemplos avançados
-
Média e desvio padrão agrupados por estado
A consulta a seguir retorna a média e o desvio padrão do peso no nascimento em Ohio em 2003, agrupados por mães fumantes e não fumantes.
Exemplo:
#legacySQL SELECT cigarette_use, /* Finds average and standard deviation */ AVG(weight_pounds) baby_weight, STDDEV(weight_pounds) baby_weight_stdev, AVG(mother_age) mother_age FROM [bigquery-public-data:samples.natality] WHERE year=2003 AND state='OH' /* Group the result values by those */ /* who smoked and those who didn't. */ GROUP BY cigarette_use;
-
Filtrar resultados da consulta usando um valor agregado
Para filtrar os resultados da consulta usando um valor agregado (por exemplo, filtrando pelo valor de uma
SUM
), use a funçãoHAVING
.HAVING
compara um valor a um resultado determinado por uma função de agregação, em oposição aWHERE
, que é operado em cada linha antes da agregação.Exemplo:
#legacySQL SELECT state, /* If 'is_male' is True, return 'Male', */ /* otherwise return 'Female' */ IF (is_male, 'Male', 'Female') AS sex, /* The count value is aliased as 'cnt' */ /* and used in the HAVING clause below. */ COUNT(*) AS cnt FROM [bigquery-public-data:samples.natality] WHERE state != '' GROUP BY state, sex HAVING cnt > 3000000 ORDER BY cnt DESC
Retorna:
+-------+--------+---------+ | state | sex | cnt | +-------+--------+---------+ | CA | Male | 7060826 | | CA | Female | 6733288 | | TX | Male | 5107542 | | TX | Female | 4879247 | | NY | Male | 4442246 | | NY | Female | 4227891 | | IL | Male | 3089555 | +-------+--------+---------+
Operadores aritméticos
Os operadores aritméticos pegam argumentos numéricos e retornam um resultado numérico. Cada argumento pode ser um literal numérico ou um valor numérico retornado por uma consulta. Se a operação aritmética é avaliada como um resultado indefinido, a operação retorna NULL
.
Sintaxe
Operador | Descrição | Exemplo |
---|---|---|
+ | Adição |
Retorna: 10 |
- | Subtração |
Retorna: 1 |
* | Multiplicação |
Retorna: 24 |
/ | Divisão |
Retorna: 1,5 |
% | Modulo |
Retorna: 2 |
Funções bit a bit
As funções bit a bit operam no nível de bits individuais e requerem argumentos numéricos. Para mais informações sobre funções bit a bit, consulte Operação bit a bit.
Três funções bit a bit extras, BIT_AND
, BIT_OR
e BIT_XOR
, estão incluídas na documentação de funções de agregação.
Sintaxe
Operador | Descrição | Exemplo |
---|---|---|
& | AND bit a bit |
Retorna: 0 |
| | OR bit a bit |
Retorna: 28 |
^ | XOR bit a bit |
Retorna: 1 |
<< | Deslocar para a esquerda bit a bit |
Retorna: 16 |
>> | Deslocar para a direita bit a bit |
Retorna: 2 |
~ | NOT bit a bit |
Retorna: -3 |
BIT_COUNT(<numeric_expr>) |
Retorna o número de bits definidos em |
Retorna: 4 |
Funções de conversão
As funções de conversão alteram o tipo de dados de uma expressão numérica. Elas são especialmente úteis para garantir que os argumentos de uma função de comparação tenham o mesmo tipo de dados.
Sintaxe
Funções de conversão | |
---|---|
BOOLEAN() |
Converte em booleano. |
BYTES() |
Converte em bytes. |
CAST(expr AS type) |
Converte expr em uma variável do tipo type . |
FLOAT() |
Converte em duplo. |
HEX_STRING() |
Converte em string hexadecimal. |
INTEGER() |
Converte em número inteiro. |
STRING() |
Converte em string. |
BOOLEAN(<numeric_expr>)
-
- Retorna
true
se<numeric_expr>
não for 0 e não NULL. - Retorna
false
se<numeric_expr>
for 0. - Retorna
NULL
se<numeric_expr>
for NULL.
- Retorna
BYTES(string_expr)
- Retorna
string_expr
como um valor do tipobytes
. CAST(expr AS type)
- Converte
expr
em uma variável do tipotype
. FLOAT(expr)
-
Retorna
expr
como um double. Oexpr
pode ser uma string como'45.78'
, mas a função retornaNULL
para valores não numéricos. HEX_STRING(numeric_expr)
- Retorna
numeric_expr
como uma string hexadecimal. INTEGER(expr)
-
Converte
expr
em um número inteiro de 64 bits.- Retorna NULL se
expr
é uma string que não corresponde a um valor inteiro. - Retorna o número de microssegundos desde o período do unix, se
expr
for um carimbo de data/hora.
- Retorna NULL se
STRING(numeric_expr)
- Returna
numeric_expr
como uma string.
Funções de comparação
As funções de comparação retornam true
ou false
, com base nos tipos de comparações a seguir:
- uma comparação de duas expressões
- uma comparação de uma expressão ou um conjunto de expressões em relação a um critério específico, como, por exemplo, presença em uma lista especificada, caráter NULL ou um valor opcional não padrão.
Algumas das funções listadas abaixo retornam valores diferentes de true
ou false
, mas os valores retornados são baseados em operações de comparação.
É possível usar expressões numéricas ou de string como argumentos para funções de comparação. (As constantes de string precisam ser delimitadas por aspas simples ou duplas.) As expressões podem ser literais ou valores buscados por uma consulta. As funções de comparação são mais frequentemente usadas como condições de filtragem em cláusulas WHERE
, mas podem ser usadas em outras cláusulas.
Sintaxe
Funções de comparação | |
---|---|
expr1 = expr2 |
Retorna true se as expressões forem iguais. |
expr1 != expr2 expr1 <> expr2
|
Retorna true se as expressões não forem iguais. |
expr1 > expr2 |
Retorna true se expr1 for maior que expr2 . |
expr1 < expr2 |
Retorna true se expr1 for menor que expr2 . |
expr1 >= expr2 |
Retorna true se expr1 for maior ou igual a expr2 . |
expr1 <= expr2 |
Retorna true se expr1 for menor ou igual a expr2 . |
expr1 BETWEEN expr2 AND expr3 |
Retorna true se o valor de expr1
for entre expr2 e expr3 , inclusive. |
expr IS NULL |
Retorna true se expr for NULL. |
expr IN() |
Retorna true se expr corresponder a
expr1 , expr2 ou qualquer valor entre parênteses. |
COALESCE() |
Retorna o primeiro argumento que não é NULL. |
GREATEST() |
Retorna o maior parâmetro numeric_expr . |
IFNULL() |
Se o argumento não for NULL, retornará o argumento. |
IS_INF() |
Retorna true se o infinito for positivo ou negativo. |
IS_NAN() |
Retorna true se o argumento for NaN . |
IS_EXPLICITLY_DEFINED() |
obsoleto: use expr IS NOT NULL . |
LEAST() |
Retorna o menor parâmetro numeric_expr do argumento. |
NVL() |
Se expr não for nulo, retornará expr . Caso contrário, retornará null_default . |
expr1 = expr2
- Retorna
true
se as expressões forem iguais. expr1 != expr2
expr1 <> expr2
- Retorna
true
se as expressões não forem iguais. expr1 > expr2
- Retorna
true
seexpr1
for maior queexpr2
. expr1 < expr2
- Retorna
true
seexpr1
for menor queexpr2
. expr1 >= expr2
- Retorna
true
seexpr1
for maior ou igual aexpr2
. expr1 <= expr2
- Retorna
true
seexpr1
for menor ou igual aexpr2
. expr1 BETWEEN expr2 AND expr3
-
Retorna
true
se o valor deexpr1
for maior ou igual aexpr2
e menor ou igual aexpr3
. expr IS NULL
- Retorna
true
seexpr
for NULL. expr IN(expr1, expr2, ...)
- Retorna
true
seexpr
corresponder aexpr1
,expr2
ou qualquer valor entre parênteses. A palavra-chaveIN
é uma abreviação eficiente para(expr = expr1 || expr = expr2 || ...)
. As expressões usadas com a palavra-chaveIN
precisam ser constantes e corresponder ao tipo de dadosexpr
. A cláusulaIN
também pode ser usada para criar semi-joins e anti-joins. Para mais informações, consulte Semi-join e anti-join. COALESCE(<expr1>, <expr2>, ...)
- Retorna o primeiro argumento que não é NULL.
GREATEST(numeric_expr1, numeric_expr2, ...)
-
Retorna o maior parâmetro
numeric_expr
. Todos os parâmetros precisam ser numéricos e do mesmo tipo. Se algum parâmetro forNULL
, essa função retornaráNULL
.Para ignorar valores
NULL
, use a funçãoIFNULL
para alterar os valoresNULL
para um que não afete a comparação. No exemplo de código a seguir, a funçãoIFNULL
é usada para alterar valoresNULL
para-1
, o que não afeta a comparação entre números positivos.SELECT GREATEST(IFNULL(a,-1), IFNULL(b,-1)) FROM (SELECT 1 as a, NULL as b);
IFNULL(expr, null_default)
- Se
expr
não for nulo, retornaráexpr
. Caso contrário, retornaránull_default
. IS_INF(numeric_expr)
- Retorna
true
senumeric_expr
é um infinito positivo ou negativo. IS_NAN(numeric_expr)
- Retorna
true
senumeric_expr
for o valor numérico especialNaN
. IS_EXPLICITLY_DEFINED(expr)
-
Essa função está obsoleta. Use
expr IS NOT NULL
, em vez disso. LEAST(numeric_expr1, numeric_expr2, ...)
-
Retorna o menor parâmetro
numeric_expr
. Todos os parâmetros precisam ser numéricos e do mesmo tipo. Se algum parâmetro forNULL
, essa função retornaráNULL
. NVL(expr, null_default)
- Se
expr
não for nulo, retornaráexpr
. Caso contrário, retornaránull_default
. A funçãoNVL
é um alias deIFNULL
.
Funções de data e hora
As funções a seguir permitem a manipulação de data e hora para carimbos de data/hora do UNIX, strings de data e tipos de dados TIMESTAMP. Para saber mais informações sobre como trabalhar com o tipo de dados TIMESTAMP, consulte Como usar TIMESTAMP.
As funções de data e hora que funcionam com carimbos de data/hora do UNIX operam na hora do UNIX. Funções de data e hora retornam valores com base no fuso horário UTC.
Sintaxe
Funções de data e hora | |
---|---|
CURRENT_DATE() |
Retorna a data atual no formato %Y-%m-%d . |
CURRENT_TIME() |
Retorna o horário atual do servidor no formato %H:%M:%S . |
CURRENT_TIMESTAMP() |
Retorna o horário atual do servidor no formato %Y-%m-%d %H:%M:%S . |
DATE() |
Retorna a data no formato %Y-%m-%d . |
DATE_ADD() |
Adiciona o intervalo especificado a um tipo de dados TIMESTAMP. |
DATEDIFF() |
Retorna o número de dias entre dois tipos de dados TIMESTAMP. |
DAY() |
Retorna o dia do mês como um número inteiro entre 1 e 31. |
DAYOFWEEK() |
Retorna o dia da semana como um número inteiro entre 1 (domingo) e 7 (sábado). |
DAYOFYEAR() |
Retorna o dia do ano como um número inteiro entre 1 e 366. |
FORMAT_UTC_USEC() |
Retorna um carimbo de data/hora UNIX no formato YYYY-MM-DD HH:MM:SS.uuuuuu . |
HOUR() |
Retorna a hora de um TIMESTAMP como um número inteiro entre 0 e 23. |
MINUTE() |
Retorna os minutos de um TIMESTAMP como um número inteiro entre 0 e 59. |
MONTH() |
Retorna o mês de um TIMESTAMP como um número inteiro entre 1 e 12. |
MSEC_TO_TIMESTAMP() |
Converte um carimbo de data/hora UNIX em milissegundos em um TIMESTAMP. |
NOW() |
Retorna o carimbo de data/hora UNIX atual em microssegundos. |
PARSE_UTC_USEC() |
Converte uma string de data em um carimbo de data/hora UNIX em microssegundos. |
QUARTER() |
Retorna o trimestre do ano de um TIMESTAMP como um número inteiro entre 1 e 4. |
SEC_TO_TIMESTAMP() |
Converte um carimbo de data/hora UNIX em segundos em um TIMESTAMP. |
SECOND() |
Retorna os segundos de um TIMESTAMP como um número inteiro entre 0 e 59. |
STRFTIME_UTC_USEC() |
Retorna uma string de data no formato date_format_str. |
TIME() |
Retorna um TIMESTAMP no formato %H:%M:%S . |
TIMESTAMP() |
Converte uma string de data em um TIMESTAMP. |
TIMESTAMP_TO_MSEC() |
Converte um TIMESTAMP em um carimbo de data/hora UNIX em milissegundos. |
TIMESTAMP_TO_SEC() |
Converte um TIMESTAMP em um carimbo de data/hora UNIX em segundos. |
TIMESTAMP_TO_USEC() |
Converte um TIMESTAMP em um carimbo de data/hora UNIX em microssegundos. |
USEC_TO_TIMESTAMP() |
Converte um carimbo de data/hora UNIX em microssegundos em um TIMESTAMP. |
UTC_USEC_TO_DAY() |
Desloca um carimbo de data/hora UNIX em microssegundos para o início do dia em que ele ocorre. |
UTC_USEC_TO_HOUR() |
Desloca um carimbo de data/hora UNIX em microssegundos para o início da hora em que ele ocorre. |
UTC_USEC_TO_MONTH() |
Desloca um carimbo de data/hora UNIX em microssegundos para o início do mês em que ele ocorre. |
UTC_USEC_TO_WEEK() |
Retorna um carimbo de data/hora UNIX em microssegundos que representa um dia na semana. |
UTC_USEC_TO_YEAR() |
Retorna um carimbo de data/hora UNIX em microssegundos que representa o ano. |
WEEK() |
Retorna a semana de um TIMESTAMP como um número inteiro entre 1 e 53. |
YEAR() |
Retorna o ano de um TIMESTAMP. |
CURRENT_DATE()
Retorna uma string legível da data atual no formato
%Y-%m-%d
.Exemplo:
SELECT CURRENT_DATE();
Retorna: 2013-02-01
CURRENT_TIME()
Retorna uma string legível do horário atual do servidor no formato
%H:%M:%S
.Exemplo:
SELECT CURRENT_TIME();
Retorna: 01:32:56
CURRENT_TIMESTAMP()
Retorna um tipo de dados TIMESTAMP do horário atual do servidor no formato
%Y-%m-%d %H:%M:%S
.Exemplo:
SELECT CURRENT_TIMESTAMP();
Retorna: 2013-02-01 01:33:35 UTC
DATE(<timestamp>)
Retorna uma string legível de um tipo de dados TIMESTAMP no formato
%Y-%m-%d
.Exemplo:
SELECT DATE(TIMESTAMP('2012-10-01 02:03:04'));
Retorna: 2012-10-01
DATE_ADD(<timestamp>,<interval>,
<interval_units>)Adiciona o intervalo especificado a um tipo de dados TIMESTAMP. Os valores
interval_units
possíveis incluemYEAR
,MONTH
,DAY
,HOUR
,MINUTE
eSECOND
. Seinterval
for um número negativo, o intervalo será subtraído do tipo de dados TIMESTAMP.Exemplo:
SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), 5, "YEAR");
Retorna: 2017-10-01 02:03:04 UTC
SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), -5, "YEAR");
Retorna: 2007-10-01 02:03:04 UTC
DATEDIFF(<timestamp1>,<timestamp2>)
Retorna o número de dias entre dois tipos de dados TIMESTAMP. O resultado é positivo se o primeiro tipo de dados TIMESTAMP vier após o segundo tipo de dados TIMESTAMP; caso contrário, o resultado é negativo.
Exemplo:
SELECT DATEDIFF(TIMESTAMP('2012-10-02 05:23:48'), TIMESTAMP('2011-06-24 12:18:35'));
Retorna: 466
Exemplo:
SELECT DATEDIFF(TIMESTAMP('2011-06-24 12:18:35'), TIMESTAMP('2012-10-02 05:23:48'));
Retorna: -466
DAY(<timestamp>)
Retorna o dia do mês de um tipo de dados TIMESTAMP como um número inteiro entre 1 e 31, inclusive.
Exemplo:
SELECT DAY(TIMESTAMP('2012-10-02 05:23:48'));
Retorna: 2
DAYOFWEEK(<timestamp>)
Retorna o dia da semana de um tipo de dados TIMESTAMP como um número inteiro entre 1 (domingo) e 7 (sábado), inclusive.
Exemplo:
SELECT DAYOFWEEK(TIMESTAMP("2012-10-01 02:03:04"));
Retorna: 2
DAYOFYEAR(<timestamp>)
Retorna o dia do ano de um tipo de dados TIMESTAMP como um número inteiro entre 1 e 366, inclusive. O número inteiro 1 refere-se a 1º de janeiro.
Exemplo:
SELECT DAYOFYEAR(TIMESTAMP("2012-10-01 02:03:04"));
Retorna: 275
FORMAT_UTC_USEC(<unix_timestamp>)
Retorna uma representação de string legível de um carimbo de data e hora UNIX no formato
YYYY-MM-DD HH:MM:SS.uuuuuu
.Exemplo:
SELECT FORMAT_UTC_USEC(1274259481071200);
Retorna: 2010-05-19 08:58:01.071200
HOUR(<timestamp>)
Retorna a hora de um tipo de dados TIMESTAMP como um número inteiro entre 0 e 23, inclusive.
Exemplo:
SELECT HOUR(TIMESTAMP('2012-10-02 05:23:48'));
Retorna: 5
MINUTE(<timestamp>)
Retorna os minutos de um tipo de dados TIMESTAMP como um número inteiro entre 0 e 59, inclusive.
Exemplo:
SELECT MINUTE(TIMESTAMP('2012-10-02 05:23:48'));
Retorna: 23
MONTH(<timestamp>)
Retorna o mês de um tipo de dados TIMESTAMP como um número inteiro entre 1 e 12, inclusive.
Exemplo:
SELECT MONTH(TIMESTAMP('2012-10-02 05:23:48'));
Retorna: 10
MSEC_TO_TIMESTAMP(<expr>)
- Converte um carimbo de data/hora UNIX em milissegundos em um tipo de dados TIMESTAMP.
Exemplo:
SELECT MSEC_TO_TIMESTAMP(1349053323000);
Retorna: 2012-10-01 01:02:03 UTC
SELECT MSEC_TO_TIMESTAMP(1349053323000 + 1000)
Retorna: 2012-10-01 01:02:04 UTC
NOW()
Retorna o carimbo de data/hora UNIX atual em microssegundos.
Exemplo:
SELECT NOW();
Retorna: 1359685811687920
PARSE_UTC_USEC(<date_string>)
-
Converte uma string de data em um carimbo de data/hora UNIX em microssegundos.
date_string
precisa ter o formatoYYYY-MM-DD HH:MM:SS[.uuuuuu]
. A parte fracional do segundo pode ter até seis dígitos de comprimento ou ser omitida.TIMESTAMP_TO_USEC é uma função equivalente que converte um argumento de tipo de dados TIMESTAMP em vez de uma string de data.
Exemplo:
SELECT PARSE_UTC_USEC("2012-10-01 02:03:04");
Retorna: 1349056984000000
QUARTER(<timestamp>)
Retorna o trimestre do ano de um tipo de dados TIMESTAMP como um inteiro entre 1 e 4, inclusive.
Exemplo:
SELECT QUARTER(TIMESTAMP("2012-10-01 02:03:04"));
Retorna: 4
SEC_TO_TIMESTAMP(<expr>)
Converte um carimbo de data/hora do UNIX em segundos em um tipo de dados TIMESTAMP.
Exemplo:
SELECT SEC_TO_TIMESTAMP(1355968987);
Retorna: 2012-12-20 02:03:07 UTC
SELECT SEC_TO_TIMESTAMP(INTEGER(1355968984 + 3));
Retorna: 2012-12-20 02:03:07 UTC
SECOND(<timestamp>)
-
Retorna os segundos de um tipo de dados TIMESTAMP como um inteiro entre 0 e 59, inclusive.
Durante um segundo bissexto, o intervalo de números inteiros está entre 0 e 60, inclusive.
Exemplo:
SELECT SECOND(TIMESTAMP('2012-10-02 05:23:48'));
Retorna: 48
STRFTIME_UTC_USEC(<unix_timestamp>,
<date_format_str>)-
Retorna uma string de data legível no formato date_format_str. date_format_str pode incluir caracteres de pontuação relacionados a datas (como date_format_str e date_format_str), bem como caracteres especiais aceitos pela função strftime em C++ (como date_format_str para o dia do mês).
Use as funções
UTC_USEC_TO_<function_name>
se você planeja agrupar dados de consulta por intervalos de tempo, como para conseguir todos os dados de um determinado mês, porque as funções são mais eficientes.Exemplo:
SELECT STRFTIME_UTC_USEC(1274259481071200, "%Y-%m-%d");
Retorna: 2010-05-19
TIME(<timestamp>)
Retorna uma string legível de um tipo de dados TIMESTAMP, no formato
%H:%M:%S
.Exemplo:
SELECT TIME(TIMESTAMP('2012-10-01 02:03:04'));
Retorna: 02:03:04
TIMESTAMP(<date_string>)
Converte uma string de data em um tipo de dados TIMESTAMP.
Exemplo:
SELECT TIMESTAMP("2012-10-01 01:02:03");
Retorna: 2012-10-01 01:02:03 UTC
TIMESTAMP_TO_MSEC(<timestamp>)
Converte um tipo de dados TIMESTAMP em um carimbo de data/hora UNIX em milissegundos.
Exemplo:
SELECT TIMESTAMP_TO_MSEC(TIMESTAMP("2012-10-01 01:02:03"));
Retorna: 1349053323000
TIMESTAMP_TO_SEC(<timestamp>)
- Converte um tipo de dados TIMESTAMP em um carimbo de data/hora UNIX em segundos.
Exemplo:
SELECT TIMESTAMP_TO_SEC(TIMESTAMP("2012-10-01 01:02:03"));
Retorna: 1349053323
TIMESTAMP_TO_USEC(<timestamp>)
-
Converte um tipo de dados TIMESTAMP em um carimbo de data/hora UNIX em microssegundos.
PARSE_UTC_USEC é uma função equivalente que converte um argumento de string de dados em vez de um tipo de dados TIMESTAMP.
Exemplo:
SELECT TIMESTAMP_TO_USEC(TIMESTAMP("2012-10-01 01:02:03"));
Retorna: 1349053323000000
USEC_TO_TIMESTAMP(<expr>)
Converte um carimbo de data/hora UNIX em microssegundos em um tipo de dados TIMESTAMP.
Exemplo:
SELECT USEC_TO_TIMESTAMP(1349053323000000);
Retorna: 2012-10-01 01:02:03 UTC
SELECT USEC_TO_TIMESTAMP(1349053323000000 + 1000000)
Retorna: 2012-10-01 01:02:04 UTC
UTC_USEC_TO_DAY(<unix_timestamp>)
-
Desloca um carimbo de data/hora UNIX em microssegundos para o início do dia em que ele ocorre.
Por exemplo, se
unix_timestamp
ocorrer em 19 de maio às 08:58, essa função retornará um carimbo de data e hora UNIX para 19 de maio às 00:00 (meia-noite).Exemplo:
SELECT UTC_USEC_TO_DAY(1274259481071200);
Retorna: 1274227200000000
UTC_USEC_TO_HOUR(<unix_timestamp>)
-
Desloca um carimbo de data/hora UNIX em microssegundos para o início da hora em que ele ocorre.
Por exemplo, se
unix_timestamp
ocorrer às 08:58, essa função retornará um carimbo de data/hora UNIX para 08:00 no mesmo dia.Exemplo:
SELECT UTC_USEC_TO_HOUR(1274259481071200);
Retorna: 1274256000000000
UTC_USEC_TO_MONTH(<unix_timestamp>)
-
Desloca um carimbo de data/hora UNIX em microssegundos para o início do mês em que ele ocorre.
Por exemplo, se
unix_timestamp
ocorrer no dia 19 de março, essa função retornará um carimbo de data/hora UNIX para 1º de março do mesmo ano.Exemplo:
SELECT UTC_USEC_TO_MONTH(1274259481071200);
Retorna: 1272672000000000
UTC_USEC_TO_WEEK(<unix_timestamp>,
<day_of_week>)-
Retorna um carimbo de data/hora para UNIX em microssegundos que representa um dia na semana do argumento
unix_timestamp
. Essa função recebe dois argumentos: um carimbo de data/hora do UNIX em microssegundos e um dia da semana de 0 (domingo) a 6 (sábado).Por exemplo, se
unix_timestamp
ocorrer na sexta-feira, 11 de abril de 2008, e você definirday_of_week
para 2 (terça-feira), a função retornará um carimbo de data/hora UNIX para a terça-feira, 8 de abril de 2008.Exemplo:
SELECT UTC_USEC_TO_WEEK(1207929480000000, 2) AS tuesday;
Retorna: 1207612800000000
UTC_USEC_TO_YEAR(<unix_timestamp>)
-
Retorna um carimbo de data/hora para UNIX em microssegundos que representa o ano do argumento
unix_timestamp
.Por exemplo, se
unix_timestamp
ocorrer em 2010, a função retornará1274259481071200
, a representação de microssegundo de2010-01-01 00:00
.Exemplo:
SELECT UTC_USEC_TO_YEAR(1274259481071200);
Retorna: 1262304000000000
WEEK(<timestamp>)
Retorna a semana de um tipo de dados TIMESTAMP como um inteiro entre 1 e 53, inclusive.
As semanas começam no domingo, portanto, se 1º de janeiro for em um dia diferente de domingo, a semana 1 tem menos de 7 dias e o primeiro domingo do ano é o primeiro dia da semana 2.
Exemplo:
SELECT WEEK(TIMESTAMP('2014-12-31'));
Retorna: 53
YEAR(<timestamp>)
- Retorna o ano de um tipo de dados TIMESTAMP.
Exemplo:
SELECT YEAR(TIMESTAMP('2012-10-02 05:23:48'));
Retorna: 2012
Exemplos avançados
-
Converter resultados de carimbo de data/hora de números inteiros em um formato legível
A consulta a seguir localiza os cinco maiores momentos no tempo em que a maioria das revisões da Wikipédia aconteceu. Para exibir os resultados em um formato legível, use a função
FORMAT_UTC_USEC()
do BigQuery, que recebe um carimbo de data/hora, em microssegundos, como uma entrada. Essa consulta multiplica os carimbos de data/hora de formato POSIX da Wikipédia (em segundos) por 1.000.000 para converter o valor em microssegundos.Exemplo:
#legacySQL SELECT /* Multiply timestamp by 1000000 and convert */ /* into a more human-readable format. */ TOP (FORMAT_UTC_USEC(timestamp * 1000000), 5) AS top_revision_time, COUNT (*) AS revision_count FROM [bigquery-public-data:samples.wikipedia];
Retorna:
+----------------------------+----------------+ | top_revision_time | revision_count | +----------------------------+----------------+ | 2002-02-25 15:51:15.000000 | 20976 | | 2002-02-25 15:43:11.000000 | 15974 | | 2010-02-02 03:34:51.000000 | 3 | | 2010-02-02 01:04:59.000000 | 3 | | 2010-02-01 23:55:05.000000 | 3 | +----------------------------+----------------+
-
Organização de resultados por carimbo de data/hora
É útil usar funções de data e hora para agrupar os resultados da consulta em intervalos correspondentes a anos, meses ou dias específicos. O exemplo a seguir usa a função
UTC_USEC_TO_MONTH()
para exibir quantos caracteres cada colaborador do Wikipedia usa nos comentários de revisão por mês.Exemplo:
#legacySQL SELECT contributor_username, /* Return the timestamp shifted to the * start of the month, formatted in * a human-readable format. Uses the * 'LEFT()' string function to return only * the first 7 characters of the formatted timestamp. */ LEFT (FORMAT_UTC_USEC( UTC_USEC_TO_MONTH(timestamp * 1000000)),7) AS month, SUM(LENGTH(comment)) as total_chars_used FROM [bigquery-public-data:samples.wikipedia] WHERE (contributor_username != '' AND contributor_username IS NOT NULL) AND timestamp > 1133395200 AND timestamp < 1157068800 GROUP BY contributor_username, month ORDER BY total_chars_used DESC;
Retorna (truncado):
+--------------------------------+---------+-----------------------+ | contributor_username | month | total_chars_used | +--------------------------------+---------+-----------------------+ | Kingbotk | 2006-08 | 18015066 | | SmackBot | 2006-03 | 7838365 | | SmackBot | 2006-05 | 5148863 | | Tawkerbot2 | 2006-05 | 4434348 | | Cydebot | 2006-06 | 3380577 | etc ...
Funções IP
As funções IP convertem endereços IP de e para um formato legível.
Sintaxe
Funções IP | |
---|---|
FORMAT_IP() |
Converte 32 bits menos significativos de integer_value em uma string de endereço IPv4 legível. |
PARSE_IP() |
Converte uma string representando o endereço IPv4 em um valor inteiro sem sinal. |
FORMAT_PACKED_IP() |
Retorna um endereço IP legível no formato
10.1.5.23 ou 2620:0:1009:1:216:36ff:feef:3f . |
PARSE_PACKED_IP() |
Retorna um endereço IP em BYTES. |
FORMAT_IP(integer_value)
- Converte 32 bits menos significativos de
integer_value
em uma string de endereço IPv4 legível. Por exemplo,FORMAT_IP(1)
retornará a string'0.0.0.1'
. PARSE_IP(readable_ip)
- Converte uma string representando o endereço IPv4 em um valor inteiro sem sinal. Por exemplo,
PARSE_IP('0.0.0.1')
retornará1
. Se string não for um endereço IPv4 válido,PARSE_IP
retornaráNULL
.
O BigQuery é compatível com gravação de endereços IPv4 e IPv6 em strings compactadas, como dados binários de 4 ou 16 bytes na ordem de bytes da rede. As funções descritas a seguir são compatíveis com a análise dos endereços de e para uma forma legível. Essas funções só operam em campos de string com IPs.
Sintaxe
FORMAT_PACKED_IP(packed_ip)
Retorna um endereço IP legível, no formato
10.1.5.23
ou2620:0:1009:1:216:36ff:feef:3f
. Exemplos:FORMAT_PACKED_IP('0123456789@ABCDE')
retorna'3031:3233:3435:3637:3839:4041:4243:4445'
FORMAT_PACKED_IP('0123')
retorna'48.49.50.51'
PARSE_PACKED_IP(readable_ip)
Retorna um endereço IP em BYTES. Se a string de entrada não for um endereço IPv4 ou IPv6 válido,
PARSE_PACKED_IP
retornaráNULL
. Exemplos:PARSE_PACKED_IP('48.49.50.51')
retorna'MDEyMw=='
PARSE_PACKED_IP('3031:3233:3435:3637:3839:4041:4243:4445')
retorna'MDEyMzQ1Njc4OUBBQkNERQ=='
Funções JSON
As funções JSON do BigQuery oferecem a capacidade de encontrar valores dentro dos dados JSON armazenados, por meio do uso de expressões semelhantes a JSONPath.
Armazenar dados JSON pode ser mais flexível do que declarar todos os campos individuais no esquema da tabela, mas pode levar a custos mais elevados. Quando você seleciona dados de uma string JSON, é cobrado pela verificação de toda a string, o que é mais caro do que se cada campo estiver em uma coluna separada. A consulta também é mais lenta, já que toda a string precisa ser analisada no momento da consulta. Mas para esquemas ad-hoc ou que mudam rápido, o custo extra do JSON pode valer a pena por causa da flexibilidade.
Use funções JSON em vez das funções de expressão regular do BigQuery se estiver trabalhando com dados estruturados, pois as funções JSON são mais fáceis de usar.
Sintaxe
Funções JSON | |
---|---|
JSON_EXTRACT() |
Seleciona um valor de acordo com a expressão JSONPath e retorna uma string JSON. |
JSON_EXTRACT_SCALAR() |
Seleciona um valor de acordo com a expressão JSONPath e retorna um escalar JSON. |
JSON_EXTRACT(json, json_path)
-
Seleciona um valor em
json
de acordo com a expressão JSONPathjson_path
.json_path
precisa ser uma constante de string. Retorna o valor no formato de string JSON. JSON_EXTRACT_SCALAR(json, json_path)
-
Seleciona um valor em
json
de acordo com a expressão JSONPathjson_path
.json_path
precisa ser uma constante de string. Retorna um valor JSON escalar.
Operadores lógicos
Operadores lógicos executam lógica binária ou ternária em expressões. A lógica binária retorna true
ou false
. A lógica ternária acomoda valores NULL
e retorna true
, false
ou NULL
.
Sintaxe
Operadores lógicos | |
---|---|
expr AND expr |
Retorna true se ambas as expressões forem verdadeiras. |
expr OR expr |
Retorna true se uma ou as duas expressões forem verdadeiras. |
NOT expr |
Retorna true se a expressão for falsa. |
expr AND expr
- Retorna
true
se ambas as expressões forem verdadeiras. - Retorna
false
se uma ou ambas as expressões forem falsas. - Retorna
NULL
se ambas as expressões são NULL ou se uma expressão é verdadeira, e a outra é NULL.
- Retorna
expr OR expr
- Retorna
true
se uma ou as duas expressões forem verdadeiras. - Retorna
false
se ambas as expressões são falsas. - Retorna
NULL
se ambas as expressões são NULL ou se uma expressão é falsa e a outra é NULL.
- Retorna
NOT expr
- Retorna
true
se a expressão for falsa. - Retorna
false
se a expressão for verdadeira. - Retorna
NULL
se a expressão for NULL.
Use
NOT
com outras funções como um operador de negação. Por exemplo,NOT IN(expr1, expr2)
ouIS NOT NULL
.- Retorna
Funções matemáticas
As funções matemáticas pegam argumentos numéricos e retornam um resultado numérico. Cada argumento pode ser um literal numérico ou um valor numérico retornado por uma consulta. Se a função matemática é avaliada como um resultado indefinido, a operação retorna NULL
.
Sintaxe
Funções matemáticas | |
---|---|
ABS() |
Retorna o valor absoluto do argumento. |
ACOS() |
Retorna o arco cosseno do argumento. |
ACOSH() |
Retorna o arco cosseno hiperbólico do argumento. |
ASIN() |
Retorna o arco seno do argumento. |
ASINH() |
Retorna o arco seno hiperbólico do argumento. |
ATAN() |
Retorna o arco tangente do argumento. |
ATANH() |
Retorna o arco tangente hiperbólico do argumento. |
ATAN2() |
Retorna o arco tangente dos dois argumentos. |
CEIL() |
Arredonda o argumento para cima até o número inteiro mais próximo e retorna o valor arredondado. |
COS() |
Retorna o cosseno do argumento. |
COSH() |
Retorna o cosseno hiperbólico do argumento. |
DEGREES() |
Converte de radianos em graus. |
EXP() |
Retorna e à potência do argumento. |
FLOOR() |
Arredonda o argumento para baixo para o número inteiro mais próximo. |
LN() LOG()
|
Retorna o logaritmo natural do argumento. |
LOG2() |
Retorna o logaritmo de Base 2 do argumento. |
LOG10() |
Retorna o logaritmo de Base 10 do argumento. |
PI() |
Retorna a constante π. |
POW() |
Retorna o primeiro argumento à potência do segundo argumento. |
RADIANS() |
Converte de graus em radianos. |
RAND() |
Retorna um valor flutuante aleatório no intervalo 0,0 <= valor < 1,0. |
ROUND() |
Arredonda o argumento para cima ou para baixo para o número inteiro mais próximo. |
SIN() |
Retorna o seno do argumento. |
SINH() |
Retorna o seno hiperbólico do argumento. |
SQRT() |
Retorna a raiz quadrada da expressão. |
TAN() |
Retorna a tangente do argumento. |
TANH() |
Retorna a tangente hiperbólica do argumento. |
ABS(numeric_expr)
- Retorna o valor absoluto do argumento.
ACOS(numeric_expr)
- Retorna o arco cosseno do argumento.
ACOSH(numeric_expr)
- Retorna o arco cosseno hiperbólico do argumento.
ASIN(numeric_expr)
- Retorna o arco seno do argumento.
ASINH(numeric_expr)
- Retorna o arco seno hiperbólico do argumento.
ATAN(numeric_expr)
- Retorna o arco tangente do argumento.
ATANH(numeric_expr)
- Retorna o arco tangente hiperbólico do argumento.
ATAN2(numeric_expr1, numeric_expr2)
- Retorna o arco tangente dos dois argumentos.
CEIL(numeric_expr)
- Arredonda o argumento para cima até o número inteiro mais próximo e retorna o valor arredondado.
COS(numeric_expr)
- Retorna o cosseno do argumento.
COSH(numeric_expr)
- Retorna o cosseno hiperbólico do argumento.
DEGREES(numeric_expr)
- Retorna
numeric_expr
, convertido de radianos para graus. EXP(numeric_expr)
- Retorna o resultado da elevação da constante "e", a base do logaritmo natural, para a potência de numeric_expr.
FLOOR(numeric_expr)
- Arredonda o argumento para baixo para o número inteiro mais próximo e retorna o valor arredondado.
LN(numeric_expr)
LOG(numeric_expr)
- Retorna o logaritmo natural do argumento.
LOG2(numeric_expr)
- Retorna o logaritmo de Base 2 do argumento.
LOG10(numeric_expr)
- Retorna o logaritmo de Base 10 do argumento.
PI()
- Retorna a constante π. A função
PI()
requer parênteses para indicar que é uma função, mas não usa argumentos entre parênteses. UsePI()
como uma constante com funções matemáticas e aritméticas. POW(numeric_expr1, numeric_expr2)
- Retorna o resultado do aumento de
numeric_expr1
para a potência denumeric_expr2
. RADIANS(numeric_expr)
- Retorna
numeric_expr
, convertido de graus para radianos. π radianos equivalem a 180 graus. RAND([int32_seed])
- Retorna um valor flutuante aleatório no intervalo 0 <= valor < 1. Cada valor
int32_seed
sempre gera a mesma sequência de números aleatórios em uma determinada consulta, contanto que você não use uma cláusulaLIMIT
. Seint32_seed
não for especificado, o BigQuery usará o carimbo de data/hora atual como valor de propagação. ROUND(numeric_expr [, digits])
- Arredonda o argumento para cima ou para baixo para o número inteiro mais próximo (ou para o número especificado de dígitos, se especificado) e retorna o valor arredondado.
SIN(numeric_expr)
- Retorna o seno do argumento.
SINH(numeric_expr)
- Retorna o seno hiperbólico do argumento.
SQRT(numeric_expr)
- Retorna a raiz quadrada da expressão.
TAN(numeric_expr)
- Retorna a tangente do argumento.
TANH(numeric_expr)
- Retorna a tangente hiperbólica do argumento.
Exemplos avançados
-
Consulta de caixa delimitadora
A consulta a seguir retorna uma coleção de pontos dentro de uma caixa delimitadora retangular centrada em torno de São Francisco (37,46, -122,50).
Exemplo:
#legacySQL SELECT year, month, AVG(mean_temp) avg_temp, MIN(min_temperature) min_temp, MAX(max_temperature) max_temp FROM [weather_geo.table] WHERE /* Return values between a pair of */ /* latitude and longitude coordinates */ lat / 1000 > 37.46 AND lat / 1000 < 37.65 AND long / 1000 > -122.50 AND long / 1000 < -122.30 GROUP BY year, month ORDER BY year, month ASC;
-
Consulta de círculo delimitador aproximado
Retorna um conjunto de até 100 pontos dentro de um círculo aproximado determinado pelo uso da Lei Esférica dos Cossenos, centrada em torno de Denver, Colorado (39,73, -104,98). Para essa consulta, usamos as funções matemáticas e trigonométricas do BigQuery, como
PI()
,SIN()
eCOS()
.Como a Terra não é uma esfera absoluta, e longitude + latitude convergem nos polos, esta consulta retorna uma aproximação que pode ser útil para muitos tipos de dados.
Exemplo:
#legacySQL SELECT distance, lat, long, temp FROM (SELECT ((ACOS(SIN(39.73756700 * PI() / 180) * SIN((lat/1000) * PI() / 180) + COS(39.73756700 * PI() / 180) * COS((lat/1000) * PI() / 180) * COS((-104.98471790 - (long/1000)) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance, AVG(mean_temp) AS temp, AVG(lat/1000) lat, AVG(long/1000) long FROM [weather_geo.table] WHERE month=1 GROUP BY distance) WHERE distance < 100 ORDER BY distance ASC LIMIT 100;
Funções de expressão regular
Com o uso da re2, o BigQuery se torna compatível com expressões regulares. Acesse essa documentação para consultar a sintaxe de expressão regular (páginas em inglês).
As expressões regulares são correspondências globais. Para começar a correspondência no início de uma palavra, use o caractere ^.
Sintaxe
Funções de expressão regular | |
---|---|
REGEXP_MATCH() |
Retornará true se o argumento corresponder à expressão regular. |
REGEXP_EXTRACT() |
Retorna a parte do argumento que corresponde ao grupo de captura dentro da expressão regular. |
REGEXP_REPLACE() |
Substitui uma substring que corresponde a uma expressão regular. |
REGEXP_MATCH('str', 'reg_exp')
Retorna true, se str corresponde à expressão regular. Para a correspondência de sequências sem expressões regulares, use CONTAINS, em vez de REGEXP_MATCH.
Exemplo:
#legacySQL SELECT word, COUNT(word) AS count FROM [bigquery-public-data:samples.shakespeare] WHERE (REGEXP_MATCH(word,r'\w\w\'\w\w')) GROUP BY word ORDER BY count DESC LIMIT 3;
Retorna:
+-------+-------+ | word | count | +-------+-------+ | ne'er | 42 | | we'll | 35 | | We'll | 33 | +-------+-------+
REGEXP_EXTRACT('str', 'reg_exp')
Retorna a parte de str que corresponde ao grupo de captura na expressão regular.
Exemplo:
#legacySQL SELECT REGEXP_EXTRACT(word,r'(\w\w\'\w\w)') AS fragment FROM [bigquery-public-data:samples.shakespeare] GROUP BY fragment ORDER BY fragment LIMIT 3;
Retorna:
+----------+ | fragment | +----------+ | NULL | | Al'ce | | As'es | +----------+
REGEXP_REPLACE('orig_str', 'reg_exp', 'replace_str')
Retorna uma sequência na qual qualquer subsequência de orig_str que corresponda a orig_str é substituída por orig_str. Por exemplo, REGEXP_REPLACE ('Hello', 'lo', 'p') retorna Help.
Exemplo:
#legacySQL SELECT REGEXP_REPLACE(word, r'ne\'er', 'never') AS expanded_word FROM [bigquery-public-data:samples.shakespeare] WHERE REGEXP_MATCH(word, r'ne\'er') GROUP BY expanded_word ORDER BY expanded_word LIMIT 5;
Retorna:
+---------------+ | expanded_word | +---------------+ | Whenever | | never | | nevertheless | | whenever | +---------------+
Exemplos avançados
-
Filtrar conjunto de resultados por expressão regular
As funções de expressão regular do BigQuery podem ser usadas para filtrar resultados em uma cláusula
WHERE
, bem como para exibir os resultados emSELECT
. O exemplo a seguir combina esses dois casos de uso de expressão regular em uma única consulta.Exemplo:
#legacySQL SELECT /* Replace white spaces in the title with underscores. */ REGEXP_REPLACE(title, r'\s+', '_') AS regexp_title, revisions FROM (SELECT title, COUNT(revision_id) as revisions FROM [bigquery-public-data:samples.wikipedia] WHERE wp_namespace=0 /* Match titles that start with 'G', end with * 'e', and contain at least two 'o's. */ AND REGEXP_MATCH(title, r'^G.*o.*o.*e$') GROUP BY title ORDER BY revisions DESC LIMIT 100);
-
Uso de expressões regulares em números inteiros ou dados flutuantes
Mesmo que as funções de expressões regulares do BigQuery funcionem somente para dados de strings, é possível usar
STRING()
para transmitir dados inteiros ou flutuantes para o formato de string. Neste exemplo,STRING()
é usado para converter o valor inteirocorpus_date
em uma string, que é então alterada porREGEXP_REPLACE
.Exemplo:
#legacySQL SELECT corpus_date, /* Cast the corpus_date to a string value */ REGEXP_REPLACE(STRING(corpus_date), '^16', 'Written in the sixteen hundreds, in the year \'' ) AS date_string FROM [bigquery-public-data:samples.shakespeare] /* Cast the corpus_date to string, */ /* match values that begin with '16' */ WHERE REGEXP_MATCH(STRING(corpus_date), '^16') GROUP BY corpus_date, date_string ORDER BY date_string DESC LIMIT 5;
Funções de string
As funções de string funcionam em dados de string. As constantes de sequência precisam ser delimitadas por aspas simples ou duplas. As funções de string diferenciam maiúsculas de minúsculas por padrão.
É possível acrescentar IGNORE CASE
ao final de uma consulta para ativar a correspondência que não diferencia
maiúsculas de minúsculas. IGNORE CASE
funciona somente em caracteres
ASCII e somente no nível superior da consulta.
Caracteres curingas não são compatíveis com essas funções. Para a funcionalidade de expressão regular, use funções de expressão regular.
Sintaxe
Funções de string | |
---|---|
CONCAT() |
Retornará a concatenação de duas ou mais strings, ou NULL se qualquer um dos valores for NULL. |
expr CONTAINS 'str' |
Retorna true se expr contém o argumento de string especificado. |
INSTR() |
Retorna o índice baseado em 1 da primeira ocorrência de uma string. |
LEFT() |
Retorna os caracteres mais à esquerda de uma string. |
LENGTH() |
Retorna o comprimento da string. |
LOWER() |
Retorna a string original com todos os caracteres em letras minúsculas. |
LPAD() |
Insere caracteres à esquerda de uma string. |
LTRIM() |
Remove caracteres do lado esquerdo de uma string. |
REPLACE() |
Substitui todas as ocorrências de uma substring. |
RIGHT() |
Retorna os caracteres mais à direita de uma string. |
RPAD() |
Insere caracteres no lado direito de uma string. |
RTRIM() |
Remove os caracteres finais do lado direito de uma string. |
SPLIT() |
Divide uma string em substrings repetidas. |
SUBSTR() |
Retorna uma substring... |
UPPER() |
Retorna a string original com todos os caracteres em maiúsculas. |
CONCAT('str1', 'str2', '...')
str1 + str2 + ...- Retorna a concatenação de duas ou mais strings, ou NULL se qualquer um dos valores for NULL. Exemplo: se
str1
forJava
estr2
forScript
,CONCAT
retornaráJavaScript
. expr CONTAINS 'str'
- Retorna
true
seexpr
contém o argumento de string especificado. Esta é uma comparação que diferencia maiúsculas de minúsculas. INSTR('str1', 'str2')
- Retorna o índice baseado em 1 da primeira ocorrência de str2 em str2 ou retorna 0 se str2 não ocorrer em str2.
LEFT('str', numeric_expr)
- Retorna os caracteres numeric_expr mais à esquerda de
str
. Se o número for maior que str, a string completa será retornada. Exemplo:LEFT('seattle', 3)
retornasea
. LENGTH('str')
- Retorna um valor numérico para o comprimento da string. Exemplo: se
str
for'123456'
,LENGTH
retornará6
. LOWER('str')
- Retorna a string original com todos os caracteres em minúsculas.
LPAD('str1', numeric_expr, 'str2')
- Preenche
str1
à esquerda comstr2
, repetindostr2
até que a string de resultado tenha exatamentenumeric_expr
caracteres. Exemplo:LPAD('1', 7, '?')
retorna??????1
. LTRIM('str1' [, str2])
-
Remove os caracteres do lado esquerdo de str1. Se str2 for omitido,
LTRIM
removerá os espaços do lado esquerdo de str2. Caso contrário,LTRIM
remove todos os caracteres em str2 do lado esquerdo de str2 (diferencia maiúsculas de minúsculas).Exemplos:
SELECT LTRIM("Say hello", "yaS")
retorna" hello"
.SELECT LTRIM("Say hello", " ySa")
retorna"hello"
. REPLACE('str1', 'str2', 'str3')
-
Substitui todas as instâncias de str2 dentro de str2 por str2.
RIGHT('str', numeric_expr)
- Retorna os caracteres numeric_expr mais à direita de
str
. Se o número é maior que a sequência, retorna a sequência inteira. Exemplo:RIGHT('kirkland', 4)
retornaland
. RPAD('str1', numeric_expr, 'str2')
- Preenche
str1
à direita comstr2
, repetindostr2
até que a string de resultado tenha exatamentenumeric_expr
caracteres. Exemplo:RPAD('1', 7, '?')
retorna1??????
. RTRIM('str1' [, str2])
-
Remove os caracteres finais do lado direito de str1. Se str2 for omitido,
RTRIM
removerá os espaços à direita de str2. Caso contrário,RTRIM
removerá todos os caracteres em str2 à direita de str2 (diferencia maiúsculas de minúsculas).Exemplos:
SELECT RTRIM("Say hello", "leo")
retorna"Say h"
.SELECT RTRIM("Say hello ", " hloe")
retorna"Say"
. SPLIT('str' [, 'delimiter'])
- Divide uma string em substrings repetidas. Se
delimiter
for especificado, a funçãoSPLIT
dividestr
em substrings, usandodelimiter
como delimitador. SUBSTR('str', index [, max_len])
- Retorna uma subsequência de
str
, começando àsindex
. Se o parâmetromax_len
opcional for usado, a string retornada terá no máximomax_len
caracteres. A contagem é iniciada em 1, então o primeiro caractere na string está na posição 1 (e não zero). Seindex
for5
, a subsequência começará com o quinto caractere da esquerda emstr
. Seindex
for-4
, a substring começará com o quarto caractere da direita emstr
. Exemplo:SUBSTR('awesome', -4, 4)
retorna a subsequênciasome
. UPPER('str')
- Retorna a string original com todos os caracteres em maiúsculas.
Como escapar caracteres especiais em sequências
Para escapar caracteres especiais, use um dos seguintes métodos:
- Use a notação
'\xDD'
, em que'\x'
é seguido pela representação hexadecimal de dois dígitos do caractere. - Use uma barra de escape na frente de barras, aspas simples e aspas duplas.
- Use sequências em estilo C (
'\a', '\b', '\f', '\n', '\r', '\t',
e'\v'
) para outros caracteres.
Alguns exemplos de escape:
'this is a space: \x20'
'this string has \'single quote\' inside it'
'first line \n second line'
"double quotes are also ok"
'\070' -> ERROR: octal escaping is not supported
Funções de caractere curinga de tabela
As funções de caractere curinga de tabela são uma maneira conveniente de consultar dados de um conjunto específico de tabelas. Uma função de caractere curinga de tabela é equivalente a uma união separada por vírgulas de todas as tabelas correspondidas por essa função. Quando você usa uma função de caractere curinga de tabela, o BigQuery acessa e cobra somente as tabelas que correspondem ao caractere curinga. As funções de caractere curinga de tabela são especificadas na cláusula FROM da consulta.
Se você usar funções de caractere curinga de tabela em uma consulta, as funções não precisam mais estar entre parênteses. Por exemplo, alguns dos exemplos a seguir usam parênteses, enquanto outros não.
Os resultados em cache não são aceitos para consultas em várias tabelas usando uma função com caracteres curinga (mesmo que a opção Usar resultados em cache esteja marcada). Se você executar a mesma consulta de caracteres curinga várias vezes, haverá cobrança por cada consulta.
Sintaxe
Funções de caractere curinga de tabela | |
---|---|
TABLE_DATE_RANGE() |
Consulta várias tabelas diárias que abrangem um período. |
TABLE_DATE_RANGE_STRICT() |
Consulta várias tabelas diárias que abrangem um período, sem datas ausentes. |
TABLE_QUERY() |
Consulta tabelas com nomes que correspondem a um predicado especificado. |
TABLE_DATE_RANGE(prefix, timestamp1, timestamp2)
-
Consulta tabelas diárias que se sobrepõem ao intervalo de tempo entre
<timestamp1>
e<timestamp2>
.Os nomes de tabelas precisam ter o seguinte formato:
<prefix><day>
, em que<day>
está no formatoYYYYMMDD
.Use as funções de data e hora para gerar os parâmetros de carimbo de data/hora. Por exemplo:
TIMESTAMP('2012-10-01 02:03:04')
DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')
Exemplo: receber tabelas entre dois dias
Este exemplo pressupõe as seguintes tabelas:
- mydata.people20140325
- mydata.people20140326
- mydata.people20140327
#legacySQL SELECT name FROM TABLE_DATE_RANGE([myproject-1234:mydata.people], TIMESTAMP('2014-03-25'), TIMESTAMP('2014-03-27')) WHERE age >= 35
Correspondem às seguintes tabelas:
- mydata.people20140325
- mydata.people20140326
- mydata.people20140327
Exemplo: conseguir tabelas em um intervalo de dois dias até "agora"
Neste exemplo, supomos que as seguintes tabelas estão em um projeto chamado
myproject-1234
:- mydata.people20140323
- mydata.people20140324
- mydata.people20140325
#legacySQL SELECT name FROM (TABLE_DATE_RANGE([myproject-1234:mydata.people], DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'), CURRENT_TIMESTAMP())) WHERE age >= 35
Corresponde às seguintes tabelas:
- mydata.people20140323
- mydata.people20140324
- mydata.people20140325
TABLE_DATE_RANGE_STRICT(prefix, timestamp1, timestamp2)
-
Esta função é equivalente a
TABLE_DATE_RANGE
. A única diferença é que, se alguma tabela diária estiver ausente na sequência,TABLE_DATE_RANGE_STRICT
falhará e retornará um erroNot Found: Table <table_name>
.Exemplo: erro na tabela que está faltando
Este exemplo pressupõe que existam as seguintes tabelas:
- people20140325
- people20140327
#legacySQL SELECT name FROM (TABLE_DATE_RANGE_STRICT([myproject-1234:mydata.people], TIMESTAMP('2014-03-25'), TIMESTAMP('2014-03-27'))) WHERE age >= 35
O exemplo acima retorna um erro "Não encontrado" para a tabela "people20140326".
TABLE_QUERY(dataset, expr)
-
Consulta tabelas em que os nomes correspondem ao
expr
fornecido. O parâmetroexpr
precisa ser representado como uma string e precisa conter uma expressão para avaliação. Por exemplo,'length(table_id) < 3'
.Exemplo: tabelas de correspondência em que os nomes contêm "oo" e têm um comprimento maior que 4
Este exemplo pressupõe que existam as seguintes tabelas:
- mydata.boo
- mydata.fork
- mydata.ooze
- mydata.spoon
#legacySQL SELECT speed FROM (TABLE_QUERY([myproject-1234:mydata], 'table_id CONTAINS "oo" AND length(table_id) >= 4'))
Correspondem às seguintes tabelas:
- mydata.ooze
- mydata.spoon
Exemplo: tabelas de correspondência que tenham nomes iniciados com "boo", seguidos por 3-5 dígitos numéricos
Neste exemplo, supomos que as seguintes tabelas estão em um projeto chamado
myproject-1234
:- mydata.book4
- mydata.book418
- mydata.boom12345
- mydata.boom123456789
- mydata.taboo999
#legacySQL SELECT speed FROM TABLE_QUERY([myproject-1234:mydata], 'REGEXP_MATCH(table_id, r"^boo[\d]{3,5}")')
Corresponde às seguintes tabelas:
- mydata.book418
- mydata.boom12345
Funções de URL
Sintaxe
Funções de URL | |
---|---|
HOST() |
Dado um URL, retorna o nome do host como uma string. |
DOMAIN() |
Dado um URL, retorna o domínio como uma string. |
TLD() |
Dada um URL, retorna o domínio de nível superior mais qualquer domínio de país no URL. |
HOST('url_str')
- Dado um URL, retorna o nome do host como uma string. Exemplo: HOST('http://www.google.com:80/index.html') retorna 'www.google.com'
DOMAIN('url_str')
- Dado um URL, retorna o domínio como uma string. Exemplo: DOMAIN('http://www.google.com:80/index.html') retorna 'google.com'.
TLD('url_str')
- Dado um URL, retorna o domínio de nível superior mais qualquer domínio de país no URL. Exemplo: o domínio TLD('http://www.google.com:80/index.html') retorna '.com'. TLD('http://www.google.co.uk:80/index.html') retorna '.co.uk'.
Observações:
- Essas funções não realizam pesquisa de DNS inversa, portanto, se você chamá-las usando um endereço IP, elas retornarão segmentos do endereço IP em vez de segmentos do nome do host.
- Todas as funções de análise de URL esperam caracteres em minúsculas. Os caracteres em maiúsculas no URL causarão um resultado NULL ou incorreto de outra forma. Considere passar a entrada para essa função por meio de LOWER () se os dados tiverem letras maiúsculas e minúsculas.
Exemplo avançado
Analisar nomes de domínio a partir de dados de URL
Esta consulta usa a função DOMAIN()
para retornar os domínios mais conhecidos listados como páginas iniciais do repositório no GitHub. Observe o uso de HAVING para filtrar registros usando o resultado da função DOMAIN()
. Esta função é útil para determinar as informações do referenciador a partir de dados de URL.
Exemplos:
#legacySQL SELECT DOMAIN(repository_homepage) AS user_domain, COUNT(*) AS activity_count FROM [bigquery-public-data:samples.github_timeline] GROUP BY user_domain HAVING user_domain IS NOT NULL AND user_domain != '' ORDER BY activity_count DESC LIMIT 5;
Retorna:
+-----------------+----------------+ | user_domain | activity_count | +-----------------+----------------+ | github.com | 281879 | | google.com | 34769 | | khanacademy.org | 17316 | | sourceforge.net | 15103 | | mozilla.org | 14091 | +-----------------+----------------+
Para analisar especificamente as informações de TLD, use a função TLD()
. Este exemplo exibe as principais TLDs que não estão na lista de exemplos comuns.
#legacySQL SELECT TLD(repository_homepage) AS user_tld, COUNT(*) AS activity_count FROM [bigquery-public-data:samples.github_timeline] GROUP BY user_tld HAVING /* Only consider TLDs that are NOT NULL */ /* or in our list of common TLDs */ user_tld IS NOT NULL AND NOT user_tld IN ('','.com','.net','.org','.info','.edu') ORDER BY activity_count DESC LIMIT 5;
Retorna:
+----------+----------------+ | user_tld | activity_count | +----------+----------------+ | .de | 22934 | | .io | 17528 | | .me | 13652 | | .fr | 12895 | | .co.uk | 9135 | +----------+----------------+
Funções de janela
As funções de janela, também conhecidas como funções analíticas, permitem cálculos em um subconjunto específico, ou "janela", de um conjunto de resultados. As funções de janela facilitam a criação de relatórios que incluem análises complexas, como médias dos últimos períodos e totais em execução.
Cada função de janela requer uma cláusula OVER
que especifica a parte superior e inferior da janela. Os três componentes da cláusula OVER
(particionamento, ordenação e enquadramento) fornecem controle
adicional sobre a janela. O particionamento permite que você divida os dados de entrada em grupos lógicos que têm uma característica comum. A ordenação permite ordenar os resultados dentro de uma partição. O enquadramento permite criar um quadro de janela deslizante dentro de uma partição que se move em relação à linha atual. Você pode configurar o tamanho do quadro da janela móvel com base em um número de linhas ou um intervalo de valores, como um intervalo de tempo.
#legacySQL SELECT <window_function> OVER ( [PARTITION BY <expr>] [ORDER BY <expr> [ASC | DESC]] [<window-frame-clause>] )
PARTITION BY
- Define a partição base sobre a qual essa função opera.
Especifique um ou mais nomes de coluna separados por vírgulas; uma partição será
criada para cada conjunto distinto de valores para essas colunas, semelhante
a uma cláusula
GROUP BY
. SePARTITION BY
for omitido, a partição base será todas as linhas na entrada para a função da janela. - A cláusula
PARTITION BY
também permite que as funções de janela particionem dados e paralelize a execução. Se você quer usar uma função de janela comallowLargeResults
ou se quer aplicar mais junções ou agregações à saída da função de janela, usePARTITION BY
para colocar em execução paralelamente. JOIN EACH
eGROUP EACH BY
não podem ser usadas na saída de funções de janela. Para gerar resultados de consulta grandes ao usar funções de janela, usePARTITION BY
.ORDER BY
- Classifica a partição. Se
ORDER BY
estiver ausente, não há garantia de ordem de classificação padrão. A classificação ocorre no nível da partição, antes de qualquer cláusula de quadro de janela ser aplicada. Se você especificar uma janelaRANGE
, adicione uma cláusulaORDER BY
. A ordem padrão éASC
. ORDER BY
é opcional em alguns casos, mas algumas funções de janela, como rank() ou dense_rank(), exigem a cláusula.- Se você usar
ORDER BY
sem especificarROWS
ouRANGE
,ORDER BY
implica que a janela se estende desde o início da partição até a linha atual. Na ausência de uma cláusulaORDER BY
, a janela é toda a partição. <window-frame-clause>
-
{ROWS | RANGE} {BETWEEN <start> AND <end> | <start> | <end>}
- Um subconjunto da partição sobre a qual operar. Pode ser do mesmo tamanho da partição ou menor. Se você usar
ORDER BY
semwindow-frame-clause
, o frame de janela padrão seráRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
. Se você omitirORDER BY
ewindow-frame-clause
, o frame de janela padrão será a partição inteira.ROWS
- define uma janela em termos de posição da linha, relativa à linha atual. Por exemplo, para adicionar uma coluna que mostre a soma das cinco linhas de valores salariais precedentes, consulteSUM(salary) OVER (ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
. O conjunto de linhas normalmente inclui a linha atual, mas isso não é necessário.RANGE
- Define uma janela em termos de um intervalo de valores em uma determinada coluna, em relação ao valor dessa coluna na linha atual. Só opera em números e datas, em que os valores de data são números inteiros simples (microssegundos desde a época). Linhas vizinhas com o mesmo valor são chamadas de linhas de mesmo nível. As linhas dos pares deCURRENT ROW
são incluídas em um frame de janela que especificaCURRENT ROW
. Por exemplo, se você especificar o fim da janela a serCURRENT ROW
e a seguinte linha na janela tiver o mesmo valor, ela será incluída no cálculo da função.BETWEEN <start> AND <end>
: um intervalo, incluindo as linhas de início e término. O intervalo não precisa incluir a linha atual, mas<start>
precisa preceder ou ser igual a<end>
.<start>
- especifica o deslocamento inicial para esta janela, relativo à linha atual. As opções a seguir são aceitas:{UNBOUNDED PRECEDING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}
em que<expr>
é um número inteiro positivo,PRECEDING
. indica um número de linha ou valor de intervalo anterior eFOLLOWING
indica um número de linha ou valor de intervalo posterior.UNBOUNDED PRECEDING
significa a primeira linha da partição. Se o início preceder a janela, ele será definido como a primeira linha da partição.<end>
- especifica o deslocamento final da janela em relação à linha atual. As seguintes opções são aceitas:{UNBOUNDED FOLLOWING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}
em que<expr>
é um número inteiro positivo,PRECEDING
indica um número de linha ou valor de intervalo anterior eFOLLOWING
indica um número de linha ou valor de intervalo posteriorUNBOUNDED FOLLOWING
significa a última linha da partição. Se o fim for além do final da janela, ele será definido como a última linha da partição.
Ao contrário das funções de agregação, que recolhem muitas linhas de entrada em uma de saída, as funções de janela retornam uma linha de saída para cada uma de entrada.
Esse recurso facilita a criação de consultas que calculam os totais em execução e as médias móveis. Por exemplo, a seguinte consulta retorna um total em execução
para um pequeno conjunto de dados de cinco linhas definidas por instruções SELECT
:
#legacySQL SELECT name, value, SUM(value) OVER (ORDER BY value) AS RunningTotal FROM (SELECT "a" AS name, 0 AS value), (SELECT "b" AS name, 1 AS value), (SELECT "c" AS name, 2 AS value), (SELECT "d" AS name, 3 AS value), (SELECT "e" AS name, 4 AS value);
Valor de retorno:
+------+-------+--------------+ | name | value | RunningTotal | +------+-------+--------------+ | a | 0 | 0 | | b | 1 | 1 | | c | 2 | 3 | | d | 3 | 6 | | e | 4 | 10 | +------+-------+--------------+
O exemplo a seguir calcula uma média móvel dos valores na linha atual e na linha que a precede. O quadro de janela compreende duas linhas que se movem com a linha atual.
#legacySQL SELECT name, value, AVG(value) OVER (ORDER BY value ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS MovingAverage FROM (SELECT "a" AS name, 0 AS value), (SELECT "b" AS name, 1 AS value), (SELECT "c" AS name, 2 AS value), (SELECT "d" AS name, 3 AS value), (SELECT "e" AS name, 4 AS value);
Valor de retorno:
+------+-------+---------------+ | name | value | MovingAverage | +------+-------+---------------+ | a | 0 | 0.0 | | b | 1 | 0.5 | | c | 2 | 1.5 | | d | 3 | 2.5 | | e | 4 | 3.5 | +------+-------+---------------+
Sintaxe
Funções de janela | |
---|---|
AVG() COUNT(*) COUNT([DISTINCT]) MAX() MIN() STDDEV() SUM() |
A mesma operação das funções Aggregate correspondentes. Porém, elas são computadas em uma janela definida pela cláusula OVER. |
CUME_DIST() |
Retorna um duplo que indica a distribuição cumulativa de um valor em um grupo de valores... |
DENSE_RANK() |
Retorna a classificação do número inteiro de um valor em um grupo de valores. |
FIRST_VALUE() |
Retorna o primeiro valor do campo especificado na janela. |
LAG() |
Permite ler dados de uma linha anterior dentro de uma janela. |
LAST_VALUE() |
Retorna o último valor do campo especificado na janela. |
LEAD() |
Permite ler dados de uma linha seguinte dentro de uma janela. |
NTH_VALUE() |
Retorna o valor de <expr> na posição
<n> do frame da janela ... |
NTILE() |
Divide a janela no número especificado de intervalos. |
PERCENT_RANK() |
Retorna a classificação da linha atual, relativa às outras linhas da partição. |
PERCENTILE_CONT() |
Retorna um valor interpolado que estaria correlacionado ao argumento percentil com relação à janela... |
PERCENTILE_DISC() |
Retorna o valor mais próximo do percentil do argumento sobre a janela. |
RANK() |
Retorna a classificação do número inteiro de um valor em um grupo de valores. |
RATIO_TO_REPORT() |
Retorna a proporção de cada valor para a soma dos valores. |
ROW_NUMBER() |
Retorna o número da linha atual do resultado da consulta sobre a janela. |
-
AVG(numeric_expr)
COUNT(*)
COUNT([DISTINCT] field)
MAX(field)
MIN(field)
STDDEV(numeric_expr)
SUM(field)
-
Essas funções de janela executam a mesma operação das
funções Aggregate, mas são calculadas
sobre uma janela definida pela cláusula OVER.
Outra diferença significativa é que a função
COUNT([DISTINCT] field)
produz resultados exatos quando usada como uma função de janela, com comportamento semelhante à função de agregaçãoEXACT_COUNT_DISTINCT()
.Na consulta de exemplo, a cláusula
ORDER BY
faz com que a janela seja computada do início da partição para a linha atual, o que gera uma soma cumulativa para esse ano.#legacySQL SELECT corpus_date, corpus, word_count, SUM(word_count) OVER ( PARTITION BY corpus_date ORDER BY word_count) annual_total FROM [bigquery-public-data:samples.shakespeare] WHERE word='love' ORDER BY corpus_date, word_count
Retorna:
corpus_date corpus word_count annual_total 0 various 37 37 0 sonnets 157 194 1590 2kinghenryvi 18 18 1590 1kinghenryvi 24 42 1590 3kinghenryvi 40 82 CUME_DIST()
-
Retorna um duplo que indica a distribuição cumulativa de um valor em um grupo de valores, calculado usando a fórmula
<number of rows preceding or tied with the current row> / <total rows>
. Valores vinculados retornam o mesmo valor de distribuição cumulativa.Essa função de janela requer
ORDER BY
na cláusulaOVER
.#legacySQL SELECT word, word_count, CUME_DIST() OVER (PARTITION BY corpus ORDER BY word_count DESC) cume_dist, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Retorna:
word word_count cume_dist handkerchief 29 0,2 satisfaction 5 0,4 displeasure 4 0,8 instruments 4 0,8 circumstance 3 1,0 DENSE_RANK()
-
Retorna a classificação do número inteiro de um valor em um grupo de valores. A classificação é calculada com base em comparações com outros valores no grupo.
Valores vinculados exibem a mesma classificação. A classificação do próximo valor é incrementada em 1. Por exemplo, se dois valores correspondem à classificação 2, o próximo valor classificado é 3. Se você preferir um intervalo na lista de classificação, use rank().
Essa função de janela requer
ORDER BY
na cláusulaOVER
.#legacySQL SELECT word, word_count, DENSE_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) dense_rank, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Retorna:word word_count dense_rank handkerchief 29 1 satisfaction 5 2 displeasure 4 3 instruments 4 3 circumstance 3 4 FIRST_VALUE(<field_name>)
-
Retorna o primeiro valor de
<field_name>
na janela.#legacySQL SELECT word, word_count, FIRST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) fv, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 1
Retorna:word word_count fv imperfectly 1 imperfectly LAG(<expr>[, <offset>[, <default_value>]])
-
Permite ler dados de uma linha anterior dentro de uma janela. Especificamente,
LAG()
retorna o valor de<expr>
para a linha localizada<offset>
linhas antes da linha atual. Se a linha não existir,<default_value>
retornará.#legacySQL SELECT word, word_count, LAG(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lag, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Retorna:
word word_count lag handkerchief 29 null satisfaction 5 handkerchief displeasure 4 satisfaction instruments 4 displeasure circumstance 3 instruments LAST_VALUE(<field_name>)
-
Retorna o último valor de
<field_name>
na janela.#legacySQL SELECT word, word_count, LAST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) lv, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 1
Retorna:
word word_count lv imperfectly 1 imperfectly LEAD(<expr>[, <offset>[, <default_value>]])
-
Permite ler dados de uma linha seguinte dentro de uma janela. Especificamente,
LEAD()
retorna o valor de<expr>
para a linha localizada<offset>
linhas após a linha atual. Se a linha não existir,<default_value>
retornará.#legacySQL SELECT word, word_count, LEAD(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lead, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Retorna:word word_count lead handkerchief 29 satisfaction satisfaction 5 displeasure displeasure 4 instruments instruments 4 circumstance circumstance 3 null NTH_VALUE(<expr>, <n>)
-
Retorna o valor de
<expr>
na posição<n>
do frame da janela, em que<n>
é um índice com base em um. NTILE(<num_buckets>)
-
Divide uma sequência de linhas em intervalos
<num_buckets>
e atribui um número de bloco correspondente, como um número inteiro, com cada linha. A funçãontile()
atribui os números do intervalo da forma mais uniforme possível e retorna um valor de 1 a<num_buckets>
para cada linha.#legacySQL SELECT word, word_count, NTILE(2) OVER (PARTITION BY corpus ORDER BY word_count DESC) ntile, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Retorna:word word_count ntile handkerchief 29 1 satisfaction 5 1 displeasure 4 1 instruments 4 2 circumstance 3 2 PERCENT_RANK()
-
Retorna a classificação da linha atual, em relação às outras linhas da partição. Os valores retornados variam entre 0 e 1, inclusive. O primeiro valor retornado é 0,0.
Essa função de janela requer
ORDER BY
na cláusulaOVER
.#legacySQL SELECT word, word_count, PERCENT_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) p_rank, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Retorna:word word_count p_rank handkerchief 29 0,0 satisfaction 5 0,25 displeasure 4 0,5 instruments 4 0,5 circumstance 3 1,0 PERCENTILE_CONT(<percentile>)
-
Retorna um valor interpolado que seria mapeado para o argumento de percentil em relação à janela, depois de ordená-los de acordo com a cláusula
ORDER BY
.<percentile>
precisa estar entre 0 e 1.Essa função de janela requer
ORDER BY
na cláusulaOVER
.#legacySQL SELECT word, word_count, PERCENTILE_CONT(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_cont, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Retorna:word word_count p_cont handkerchief 29 4 satisfaction 5 4 displeasure 4 4 instruments 4 4 circumstance 3 4 PERCENTILE_DISC(<percentile>)
-
Retorna o valor mais próximo do percentil do argumento sobre a janela.
<percentile>
precisa estar entre 0 e 1.Essa função de janela requer
ORDER BY
na cláusulaOVER
.#legacySQL SELECT word, word_count, PERCENTILE_DISC(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_disc, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Retorna:word word_count p_disc handkerchief 29 4 satisfaction 5 4 displeasure 4 4 instruments 4 4 circumstance 3 4 RANK()
-
Retorna a classificação do número inteiro de um valor em um grupo de valores. A classificação é calculada com base em comparações com outros valores no grupo.
Valores vinculados exibem a mesma classificação. A classificação do próximo valor é incrementada de acordo com quantos valores vinculados ocorreram antes dele. Por exemplo, se dois valores se encaixam na classificação 2, o próximo valor classificado será 4, e não 3. Se você preferir não ter nenhum intervalo na lista de classificação, use dense_rank().
Essa função de janela requer
ORDER BY
na cláusulaOVER
.#legacySQL SELECT word, word_count, RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) rank, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Retorna:word word_count rank handkerchief 29 1 satisfaction 5 2 displeasure 4 3 instruments 4 3 circumstance 3 5 RATIO_TO_REPORT(<column>)
-
Retorna a proporção de cada valor para a soma dos valores, como um duplo entre 0 e 1.
#legacySQL SELECT word, word_count, RATIO_TO_REPORT(word_count) OVER (PARTITION BY corpus ORDER BY word_count DESC) r_to_r, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Retorna:word word_count r_to_r handkerchief 29 0,6444444444444445 satisfaction 5 0,1111111111111111 displeasure 4 0,08888888888888889 instruments 4 0,08888888888888889 circumstance 3 0,06666666666666667 ROW_NUMBER()
-
Retorna o número da linha atual do resultado da consulta sobre a janela, começando com 1.
#legacySQL SELECT word, word_count, ROW_NUMBER() OVER (PARTITION BY corpus ORDER BY word_count DESC) row_num, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Retorna:word word_count row_num handkerchief 29 1 satisfaction 5 2 displeasure 4 3 instruments 4 4 circumstance 3 5
Outras funções
Sintaxe
Outras funções | |
---|---|
CASE WHEN ... THEN |
Use CASE para escolher entre duas ou mais expressões alternativas na consulta. |
CURRENT_USER() |
Retorna o endereço de e-mail do usuário que está executando a consulta. |
EVERY() |
Retornará true se o argumento for true para todas as entradas. |
FROM_BASE64() |
Converte a string de entrada codificada em base-64 em formato de BYTES. |
HASH() |
Calcula e retorna um valor de hash assinado de 64 bits. |
FARM_FINGERPRINT() |
Calcula e retorna um valor de impressão digital assinado de 64 bits. |
IF() |
Se o primeiro argumento for true, retornará o segundo argumento. Caso contrário, retornará o terceiro. |
POSITION() |
Retorna a posição sequencial baseada em 1 do argumento. |
SHA1() |
Retorna um hash SHA1 no formato BYTES. |
SOME() |
Retornará true se o argumento for true para pelo menos uma das entradas. |
TO_BASE64() |
Converte o argumento BYTES em uma string codificada em base-64. |
CASE WHEN when_expr1 THEN then_expr1
WHEN when_expr2 THEN then_expr2 ...
ELSE else_expr END- Use CASE para escolher entre duas ou mais expressões alternativas na consulta. Expressões WHEN precisam ser booleanas, e todas as expressões em cláusulas THEN e ELSE precisam ter tipos compatíveis.
CURRENT_USER()
- Retorna o endereço de e-mail do usuário que está executando a consulta.
EVERY(<condition>)
- Retorna
true
secondition
for verdadeiro para todas as suas entradas. Quando usada com a cláusulaOMIT IF
, essa função é útil para consultas que envolvem campos repetidos. FROM_BASE64(<str>)
- Converte
str
string de entrada codificada como base64 no formato BYTES. Para converter BYTES em uma string codificada em base64, TO_BASE64(). HASH(expr)
- Calcula e retorna um valor de hash assinado de 64 bits dos bytes de
expr
, conforme definido pela biblioteca CityHash (versão 1.0.3). Qualquer expressão de string ou número inteiro é compatível e a função respeitaIGNORE CASE
para strings, retornando valores invariantes de caso. FARM_FINGERPRINT(expr)
- Calcula e retorna um valor de impressão digital assinado de 64 bits da entrada
STRING
ouBYTES
, usando a funçãoFingerprint64
a partir da biblioteca FarmHash de código aberto. A saída dessa função para uma entrada específica nunca vai mudar e corresponde à saída da funçãoFARM_FINGERPRINT
ao usar o GoogleSQL. RespeitaIGNORE CASE
para strings, retornando valores invariantes de caso. IF(condition, true_return, false_return)
- Retorna
true_return
oufalse_return
, dependendo decondition
ser verdadeiro ou falso. Os valores retornados podem ser literais ou valores derivados de campo, mas precisam ter o mesmo tipo de dados. Os valores derivados de campo não precisam ser incluídos na cláusulaSELECT
. POSITION(field)
- Retorna a posição sequencial baseada em 1 do campo dentro de um conjunto de campos repetidos.
SHA1(<str>)
- Retorna um hash SHA1, no formato BYTES, da string de entrada
str
. Converta o resultado para base64 usando TO_BASE64(). Por exemplo:#legacySQL SELECT TO_BASE64(SHA1(corpus)) FROM [bigquery-public-data:samples.shakespeare] LIMIT 100;
SOME(<condition>)
- Retorna
true
secondition
for verdadeiro para pelo menos uma de suas entradas. Quando usada com a cláusulaOMIT IF
, essa função é útil para consultas que envolvem campos repetidos. TO_BASE64(<bin_data>)
- Converte
bin_data
da entrada BYTES em uma string codificada em base64. Por exemplo:#legacySQL SELECT TO_BASE64(SHA1(title)) FROM [bigquery-public-data:samples.wikipedia] LIMIT 100;
Para converter uma string codificada em base64 em BYTES, utilize FROM_BASE64().
Exemplos avançados
-
Organização de resultados em categorias usando condicionais
A consulta a seguir usa um bloco
CASE/WHEN
para agrupar os resultados em categorias de região com base em uma lista de estados. Se o estado não aparecer como uma opção em uma das instruçõesWHEN
, o valor do estado será predefinido para "Nenhum".Exemplo:
#legacySQL SELECT CASE WHEN state IN ('WA', 'OR', 'CA', 'AK', 'HI', 'ID', 'MT', 'WY', 'NV', 'UT', 'CO', 'AZ', 'NM') THEN 'West' WHEN state IN ('OK', 'TX', 'AR', 'LA', 'TN', 'MS', 'AL', 'KY', 'GA', 'FL', 'SC', 'NC', 'VA', 'WV', 'MD', 'DC', 'DE') THEN 'South' WHEN state IN ('ND', 'SD', 'NE', 'KS', 'MN', 'IA', 'MO', 'WI', 'IL', 'IN', 'MI', 'OH') THEN 'Midwest' WHEN state IN ('NY', 'PA', 'NJ', 'CT', 'RI', 'MA', 'VT', 'NH', 'ME') THEN 'Northeast' ELSE 'None' END as region, average_mother_age, average_father_age, state, year FROM (SELECT year, state, SUM(mother_age)/COUNT(mother_age) as average_mother_age, SUM(father_age)/COUNT(father_age) as average_father_age FROM [bigquery-public-data:samples.natality] WHERE father_age < 99 GROUP BY year, state) ORDER BY year LIMIT 5;
Retorna:
+--------+--------------------+--------------------+-------+------+ | region | average_mother_age | average_father_age | state | year | +--------+--------------------+--------------------+-------+------+ | South | 24.342600163532296 | 27.683769419460344 | AR | 1969 | | West | 25.185041908446163 | 28.268214055448098 | AK | 1969 | | West | 24.780776677578217 | 27.831181063905248 | CA | 1969 | | West | 25.005834769924412 | 27.942978384829598 | AZ | 1969 | | South | 24.541730952905738 | 27.686430093306885 | AL | 1969 | +--------+--------------------+--------------------+-------+------+
-
Simulação de uma tabela dinâmica
Use declarações condicionais para organizar os resultados de uma consulta subselect em linhas e colunas. No exemplo a seguir, os resultados de uma pesquisa pelos artigos mais revisados da Wikipédia que começam com o valor 'Google' são organizados em colunas, em que a revisão é considerada se satisfaz a um conjunto de critérios.
Exemplo:
#legacySQL SELECT page_title, /* Populate these columns as True or False, */ /* depending on the condition */ IF (page_title CONTAINS 'search', INTEGER(total), 0) AS search, IF (page_title CONTAINS 'Earth' OR page_title CONTAINS 'Maps', INTEGER(total), 0) AS geo, FROM /* Subselect to return top revised Wikipedia articles */ /* containing 'Google', followed by additional text. */ (SELECT TOP (title, 5) as page_title, COUNT (*) as total FROM [bigquery-public-data:samples.wikipedia] WHERE REGEXP_MATCH (title, r'^Google.+') AND wp_namespace = 0 );
Retorna:
+---------------+--------+------+ | page_title | search | geo | +---------------+--------+------+ | Google search | 4261 | 0 | | Google Earth | 0 | 3874 | | Google Chrome | 0 | 0 | | Google Maps | 0 | 2617 | | Google bomb | 0 | 0 | +---------------+--------+------+
-
Como usar HASH para selecionar uma amostra aleatória dos dados
Algumas consultas podem fornecer um resultado útil usando subamostragem aleatória do conjunto de resultados. Para recuperar uma amostragem aleatória de valores, use a função
HASH
para retornar resultados em que o módulo "n" do hash seja igual a zero.Por exemplo, a consulta a seguir encontrará o
HASH()
do valor do "título" e, em seguida, verificará se esse valor do módulo "2" é zero. Isso precisa resultar em cerca de 50% dos valores rotulados como "amostrados". Para amostrar menos valores, aumente o valor da operação de módulo de "2" para algo maior. Na consulta, a funçãoABS
é usada em combinação comHASH
porque valores negativos podem ser retornados porHASH
, e o operador de módulo em um valor negativo gera um valor negativo.Exemplo:
#legacySQL SELECT title, HASH(title) AS hash_value, IF(ABS(HASH(title)) % 2 == 1, 'True', 'False') AS included_in_sample FROM [bigquery-public-data:samples.wikipedia] WHERE wp_namespace = 0 LIMIT 5;