Sintaxe, funções e operadores do SQL legado

Neste documento, detalhamos a sintaxe, as funções e os operadores de consultas do SQL legado. A sintaxe de consulta de preferência para o BigQuery é o GoogleSQL. Para mais informações sobre o GoogleSQL, consulte Sintaxe de consulta do GoogleSQL.

Sintaxe das consultas

Observação: não há diferenciação entre letras maiúsculas e minúsculas nas palavras-chave. Neste documento, palavras-chave como SELECT estão em letras maiúsculas para fins ilustrativos.

Cláusula SELECT

A cláusula SELECT especifica uma lista de expressões a serem calculadas. As expressões na cláusula SELECT podem conter nomes de campos, literais e chamadas de função, incluindo funções de agregação e de janela, e também combinações desses três itens. A lista de expressões é separada por vírgulas.

Cada expressão pode receber um alias por meio da adição de um espaço seguido de um identificador após a expressão. A palavra-chave opcional AS pode ser adicionada entre a expressão e o alias para melhorar a legibilidade. Alias definidos em uma cláusula SELECT podem ser referenciados nas cláusulas GROUP BY, HAVING e ORDER BY da consulta, mas não nas FROM, WHERE ou OMIT RECORD IF nem por outras expressões na mesma cláusula SELECT.

Observações:

• Se você usar uma função agregada na cláusula SELECT, precisará usá-la em todas as expressões ou sua consulta precisa ter uma cláusula GROUP BY que inclua todos os campos não agregados na sua 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. */
  

• Utilize colchetes para escapar palavras reservadas e usá-las como nome de campo e alias. Por exemplo, se você tiver uma coluna chamada "partition", que é uma palavra reservada na sintaxe do BigQuery, as consultas que fizerem referência a esse campo apresentarão falhas com mensagens de erro obscuras, a menos que você as escape com colchetes:

  SELECT [partition] FROM ...

Exemplo

Neste exemplo, definimos aliases na cláusula SELECT e, em seguida, referenciamos a um deles na cláusula ORDER BY. Não é possível mencionar a coluna word usando word_alias na cláusula WHERE. A referência tem que ser feita pelo nome. O alias len também não fica visível na cláusula WHERE. Ele será visível para uma cláusula HAVING.

#legacySQL
SELECT
  word AS word_alias,
  LENGTH(word) AS len
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  word CONTAINS 'th'
ORDER BY
  len;

Modificador WITHIN para funções de agregação

aggregate_function WITHIN RECORD [ [ AS ] alias ]

A palavra-chave WITHIN faz com que a função de agregação reúna valores repetidos dentro de cada registro. Para cada registro de entrada, será produzida exatamente uma saída agregada. Esse tipo de agregação é denominado agregação em escopo. Como a agregação com escopo produz uma saída para cada registro, as expressões não agregadas podem ser selecionadas ao lado de expressões com escopo agregado sem o uso de uma cláusula GROUP BY.

É mais comum você utilizar o escopo RECORD ao usar agregação em escopo. Se você tem um esquema muito complexo aninhado e repetido, pode precisar executar agregações dentro de escopos de sub-registro. Isso pode ser feito pela substituição da palavra-chave RECORD na sintaxe acima pelo nome do node no esquema em que você quiser que a agregação seja realizada. Para mais informações sobre esse comportamento avançado, consulte Como lidar com dados.

Exemplo

Este exemplo executa uma agregação COUNT em escopo e, em seguida, filtra e classifica os registros pelo valor agregado.

#legacySQL
SELECT
  repository.url,
  COUNT(payload.pages.page_name) WITHIN RECORD AS page_count
FROM
  [bigquery-public-data:samples.github_nested]
HAVING
  page_count > 80
ORDER BY
  page_count DESC;

Cláusula FROM

FROM
  [project_name:]datasetId.tableId [ [ AS ] alias ] |
  (subquery) [ [ AS ] alias ] |
  JOIN clause |
  FLATTEN clause |
  table wildcard function

A cláusula FROM especifica os dados de origem a serem consultados. As consultas do BigQuery podem ser executadas diretamente sobre tabelas, subconsultas, tabelas conjuntas e tabelas modificadas por operadores especiais descritos abaixo. As combinações destas origens de dados podem ser consultadas usando a vírgula, que é o operador UNION ALL no BigQuery.

Tabelas de referência

Ao fazer referência a uma tabela, datasetId e datasetId precisam ser especificados. datasetId é opcional. Se o project_name não for especificado, o BigQuery usará o projeto atual como padrão. Se o nome do projeto incluir um traço, será preciso colocar a referência da tabela entre colchetes.

Exemplo

[my-dashed-project:dataset1.tableName]

As tabelas podem receber um alias por meio da adição de um espaço seguido de um identificador após o nome da tabela. É possível adicionar a palavra-chave opcional AS entre o tableId e o alias para melhorar a leitura.

Ao fazer referência a colunas de uma tabela, você pode usar o nome de coluna simples ou prefixar o nome da coluna com o alias, se houver algum especificado, ou com o datasetId e datasetId, desde que não haja nenhum datasetId especificado. O project_name não pode ser incluído no prefixo da coluna porque o caractere de dois pontos não é permitido em nomes de campo.

Exemplos

Este exemplo faz referência a uma coluna sem prefixo de tabela.

#legacySQL
SELECT
  word
FROM
  [bigquery-public-data:samples.shakespeare];

Este exemplo tem datasetId e datasetId como prefixo do nome da coluna. Observe que o project_name não pode ser incluído aqui. Esse método só funcionará se o conjunto de dados estiver no projeto padrão atual.

#legacySQL
SELECT
  samples.shakespeare.word
FROM
  samples.shakespeare;

Este exemplo tem um alias de tabela como prefixo do nome da coluna.

#legacySQL
SELECT
  t.word
FROM
  [bigquery-public-data:samples.shakespeare] AS t;

Tabelas particionadas por intervalo de números inteiros

No entanto, o SQL legado é compatível com o uso de decoradores de tabela para lidar com uma partição específica em uma tabela particionada de intervalo de números inteiros. A chave para lidar com uma variação de partição é o início da variação.

O exemplo a seguir investiga a partição de variação que começa com 30.

#legacySQL
SELECT
  *
FROM
  dataset.table$30;

Não é possível usar SQL legado para consultar uma tabela particionada por intervalo de números inteiros inteira. Em vez disso, a consulta retorna um erro como este:

Querying tables partitioned on a field is not supported in Legacy SQL

Uso de subconsultas

Uma subconsulta é uma instrução SELECT aninhada entre parênteses. As expressões calculadas na cláusula SELECT da subconsulta estão disponíveis para consulta externa, assim como as colunas de uma tabela.

As subconsultas podem ser usadas para calcular agregações e outras expressões. A gama completa de operadores SQL está disponível na subconsulta. Isso significa que uma subconsulta pode conter outras, as subconsultas podem realizar associações e agrupar agregações, etc.

Vírgula como UNION ALL

Ao contrário do GoogleSQL, o SQL legado usa a vírgula como um operador UNION ALL. em vez de um operador CROSS JOIN. Esse é um comportamento legado que evoluiu porque, historicamente, o BigQuery não era compatível com CROSS JOIN e usuários do BigQuery precisavam escrever consultas UNION ALL. No GoogleSQL, as consultas que executam uniões são particularmente detalhadas. Usar a vírgula como operador de união permite que essas consultas sejam escritas de maneira muito mais eficiente. Por exemplo, esta consulta pode ser usada para executar uma única consulta em registros de vários dias.

#legacySQL
SELECT
  FORMAT_UTC_USEC(event.timestamp_in_usec) AS time,
  request_url
FROM
  [applogs.events_20120501],
  [applogs.events_20120502],
  [applogs.events_20120503]
WHERE
  event.username = 'root' AND
  NOT event.source_ip.is_internal;

Consultas que unem um grande número de tabelas normalmente são executadas de maneira mais lenta que aquelas que processam a mesma quantidade de dados a partir de uma única tabela. A diferença de desempenho pode ser de até 50 ms por tabela adicional. Uma única consulta pode unir no máximo 1.000 tabelas.

Funções de caractere curinga de tabela

O termo função de caractere curinga de tabela refere-se a um tipo especial de função exclusiva para o BigQuery. Essas funções são usadas na cláusula FROM para corresponder a uma coleção de nomes de tabela usando um dos vários tipos de filtros. Por exemplo, a função TABLE_DATE_RANGE pode ser utilizada para consultar apenas um conjunto específico de tabelas diárias. Para mais informações sobre essas funções, consulte Funções de caractere curinga de tabela.

Operador FLATTEN

(FLATTEN([project_name:]datasetId.tableId, field_to_be_flattened))
(FLATTEN((subquery), field_to_be_flattened))

Ao contrário dos sistemas típicos de processamento de SQL, o BigQuery foi projetado para lidar com dados repetidos. Por conta disso, os usuários do BigQuery às vezes precisam escrever consultas que manipulem a estrutura de registros repetidos. Uma maneira de fazer isso é usando o operador FLATTEN.

FLATTEN converte um nó no esquema de repetido para opcional. Dado um registro com um ou mais valores para um campo repetido, FLATTEN criará vários registros, um para cada valor no campo repetido. Todos os outros campos selecionados a partir do registro são duplicados em cada novo registro de saída. FLATTEN pode ser aplicado repetidamente para remover vários níveis de repetição.

Para mais informações e exemplos, consulte Como lidar com dados.

Operador JOIN

O BigQuery é compatível com vários operadores JOIN em cada cláusula FROM. As operações JOIN subsequentes usam os resultados da operação JOIN anterior como a entrada JOIN esquerda. Os campos de qualquer entrada JOIN anterior podem ser usados como chaves nas cláusulas ON dos operadores JOIN subsequentes.

Tipos de JOIN

O BigQuery é compatível com operações INNER, [FULL|RIGHT|LEFT] OUTER e CROSS JOIN. Se nada for especificado, o padrão será INNER.

Operações CROSS JOIN não permitem cláusulas ON. CROSS JOIN pode retornar uma grande quantidade de dados e pode resultar em uma consulta lenta e ineficiente ou em uma consulta que excede o máximo permitido de recursos por consulta. Essas consultas apresentarão falha com um erro. Quando possível, prefira consultas que não usam CROSS JOIN. Por exemplo, CROSS JOIN é frequentemente usado em locais em que funções de janela seriam mais eficientes.

Modificador EACH

O modificador EACH é uma dica que indica ao BigQuery que ele execute JOIN usando várias partições. Isso é particularmente útil quando você sabe que ambos os lados do JOIN são grandes. O modificador EACH não pode ser usado em cláusulas CROSS JOIN.

EACH costumava ser incentivado em muitas ocasiões, mas não é mais o caso. Quando possível, use JOIN sem o modificador EACH para melhor desempenho. Use JOIN EACH quando sua consulta falhar com uma mensagem de erro de recursos excedidos.

Semi-join e Anti-join

Além de ser compatível com JOIN na cláusula FROM, o BigQuery também é compatível com dois tipos de junções na cláusula WHERE: semi-join e anti-semi-join. Uma semi-join é especificada usando a palavra-chave IN com uma subconsulta. Já anti-join, usando as palavras-chave NOT IN.

Exemplos

A consulta a seguir usa uma semi-join para encontrar ngrams em que a primeira palavra no ngram é também a segunda palavra em outro que tem "AND" como a terceira.

#legacySQL
SELECT
  ngram
FROM
  [bigquery-public-data:samples.trigrams]
WHERE
  first IN (SELECT
              second
            FROM
              [bigquery-public-data:samples.trigrams]
            WHERE
              third = "AND")
LIMIT 10;

A consulta a seguir usa uma semi-join para retornar o número de mulheres com mais de 50 anos que deram à luz nos dez estados com os maiores índices de nascimento.

#legacySQL
SELECT
  mother_age,
  COUNT(mother_age) total
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state IN (SELECT
              state
            FROM
              (SELECT
                 state,
                 COUNT(state) total
               FROM
                 [bigquery-public-data:samples.natality]
               GROUP BY
                 state
               ORDER BY
                 total DESC
               LIMIT 10))
  AND mother_age > 50
GROUP BY
  mother_age
ORDER BY
  mother_age DESC

Para ver os números dos outros 40 estados, use uma anti-join. A consulta a seguir é quase idêntica ao exemplo anterior, mas usa NOT IN em vez de IN para retornar o número de mulheres com mais de 50 anos que deram à luz nos 40 estados com o menor número de nascimentos.

#legacySQL
SELECT
  mother_age,
  COUNT(mother_age) total
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state NOT IN (SELECT
                  state
                FROM
                  (SELECT
                     state,
                     COUNT(state) total
                   FROM
                     [bigquery-public-data:samples.natality]
                   GROUP BY
                     state
                   ORDER BY
                     total DESC
                   LIMIT 10))
  AND mother_age > 50
GROUP BY
  mother_age
ORDER BY
  mother_age DESC

Observações:

• O BigQuery não é compatível com semi-joins ou anti-semi-joins correlacionadas. A subconsulta não pode fazer referência a nenhum campo da consulta externa.
• A subconsulta usada em uma semi-join ou anti-semi-join precisa selecionar exatamente um campo.
• Os tipos do campo selecionado e o campo que está sendo usado da consulta externa na cláusula WHERE precisam corresponder exatamente. O BigQuery não fará nenhum tipo de coerção para semi-joins ou anti-semi-joins.

Cláusula WHERE

A cláusula WHERE, às vezes chamada de predicado, filtra registros produzidos pela cláusula FROM usando uma expressão booleana. Múltiplas condições podem ser juntadas por cláusulas booleanas AND e OR, opcionalmente delimitadas por um parênteses — () — que as agrupa. Os campos listados em uma cláusula WHERE não precisam ser selecionados na cláusula SELECT correspondente, e a expressão da WHERE não pode referenciar expressões calculadas na SELECT da consulta à qual a WHERE pertence.

Observação: não é possível usar as funções de agregação na cláusula WHERE. Use uma cláusula HAVING e uma consulta externa se for preciso filtrar a saída de uma função de agregação.

Exemplo

O exemplo a seguir usa uma disjunção de expressões booleanas na cláusula WHERE, as duas expressões unidas por um operador OR. Um registro de entrada passará pelo filtro WHERE se qualquer uma das expressões retornar true.

#legacySQL
SELECT
  word
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  (word CONTAINS 'prais' AND word CONTAINS 'ing') OR
  (word CONTAINS 'laugh' AND word CONTAINS 'ed');

Cláusula OMIT RECORD IF

A cláusula OMIT RECORD IF é uma construção exclusiva do BigQuery. É particularmente útil para lidar com esquemas repetidos aninhados. É semelhante a uma cláusula WHERE , mas diferente em dois aspectos importantes. Primeiro, ele usa uma condição de exclusão, que significa que os registros são omitidos se a expressão retornar true, mas mantidos se a expressão retornar false ou null. Segundo, a cláusula OMIT RECORD IF pode usar (e geralmente usa) funções de agregação com escopo na condição.

Além de filtrar os registros completos, OMIT...IF pode especificar um escopo mais restrito para filtrar apenas partes de um registro. Isso é feito usando o nome de um nó que não seja uma folha no seu esquema, em vez de RECORD na sua cláusula OMIT...IF. Essa funcionalidade raramente é utilizada pelos usuários do BigQuery. Você encontra mais informações sobre esse comportamento avançado em um link na documentação de WITHIN acima.

Se você usa OMIT...IF para excluir uma parte de um registro em um campo de repetição e a consulta também seleciona outros campos de repetição independentes, o BigQuery omite uma parte dos outros registros repetidos na consulta. Se você vir o erro Cannot perform OMIT IF on repeated scope <scope> with independently repeating pass through field <field>, recomendamos mudar para o GoogleSQL. Para mais informações sobre a migração das intruções OMIT...IF para o GoogleSQL, consulte Migração para o GoogleSQL.

Exemplo

Referindo-se ao exemplo usado para o modificador WITHIN, OMIT RECORD IF pode ser usado para realizar a mesma tarefa que WITHIN e HAVING foram usadas para fazer nesse exemplo.

#legacySQL
SELECT
  repository.url
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF
  COUNT(payload.pages.page_name) <= 80;

Cláusula GROUP BY

A cláusula GROUP BY permite agrupar linhas que tenham os mesmos valores para um determinado campo ou conjunto de campos, para que você possa calcular agregações de campos relacionados. O agrupamento ocorre após a filtragem realizada na cláusula WHERE, mas antes que as expressões na cláusula SELECT sejam calculadas. Os resultados da expressão não podem ser usados como chaves de grupo na cláusula GROUP BY.

Exemplo

Esta consulta localiza as dez primeiras palavras mais comuns no conjunto de dados de amostra de trigramas. Além de demonstrar o uso da cláusula GROUP BY, ela demonstra como índices posicionais podem ser usados em vez de nomes de campos nas cláusulas GROUP BY e ORDER BY.

#legacySQL
SELECT
  first,
  COUNT(ngram)
FROM
  [bigquery-public-data:samples.trigrams]
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT 10;

A agregação realizada usando uma cláusula GROUP BY é chamada agregação agrupada . Ao contrário da agregação em escopo, a agregação agrupada é comum na maioria dos sistemas de processamento SQL.

O modificador EACH

O modificador EACH é uma dica que indica ao BigQuery que ele execute GROUP BY usando várias partições. Ele é particularmente útil quando você sabe que o conjunto de dados contém um grande número de valores distintos para as chaves de grupo.

EACH costumava ser incentivado em muitas ocasiões, mas não é mais o caso. Usar GROUP BY sem o modificador EACH normalmente proporciona melhor desempenho. Use GROUP EACH BY quando sua consulta falhar com uma mensagem de erro de recursos excedidos.

A função ROLLUP

Quando a função ROLLUP é usada, linhas extras que representam agregações acumuladas são adicionadas pelo BigQuery ao final da consulta. Todos os campos listados após ROLLUP precisam ser incluídos em um único conjunto de parênteses. Nas linhas adicionadas por causa da função ROLLUP, NULL indica as colunas para as quais a agregação é acumulada.

Exemplo

Esta consulta gera contagens por ano de nascimentos masculinos e femininos a partir do conjunto de dados de natalidade da amostra.

#legacySQL
SELECT
  year,
  is_male,
  COUNT(1) as count
FROM
  [bigquery-public-data:samples.natality]
WHERE
  year >= 2000
  AND year <= 2002
GROUP BY
  ROLLUP(year, is_male)
ORDER BY
  year,
  is_male;

Estes são os resultados da consulta. Observe que há linhas em que uma ou ambas as chaves do grupo são NULL. Essas linhas são as linhas acumuladas.

+------+---------+----------+
| year | is_male |  count   |
+------+---------+----------+
| NULL |    NULL | 12122730 |
| 2000 |    NULL |  4063823 |
| 2000 |   false |  1984255 |
| 2000 |    true |  2079568 |
| 2001 |    NULL |  4031531 |
| 2001 |   false |  1970770 |
| 2001 |    true |  2060761 |
| 2002 |    NULL |  4027376 |
| 2002 |   false |  1966519 |
| 2002 |    true |  2060857 |
+------+---------+----------+

Ao usar a função ROLLUP, é possível usar GROUPING para distinguir entre linhas que foram adicionadas por causa de ROLLUP e linhas que realmente têm um valor NULL para a chave de grupo.

Exemplo

Esta consulta adiciona a função GROUPING ao exemplo anterior para identificar melhor as linhas adicionadas por conta de ROLLUP.

#legacySQL
SELECT
  year,
  GROUPING(year) as rollup_year,
  is_male,
  GROUPING(is_male) as rollup_gender,
  COUNT(1) as count
FROM
  [bigquery-public-data:samples.natality]
WHERE
  year >= 2000
  AND year <= 2002
GROUP BY
  ROLLUP(year, is_male)
ORDER BY
  year,
  is_male;

Estes são o resultado que a nova consulta retorna.

+------+-------------+---------+---------------+----------+
| year | rollup_year | is_male | rollup_gender |  count   |
+------+-------------+---------+---------------+----------+
| NULL |           1 |    NULL |             1 | 12122730 |
| 2000 |           0 |    NULL |             1 |  4063823 |
| 2000 |           0 |   false |             0 |  1984255 |
| 2000 |           0 |    true |             0 |  2079568 |
| 2001 |           0 |    NULL |             1 |  4031531 |
| 2001 |           0 |   false |             0 |  1970770 |
| 2001 |           0 |    true |             0 |  2060761 |
| 2002 |           0 |    NULL |             1 |  4027376 |
| 2002 |           0 |   false |             0 |  1966519 |
| 2002 |           0 |    true |             0 |  2060857 |
+------+-------------+---------+---------------+----------+

Observações:

• Os campos não agregados na cláusula SELECT precisam ser listados na clá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. */
  

• 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, porque a função de igualdade desses tipos não é bem definida.
• Como o sistema é interativo, consultas que produzem um grande número de grupos podem apresentar falha. O uso da função TOP em vez de GROUP BY pode resolver alguns problemas de escalonamento.

Cláusula HAVING

A cláusula HAVING se comporta exatamente como a cláusula WHERE, exceto que ela é avaliada após a cláusula SELECT para que os resultados de todas as expressões calculadas fiquem visíveis para a cláusula HAVING. A cláusula HAVING só pode se referir às saídas da SELECT correspondente.

Exemplo

Esta consulta calcula as primeiras palavras mais comuns no conjunto de dados de exemplo ngram que contêm a letra "a" e ocorrem no máximo 10.000 vezes.

#legacySQL
SELECT
  first,
  COUNT(ngram) ngram_count
FROM
  [bigquery-public-data:samples.trigrams]
GROUP BY
  1
HAVING
  first contains "a"
  AND ngram_count < 10000
ORDER BY
  2 DESC
LIMIT 10;

Cláusula ORDER BY

A cláusula ORDER BY classifica os resultados de uma consulta em ordem crescente ou decrescente usando um ou mais campos-chave. Para classificar por múltiplos campos ou aliases, insira-os como uma lista separada por vírgulas. Os resultados são classificados nos campos na ordem em que estão listados. Use os valores DESC (decrescente) ou ASC (crescente) para especificar a direção de classificação. O padrão é ASC. Uma direção de classificação diferente pode ser especificada para cada chave de classificação.

A cláusula ORDER BY é avaliada após a cláusula SELECT para que possa referenciar a saída de qualquer expressão calculada em SELECT. Se um campo receber um alias na cláusula SELECT, o alias precisará ser usado na cláusula ORDER BY.

Cláusula LIMIT

A cláusula LIMIT limita o número de linhas no conjunto de resultados retornado. Como as consultas do BigQuery operam regularmente em um número muito grande de linhas, LIMIT é uma boa maneira de evitar consultas de longa execução por meio do processamento de apenas um subconjunto das linhas.

Observações:

• A cláusula LIMIT interromperá o processamento e retornará os resultados quando atender aos requisitos. Isso pode reduzir o tempo de processamento de algumas consultas, mas quando você especifica funções de agregação, como cláusulas COUNT ou ORDER BY, todo o conjunto de resultados ainda precisa ser processado antes de retornar 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

Os detalhes de cláusulas individuais das instruções SELECT do BigQuery estão descritos acima. Nesta parte, apresentamos a gramática completa de instruções SELECT em um formato compacto e com links para as seções individuais.

query:
    SELECT { * | field_path.* | expression } [ [ AS ] alias ] [ , ... ]
    [ FROM from_body
      [ WHERE bool_expression ]
      [ OMIT RECORD IF bool_expression]
      [ GROUP [ EACH ] BY [ ROLLUP ] { field_name_or_alias } [ , ... ] ]
      [ HAVING bool_expression ]
      [ ORDER BY field_name_or_alias [ { DESC | ASC } ] [, ... ] ]
      [ LIMIT n ]
    ];

from_body:
    {
      from_item [, ...] |  # Warning: Comma means UNION ALL here
      from_item [ join_type ] JOIN [ EACH ] from_item [ ON join_predicate ] |
      (FLATTEN({ table_name | (query) }, field_name_or_alias)) |
      table_wildcard_function
    }

from_item:
    { table_name | (query) } [ [ AS ] alias ]

join_type:
    { INNER | [ FULL ] [ OUTER ] | RIGHT [ OUTER ] | LEFT [ OUTER ] | CROSS }

join_predicate:
    field_from_one_side_of_the_join = field_from_the_other_side_of_the_join [ AND ...]

expression:
    {
      literal_value |
      field_name_or_alias |
      function_call
    }

bool_expression:
    {
      expression_which_results_in_a_boolean_value |
      bool_expression AND bool_expression |
      bool_expression OR bool_expression |
      NOT bool_expression
    }

Notação:

• Colchetes "[ ]" indicam cláusulas opcionais.
• Chaves "{ }" delimitam um conjunto de opções.
• Barra vertical "|" indica um OR lógico.
• Uma vírgula ou palavra-chave seguida por reticências entre colchetes "[, ... ]" indica que o item anterior pode se repetir em uma lista com o separador especificado.
• Parênteses "( )" indicam parênteses literais.

Funções e operadores compatíveis

A maioria das cláusulas de instrução SELECT é compatível com funções. Os campos referenciados em uma função não precisam ser listados em nenhuma cláusula SELECT. Portanto, a consulta a seguir é válida, mesmo que o campo clicks não seja exibido diretamente:

#legacySQL
SELECT country, SUM(clicks) FROM table GROUP BY country;

Funções de agregação
AVG()	Retorna a média dos valores para um grupo de linhas...
BIT_AND()	Retorna o resultado de uma operação AND bit a bit...
BIT_OR()	Retorna o resultado de uma operação OR bit a bit...
BIT_XOR()	Retorna o resultado de uma operação XOR bit a bit...
CORR()	Retorna o coeficiente de correlação de Pearson de um conjunto de pares de números.
COUNT()	Retorna o número total de valores...
COUNT([DISTINCT])	Retorna o número total de valores não NULL...
COVAR_POP()	Calcula a covariância de população dos valores...
COVAR_SAMP()	Calcula a covariância de exemplo dos valores...
EXACT_COUNT_DISTINCT()	Retorna o número exato de valores não NULL distintos para o campo especificado.
FIRST()	Retorna o primeiro valor sequencial no escopo da função.
GROUP_CONCAT()	Concatena strings múltiplas em uma única string...
GROUP_CONCAT_UNQUOTED()	Concatena strings múltiplas em uma única string... não adicionará aspas duplas...
LAST()	Retorna o último valor sequencial...
MAX()	Retorna o valor máximo...
MIN()	Retorna o valor mínimo...
NEST()	Agrupa todos os valores no escopo de agregação atual em um campo repetido.
NTH()	Retorna o enésimo valor sequencial...
QUANTILES()	Calcula aproximado mínimo, máximo e quantis...
STDDEV()	Retorna o desvio padrão...
STDDEV_POP()	Calcula o desvio padrão da população...
STDDEV_SAMP()	Calcula o desvio padrão da amostra...
SUM()	Retorna a soma total dos valores...
TOP() ... COUNT(*)	Retorna os principais registros max_records por frequência.
UNIQUE()	Retorna o conjunto de valores não NULL exclusivos...
VARIANCE()	Calcula a variância dos valores...
VAR_POP()	Calcula a variância de população dos valores...
VAR_SAMP()	Calcula a variância de amostra dos valores...

Operadores aritméticos
+	Adição
-	Subtração
*	Multiplicação
/	Divisão
%	Modulo

Funções bit a bit
&	AND bit a bit
|	OR bit a bit
^	XOR bit a bit
<<	Desloca para a esquerda bit a bit
>>	Desloca para a direita bit a bit
~	NOT bit a bit
BIT_COUNT()	Retorna o número de bits...

Funções de conversão
BOOLEAN()	Converte em booleano.
BYTES()	Converte em bytes.
CAST(expr AS type)	Converte expr em uma variável do tipo type.
FLOAT()	Converte em duplo.
HEX_STRING()	Converte em string hexadecimal.
INTEGER()	Converte em número inteiro.
STRING()	Converte em string.

Funções de comparação
expr1 = expr2	Retorna true se as expressões forem iguais.
expr1 != expr2 expr1 <> expr2	Retorna true se as expressões não forem iguais.
expr1 > expr2	Retorna true se expr1 for maior que expr2.
expr1 < expr2	Retorna true se expr1 for menor que expr2.
expr1 >= expr2	Retorna true se expr1 for maior ou igual a expr2.
expr1 <= expr2	Retorna true se expr1 for menor ou igual a expr2.
expr1 BETWEEN expr2 AND expr3	Retorna true se o valor de expr1 for entre expr2 e expr3, inclusive.
expr IS NULL	Retorna true se expr for NULL.
expr IN()	Retorna true se expr corresponder a expr1, expr2 ou qualquer valor entre parênteses.
COALESCE()	Retorna o primeiro argumento que não é NULL.
GREATEST()	Retorna o maior parâmetro numeric_expr.
IFNULL()	Se o argumento não for NULL, retornará o argumento.
IS_INF()	Retorna true se o infinito for positivo ou negativo.
IS_NAN()	Retorna true se o argumento for NaN.
IS_EXPLICITLY_DEFINED()	obsoleto: use expr IS NOT NULL.
LEAST()	Retorna o menor parâmetro numeric_expr do argumento.
NVL()	Se expr não for nulo, retornará expr. Caso contrário, retornará null_default.

Funções de data e hora
CURRENT_DATE()	Retorna a data atual no formato %Y-%m-%d.
CURRENT_TIME()	Retorna o horário atual do servidor no formato %H:%M:%S.
CURRENT_TIMESTAMP()	Retorna o horário atual do servidor no formato %Y-%m-%d %H:%M:%S.
DATE()	Retorna a data no formato %Y-%m-%d.
DATE_ADD()	Adiciona o intervalo especificado a um tipo de dados TIMESTAMP.
DATEDIFF()	Retorna o número de dias entre dois tipos de dados TIMESTAMP.
DAY()	Retorna o dia do mês como um número inteiro entre 1 e 31.
DAYOFWEEK()	Retorna o dia da semana como um número inteiro entre 1 (domingo) e 7 (sábado).
DAYOFYEAR()	Retorna o dia do ano como um número inteiro entre 1 e 366.
FORMAT_UTC_USEC()	Retorna um carimbo de data/hora UNIX no formato YYYY-MM-DD HH:MM:SS.uuuuuu.
HOUR()	Retorna a hora de um TIMESTAMP como um número inteiro entre 0 e 23.
MINUTE()	Retorna os minutos de um TIMESTAMP como um número inteiro entre 0 e 59.
MONTH()	Retorna o mês de um TIMESTAMP como um número inteiro entre 1 e 12.
MSEC_TO_TIMESTAMP()	Converte um carimbo de data/hora UNIX em milissegundos em um TIMESTAMP.
NOW()	Retorna o carimbo de data/hora UNIX atual em microssegundos.
PARSE_UTC_USEC()	Converte uma string de data em um carimbo de data/hora UNIX em microssegundos.
QUARTER()	Retorna o trimestre do ano de um TIMESTAMP como um número inteiro entre 1 e 4.
SEC_TO_TIMESTAMP()	Converte um carimbo de data/hora UNIX em segundos em um TIMESTAMP.
SECOND()	Retorna os segundos de um TIMESTAMP como um número inteiro entre 0 e 59.
STRFTIME_UTC_USEC()	Retorna uma string de data no formato date_format_str.
TIME()	Retorna um TIMESTAMP no formato %H:%M:%S.
TIMESTAMP()	Converte uma string de data em um TIMESTAMP.
TIMESTAMP_TO_MSEC()	Converte um TIMESTAMP em um carimbo de data/hora UNIX em milissegundos.
TIMESTAMP_TO_SEC()	Converte um TIMESTAMP em um carimbo de data/hora UNIX em segundos.
TIMESTAMP_TO_USEC()	Converte um TIMESTAMP em um carimbo de data/hora UNIX em microssegundos.
USEC_TO_TIMESTAMP()	Converte um carimbo de data/hora UNIX em microssegundos em um TIMESTAMP.
UTC_USEC_TO_DAY()	Desloca um carimbo de data/hora UNIX em microssegundos para o início do dia em que ele ocorre.
UTC_USEC_TO_HOUR()	Desloca um carimbo de data/hora UNIX em microssegundos para o início da hora em que ele ocorre.
UTC_USEC_TO_MONTH()	Desloca um carimbo de data/hora UNIX em microssegundos para o início do mês em que ele ocorre.
UTC_USEC_TO_WEEK()	Retorna um carimbo de data/hora UNIX em microssegundos que representa um dia na semana.
UTC_USEC_TO_YEAR()	Retorna um carimbo de data/hora UNIX em microssegundos que representa o ano.
WEEK()	Retorna a semana de um TIMESTAMP como um número inteiro entre 1 e 53.
YEAR()	Retorna o ano de um TIMESTAMP.

Funções IP
FORMAT_IP()	Converte 32 bits menos significativos de integer_value em uma string de endereço IPv4 legível.
PARSE_IP()	Converte uma string representando o endereço IPv4 em um valor inteiro sem sinal.
FORMAT_PACKED_IP()	Retorna um endereço IP legível no formato 10.1.5.23 ou 2620:0:1009:1:216:36ff:feef:3f.
PARSE_PACKED_IP()	Retorna um endereço IP em BYTES.

Funções JSON
JSON_EXTRACT()	Seleciona um valor de acordo com a expressão JSONPath e retorna uma string JSON.
JSON_EXTRACT_SCALAR()	Seleciona um valor de acordo com a expressão JSONPath e retorna um escalar JSON.

Operadores lógicos
expr AND expr	Retorna true se ambas as expressões forem verdadeiras.
expr OR expr	Retorna true se uma ou as duas expressões forem verdadeiras.
NOT expr	Retorna true se a expressão for falsa.

Funções matemáticas
ABS()	Retorna o valor absoluto do argumento.
ACOS()	Retorna o arco cosseno do argumento.
ACOSH()	Retorna o arco cosseno hiperbólico do argumento.
ASIN()	Retorna o arco seno do argumento.
ASINH()	Retorna o arco seno hiperbólico do argumento.
ATAN()	Retorna o arco tangente do argumento.
ATANH()	Retorna o arco tangente hiperbólico do argumento.
ATAN2()	Retorna o arco tangente dos dois argumentos.
CEIL()	Arredonda o argumento para cima até o número inteiro mais próximo e retorna o valor arredondado.
COS()	Retorna o cosseno do argumento.
COSH()	Retorna o cosseno hiperbólico do argumento.
DEGREES()	Converte de radianos em graus.
EXP()	Retorna e à potência do argumento.
FLOOR()	Arredonda o argumento para baixo para o número inteiro mais próximo.
LN() LOG()	Retorna o logaritmo natural do argumento.
LOG2()	Retorna o logaritmo de Base 2 do argumento.
LOG10()	Retorna o logaritmo de Base 10 do argumento.
PI()	Retorna a constante π.
POW()	Retorna o primeiro argumento à potência do segundo argumento.
RADIANS()	Converte de graus em radianos.
RAND()	Retorna um valor flutuante aleatório no intervalo 0,0 <= valor < 1,0.
ROUND()	Arredonda o argumento para cima ou para baixo para o número inteiro mais próximo.
SIN()	Retorna o seno do argumento.
SINH()	Retorna o seno hiperbólico do argumento.
SQRT()	Retorna a raiz quadrada da expressão.
TAN()	Retorna a tangente do argumento.
TANH()	Retorna a tangente hiperbólica do argumento.

Funções de expressão regular
REGEXP_MATCH()	Retornará true se o argumento corresponder à expressão regular.
REGEXP_EXTRACT()	Retorna a parte do argumento que corresponde ao grupo de captura dentro da expressão regular.
REGEXP_REPLACE()	Substitui uma substring que corresponde a uma expressão regular.

Funções de string
CONCAT()	Retornará a concatenação de duas ou mais strings, ou NULL se qualquer um dos valores for NULL.
expr CONTAINS 'str'	Retorna true se expr contém o argumento de string especificado.
INSTR()	Retorna o índice baseado em 1 da primeira ocorrência de uma string.
LEFT()	Retorna os caracteres mais à esquerda de uma string.
LENGTH()	Retorna o comprimento da string.
LOWER()	Retorna a string original com todos os caracteres em letras minúsculas.
LPAD()	Insere caracteres à esquerda de uma string.
LTRIM()	Remove caracteres do lado esquerdo de uma string.
REPLACE()	Substitui todas as ocorrências de uma substring.
RIGHT()	Retorna os caracteres mais à direita de uma string.
RPAD()	Insere caracteres no lado direito de uma string.
RTRIM()	Remove os caracteres finais do lado direito de uma string.
SPLIT()	Divide uma string em substrings repetidas.
SUBSTR()	Retorna uma substring...
UPPER()	Retorna a string original com todos os caracteres em letras maiúsculas.

Funções de caractere curinga de tabela
TABLE_DATE_RANGE()	Consulta várias tabelas diárias que abrangem um período.
TABLE_DATE_RANGE_STRICT()	Consulta várias tabelas diárias que abrangem um período, sem datas ausentes.
TABLE_QUERY()	Consulta tabelas com nomes que correspondem a um predicado especificado.

Funções de URL
HOST()	Dado um URL, retorna o nome do host como uma string.
DOMAIN()	Dado um URL, retorna o domínio como uma string.
TLD()	Dado um URL, retorna o domínio de nível superior mais qualquer domínio de país no URL.

Funções de janela
AVG() COUNT(*) COUNT([DISTINCT]) MAX() MIN() STDDEV() SUM()	A mesma operação das funções Aggregate correspondentes. Porém, elas são computadas em uma janela definida pela cláusula OVER.
CUME_DIST()	Retorna um duplo que indica a distribuição cumulativa de um valor em um grupo de valores...
DENSE_RANK()	Retorna a classificação do número inteiro de um valor em um grupo de valores.
FIRST_VALUE()	Retorna o primeiro valor do campo especificado na janela.
LAG()	Permite ler dados de uma linha anterior dentro de uma janela.
LAST_VALUE()	Retorna o último valor do campo especificado na janela.
LEAD()	Permite ler dados de uma linha seguinte dentro de uma janela.
NTH_VALUE()	Retorna o valor de <expr> na posição <n> do frame da janela ...
NTILE()	Divide a janela no número especificado de intervalos.
PERCENT_RANK()	Retorna a classificação da linha atual, relativa às outras linhas da partição.
PERCENTILE_CONT()	Retorna um valor interpolado que estaria correlacionado ao argumento percentil com relação à janela...
PERCENTILE_DISC()	Retorna o valor mais próximo do percentil do argumento sobre a janela.
RANK()	Retorna a classificação do número inteiro de um valor em um grupo de valores.
RATIO_TO_REPORT()	Retorna a proporção de cada valor para a soma dos valores.
ROW_NUMBER()	Retorna o número da linha atual do resultado da consulta sobre a janela.

Outras funções
CASE WHEN ... THEN	Use CASE para escolher entre duas ou mais expressões alternativas na consulta.
CURRENT_USER()	Retorna o endereço de e-mail do usuário que está executando a consulta.
EVERY()	Retornará true se o argumento for true para todas as entradas.
FROM_BASE64()	Converte a string de entrada codificada em base-64 em formato de BYTES.
HASH()	Calcula e retorna um valor de hash assinado de 64 bits.
FARM_FINGERPRINT()	Calcula e retorna um valor de impressão digital assinado de 64 bits.
IF()	Se o primeiro argumento for true, retornará o segundo argumento. Caso contrário, retornará o terceiro.
POSITION()	Retorna a posição sequencial baseada em 1 do argumento.
SHA1()	Retorna um hash SHA1 no formato BYTES.
SOME()	Retornará true se o argumento for true para pelo menos uma das entradas.
TO_BASE64()	Converte o argumento BYTES em uma string codificada em base-64.

Funções de agregação

As funções de agregação retornam valores que representam resumos de conjuntos de dados maiores, o que torna essas funções especialmente úteis para a análise de registros. Uma função de agregação opera em um conjunto de valores e retorna um único valor por tabela, grupo ou escopo:

• Agregação de tabelas

  Usa uma função de agregação para resumir todas as linhas de qualificação na tabela. Por exemplo:

  SELECT COUNT(f1) FROM ds.Table;

• Agregação em grupo

  Usa uma função de agregação e uma cláusula GROUP BY que especifica um campo não agregado para resumir as linhas por grupo. Exemplo:

  SELECT COUNT(f1) FROM ds.Table GROUP BY b1;

  A função TOP representa um caso especializado de agregação em grupo.

• Agregação em escopo

  Este recurso se aplica somente a tabelas que tenham campos aninhados.
  Para agregar valores repetidos dentro de um escopo definido, são usadas uma função de agregação e a palavra-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 nó.

Você pode aplicar uma restrição a uma função de agregação usando uma das seguintes opções:

• Um alias em uma consulta de subseleção. A restrição é especificada na cláusula WHERE externa.

  #legacySQL
  SELECT corpus, count_corpus_words
  FROM
    (SELECT corpus, count(word) AS count_corpus_words
    FROM [bigquery-public-data:samples.shakespeare]
    GROUP BY corpus) AS sub_shakespeare
  WHERE count_corpus_words > 4000
  

• Um alias em uma cláusula HAVING.

  #legacySQL
  SELECT corpus, count(word) AS count_corpus_words
  FROM [bigquery-public-data:samples.shakespeare]
  GROUP BY corpus
  HAVING count_corpus_words > 4000;
  

Também é possível se referir a um alias nas cláusulas GROUP BY ou ORDER BY.

Sintaxe

Funções de agregação
AVG()	Retorna a média dos valores para um grupo de linhas...
BIT_AND()	Retorna o resultado de uma operação AND bit a bit...
BIT_OR()	Retorna o resultado de uma operação OR bit a bit...
BIT_XOR()	Retorna o resultado de uma operação XOR bit a bit...
CORR()	Retorna o coeficiente de correlação de Pearson de um conjunto de pares de números.
COUNT()	Retorna o número total de valores...
COUNT([DISTINCT])	Retorna o número total de valores não NULL...
COVAR_POP()	Calcula a covariância de população dos valores...
COVAR_SAMP()	Calcula a covariância de exemplo dos valores...
EXACT_COUNT_DISTINCT()	Retorna o número exato de valores não NULL distintos para o campo especificado.
FIRST()	Retorna o primeiro valor sequencial no escopo da função.
GROUP_CONCAT()	Concatena strings múltiplas em uma única string...
GROUP_CONCAT_UNQUOTED()	Concatena strings múltiplas em uma única string... não adicionará aspas duplas...
LAST()	Retorna o último valor sequencial...
MAX()	Retorna o valor máximo...
MIN()	Retorna o valor mínimo...
NEST()	Agrupa todos os valores no escopo de agregação atual em um campo repetido.
NTH()	Retorna o enésimo valor sequencial...
QUANTILES()	Calcula aproximado mínimo, máximo e quantis...
STDDEV()	Retorna o desvio padrão...
STDDEV_POP()	Calcula o desvio padrão da população...
STDDEV_SAMP()	Calcula o desvio padrão da amostra...
SUM()	Retorna a soma total dos valores...
TOP() ... COUNT(*)	Retorna os principais registros max_records por frequência.
UNIQUE()	Retorna o conjunto de valores não NULL exclusivos...
VARIANCE()	Calcula a variância dos valores...
VAR_POP()	Calcula a variância de população dos valores...
VAR_SAMP()	Calcula a variância de amostra dos valores...

AVG(numeric_expr)

Retorna a média dos valores de um grupo de linhas computadas por numeric_expr. Linhas com valor NULL não estão incluídas no cálculo.

BIT_AND(numeric_expr)

Retorna o resultado de uma operação AND bit a bit entre cada instância de numeric_expr em todas as linhas. Os valores NULL são ignorados. Essa função retorna 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. Essa função retorna 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. Essa função retorna 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 usando COUNT(*) com a função TOP, é melhor especificar explicitamente o campo para contar.

COUNT([DISTINCT] field [, n])

Retorna o número total de valores não NULL no escopo da função.

A função retornará o número de valores distintos do campo especificado se você usar a palavra-chave DISTINCT. O valor retornado 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 mais precisão de COUNT(DISTINCT), poderá especificar um segundo parâmetro, n, que fornece o limite abaixo do qual os resultados exatos são garantidos. Por padrão, n é 1000, mas se você der um n maior, conseguirá resultados exatos para COUNT(DISTINCT) até esse valor de n. No entanto, fornecer valores maiores de n reduzirá a escalabilidade desse operador e poderá aumentar substancialmente o tempo de execução da consulta ou fazer com que a consulta falhe.

Para calcular o número exato de valores distintos, use EXACT_COUNT_DISTINCT. Ou, para uma abordagem mais escalonável, considere usar GROUP EACH BY no(s) campo(s) relevante(s) e, em seguida, aplicar COUNT(*). A abordagem GROUP EACH BY é mais escalonável, mas pode gerar uma leve penalidade de desempenho inicial.

COVAR_POP(numeric_expr1, numeric_expr2)

Calcula a covariância de preenchimento dos valores computados por numeric_expr1 e numeric_expr2.

COVAR_SAMP(numeric_expr1, numeric_expr2)

Calcula a covariância de amostra dos valores computados 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 (campo DISTINCT).

FIRST(expr)

Retorna o primeiro valor sequencial no escopo da função.

GROUP_CONCAT('str' [, separator])

Concatena strings múltiplas em uma única string, em que cada valor é separado pelo parâmetro opcional separator. Se separator for omitido, o BigQuery retornará uma string separada por vírgula.

Se uma string nos dados de origem contém um caractere de aspas duplas, GROUP_CONCAT retorna a string com as aspas duplas adicionadas. Por exemplo, a string a"b retornaria como "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 strings múltiplas em uma única string, em que cada valor é separado pelo parâmetro opcional separator. Se separator for omitido, o BigQuery retornará uma string separada por vírgula.

Ao contrário de GROUP_CONCAT, essa função não adicionará aspas duplas aos valores retornados que incluem um caractere de aspas duplas. Por exemplo, a string a"b retornaria como 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 e contém um campo repetido para todos os valores y pareados com x na entrada da consulta. A função NEST requer uma cláusula GROUP BY.

O BigQuery nivela automaticamente os resultados da consulta. Portanto, se você usar a função NEST na consulta de nível superior, os resultados não conterão campos repetidos. Use a função NEST ao utilizar uma subseleção que produz resultados intermediários para uso imediato por parte da mesma consulta.

NTH(n, field)

Retorna o valor sequencial de nth no escopo da função, em que n é uma constante. A função NTH começa a contar a partir de 1, por isso, zero não é aplicável. Se o escopo da função tiver menos de n valores, a função retornará NULL.

QUANTILES(expr[, buckets])

Calcula o mínimo, o máximo e os quantis aproximados para a expressão de entrada. Os valores de entrada NULL são ignorados. Uma entrada vazia ou exclusivamente NULL resulta em saída NULL. O número de quantis calculados é controlado com o parâmetro buckets opcional, que inclui o mínimo e o máximo na contagem. Para calcular N-tiles aproximados, use N + 1 buckets. O valor padrão de buckets é 100. Observação: o padrão de 100 não estima percentis. Para estimar os percentis, use 101 buckets pelo menos. Se especificado explicitamente, buckets precisa ser pelo menos 2.

O erro fracionário por quantil é epsilon = 1 / buckets, o que significa que o erro diminui à medida que o número de intervalos aumenta. Por exemplo:

QUANTILES(<expr>, 2) # computes min and max with 50% error.
QUANTILES(<expr>, 3) # computes min, median, and max with 33% error.
QUANTILES(<expr>, 5) # computes quartiles with 25% error.
QUANTILES(<expr>, 11) # computes deciles with 10% error.
QUANTILES(<expr>, 21) # computes vigintiles with 5% error.
QUANTILES(<expr>, 101) # computes percentiles with 1% error.

A função NTH pode ser usada para escolher um quantil particular, mas lembre-se 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º vigintil (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 percentil 95 com 0,1% de erro, use NTH(951, QUANTILES(expr, 1001)).

STDDEV(numeric_expr)

Retorna o desvio padrão dos valores computados por numeric_expr. Linhas com valor NULL não estão incluídas no cálculo. A função STDDEV é um alias de STDDEV_SAMP.

STDDEV_POP(numeric_expr)

Calcula o desvio-padrão da população do valor computado 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 compreender apenas uma amostra representativa da população, use STDDEV_SAMP(). Para mais informações sobre o desvio padrão de amostra em comparação com população, consulte Desvio padrão na Wikipédia.

STDDEV_SAMP(numeric_expr)

Calcula o desvio-padrão da amostra do valor computado por numeric_expr. Use STDDEV_SAMP() para calcular o desvio padrão de uma população inteira com base em uma amostra representativa da população. Se o conjunto de dados compreender toda a população, use STDDEV_POP(). Para mais informações sobre o desvio padrão de amostra em comparação com população, consulte Desvio padrão na Wikipédia.

SUM(field)

Retorna a soma total dos valores no escopo da função. Somente para uso com tipos de dados numéricos.

TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)

Retorna os principais registros max_records por frequência. Consulte a descrição a seguir da função TOP para ver detalhes.

UNIQUE(expr)

Retorna o conjunto de valores exclusivos e não NULL no escopo da função em uma ordem indefinida. Semelhante a uma cláusula GROUP BY grande sem a palavra-chave EACH, a consulta apresentará um erro "Recursos excedidos" se houver muitos valores distintos. No entanto, ao contrário de GROUP BY, a função UNIQUE pode ser aplicada com agregação com escopo definido, permitindo operação eficiente em campos aninhados com um número limitado de valores.

VARIANCE(numeric_expr)

Calcula a variância dos valores calculados por numeric_expr. Linhas com valor NULL não estão incluídas no cálculo. A função VARIANCE é um alias de VAR_SAMP.

VAR_POP(numeric_expr)

Calcula a variância da população dos valores computados por numeric_expr. Para mais informações sobre o desvio padrão de amostra em comparação com população, consulte Desvio padrão na Wikipédia.

VAR_SAMP(numeric_expr)

Calcula a variância de amostra dos valores computados por numeric_expr. Para mais informações sobre o desvio padrão de amostra em comparação com população, consulte Desvio padrão na Wikipédia.

Função TOP()

TOP é uma função alternativa à cláusula GROUP BY. Ela é usada como uma sintaxe simplificada para GROUP BY ... ORDER BY ... LIMIT .... Geralmente, a função TOP executa mais rápido do que a consulta ... GROUP BY ... ORDER BY ... LIMIT ... completa, mas pode retornar somente resultados aproximados. Veja a seguir a sintaxe da função TOP:

TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)

Ao usar TOP em uma cláusula SELECT, inclua COUNT(*) como um dos campos.

Uma consulta que utiliza a função TOP() só pode retornar dois campos: o campo TOP e o valor COUNT(*).

field|alias

O campo ou alias a ser retornado.

max_values

[Opcional] O número máximo de resultados a serem retornados. O padrão é 20.

multiplier

Um número inteiro positivo que aumenta os valores retornados por COUNT(*) pelo múltiplo especificado.

Exemplos de TOP()

• Consultas de exemplo básicas que usam TOP()

  As consultas a seguir usam TOP() para retornar 10 linhas.

  Exemplo 1:

  #legacySQL
  SELECT
    TOP(word, 10) as word, COUNT(*) as cnt
  FROM
    [bigquery-public-data:samples.shakespeare]
  WHERE
    word CONTAINS "th";
  

  Exemplo 2:

  #legacySQL
  SELECT
    word, left(word, 3)
  FROM
    (SELECT TOP(word, 10) AS word, COUNT(*)
       FROM [bigquery-public-data:samples.shakespeare]
       WHERE word CONTAINS "th");
  

• Comparar TOP() 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 em que as palavras foram usadas. A consulta TOP será executada muito mais rapidamente:

  Exemplo sem TOP():

  #legacySQL
  SELECT
    word, COUNT(*) AS cnt
  FROM
    ds.Table
  WHERE
    word CONTAINS 'th'
  GROUP BY
    word
  ORDER BY
    cnt DESC LIMIT 10;
  

  Exemplo com TOP():

  #legacySQL
  SELECT
    TOP(word, 10), COUNT(*)
  FROM
    ds.Table
  WHERE
    word contains 'th';
  

• Como usar o parâmetro multiplier.

  As consultas a seguir mostram como o parâmetro multiplier afeta o resultado da consulta. A primeira consulta retorna o número de nascimentos por mês em Wyoming. A segunda consulta usa o parâmetro multiplier para multiplicar os valores 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 é operado em cada linha antes da agregação.

  Exemplo:

  #legacySQL
  SELECT
    state,
    /* If 'is_male' is True, return 'Male', */
    /* otherwise return 'Female' */
    IF (is_male, 'Male', 'Female') AS sex,
    /* The count value is aliased as 'cnt' */
    /* and used in the HAVING clause below. */
    COUNT(*) AS cnt
  FROM
    [bigquery-public-data:samples.natality]
  WHERE
    state != ''
  GROUP BY
    state, sex
  HAVING
    cnt > 3000000
  ORDER BY
    cnt DESC
  

  Retorna:

  +-------+--------+---------+
  | state |  sex   |   cnt   |
  +-------+--------+---------+
  | CA    | Male   | 7060826 |
  | CA    | Female | 6733288 |
  | TX    | Male   | 5107542 |
  | TX    | Female | 4879247 |
  | NY    | Male   | 4442246 |
  | NY    | Female | 4227891 |
  | IL    | Male   | 3089555 |
  +-------+--------+---------+
  

Operadores aritméticos

Os operadores aritméticos pegam argumentos numéricos e retornam um resultado numérico. Cada argumento pode ser um literal numérico ou um valor numérico retornado por uma consulta. Se a operação aritmética é avaliada como um resultado indefinido, a operação retorna NULL.

Sintaxe

Operador	Descrição	Exemplo
+	Adição	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
%	Modulo	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 incluídas na documentação de 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 definidos 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>)

• Retorna true se <numeric_expr> não for 0 e não NULL.
• Retorna false se <numeric_expr> for 0.
• Retorna NULL se <numeric_expr> for NULL.

BYTES(string_expr)

Retorna string_expr como um valor do tipo bytes.

CAST(expr AS type)

Converte expr em uma variável do tipo type.

FLOAT(expr)

Retorna expr como um double. 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.

• Retorna NULL se expr é uma string que não corresponde a um valor inteiro.
• Retorna o número de microssegundos desde o período do unix, se expr for um carimbo de data/hora.

STRING(numeric_expr)

Returna numeric_expr como uma string.

Funções de comparação

As funções de comparação retornam true ou false, com base nos tipos de comparações a seguir:

• uma comparação de duas expressões
• uma comparação de uma expressão ou um conjunto de expressões em relação a um critério específico, como, por exemplo, presença em uma lista especificada, caráter NULL ou um valor opcional não padrão.

Algumas das funções listadas abaixo retornam valores diferentes de true ou false, mas os valores retornados são baseados em operações de comparação.

É possível usar expressões numéricas ou de string como argumentos para funções de comparação. (As constantes de string precisam ser delimitadas por aspas simples ou duplas.) As expressões podem ser literais ou valores buscados por uma consulta. As funções de comparação são mais frequentemente usadas como condições de filtragem em cláusulas WHERE, mas podem ser usadas em outras cláusulas.

Sintaxe

Funções de comparação
expr1 = expr2	Retorna true se as expressões forem iguais.
expr1 != expr2 expr1 <> expr2	Retorna true se as expressões não forem iguais.
expr1 > expr2	Retorna true se expr1 for maior que expr2.
expr1 < expr2	Retorna true se expr1 for menor que expr2.
expr1 >= expr2	Retorna true se expr1 for maior ou igual a expr2.
expr1 <= expr2	Retorna true se expr1 for menor ou igual a expr2.
expr1 BETWEEN expr2 AND expr3	Retorna true se o valor de expr1 for entre expr2 e expr3, inclusive.
expr IS NULL	Retorna true se expr for NULL.
expr IN()	Retorna true se expr corresponder a expr1, expr2 ou qualquer valor entre parênteses.
COALESCE()	Retorna o primeiro argumento que não é NULL.
GREATEST()	Retorna o maior parâmetro numeric_expr.
IFNULL()	Se o argumento não for NULL, retornará o argumento.
IS_INF()	Retorna true se o infinito for positivo ou negativo.
IS_NAN()	Retorna true se o argumento for NaN.
IS_EXPLICITLY_DEFINED()	obsoleto: use expr IS NOT NULL.
LEAST()	Retorna o menor parâmetro numeric_expr do argumento.
NVL()	Se expr não for nulo, retornará expr. Caso contrário, retornará null_default.

expr1 = expr2

Retorna true se as expressões forem iguais.

expr1 != expr2
expr1 <> expr2

Retorna true se as expressões não forem iguais.

expr1 > expr2

Retorna true se expr1 for maior que expr2.

expr1 < expr2

Retorna true se expr1 for menor que expr2.

expr1 >= expr2

Retorna true se expr1 for maior ou igual a expr2.

expr1 <= expr2

Retorna true se expr1 for menor ou igual a expr2.

expr1 BETWEEN expr2 AND expr3

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

expr IS NULL

Retorna true se expr for NULL.

expr IN(expr1, expr2, ...)

Retorna true se expr corresponder a expr1, expr2 ou qualquer valor entre 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 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 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.

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 nulo, retornará expr. Caso contrário, retornará null_default.

IS_INF(numeric_expr)

Retorna true se numeric_expr é um infinito positivo ou negativo.

IS_NAN(numeric_expr)

Retorna true se numeric_expr for o valor numérico especial NaN.

IS_EXPLICITLY_DEFINED(expr)

Essa função está obsoleta. Use expr IS NOT NULL, em vez disso.

LEAST(numeric_expr1, numeric_expr2, ...)

Retorna o menor parâmetro numeric_expr. Todos os parâmetros precisam ser numéricos e do mesmo tipo. Se algum parâmetro for NULL, essa função retornará NULL.

NVL(expr, null_default)

Se expr não for nulo, retornará expr. Caso contrário, retornará null_default. A funçã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 o horário atual do servidor no formato %H:%M:%S.
CURRENT_TIMESTAMP()	Retorna o horário atual do servidor no formato %Y-%m-%d %H:%M:%S.
DATE()	Retorna a data no formato %Y-%m-%d.
DATE_ADD()	Adiciona o intervalo especificado a um tipo de dados TIMESTAMP.
DATEDIFF()	Retorna o número de dias entre dois tipos de dados TIMESTAMP.
DAY()	Retorna o dia do mês como um número inteiro entre 1 e 31.
DAYOFWEEK()	Retorna o dia da semana como um número inteiro entre 1 (domingo) e 7 (sábado).
DAYOFYEAR()	Retorna o dia do ano como um número inteiro entre 1 e 366.
FORMAT_UTC_USEC()	Retorna um carimbo de data/hora UNIX no formato YYYY-MM-DD HH:MM:SS.uuuuuu.
HOUR()	Retorna a hora de um TIMESTAMP como um número inteiro entre 0 e 23.
MINUTE()	Retorna os minutos de um TIMESTAMP como um número inteiro entre 0 e 59.
MONTH()	Retorna o mês de um TIMESTAMP como um número inteiro entre 1 e 12.
MSEC_TO_TIMESTAMP()	Converte um carimbo de data/hora UNIX em milissegundos em um TIMESTAMP.
NOW()	Retorna o carimbo de data/hora UNIX atual em microssegundos.
PARSE_UTC_USEC()	Converte uma string de data em um carimbo de data/hora UNIX em microssegundos.
QUARTER()	Retorna o trimestre do ano de um TIMESTAMP como um número inteiro entre 1 e 4.
SEC_TO_TIMESTAMP()	Converte um carimbo de data/hora UNIX em segundos em um TIMESTAMP.
SECOND()	Retorna os segundos de um TIMESTAMP como um número inteiro entre 0 e 59.
STRFTIME_UTC_USEC()	Retorna uma string de data no formato date_format_str.
TIME()	Retorna um TIMESTAMP no formato %H:%M:%S.
TIMESTAMP()	Converte uma string de data em um TIMESTAMP.
TIMESTAMP_TO_MSEC()	Converte um TIMESTAMP em um carimbo de data/hora UNIX em milissegundos.
TIMESTAMP_TO_SEC()	Converte um TIMESTAMP em um carimbo de data/hora UNIX em segundos.
TIMESTAMP_TO_USEC()	Converte um TIMESTAMP em um carimbo de data/hora UNIX em microssegundos.
USEC_TO_TIMESTAMP()	Converte um carimbo de data/hora UNIX em microssegundos em um TIMESTAMP.
UTC_USEC_TO_DAY()	Desloca um carimbo de data/hora UNIX em microssegundos para o início do dia em que ele ocorre.
UTC_USEC_TO_HOUR()	Desloca um carimbo de data/hora UNIX em microssegundos para o início da hora em que ele ocorre.
UTC_USEC_TO_MONTH()	Desloca um carimbo de data/hora UNIX em microssegundos para o início do mês em que ele ocorre.
UTC_USEC_TO_WEEK()	Retorna um carimbo de data/hora UNIX em microssegundos que representa um dia na semana.
UTC_USEC_TO_YEAR()	Retorna um carimbo de data/hora UNIX em microssegundos que representa o ano.
WEEK()	Retorna a semana de um TIMESTAMP como um número inteiro entre 1 e 53.
YEAR()	Retorna o ano de um TIMESTAMP.

CURRENT_DATE()

Retorna uma string legível da data atual no formato %Y-%m-%d.

Exemplo:

SELECT CURRENT_DATE();

Retorna: 2013-02-01

CURRENT_TIME()

Retorna uma string legível do horário atual do servidor no formato %H:%M:%S.

Exemplo:

SELECT CURRENT_TIME();

Retorna: 01:32:56

CURRENT_TIMESTAMP()

Retorna um tipo de dados TIMESTAMP do horário atual do servidor no formato %Y-%m-%d %H:%M:%S.

Exemplo:

SELECT CURRENT_TIMESTAMP();

Retorna: 2013-02-01 01:33:35 UTC

DATE(<timestamp>)

Retorna uma string legível de um tipo de dados TIMESTAMP no formato %Y-%m-%d.

Exemplo:

SELECT DATE(TIMESTAMP('2012-10-01 02:03:04'));

Retorna: 2012-10-01

DATE_ADD(<timestamp>,<interval>,
                 <interval_units>)

Adiciona o intervalo especificado a um tipo de dados TIMESTAMP. Os valores interval_units possíveis 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 e hora UNIX no formato YYYY-MM-DD HH:MM:SS.uuuuuu.

Exemplo:

SELECT FORMAT_UTC_USEC(1274259481071200);

Retorna: 2010-05-19 08:58:01.071200

HOUR(<timestamp>)

Retorna a hora de um tipo de dados TIMESTAMP como um número inteiro entre 0 e 23, inclusive.

Exemplo:

SELECT HOUR(TIMESTAMP('2012-10-02 05:23:48'));

Retorna: 5

MINUTE(<timestamp>)

Retorna os minutos de um tipo de dados TIMESTAMP como um número inteiro entre 0 e 59, inclusive.

Exemplo:

SELECT MINUTE(TIMESTAMP('2012-10-02 05:23:48'));

Retorna: 23

MONTH(<timestamp>)

Retorna o mês de um tipo de dados TIMESTAMP como um número inteiro entre 1 e 12, inclusive.

Exemplo:

SELECT MONTH(TIMESTAMP('2012-10-02 05:23:48'));

Retorna: 10

MSEC_TO_TIMESTAMP(<expr>)

Converte um carimbo de data/hora UNIX em milissegundos em um tipo de dados TIMESTAMP.

Exemplo:

SELECT MSEC_TO_TIMESTAMP(1349053323000);

Retorna: 2012-10-01 01:02:03 UTC

SELECT MSEC_TO_TIMESTAMP(1349053323000 + 1000)

Retorna: 2012-10-01 01:02:04 UTC

NOW()

Retorna o carimbo de data/hora UNIX atual em microssegundos.

Exemplo:

SELECT NOW();

Retorna: 1359685811687920

PARSE_UTC_USEC(<date_string>)

Converte uma string de data em um carimbo de data/hora UNIX em microssegundos. date_string precisa ter o 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 date_format_str e date_format_str), bem como caracteres especiais aceitos pela função strftime em C++ (como date_format_str para o dia do mês).

Use as funções UTC_USEC_TO_<function_name> se você planeja agrupar dados de consulta por intervalos de tempo, como para conseguir todos os dados de um determinado mês, porque as funções são mais eficientes.

Exemplo:

SELECT STRFTIME_UTC_USEC(1274259481071200, "%Y-%m-%d");

Retorna: 2010-05-19

TIME(<timestamp>)

Retorna uma string legível de um tipo de dados TIMESTAMP, no formato %H:%M:%S.

Exemplo:

SELECT TIME(TIMESTAMP('2012-10-01 02:03:04'));

Retorna: 02:03:04

TIMESTAMP(<date_string>)

Converte uma string de data em um tipo de dados TIMESTAMP.

Exemplo:

SELECT TIMESTAMP("2012-10-01 01:02:03");

Retorna: 2012-10-01 01:02:03 UTC

TIMESTAMP_TO_MSEC(<timestamp>)

Converte um tipo de dados TIMESTAMP em um carimbo de data/hora UNIX em milissegundos.

Exemplo:

SELECT TIMESTAMP_TO_MSEC(TIMESTAMP("2012-10-01 01:02:03"));

Retorna: 1349053323000

TIMESTAMP_TO_SEC(<timestamp>)

Converte um tipo de dados TIMESTAMP em um carimbo de data/hora UNIX em segundos.

Exemplo:

SELECT TIMESTAMP_TO_SEC(TIMESTAMP("2012-10-01 01:02:03"));

Retorna: 1349053323

TIMESTAMP_TO_USEC(<timestamp>)

Converte um tipo de dados TIMESTAMP em um carimbo de data/hora UNIX em microssegundos.

PARSE_UTC_USEC é uma função equivalente que converte um argumento de string de dados em vez de um tipo de dados TIMESTAMP.

Exemplo:

SELECT TIMESTAMP_TO_USEC(TIMESTAMP("2012-10-01 01:02:03"));

Retorna: 1349053323000000

USEC_TO_TIMESTAMP(<expr>)

Converte um carimbo de data/hora UNIX em microssegundos em um tipo de dados TIMESTAMP.

Exemplo:

SELECT USEC_TO_TIMESTAMP(1349053323000000);

Retorna: 2012-10-01 01:02:03 UTC

SELECT USEC_TO_TIMESTAMP(1349053323000000 + 1000000)

Retorna: 2012-10-01 01:02:04 UTC

UTC_USEC_TO_DAY(<unix_timestamp>)

Desloca um carimbo de data/hora UNIX em microssegundos para o início do dia em que ele ocorre.

Por exemplo, se unix_timestamp ocorrer em 19 de maio às 08:58, essa função retornará um carimbo de data e hora UNIX para 19 de maio às 00:00 (meia-noite).

Exemplo:

SELECT UTC_USEC_TO_DAY(1274259481071200);

Retorna: 1274227200000000

UTC_USEC_TO_HOUR(<unix_timestamp>)

Desloca um carimbo de data/hora UNIX em microssegundos para o início da hora em que ele ocorre.

Por exemplo, se unix_timestamp ocorrer às 08:58, essa função retornará um carimbo de data/hora UNIX para 08:00 no mesmo dia.

Exemplo:

SELECT UTC_USEC_TO_HOUR(1274259481071200);

Retorna: 1274256000000000

UTC_USEC_TO_MONTH(<unix_timestamp>)

Desloca um carimbo de data/hora UNIX em microssegundos para o início do mês em que ele ocorre.

Por exemplo, se unix_timestamp ocorrer no dia 19 de março, essa função retornará um carimbo de data/hora UNIX para 1º de março do mesmo ano.

Exemplo:

SELECT UTC_USEC_TO_MONTH(1274259481071200);

Retorna: 1272672000000000

UTC_USEC_TO_WEEK(<unix_timestamp>,
                 <day_of_week>)

Retorna um carimbo de data/hora para UNIX em microssegundos que representa um dia na semana do argumento unix_timestamp. Essa função recebe dois argumentos: um carimbo de data/hora do UNIX em microssegundos e um dia da semana de 0 (domingo) a 6 (sábado).

Por exemplo, se unix_timestamp ocorrer na sexta-feira, 11 de abril de 2008, e você definir day_of_week para 2 (terça-feira), a função retornará um carimbo de data/hora UNIX para a terça-feira, 8 de abril de 2008.

Exemplo:

SELECT UTC_USEC_TO_WEEK(1207929480000000, 2) AS tuesday;

Retorna: 1207612800000000

UTC_USEC_TO_YEAR(<unix_timestamp>)

Retorna um carimbo de data/hora para UNIX em microssegundos que representa o ano do argumento unix_timestamp.

Por exemplo, se unix_timestamp ocorrer em 2010, a função retornará 1274259481071200, a representação de microssegundo 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, que recebe um carimbo de data/hora, em microssegundos, como uma entrada. Essa consulta multiplica os carimbos de data/hora de formato POSIX da Wikipédia (em segundos) por 1.000.000 para converter o valor em microssegundos.

  Exemplo:

  #legacySQL
  SELECT
    /* Multiply timestamp by 1000000 and convert */
    /* into a more human-readable format. */
    TOP (FORMAT_UTC_USEC(timestamp * 1000000), 5)
      AS top_revision_time,
    COUNT (*) AS revision_count
  FROM
    [bigquery-public-data:samples.wikipedia];
  

  Retorna:

  +----------------------------+----------------+
  |     top_revision_time      | revision_count |
  +----------------------------+----------------+
  | 2002-02-25 15:51:15.000000 |          20976 |
  | 2002-02-25 15:43:11.000000 |          15974 |
  | 2010-02-02 03:34:51.000000 |              3 |
  | 2010-02-02 01:04:59.000000 |              3 |
  | 2010-02-01 23:55:05.000000 |              3 |
  +----------------------------+----------------+
  

• Organização de resultados por carimbo de data/hora

  É útil usar funções de data e hora para agrupar os resultados da consulta em intervalos correspondentes a anos, meses ou dias específicos. O exemplo a seguir usa a função UTC_USEC_TO_MONTH() para exibir quantos caracteres cada colaborador do Wikipedia usa nos comentários de revisão por mês.

  Exemplo:

  #legacySQL
  SELECT
    contributor_username,
    /* Return the timestamp shifted to the
     * start of the month, formatted in
     * a human-readable format. Uses the
     * 'LEFT()' string function to return only
     * the first 7 characters of the formatted timestamp.
     */
    LEFT (FORMAT_UTC_USEC(
      UTC_USEC_TO_MONTH(timestamp * 1000000)),7)
      AS month,
    SUM(LENGTH(comment)) as total_chars_used
  FROM
    [bigquery-public-data:samples.wikipedia]
  WHERE
    (contributor_username != '' AND
     contributor_username IS NOT NULL)
    AND timestamp > 1133395200
    AND timestamp < 1157068800
  GROUP BY
    contributor_username, month
  ORDER BY
    total_chars_used DESC;
  

  Retorna (truncado):

  +--------------------------------+---------+-----------------------+
  |      contributor_username      |  month  | total_chars_used      |
  +--------------------------------+---------+-----------------------+
  | Kingbotk                       | 2006-08 |              18015066 |
  | SmackBot                       | 2006-03 |               7838365 |
  | SmackBot                       | 2006-05 |               5148863 |
  | Tawkerbot2                     | 2006-05 |               4434348 |
  | Cydebot                        | 2006-06 |               3380577 |
  etc ...
  

Funções IP

As funções IP convertem endereços IP de e para um formato legível.

Sintaxe

Funções IP
FORMAT_IP()	Converte 32 bits menos significativos de integer_value em uma string de endereço IPv4 legível.
PARSE_IP()	Converte uma string representando o endereço IPv4 em um valor inteiro sem sinal.
FORMAT_PACKED_IP()	Retorna um endereço IP legível no formato 10.1.5.23 ou 2620:0:1009:1:216:36ff:feef:3f.
PARSE_PACKED_IP()	Retorna um endereço IP em BYTES.

FORMAT_IP(integer_value)

Converte 32 bits menos significativos de integer_value em uma string de endereço IPv4 legível. Por exemplo, FORMAT_IP(1) retornará a string '0.0.0.1'.

PARSE_IP(readable_ip)

Converte uma string representando o endereço IPv4 em um valor inteiro sem sinal. Por exemplo, PARSE_IP('0.0.0.1') retornará 1. Se string não for um endereço IPv4 válido, PARSE_IP retornará NULL.

O BigQuery é compatível com gravação de endereços IPv4 e IPv6 em strings compactadas, como dados binários de 4 ou 16 bytes na ordem de bytes da rede. As funções descritas a seguir são compatíveis com a análise dos endereços de e para uma forma legível. Essas funções só operam em campos de string com IPs.

Sintaxe

FORMAT_PACKED_IP(packed_ip)

Retorna um endereço IP legível, no formato 10.1.5.23 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. json_path precisa ser uma constante de string. Retorna o valor no formato de string JSON.

JSON_EXTRACT_SCALAR(json, json_path)

Seleciona um valor em json de acordo com a expressão JSONPath json_path. json_path precisa ser uma constante de string. Retorna um valor JSON escalar.

Operadores lógicos

Operadores lógicos executam lógica binária ou ternária em expressões. A lógica binária retorna true ou false. A lógica ternária acomoda valores NULL e retorna true, false ou NULL.

Sintaxe

Operadores lógicos
expr AND expr	Retorna true se ambas as expressões forem verdadeiras.
expr OR expr	Retorna true se uma ou as duas expressões forem verdadeiras.
NOT expr	Retorna true se a expressão for falsa.

expr AND expr

• Retorna true se ambas as expressões forem verdadeiras.
• Retorna false se uma ou ambas as expressões forem falsas.
• Retorna NULL se ambas as expressões são NULL ou se uma expressão é verdadeira, e a outra é NULL.

expr OR expr

• Retorna true se uma ou as duas expressões forem verdadeiras.
• Retorna false se ambas as expressões são falsas.
• Retorna NULL se ambas as expressões são NULL ou se uma expressão é falsa e a outra é NULL.

NOT expr

• Retorna true se a expressão for falsa.
• Retorna false se a expressão for verdadeira.
• Retorna NULL se a expressão for NULL.

Use NOT com outras funções como um operador de negação. Por exemplo, NOT IN(expr1, expr2) 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 em graus.
EXP()	Retorna e à potência do argumento.
FLOOR()	Arredonda o argumento para baixo para o número inteiro mais próximo.
LN() LOG()	Retorna o logaritmo natural do argumento.
LOG2()	Retorna o logaritmo de Base 2 do argumento.
LOG10()	Retorna o logaritmo de Base 10 do argumento.
PI()	Retorna a constante π.
POW()	Retorna o primeiro argumento à potência do segundo argumento.
RADIANS()	Converte de graus em radianos.
RAND()	Retorna um valor flutuante aleatório no intervalo 0,0 <= valor < 1,0.
ROUND()	Arredonda o argumento para cima ou para baixo para o número inteiro mais próximo.
SIN()	Retorna o seno do argumento.
SINH()	Retorna o seno hiperbólico do argumento.
SQRT()	Retorna a raiz quadrada da expressão.
TAN()	Retorna a tangente do argumento.
TANH()	Retorna a tangente hiperbólica do argumento.

ABS(numeric_expr)

Retorna o valor absoluto do argumento.

ACOS(numeric_expr)

Retorna o arco cosseno do argumento.

ACOSH(numeric_expr)

Retorna o arco cosseno hiperbólico do argumento.

ASIN(numeric_expr)

Retorna o arco seno do argumento.

ASINH(numeric_expr)

Retorna o arco seno hiperbólico do argumento.

ATAN(numeric_expr)

Retorna o arco tangente do argumento.

ATANH(numeric_expr)

Retorna o arco tangente hiperbólico do argumento.

ATAN2(numeric_expr1, numeric_expr2)

Retorna o arco tangente dos dois argumentos.

CEIL(numeric_expr)

Arredonda o argumento para cima até o número inteiro mais próximo e retorna o valor arredondado.

COS(numeric_expr)

Retorna o cosseno do argumento.

COSH(numeric_expr)

Retorna o cosseno hiperbólico do argumento.

DEGREES(numeric_expr)

Retorna numeric_expr, convertido de radianos para graus.

EXP(numeric_expr)

Retorna o resultado da elevação da constante "e", a base do logaritmo natural, para a potência de numeric_expr.

FLOOR(numeric_expr)

Arredonda o argumento para baixo para o número inteiro mais próximo e retorna o valor arredondado.

LN(numeric_expr)
LOG(numeric_expr)

Retorna o logaritmo natural do argumento.

LOG2(numeric_expr)

Retorna o logaritmo de Base 2 do argumento.

LOG10(numeric_expr)

Retorna o logaritmo de Base 10 do argumento.

PI()

Retorna a constante π. A função PI() requer parênteses para indicar que é uma função, mas não usa argumentos entre parênteses. Use PI() como uma constante com funções matemáticas e aritméticas.

POW(numeric_expr1, numeric_expr2)

Retorna o resultado do aumento de numeric_expr1 para a potência 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 <= valor < 1. Cada valor int32_seed sempre gera a mesma sequência de números aleatórios em uma determinada consulta, contanto que você não use uma cláusula LIMIT. Se int32_seed não for especificado, o BigQuery usará o carimbo de data/hora atual como valor de propagação.

ROUND(numeric_expr [, digits])

Arredonda o argumento para cima ou para baixo para o número inteiro mais próximo (ou para o número especificado de dígitos, se especificado) e retorna o valor arredondado.

SIN(numeric_expr)

Retorna o seno do argumento.

SINH(numeric_expr)

Retorna o seno hiperbólico do argumento.

SQRT(numeric_expr)

Retorna a raiz quadrada da expressão.

TAN(numeric_expr)

Retorna a tangente do argumento.

TANH(numeric_expr)

Retorna a tangente hiperbólica do argumento.

Exemplos avançados

• Consulta de caixa delimitadora

  A consulta a seguir retorna uma coleção de pontos dentro de uma caixa delimitadora retangular centrada em torno de São Francisco (37,46, -122,50).

  Exemplo:

  #legacySQL
  SELECT
    year, month,
    AVG(mean_temp) avg_temp,
    MIN(min_temperature) min_temp,
    MAX(max_temperature) max_temp
  FROM
    [weather_geo.table]
  WHERE
    /* Return values between a pair of */
    /* latitude and longitude coordinates */
    lat / 1000 > 37.46 AND
    lat / 1000 < 37.65 AND
    long / 1000 > -122.50 AND
    long / 1000 < -122.30
  GROUP BY
    year, month
  ORDER BY
    year, month ASC;
  

• Consulta de círculo delimitador aproximado

  Retorna um conjunto de até 100 pontos dentro de um círculo aproximado determinado pelo uso da Lei Esférica dos Cossenos, centrada em torno de Denver, Colorado (39,73, -104,98). Para essa consulta, usamos as funções matemáticas e trigonométricas do BigQuery, como PI(), SIN() 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 re2, o BigQuery se torna compatível com expressões regulares. Acesse essa documentação para consultar a sintaxe de expressão regular (páginas em inglês).

As expressões regulares são correspondências globais. Para começar a correspondência no início de uma palavra, use o caractere ^.

Sintaxe

Funções de expressão regular
REGEXP_MATCH()	Retornará true se o argumento corresponder à expressão regular.
REGEXP_EXTRACT()	Retorna a parte do argumento que corresponde ao grupo de captura dentro da expressão regular.
REGEXP_REPLACE()	Substitui uma substring que corresponde a uma expressão regular.

REGEXP_MATCH('str', 'reg_exp')

Retorna true, se str corresponde à expressão regular. Para a correspondência de sequências sem expressões regulares, use CONTAINS, em vez de REGEXP_MATCH.

Exemplo:

#legacySQL
SELECT
   word,
   COUNT(word) AS count
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   (REGEXP_MATCH(word,r'\w\w\'\w\w'))
GROUP BY word
ORDER BY count DESC
LIMIT 3;

Retorna:

+-------+-------+
| word  | count |
+-------+-------+
| ne'er |    42 |
| we'll |    35 |
| We'll |    33 |
+-------+-------+

REGEXP_EXTRACT('str', 'reg_exp')

Retorna a parte de str que corresponde ao grupo de captura na expressão regular.

Exemplo:

#legacySQL
SELECT
   REGEXP_EXTRACT(word,r'(\w\w\'\w\w)') AS fragment
FROM
   [bigquery-public-data:samples.shakespeare]
GROUP BY fragment
ORDER BY fragment
LIMIT 3;

Retorna:

+----------+
| fragment |
+----------+
| NULL     |
| Al'ce    |
| As'es    |
+----------+

REGEXP_REPLACE('orig_str', 'reg_exp', 'replace_str')

Retorna uma sequência na qual qualquer subsequência de orig_str que corresponda a orig_str é substituída por orig_str. Por exemplo, REGEXP_REPLACE ('Hello', 'lo', 'p') retorna Help.

Exemplo:

#legacySQL
SELECT
  REGEXP_REPLACE(word, r'ne\'er', 'never') AS expanded_word
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  REGEXP_MATCH(word, r'ne\'er')
GROUP BY expanded_word
ORDER BY expanded_word
LIMIT 5;

Retorna:

+---------------+
| expanded_word |
+---------------+
| Whenever      |
| never         |
| nevertheless  |
| whenever      |
+---------------+

Exemplos avançados

• Filtrar conjunto de resultados por expressão regular

  As funções de expressão regular do BigQuery podem ser usadas para filtrar resultados em uma cláusula WHERE, bem como para exibir os resultados 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

  Mesmo que as funções de expressões regulares do BigQuery funcionem somente para dados de strings, é possível usar STRING() para transmitir dados inteiros ou flutuantes para o formato de string. Neste exemplo, STRING() é usado para converter o valor 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. É possível acrescentar IGNORE CASE ao final de uma consulta para ativar a correspondência que não diferencia maiúsculas de minúsculas. IGNORE CASE funciona somente em caracteres ASCII e somente no nível superior da consulta.

Caracteres curingas não são compatíveis com essas funções. Para a funcionalidade de expressão regular, use funções de expressão regular.

Sintaxe

Funções de string
CONCAT()	Retornará a concatenação de duas ou mais strings, ou NULL se qualquer um dos valores for NULL.
expr CONTAINS 'str'	Retorna true se expr contém o argumento de string especificado.
INSTR()	Retorna o índice baseado em 1 da primeira ocorrência de uma string.
LEFT()	Retorna os caracteres mais à esquerda de uma string.
LENGTH()	Retorna o comprimento da string.
LOWER()	Retorna a string original com todos os caracteres em letras minúsculas.
LPAD()	Insere caracteres à esquerda de uma string.
LTRIM()	Remove caracteres do lado esquerdo de uma string.
REPLACE()	Substitui todas as ocorrências de uma substring.
RIGHT()	Retorna os caracteres mais à direita de uma string.
RPAD()	Insere caracteres no lado direito de uma string.
RTRIM()	Remove os caracteres finais do lado direito de uma string.
SPLIT()	Divide uma string em substrings repetidas.
SUBSTR()	Retorna uma substring...
UPPER()	Retorna a string original com todos os caracteres em maiúsculas.

CONCAT('str1', 'str2', '...')
str1 + str2 + ...

Retorna a concatenação de duas ou mais strings, ou NULL se qualquer um dos valores for NULL. Exemplo: se str1 for Java e str2 for Script, CONCAT retornará JavaScript.

expr CONTAINS 'str'

Retorna true se expr contém o argumento de string especificado. Esta é uma comparação que diferencia maiúsculas de minúsculas.

INSTR('str1', 'str2')

Retorna o índice baseado em 1 da primeira ocorrência de str2 em str2 ou retorna 0 se str2 não ocorrer em str2.

LEFT('str', numeric_expr)

Retorna os caracteres numeric_expr mais à esquerda de str. Se o número for maior que str, a string completa será retornada. Exemplo: LEFT('seattle', 3) retorna sea.

LENGTH('str')

Retorna um valor numérico para o comprimento da string. Exemplo: se str for '123456', LENGTH retornará 6.

LOWER('str')

Retorna a string original com todos os caracteres em minúsculas.

LPAD('str1', numeric_expr, 'str2')

Preenche str1 à esquerda com str2, repetindo str2 até que a string de resultado tenha 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 removerá os espaços do lado esquerdo de str2. Caso contrário, LTRIM remove todos os caracteres em str2 do lado esquerdo de str2 (diferencia maiúsculas de minúsculas).

Exemplos:

SELECT LTRIM("Say hello", "yaS") retorna " hello".

SELECT LTRIM("Say hello", " ySa") retorna "hello".

REPLACE('str1', 'str2', 'str3')

Substitui todas as instâncias de str2 dentro de str2 por str2.

RIGHT('str', numeric_expr)

Retorna os caracteres numeric_expr mais à direita de str. Se o número é maior que a sequência, retorna a sequência inteira. Exemplo: RIGHT('kirkland', 4) retorna land.

RPAD('str1', numeric_expr, 'str2')

Preenche str1 à direita com str2, repetindo str2 até que a string de resultado tenha 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 str2. Caso contrário, RTRIM removerá todos os caracteres em str2 à direita de str2 (diferencia maiúsculas de minúsculas).

Exemplos:

SELECT RTRIM("Say hello", "leo") retorna "Say h".

SELECT RTRIM("Say hello ", " hloe") retorna "Say".

SPLIT('str' [, 'delimiter'])

Divide uma string em substrings repetidas. Se delimiter for especificado, a função SPLIT divide str em substrings, usando delimiter como delimitador.

SUBSTR('str', index [, max_len])

Retorna uma subsequência de str, começando às index. Se o parâmetro max_len opcional for usado, a string retornada terá no máximo max_len caracteres. A contagem é iniciada em 1, então o primeiro caractere na string está na posição 1 (e não zero). Se index for 5, a subsequência começará com o quinto caractere da esquerda em str. Se index for -4, a substring começará com o quarto caractere da direita em str. Exemplo: SUBSTR('awesome', -4, 4) retorna a subsequência 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.
• Use sequências em estilo C ('\a', '\b', '\f', '\n', '\r', '\t', e '\v') para outros caracteres.

Alguns exemplos de escape:

'this is a space: \x20'
'this string has \'single quote\' inside it'
'first line \n second line'
"double quotes are also ok"
'\070' -> ERROR: octal escaping is not supported

Funções de caractere curinga de tabela

As funções de caractere curinga de tabela são uma maneira conveniente de consultar dados de um conjunto específico de tabelas. Uma função de caractere curinga de tabela é equivalente a uma união separada por vírgulas de todas as tabelas correspondidas por essa função. Quando você usa uma função de caractere curinga de tabela, o BigQuery acessa e cobra somente as tabelas que correspondem ao caractere curinga. As funções de caractere curinga de tabela são especificadas na cláusula FROM da consulta.

Se você usar funções de caractere curinga de tabela em uma consulta, as funções não precisam mais estar entre parênteses. Por exemplo, alguns dos exemplos a seguir usam parênteses, enquanto outros não.

Os resultados em cache não são aceitos para consultas em várias tabelas usando uma função com caracteres curinga (mesmo que a opção Usar resultados em cache esteja marcada). Se você executar a mesma consulta de caracteres curinga várias vezes, haverá cobrança por cada consulta.

Sintaxe

Funções de caractere curinga de tabela
TABLE_DATE_RANGE()	Consulta várias tabelas diárias que abrangem um período.
TABLE_DATE_RANGE_STRICT()	Consulta várias tabelas diárias que abrangem um período, sem datas ausentes.
TABLE_QUERY()	Consulta tabelas com nomes que correspondem a um predicado especificado.

TABLE_DATE_RANGE(prefix, timestamp1, timestamp2)

Consulta tabelas diárias que se sobrepõem ao intervalo de tempo entre <timestamp1> e <timestamp2>.

Os nomes de tabelas precisam ter o seguinte formato: <prefix><day>, em que <day> está no formato YYYYMMDD.

Use as funções de data e hora para gerar os parâmetros de carimbo de data/hora. Por exemplo:

• TIMESTAMP('2012-10-01 02:03:04')
• DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')

Exemplo: receber tabelas entre dois dias

Este exemplo pressupõe as seguintes tabelas:

• mydata.people20140325
• mydata.people20140326
• mydata.people20140327

#legacySQL
SELECT
  name
FROM
  TABLE_DATE_RANGE([myproject-1234:mydata.people],
                    TIMESTAMP('2014-03-25'),
                    TIMESTAMP('2014-03-27'))
WHERE
  age >= 35

Correspondem às seguintes tabelas:

• mydata.people20140325
• mydata.people20140326
• mydata.people20140327

Exemplo: conseguir tabelas em um intervalo de dois dias até "agora"

Neste exemplo, supomos que as seguintes tabelas estão em um projeto chamado myproject-1234:

• mydata.people20140323
• mydata.people20140324
• mydata.people20140325

#legacySQL
SELECT
  name
FROM
  (TABLE_DATE_RANGE([myproject-1234:mydata.people],
                    DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'),
                    CURRENT_TIMESTAMP()))
WHERE
  age >= 35

Corresponde às seguintes tabelas:

• mydata.people20140323
• mydata.people20140324
• mydata.people20140325

TABLE_DATE_RANGE_STRICT(prefix, timestamp1, timestamp2)

Esta função é equivalente a TABLE_DATE_RANGE. A única diferença é que, se alguma tabela diária estiver ausente na sequência, TABLE_DATE_RANGE_STRICT falhará e retornará um erro Not Found: Table <table_name>.

Exemplo: erro na tabela que está faltando

Este exemplo pressupõe que existam as seguintes tabelas:

• people20140325
• people20140327

#legacySQL
SELECT
  name
FROM
  (TABLE_DATE_RANGE_STRICT([myproject-1234:mydata.people],
                    TIMESTAMP('2014-03-25'),
                    TIMESTAMP('2014-03-27')))
WHERE age >= 35

O exemplo acima retorna um erro "Não encontrado" para a tabela "people20140326".

TABLE_QUERY(dataset, expr)

Consulta tabelas em que os nomes correspondem ao expr fornecido. O parâmetro expr precisa ser representado como uma string e precisa conter uma expressão para avaliação. Por exemplo, 'length(table_id) < 3'.

Exemplo: tabelas de correspondência em que os nomes contêm "oo" e têm um comprimento maior que 4

Este exemplo pressupõe que existam as seguintes tabelas:

• mydata.boo
• mydata.fork
• mydata.ooze
• mydata.spoon

#legacySQL
SELECT
  speed
FROM (TABLE_QUERY([myproject-1234:mydata],
                  'table_id CONTAINS "oo" AND length(table_id) >= 4'))

Correspondem às seguintes tabelas:

• mydata.ooze
• mydata.spoon

Exemplo: tabelas de correspondência que tenham nomes iniciados com "boo", seguidos por 3-5 dígitos numéricos

Neste exemplo, supomos que as seguintes tabelas estão em um projeto chamado myproject-1234:

• mydata.book4
• mydata.book418
• mydata.boom12345
• mydata.boom123456789
• mydata.taboo999

#legacySQL
SELECT
  speed
FROM
  TABLE_QUERY([myproject-1234:mydata],
               'REGEXP_MATCH(table_id, r"^boo[\d]{3,5}")')

Corresponde às seguintes tabelas:

• mydata.book418
• mydata.boom12345

Funções de URL

Sintaxe

Funções de URL
HOST()	Dado um URL, retorna o nome do host como uma string.
DOMAIN()	Dado um URL, retorna o domínio como uma string.
TLD()	Dada um URL, retorna o domínio de nível superior mais qualquer domínio de país no URL.

HOST('url_str')

Dado um URL, retorna o nome do host como uma string. Exemplo: HOST('http://www.google.com:80/index.html') retorna 'www.google.com'

DOMAIN('url_str')

Dado um URL, retorna o domínio como uma string. Exemplo: DOMAIN('http://www.google.com:80/index.html') retorna 'google.com'.

TLD('url_str')

Dado um URL, retorna o domínio de nível superior mais qualquer domínio de país no URL. Exemplo: o domínio TLD('http://www.google.com:80/index.html') retorna '.com'. TLD('http://www.google.co.uk:80/index.html') retorna '.co.uk'.

Observações:

• Essas funções não realizam pesquisa de DNS inversa, portanto, se você chamá-las usando um endereço IP, elas retornarão segmentos do endereço IP em vez de segmentos do nome do host.
• Todas as funções de análise de URL esperam caracteres em minúsculas. Os caracteres em maiúsculas no URL causarão um resultado NULL ou incorreto de outra forma. Considere passar a entrada para essa função por meio de LOWER () se os dados tiverem letras maiúsculas e minúsculas.

Exemplo avançado

Analisar nomes de domínio a partir de dados de URL

Esta consulta usa a função DOMAIN() para retornar os domínios mais conhecidos listados como páginas iniciais do repositório no GitHub. Observe o uso de HAVING para filtrar registros usando o resultado da função DOMAIN(). Esta função é útil para determinar as informações do referenciador a partir de dados de URL.

Exemplos:

#legacySQL
SELECT
  DOMAIN(repository_homepage) AS user_domain,
  COUNT(*) AS activity_count
FROM
  [bigquery-public-data:samples.github_timeline]
GROUP BY
  user_domain
HAVING
  user_domain IS NOT NULL AND user_domain != ''
ORDER BY
  activity_count DESC
LIMIT 5;

Retorna:

+-----------------+----------------+
|   user_domain   | activity_count |
+-----------------+----------------+
| github.com      |         281879 |
| google.com      |          34769 |
| khanacademy.org |          17316 |
| sourceforge.net |          15103 |
| mozilla.org     |          14091 |
+-----------------+----------------+

Para analisar especificamente as informações de TLD, use a função TLD(). Este exemplo exibe as principais TLDs que não estão na lista de exemplos comuns.

#legacySQL
SELECT
  TLD(repository_homepage) AS user_tld,
  COUNT(*) AS activity_count
FROM
  [bigquery-public-data:samples.github_timeline]
GROUP BY
  user_tld
HAVING
  /* Only consider TLDs that are NOT NULL */
  /* or in our list of common TLDs */
  user_tld IS NOT NULL AND NOT user_tld
  IN ('','.com','.net','.org','.info','.edu')
ORDER BY
  activity_count DESC
LIMIT 5;

Retorna:

+----------+----------------+
| user_tld | activity_count |
+----------+----------------+
| .de      |          22934 |
| .io      |          17528 |
| .me      |          13652 |
| .fr      |          12895 |
| .co.uk   |           9135 |
+----------+----------------+

Funções de janela

As funções de janela, também conhecidas como funções analíticas, permitem cálculos em um subconjunto específico, ou "janela", de um conjunto de resultados. As funções de janela facilitam a criação de relatórios que incluem análises complexas, como médias dos últimos períodos e totais em execução.

Cada função de janela requer uma cláusula OVER que especifica a parte superior e inferior da janela. Os três componentes da cláusula OVER (particionamento, ordenação e enquadramento) fornecem controle adicional sobre a janela. O particionamento permite que você divida os dados de entrada em grupos lógicos que têm uma característica comum. A ordenação permite ordenar os resultados dentro de uma partição. O enquadramento permite criar um quadro de janela deslizante dentro de uma partição que se move em relação à linha atual. Você pode configurar o tamanho do quadro da janela móvel com base em um número de linhas ou um intervalo de valores, como um intervalo de tempo.

#legacySQL
SELECT <window_function>
  OVER (
      [PARTITION BY <expr>]
      [ORDER BY <expr> [ASC | DESC]]
      [<window-frame-clause>]
     )

PARTITION BY

Define a partição base sobre a qual essa função opera. Especifique um ou mais nomes de coluna separados por vírgulas; uma partição será criada para cada conjunto distinto de valores para essas colunas, semelhante a uma cláusula GROUP BY. Se PARTITION BY for omitido, a partição base será todas as linhas na entrada para a função da janela.

A cláusula PARTITION BY também permite que as funções de janela particionem dados e paralelize a execução. Se você quer usar uma função de janela com allowLargeResults ou se quer aplicar mais junções ou agregações à saída da função de janela, use PARTITION BY para colocar em execução paralelamente.

JOIN EACH e GROUP EACH BY não podem ser usadas na saída de funções de janela. Para gerar resultados de consulta grandes ao usar funções de janela, use PARTITION BY.

ORDER BY

Classifica a partição. Se ORDER BY estiver ausente, não há garantia de ordem de classificação padrão. A classificação ocorre no nível da partição, antes de qualquer cláusula de quadro de janela ser aplicada. Se você especificar uma janela RANGE, adicione uma cláusula ORDER BY. A ordem padrão é ASC.

ORDER BY é opcional em alguns casos, mas algumas funções de janela, como rank() ou dense_rank(), exigem a cláusula.

Se você usar ORDER BY sem especificar ROWS ou RANGE, ORDER BY implica que a janela se estende desde o início da partição até a linha atual. Na ausência de uma cláusula ORDER BY, a janela é toda a partição.

<window-frame-clause>

{ROWS | RANGE} {BETWEEN <start> AND <end> | <start> | <end>}

Um subconjunto da partição sobre a qual operar. Pode ser do mesmo tamanho da partição ou menor. Se você usar ORDER BY sem window-frame-clause, o frame de janela padrão será RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Se você omitir ORDER BY e window-frame-clause, o frame de janela padrão será a partição inteira.

• ROWS - define uma janela em termos de posição da linha, relativa à linha atual. Por exemplo, para adicionar uma coluna que mostre a soma das cinco linhas de valores salariais precedentes, consulte 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 dos pares de CURRENT ROW são incluídas em um frame de janela que especifica CURRENT ROW. Por exemplo, se você especificar o fim da janela a ser CURRENT ROW e a seguinte linha na janela tiver o mesmo valor, ela será incluída no cálculo da função.
• BETWEEN <start> AND <end>: um intervalo, incluindo as linhas de início e término. O intervalo não precisa incluir a linha atual, mas <start> precisa preceder ou ser igual a <end>.
• <start> - especifica o deslocamento inicial para esta janela, relativo à linha atual. As opções a seguir são aceitas:

  {UNBOUNDED PRECEDING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}

  em que <expr> é um número inteiro positivo, PRECEDING. indica um número de linha ou valor de intervalo anterior e FOLLOWING indica um número de linha ou valor de intervalo posterior. UNBOUNDED PRECEDING significa a primeira linha da partição. Se o início preceder a janela, ele será definido como a primeira linha da partição.
• <end> - especifica o deslocamento final da janela em relação à linha atual. As seguintes opções são aceitas:

  {UNBOUNDED FOLLOWING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}

  em que <expr> é um número inteiro positivo, PRECEDING indica um número de linha ou valor de intervalo anterior e FOLLOWING indica um número de linha ou valor de intervalo 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 frame da janela ...
NTILE()	Divide a janela no número especificado de intervalos.
PERCENT_RANK()	Retorna a classificação da linha atual, relativa às outras linhas da partição.
PERCENTILE_CONT()	Retorna um valor interpolado que estaria correlacionado ao argumento percentil com relação à janela...
PERCENTILE_DISC()	Retorna o valor mais próximo do percentil do argumento sobre a janela.
RANK()	Retorna a classificação do número inteiro de um valor em um grupo de valores.
RATIO_TO_REPORT()	Retorna a proporção de cada valor para a soma dos valores.
ROW_NUMBER()	Retorna o número da linha atual do resultado da consulta sobre a janela.

AVG(numeric_expr)
COUNT(*)
COUNT([DISTINCT] field)
MAX(field)
MIN(field)
STDDEV(numeric_expr)
SUM(field)

Essas funções de janela executam a mesma operação das funções Aggregate, mas são calculadas sobre uma janela definida pela cláusula OVER.

Outra diferença significativa é que a função COUNT([DISTINCT] field) produz resultados exatos quando usada como uma função de janela, com comportamento semelhante à função de agregação EXACT_COUNT_DISTINCT().

Na consulta de exemplo, a cláusula ORDER BY faz com que a janela seja computada do início da partição para a linha atual, o que gera uma soma cumulativa para esse ano.

#legacySQL
SELECT
   corpus_date,
   corpus,
   word_count,
   SUM(word_count) OVER (
     PARTITION BY corpus_date
     ORDER BY word_count) annual_total
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   word='love'
ORDER BY
   corpus_date, word_count
        

Retorna:

corpus_date	corpus	word_count	annual_total
0	various	37	37
0	sonnets	157	194
1590	2kinghenryvi	18	18
1590	1kinghenryvi	24	42
1590	3kinghenryvi	40	82

CUME_DIST()

Retorna um duplo que indica a distribuição cumulativa de um valor em um grupo de valores, calculado usando a fórmula <number of rows preceding or tied with the current row> / <total rows>. Valores vinculados retornam o mesmo valor de distribuição cumulativa.

Essa função de janela requer ORDER BY na clá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().

Essa função de janela requer 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 a linha localizada <offset> linhas antes da linha atual. Se a linha não existir, <default_value> retornará.

#legacySQL
SELECT
   word,
   word_count,
   LAG(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lag,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

Retorna:

word	word_count	lag
handkerchief	29	null
satisfaction	5	handkerchief
displeasure	4	satisfaction
instruments	4	displeasure
circumstance	3	instruments

LAST_VALUE(<field_name>)

Retorna o último valor de <field_name> na janela.

#legacySQL
SELECT
   word,
   word_count,
   LAST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) lv,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 1

Retorna:

word	word_count	lv
imperfectly	1	imperfectly

LEAD(<expr>[, <offset>[, <default_value>]])

Permite ler dados de uma linha seguinte dentro de uma janela. Especificamente, LEAD() retorna o valor de <expr> para a linha localizada <offset> linhas após a linha atual. Se a linha não existir, <default_value> retornará.

#legacySQL
SELECT
   word,
   word_count,
   LEAD(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lead,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

Retorna:

word	word_count	lead
handkerchief	29	satisfaction
satisfaction	5	displeasure
displeasure	4	instruments
instruments	4	circumstance
circumstance	3	null

NTH_VALUE(<expr>, <n>)

Retorna o valor de <expr> na posição <n> do frame da janela, em que <n> é um índice com base em um.

NTILE(<num_buckets>)

Divide uma sequência de linhas em intervalos <num_buckets> e atribui um número de bloco correspondente, como um número inteiro, com cada linha. A função ntile() atribui os números do intervalo da forma mais uniforme possível e retorna um valor de 1 a <num_buckets> para cada linha.

#legacySQL
SELECT
   word,
   word_count,
   NTILE(2) OVER (PARTITION BY corpus ORDER BY word_count DESC) ntile,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

Retorna:

word	word_count	ntile
handkerchief	29	1
satisfaction	5	1
displeasure	4	1
instruments	4	2
circumstance	3	2

PERCENT_RANK()

Retorna a classificação da linha atual, em relação às outras linhas da partição. Os valores retornados variam entre 0 e 1, inclusive. O primeiro valor retornado é 0,0.

Essa função de janela requer ORDER BY na clá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 seria mapeado para o argumento de percentil em relação à janela, depois de ordená-los de acordo com a cláusula ORDER BY.

<percentile> precisa estar entre 0 e 1.

Essa função de janela requer ORDER BY na clá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.

Essa função de janela requer 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().

Essa função de janela requer 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 em formato de BYTES.
HASH()	Calcula e retorna um valor de hash assinado de 64 bits.
FARM_FINGERPRINT()	Calcula e retorna um valor de impressão digital assinado de 64 bits.
IF()	Se o primeiro argumento for true, retornará o segundo argumento. Caso contrário, retornará o terceiro.
POSITION()	Retorna a posição sequencial baseada em 1 do argumento.
SHA1()	Retorna um hash SHA1 no formato BYTES.
SOME()	Retornará true se o argumento for true para pelo menos uma das entradas.
TO_BASE64()	Converte o argumento BYTES em uma string codificada em base-64.

CASE WHEN when_expr1 THEN then_expr1
  WHEN when_expr2 THEN then_expr2 ...
  ELSE else_expr END

Use CASE para escolher entre duas ou mais expressões alternativas na consulta. Expressões WHEN precisam ser booleanas, e todas as expressões em cláusulas THEN e ELSE precisam ter tipos compatíveis.

CURRENT_USER()

Retorna o endereço de e-mail do usuário que está executando a consulta.

EVERY(<condition>)

Retorna true se condition for verdadeiro para todas as suas entradas. Quando usada com a cláusula OMIT IF, essa função é útil para consultas que envolvem campos repetidos.

FROM_BASE64(<str>)

Converte str string de entrada codificada como base64 no formato BYTES. Para converter BYTES em uma string codificada em base64, TO_BASE64().

HASH(expr)

Calcula e retorna um valor de hash assinado de 64 bits dos bytes de expr, conforme definido pela biblioteca CityHash (versão 1.0.3). Qualquer expressão de string ou número inteiro é compatível e a função respeita IGNORE CASE para strings, retornando valores invariantes de caso.

FARM_FINGERPRINT(expr)

Calcula e retorna um valor de impressão digital assinado de 64 bits da entrada STRING ou BYTES, usando a função Fingerprint64 a partir da biblioteca FarmHash de código aberto. A saída dessa função para uma entrada específica nunca vai mudar e corresponde à saída da função FARM_FINGERPRINT ao usar o GoogleSQL. Respeita IGNORE CASE para strings, retornando valores invariantes de caso.

IF(condition, true_return, false_return)

Retorna true_return ou false_return, dependendo de condition ser verdadeiro ou falso. Os valores retornados podem ser literais ou valores derivados de campo, mas precisam ter o mesmo tipo de dados. Os valores derivados de campo não precisam ser incluídos na cláusula SELECT.

POSITION(field)

Retorna a posição sequencial baseada em 1 do campo dentro de um conjunto de campos repetidos.

SHA1(<str>)

Retorna um hash SHA1, no formato BYTES, da string de entrada str. Converta o resultado para base64 usando TO_BASE64(). Por exemplo:

#legacySQL
SELECT
  TO_BASE64(SHA1(corpus))
FROM
  [bigquery-public-data:samples.shakespeare]
LIMIT
  100;

SOME(<condition>)

Retorna true se condition for verdadeiro para pelo menos uma de suas entradas. Quando usada com a cláusula OMIT IF, essa função é útil para consultas que envolvem campos repetidos.

TO_BASE64(<bin_data>)

Converte bin_data da entrada BYTES em uma string codificada em base64. Por exemplo:

#legacySQL
SELECT
  TO_BASE64(SHA1(title))
FROM
  [bigquery-public-data:samples.wikipedia]
LIMIT
  100;

Para converter uma string codificada em base64 em BYTES, utilize FROM_BASE64().

Exemplos avançados

• Organização de resultados em categorias usando condicionais

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

  Exemplo:

  #legacySQL
  SELECT
    CASE
      WHEN state IN ('WA', 'OR', 'CA', 'AK', 'HI', 'ID',
                     'MT', 'WY', 'NV', 'UT', 'CO', 'AZ', 'NM')
        THEN 'West'
      WHEN state IN ('OK', 'TX', 'AR', 'LA', 'TN', 'MS', 'AL',
                     'KY', 'GA', 'FL', 'SC', 'NC', 'VA', 'WV',
                     'MD', 'DC', 'DE')
        THEN 'South'
      WHEN state IN ('ND', 'SD', 'NE', 'KS', 'MN', 'IA',
                     'MO', 'WI', 'IL', 'IN', 'MI', 'OH')
        THEN 'Midwest'
      WHEN state IN ('NY', 'PA', 'NJ', 'CT',
                     'RI', 'MA', 'VT', 'NH', 'ME')
        THEN 'Northeast'
      ELSE 'None'
    END as region,
    average_mother_age,
    average_father_age,
    state, year
  FROM
    (SELECT
       year, state,
       SUM(mother_age)/COUNT(mother_age) as average_mother_age,
       SUM(father_age)/COUNT(father_age) as average_father_age
     FROM
       [bigquery-public-data:samples.natality]
     WHERE
       father_age < 99
     GROUP BY
       year, state)
  ORDER BY
    year
  LIMIT 5;
  

  Retorna:

  +--------+--------------------+--------------------+-------+------+
  | region | average_mother_age | average_father_age | state | year |
  +--------+--------------------+--------------------+-------+------+
  | South  | 24.342600163532296 | 27.683769419460344 | AR    | 1969 |
  | West   | 25.185041908446163 | 28.268214055448098 | AK    | 1969 |
  | West   | 24.780776677578217 | 27.831181063905248 | CA    | 1969 |
  | West   | 25.005834769924412 | 27.942978384829598 | AZ    | 1969 |
  | South  | 24.541730952905738 | 27.686430093306885 | AL    | 1969 |
  +--------+--------------------+--------------------+-------+------+
  

• Simulação de uma tabela dinâmica

  Use declarações condicionais para organizar os resultados de uma consulta subselect em linhas e colunas. No exemplo a seguir, os resultados de uma pesquisa pelos artigos mais revisados da Wikipédia que começam com o valor 'Google' são organizados em colunas, em que a revisão é considerada se satisfaz a um conjunto de critérios.

  Exemplo:

  #legacySQL
  SELECT
    page_title,
    /* Populate these columns as True or False, */
    /*  depending on the condition */
    IF (page_title CONTAINS 'search',
        INTEGER(total), 0) AS search,
    IF (page_title CONTAINS 'Earth' OR
        page_title CONTAINS 'Maps', INTEGER(total), 0) AS geo,
  FROM
    /* Subselect to return top revised Wikipedia articles */
    /* containing 'Google', followed by additional text. */
    (SELECT
      TOP (title, 5) as page_title,
      COUNT (*) as total
     FROM
       [bigquery-public-data:samples.wikipedia]
     WHERE
       REGEXP_MATCH (title, r'^Google.+') AND wp_namespace = 0
    );
  

  Retorna:

  +---------------+--------+------+
  |  page_title   | search | geo  |
  +---------------+--------+------+
  | Google search |   4261 |    0 |
  | Google Earth  |      0 | 3874 |
  | Google Chrome |      0 |    0 |
  | Google Maps   |      0 | 2617 |
  | Google bomb   |      0 |    0 |
  +---------------+--------+------+
  

• Como usar HASH para selecionar uma amostra aleatória dos dados

  Algumas consultas podem fornecer um resultado útil usando subamostragem aleatória do conjunto de resultados. Para recuperar uma amostragem aleatória de valores, use a função HASH para retornar resultados em que o módulo "n" do hash seja igual a zero.

  Por exemplo, a consulta a seguir encontrará o HASH() do valor do "título" e, em seguida, verificará se esse valor do módulo "2" é zero. Isso precisa resultar em cerca de 50% dos valores rotulados como "amostrados". Para amostrar menos valores, aumente o valor da operação de módulo de "2" para algo maior. Na consulta, a funçã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;