Operadores e funções do SQL legado

Neste documento, você conhecerá as funções e operadores do SQL legado. A sintaxe de consulta preferida para BigQuery é SQL padrão. Para saber mais sobre o SQL padrão, consulte esta página.

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
% Módulo
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 Retornará true se as expressões forem iguais.
expr1 != expr2
expr1 <> expr2
Retorna true, se as expressões não são iguais.
expr1 > expr2 Retorna true, se expr1 é maior que expr2.
expr1 < expr2 Retorna true, se expr1 é menor que expr2.
expr1 >= expr2 Retorna true, se expr1 é maior que ou igual a expr2.
expr1 <= expr2 Retornará true, se expr1 for menor que ou igual a expr2.
expr1 BETWEEN expr2 AND expr3 Retornará true, se o valor de expr1 estiver entre expr2 e expr3, inclusive.
expr IS NULL Retornará true, se expr for NULL.
expr IN() Retornará true, se expr corresponder a expr1, expr2 ou a qualquer valor nos parênteses.
COALESCE() Retornará o primeiro argumento que não for NULL.
GREATEST() Retorna o maior parâmetro de numeric_expr.
IFNULL() Se o argumento não for NULL, retornará o argumento.
IS_INF() Retornará true se for infinito positivo ou negativo.
IS_NAN() Retornará true se o argumento for NaN.
IS_EXPLICITLY_DEFINED() Obsoleto: substitua por expr IS NOT NULL.
LEAST() Retorna o menor argumento do parâmetro numeric_expr.
NVL() Se expr não for NULL, 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 a hora atual do servidor no formato %H:%M:%S.
CURRENT_TIMESTAMP() Retorna a hora 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 os 32 bits menos significativos de integer_value em uma string de endereço IPv4 legível.
PARSE_IP() Converte uma string que representa um endereço IPv4 em um valor inteiro não assinado.
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 Retornará true se ambas as expressões forem verdadeiras.
expr OR expr Retornará true se uma ou ambas as expressões forem verdadeiras.
NOT expr Retornará 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 para 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' Retornará true, se expr tiver o argumento da sequência especificada.
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 quadro 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.

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 dos três. 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. Os aliases definidos em uma cláusula SELECT podem ser referenciados pelas cláusulas GROUP BY, HAVING e ORDER BY da consulta, mas não pelas cláusulas FROM, WHERE ou OMIT RECORD IF nem por outras expressões na mesma cláusula SELECT.

Observações:

  • Se você usar uma função de agregação na cláusula SELECT, será preciso usar uma função de agregação em todas as expressões ou a consulta precisará ter uma cláusula GROUP BY que inclua todos os campos não agregados da cláusula SELECT 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. */
    
  • Use 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

Este exemplo define aliases na cláusula SELECT e depois faz referência a um deles na cláusula ORDER BY. A coluna word não pode ser referenciada por 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 seria 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 em escopo produz uma saída para cada registro, expressões não agregadas podem ser selecionadas ao lado de expressões com agregação em escopo sem usar 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ê deseja 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. É possível consultar combinações dessas fontes de dados com a vírgula, que é o operador UNION ALL do BigQuery.

Tabelas de referência

Ao fazer referência a uma tabela, datasetId e tableId precisam ser especificados. project_name é opcional. Se project_name não for especificado, o BigQuery deixará 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. A palavra-chave opcional AS pode ser adicionada entre 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 tableId, desde que não haja nenhum project_name 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 tableId 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;

Uso de subconsultas

Uma subconsulta é uma instrução SELECT unida 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 estariam.

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 SQL padrão, o BigQuery usa vírgula como um operador UNION ALL em vez de como um operador CROSS JOIN. Esse é um comportamento legado que evoluiu porque, historicamente, o BigQuery não era compatível com CROSS JOIN e os usuários do BigQuery precisavam regularmente escrever consultas UNION ALL. No SQL padrão, 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 node 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 as operações INNER, [FULL|RIGHT|LEFT] OUTER e CROSS JOIN. Se nada for especificado, o padrão será INNER.

As operações CROSS JOIN não permitem cláusulas ON. CROSS JOIN consegue retornar uma grande quantidade de dados e pode resultar em uma consulta lenta e ineficiente ou em uma consulta que ultrapasse o número máximo permitido de recursos por consulta. Essas consultas apresentarão falha com um erro. Quando possível, prefira consultas que não usem CROSS JOIN. Por exemplo, CROSS JOIN muitas vezes é usado quando 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 a consulta apresentar falha 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 com a palavra-chave IN com uma subconsulta, enquanto que anti-join usa 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, você pode usar um 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 os menores índices de nascimento.

#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 a partir da consulta externa na cláusula WHERE precisam coincidir 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: funções de agregação não podem ser usadas 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. Ela é semelhante a uma cláusula WHERE , mas diferente em dois aspectos importantes. Primeiro, ela usa uma condição de exclusão, o que significa que os registros são omitidos se a expressão retorna true, mas mantidos se a expressão retorna false ou null. Em segundo lugar, 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 registros completos, OMIT...IF pode especificar um escopo mais estreito para filtrar apenas partes de um registro. Para tanto, o nome de um nó não folha do esquema é usado no lugar de RECORD na cláusula OMIT...IF. Essa funcionalidade raramente é utilizada pelos usuários do BigQuery. É possível encontrar mais documentação sobre esse comportamento avançado em um link na documentação de WITHIN acima.

Se você usar OMIT...IF para excluir parte de um registro em um campo repetido, e outros campos repetidos independentes também forem selecionados pela consulta, uma parte dos outros registros repetidos da consulta será omitida pelo BigQuery. Se for exibido o erro Cannot perform OMIT IF on repeated scope <scope> with independently repeating pass through field <field>, recomendamos que você passe para o SQL padrão. Para saber mais sobre a migração de instruções OMIT...IF para SQL padrão, consulte Como migrar para o SQL padrão.

Exemplo

Voltando ao exemplo usado para o modificador WITHIN, OMIT RECORD IF pode ser usado para realizar o mesmo para o qual WITHIN e HAVING foram usados 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 têm 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, ele demonstra como índices posicionais podem ser usados ​​em vez de nomes de campo 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 feita por uma cláusula GROUP BY é chamada de 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 geralmente oferece melhor desempenho. Use GROUP EACH BY quando a consulta apresentar falha 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 de grupo são NULL. Essas 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, você pode 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:

  • Campos não agregados existentes na cláusula SELECT precisam ser listados na cláusula GROUP 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. */
    
  • As expressões calculadas na cláusula SELECT não podem ser usadas na cláusula GROUP 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, pois 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 de GROUP BY, pode resolver alguns problemas de escalonamento.

Cláusula HAVING

A cláusula HAVING funciona exatamente como a cláusula WHERE, exceto que ela é avaliada após a cláusula SELECT para os resultados de todas expressões computadas serem visíveis à cláusula HAVING. A cláusula HAVING só pode se referir às saídas da cláusula 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 de 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 SELECT para que ela possa fazer referência à saída de qualquer expressão calculada em SELECT. Se um campo recebe um alias na cláusula SELECT, o alias precisa 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 para algumas consultas, mas quando você especifica funções de agregação, como, por exemplo, cláusulas COUNT ou ORDER BY, o conjunto de resultados completo ainda precisa ser processado antes do retorno dos resultados. A cláusula LIMIT é 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

As cláusulas individuais das instruções SELECT do BigQuery estão descritas em detalhes acima. Aqui, apresentamos a gramática completa de instruções SELECT em uma forma compacta 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.
  • A 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 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 linhas por grupo. Por 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-chave WITHIN. 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 node.

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;
    

Você também pode fazer referência 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 para um grupo de linhas calculadas 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 de numeric_expr em todas as linhas. Os valores NULL são ignorados. Com esta função, será retornado NULL se todas as instâncias de numeric_expr forem avaliadas como NULL.
BIT_OR(numeric_expr)
Retorna o resultado de uma operação OR bit a bit entre cada instância de numeric_expr em todas as linhas. Os valores NULL são ignorados. Com esta função, será retornado NULL se todas as instâncias de numeric_expr forem avaliadas como NULL.
BIT_XOR(numeric_expr)
Retorna o resultado de uma operação XOR bit a bit entre cada instância de numeric_expr em todas as linhas. Os valores NULL são ignorados. Com esta função, será retornado NULL se todas as instâncias de numeric_expr forem avaliadas como NULL.
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 utilizando COUNT(*) com a função TOP, é melhor especificar explicitamente o campo a ser contado.
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 para DISTINCT é uma aproximação estatística e não é garantido que esteja exato.

Use EXACT_COUNT_DISTINCT() para uma resposta exata.

Se você precisar de maior precisão de COUNT(DISTINCT), poderá especificar um segundo parâmetro, n, que forneça o limite abaixo do qual os resultados exatos são garantidos. Por padrão, n é 1000, mas se você inserir um n maior, conseguirá resultados exatos para COUNT(DISTINCT) até esse valor de n. No entanto, inserir valores maiores de n reduzirá a escalabilidade desse operador e poderá aumentar substancialmente o tempo de execução da consulta ou causar uma falha na consulta.

Para calcular o número exato de valores distintos, use EXACT_COUNT_DISTINCT. Se preferir, para uma abordagem mais escalonável, use GROUP EACH BY nos campos relevantes e depois aplique COUNT(*). A abordagem com GROUP EACH BY é mais escalável, mas pode ter uma pequena penalidade de desempenho inicial.

COVAR_POP(numeric_expr1, numeric_expr2)
Calcula a covariância da população dos valores calculados por numeric_expr1 e numeric_expr2.
COVAR_SAMP(numeric_expr1, numeric_expr2)
Calcula a covariância de amostra dos valores calculados por numeric_expr1 e numeric_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(DISTINCT field).
FIRST(expr)
Retorna o primeiro valor sequencial no escopo da função.
GROUP_CONCAT('str' [, separator])

Concatenação de várias strings em uma só, cada valor é separado pelo parâmetro opcional separator. Se separator for omitido, uma string separada por vírgulas será retornada pelo BigQuery.

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 string a"b retornaria "a""b". Use GROUP_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ção de várias strings em uma só, cada valor é separado pelo parâmetro opcional separator. Se separator for omitido, uma string separada por vírgulas será retornada pelo BigQuery.

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 string a"b retornaria a"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 valor x distinto. A saída contém um campo repetido para todos os valores y pareados com x na entrada de consulta. A função NEST requer uma cláusula GROUP BY.

O BigQuery equaliza automaticamente 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ção NEST ao usar uma subseleção que produz resultados intermediários para uso imediato por parte da mesma consulta.

NTH(n, field)
Retorna o valor sequencial no. no escopo da função, em que n é uma constante. A função NTH começa a contar a partir em 1, por isso, não há termo zero. Se o escopo da função tem menos de n valores, a função retorna 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 exclusivamente NULL resulta em saída NULL. O número de quantis calculados é controlado com o parâmetro opcional buckets, que inclui os valores mínimo e máximo na contagem. Para calcular Ntis, use N+1 buckets. O valor padrão de buckets é 100. (Observação: o padrão de 100 não estima percentis. Para estimar percentis, use no mínimo 101 buckets.) Se for explicitamente especificado, buckets precisa ser pelo menos 2.

O erro fracional 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 que NTH é baseado em 1, e que QUANTILES 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 de expr, enquanto NTH(20, QUANTILES(expr, 21)) estima o 19º vingtil (95º percentil) de expr. 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%, use NTH(501, QUANTILES(expr, 1001)). Para o 95º percentil com 0,1% de erro, use NTH(951, QUANTILES(expr, 1001)).

STDDEV(numeric_expr)
Retorna o desvio padrão dos valores calculados por numeric_expr. Linhas com valor NULL não estão incluídas no cálculo. A função STDDEV é um alias para STDDEV_SAMP.
STDDEV_POP(numeric_expr)
Calcula o desvio padrão de população do valor calculado por numeric_expr. Use STDDEV_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 incluir apenas uma amostra representativa da população, use STDDEV_SAMP(). Para mais informações sobre a população versus o desvio padrão da amostra, consulte Desvio padrão na Wikipédia.
STDDEV_SAMP(numeric_expr)
Calcula o desvio padrão de amostra do valor calculado por numeric_expr. Use STDDEV_SAMP() para calcular o desvio padrão de uma população inteira com base em uma amostra representativa dela. Se o conjunto de dados incluir toda a população, use STDDEV_POP(). Para mais informações sobre a população versus o desvio padrão da amostra, 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-chave EACH, a consulta vai apresentar falha e exibir o erro "Recursos excedidos" se houver muitos valores distintos. Ao contrário de GROUP BY, no entanto, a função UNIQUE pode ser aplicada com agregação em escopo, possibilitando uma 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ção VARIANCE é um alias para VAR_SAMP.
VAR_POP(numeric_expr)
Calcula a variância de população dos valores calculados por numeric_expr. Para mais informações sobre a população versus o desvio padrão da amostra, consulte Desvio padrão na Wikipédia.
VAR_SAMP(numeric_expr)
Calcula a variância de amostra dos valores calculados por numeric_expr. Para mais informações sobre a população versus o desvio padrão da amostra, consulte Desvio padrão na Wikipédia.

Função TOP()

TOP é uma função alternativa à cláusula GROUP BY. Ela é usada como sintaxe simplificada para GROUP BY ... ORDER BY ... LIMIT .... Geralmente, a execução da função TOP é mais rápida que uma consulta ... GROUP BY ... ORDER BY ... LIMIT ..., mas pode retornar apenas 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 é preciso incluir 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()

  • Exemplo de consultas básicas que usam TOP()

    As consultas a seguir usam TOP() para retornar dez 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");
    
  • Comparação de TOP() com GROUP BY...ORDER BY...LIMIT

    A consulta retorna, em ordem, as dez palavras usadas mais frequentemente contendo "th" e o número de documentos nos quais 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';
    
  • Uso do parâmetro multiplier.

    As seguintes consultas 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 usa o parâmetro multiplier para multiplicar os valores de cnt 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ção HAVING. HAVING compara um valor a um resultado determinado por uma função de agregação, em oposição a WHERE, que opera 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

SELECT 6 + (5 - 1);

Retorna: 10

- Subtração

SELECT 6 - (4 + 1);

Retorna: 1

* Multiplicação

SELECT 6 * (5 - 1);

Retorna: 24

/ Divisão

SELECT 6 / (2 + 2);

Retorna: 1,5

% Módulo

SELECT 6 % (2 + 2);

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 documentadas em funções de agregação.

Sintaxe

Operador Descrição Exemplo
& AND bit a bit

SELECT (1 + 3) & 1

Retorna: 0

| OR bit a bit

SELECT 24 | 12

Retorna: 28

^ XOR bit a bit

SELECT 1 ^ 0

Retorna: 1

<< Deslocar para a esquerda bit a bit

SELECT 1 << (2 + 2)

Retorna: 16

>> Deslocar para a direita bit a bit

SELECT (6 + 2) >> 2

Retorna: 2

~ NOT bit a bit

SELECT ~2

Retorna: -3

BIT_COUNT(<numeric_expr>)

Retorna o número de bits definido em <numeric_expr>.

SELECT BIT_COUNT(29);

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>)
  • Retornará true, se <numeric_expr> não for 0 nem NULL.
  • Retornará false, se <numeric_expr> for 0.
  • Retornará NULL, se <numeric_expr> for NULL.
BYTES(string_expr)
Retorna string_expr como o valor do tipo bytes.
CAST(expr AS type)
Converte expr em uma variável do tipo type.
FLOAT(expr)
Retorna expr como um valor duplo. O expr pode ser uma string como '45.78', mas a função retorna NULL 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.
  • Retornará NULL, se expr for uma string que não corresponde a um valor inteiro.
  • Retornará o número de microssegundos, desde a época unix, se expr for um carimbo de data/hora.
STRING(numeric_expr)
Retorna numeric_expr como uma sequência.

Funções de comparação

Funções de comparação retornam true ou false, com base nos seguintes tipos de comparações:

  • 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 outros valores além de true ou false, mas eles são baseados em operações de comparação.

Você pode 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 Retornará true se as expressões forem iguais.
expr1 != expr2
expr1 <> expr2
Retorna true, se as expressões não são iguais.
expr1 > expr2 Retorna true, se expr1 é maior que expr2.
expr1 < expr2 Retorna true, se expr1 é menor que expr2.
expr1 >= expr2 Retorna true, se expr1 é maior que ou igual a expr2.
expr1 <= expr2 Retornará true, se expr1 for menor que ou igual a expr2.
expr1 BETWEEN expr2 AND expr3 Retornará true, se o valor de expr1 estiver entre expr2 e expr3, inclusive.
expr IS NULL Retornará true, se expr for NULL.
expr IN() Retornará true, se expr corresponder a expr1, expr2 ou a qualquer valor nos parênteses.
COALESCE() Retornará o primeiro argumento que não for NULL.
GREATEST() Retorna o maior parâmetro de numeric_expr.
IFNULL() Se o argumento não for NULL, retornará o argumento.
IS_INF() Retornará true se for infinito positivo ou negativo.
IS_NAN() Retornará true se o argumento for NaN.
IS_EXPLICITLY_DEFINED() Obsoleto: substitua por expr IS NOT NULL.
LEAST() Retorna o menor argumento do parâmetro numeric_expr.
NVL() Se expr não for NULL, retornará expr; caso contrário, retornará null_default.
expr1 = expr2
Retornará true, se as expressões forem iguais.
expr1 != expr2
expr1 <> expr2
Retorna true, se as expressões não são iguais.
expr1 > expr2
Retorna true, se expr1 é maior que expr2.
expr1 < expr2
Retorna true, se expr1 é menor que expr2.
expr1 >= expr2
Retorna true, se expr1 é maior que ou igual a expr2.
expr1 <= expr2
Retornará true, se expr1 for menor que ou igual a expr2.
expr1 BETWEEN expr2 AND expr3

Retornará true se o valor de expr1 for maior ou igual a expr2 e menor ou igual a expr3.

expr IS NULL
Retornará true, se expr for NULL.
expr IN(expr1, expr2, ...)
Retorna true, se expr corresponde a expr1, expr2 ou a qualquer valor nos parênteses. A palavra-chave IN é uma abreviação eficiente para (expr = expr1 || expr = expr2 || ...). As expressões usadas com a palavra-chave IN precisam ser constantes e corresponder ao tipo de dados de expr. A cláusula IN 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 de numeric_expr. Todos os parâmetros precisam ser numéricos e do mesmo tipo. Se algum parâmetro é NULL, esta função retorna NULL.

Para ignorar valores NULL, use a função IFNULL para alterar os valores NULL para um que não afete a comparação. No exemplo de código a seguir, a função IFNULL é usada para alterar valores NULL 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 NULL, retornará expr; caso contrário, retornará null_default.
IS_INF(numeric_expr)
Retornará true, se numeric_expr for infinito positivo ou negativo.
IS_NAN(numeric_expr)
Retornará true, se numeric_expr for o valor numérico especial NaN.
IS_EXPLICITLY_DEFINED(expr)

Essa função está obsoleta. Em vez dela, use expr IS NOT NULL.

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 for NULL, essa função retornará NULL

NVL(expr, null_default)
Se expr não for NULL, retornará expr; caso contrário, retornará null_default. A função NVL é um alias de IFNULL.

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 a hora atual do servidor no formato %H:%M:%S.
CURRENT_TIMESTAMP() Retorna a hora 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 da hora atual do servidor no formato %H:%M:%S.

Exemplo:

SELECT CURRENT_TIME();

Retorna: 01:32:56

CURRENT_TIMESTAMP()

Retorna um tipo de dados TIMESTAMP da hora 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. Valores possíveis de interval_units incluem YEAR, MONTH, DAY, HOUR, MINUTE e SECOND. Se interval 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/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 formato YYYY-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 / e - e caracteres especiais aceitos pela função strftime em C++, como %d para o dia do mês.

Use as funções UTC_USEC_TO_<function_name> se quiser agrupar dados de consulta por intervalos de tempo, como ao recuperar todos os dados de determinado mês, porque essas 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 ocorre em 19 de maio às 08:58, esta função retorna um carimbo de data/hora UNIX para 19 de maio à 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 ocorre às 08:58, esta função retorna um carimbo de data/hora UNIX para as 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 ocorre em 19 de março, esta 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 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, 2008-04-11 e você definir day_of_week para 2 (terça-feira), a função retornará um carimbo de data/hora do UNIX para terça-feira, 2008-04-08.

Exemplo:

SELECT UTC_USEC_TO_WEEK(1207929480000000, 2) AS tuesday;

Retorna: 1207612800000000

UTC_USEC_TO_YEAR(<unix_timestamp>)

Retorna um carimbo de data/hora do 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 em microssegundos de 2010-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. Nela, um carimbo de data/hora em microssegundos é aceito como 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 da Wikipédia 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 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 formulário 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 string de endereço IPv4 legível. Por exemplo, FORMAT_IP(1) retorna 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') retorna 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 ou 2620: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 JSONPath json_path. É preciso que json_path seja 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 JSONPath json_path. É preciso que json_path seja 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 Retornará true se ambas as expressões forem verdadeiras.
expr OR expr Retornará true se uma ou ambas as expressões forem verdadeiras.
NOT expr Retorna true, se a expressão é falsa.
expr AND expr
  • Retorna true, se ambas as expressões são verdadeiras.
  • Retorna false, se uma ou ambas as expressões são falsas.
  • Retorna NULL, se ambas as expressões são NULL ou se uma expressão é verdadeira, e a outra é NULL.
expr OR expr
  • Retorna true, se uma ou ambas as expressões são 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.
NOT expr
  • Retorna true, se a expressão é falsa.
  • Retorna false, se a expressão é true.
  • Retorna NULL, se a expressão é NULL.

Você pode usar NOT com outras funções como um operador de negação. Por exemplo, NOT IN(expr1, expr2) ou IS NOT NULL.

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 para 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 o parêntese para indicar que é uma função, mas não leva argumentos nesses parênteses. Você pode usar o PI() como uma constante com funções matemáticas e aritméticas.
POW(numeric_expr1, numeric_expr2)
Retorna o resultado da elevação de numeric_expr1 à potência de numeric_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,0 <= valor < 1,0. Cada valor int32_seed sempre gera a mesma sequência de números aleatórios em uma determinada consulta, desde que você não use uma cláusula LIMIT. Se int32_seed não for especificado, o carimbo de data/hora atual será usado pelo BigQuery como o valor de semente.
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). Esta consulta faz uso das funções matemáticas e trigonométricas do BigQuery, como PI(), SIN() e COS().

    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 biblioteca re2, o BigQuery se torna compatível com expressões regulares. Acesse essa documentação para consultar a sintaxe de expressão regular.

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 strings 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 reg_exp é substituída por replace_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 em SELECT. 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

    Embora as funções de expressão regular do BigQuery funcionem apenas para dados de string, é possível usar a função STRING() para converter dados flutuantes ou inteiros em formato de string. Neste exemplo, STRING() é usado para converter o valor inteiro corpus_date em uma string, que é então alterada por REGEXP_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. Você pode anexar IGNORE CASE ao final de uma consulta para ativar a correspondência que não diferencia maiúsculas de minúsculas. IGNORE CASE funciona apenas em caracteres ASCII e 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' Retornará true, se expr contiver o argumento da sequência especificada.
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 + ...
Retornará a concatenação de duas ou mais strings ou NULL, se algum dos valores for NULL. Exemplo: se str1 for Java e str2 for Script, CONCAT retornará JavaScript.
expr CONTAINS 'str'
Retornará true, se expr contiver o argumento de string especificado. Esta é uma comparação que diferencia maiúsculas de minúsculas.
INSTR('str1', 'str2')
Retorna o índice de base um da primeira ocorrência de str2 em str1 ou retorna 0, se str2 não ocorrer em str1.
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) retorna sea.
LENGTH('str')
Retorna um valor numérico para o comprimento da string. Exemplo: se str for '123456', LENGTH retorna 6.
LOWER('str')
Retorna a string original com todos os caracteres em letras minúsculas.
LPAD('str1', numeric_expr, 'str2')
Preenche str1 à esquerda com str2, repetindo str2 até a string de resultado ter exatamente numeric_expr caracteres. Exemplo: LPAD('1', 7, '?') retorna ??????1.
LTRIM('str1' [, str2])

Remove os caracteres do lado esquerdo de str1. Se str2 for omitido, LTRIM remove espaços do lado esquerdo de str1. Caso contrário, LTRIM remove todos os caracteres em str2 do lado esquerdo de str1 (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 str1 por str3.

Retorna os caracteres de numeric_expr à extrema direita de str. Se o número é maior que a sequência, retorna a sequência inteira. Exemplo: RIGHT('kirkland', 4) retorna land.
RPAD('str1', numeric_expr, 'str2')
Preenche str1 à direita com str2, repetindo str2 até que a string resultante seja exatamente numeric_expr caracteres. Exemplo: RPAD('1', 7, '?') retorna 1??????
RTRIM('str1' [, str2])

Remove os caracteres finais do lado direito de str1. Se str2 for omitido, RTRIM removerá os espaços à direita de str1. Caso contrário, RTRIM removerá todos os caracteres em str2 à direita de str1 (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ção SPLIT interrompe str em substrings, usando delimiter como delimitador.
SUBSTR('str', index [, max_len])
Retorna uma substring de str, iniciando em index. Se o parâmetro max_len opcional for usado, a string retornada terá um máximo de max_len caracteres. A contagem é iniciada em 1, então o primeiro caractere na string está na posição 1 (não zero). Se index for 5, a substring começará com o quinto caractere da esquerda em str. Se index for -4, a subsequência começará com o quarto caractere da direita em str. Exemplo: SUBSTR('awesome', -4, 4) retorna a substring some.
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.
  • Usar sequências de 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 que tenham nomes correspondentes 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 formato YYYYMMDD.

Use as funções de data e hora para gerar os parâmetros de carimbo de data/hora. 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, pressupõe-se que as tabelas a seguir estejam em um projeto denominado 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 faltar alguma tabela diária na sequência, TABLE_DATE_RANGE_STRICT falhará e retornará um erro Not Found: Table <table_name>.

Exemplo: erro na tabela ausente

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 com nomes que correspondem à expr fornecida. O parâmetro expr precisa ser representado como uma string e conter uma expressão para avaliação. Por exemplo, 'length(table_id) < 3'.

Exemplo: tabelas de correspondência cujos 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, pressupõe-se que as tabelas a seguir estejam em um projeto denominado 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: 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

Nessa consulta, a função DOMAIN() é usada 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 informações 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 colunas separadas por vírgula. Uma partição será criada para cada conjunto distinto de valores para essas colunas, semelhante a uma cláusula GROUP BY. Se PARTITION BY for omitida, a partição base será todas as linhas na entrada para a função de janela.
A cláusula PARTITION BY também permite que as funções de janela particionem dados e carreguem a execução em paralelo. Se você deseja usar uma função de janela com allowLargeResults, ou se pretende aplicar mais junções ou agregações à saída da função de janela, use PARTITION BY para carregar a execução em paralelo.
As cláusulas JOIN EACH e GROUP EACH BY não podem ser usadas ​​na saída de funções de janela. Para gerar resultados de consulta extensos com funções de janela, é preciso usar PARTITION BY.
ORDER BY
Classifica a partição. Se ORDER BY estiver ausente, não haverá garantia de uma 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 janela RANGE, será preciso adicionar uma cláusula ORDER BY. A ordem padrão é ASC.
ORDER BY é opcional em alguns casos, mas certas funções de janela como rank() ou dense_rank() precisam dessa cláusula.
Se você usar ORDER BY sem especificar ROWS ou RANGE, ORDER BY implicará que a janela se estende do início da partição até a linha atual. Na ausência de uma cláusula ORDER BY, a janela é a partição inteira.
<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 sem um window-frame-clause, o quadro de janela padrão será RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Se você omitir ORDER BY e window-frame-clause, o quadro 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 anteriores de valores de salário, você consultaria SUM(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 de mesmo nível de CURRENT ROW estão incluídas em uma moldura de janela que especifica CURRENT ROW. Por exemplo, se você especificar a extremidade da janela como CURRENT ROW e a linha seguinte 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 inicial e final. O intervalo não precisa incluir a linha atual, mas <start> precisa preceder ou igualar <end>.
  • <start> - especifica o deslocamento inicial para esta janela, em relação à linha atual. As seguintes opções são compatíveis:
    {UNBOUNDED PRECEDING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}
    onde <expr> é um número inteiro positivo, PRECEDING indica um valor de intervalo ou número de linha anterior e FOLLOWING indica um valor de intervalo ou número de linha 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 para esta janela, em relação à linha atual. As seguintes opções são compatíveis:
    {UNBOUNDED FOLLOWING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}
    onde <expr> é um número inteiro positivo, PRECEDING indica um valor de intervalo ou número de linha anterior e FOLLOWING indica um valor de intervalo ou número de linha posterior. UNBOUNDED 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 quadro 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 de agregação, 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 um comportamento semelhante ao da função agregada EXACT_COUNT_DISTINCT().

Na consulta de exemplo, a cláusula ORDER BY faz com que a janela seja calculada a partir do início da partição até 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. Ele é calculado pela fórmula <number of rows preceding or tied with the current row> / <total rows>. Valores vinculados retornam o mesmo valor de distribuição cumulativa.

Esta função de janela exige ORDER BY na cláusula OVER.

#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().

Esta função de janela exige ORDER BY na cláusula OVER.

#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 as linhas localizadas <offset> antes da linha atual. <default_value> será retornado se a linha não existir.

#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 nulo
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 as linhas localizadas <offset> após a linha atual. No caso de ausência da linha, é retornado <default_value>.

#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 quadro da janela, em que <n> é um índice baseado em um.

NTILE(<num_buckets>)

Divide uma sequência de linhas em intervalos <num_buckets> e atribui um número de intervalos correspondente a cada linha, como um número inteiro. Com a função ntile(), os números de intervalo são atribuídos da maneira mais equitativa possível, e um valor entre 1 e <num_buckets> é retornado 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.

Esta função de janela exige ORDER BY na cláusula OVER.

#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 mapearia para o argumento percentil com respeito à janela, depois de ordená-los de acordo com a cláusula ORDER BY.

<percentile> precisa estar entre 0 e 1.

Esta função de janela exige ORDER BY na cláusula OVER.

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

Esta função de janela exige ORDER BY na cláusula OVER.

#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().

Esta função de janela exige ORDER BY na cláusula OVER.

#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 no formato 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 se condition for verdadeiro para todas as suas entradas. Quando usada com a cláusula OMIT IF, essa função é útil para consultas que envolvam campos repetidos.
FROM_BASE64(<str>)
Converte a entrada str codificada como base64 para o formato BYTES. Para converter BYTES em uma string codificada em base64, use 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 sequência ou expressão de número inteiro é compatível, e a função respeita IGNORE CASE para sequências, retornando valores invariantes de caso.
FARM_FINGERPRINT(expr)
Calcula e retorna um valor de impressão digital assinado de 64 bits da entrada STRING ou BYTES usando a função Fingerprint64 da biblioteca de código aberto do FarmHash. A saída dessa função para uma entrada particular nunca mudará e combina a saída da função FARM_FINGERPRINT ao usar o SQL padrão. Respeita IGNORE CASE para strings, retornando valores invariantes de casos.
IF(condition, true_return, false_return)
Retorna true_return ou false_return, dependendo se condition é verdadeira ou falsa. Os valores retornados podem ser literais ou valores derivados de campo, mas devem ter o mesmo tipo de dados. Os valores derivados de campo não precisam ser incluídos na cláusula SELECT.
POSITION(field)
Retorna a posição sequencial baseada em 1 de campo dentro de um conjunto de campos repetidos.
SHA1(<str>)
Retorna um hash SHA1 no formato BYTES desde a string de entrada str. Você pode converter o resultado para base64 usando TO_BASE64(). Exemplo:
#legacySQL
SELECT
  TO_BASE64(SHA1(corpus))
FROM
  [bigquery-public-data:samples.shakespeare]
LIMIT
  100;
SOME(<condition>)
Retornará true se condition for verdadeiro para pelo menos uma das entradas. Quando usada com a cláusula OMIT IF, essa função é útil para consultas que envolvam campos repetidos.
TO_BASE64(<bin_data>)
Converte a entrada BYTES bin_data 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 sequência codificada em base64 em BYTES, use FROM_BASE64().

Exemplos avançados

  • Organização de resultados em categorias usando condicionais

    A consulta a seguir usa um bloco CASE/WHEN para organizar resultados em categorias "região" com base em uma lista de estados. Se o estado não aparecer como uma opção em uma das instruções WHEN, o valor do estado será predefinido para "None".

    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 localizará o HASH() do valor "title", depois verificará se esse módulo de valor "2" é zero. Isso deve resultar em cerca de 50% dos valores sendo rotulados como "amostrados". Para obter menos valores, aumente o valor da operação do módulo de "2" para algo maior. Na consulta, a função ABS é usada em combinação com HASH porque valores negativos podem ser retornados por HASH, 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;
    
Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.