Sintaxe, funções e operadores do SQL antigo

Este documento detalha a sintaxe, as funções e os operadores de consultas SQL antigas. A sintaxe de consulta preferida para o BigQuery é o GoogleSQL. Para informações sobre o GoogleSQL, consulte o artigo Sintaxe de consulta do GoogleSQL.

Sintaxe de consulta

Nota: as palavras-chave não são sensíveis a maiúsculas e minúsculas. Neste documento, as palavras-chave, como SELECT, são escritas em maiúsculas para fins ilustrativos.

Cláusula SELECT

A cláusula SELECT especifica uma lista de expressões a calcular. As expressões na cláusula SELECT podem conter nomes de campos, literais e chamadas de funções (incluindo funções de agregação e funções de janela), bem como combinações dos três. A lista de expressões está separada por vírgulas.

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

Notas:

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

• Pode usar parênteses retos para escapar a palavras reservadas para que as possa usar como nome de campo e alias. Por exemplo, se tiver uma coluna denominada "partition", que é uma palavra reservada na sintaxe do BigQuery, as consultas que fazem referência a esse campo falham com mensagens de erro obscuras, a menos que o interprete de forma literal com parênteses retos:

  SELECT [partition] FROM ...

Exemplo

Este exemplo define aliases na cláusula SELECT e, em seguida, faz referência a um deles na cláusula ORDER BY. Tenha em atenção que não é possível fazer referência à coluna word através do word_alias na cláusula WHERE. Tem de fazer referência à coluna pelo nome. O alias len também não é visível na cláusula WHERE. Seria visível para uma cláusula de 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 agregue valores repetidos em cada registo. Para cada registo de entrada, é produzido exatamente um resultado agregado. Este tipo de agregação é denominado agregação com âmbito. Uma vez que a agregação ao nível do âmbito produz resultados para cada registo, é possível selecionar expressões não agregadas juntamente com expressões agregadas ao nível do âmbito sem usar uma cláusula GROUP BY.

Normalmente, usa o âmbito RECORD quando usa a agregação com âmbito. Se tiver um esquema aninhado e repetido muito complexo, pode ter de fazer agregações em âmbitos de sub-registos. Isto pode ser feito substituindo a palavra-chave RECORD na sintaxe acima pelo nome do nó no seu esquema onde quer que a agregação seja realizada. Para mais informações sobre esse comportamento avançado, consulte o artigo Lidar com dados.

Exemplo

Este exemplo executa uma agregação com âmbito de COUNT e, em seguida, filtra e ordena os registos 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 consultar. As consultas do BigQuery podem ser executadas diretamente em tabelas, subconsultas, tabelas unidas e tabelas modificadas por operadores especiais descritos abaixo. Pode consultar combinações destas origens de dados usando a vírgula, que é o operador UNION ALL no BigQuery.

Tabelas de referência

Quando fizer referência a uma tabela, tem de especificar datasetId e tableId; project_name é opcional. Se project_name não for especificado, o BigQuery usa o projeto atual por predefinição. Se o nome do projeto incluir um travessão, tem de colocar toda a referência da tabela entre parênteses.

Exemplo

[my-dashed-project:dataset1.tableName]

Pode atribuir um alias às tabelas adicionando um espaço seguido de um identificador após o nome da tabela. A palavra-chave AS opcional pode ser adicionada entre o tableId e o alias para melhorar a legibilidade.

Quando faz referência a colunas de uma tabela, pode usar o nome simples da coluna ou prefixar o nome da coluna com o alias, se tiver especificado um, ou com o datasetId e o tableId, desde que não tenha sido especificado nenhum project_name. Não é possível incluir project_name no prefixo da coluna porque o caráter dois pontos não é permitido nos nomes dos campos.

Exemplos

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

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

Este exemplo prefixa o nome da coluna com o datasetId e o tableId. Tenha em atenção que o project_name não pode ser incluído neste exemplo. Este método só funciona se o conjunto de dados estiver no seu projeto predefinido atual.

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

Este exemplo prefixa o nome da coluna com um alias da tabela.

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

Tabelas particionadas por intervalo de números inteiros

O SQL antigo suporta a utilização de decoradores de tabelas para aceder a uma partição específica numa tabela particionada por intervalo de números inteiros. A chave para aceder a uma partição de intervalo é o início do intervalo.

O exemplo seguinte consulta a partição de intervalo que começa com 30:

#legacySQL
SELECT
  *
FROM
  dataset.table$30;

Tenha em atenção que não pode usar o SQL antigo para consultar uma tabela particionada por intervalo de números inteiros completa. Em vez disso, a consulta devolve um erro semelhante ao seguinte:

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

Usar subconsultas

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

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

Vírgula como UNION ALL

Ao contrário do GoogleSQL, o SQL antigo usa a vírgula como um operador UNION ALL em vez de um operador CROSS JOIN. Este é um comportamento antigo que evoluiu porque, historicamente, o BigQuery não suportava CROSS JOIN e os utilizadores do BigQuery precisavam regularmente de escrever consultas UNION ALL. No GoogleSQL, as consultas que executam uniões são particularmente detalhadas. A utilização da vírgula como operador de união permite escrever essas consultas de forma muito mais eficiente. Por exemplo, esta consulta pode ser usada para executar uma única consulta em registos 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;

As consultas que unem um grande número de tabelas são normalmente executadas mais lentamente do que as consultas que processam a mesma quantidade de dados de uma única tabela. A diferença no desempenho pode ser de até 50 ms por tabela adicional. Uma única consulta pode unir, no máximo, 1000 tabelas.

Funções de carateres universais de tabelas

O termo função de carateres universais de tabela refere-se a um tipo especial de função exclusivo do BigQuery. Estas funções são usadas na cláusula FROM para fazer corresponder uma coleção de nomes de tabelas usando um de vários tipos de filtros. Por exemplo, a função TABLE_DATE_RANGE pode ser usada para consultar apenas um conjunto específico de tabelas diárias. Para mais informações sobre estas funções, consulte o artigo Funções de carateres universais de tabelas.

Operador FLATTEN

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

Ao contrário dos sistemas de processamento de SQL típicos, o BigQuery foi concebido para processar dados repetidos. Por este motivo, os utilizadores do BigQuery têm, por vezes, de escrever consultas que manipulam a estrutura dos registos repetidos. Uma forma de o fazer é usar o operador FLATTEN.

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

Para mais informações e exemplos, consulte o artigo Lidar com dados.

Operador JOIN

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

Tipos de JOIN

O BigQuery suporta operações INNER, [FULL|RIGHT|LEFT] OUTER e CROSS JOIN. Se não for especificado, o valor predefinido é INNER.

As operações CROSS JOIN não permitem cláusulas ON. CROSS JOIN pode devolver uma grande quantidade de dados e resultar numa consulta lenta e ineficiente ou numa consulta que excede os recursos máximos permitidos por consulta. Essas consultas vão falhar com um erro. Sempre que possível, prefira consultas que não usem CROSS JOIN. Por exemplo, CROSS JOIN é frequentemente usado em locais onde as funções de janela seriam mais eficientes.

Modificador EACH

O modificador EACH é uma sugestão que indica ao BigQuery para executar a função JOIN usando várias partições. Isto é particularmente útil quando sabe que ambos os lados do JOIN são grandes. Não é possível usar o modificador EACH em cláusulas CROSS JOIN.

EACH era incentivado em muitos casos, mas já não é assim. Sempre que possível, use JOIN sem o modificador EACH para um melhor desempenho. Use JOIN EACH quando a sua consulta falhar com uma mensagem de erro de recursos excedidos.

Semi-join e anti-join

Além de suportar JOIN na cláusula FROM, o BigQuery também suporta dois tipos de junções na cláusula WHERE: junção parcial e junção parcial anti. Uma junção parcial é especificada através da palavra-chave IN com uma subconsulta; uma antijunção, através das palavras-chave NOT IN.

Exemplos

A seguinte consulta usa uma junção parcial para encontrar n-gramas em que a primeira palavra no n-grama é também a segunda palavra noutro n-grama que tem "E" como a terceira palavra no n-grama.

#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 seguinte usa uma junção parcial para devolver o número de mulheres com mais de 50 anos que deram à luz nos 10 estados com mais nascimentos.

#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, pode usar uma anti-junção. A consulta seguinte é quase idêntica ao exemplo anterior, mas usa NOT IN em vez de IN para devolver 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

Notas:

• O BigQuery não suporta junções semijuntas ou antissemijuntas correlacionadas. A subconsulta não pode fazer referência a nenhum campo da consulta externa.
• A subconsulta usada numa junção semi ou antissemi tem de selecionar exatamente um campo.
• Os tipos do campo selecionado e do campo usado na consulta externa na cláusula WHERE têm de corresponder exatamente. O BigQuery não faz nenhuma coerção de tipo para junções semi ou anti-semi.

Cláusula WHERE

A cláusula WHERE, por vezes denominada predicado, filtra os registos produzidos pela cláusula FROM através de uma expressão booleana. É possível unir várias condições com cláusulas booleanas AND e OR, opcionalmente entre parênteses ( ) para as agrupar. Os campos apresentados numa cláusula WHERE não têm de ser selecionados na cláusula SELECT correspondente, e a expressão da cláusula WHERE não pode fazer referência a expressões calculadas na cláusula SELECT da consulta à qual a cláusula WHERE pertence.

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

Exemplo

O exemplo seguinte usa uma disjunção de expressões booleanas na cláusula WHERE —as duas expressões unidas por um operador OR. Um registo de entrada passa pelo filtro WHERE se qualquer uma das expressões devolver 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');

OMIT RECORD IF clause

A cláusula OMIT RECORD IF é uma construção exclusiva do BigQuery. É particularmente útil para lidar com esquemas aninhados e repetidos. É semelhante a uma cláusula WHERE , mas difere em dois aspetos importantes. Primeiro, usa uma condição de exclusão, o que significa que os registos são omitidos se a expressão devolver true, mas mantidos se a expressão devolver false ou null. Em segundo lugar, a cláusula OMIT RECORD IF pode (e normalmente usa) funções de agregação com âmbito na sua condição.

Além de filtrar registos completos, OMIT...IF pode especificar um âmbito mais restrito para filtrar apenas partes de um registo. Isto é feito usando o nome de um nó não folha no seu esquema, em vez de RECORD na sua cláusula OMIT...IF. Esta funcionalidade é raramente usada pelos utilizadores do BigQuery. Pode encontrar mais documentação sobre este comportamento avançado com hiperligação na documentação WITHIN acima.

Se usar OMIT...IF para excluir uma parte de um registo num campo repetido e a consulta também selecionar outros campos repetidos independentemente, o BigQuery omite uma parte dos outros registos repetidos na consulta. Se vir o erro Cannot perform OMIT IF on repeated scope <scope> with independently repeating pass through field <field>, recomendamos que mude para o GoogleSQL. Para informações sobre a migração de declarações OMIT...IF para o GoogleSQL, consulte o artigo Migrar para o GoogleSQL.

Exemplo

Voltando ao exemplo usado para o modificador WITHIN, pode usar OMIT RECORD IF para fazer o mesmo que WITHIN e HAVING faziam 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-lhe agrupar linhas que têm os mesmos valores para um determinado campo ou conjunto de campos, para que possa calcular agregações de campos relacionados. O agrupamento ocorre após a filtragem realizada na cláusula WHERE, mas antes de as expressões na cláusula SELECT serem calculadas. Não é possível usar os resultados da expressão como chaves de grupo na cláusula GROUP BY.

Exemplo

Esta consulta encontra as dez primeiras palavras mais comuns no conjunto de dados de exemplo de trigramas. Além de demonstrar a utilização da cláusula GROUP BY, demonstra como os índices posicionais podem ser usados em vez dos nomes dos 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 com uma cláusula GROUP BY é denominada agregação agrupada . Ao contrário da agregação com âmbito, a agregação agrupada é comum na maioria dos sistemas de processamento de SQL.

O modificador EACH

O modificador EACH é uma sugestão que indica ao BigQuery para executar a função GROUP BY usando várias partições. Isto é particularmente útil quando sabe que o seu conjunto de dados contém um grande número de valores distintos para as chaves de grupo.

EACH era incentivado em muitos casos, mas já não é assim. A utilização de GROUP BY sem o modificador EACH oferece normalmente um melhor desempenho. Use GROUP EACH BY quando a sua consulta falhar com uma mensagem de erro de recursos excedidos.

A função ROLLUP

Quando a função ROLLUP é usada, o BigQuery adiciona linhas adicionais ao resultado da consulta que representam agregações agrupadas. Todos os campos indicados após ROLLUP têm de estar incluídos num único conjunto de parênteses. Nas linhas adicionadas devido à função ROLLUP, NULL indica as colunas para as quais a agregação é resumida.

Exemplo

Esta consulta gera contagens por ano de nascimentos masculinos e femininos a partir do conjunto de dados de natalidade de 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. Repare que existem linhas em que uma ou ambas as chaves do grupo são NULL. Estas linhas são as linhas de agregação.

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

Quando usar a função ROLLUP, pode usar a função GROUPING para distinguir entre linhas que foram adicionadas devido à função ROLLUP e linhas que têm efetivamente um valor NULL para a chave do grupo.

Exemplo

Esta consulta adiciona a função GROUPING ao exemplo anterior para identificar melhor as linhas adicionadas devido à função 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;

Este é o resultado devolvido pela nova consulta.

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

Notas:

• Os campos não agregados na cláusula SELECT têm de ser apresentados 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. */

• Não é possível usar expressões calculadas na cláusula SELECT 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. */

• O agrupamento por valores flutuantes e duplos não é suportado, porque a função de igualdade para esses tipos não está bem definida.
• Uma vez que o sistema é interativo, as consultas que produzem um grande número de grupos podem falhar. A utilização da TOPfunção em vez de GROUP BY pode resolver alguns problemas de dimensionamento.

Cláusula HAVING

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

Exemplo

Esta consulta calcula as palavras primeiras mais comuns no conjunto de dados de exemplo de n-gramas 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 ordena os resultados de uma consulta por ordem ascendente ou descendente usando um ou mais campos principais. Para ordenar por vários campos ou alias, introduza-os como uma lista separada por vírgulas. Os resultados são ordenados pelos campos na ordem em que são apresentados. Use DESC (descendente) ou ASC (ascendente) para especificar a direção da ordenação. ASC é a predefinição. Pode especificar uma direção de ordenação diferente para cada chave de ordenação.

A cláusula ORDER BY é avaliada após a cláusula SELECT para poder fazer referência ao resultado de qualquer expressão calculada na cláusula SELECT. Se um campo receber um alias na cláusula SELECT, o alias tem de ser usado na cláusula ORDER BY.

Cláusula LIMIT

A cláusula LIMIT limita o número de linhas no conjunto de resultados devolvido. Uma vez que as consultas do BigQuery operam regularmente sobre um número muito elevado de linhas, LIMIT é uma boa forma de evitar consultas de longa duração processando apenas um subconjunto das linhas.

Notas:

• A cláusula LIMIT para o processamento e devolve resultados quando cumpre os seus requisitos. Isto pode reduzir o tempo de processamento de algumas consultas, mas quando especifica funções agregadas, como COUNT ou cláusulas ORDER BY, o conjunto de resultados completo tem de ser processado antes de devolver os resultados. A cláusula LIMIT é a última a ser avaliada.
• Uma consulta com uma cláusula LIMIT pode continuar a ser não determinística se não existir um operador na consulta que garanta a ordenação do conjunto de resultados de saída. Isto acontece porque o BigQuery é executado com um grande número de trabalhadores paralelos. A ordem em que as tarefas paralelas são devolvidas não é garantida.
• A cláusula LIMIT não pode conter funções; só aceita uma constante numérica.
• Quando a cláusula LIMIT é usada, o total de bytes processados e os bytes faturados podem variar para a mesma consulta.

Gramática de consulta

As cláusulas individuais das declarações SELECT do BigQuery são descritas detalhadamente acima. Aqui, apresentamos a gramática completa das SELECTdeclarações de forma compacta com links para as secçõ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:

• Os parênteses retos "[ ]" indicam cláusulas opcionais.
• As chavetas "{ }" incluem um conjunto de opções.
• A barra vertical "|" indica um OU lógico.
• Uma vírgula ou uma palavra-chave seguida de reticências entre parênteses retos "[, ... ]" indica que o item anterior pode repetir-se numa lista com o separador especificado.
• Os parênteses "( )" indicam parênteses literais.

Funções e operadores suportados

A maioria das cláusulas de declaração SELECT suporta funções. Os campos referenciados numa função não têm de ser apresentados numa cláusula SELECT. Por conseguinte, a seguinte consulta é válida, mesmo que o campo clicks não seja apresentado diretamente:

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

Funções de agregação
AVG()	Devolve a média dos valores de um grupo de linhas…
BIT_AND()	Devolve o resultado de uma operação AND bit-a-bit ...
BIT_OR()	Devolve o resultado de uma operação OU bit a bit ...
BIT_XOR()	Devolve o resultado de uma operação XOU bit a bit ...
CORR()	Devolve o coeficiente de correlação de Pearson de um conjunto de pares de números.
COUNT()	Devolve o número total de valores ...
COUNT([DISTINCT])	Devolve o número total de valores não NULL ...
COVAR_POP()	Calcula a covariância da população dos valores ...
COVAR_SAMP()	Calcula a covariância de amostra dos valores …
EXACT_COUNT_DISTINCT()	Devolve o número exato de valores distintos não NULL para o campo especificado.
FIRST()	Devolve o primeiro valor sequencial no âmbito da função.
GROUP_CONCAT()	Concatena várias strings numa única string…
GROUP_CONCAT_UNQUOTED()	Concatena várias strings numa única string … não adiciona aspas duplas …
LAST()	Devolve o último valor sequencial ...
MAX()	Devolve o valor máximo …
MIN()	Devolve o valor mínimo …
NEST()	Agrega todos os valores no âmbito de agregação atual num campo repetido.
NTH()	Devolve o enésimo valor sequencial ...
QUANTILES()	Calcula os valores mínimo, máximo e quantis aproximados…
STDDEV()	Devolve o desvio padrão ...
STDDEV_POP()	Calcula o desvio padrão da população ...
STDDEV_SAMP()	Calcula o desvio padrão amostral ...
SUM()	Devolve o total da soma dos valores ...
TOP() ... COUNT(*)	Devolve os registos superiores max_records por frequência.
UNIQUE()	Devolve o conjunto de valores únicos não NULL ...
VARIANCE()	Calcula a variância dos valores ...
VAR_POP()	Calcula a variância da população dos valores …
VAR_SAMP()	Calcula a variância de amostra dos valores ...

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

Funções bit a bit
&	AND bit a bit
|	OR bit-a-bit
^	XOU bit a bit
<<	Deslocamento bit a bit para a esquerda
>>	Deslocamento bit a bit para a direita
~	NOT bit a bit
BIT_COUNT()	Devolve o número de bits …

Funções de transmissão
BOOLEAN()	Converter para booleano.
BYTES()	Converter para bytes.
CAST(expr AS type)	Converte expr numa variável do tipo type.
FLOAT()	Transmitir para o dobro.
HEX_STRING()	Converte para string hexadecimal.
INTEGER()	Converter em número inteiro.
STRING()	Converter para string.

Funções de comparação
expr1 = expr2	Devolve true se as expressões forem iguais.
expr1 != expr2 expr1 <> expr2	Devolve true se as expressões não forem iguais.
expr1 > expr2	Devolve true se expr1 for superior a expr2.
expr1 < expr2	Devolve true se expr1 for inferior a expr2.
expr1 >= expr2	Devolve true se expr1 for superior ou igual a expr2.
expr1 <= expr2	Devolve true se expr1 for inferior ou igual a expr2.
expr1 BETWEEN expr2 AND expr3	Devolve true se o valor de expr1 estiver entre expr2 e expr3, inclusive.
expr IS NULL	Devolve true se expr for NULL.
expr IN()	Devolve true se expr corresponder a expr1, expr2 ou qualquer valor entre parênteses.
COALESCE()	Devolve o primeiro argumento que não seja NULL.
GREATEST()	Devolve o parâmetro numeric_expr mais elevado.
IFNULL()	Se o argumento não for nulo, devolve o argumento.
IS_INF()	Devolve true se for infinito positivo ou negativo.
IS_NAN()	Devolve true se o argumento for NaN.
IS_EXPLICITLY_DEFINED()	Descontinuado: em alternativa, use expr IS NOT NULL.
LEAST()	Devolve o parâmetro numeric_expr do argumento mais pequeno.
NVL()	Se expr não for nulo, devolve expr; caso contrário, devolve null_default.

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

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

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

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

Funções matemáticas
ABS()	Devolve o valor absoluto do argumento.
ACOS()	Devolve o arco cosseno do argumento.
ACOSH()	Devolve o arco cosseno hiperbólico do argumento.
ASIN()	Devolve o arco seno do argumento.
ASINH()	Devolve o arco seno hiperbólico do argumento.
ATAN()	Devolve a arcotangente do argumento.
ATANH()	Devolve a tangente hiperbólica inversa do argumento.
ATAN2()	Devolve a arcotangente dos dois argumentos.
CEIL()	Arredonda o argumento para o número inteiro mais próximo e devolve o valor arredondado.
COS()	Devolve o cosseno do argumento.
COSH()	Devolve o cosseno hiperbólico do argumento.
DEGREES()	Converte de radianos para graus.
EXP()	Devolve e elevado à potência do argumento.
FLOOR()	Arredonda o argumento para baixo, para o número inteiro mais próximo.
LN() LOG()	Devolve o logaritmo natural do argumento.
LOG2()	Devolve o logaritmo de base 2 do argumento.
LOG10()	Devolve o logaritmo de base 10 do argumento.
PI()	Devolve a constante π.
POW()	Devolve o primeiro argumento elevado à potência do segundo argumento.
RADIANS()	Converte de graus para radianos.
RAND()	Devolve um valor de ponto 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()	Devolve o seno do argumento.
SINH()	Devolve o seno hiperbólico do argumento.
SQRT()	Devolve a raiz quadrada da expressão.
TAN()	Devolve a tangente do argumento.
TANH()	Devolve a tangente hiperbólica do argumento.

Funções de expressões regulares
REGEXP_MATCH()	Devolve true se o argumento corresponder à expressão regular.
REGEXP_EXTRACT()	Devolve a parte do argumento que corresponde ao grupo de captura na expressão regular.
REGEXP_REPLACE()	Substitui uma substring que corresponde a uma expressão regular.

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

Funções de carateres universais de tabelas
TABLE_DATE_RANGE()	Consultar várias tabelas diárias que abrangem um intervalo de datas.
TABLE_DATE_RANGE_STRICT()	Consultas a várias tabelas diárias que abrangem um intervalo de datas, sem datas em falta.
TABLE_QUERY()	Consulta tabelas cujos nomes correspondem a um predicado especificado.

Funções de URL
HOST()	Dado um URL, devolve o nome do anfitrião como uma string.
DOMAIN()	Dado um URL, devolve o domínio como uma string.
TLD()	Dado um URL, devolve 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 que as funções de agregação correspondentes, mas são calculadas num período definido pela cláusula OVER.
CUME_DIST()	Devolve um valor duplo que indica a distribuição cumulativa de um valor num grupo de valores…
DENSE_RANK()	Devolve a classificação inteira de um valor num grupo de valores.
FIRST_VALUE()	Devolve o primeiro valor do campo especificado na janela.
LAG()	Permite-lhe ler dados de uma linha anterior numa janela.
LAST_VALUE()	Devolve o último valor do campo especificado na janela.
LEAD()	Permite-lhe ler dados de uma linha seguinte numa janela.
NTH_VALUE()	Devolve o valor de <expr> na posição <n> da moldura da janela ...
NTILE()	Divide a janela no número especificado de grupos.
PERCENT_RANK()	Devolve a classificação da linha atual, relativamente às outras linhas na partição.
PERCENTILE_CONT()	Devolve um valor interpolado que seria mapeado para o argumento de percentil relativamente à janela ...
PERCENTILE_DISC()	Devolve o valor mais próximo da percentagem do argumento na janela.
RANK()	Devolve a classificação inteira de um valor num grupo de valores.
RATIO_TO_REPORT()	Devolve a proporção de cada valor em relação à soma dos valores.
ROW_NUMBER()	Devolve o número da linha atual do resultado da consulta na janela.

Outras funções
CASE WHEN ... THEN	Use CASE para escolher entre duas ou mais expressões alternativas na sua consulta.
CURRENT_USER()	Devolve o endereço de email do utilizador que está a executar a consulta.
EVERY()	Devolve verdadeiro se o argumento for verdadeiro para todas as respetivas entradas.
FROM_BASE64()	Converte a string de entrada codificada em base64 no formato BYTES.
HASH()	Calcula e devolve um valor de hash com sinal de 64 bits…
FARM_FINGERPRINT()	Calcula e devolve um valor de impressão digital com sinal de 64 bits ...
IF()	Se o primeiro argumento for verdadeiro, devolve o segundo argumento; caso contrário, devolve o terceiro argumento.
POSITION()	Devolve a posição sequencial baseada em 1 do argumento.
SHA1()	Devolve um hash SHA1 no formato BYTES.
SOME()	Devolve verdadeiro se o argumento for verdadeiro para, pelo menos, uma das respetivas entradas.
TO_BASE64()	Converte o argumento BYTES numa string codificada em base-64.

Funções de agregação

As funções de agregação devolvem valores que representam resumos de conjuntos de dados maiores, o que torna estas funções particularmente úteis para analisar registos. Uma função de agregação opera contra uma coleção de valores e devolve um único valor por tabela, grupo ou âmbito:

• Agregação de tabelas

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

  SELECT COUNT(f1) FROM ds.Table;

• Agregação de grupos

  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. Por exemplo:

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

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

• Agregação com âmbito

  Esta funcionalidade aplica-se apenas a tabelas que tenham campos aninhados.
  Usa uma função de agregação e a palavra-chave WITHIN para agregar valores repetidos num âmbito definido. Por exemplo:

  SELECT COUNT(m1.f2) WITHIN RECORD FROM Table;

  O âmbito pode ser RECORD, que corresponde a toda a linha, ou um nó (campo repetido numa linha). As funções de agregação operam sobre os valores no âmbito e devolvem resultados agregados para cada registo ou nó.

Pode aplicar uma restrição a uma função de agregação através de uma das seguintes opções:

• Um alias numa consulta subselect. A restrição é especificada na cláusula WHERE exterior.

  #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 numa 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 pode referir-se a um alias nas cláusulas GROUP BY ou ORDER BY.

Sintaxe

Funções de agregação
AVG()	Devolve a média dos valores de um grupo de linhas…
BIT_AND()	Devolve o resultado de uma operação AND bit-a-bit ...
BIT_OR()	Devolve o resultado de uma operação OU bit a bit ...
BIT_XOR()	Devolve o resultado de uma operação XOU bit a bit ...
CORR()	Devolve o coeficiente de correlação de Pearson de um conjunto de pares de números.
COUNT()	Devolve o número total de valores ...
COUNT([DISTINCT])	Devolve o número total de valores não NULL ...
COVAR_POP()	Calcula a covariância da população dos valores ...
COVAR_SAMP()	Calcula a covariância de amostra dos valores …
EXACT_COUNT_DISTINCT()	Devolve o número exato de valores distintos não NULL para o campo especificado.
FIRST()	Devolve o primeiro valor sequencial no âmbito da função.
GROUP_CONCAT()	Concatena várias strings numa única string…
GROUP_CONCAT_UNQUOTED()	Concatena várias strings numa única string … não adiciona aspas duplas …
LAST()	Devolve o último valor sequencial ...
MAX()	Devolve o valor máximo …
MIN()	Devolve o valor mínimo …
NEST()	Agrega todos os valores no âmbito de agregação atual num campo repetido.
NTH()	Devolve o enésimo valor sequencial ...
QUANTILES()	Calcula os valores mínimo, máximo e quantis aproximados…
STDDEV()	Devolve o desvio padrão ...
STDDEV_POP()	Calcula o desvio padrão da população ...
STDDEV_SAMP()	Calcula o desvio padrão amostral ...
SUM()	Devolve o total da soma dos valores ...
TOP() ... COUNT(*)	Devolve os registos superiores max_records por frequência.
UNIQUE()	Devolve o conjunto de valores únicos não NULL ...
VARIANCE()	Calcula a variância dos valores ...
VAR_POP()	Calcula a variância da população dos valores …
VAR_SAMP()	Calcula a variância de amostra dos valores ...

AVG(numeric_expr)

Devolve a média dos valores de um grupo de linhas calculada por numeric_expr. As linhas com um valor NULL não são incluídas no cálculo.

BIT_AND(numeric_expr)

Devolve 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. Esta função devolve NULL se todas as instâncias de numeric_expr forem avaliadas como NULL.

BIT_OR(numeric_expr)

Devolve 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. Esta função devolve NULL se todas as instâncias de numeric_expr forem avaliadas como NULL.

BIT_XOR(numeric_expr)

Devolve 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. Esta função devolve NULL se todas as instâncias de numeric_expr forem avaliadas como NULL.

CORR(numeric_expr, numeric_expr)

Devolve o coeficiente de correlação de Pearson de um conjunto de pares de números.

COUNT(*)

Devolve o número total de valores (NULL e não NULL) no âmbito da função. A menos que esteja a usar COUNT(*) com a função TOP, é melhor especificar explicitamente o campo a contabilizar.

COUNT([DISTINCT] field [, n])

Devolve o número total de valores não NULL no âmbito da função.

Se usar a palavra-chave DISTINCT, a função devolve o número de valores distintos para o campo especificado. Tenha em atenção que o valor devolvido para DISTINCT é uma aproximação estatística e não é garantido que seja exato.

Use EXACT_COUNT_DISTINCT() para uma resposta exata.

Se precisar de uma maior precisão do COUNT(DISTINCT), pode especificar um segundo parâmetro, n, que indica o limite abaixo do qual os resultados exatos são garantidos. Por predefinição, n é 1000, mas se indicar um valor de n superior, recebe resultados exatos para COUNT(DISTINCT) até esse valor de n. No entanto, atribuir valores maiores a n reduz a escalabilidade deste operador e pode 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. Em alternativa, para uma abordagem mais escalável, considere usar GROUP EACH BY nos campos relevantes e, em seguida, aplicar COUNT(*). A abordagem GROUP EACH BY é mais escalável, mas pode incorrer numa ligeira penalização do desempenho inicial.

COVAR_POP(numeric_expr1, numeric_expr2)

Calcula a covariância da população dos valores calculados por numeric_expr1 e numeric_expr2.

COVAR_SAMP(numeric_expr1, numeric_expr2)

Calcula a covariância da amostra dos valores calculados por numeric_expr1 e numeric_expr2.

EXACT_COUNT_DISTINCT(field)

Devolve o número exato de valores distintos e não NULL para o campo especificado. Para uma melhor escalabilidade e desempenho, use COUNT(DISTINCT field).

FIRST(expr)

Devolve o primeiro valor sequencial no âmbito da função.

GROUP_CONCAT('str' [, separator])

Concatena várias strings numa única string, em que cada valor é separado pelo parâmetro separator opcional. Se separator for omitido, o BigQuery devolve uma string separada por vírgulas.

Se uma string nos dados de origem contiver um caráter de aspas duplas, a função GROUP_CONCAT devolve a string com aspas duplas adicionadas. Por exemplo, a string a"b seria devolvida como "a""b". Use GROUP_CONCAT_UNQUOTED se preferir que estas strings não sejam devolvidas 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 várias strings numa única string, em que cada valor é separado pelo parâmetro separator opcional. Se separator for omitido, o BigQuery devolve uma string separada por vírgulas.

Ao contrário de GROUP_CONCAT, esta função não adiciona aspas duplas aos valores devolvidos que incluem um caráter de aspas duplas. Por exemplo, a string a"b seria devolvida como a"b.

Exemplo:

#legacySQL
SELECT
  GROUP_CONCAT_UNQUOTED(x)
FROM (
  SELECT
    'a"b' AS x),
  (
  SELECT
    'cd' AS x);

LAST(field)

Devolve o último valor sequencial no âmbito da função.

MAX(field)

Devolve o valor máximo no âmbito da função.

MIN(field)

Devolve o valor mínimo no âmbito da função.

NEST(expr)

Agrega todos os valores no âmbito de agregação atual num campo repetido. Por exemplo, a consulta "SELECT x, NEST(y) FROM ... GROUP BY x" devolve um registo de saída para cada valor x distinto e contém um campo repetido para todos os valores y sincronizados com x na entrada da consulta. A função NEST requer uma cláusula GROUP BY.

O BigQuery reduz automaticamente os resultados da consulta. Por isso, se usar a função NEST na consulta de nível superior, os resultados não vão conter campos repetidos. Use a função NEST quando usar uma subseleção que produza resultados intermédios para utilização imediata pela mesma consulta.

NTH(n, field)

Devolve o nº valor sequencial no âmbito da função, em que n é uma constante. A função NTH começa a contar a partir de 1, pelo que não existe um termo zero. Se o âmbito da função tiver menos de n valores, a função devolve NULL.

QUANTILES(expr[, buckets])

Calcula os valores mínimos, máximos e quantis aproximados para a expressão de entrada. Os valores de entrada NULL são ignorados. Uma entrada vazia ou exclusivamente NULL resulta numa 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 os N-tiles aproximados, use N+1 buckets. O valor predefinido de buckets é 100. (Nota: o valor predefinido de 100 não estima percentis. Para estimar percentis, use, no mínimo, 101 buckets.) Se especificado explicitamente, buckets tem de ser, pelo menos, 2.

O erro fracionário por quantil é épsilon = 1 / buckets, o que significa que o erro diminui à medida que o número de contentores 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 específico, mas lembre-se de que NTH tem base 1 e que QUANTILES devolve o mínimo (o "0.º" quantil) na primeira posição e o máximo (o "100.º" percentil ou o "N.º" N-til) 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.º vigintile (percentil 95) 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 dos cálculos anteriores de 5% para 0,1%, use 1001 contentores em vez de 21 e ajuste o argumento à função NTH em conformidade. Para calcular a mediana com um erro de 0,1%, use NTH(501, QUANTILES(expr, 1001)); para o 95.º percentil com um erro de 0,1%, use NTH(951, QUANTILES(expr, 1001)).

STDDEV(numeric_expr)

Devolve o desvio padrão dos valores calculados por numeric_expr. As linhas com um valor NULL não sã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 calculado por numeric_expr. Use STDDEV_POP() para calcular o desvio padrão de um conjunto de dados que abrange toda a população de interesse. Se o seu conjunto de dados incluir apenas uma amostra representativa da população, use STDDEV_SAMP() em alternativa. Para mais informações sobre o desvio padrão da população em comparação com o desvio padrão da amostra, consulte o artigo Desvio padrão na Wikipédia.

STDDEV_SAMP(numeric_expr)

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

SUM(field)

Devolve o total da soma dos valores no âmbito da função. Apenas para utilização com tipos de dados numéricos.

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

Devolve os max_records principais registos por frequência. Consulte a descrição da TOP abaixo para ver detalhes.

UNIQUE(expr)

Devolve o conjunto de valores exclusivos e não NULL no âmbito da função numa ordem indefinida. Semelhante a uma cláusula GROUP BY grande sem a palavra-chave EACH, a consulta falha com um erro "Resources Exceeded" se existirem demasiados valores distintos. No entanto, ao contrário de GROUP BY, a função UNIQUE pode ser aplicada com agregação no âmbito, o que permite uma operação eficiente em campos aninhados com um número limitado de valores.

VARIANCE(numeric_expr)

Calcula a variância dos valores calculados por numeric_expr. As linhas com um valor NULL não sã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 calculados por numeric_expr. Para mais informações sobre o desvio padrão da população em comparação com o desvio padrão da amostra, consulte o artigo Desvio padrão na Wikipédia.

VAR_SAMP(numeric_expr)

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

Função TOP()

TOP é uma função que é uma alternativa à cláusula GROUP BY. É usado como sintaxe simplificada para GROUP BY ... ORDER BY ... LIMIT .... Geralmente, a função TOP tem um desempenho mais rápido do que a consulta ... GROUP BY ... ORDER BY ... LIMIT ... completa, mas pode devolver apenas resultados aproximados. Segue-se a sintaxe da função TOP:

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

Quando usar TOP numa cláusula SELECT, tem de incluir COUNT(*) como um dos campos.

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

field|alias

O campo ou o alias a devolver.

max_values

[Opcional] O número máximo de resultados a devolver. A predefinição é 20.

multiplier

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

Exemplos de TOP()

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

  As seguintes consultas usam TOP() para devolver 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");

• Compare TOP() com GROUP BY...ORDER BY...LIMIT

  A consulta devolve, por ordem, as 10 palavras mais usadas que contêm "th" e o número de documentos em que as palavras foram usadas. A consulta TOP é 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';

• Usar o parâmetro multiplier.

  As consultas seguintes mostram como o parâmetro multiplier afeta o resultado da consulta. A primeira consulta devolve o número de nascimentos por mês no Wyoming. A segunda consulta usa o parâmetro multiplier para multiplicar os valores de cnt por 100.

  Exemplo sem o parâmetro multiplier:

  #legacySQL
  SELECT
    TOP(month,3) as month, COUNT(*) as cnt
  FROM
    [bigquery-public-data:samples.natality]
  WHERE
    state = "WY";

  Devoluções:

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

  Devoluções:

  +-------+---------+
  | month |   cnt   |
  +-------+---------+
  |   7   | 1959400 |
  |   5   | 1903800 |
  |   8   | 1903000 |
  +-------+---------+
  

Nota: tem de incluir COUNT(*) na cláusula SELECT para usar TOP.

Exemplos avançados

• Média e desvio padrão agrupados por condição

  A seguinte consulta devolve a média e o desvio padrão dos pesos à nascença no Ohio em 2003, agrupados por mães que fumam e não fumam.

  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;

• Filtre os resultados da consulta através de um valor agregado

  Para filtrar os resultados da consulta através de um valor agregado (por exemplo, filtrar pelo valor de um SUM), use a função HAVING. HAVING compara um valor com um resultado determinado por uma função de agregação, ao contrário de WHERE, que opera em cada linha antes da agregação.

  Exemplo:

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

  Devoluções:

  +-------+--------+---------+
  | 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 usam argumentos numéricos e devolvem um resultado numérico. Cada argumento pode ser um literal numérico ou um valor numérico devolvido por uma consulta. Se a operação aritmética for avaliada como um resultado indefinido, a operação devolve NULL.

Sintaxe

Operador	Descrição	Exemplo
+	Adição	SELECT 6 + (5 - 1); Devoluções: 10
-	Subtração	SELECT 6 - (4 + 1); Devoluções: 1
*	Multiplicação	SELECT 6 * (5 - 1); Devoluções: 24
/	Divisão	SELECT 6 / (2 + 2); Devoluções: 1,5
%	Módulo	SELECT 6 % (2 + 2); Devoluções: 2

Funções bit a bit

As funções bit-a-bit operam ao nível de bits individuais e requerem argumentos numéricos. Para mais informações sobre as funções bit a bit, consulte o artigo Operação bit a bit.

Três funções bit a bit adicionais, BIT_AND, BIT_OR e BIT_XOR, estão documentadas nas funções de agregação.

Sintaxe

Operador	Descrição	Exemplo
&	AND bit a bit	SELECT (1 + 3) & 1 Devoluções: 0
|	OR bit-a-bit	SELECT 24 | 12 Devoluções: 28
^	XOU bit a bit	SELECT 1 ^ 0 Devoluções: 1
<<	Deslocamento bit a bit para a esquerda	SELECT 1 << (2 + 2) Devoluções: 16
>>	Deslocamento bit a bit para a direita	SELECT (6 + 2) >> 2 Devoluções: 2
~	NOT bit a bit	SELECT ~2 Devoluções: -3
BIT_COUNT(<numeric_expr>)	Devolve o número de bits definidos em <numeric_expr>.	SELECT BIT_COUNT(29); Devoluções: 4

Funções de transmissão

As funções de conversão alteram o tipo de dados de uma expressão numérica. As funções de conversão são particularmente úteis para garantir que os argumentos numa função de comparação têm o mesmo tipo de dados.

Sintaxe

Funções de transmissão
BOOLEAN()	Converter para booleano.
BYTES()	Converter para bytes.
CAST(expr AS type)	Converte expr numa variável do tipo type.
FLOAT()	Transmitir para o dobro.
HEX_STRING()	Converte para string hexadecimal.
INTEGER()	Converter em número inteiro.
STRING()	Converter para string.

BOOLEAN(<numeric_expr>)

• Devolve true se <numeric_expr> não for 0 nem NULL.
• Devolve false se <numeric_expr> for 0.
• Devolve NULL se <numeric_expr> for NULL.

BYTES(string_expr)

Devolve string_expr como um valor do tipo bytes.

CAST(expr AS type)

Converte expr numa variável do tipo type.

FLOAT(expr)

Devolve expr como um número de vírgula flutuante de precisão dupla. O expr pode ser uma string como '45.78', mas a função devolve NULL para valores não numéricos.

HEX_STRING(numeric_expr)

Devolve numeric_expr como uma string hexadecimal.

INTEGER(expr)

Converte expr num número inteiro de 64 bits.

• Devolve NULL se expr for uma string que não corresponda a um valor inteiro.
• Devolve o número de microssegundos desde a época Unix se expr for uma data/hora.

STRING(numeric_expr)

devolve numeric_expr como uma string.

Funções de comparação

As funções de comparação devolvem true ou false, com base nos seguintes tipos de comparações:

• Uma comparação de duas expressões.
• Uma comparação de uma expressão ou um conjunto de expressões com um critério específico, como estar numa lista especificada, ser NULL ou ser um valor opcional não predefinido.

Algumas das funções indicadas abaixo devolvem valores diferentes de true ou false, mas os valores que devolvem baseiam-se em operações de comparação.

Pode usar expressões numéricas ou de string como argumentos para funções de comparação. (As constantes de string têm de estar entre aspas simples ou duplas.) As expressões podem ser literais ou valores obtidos por uma consulta. As funções de comparação são usadas com mais frequência como condições de filtragem em cláusulas WHERE, mas podem ser usadas noutras cláusulas.

Sintaxe

Funções de comparação
expr1 = expr2	Devolve true se as expressões forem iguais.
expr1 != expr2 expr1 <> expr2	Devolve true se as expressões não forem iguais.
expr1 > expr2	Devolve true se expr1 for superior a expr2.
expr1 < expr2	Devolve true se expr1 for inferior a expr2.
expr1 >= expr2	Devolve true se expr1 for superior ou igual a expr2.
expr1 <= expr2	Devolve true se expr1 for inferior ou igual a expr2.
expr1 BETWEEN expr2 AND expr3	Devolve true se o valor de expr1 estiver entre expr2 e expr3, inclusive.
expr IS NULL	Devolve true se expr for NULL.
expr IN()	Devolve true se expr corresponder a expr1, expr2 ou qualquer valor entre parênteses.
COALESCE()	Devolve o primeiro argumento que não seja NULL.
GREATEST()	Devolve o parâmetro numeric_expr mais elevado.
IFNULL()	Se o argumento não for nulo, devolve o argumento.
IS_INF()	Devolve true se for infinito positivo ou negativo.
IS_NAN()	Devolve true se o argumento for NaN.
IS_EXPLICITLY_DEFINED()	Descontinuado: em alternativa, use expr IS NOT NULL.
LEAST()	Devolve o parâmetro numeric_expr do argumento mais pequeno.
NVL()	Se expr não for nulo, devolve expr; caso contrário, devolve null_default.

expr1 = expr2

Devolve true se as expressões forem iguais.

expr1 != expr2
expr1 <> expr2

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

expr1 > expr2

Devolve true se expr1 for superior a expr2.

expr1 < expr2

Devolve true se expr1 for inferior a expr2.

expr1 >= expr2

devolve true se expr1 for superior ou igual a expr2.

expr1 <= expr2

devolve true se expr1 for inferior ou igual a expr2.

expr1 BETWEEN expr2 AND expr3

Devolve true se o valor de expr1 for superior ou igual a expr2 e inferior ou igual a expr3.

expr IS NULL

Devolve true se expr for NULL.

expr IN(expr1, expr2, ...)

Devolve true se expr corresponder a expr1, expr2 ou qualquer valor entre parênteses. A palavra-chave IN é uma abreviatura eficiente de (expr = expr1 || expr = expr2 || ...). As expressões usadas com a palavra-chave IN têm de ser constantes e têm de corresponder ao tipo de dados de expr. A cláusula IN também pode ser usada para criar junções parciais e junções anti. Para mais informações, consulte os artigos Semi-join e Anti-join.

COALESCE(<expr1>, <expr2>, ...)

Devolve o primeiro argumento que não é NULL.

GREATEST(numeric_expr1, numeric_expr2, ...)

Devolve o parâmetro numeric_expr mais elevado. Todos os parâmetros têm de ser numéricos e do mesmo tipo. Se algum parâmetro for NULL, esta função devolve NULL.

Para ignorar valores NULL, use a função IFNULL para alterar os valores NULL para um valor que não afete a comparação. No exemplo de código seguinte, a função IFNULL é usada para alterar os 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, devolve expr. Caso contrário, devolve null_default.

IS_INF(numeric_expr)

Devolve true se numeric_expr for infinito positivo ou negativo.

IS_NAN(numeric_expr)

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

IS_EXPLICITLY_DEFINED(expr)

Esta função foi descontinuada. Em alternativa, use expr IS NOT NULL.

LEAST(numeric_expr1, numeric_expr2, ...)

Devolve o parâmetro numeric_expr mais pequeno. Todos os parâmetros têm de ser numéricos e do mesmo tipo. Se algum parâmetro for NULL, esta função devolve NULL

NVL(expr, null_default)

Se expr não for nulo, devolve expr. Caso contrário, devolve null_default. A função NVL é um alias de IFNULL.

Funções de data e hora

As seguintes funções permitem a manipulação de datas e horas para indicações de tempo UNIX, strings de data e tipos de dados TIMESTAMP. Para mais informações sobre como trabalhar com o tipo de dados TIMESTAMP, consulte Usar TIMESTAMP.

As funções de data e hora que funcionam com indicações de tempo UNIX operam com base na hora UNIX. As funções de data e hora devolvem valores com base no fuso horário UTC.

Sintaxe

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

CURRENT_DATE()

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

Exemplo:

SELECT CURRENT_DATE();

Devoluções: 01-02-2013

CURRENT_TIME()

Devolve uma string legível por humanos da hora atual do servidor no formato %H:%M:%S.

Exemplo:

SELECT CURRENT_TIME();

Regressos: 01:32:56

CURRENT_TIMESTAMP()

Devolve um tipo de dados TIMESTAMP da hora atual do servidor no formato %Y-%m-%d %H:%M:%S.

Exemplo:

SELECT CURRENT_TIMESTAMP();

Devoluções: 2013-02-01 01:33:35 UTC

DATE(<timestamp>)

Devolve 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'));

Devoluções: 01/10/2012

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 é subtraído ao tipo de dados TIMESTAMP.

Exemplo:

SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), 5, "YEAR");

Returns: 2017-10-01 02:03:04 UTC

SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), -5, "YEAR");

Devoluções: 2007-10-01 02:03:04 UTC

DATEDIFF(<timestamp1>,<timestamp2>)

Devolve o número de dias entre dois tipos de dados TIMESTAMP. O resultado é positivo se o tipo de dados TIMESTAMP primeiro vier depois do tipo de dados TIMESTAMP segundo e, caso contrário, o resultado é negativo.

Exemplo:

SELECT DATEDIFF(TIMESTAMP('2012-10-02 05:23:48'), TIMESTAMP('2011-06-24 12:18:35'));

Devoluções: 466

Exemplo:

SELECT DATEDIFF(TIMESTAMP('2011-06-24 12:18:35'), TIMESTAMP('2012-10-02 05:23:48'));

Devoluções: -466

DAY(<timestamp>)

Devolve 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'));

Devoluções: 2

DAYOFWEEK(<timestamp>)

Devolve 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"));

Devoluções: 2

DAYOFYEAR(<timestamp>)

Devolve 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"));

Devoluções: 275

FORMAT_UTC_USEC(<unix_timestamp>)

Devolve uma representação de string legível de uma data/hora UNIX no formato YYYY-MM-DD HH:MM:SS.uuuuuu.

Exemplo:

SELECT FORMAT_UTC_USEC(1274259481071200);

Devoluções: 2010-05-19 08:58:01.071200

HOUR(<timestamp>)

Devolve 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'));

Devoluções: 5

MINUTE(<timestamp>)

Devolve 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'));

Devoluções: 23

MONTH(<timestamp>)

Devolve 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'));

Devoluções: 10

MSEC_TO_TIMESTAMP(<expr>)

Converte uma indicação de tempo UNIX em milissegundos num tipo de dados TIMESTAMP.

Exemplo:

SELECT MSEC_TO_TIMESTAMP(1349053323000);

Devoluções: 2012-10-01 01:02:03 UTC

SELECT MSEC_TO_TIMESTAMP(1349053323000 + 1000)

Devoluções: 2012-10-01 01:02:04 UTC

NOW()

Devolve a indicação de tempo UNIX atual em microssegundos.

Exemplo:

SELECT NOW();

Devoluções: 1359685811687920

PARSE_UTC_USEC(<date_string>)

Converte uma string de data numa indicação de tempo UNIX em microssegundos. date_string tem de ter o formato YYYY-MM-DD HH:MM:SS[.uuuuuu]. A parte fracionária do segundo pode ter até 6 dígitos ou pode ser omitida.

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

Exemplo:

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

Devoluções: 1349056984000000

QUARTER(<timestamp>)

Devolve o trimestre do ano de um tipo de dados TIMESTAMP como um número inteiro entre 1 e 4, inclusive.

Exemplo:

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

Devoluções: 4

SEC_TO_TIMESTAMP(<expr>)

Converte uma indicação de tempo UNIX em segundos num tipo de dados TIMESTAMP.

Exemplo:

SELECT SEC_TO_TIMESTAMP(1355968987);

Devoluções: 2012-12-20 02:03:07 UTC

SELECT SEC_TO_TIMESTAMP(INTEGER(1355968984 + 3));

Devoluções: 2012-12-20 02:03:07 UTC

SECOND(<timestamp>)

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

Durante um segundo intercalar, o intervalo de números inteiros está entre 0 e 60, inclusive.

Exemplo:

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

Devoluções: 48

STRFTIME_UTC_USEC(<unix_timestamp>,
                  <date_format_str>)

Devolve uma string de data legível no formato date_format_str. date_format_str pode incluir carateres de pontuação relacionados com a data (como / e -) e carateres especiais aceites pela função strftime em C++ (como %d para o dia do mês).

Use as funções UTC_USEC_TO_<function_name> se planear agrupar os dados de consulta por intervalos de tempo, como obter 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");

Devoluções: 19-05-2010

TIME(<timestamp>)

Devolve 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'));

Devoluções: 02:03:04

TIMESTAMP(<date_string>)

Converter uma string de data num tipo de dados TIMESTAMP.

Exemplo:

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

Devoluções: 2012-10-01 01:02:03 UTC

TIMESTAMP_TO_MSEC(<timestamp>)

Converte um tipo de dados TIMESTAMP numa indicação de tempo UNIX em milissegundos.

Exemplo:

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

Devoluções: 1349053323000

TIMESTAMP_TO_SEC(<timestamp>)

Converte um tipo de dados TIMESTAMP numa indicação de tempo UNIX em segundos.

Exemplo:

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

Devoluções: 1349053323

TIMESTAMP_TO_USEC(<timestamp>)

Converte um tipo de dados TIMESTAMP numa indicação de tempo 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"));

Devoluções: 1349053323000000

USEC_TO_TIMESTAMP(<expr>)

Converte uma indicação de tempo UNIX em microssegundos num tipo de dados TIMESTAMP.

Exemplo:

SELECT USEC_TO_TIMESTAMP(1349053323000000);

Devoluções: 2012-10-01 01:02:03 UTC

SELECT USEC_TO_TIMESTAMP(1349053323000000 + 1000000)

Devoluções: 2012-10-01 01:02:04 UTC

UTC_USEC_TO_DAY(<unix_timestamp>)

Desloca uma indicação de tempo UNIX em microssegundos para o início do dia em que ocorre.

Por exemplo, se unix_timestamp ocorrer a 19 de maio às 08:58, esta função devolve uma data/hora UNIX para 19 de maio às 00:00 (meia-noite).

Exemplo:

SELECT UTC_USEC_TO_DAY(1274259481071200);

Devoluções: 1274227200000000

UTC_USEC_TO_HOUR(<unix_timestamp>)

Desloca uma indicação de tempo UNIX em microssegundos para o início da hora em que ocorre.

Por exemplo, se unix_timestamp ocorrer às 08:58, esta função devolve uma indicação de tempo UNIX para as 08:00 do mesmo dia.

Exemplo:

SELECT UTC_USEC_TO_HOUR(1274259481071200);

Devoluções: 1274256000000000

UTC_USEC_TO_MONTH(<unix_timestamp>)

Desloca uma indicação de tempo UNIX em microssegundos para o início do mês em que ocorre.

Por exemplo, se unix_timestamp ocorrer a 19 de março, esta função devolve uma data/hora UNIX para 1 de março do mesmo ano.

Exemplo:

SELECT UTC_USEC_TO_MONTH(1274259481071200);

Devoluções: 1272672000000000

UTC_USEC_TO_WEEK(<unix_timestamp>,
                 <day_of_week>)

Devolve uma indicação de tempo UNIX em microssegundos que representa um dia na semana do argumento unix_timestamp. Esta função seleciona dois argumentos: uma indicação de tempo 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/04/2008, e definir day_of_week como 2 (terça-feira), a função devolve uma indicação de tempo UNIX para terça-feira, 08/04/2008.

Exemplo:

SELECT UTC_USEC_TO_WEEK(1207929480000000, 2) AS tuesday;

Devoluções: 1207612800000000

UTC_USEC_TO_YEAR(<unix_timestamp>)

Devolve uma indicação de tempo UNIX em microssegundos que representa o ano do argumento unix_timestamp.

Por exemplo, se unix_timestamp ocorrer em 2010, a função devolve 1274259481071200, a representação em microssegundos de 2010-01-01 00:00.

Exemplo:

SELECT UTC_USEC_TO_YEAR(1274259481071200);

Devoluções: 1262304000000000

WEEK(<timestamp>)

Devolve a semana de um tipo de dados TIMESTAMP como um número inteiro entre 1 e 53, inclusive.

As semanas começam ao domingo. Por isso, se o dia 1 de janeiro for num 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'));

Devoluções: 53

YEAR(<timestamp>)

Devolve o ano de um tipo de dados TIMESTAMP.

Exemplo:

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

Devoluções: 2012

Exemplos avançados

• Converta resultados de data/hora de números inteiros num formato legível para humanos

  A seguinte consulta encontra os 5 principais momentos em que ocorreram mais revisões da Wikipédia. Para apresentar os resultados num formato legível para humanos, use a função FORMAT_UTC_USEC() do BigQuery, que recebe uma data/hora, em microssegundos, como entrada. Esta consulta multiplica as indicações de tempo no formato POSIX da Wikipédia (em segundos) por 1000000 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];

  Devoluções:

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

• Agrupar resultados por data/hora

  É útil usar funções de data e hora para agrupar os resultados da consulta em contentores correspondentes a anos, meses ou dias específicos. O exemplo seguinte usa a função UTC_USEC_TO_MONTH() para apresentar quantos carateres cada colaborador da Wikipédia usa nos respetivos comentários de revisões 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;

  Devoluções (truncadas):

  +--------------------------------+---------+-----------------------+
  |      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 de IP

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

Sintaxe

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

FORMAT_IP(integer_value)

Converte os 32 bits menos significativos de integer_value na string de endereço IPv4 legível. Por exemplo, FORMAT_IP(1) devolve a string '0.0.0.1'.

PARSE_IP(readable_ip)

Converte uma string que representa um endereço IPv4 num valor inteiro sem sinal. Por exemplo, PARSE_IP('0.0.0.1') devolve 1. Se a string não for um endereço IPv4 válido, PARSE_IP devolve NULL.

O BigQuery suporta a escrita 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 abaixo suportam a análise dos endereços para e a partir de um formato legível por humanos. Estas funções só funcionam em campos de strings com IPs.

Sintaxe

FORMAT_PACKED_IP(packed_ip)

Devolve 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') devoluções '3031:3233:3435:3637:3839:4041:4243:4445'
• FORMAT_PACKED_IP('0123') devoluções '48.49.50.51'

PARSE_PACKED_IP(readable_ip)

Devolve 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 vai devolver NULL. Exemplos:

• PARSE_PACKED_IP('48.49.50.51') devoluções 'MDEyMw=='
• PARSE_PACKED_IP('3031:3233:3435:3637:3839:4041:4243:4445') devoluções 'MDEyMzQ1Njc4OUBBQkNERQ=='

Funções JSON

As funções JSON do BigQuery permitem-lhe encontrar valores nos seus dados JSON armazenados através de expressões semelhantes a JSONPath.

O armazenamento de dados JSON pode ser mais flexível do que declarar todos os campos individuais no esquema da tabela, mas pode gerar custos mais elevados. Quando seleciona dados de uma string JSON, é-lhe cobrada a análise de toda a string, o que é mais caro do que se cada campo estivesse numa coluna separada. A consulta também é mais lenta, uma vez que toda a string tem de ser analisada no momento da consulta. No entanto, para esquemas ad hoc ou que mudam rapidamente, a flexibilidade do JSON pode compensar o custo adicional.

Use funções JSON em vez das funções de expressões regulares do BigQuery se estiver a trabalhar com dados estruturados, uma vez que 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 devolve uma string JSON.
JSON_EXTRACT_SCALAR()	Seleciona um valor de acordo com a expressão JSONPath e devolve um escalar JSON.

JSON_EXTRACT(json, json_path)

Seleciona um valor em json de acordo com a expressão JSONPath json_path. json_path tem de ser uma constante de string. Devolve 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 tem de ser uma constante de string. Devolve um valor JSON escalar.

Operadores lógicos

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

Sintaxe

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

expr AND expr

• Devolve true se ambas as expressões forem verdadeiras.
• Devolve false se uma ou ambas as expressões forem falsas.
• Devolve NULL se ambas as expressões forem NULL ou se uma expressão for verdadeira e a outra for NULL.

expr OR expr

• Devolve true se uma ou ambas as expressões forem verdadeiras.
• Devolve false se ambas as expressões forem falsas.
• Devolve NULL se ambas as expressões forem NULL ou se uma expressão for falsa e a outra for NULL.

NOT expr

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

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

Funções matemáticas

As funções matemáticas recebem argumentos numéricos e devolvem um resultado numérico. Cada argumento pode ser um literal numérico ou um valor numérico devolvido por uma consulta. Se a função matemática for avaliada como um resultado indefinido, a operação devolve NULL.

Sintaxe

Funções matemáticas
ABS()	Devolve o valor absoluto do argumento.
ACOS()	Devolve o arco cosseno do argumento.
ACOSH()	Devolve o arco cosseno hiperbólico do argumento.
ASIN()	Devolve o arco seno do argumento.
ASINH()	Devolve o arco seno hiperbólico do argumento.
ATAN()	Devolve a arcotangente do argumento.
ATANH()	Devolve a tangente hiperbólica inversa do argumento.
ATAN2()	Devolve a arcotangente dos dois argumentos.
CEIL()	Arredonda o argumento para o número inteiro mais próximo e devolve o valor arredondado.
COS()	Devolve o cosseno do argumento.
COSH()	Devolve o cosseno hiperbólico do argumento.
DEGREES()	Converte de radianos para graus.
EXP()	Devolve e elevado à potência do argumento.
FLOOR()	Arredonda o argumento para baixo, para o número inteiro mais próximo.
LN() LOG()	Devolve o logaritmo natural do argumento.
LOG2()	Devolve o logaritmo de base 2 do argumento.
LOG10()	Devolve o logaritmo de base 10 do argumento.
PI()	Devolve a constante π.
POW()	Devolve o primeiro argumento elevado à potência do segundo argumento.
RADIANS()	Converte de graus para radianos.
RAND()	Devolve um valor de ponto 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()	Devolve o seno do argumento.
SINH()	Devolve o seno hiperbólico do argumento.
SQRT()	Devolve a raiz quadrada da expressão.
TAN()	Devolve a tangente do argumento.
TANH()	Devolve a tangente hiperbólica do argumento.

ABS(numeric_expr)

Devolve o valor absoluto do argumento.

ACOS(numeric_expr)

Devolve o arco cosseno do argumento.

ACOSH(numeric_expr)

Devolve o arco cosseno hiperbólico do argumento.

ASIN(numeric_expr)

Devolve o arco seno do argumento.

ASINH(numeric_expr)

Devolve o arco seno hiperbólico do argumento.

ATAN(numeric_expr)

Devolve a arcotangente do argumento.

ATANH(numeric_expr)

Devolve a arcotangente hiperbólica do argumento.

ATAN2(numeric_expr1, numeric_expr2)

Devolve a arcotangente dos dois argumentos.

CEIL(numeric_expr)

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

COS(numeric_expr)

Devolve o cosseno do argumento.

COSH(numeric_expr)

Devolve o cosseno hiperbólico do argumento.

DEGREES(numeric_expr)

Devolve numeric_expr, convertido de radianos para graus.

EXP(numeric_expr)

Devolve o resultado da elevação da constante "e" (a base do logaritmo natural) à potência de numeric_expr.

FLOOR(numeric_expr)

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

LN(numeric_expr)
LOG(numeric_expr)

Devolve o logaritmo natural do argumento.

LOG2(numeric_expr)

Devolve o logaritmo de base 2 do argumento.

LOG10(numeric_expr)

Devolve o logaritmo de base 10 do argumento.

PI()

Devolve a constante π. A função PI() requer parênteses para indicar que é uma função, mas não aceita argumentos nesses parênteses. Pode usar PI() como uma constante com funções matemáticas e aritméticas.

POW(numeric_expr1, numeric_expr2)

Devolve o resultado da elevação de numeric_expr1 à potência de numeric_expr2.

RADIANS(numeric_expr)

Devolve numeric_expr, convertido de graus para radianos. (Tenha em atenção que π radianos equivalem a 180 graus.)

RAND([int32_seed])

Devolve um valor de vírgula flutuante aleatório no intervalo 0,0 <= valor < 1,0. Cada valor int32_seed gera sempre a mesma sequência de números aleatórios numa determinada consulta, desde que não use uma cláusula LIMIT. Se int32_seed não for especificado, o BigQuery usa a data/hora atual como o valor inicial.

ROUND(numeric_expr [, digits])

Arredonda o argumento por excesso ou por defeito para o número inteiro mais próximo (ou, se especificado, para o número de dígitos especificado) e devolve o valor arredondado.

SIN(numeric_expr)

Devolve o seno do argumento.

SINH(numeric_expr)

Devolve o seno hiperbólico do argumento.

SQRT(numeric_expr)

Devolve a raiz quadrada da expressão.

TAN(numeric_expr)

Devolve a tangente do argumento.

TANH(numeric_expr)

Devolve a tangente hiperbólica do argumento.

Exemplos avançados

• Consulta de caixa limitadora

  A seguinte consulta devolve uma coleção de pontos dentro de uma caixa delimitadora retangular centrada em 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

  Devolve uma coleção de até 100 pontos num círculo aproximado determinado pela através da lei esférica dos cossenos, centrada em Denver, Colorado (39,73, -104,98). Esta consulta usa as funções matemáticas e trigonométricas do BigQuery, como PI(), SIN() e COS().

  Uma vez que a Terra não é uma esfera absoluta e a longitude + latitude converge nos polos, esta consulta devolve 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ões regulares

O BigQuery oferece suporte para expressões regulares através da biblioteca re2. Consulte essa documentação para ver a respetiva sintaxe de expressões regulares.

Tenha em atenção que as expressões regulares são correspondências globais. Para começar a fazer a correspondência no início de uma palavra, tem de usar o caráter ^.

Sintaxe

Funções de expressões regulares
REGEXP_MATCH()	Devolve true se o argumento corresponder à expressão regular.
REGEXP_EXTRACT()	Devolve a parte do argumento que corresponde ao grupo de captura na expressão regular.
REGEXP_REPLACE()	Substitui uma substring que corresponde a uma expressão regular.

REGEXP_MATCH('str', 'reg_exp')

Devolve true se str corresponder à expressão regular. Para a correspondência de strings sem expressões regulares, use CONTÉM 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;

Devoluções:

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

REGEXP_EXTRACT('str', 'reg_exp')

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

Devoluções:

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

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

Devolve uma string em que qualquer substring de orig_str que corresponda a reg_exp é substituída por replace_str. Por exemplo, REGEXP_REPLACE ("Olá", "lá", "p") devolve Olá.

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;

Devoluções:

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

Exemplos avançados

• Filtrar conjunto de resultados por correspondência de expressão regular

  As funções de expressões regulares do BigQuery podem ser usadas para filtrar resultados numa cláusula WHERE, bem como para apresentar resultados na cláusula SELECT. O exemplo seguinte combina estes dois exemplos de utilização de expressões regulares numa ú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);

• Usar expressões regulares em dados de números inteiros ou de vírgula flutuante

  Embora as funções de expressão regular do BigQuery só funcionem para dados de strings, é possível usar a função STRING() para converter dados de números inteiros ou de vírgula flutuante no formato de string. Neste exemplo, STRING() é usado para converter o valor inteiro corpus_date numa string, que é 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 operam em dados de string. As constantes de string têm de estar entre aspas simples ou duplas. As funções de string são sensíveis a maiúsculas e minúsculas por predefinição. Pode anexar IGNORE CASE ao final de uma consulta para ativar a correspondência insensível a maiúsculas e minúsculas. IGNORE CASE só funciona com carateres ASCII e apenas no nível superior da consulta.

Os carateres universais não são suportados nestas funções. Para usar a funcionalidade de expressões regulares, use funções de expressões regulares.

Sintaxe

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

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

Devolve a concatenação de duas ou mais strings ou NULL se algum dos valores for NULL. Exemplo: se str1 for Java e str2 for Script, CONCAT devolve JavaScript.

expr CONTAINS 'str'

Devolve true se expr contiver o argumento de string especificado. Esta é uma comparação sensível a maiúsculas e minúsculas.

INSTR('str1', 'str2')

Devolve o índice baseado em um da primeira ocorrência de str2 em str1 ou devolve 0 se str2 não ocorrer em str1.

LEFT('str', numeric_expr)

Devolve os carateres mais à esquerda de numeric_expr de str. Se o número for superior a str, é devolvida a string completa. Exemplo: LEFT('seattle', 3) devolve sea.

LENGTH('str')

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

LOWER('str')

Devolve a string original com todos os carateres 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 carateres. Exemplo: LPAD('1', 7, '?') devolve ??????1.

LTRIM('str1' [, str2])

Remove carateres do lado esquerdo de str1. Se str2 for omitido, LTRIM remove espaços do lado esquerdo de str1. Caso contrário, LTRIM remove todos os carateres em str2 do lado esquerdo de str1 (sensível a maiúsculas e minúsculas).

Exemplos:

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

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

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

Substitui todas as instâncias de str2 em str1 por str3.

RIGHT('str', numeric_expr)

Devolve os numeric_expr carateres mais à direita de str. Se o número for maior do que o comprimento da string, é devolvida a string completa. Exemplo: RIGHT('kirkland', 4) devolve land.

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

Preenche str1 à direita com str2, repetindo str2 até que a string de resultado tenha exatamente numeric_expr carateres. Exemplo: RPAD('1', 7, '?') devolve 1??????.

RTRIM('str1' [, str2])

Remove os carateres finais do lado direito de str1. Se str2 for omitido, RTRIM remove os espaços finais de str1. Caso contrário, RTRIM remove todos os carateres em str2 do lado direito de str1 (sensível a maiúsculas e minúsculas).

Exemplos:

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

SELECT RTRIM("Say hello ", " hloe") devolve "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])

Devolve uma substring de str, começando em index. Se for usado o parâmetro max_len opcional, a string devolvida tem um comprimento máximo de max_len carateres. A contagem começa em 1, pelo que o primeiro caráter na string está na posição 1 (não zero). Se index for 5, a substring começa com o 5.º caráter a partir da esquerda em str. Se index for -4, a substring começa com o 4.º caráter a partir da direita em str. Exemplo: SUBSTR('awesome', -4, 4) devolve a substring some.

UPPER('str')

Devolve a string original com todos os carateres em maiúsculas.

Escape de carateres especiais em strings

Para ignorar carateres 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 caráter.
• Use uma barra de escape antes de barras, aspas simples e aspas duplas.
• Use sequências de estilo C ('\a', '\b', '\f', '\n', '\r', '\t', e '\v') para outros carateres.

Alguns exemplos de carateres 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 carateres universais de tabelas

As funções com carateres universais de tabelas são uma forma conveniente de consultar dados de um conjunto específico de tabelas. Uma função de caráter universal de tabela é equivalente a uma união separada por vírgulas de todas as tabelas correspondentes à função de caráter universal. Quando usa uma função com carateres universais de tabela, o BigQuery só acede e cobra pelas tabelas que correspondem ao caráter universal. As funções de carateres universais de tabelas são especificadas na cláusula FROM da consulta.

Se usar funções de caráter universal de tabelas numa consulta, as funções já não têm de estar entre parênteses. Por exemplo, alguns dos seguintes exemplos usam parênteses, enquanto outros não.

Não é possível colocar em cache os resultados das consultas de várias tabelas que usem uma função de caráter universal (mesmo que a opção Usar resultados em cache esteja selecionada). Se executar várias vezes a mesma consulta com um caráter universal, tem de pagar cada consulta.

Sintaxe

Funções de carateres universais de tabelas
TABLE_DATE_RANGE()	Consultar várias tabelas diárias que abrangem um intervalo de datas.
TABLE_DATE_RANGE_STRICT()	Consultas a várias tabelas diárias que abrangem um intervalo de datas, sem datas em falta.
TABLE_QUERY()	Consulta tabelas cujos nomes 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 das tabelas têm de ter o seguinte formato: <prefix><day>, em que <day> está no formato YYYYMMDD.

Pode usar funções de data e hora para gerar os parâmetros de data/hora. Por exemplo:

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

Exemplo: obter tabelas entre dois dias

Este exemplo pressupõe a existência das 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

Corresponde às seguintes tabelas:

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

Exemplo: obter tabelas num intervalo de dois dias até "agora"

Este exemplo pressupõe que as seguintes tabelas existem num projeto denominado myproject-1234:

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

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

Corresponde às seguintes tabelas:

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

TABLE_DATE_RANGE_STRICT(prefix, timestamp1, timestamp2)

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

Exemplo: erro na tabela em falta

Este exemplo pressupõe a existência das 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 devolve um erro "Não encontrado" para a tabela "people20140326".

TABLE_QUERY(dataset, expr)

Consulta tabelas cujos nomes correspondem ao expr fornecido. O parâmetro expr tem de ser representado como uma string e tem de conter uma expressão a avaliar. Por exemplo, 'length(table_id) < 3'.

Exemplo: corresponder a tabelas cujos nomes contenham "oo" e tenham um comprimento superior a 4

Este exemplo pressupõe a existência das 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'))

Corresponde às seguintes tabelas:

• mydata.ooze
• mydata.spoon

Exemplo: corresponder a tabelas cujos nomes começam por "boo", seguidos de 3 a 5 dígitos numéricos

Este exemplo pressupõe que as seguintes tabelas existem num projeto denominado myproject-1234:

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

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

Corresponde às seguintes tabelas:

• mydata.book418
• mydata.boom12345

Funções de URL

Sintaxe

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

HOST('url_str')

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

DOMAIN('url_str')

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

TLD('url_str')

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

Notas:

• Estas funções não realizam uma pesquisa de DNS inversa. Por isso, se chamar estas funções através de um endereço IP, as funções devolvem segmentos do endereço IP em vez de segmentos do nome do anfitrião.
• Todas as funções de análise de URL esperam carateres em minúsculas. Os carateres em maiúsculas no URL resultam num resultado NULL ou incorreto. Considere transmitir a entrada a esta função através de LOWER() se os seus dados tiverem letras maiúsculas e minúsculas.

Exemplo avançado

Analise nomes de domínios a partir de dados de URLs

Esta consulta usa a função DOMAIN() para devolver os domínios mais populares listados como páginas iniciais de repositórios no GitHub. Repare na utilização de HAVING para filtrar registos através do resultado da função DOMAIN(). Esta é uma função útil para determinar informações de referenciadores 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;

Devoluções:

+-----------------+----------------+
|   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 apresenta os TLDs principais que não estão numa 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;

Devoluções:

+----------+----------------+
| 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 num subconjunto específico, ou "janela", de um conjunto de resultados. As funções de janela facilitam a criação de relatórios que incluem estatísticas complexas, como médias móveis e totais acumulados.

Cada função de janela requer uma cláusula OVER que especifique o início e o fim da janela. Os três componentes da cláusula OVER (partição, ordenação e enquadramento) oferecem um controlo adicional sobre a janela. A partição permite-lhe dividir os dados de entrada em grupos lógicos com uma caraterística comum. A ordenação permite-lhe ordenar os resultados numa partição. A enquadramento permite-lhe criar um quadro de janela deslizante numa partição que se move relativamente à linha atual. Pode configurar o tamanho da janela móvel com base num número de linhas ou num 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 esta função opera. Especifique um ou mais nomes de colunas separados por vírgulas. É criada uma partição para cada conjunto distinto de valores para estas colunas, semelhante a uma cláusula GROUP BY. Se PARTITION BY for omitido, a partição base é todas as linhas na entrada para a função de janela.

A cláusula PARTITION BY também permite que as funções de janela particionem dados e paralelizem a execução. Se quiser usar uma função de janela com allowLargeResults ou se pretender aplicar mais junções ou agregações ao resultado da função de janela, use PARTITION BY para paralelizar a execução.

Não é possível usar as cláusulas

JOIN EACH e GROUP EACH BY no resultado de funções de janela. Para gerar resultados de consultas grandes quando usar funções de janela, tem de usar PARTITION BY.

ORDER BY

Ordena a partição. Se ORDER BY estiver ausente, não existe garantia de qualquer ordem de ordenação predefinida. A ordenação ocorre ao nível da partição, antes de ser aplicada qualquer cláusula de quadro de janela. Se especificar uma janela RANGE, deve adicionar uma cláusula ORDER BY. A ordem predefinida é ASC.

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

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

<window-frame-clause>

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

Um subconjunto da partição sobre a qual operar. Pode ter o mesmo tamanho que a partição ou ser mais pequena. Se usar ORDER BY sem um window-frame-clause, o frame da janela predefinido é RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Se omitir ORDER BY e window-frame-clause, a frame da janela predefinida é 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 dos valores de salário das 5 linhas anteriores, consultaria SUM(salary) OVER (ROWS BETWEEN 5 PRECEDING AND CURRENT ROW). O conjunto de linhas inclui normalmente a linha atual, mas isso não é obrigatório.
• RANGE: define uma janela em termos de um intervalo de valores numa determinada coluna, relativamente ao valor dessa coluna na linha atual. Funciona apenas com números e datas, em que os valores de data são números inteiros simples (microssegundos desde a época). As linhas adjacentes com o mesmo valor são denominadas linhas pares. As linhas semelhantes de CURRENT ROW estão incluídas num intervalo que especifica CURRENT ROW. Por exemplo, se especificar que o fim do período é CURRENT ROW e a linha seguinte no período tiver o mesmo valor, esta é incluída no cálculo da função.
• BETWEEN <start> AND <end> – Um intervalo, incluindo as linhas de início e fim. O intervalo não tem de incluir a linha atual, mas <start> tem de preceder ou ser igual a <end>.
• <start> - Especifica o desvio de início para esta janela, relativamente à linha atual. As seguintes opções são suportadas:

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

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

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

  onde <expr> é um número inteiro positivo, PRECEDING indica um número de linha ou um valor de intervalo anterior e FOLLOWING indica um número de linha ou um valor de intervalo posterior. UNBOUNDED FOLLOWING significa a última linha da partição. Se o fim estiver além do fim da janela, é definido como a última linha da partição.

Ao contrário das funções de agregação, que reduzem muitas linhas de entrada a uma linha de saída, as funções de janela devolvem uma linha de saída para cada linha de entrada. Esta funcionalidade facilita a criação de consultas que calculam os totais acumulados e as médias móveis. Por exemplo, a seguinte consulta devolve um total acumulado para um pequeno conjunto de dados de cinco linhas definido por declaraçõ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 devolvido:

+------+-------+--------------+
| name | value | RunningTotal |
+------+-------+--------------+
| a    |     0 |            0 |
| b    |     1 |            1 |
| c    |     2 |            3 |
| d    |     3 |            6 |
| e    |     4 |           10 |
+------+-------+--------------+

O exemplo seguinte calcula uma média móvel dos valores na linha atual e na linha anterior. A moldura da janela é composta por 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 devolvido:

+------+-------+---------------+
| 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 que as funções de agregação correspondentes, mas são calculadas num período definido pela cláusula OVER.
CUME_DIST()	Devolve um valor duplo que indica a distribuição cumulativa de um valor num grupo de valores…
DENSE_RANK()	Devolve a classificação inteira de um valor num grupo de valores.
FIRST_VALUE()	Devolve o primeiro valor do campo especificado na janela.
LAG()	Permite-lhe ler dados de uma linha anterior numa janela.
LAST_VALUE()	Devolve o último valor do campo especificado na janela.
LEAD()	Permite-lhe ler dados de uma linha seguinte numa janela.
NTH_VALUE()	Devolve o valor de <expr> na posição <n> da moldura da janela ...
NTILE()	Divide a janela no número especificado de grupos.
PERCENT_RANK()	Devolve a classificação da linha atual, relativamente às outras linhas na partição.
PERCENTILE_CONT()	Devolve um valor interpolado que seria mapeado para o argumento de percentil relativamente à janela ...
PERCENTILE_DISC()	Devolve o valor mais próximo da percentagem do argumento na janela.
RANK()	Devolve a classificação inteira de um valor num grupo de valores.
RATIO_TO_REPORT()	Devolve a proporção de cada valor em relação à soma dos valores.
ROW_NUMBER()	Devolve o número da linha atual do resultado da consulta na janela.

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

Estas funções de janela executam a mesma operação que as funções de agregação correspondentes, mas são calculadas numa janela definida pela cláusula OVER.

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

Na consulta de exemplo, a cláusula ORDER BY faz com que a janela seja calculada a partir do início da partição até à 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
        

Devoluções:

corpus_date	corpus	word_count	annual_total
0	vários	37	37
0	sonetos	157	194
1590	2kinghenryvi	18	18
1590	1kinghenryvi	24	42
1590	3kinghenryvi	40	82

CUME_DIST()

Devolve um valor duplo que indica a distribuição cumulativa de um valor num grupo de valores, calculado através da fórmula <number of rows preceding or tied with the current row> / <total rows>. Os valores empatados devolvem o mesmo valor de distribuição cumulativa.

Esta 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

Devoluções:

palavra	word_count	cume_dist
lenço	29	0.2
satisfação	5	0,4
descontentamento	4	0,8
instrumentos	4	0,8
circunstância	3	1,0

DENSE_RANK()

Devolve a classificação inteira de um valor num grupo de valores. A classificação é calculada com base em comparações com outros valores no grupo.

Os valores empatados são apresentados com a mesma classificação. A classificação do valor seguinte é incrementada em 1. Por exemplo, se dois valores ficarem empatados no 2.º lugar, o valor seguinte na classificação é o 3.º. Se preferir uma lacuna na lista de classificação, use rank().

Esta 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

Devoluções:

palavra	word_count	dense_rank
lenço	29	1
satisfação	5	2
descontentamento	4	3
instrumentos	4	3
circunstância	3	4

FIRST_VALUE(<field_name>)

Devolve 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

Devoluções:

palavra	word_count	fv
imperfeitamente	1	imperfeitamente

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

Permite-lhe ler dados de uma linha anterior numa janela. Especificamente, LAG() devolve o valor de <expr> para a linha localizada <offset> linhas antes da linha atual. Se a linha não existir, <default_value> é devolvido.

#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

Devoluções:

palavra	word_count	atraso
lenço	29	nulo
satisfação	5	lenço
descontentamento	4	satisfação
instrumentos	4	descontentamento
circunstância	3	instrumentos

LAST_VALUE(<field_name>)

Devolve 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

Devoluções:

palavra	word_count	lv
imperfeitamente	1	imperfeitamente

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

Permite-lhe ler dados de uma linha seguinte numa janela. Especificamente, LEAD() devolve o valor de <expr> para a linha localizada <offset> linhas após a linha atual. Se a linha não existir, <default_value> é devolvido.

#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

Devoluções:

palavra	word_count	lead
lenço	29	satisfação
satisfação	5	descontentamento
descontentamento	4	instrumentos
instrumentos	4	circunstância
circunstância	3	nulo

NTH_VALUE(<expr>, <n>)

Devolve o valor de <expr> na posição <n> da moldura da janela, onde <n> é um índice baseado em um.

NTILE(<num_buckets>)

Divide uma sequência de linhas em <num_buckets> recipientes e atribui um número de recipiente correspondente, como um número inteiro, a cada linha. A função ntile() atribui os números dos grupos de forma tão igual quanto possível e devolve 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

Devoluções:

palavra	word_count	ntile
lenço	29	1
satisfação	5	1
descontentamento	4	1
instrumentos	4	2
circunstância	3	2

PERCENT_RANK()

Devolve a classificação da linha atual, relativamente às outras linhas na partição. Os valores devolvidos variam entre 0 e 1, inclusive. O primeiro valor devolvido é 0,0.

Esta 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

Devoluções:

palavra	word_count	p_rank
lenço	29	0.0
satisfação	5	0,25
descontentamento	4	0,5
instrumentos	4	0,5
circunstância	3	1,0

PERCENTILE_CONT(<percentile>)

Devolve um valor interpolado que seria mapeado para o argumento percentil relativamente à janela, depois de os ordenar de acordo com a cláusula ORDER BY.

<percentile> tem de ser um número entre 0 e 1.

Esta 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

Devoluções:

palavra	word_count	p_cont
lenço	29	4
satisfação	5	4
descontentamento	4	4
instrumentos	4	4
circunstância	3	4

PERCENTILE_DISC(<percentile>)

Devolve o valor mais próximo da percentagem do argumento na janela.

<percentile> tem de ser um número entre 0 e 1.

Esta 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

Devoluções:

palavra	word_count	p_disc
lenço	29	4
satisfação	5	4
descontentamento	4	4
instrumentos	4	4
circunstância	3	4

RANK()

Devolve a classificação inteira de um valor num grupo de valores. A classificação é calculada com base em comparações com outros valores no grupo.

Os valores empatados são apresentados com a mesma classificação. A classificação do valor seguinte é incrementada de acordo com o número de valores empatados que ocorreram antes. Por exemplo, se dois valores ficarem empatados no 2.º lugar, o valor seguinte na classificação é 4 e não 3. Se preferir que não existam lacunas na lista de classificação, use dense_rank().

Esta 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

Devoluções:

palavra	word_count	classificação
lenço	29	1
satisfação	5	2
descontentamento	4	3
instrumentos	4	3
circunstância	3	5

RATIO_TO_REPORT(<column>)

Devolve a proporção de cada valor em relação à soma dos valores, como um valor de vírgula flutuante 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

Devoluções:

palavra	word_count	r_to_r
lenço	29	0,6444444444444445
satisfação	5	0,1111111111111111
descontentamento	4	0,08888888888888889
instrumentos	4	0,08888888888888889
circunstância	3	0,06666666666666667

ROW_NUMBER()

Devolve o número da linha atual do resultado da consulta na janela, começando por 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

Devoluções:

palavra	word_count	row_num
lenço	29	1
satisfação	5	2
descontentamento	4	3
instrumentos	4	4
circunstância	3	5

Outras funções

Sintaxe

Outras funções
CASE WHEN ... THEN	Use CASE para escolher entre duas ou mais expressões alternativas na sua consulta.
CURRENT_USER()	Devolve o endereço de email do utilizador que está a executar a consulta.
EVERY()	Devolve verdadeiro se o argumento for verdadeiro para todas as respetivas entradas.
FROM_BASE64()	Converte a string de entrada codificada em base64 no formato BYTES.
HASH()	Calcula e devolve um valor de hash com sinal de 64 bits…
FARM_FINGERPRINT()	Calcula e devolve um valor de impressão digital com sinal de 64 bits ...
IF()	Se o primeiro argumento for verdadeiro, devolve o segundo argumento; caso contrário, devolve o terceiro argumento.
POSITION()	Devolve a posição sequencial baseada em 1 do argumento.
SHA1()	Devolve um hash SHA1 no formato BYTES.
SOME()	Devolve verdadeiro se o argumento for verdadeiro para, pelo menos, uma das respetivas entradas.
TO_BASE64()	Converte o argumento BYTES numa 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 sua consulta. WHEN As expressões têm de ser booleanas e todas as expressões nas cláusulas THEN e na cláusula ELSE têm de ser tipos compatíveis.

CURRENT_USER()

Devolve o endereço de email do utilizador que está a executar a consulta.

EVERY(<condition>)

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

FROM_BASE64(<str>)

Converte a string de entrada codificada em base64 str no formato BYTES. Para converter BYTES numa string com codificação base64, use TO_BASE64().

HASH(expr)

Calcula e devolve um valor de hash com sinal 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 é suportada e a função respeita IGNORE CASE para strings, devolvendo valores independentes de maiúsculas e minúsculas.

FARM_FINGERPRINT(expr)

Calcula e devolve um valor de impressão digital assinado de 64 bits da entrada STRING ou BYTES através da função Fingerprint64 da biblioteca FarmHash de código aberto. O resultado desta função para uma entrada específica nunca muda e corresponde ao resultado da função FARM_FINGERPRINT quando usa o GoogleSQL. Respeita IGNORE CASE para strings, devolvendo valores independentes de maiúsculas e minúsculas.

IF(condition, true_return, false_return)

Devolve true_return ou false_return, consoante condition seja verdadeiro ou falso. Os valores devolvidos podem ser literais ou valores derivados de campos, mas têm de ser do mesmo tipo de dados. Os valores derivados de campos não têm de ser incluídos na cláusula SELECT.

POSITION(field)

Devolve a posição sequencial baseada em um de field num conjunto de campos repetidos.

SHA1(<str>)

Devolve um hash SHA1, no formato BYTES, da string de entrada str. Pode converter o resultado em base64 através de TO_BASE64(). Por exemplo:

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

SOME(<condition>)

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

TO_BASE64(<bin_data>)

Converte a entrada BYTES bin_data numa 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 para BYTES, use FROM_BASE64().

Exemplos avançados

• Agrupar resultados em categorias usando condições

  A consulta seguinte usa um bloco CASE/WHEN para agrupar os resultados em categorias de "região" com base numa lista de estados. Se o estado não aparecer como uma opção numa das declarações WHEN, o valor do estado é predefinido como "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;

  Devoluções:

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

• Simular uma tabela dinâmica

  Use declarações condicionais para organizar os resultados de uma consulta subselecionada em linhas e colunas. No exemplo abaixo, os resultados de uma pesquisa dos artigos da Wikipédia mais revistos que começam com o valor "Google" estão organizados em colunas onde as contagens de revisões são apresentadas se cumprirem vários 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
    );

  Devoluções:

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

• Usar a função HASH para selecionar uma amostra aleatória dos seus dados

  Algumas consultas podem fornecer um resultado útil através da subamostragem aleatória do conjunto de resultados. Para obter uma amostragem aleatória de valores, use a função HASH para devolver resultados em que o módulo "n" do hash é igual a zero.

  Por exemplo, a seguinte consulta encontra o HASH() do valor "title" e, em seguida, verifica se esse valor módulo "2" é zero. Isto deve resultar em cerca de 50% dos valores etiquetados como "amostrados". Para criar uma amostra de menos valores, aumente o valor da operação de módulo de "2" para algo maior. A consulta usa a função ABS em combinação com HASH, porque HASH pode devolver valores negativos, e o operador módulo num valor negativo produz 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;