Funções, operadores e condicionais

Mantenha tudo organizado com as coleções Salve e categorize o conteúdo com base nas suas preferências.

Este tópico é uma compilação de funções, operadores e expressões condicionais.

Para saber mais sobre como chamar funções, regras de chamada de função, o prefixo SAFE e tipos especiais de argumentos, consulte Chamadas de função.


OPERADORES E CONDICIONAIS

Operadores

Os operadores são representados por caracteres especiais ou palavras-chave. Eles não usam a sintaxe de chamada de função. Um operador manipula qualquer número de entradas de dados, também chamados de operandos, e retorna um resultado.

Convenções comuns:

  • A menos que especificado em contrário, todos os operadores retornam NULL quando um dos operandos é NULL.
  • Todos os operadores geram um erro se o resultado do cálculo estourar.
  • Para todas as operações de ponto flutuante, +/-inf e NaN só poderão ser retornadas se um dos operandos for +/-inf ou NaN. Em outros casos, um erro é retornado.

Precedência do operador

A tabela a seguir lista todos os operadores do BigQuery da precedência mais alta à mais baixa, isto é, na ordem em que são avaliados em uma declaração.

Ordem de precedência Operador Tipos de dados de entrada Nome Arity do operador
1 Operador de acesso ao campo JSON
STRUCT
Operador de acesso ao campo Binário
  Operador de subscrito da matriz ARRAY Posição da matriz. Precisa ser usado com OFFSET ou ORDINAL. Consulte Funções ARRAY. Binário
  Operador de subscrito JSON JSON Nome do campo ou posição da matriz em JSON. Binário
2 + Todos os tipos numéricos Mais unário. Unário
  - Todos os tipos numéricos Unário menos Unário
  ~ Inteiro ou BYTES Bit a bit NOT Unário
3 * Todos os tipos numéricos Multiplicação Binário
  / Todos os tipos numéricos Divisão Binário
  || STRING, BYTES ou ARRAY<T> Operador de concatenação Binário
4 + Todos os tipos numéricos, DATE com INT64, INTERVAL Adição Binário
  - Todos os tipos numéricos, DATE com INT64, INTERVAL Subtração Binário
5 << Inteiro ou BYTES Bit a bit deslocado para a esquerda Binário
  >> Inteiro ou BYTES Bit a bit deslocado para a direita Binário
6 & Inteiro ou BYTES Bit a bit and Binário
7 ^ Inteiro ou BYTES Bit a bit xor Binário
8 | Inteiro ou BYTES Bit a bit or Binário
9 (operadores de comparação) = Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados. Igual Binário
  < Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados. Menos de Binário
  > Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados. Maior que Binário
  <= Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados. Menor que ou igual a Binário
  >= Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados. Maior que ou igual a Binário
  !=, <> Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados. Diferente Binário
  [NOT] LIKE STRING e byte O valor [não] corresponde ao padrão especificado Binário
  [NOT] BETWEEN Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados. O valor [não] está dentro do intervalo especificado Binário
  [NOT] IN Qualquer tipo comparável. Para ver uma lista completa, consulte Tipos de dados. O valor [não] está no grupo de valores especificados Binário
  IS [NOT] NULL Todos O valor [não] é NULL Unário
  IS [NOT] TRUE BOOL O valor [não] é TRUE. Unário
  IS [NOT] FALSE BOOL O valor [não] é FALSE. Unário
10 NOT BOOL Lógico NOT Unário
11 AND BOOL Lógico AND Binário
12 OR BOOL Lógico OR Binário

Os operadores com a mesma precedência são associativos da esquerda, ou seja, são agrupados da esquerda para a direita. Por exemplo, a expressão:

x AND y AND z

é interpretada como

( ( x AND y ) AND z )

A expressão:

x * y / z

é interpretada como:

( ( x * y ) / z )

Todos os operadores de comparação têm a mesma prioridade, mas eles não são associativos. Portanto, é preciso usar parênteses para resolver ambiguidade. Exemplo:

(x < y) IS FALSE

Operador de acesso ao campo

expression.fieldname[. ...]

Descrição

Recebe o valor de um campo. Também conhecida como operador de ponto. Pode ser usado para acessar campos aninhados. Por exemplo, expression.fieldname1.fieldname2.

Tipos de entrada

  • STRUCT
  • JSON

Tipo de retorno

  • Para STRUCT: tipo de dados SQL de fieldname. Se um campo não for encontrado na estrutura, ocorrerá um erro.
  • Para JSON: JSON. Se um campo não for encontrado em um valor JSON, um NULL SQL será retornado.

Exemplo

No exemplo a seguir, a expressão é t.customer e as operações de acesso ao campo são .address e .country. Uma operação é um aplicativo de um operador (.) para operandos específicos (neste caso, address e country ou, mais especificamente, t.customer e address, para a primeira operação, e t.customer.address ecountry para a segunda operação.

WITH orders AS (
  SELECT STRUCT(STRUCT('Yonge Street' AS street, 'Canada' AS country) AS address) AS customer
)
SELECT t.customer.address.country FROM orders AS t;

+---------+
| country |
+---------+
| Canada  |
+---------+

Operador de subscrito da matriz

array_expression[array_subscript_specifier]

array_subscript_specifier:
  position_keyword(index)

position_keyword:
  { OFFSET | SAFE_OFFSET | ORDINAL | SAFE_ORDINAL }

Descrição

Recebe um valor de uma matriz em um local específico.

Tipos de entrada

  • array_expression: a matriz de entrada.
  • position_keyword: onde o índice da matriz precisa ser iniciado e como os índices fora do intervalo são processados. Suas opções são:
    • OFFSET: o índice começa em zero. Gera um erro se o índice estiver fora do intervalo.
    • SAFE_OFFSET: o índice começa em zero. Retornará NULL se o índice estiver fora do intervalo.
    • ORDINAL: o índice começa em um. Gera um erro se o índice estiver fora do intervalo.
    • SAFE_ORDINAL: o índice começa em um. Retornará NULL se o índice estiver fora do intervalo.
  • index: um número inteiro que representa uma posição específica na matriz.

Tipo de retorno

T em que array_expression é ARRAY<T>.

Exemplos

Neste exemplo, o operador de subscrito de matriz é usado para retornar valores em locais específicos em item_array. Este exemplo também mostra o que acontece quando você faz referência a um índice (6) em uma matriz fora do intervalo. Se o prefixo SAFE estiver incluído, NULL será retornado. Caso contrário, um erro será produzido.

WITH Items AS (SELECT ["coffee", "tea", "milk"] AS item_array)
SELECT
  item_array,
  item_array[OFFSET(1)] AS item_offset,
  item_array[ORDINAL(1)] AS item_ordinal,
  item_array[SAFE_OFFSET(6)] AS item_safe_offset,
FROM Items

+----------------------------------+--------------+--------------+------------------+
| item_array                       | item_offset  | item_ordinal | item_safe_offset |
+----------------------------------+--------------+--------------+------------------+
| [coffee, tea, milk]              | tea          | coffee       | NULL             |
+----------------------------------+--------------+--------------+------------------+

No exemplo a seguir, quando você faz referência a um índice em uma matriz fora do intervalo e o prefixo SAFE não está incluído, é gerado um erro.

WITH Items AS (SELECT ["coffee", "tea", "milk"] AS item_array)
SELECT
  item_array[OFFSET(6)] AS item_offset
FROM Items

-- Error. OFFSET(6) is out of range.

Operador de subscrito JSON

json_expression[array_element_id]
json_expression[field_name]

Descrição

Recebe o valor de um elemento ou campo de matriz em uma expressão JSON. Pode ser usado para acessar dados aninhados.

Tipos de entrada

  • JSON expression: a expressão JSON que contém um elemento ou campo de matriz a ser retornado.
  • [array_element_id]: uma expressão INT64 que representa um índice baseado em zeros na matriz. Se um valor negativo for inserido ou o valor for maior ou igual ao tamanho da matriz, ou a expressão JSON não representar uma matriz JSON, um NULL SQL será retornado.
  • [field_name]: uma expressão STRING que representa o nome de um campo em JSON. Se o nome do campo não for encontrado ou se a expressão JSON não for um objeto JSON, um NULL SQL será retornado.

Tipo de retorno

JSON

Exemplo

Estes são os elementos do exemplo a seguir:

  • json_value é uma expressão JSON.
  • .class é um acesso a campo JSON.
  • .students é um acesso a campo JSON.
  • [0] é uma expressão de subscrito JSON com um deslocamento de elemento que acessa o elemento zero de uma matriz no valor JSON.
  • ['name'] é uma expressão de subscrito JSON com um nome de campo que acessa um campo.
SELECT json_value.class.students[0]['name'] AS first_student
FROM
  UNNEST(
    [
      JSON '{"class" : {"students" : [{"name" : "Jane"}]}}',
      JSON '{"class" : {"students" : []}}',
      JSON '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'])
    AS json_value;

+-----------------+
| first_student   |
+-----------------+
| "Jane"          |
| NULL            |
| "John"          |
+-----------------+

Operadores aritméticos

Todos os operadores aritméticos aceitam a entrada do tipo numérico T, também presente no tipo de resultado, a menos que indicado de outra forma na descrição abaixo:

Nome Sintaxe
Adição X + Y
Subtração X - Y
Multiplicação X * Y
Divisão X / Y
Mais unário. + X
Unário menos - X

NOTA: as operações de divisão por zero retornam um erro. Para retornar um resultado diferente, considere as funções IEEE_DIVIDE ou SAFE_DIVIDE.

Tipos de resultados para adição, subtração e multiplicação:

INPUTINT64NUMERICBIGNUMERICFLOAT64
INT64INT64NUMERICBIGNUMERICFLOAT64
NUMERICNUMERICNUMERICBIGNUMERICFLOAT64
BIGNUMERICBIGNUMERICBIGNUMERICBIGNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64FLOAT64

Tipos de resultados para divisão:

INPUTINT64NUMERICBIGNUMERICFLOAT64
INT64FLOAT64NUMERICBIGNUMERICFLOAT64
NUMERICNUMERICNUMERICBIGNUMERICFLOAT64
BIGNUMERICBIGNUMERICBIGNUMERICBIGNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64FLOAT64

Tipos de resultados para Unary Plus:

INPUTINT64NUMERICBIGNUMERICFLOAT64
OUTPUTINT64NUMERICBIGNUMERICFLOAT64

Tipos de resultados para Unário menos:

INPUTINT64NUMERICBIGNUMERICFLOAT64
OUTPUTINT64NUMERICBIGNUMERICFLOAT64

Operadores aritméticos de data

Os operadores '+' e '-' podem ser usados para operações aritméticas em datas.

date_expression + int64_expression
int64_expression + date_expression
date_expression - int64_expression

Descrição

Adiciona ou subtrai int64_expression dias a ou de date_expression. Isso é equivalente às funções DATE_ADD ou DATE_SUB, quando o intervalo é expresso em dias.

Tipo de dados retornados

DATE

Exemplo

SELECT DATE "2020-09-22" + 1 AS day_later, DATE "2020-09-22" - 7 AS week_ago

+------------+------------+
| day_later  | week_ago   |
+------------+------------+
| 2020-09-23 | 2020-09-15 |
+------------+------------+

Subtração de data e hora

date_expression - date_expression
timestamp_expression - timestamp_expression
datetime_expression - datetime_expression

Descrição

Calcula a diferença entre dois valores de data e hora como um intervalo.

Tipo de dados retornados

INTERVAL

Exemplo

SELECT
  DATE "2021-05-20" - DATE "2020-04-19" AS date_diff,
  TIMESTAMP "2021-06-01 12:34:56.789" - TIMESTAMP "2021-05-31 00:00:00" AS time_diff

+-------------------+------------------------+
| date_diff         | time_diff              |
+-------------------+------------------------+
| 0-0 396 0:0:0     | 0-0 0 36:34:56.789     |
+-------------------+------------------------+

Operadores aritméticos de intervalo

Adição e subtração

date_expression + interval_expression = DATETIME
date_expression - interval_expression = DATETIME
timestamp_expression + interval_expression = TIMESTAMP
timestamp_expression - interval_expression = TIMESTAMP
datetime_expression + interval_expression = DATETIME
datetime_expression - interval_expression = DATETIME

Descrição

Adiciona um intervalo a um valor de data e hora ou subtrai um intervalo de um valor de data e hora. Exemplo

SELECT
  DATE "2021-04-20" + INTERVAL 25 HOUR AS date_plus,
  TIMESTAMP "2021-05-02 00:01:02.345" - INTERVAL 10 SECOND AS time_minus;

+-------------------------+--------------------------------+
| date_plus               | time_minus                     |
+-------------------------+--------------------------------+
| 2021-04-21 01:00:00     | 2021-05-02 00:00:52.345+00     |
+-------------------------+--------------------------------+

Multiplicação e divisão

interval_expression * integer_expression = INTERVAL
interval_expression / integer_expression = INTERVAL

Descrição

Multiplica ou divide um valor de intervalo por um número inteiro.

Exemplo

SELECT
  INTERVAL '1:2:3' HOUR TO SECOND * 10 AS mul1,
  INTERVAL 35 SECOND * 4 AS mul2,
  INTERVAL 10 YEAR / 3 AS div1,
  INTERVAL 1 MONTH / 12 AS div2

+----------------+--------------+-------------+--------------+
| mul1           | mul2         | div1        | div2         |
+----------------+--------------+-------------+--------------+
| 0-0 0 10:20:30 | 0-0 0 0:2:20 | 3-4 0 0:0:0 | 0-0 2 12:0:0 |
+----------------+--------------+-------------+--------------+

Operadores bit a bit

Todos os operadores bit a bit retornam o mesmo tipo e comprimento do primeiro operando.

Nome Sintaxe Tipo de dados de entrada Descrição
Bit a bit not ~ X Inteiro ou BYTES Executa a negação lógica em cada bit, formando o complemento dos números um do valor binário.
Bit a bit or X | Y X: número inteiro ou BYTES
Y: o mesmo tipo que X
Toma dois padrões de bits de comprimento igual e executa a operação lógica OR inclusiva em cada par dos bits correspondentes. Esse operador gerará um erro se X e Y forem BYTES de comprimentos diferentes.
Bit a bit xor X ^ Y X: número inteiro ou BYTES
Y: o mesmo tipo que X
Toma dois padrões de bits de comprimento igual e executa a operação lógica OR exclusiva em cada par dos bits correspondentes. Esse operador gerará um erro se X e Y forem BYTES de comprimentos diferentes.
Bit a bit and X & Y X: número inteiro ou BYTES
Y: o mesmo tipo que X
Toma dois padrões de bits de comprimento igual e executa a operação lógica AND em cada par dos bits correspondentes. Esse operador gerará um erro se X e Y forem BYTES de comprimentos diferentes.
Deslocado para a esquerda X << Y X: número inteiro ou BYTES
Y: INT64
Desloca o primeiro operando X para a esquerda. Esse operador retorna 0 ou uma sequência de bytes de b'\x00' se o segundo operando, Y, for maior ou igual ao comprimento do bit do primeiro operando, X, (por exemplo, 64, se X tiver o tipo INT64). Esse operador gerará um erro se Y for negativo.
Deslocado para a direita X >> Y X: número inteiro ou BYTES
Y: INT64
Desloca o primeiro operando X para a direita. Esse operador não faz a extensão de bit de sinal com um tipo com sinal. Isso significa que ele preenche com 0 os bits vagos à esquerda. Esse operador retorna 0 ou uma sequência de bytes de b'\x00' se o segundo operando, Y, for maior ou igual ao comprimento do bit do primeiro operando, X, (por exemplo, 64, se X tiver o tipo INT64). Esse operador gerará um erro se Y for negativo.

Operadores lógicos

O BigQuery é compatível com os operadores lógicos AND, OR e NOT. Os operadores lógicos permitem apenas entradas BOOL ou NULL e usam lógica de três valores (em inglês) para produzir um resultado. O resultado pode ser TRUE, FALSE ou NULL:

x y x AND y x OR y
TRUE TRUE TRUE TRUE
TRUE FALSE FALSE TRUE
TRUE NULL NULL TRUE
FALSE TRUE FALSE TRUE
FALSE FALSE FALSE FALSE
FALSE NULL FALSE NULL
NULL TRUE NULL TRUE
NULL FALSE FALSE NULL
NULL NULL NULL NULL
x NOT X
TRUE FALSE
FALSE TRUE
NULL NULL

Por exemplo

Os exemplos nesta seção fazem referência a uma tabela chamada entry_table:

+-------+
| entry |
+-------+
| a     |
| b     |
| c     |
| NULL  |
+-------+
SELECT 'a' FROM entry_table WHERE entry = 'a'

-- a => 'a' = 'a' => TRUE
-- b => 'b' = 'a' => FALSE
-- NULL => NULL = 'a' => NULL

+-------+
| entry |
+-------+
| a     |
+-------+
SELECT entry FROM entry_table WHERE NOT (entry = 'a')

-- a => NOT('a' = 'a') => NOT(TRUE) => FALSE
-- b => NOT('b' = 'a') => NOT(FALSE) => TRUE
-- NULL => NOT(NULL = 'a') => NOT(NULL) => NULL

+-------+
| entry |
+-------+
| b     |
| c     |
+-------+
SELECT entry FROM entry_table WHERE entry IS NULL

-- a => 'a' IS NULL => FALSE
-- b => 'b' IS NULL => FALSE
-- NULL => NULL IS NULL => TRUE

+-------+
| entry |
+-------+
| NULL  |
+-------+

Operadores de comparação

As comparações sempre retornam BOOL. Elas geralmente exigem que os dois operandos sejam do mesmo tipo. Se os operandos são de tipos diferentes e o BigQuery pode converter os valores deles em um tipo comum sem perda de precisão, ele geralmente os força a esse tipo comum para a comparação. O BigQuery geralmente coage literais ao tipo não literal, quando o primeiro tipo está presente. Os tipos de dados comparáveis são definidos na seção Tipos de dados.

STRUCTs aceitam apenas quatro operadores de comparação: igual (=), diferente (!= e <>) e IN.

As regras a seguir são aplicadas na comparação desses tipos de dados:

  • FLOAT64: todas as comparações com NaN retornam FALSE, exceto != e <>, que retornam TRUE.
  • BOOL: FALSE é menor que TRUE.
  • STRING: as strings são comparadas ponto de código a ponto de código. Isso significa que strings canonicamente equivalentes somente serão comparadas como iguais se tiverem sido normalizadas primeiro.
  • NULL: A convenção é válida aqui: qualquer operação com uma entrada NULL retorna NULL.
Nome Sintaxe Descrição
Menor que X < Y Retorna TRUE, se X for menor que Y. Esse operador é compatível com a especificação de compilação.
Menor que ou igual a X <= Y Retorna TRUE, se X for menor ou igual a Y. Esse operador é compatível com a especificação de compilação.
Maior que X > Y Retorna TRUE, se X for maior que Y. Esse operador é compatível com a especificação de compilação.
Maior que ou igual a X >= Y Retorna TRUE, se X for maior ou igual a Y. Esse operador é compatível com a especificação de compilação.
Igual X = Y Retorna TRUE, se X for igual a Y. Esse operador é compatível com a especificação de compilação.
Diferente X != Y
X <> Y
Retorna TRUE, se X não for igual a Y. Esse operador é compatível com a especificação de compilação.
BETWEEN X [NOT] BETWEEN Y AND Z

Retorna TRUE, se X [não] estiver dentro do intervalo especificado. O resultado de "X BETWEEN Y AND Z" é equivalente a "Y <= X AND X <= Z", mas X é avaliado somente uma vez no primeiro. Esse operador é compatível com a especificação de compilação.

LIKE X [NOT] LIKE Y Verifica se a STRING no primeiro operando, X, corresponde a um padrão especificado pelo segundo operando, Y. As expressões podem conter estes caracteres:
  • Um sinal de porcentagem "%" corresponde a qualquer número de caracteres ou bytes.
  • Um sublinhado "_" corresponde a um único caractere ou byte.
  • É possível inserir um caractere de escape em "\", "_" ou "%" usando duas barras invertidas. Por exemplo, "\\%". Se você estiver usando strings brutas, basta uma barra invertida. Por exemplo, r"\%";
IN Várias - veja abaixo Retorna FALSE se o operando à direita estiver vazio. Retorna NULL se o operando à esquerda for NULL. Retorna TRUE ou NULL, nunca FALSE, se o operando à direita contiver NULL. Os argumentos em qualquer lado do operador IN são expressões gerais. Nenhum operando precisa ser um literal, embora seja mais comum usar um literal à direita. X é avaliado apenas uma vez. Este operador geralmente é compatível com a especificação de compilação.

Quando a igualdade dos valores com tipo de dados STRUCT é testada, é possível que um ou mais campos sejam NULL. Nesses casos:

  • Se todos os valores de campo não NULL forem iguais, a comparação retornará NULL.
  • Se qualquer valor de campo não NULL for diferente, a comparação retornará FALSE.

A tabela a seguir demonstra como os tipos de dados STRUCT são comparados quando têm campos com valores NULL.

Struct1 Struct2 Struct1 = Struct2
STRUCT(1, NULL) STRUCT(1, NULL) NULL
STRUCT(1, NULL) STRUCT(2, NULL) FALSE
STRUCT(1,2) STRUCT(1, NULL) NULL

Operador EXISTS

EXISTS ( subquery )

Descrição

Retorna TRUE, se a subconsulta produzir uma ou mais linhas. Retorna FALSE, se a subconsulta não produzir linha alguma. Nunca retorna NULL. Para saber mais sobre como é possível usar uma subconsulta com EXISTS, consulte subconsultas EXISTS.

Exemplos

Neste exemplo, o operador EXISTS retorna FALSE porque não há linhas em Words em que a direção é south:

WITH Words AS (
  SELECT 'Intend' as value, 'east' as direction UNION ALL
  SELECT 'Secure', 'north' UNION ALL
  SELECT 'Clarity', 'west'
 )
SELECT EXISTS ( SELECT value FROM Words WHERE direction = 'south' ) as result;

+--------+
| result |
+--------+
| FALSE  |
+--------+

Operador IN

O operador IN aceita a seguinte sintaxe:

search_value [NOT] IN value_set

value_set:
  {
    (expression[, ...])
    | (subquery)
    | UNNEST(array_expression)
  }

Descrição

Verifica um valor igual em um conjunto de valores. Regras semânticas são aplicáveis, mas, em geral, IN retorna TRUE se um valor igual for encontrado, FALSE se um valor igual for excluído. Caso contrário, NULL. NOT IN retornará FALSE se um valor igual for encontrado, TRUE se um valor igual for excluído. Caso contrário, NULL.

  • search_value: a expressão que é comparada a um conjunto de valores.
  • value_set: um ou mais valores a serem comparados a um valor de pesquisa.

    • (expression[, ...]): uma lista de expressões.
    • (subquery): uma subconsulta que retorna uma única coluna. Os valores nessa coluna são o conjunto de valores. Se nenhuma linha for produzida, o conjunto de valores ficará vazio.
    • UNNEST(array_expression): um operador UNNEST que retorna uma coluna de valores de uma expressão de matriz. É equivalente a:

      IN (SELECT element FROM UNNEST(array_expression) AS element)
      

Regras semânticas

Ao usar o operador IN, a seguinte semântica se aplica nesta ordem:

  • Retorna FALSE se value_set estiver vazio.
  • Retorna NULL se search_value for NULL.
  • Retorna TRUE se value_set contém um valor igual a search_value.
  • Retorna NULL se value_set contém um NULL.
  • Retorna FALSE.

Ao usar o operador NOT IN, a seguinte semântica se aplica nesta ordem:

  • Retorna TRUE se value_set estiver vazio.
  • Retorna NULL se search_value for NULL.
  • Retorna FALSE se value_set contém um valor igual a search_value.
  • Retorna NULL se value_set contém um NULL.
  • Retorna TRUE.

Esse operador geralmente é compatível com a compilação. No entanto, x [NOT] IN UNNEST não é compatível.

A semântica de:

x IN (y, z, ...)

é definida como equivalente a

(x = y) OR (x = z) OR ...

e os formatos de subconsulta e matriz são definidos de maneira semelhante.

x NOT IN ...

é equivalente a:

NOT(x IN ...)

O formato UNNEST trata uma varredura de matriz como UNNEST na cláusula FROM:

x [NOT] IN UNNEST(<array expression>)

Esse formato é usado muitas vezes com parâmetros ARRAY. Exemplo:

x IN UNNEST(@array_parameter)

Consulte o tópico Matrizes para mais informações sobre como usar essa sintaxe.

IN pode ser usado com chaves de várias partes usando a sintaxe struct do construtor. Exemplo:

(Key1, Key2) IN ( (12,34), (56,78) )
(Key1, Key2) IN ( SELECT (table.a, table.b) FROM table )

Consulte o Tipo de estrutura para mais informações.

Tipo de dados retornados

BOOL

Exemplos

Use essas cláusulas WITH para emular tabelas temporárias de Words e Items nos exemplos a seguir:

WITH Words AS (
  SELECT 'Intend' as value UNION ALL
  SELECT 'Secure' UNION ALL
  SELECT 'Clarity' UNION ALL
  SELECT 'Peace' UNION ALL
  SELECT 'Intend'
 )
SELECT * FROM Words;

+----------+
| value    |
+----------+
| Intend   |
| Secure   |
| Clarity  |
| Peace    |
| Intend   |
+----------+
WITH
  Items AS (
    SELECT STRUCT('blue' AS color, 'round' AS shape) AS info UNION ALL
    SELECT STRUCT('blue', 'square') UNION ALL
    SELECT STRUCT('red', 'round')
  )
SELECT * FROM Items;

+----------------------------+
| info                       |
+----------------------------+
| {blue color, round shape}  |
| {blue color, square shape} |
| {red color, round shape}   |
+----------------------------+

Exemplo com IN e uma expressão:

SELECT * FROM Words WHERE value IN ('Intend', 'Secure');

+----------+
| value    |
+----------+
| Intend   |
| Secure   |
| Intend   |
+----------+

Exemplo com NOT IN e uma expressão:

SELECT * FROM Words WHERE value NOT IN ('Intend');

+----------+
| value    |
+----------+
| Secure   |
| Clarity  |
| Peace    |
+----------+

Exemplo com IN, uma subconsulta escalar e uma expressão:

SELECT * FROM Words WHERE value IN ((SELECT 'Intend'), 'Clarity');

+----------+
| value    |
+----------+
| Intend   |
| Clarity  |
| Intend   |
+----------+

Exemplo com IN e uma operação UNNEST:

SELECT * FROM Words WHERE value IN UNNEST(['Secure', 'Clarity']);

+----------+
| value    |
+----------+
| Secure   |
| Clarity  |
+----------+

Exemplo com IN e um STRUCT:

SELECT
  (SELECT AS STRUCT Items.info) as item
FROM
  Items
WHERE (info.shape, info.color) IN (('round', 'blue'));

+------------------------------------+
| item                               |
+------------------------------------+
| { {blue color, round shape} info } |
+------------------------------------+

Operadores IS

Os operadores IS retornam TRUE ou FALSE para a condição que estão testando. Eles nunca retornam NULL, mesmo para entradas NULL, ao contrário das funções IS_INF e IS_NAN definidas em Funções matemáticas. Se NOT estiver presente, o valor de saída BOOL será invertido.

Sintaxe da função Tipo de dados de entrada Tipo de dados de resultado Descrição

X IS TRUE
BOOL BOOL Avalia como VERDADEIRO se X for avaliado como VERDADEIRO. Caso contrário, avalia como FALSO.

X IS NOT TRUE
BOOL BOOL Avalia como FALSO se X for avaliado como VERDADEIRO. Caso contrário, avalia como VERDADEIRO.

X IS FALSE
BOOL BOOL Avalia como VERDADEIRO se X for avaliado como FALSO. Caso contrário, avalia como FALSO.

X IS NOT FALSE
BOOL BOOL Avalia como FALSO se X for avaliado como FALSO. Caso contrário, avalia como VERDADEIRO.

X IS NULL
Qualquer tipo de valor BOOL Avalia como VERDADEIRO se X for considerado NULL. Caso contrário, é avaliado como FALSO.

X IS NOT NULL
Qualquer tipo de valor BOOL Avalia como FALSO se X for considerado NULL. Caso contrário, é avaliado como VERDADEIRO.

X IS UNKNOWN
BOOL BOOL Avalia como VERDADEIRO se X for considerado NULL. Caso contrário, é avaliado como FALSO.

X IS NOT UNKNOWN
BOOL BOOL Avalia como FALSO se X for considerado NULL. Caso contrário, avalia como VERDADEIRO.

Operador IS DISTINCT FROM.

expression_1 IS [NOT] DISTINCT FROM expression_2

Descrição

IS DISTINCT FROM retornará TRUE se os valores de entrada forem considerados distintos entre si pelas cláusulas DISTINCT e GROUP BY. Caso contrário, retornará FALSE.

a IS DISTINCT FROM b ser TRUE equivale a:

  • SELECT COUNT(DISTINCT x) FROM UNNEST([a,b]) x retornando 2.
  • SELECT * FROM UNNEST([a,b]) x GROUP BY x retornando duas linhas.

a IS DISTINCT FROM b é equivalente a NOT (a = b), exceto nestes casos:

  • Esse operador nunca retorna NULL, então os valores NULL são considerados distintos dos valores não NULL, não outros NULL.
  • Os valores NaN são considerados diferentes dos outros valores NaN, mas não outros NaN.

Tipos de entrada

  • expression_1: o primeiro valor a ser comparado. Pode ser um tipo de dados agrupável, NULL ou NaN.
  • expression_2: o segundo valor a ser comparado. Pode ser um tipo de dados agrupável, NULL ou NaN.
  • NOT: se presente, o valor de saída BOOL é invertido.

Tipo de retorno

BOOL

Exemplos

Retornam TRUE:

SELECT 1 IS DISTINCT FROM 2
SELECT 1 IS DISTINCT FROM NULL
SELECT 1 IS NOT DISTINCT FROM 1
SELECT NULL IS NOT DISTINCT FROM NULL

Retornam FALSE:

SELECT NULL IS DISTINCT FROM NULL
SELECT 1 IS DISTINCT FROM 1
SELECT 1 IS NOT DISTINCT FROM 2
SELECT 1 IS NOT DISTINCT FROM NULL

Operador de concatenação

O operador de concatenação combina vários valores em um.

Sintaxe da função Tipo de dados de entrada Tipo de dados de resultado

STRING || STRING [ || ... ]
STRING STRING

BYTES || BYTES [ || ... ]
BYTES STRING

ARRAY<T> || ARRAY<T> [ || ... ]
ARRAY<T> ARRAY<T>

Expressões condicionais

As expressões condicionais impõem restrições na ordem de avaliação das entradas. Em essência, elas são avaliadas da esquerda para a direita, com curto-circuito, e somente avaliam o valor da saída escolhida. Por outro lado, todas as entradas das funções regulares são avaliadas antes de chamar a função. O curto-circuito em expressões condicionais pode ser explorado para o tratamento de erros ou ajuste do desempenho.

expr CASE

CASE expr
  WHEN expr_to_match THEN result
  [ ... ]
  [ ELSE else_result ]
  END

Descrição

Compara expr ao expr_to_match de cada cláusula WHENsucessiva e retorna o primeiro resultado em que essa comparação retorna verdadeiro. As cláusulas WHEN restantes e else_result não são avaliadas. Se a comparação expr = expr_to_match retorna falso ou NULL para todas as cláusulas WHEN, retorna else_result se estiver presente. Se não estiver presente, retorna NULL.

expr e expr_to_match podem ser de qualquer tipo. Eles precisam ser implicitamente coercíveis para um supertipo comum. As comparações de igualdade são feitas em valores coercíveis. Pode haver vários tipos de result. Expressões result e else_result devem ser coercíveis a um supertipo em comum.

Essa expressão é compatível com a especificação de compilação.

Tipo de dados retornados

Supertipo de result[, ...] e else_result.

Exemplo

WITH Numbers AS (
  SELECT 90 as A, 2 as B UNION ALL
  SELECT 50, 8 UNION ALL
  SELECT 60, 6 UNION ALL
  SELECT 50, 10
)
SELECT
  A,
  B,
  CASE A
    WHEN 90 THEN 'red'
    WHEN 50 THEN 'blue'
    ELSE 'green'
    END
    AS result
FROM Numbers

+------------------+
| A  | B  | result |
+------------------+
| 90 | 2  | red    |
| 50 | 8  | blue   |
| 60 | 6  | green  |
| 50 | 10 | blue   |
+------------------+

CASE

CASE
  WHEN condition THEN result
  [ ... ]
  [ ELSE else_result ]
  END

Descrição

Avalia a condição de cada cláusula WHEN sucessiva e retorna o primeiro resultado em que a condição é verdadeira. As cláusulas WHEN e else_result restantes não são avaliadas. Se todas as condições forem falsas ou NULL, retornará else_result se presente; se não estiver presente, retornará NULL.

condition precisa ser uma expressão booleana. Pode haver vários tipos de result. Expressões result e else_result precisam ser implicitamente coercíveis a um supertipo comum.

Essa expressão é compatível com a especificação de compilação.

Tipo de dados retornados

Supertipo de result[, ...] e else_result.

Exemplo

WITH Numbers AS (
  SELECT 90 as A, 2 as B UNION ALL
  SELECT 50, 6 UNION ALL
  SELECT 20, 10
)
SELECT
  A,
  B,
  CASE
    WHEN A > 60 THEN 'red'
    WHEN A > 30 THEN 'blue'
    ELSE 'green'
    END
    AS result
FROM Numbers

+------------------+
| A  | B  | result |
+------------------+
| 90 | 2  | red    |
| 50 | 6  | blue   |
| 20 | 10 | green  |
+------------------+

COALESCE

COALESCE(expr[, ...])

Descrição

Retorna o valor da primeira expressão não nula. As expressões restantes não são avaliadas. Uma expressão de entrada pode ser de qualquer tipo. Pode haver vários tipos de expressão de entrada. Todas as expressões de entrada precisam ser coercíveis implicitamente a um supertipo comum.

Tipo de dados retornados

Supertipo de expr[, ...].

Exemplos

SELECT COALESCE('A', 'B', 'C') as result

+--------+
| result |
+--------+
| A      |
+--------+
SELECT COALESCE(NULL, 'B', 'C') as result

+--------+
| result |
+--------+
| B      |
+--------+

IF

IF(expr, true_result, else_result)

Descrição

Se expr for verdadeiro, retornará true_result, caso contrário, retornará else_result. else_result não será avaliado se expr for verdadeiro. true_result não será avaliado se expr for falso ou NULL.

expr precisa ser uma expressão booleana. true_result e else_result precisam ser coercíveis a um supertipo comum.

Tipo de dados retornados

Supertipo de true_result e else_result.

Exemplo

WITH Numbers AS (
  SELECT 10 as A, 20 as B UNION ALL
  SELECT 50, 30 UNION ALL
  SELECT 60, 60
)
SELECT
  A,
  B,
  IF(A < B, 'true', 'false') AS result
FROM Numbers

+------------------+
| A  | B  | result |
+------------------+
| 10 | 20 | true   |
| 50 | 30 | false  |
| 60 | 60 | false  |
+------------------+

IFNULL

IFNULL(expr, null_result)

Descrição

Se expr for NULL, retornará null_result. Caso contrário, retorna expr. Se expr não for NULL, null_result não será avaliado.

expr e null_result podem ser de qualquer tipo e precisam ser implicitamente coercíveis a um supertipo comum. Sinônimo de COALESCE(expr, null_result).

Tipo de dados retornados

Supertipo de expr ou null_result.

Exemplos

SELECT IFNULL(NULL, 0) as result

+--------+
| result |
+--------+
| 0      |
+--------+
SELECT IFNULL(10, 0) as result

+--------+
| result |
+--------+
| 10     |
+--------+

NULLIF

NULLIF(expr, expr_to_match)

Descrição

Retornará NULL se expr = expr_to_match for verdadeiro. Caso contrário, retornará expr.

expr e expr_to_match precisam ser implicitamente coercíveis a um supertipo comum e precisam ser comparáveis.

Essa expressão é compatível com a especificação de compilação.

Tipo de dados retornados

Supertipo de expr e expr_to_match.

Exemplo

SELECT NULLIF(0, 0) as result

+--------+
| result |
+--------+
| NULL   |
+--------+
SELECT NULLIF(10, 0) as result

+--------+
| result |
+--------+
| 10     |
+--------+

FUNÇÕES

Funções de agregação

As funções de agregação geral a seguir estão disponíveis no SQL padrão do Google. Para saber mais sobre a sintaxe de chamadas de função de agregação, consulte Chamadas de funções de agregação.

ANY_VALUE

ANY_VALUE(
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descrição

Retorna expression para alguma linha escolhida do grupo. A escolha da linha é um processo não determinístico e não aleatório. Retorna NULL quando a entrada não produz linhas. Retorna NULL quando expression é NULL para todas as linhas no grupo.

ANY_VALUE se comporta como se RESPECT NULLS fosse especificado. As linhas em que expression é NULL são consideradas e podem ser selecionadas.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função de agregação.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função de janela.

Tipos de argumentos compatíveis

Qualquer um

Tipos de dados retornados

Corresponde aos tipos de dados de entrada.

Exemplos

SELECT ANY_VALUE(fruit) as any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;

+-----------+
| any_value |
+-----------+
| apple     |
+-----------+
SELECT
  fruit,
  ANY_VALUE(fruit) OVER (ORDER BY LENGTH(fruit) ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;

+--------+-----------+
| fruit  | any_value |
+--------+-----------+
| pear   | pear      |
| apple  | pear      |
| banana | apple     |
+--------+-----------+

ARRAY_AGG

ARRAY_AGG(
  [ DISTINCT ]
  expression
  [ { IGNORE | RESPECT } NULLS ]
  [ ORDER BY key [ { ASC | DESC } ] [, ... ] ]
  [ LIMIT n ]
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descrição

Retorna um ARRAY de valores expression.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função de agregação.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função de janela.

Um erro será gerado se uma matriz no resultado da consulta final contiver um elemento NULL.

Tipos de argumentos compatíveis

Todos os tipos de dados, exceto ARRAY.

Tipos de dados retornados

ARRAY

Se houver zero linhas de entrada, essa função retornará NULL.

Exemplos

SELECT ARRAY_AGG(x) AS array_agg FROM UNNEST([2, 1,-2, 3, -2, 1, 2]) AS x;

+-------------------------+
| array_agg               |
+-------------------------+
| [2, 1, -2, 3, -2, 1, 2] |
+-------------------------+
SELECT ARRAY_AGG(DISTINCT x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+---------------+
| array_agg     |
+---------------+
| [2, 1, -2, 3] |
+---------------+
SELECT ARRAY_AGG(x IGNORE NULLS) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

+-------------------+
| array_agg         |
+-------------------+
| [1, -2, 3, -2, 1] |
+-------------------+
SELECT ARRAY_AGG(x ORDER BY ABS(x)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+-------------------------+
| array_agg               |
+-------------------------+
| [1, 1, 2, -2, -2, 2, 3] |
+-------------------------+
SELECT ARRAY_AGG(x LIMIT 5) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+-------------------+
| array_agg         |
+-------------------+
| [2, 1, -2, 3, -2] |
+-------------------+
WITH vals AS
  (
    SELECT 1 x UNION ALL
    SELECT -2 x UNION ALL
    SELECT 3 x UNION ALL
    SELECT -2 x UNION ALL
    SELECT 1 x
  )
SELECT ARRAY_AGG(DISTINCT x ORDER BY x) as array_agg
FROM vals;

+------------+
| array_agg  |
+------------+
| [-2, 1, 3] |
+------------+
WITH vals AS
  (
    SELECT 1 x, 'a' y UNION ALL
    SELECT 1 x, 'b' y UNION ALL
    SELECT 2 x, 'a' y UNION ALL
    SELECT 2 x, 'c' y
  )
SELECT x, ARRAY_AGG(y) as array_agg
FROM vals
GROUP BY x;

+---------------+
| x | array_agg |
+---------------+
| 1 | [a, b]    |
| 2 | [a, c]    |
+---------------+
SELECT
  x,
  ARRAY_AGG(x) OVER (ORDER BY ABS(x)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+----+-------------------------+
| x  | array_agg               |
+----+-------------------------+
| 1  | [1, 1]                  |
| 1  | [1, 1]                  |
| 2  | [1, 1, 2, -2, -2, 2]    |
| -2 | [1, 1, 2, -2, -2, 2]    |
| -2 | [1, 1, 2, -2, -2, 2]    |
| 2  | [1, 1, 2, -2, -2, 2]    |
| 3  | [1, 1, 2, -2, -2, 2, 3] |
+----+-------------------------+

ARRAY_CONCAT_AGG

ARRAY_CONCAT_AGG(
  expression
  [ ORDER BY key [ { ASC | DESC } ] [, ... ] ]
  [ LIMIT n ]
)

Descrição

Concatena elementos de expression do tipo ARRAY, retornando um único ARRAY como resultado. Essa função ignora matrizes de entrada NULL, mas respeita os elementos NULL em matrizes de entrada diferentes de NULL. No entanto, um erro será gerado se houver um elemento NULL em uma matriz no resultado da consulta final.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função de agregação.

Tipos de argumentos compatíveis

ARRAY

Tipos de dados retornados

ARRAY

Retorna NULL se não houver nenhuma linha de entrada ou expression for avaliado como NULL para todas as linhas.

Exemplos

SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x)) AS array_concat_agg FROM (
  SELECT [NULL, 1, 2, 3, 4] AS x
  UNION ALL SELECT NULL
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+-----------------------------------+
| array_concat_agg                  |
+-----------------------------------+
| [NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9] |
+-----------------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x))) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+-----------------------------------+
| array_concat_agg                  |
+-----------------------------------+
| [5, 6, 7, 8, 9, 1, 2, 3, 4]       |
+-----------------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x LIMIT 2)) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+--------------------------+
| array_concat_agg         |
+--------------------------+
| [1, 2, 3, 4, 5, 6]       |
+--------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x) LIMIT 2)) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+------------------+
| array_concat_agg |
+------------------+
| [5, 6, 7, 8, 9]  |
+------------------+

AVG

AVG(
  [ DISTINCT ]
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descrição

Retorna a média de valores de entrada NULL, ou NaN se a entrada tiver um NaN.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função de agregação.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função de janela.

Tipos de argumentos compatíveis

Qualquer tipo de entrada numérica, como INT64. Para tipos de entrada de ponto flutuante, o resultado retornado não é determinista. Isso significa que o resultado pode ser diferente sempre que essa função for usada.

Tipos de dados retornados

INPUTINT64NUMERICBIGNUMERICFLOAT64
OUTPUTFLOAT64NUMERICBIGNUMERICFLOAT64

Exemplos

SELECT AVG(x) as avg
FROM UNNEST([0, 2, 4, 4, 5]) as x;

+-----+
| avg |
+-----+
| 3   |
+-----+
SELECT AVG(DISTINCT x) AS avg
FROM UNNEST([0, 2, 4, 4, 5]) AS x;

+------+
| avg  |
+------+
| 2.75 |
+------+
SELECT
  x,
  AVG(x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg
FROM UNNEST([0, 2, NULL, 4, 4, 5]) AS x;

+------+------+
| x    | avg  |
+------+------+
| NULL | NULL |
| 0    | 0    |
| 2    | 1    |
| 4    | 3    |
| 4    | 4    |
| 5    | 4.5  |
+------+------+

BIT_AND

BIT_AND(
  expression
)

Descrição

Executa uma operação bit a bit AND em expression e retorna o resultado.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função de agregação.

Tipos de argumentos compatíveis

  • INT64

Tipos de dados retornados

INT64

Exemplos

SELECT BIT_AND(x) as bit_and FROM UNNEST([0xF001, 0x00A1]) as x;

+---------+
| bit_and |
+---------+
| 1       |
+---------+

BIT_OR

BIT_OR(
  expression
)

Descrição

Executa uma operação bit a bit OR em expression e retorna o resultado.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função de agregação.

Tipos de argumentos compatíveis

  • INT64

Tipos de dados retornados

INT64

Exemplos

SELECT BIT_OR(x) as bit_or FROM UNNEST([0xF001, 0x00A1]) as x;

+--------+
| bit_or |
+--------+
| 61601  |
+--------+

BIT_XOR

BIT_XOR(
  [ DISTINCT ]
  expression
)

Descrição

Executa uma operação bit a bit XOR em expression e retorna o resultado.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função de agregação.

Tipos de argumentos compatíveis

  • INT64

Tipos de dados retornados

INT64

Exemplos

SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 5678    |
+---------+
SELECT BIT_XOR(DISTINCT x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+

COUNT

1.

COUNT(*)
[OVER over_clause]

2.

COUNT(
  [ DISTINCT ]
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descrição

  1. Retorna o número de linhas na entrada.
  2. Retorna o número de linhas com expression avaliado para qualquer valor diferente de NULL.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função de agregação.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função de janela.

Essa função com DISTINCT com a especificação de compilação.

Tipos de argumentos compatíveis

expression pode ser qualquer tipo de dados. Se DISTINCT estiver presente, expression só poderá ser um tipo de dados agrupável.

Tipos de dados retornados

INT64

Exemplos

É possível usar a função COUNT para retornar o número de linhas em uma tabela ou o número de valores distintos de uma expressão. Exemplo:

SELECT
  COUNT(*) AS count_star,
  COUNT(DISTINCT x) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;

+------------+--------------+
| count_star | count_dist_x |
+------------+--------------+
| 4          | 3            |
+------------+--------------+
SELECT
  x,
  COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
  COUNT(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;

+------+------------+--------------+
| x    | count_star | count_dist_x |
+------+------------+--------------+
| 1    | 3          | 2            |
| 4    | 3          | 2            |
| 4    | 3          | 2            |
| 5    | 1          | 1            |
+------+------------+--------------+
SELECT
  x,
  COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
  COUNT(x) OVER (PARTITION BY MOD(x, 3)) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;

+------+------------+---------+
| x    | count_star | count_x |
+------+------------+---------+
| NULL | 1          | 0       |
| 1    | 3          | 3       |
| 4    | 3          | 3       |
| 4    | 3          | 3       |
| 5    | 1          | 1       |
+------+------------+---------+

Se você quiser contar o número de valores distintos de uma expressão para que uma determinada condição é atendida, use este roteiro:

COUNT(DISTINCT IF(condition, expression, NULL))

Aqui, IF retornará o valor de expression se condition for TRUE. Caso contrário, retornará NULL. O COUNT(DISTINCT ...) próximo ignora os valores NULL, então serão contados apenas os valores distintos de expression para os quais condition é TRUE.

Por exemplo, para contar o número de valores positivos distintos de x:

SELECT COUNT(DISTINCT IF(x > 0, x, NULL)) AS distinct_positive
FROM UNNEST([1, -2, 4, 1, -5, 4, 1, 3, -6, 1]) AS x;

+-------------------+
| distinct_positive |
+-------------------+
| 3                 |
+-------------------+

Ou para contar o número de datas distintas em que um determinado tipo de evento ocorreu:

WITH Events AS (
  SELECT DATE '2021-01-01' AS event_date, 'SUCCESS' AS event_type
  UNION ALL
  SELECT DATE '2021-01-02' AS event_date, 'SUCCESS' AS event_type
  UNION ALL
  SELECT DATE '2021-01-02' AS event_date, 'FAILURE' AS event_type
  UNION ALL
  SELECT DATE '2021-01-03' AS event_date, 'SUCCESS' AS event_type
  UNION ALL
  SELECT DATE '2021-01-04' AS event_date, 'FAILURE' AS event_type
  UNION ALL
  SELECT DATE '2021-01-04' AS event_date, 'FAILURE' AS event_type
)
SELECT
  COUNT(DISTINCT IF(event_type = 'FAILURE', event_date, NULL))
    AS distinct_dates_with_failures
FROM Events;

+------------------------------+
| distinct_dates_with_failures |
+------------------------------+
| 2                            |
+------------------------------+

COUNTIF

COUNTIF(
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descrição

Retorna a contagem de valores TRUE para expression. Retorna 0 se houver zero linhas de entrada ou se expression for avaliado como FALSE ou NULL para todas as linhas.

Como expression precisa ser um BOOL, o formato COUNTIF(DISTINCT ...) não é compatível. Isso não seria útil: há apenas um valor distinto de TRUE. Normalmente, quando alguém quer combinar COUNTIF e DISTINCT, a intenção é contar o número de valores distintos de uma expressão para a qual uma determinada condição é atendida. Uma forma de fazer isso é:

COUNT(DISTINCT IF(condition, expression, NULL))

Observe que se usa COUNT, não COUNTIF. A parte IF foi colocada para dentro. Para saber mais, veja os exemplos de COUNT.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função de agregação.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função de janela.

Tipos de argumentos compatíveis

BOOL

Tipos de dados retornados

INT64

Exemplos

SELECT COUNTIF(x<0) AS num_negative, COUNTIF(x>0) AS num_positive
FROM UNNEST([5, -2, 3, 6, -10, -7, 4, 0]) AS x;

+--------------+--------------+
| num_negative | num_positive |
+--------------+--------------+
| 3            | 4            |
+--------------+--------------+
SELECT
  x,
  COUNTIF(x<0) OVER (ORDER BY ABS(x) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS num_negative
FROM UNNEST([5, -2, 3, 6, -10, NULL, -7, 4, 0]) AS x;

+------+--------------+
| x    | num_negative |
+------+--------------+
| NULL | 0            |
| 0    | 1            |
| -2   | 1            |
| 3    | 1            |
| 4    | 0            |
| 5    | 0            |
| 6    | 1            |
| -7   | 2            |
| -10  | 2            |
+------+--------------+

LOGICAL_AND

LOGICAL_AND(
  expression
)

Descrição

Retorna a lógica AND de todas as expressões diferentes de NULL. Retorna NULL, se não houver nenhuma linha de entrada ou expression for avaliado como NULL para todas as linhas.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função de agregação.

Tipos de argumentos compatíveis

BOOL

Tipos de dados retornados

BOOL

Exemplos

SELECT LOGICAL_AND(x) AS logical_and FROM UNNEST([true, false, true]) AS x;

+-------------+
| logical_and |
+-------------+
| false       |
+-------------+

LOGICAL_OR

LOGICAL_OR(
  expression
)

Descrição

Retorna a lógica OR de todas as expressões diferentes de NULL. Retorna NULL, se não houver nenhuma linha de entrada ou expression for avaliado como NULL para todas as linhas.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função de agregação.

Tipos de argumentos compatíveis

BOOL

Tipos de dados retornados

BOOL

Exemplos

SELECT LOGICAL_OR(x) AS logical_or FROM UNNEST([true, false, true]) AS x;

+------------+
| logical_or |
+------------+
| true       |
+------------+

MAX

MAX(
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descrição

Retorna o valor máximo de expressões não NULL. Retorna NULL, se não houver nenhuma linha de entrada ou expression for avaliado como NULL para todas as linhas. Retorna NaN se a entrada contiver um NaN.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função de agregação.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função de janela.

Essa função é compatível com a especificação de compilação.

Tipos de argumentos compatíveis

Qualquer tipo de dados ordenáveis.

Tipos de dados retornados

Igual ao tipo de dados usado como os valores da entrada.

Exemplos

SELECT MAX(x) AS max
FROM UNNEST([8, 37, 4, 55]) AS x;

+-----+
| max |
+-----+
| 55  |
+-----+
SELECT x, MAX(x) OVER (PARTITION BY MOD(x, 2)) AS max
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

+------+------+
| x    | max  |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8    | 8    |
| 4    | 8    |
| 37   | 55   |
| 55   | 55   |
+------+------+

MIN

MIN(
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descrição

Retorna o valor mínimo de expressões não NULL. Retorna NULL, se não houver nenhuma linha de entrada ou expression for avaliado como NULL para todas as linhas. Retorna NaN se a entrada contiver um NaN.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função de agregação.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função de janela.

Essa função é compatível com a especificação de compilação.

Tipos de argumentos compatíveis

Qualquer tipo de dados ordenáveis.

Tipos de dados retornados

Igual ao tipo de dados usado como os valores da entrada.

Exemplos

SELECT MIN(x) AS min
FROM UNNEST([8, 37, 4, 55]) AS x;

+-----+
| min |
+-----+
| 4   |
+-----+
SELECT x, MIN(x) OVER (PARTITION BY MOD(x, 2)) AS min
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

+------+------+
| x    | min  |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8    | 4    |
| 4    | 4    |
| 37   | 37   |
| 55   | 37   |
+------+------+

STRING_AGG

STRING_AGG(
  [ DISTINCT ]
  expression [, delimiter]
  [ ORDER BY key [ { ASC | DESC } ] [, ... ] ]
  [ LIMIT n ]
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descrição

Retorna um valor (STRING ou BYTES) recebido ao concatenar valores não NULL. Retorna NULL se não houver nenhuma linha de entrada ou expression for avaliado como NULL para todas as linhas.

Se um delimiter for especificado, os valores concatenados serão separados por esse delimitador; caso contrário, uma vírgula será usada como um delimitador.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função de agregação.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função de janela.

Tipos de argumentos compatíveis

STRING ou BYTES.

Tipos de dados retornados

STRING ou BYTES.

Exemplos

SELECT STRING_AGG(fruit) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+------------------------+
| string_agg             |
+------------------------+
| apple,pear,banana,pear |
+------------------------+
SELECT STRING_AGG(fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+------------------------------+
| string_agg                   |
+------------------------------+
| apple & pear & banana & pear |
+------------------------------+
SELECT STRING_AGG(DISTINCT fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+-----------------------+
| string_agg            |
+-----------------------+
| apple & pear & banana |
+-----------------------+
SELECT STRING_AGG(fruit, " & " ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+------------------------------+
| string_agg                   |
+------------------------------+
| pear & pear & apple & banana |
+------------------------------+
SELECT STRING_AGG(fruit, " & " LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+--------------+
| string_agg   |
+--------------+
| apple & pear |
+--------------+
SELECT STRING_AGG(DISTINCT fruit, " & " ORDER BY fruit DESC LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+---------------+
| string_agg    |
+---------------+
| pear & banana |
+---------------+
SELECT
  fruit,
  STRING_AGG(fruit, " & ") OVER (ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+--------+------------------------------+
| fruit  | string_agg                   |
+--------+------------------------------+
| NULL   | NULL                         |
| pear   | pear & pear                  |
| pear   | pear & pear                  |
| apple  | pear & pear & apple          |
| banana | pear & pear & apple & banana |
+--------+------------------------------+

SUM

SUM(
  [ DISTINCT ]
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descrição

Retorna a soma de valores não nulos.

Se a expressão for um valor de ponto flutuante, a soma não será determinista. Isso significa que o resultado pode ser diferente sempre que essa função for usada.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função de agregação.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função de janela.

Tipos de argumentos compatíveis

Quaisquer tipos de dados numéricos e INTERVAL compatíveis.

Tipos de dados retornados

INPUTINT64NUMERICBIGNUMERICFLOAT64INTERVAL
OUTPUTINT64NUMERICBIGNUMERICFLOAT64INTERVAL

Casos especiais:

Retorna NULL, se a entrada contiver apenas NULLs.

Retorna NULL, se a entrada não contiver linhas.

Retorna Inf, se a entrada contiver Inf.

Retorna -Inf se a entrada contiver -Inf.

Retorna NaN se a entrada contiver um NaN.

Retorna NaN se a entrada contiver uma combinação de Inf e -Inf.

Exemplos

SELECT SUM(x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 25  |
+-----+
SELECT SUM(DISTINCT x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 15  |
+-----+
SELECT
  x,
  SUM(x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+---+-----+
| x | sum |
+---+-----+
| 3 | 6   |
| 3 | 6   |
| 1 | 10  |
| 4 | 10  |
| 4 | 10  |
| 1 | 10  |
| 2 | 9   |
| 5 | 9   |
| 2 | 9   |
+---+-----+
SELECT
  x,
  SUM(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+---+-----+
| x | sum |
+---+-----+
| 3 | 3   |
| 3 | 3   |
| 1 | 5   |
| 4 | 5   |
| 4 | 5   |
| 1 | 5   |
| 2 | 7   |
| 5 | 7   |
| 2 | 7   |
+---+-----+
SELECT SUM(x) AS sum
FROM UNNEST([]) AS x;

+------+
| sum  |
+------+
| NULL |
+------+

Funções de agregação estatística

As funções de agregação estatística a seguir estão disponíveis no SQL padrão do Google. Para saber mais sobre a sintaxe de chamadas de função de agregação, consulte Chamadas de funções de agregação.

CORR

CORR(
  X1, X2
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descrição

Retorna o coeficiente de correlação de Pearson de um grupo de pares de números. O primeiro número de cada par é a variável dependente e o segundo número é a variável independente. O resultado retornado está entre -1 e 1. Um resultado igual a 0 indica que não há correlação.

Todos os tipos numéricos são aceitos. Se a entrada for NUMERIC ou BIGNUMERIC, a agregação interna será estável com a saída final convertida em uma FLOAT64. Caso contrário, a entrada é convertida em FLOAT64 antes da agregação, resultando em um resultado potencialmente instável.

Essa função ignora pares de entrada que tenham um ou mais valores NULL. Se houver menos de dois pares de entrada sem valores NULL, a função retornará NULL.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função de agregação.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função de janela.

Tipo de dados retornados

FLOAT64

COVAR_POP

COVAR_POP(
  X1, X2
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descrição

Retorna a covariância da população de pares de números. O primeiro número é a variável dependente e o segundo é a variável independente. O resultado retornado está entre -Inf e +Inf.

Todos os tipos numéricos são aceitos. Se a entrada for NUMERIC ou BIGNUMERIC, a agregação interna será estável com a saída final convertida em uma FLOAT64. Caso contrário, a entrada é convertida em FLOAT64 antes da agregação, resultando em um resultado potencialmente instável.

Essa função ignora pares de entrada que tenham um ou mais valores NULL. Se não houver um par de entrada sem valores NULL, esta função retornará NULL. Se houver exatamente um par de entrada sem valores NULL, esta função retornará 0.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função de agregação.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função de janela.

Tipo de dados retornados

FLOAT64

COVAR_SAMP

COVAR_SAMP(
  X1, X2
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descrição

Retorna a covariância de amostra de um conjunto de pares de números. O primeiro número é a variável dependente e o segundo é a variável independente. O resultado retornado está entre -Inf e +Inf.

Todos os tipos numéricos são aceitos. Se a entrada for NUMERIC ou BIGNUMERIC, a agregação interna será estável com a saída final convertida em uma FLOAT64. Caso contrário, a entrada é convertida em FLOAT64 antes da agregação, resultando em um resultado potencialmente instável.

Essa função ignora pares de entrada que tenham um ou mais valores NULL. Se houver menos de dois pares de entrada sem valores NULL, a função retornará NULL.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função de agregação.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função de janela.

Tipo de dados retornados

FLOAT64

STDDEV_POP

STDDEV_POP(
  [ DISTINCT ]
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descrição

Retorna o desvio padrão polarizado da população dos valores. O resultado retornado está entre 0 e +Inf.

Todos os tipos numéricos são aceitos. Se a entrada for NUMERIC ou BIGNUMERIC, a agregação interna será estável com a saída final convertida em uma FLOAT64. Caso contrário, a entrada é convertida em FLOAT64 antes da agregação, resultando em um resultado potencialmente instável.

Essa função ignora as entradas NULL. Se todas as entradas forem ignoradas, ela retornará NULL.

Mas se ela receber uma única entrada diferente de NULL, retornará 0.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função de agregação.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função de janela.

Tipo de dados retornados

FLOAT64

STDDEV_SAMP

STDDEV_SAMP(
  [ DISTINCT ]
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descrição

Retorna o desvio padrão polarizado de amostra dos valores. O resultado retornado está entre 0 e +Inf.

Todos os tipos numéricos são aceitos. Se a entrada for NUMERIC ou BIGNUMERIC, a agregação interna será estável com a saída final convertida em uma FLOAT64. Caso contrário, a entrada é convertida em FLOAT64 antes da agregação, resultando em um resultado potencialmente instável.

Essa função ignora as entradas NULL. Se houver menos de duas entradas diferentes de NULL, ela retornará NULL.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função de agregação.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função de janela.

Tipo de dados retornados

FLOAT64

STDDEV

STDDEV(
  [ DISTINCT ]
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descrição

Um sinônimo de STDDEV_SAMP.

VAR_POP

VAR_POP(
  [ DISTINCT ]
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descrição

Retorna a variância polarizada da população dos valores. O resultado retornado está entre 0 e +Inf.

Todos os tipos numéricos são aceitos. Se a entrada for NUMERIC ou BIGNUMERIC, a agregação interna será estável com a saída final convertida em uma FLOAT64. Caso contrário, a entrada é convertida em FLOAT64 antes da agregação, resultando em um resultado potencialmente instável.

Essa função ignora as entradas NULL. Se todas as entradas forem ignoradas, ela retornará NULL.

Mas se ela receber uma única entrada diferente de NULL, retornará 0.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função de janela.

Tipo de dados retornados

FLOAT64

VAR_SAMP

VAR_SAMP(
  [ DISTINCT ]
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descrição

Retorna a variância não polarizada de amostra dos valores. O resultado retornado está entre 0 e +Inf.

Todos os tipos numéricos são aceitos. Se a entrada for NUMERIC ou BIGNUMERIC, a agregação interna será estável com a saída final convertida em uma FLOAT64. Caso contrário, a entrada é convertida em FLOAT64 antes da agregação, resultando em um resultado potencialmente instável.

Essa função ignora as entradas NULL. Se houver menos de duas entradas diferentes de NULL, ela retornará NULL.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função de agregação.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função de janela.

Tipo de dados retornados

FLOAT64

VARIANCE

VARIANCE(
  [ DISTINCT ]
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descrição

Um sinônimo de VAR_SAMP.

Funções de agregação aproximada

As funções de agregação aproximada a seguir estão disponíveis no SQL padrão do Google. Para saber mais sobre a sintaxe de chamadas de função de agregação, consulte Chamadas de funções de agregação.

As funções de agregação aproximadas são escaláveis no que diz respeito ao uso e tempo da memória. No entanto, elas produzem resultados aproximados em vez de exatos. Essas funções normalmente exigem menos memória do que a função de agregação exata, como COUNT(DISTINCT ...), mas também apresentam incerteza estatística. Isso torna a agregação aproximada apropriada para streamings grandes de dados para os quais o uso de memória linear é impraticável, bem como para dados que já são aproximados.

As funções de agregação aproximadas nesta seção funcionam diretamente nos dados de entrada, em vez de uma estimativa intermediária dos dados. Essas funções não permitem que os usuários especifiquem a precisão da estimativa com sketches. Se quiser especificar a precisão com sketches, consulte:

APPROX_COUNT_DISTINCT

APPROX_COUNT_DISTINCT(
  expression
)

Descrição

Retorna o resultado aproximado para COUNT(DISTINCT expression). O valor retornado é uma estimativa estatística e não necessariamente o valor real.

Essa função é menos exata que COUNT(DISTINCT expression), mas funciona melhor com entradas muito grandes.

Tipos de argumentos compatíveis

Qualquer tipo de dado, exceto: ARRAY STRUCT

Tipos de dados retornados

INT64

Exemplos

SELECT APPROX_COUNT_DISTINCT(x) as approx_distinct
FROM UNNEST([0, 1, 1, 2, 3, 5]) as x;

+-----------------+
| approx_distinct |
+-----------------+
| 5               |
+-----------------+

APPROX_QUANTIL

APPROX_QUANTILES(
  [ DISTINCT ]
  expression, number
  [ { IGNORE | RESPECT } NULLS ]
)

Descrição

Retorna os limites aproximados para um grupo de valores expression, sendo que number representa quantos quantis serão criados. Essa função retorna uma matriz de elementos number + 1, em que o primeiro elemento é o mínimo aproximado e o último elemento é o máximo aproximado.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função agregadas.

Tipos de argumentos compatíveis

expression pode ser qualquer tipo de dado compatível, exceto: ARRAY STRUCT

number precisa ser INT64.

Tipos de dados retornados

Um ARRAY do tipo especificado pelo parâmetro expression.

Retorna NULL se não houver nenhuma linha de entrada ou expression for avaliado como NULL para todas as linhas.

Exemplos

SELECT APPROX_QUANTILES(x, 2) AS approx_quantiles
FROM UNNEST([1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [1, 5, 10]       |
+------------------+
SELECT APPROX_QUANTILES(x, 100)[OFFSET(90)] AS percentile_90
FROM UNNEST([1, 2, 3, 4, 5, 6, 7, 8, 9, 10]) AS x;

+---------------+
| percentile_90 |
+---------------+
| 9             |
+---------------+
SELECT APPROX_QUANTILES(DISTINCT x, 2) AS approx_quantiles
FROM UNNEST([1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [1, 6, 10]       |
+------------------+
SELECT FORMAT("%T", APPROX_QUANTILES(x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [NULL, 4, 10]    |
+------------------+
SELECT FORMAT("%T", APPROX_QUANTILES(DISTINCT x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [NULL, 6, 10]    |
+------------------+

APPROX_TOP_COUNT

APPROX_TOP_COUNT(
  expression, number
)

Descrição

Retorna os elementos principais aproximados de expression. O parâmetro number especifica o número de elementos retornados.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função agregadas.

Tipos de argumentos compatíveis

expression pode ser de qualquer tipo de dado compatível com a cláusula GROUP BY.

number precisa ser INT64.

Tipos de dados retornados

Uma ARRAY do tipo STRUCT. O STRUCT contém dois campos. O primeiro campo (chamado value) contém um valor de entrada. O segundo campo (chamado count) contém um INT64 que especifica o número de vezes que o valor foi retornado.

Retorna NULL, se houver linhas de entrada igual a zero.

Exemplos

SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST(["apple", "apple", "pear", "pear", "pear", "banana"]) as x;

+-------------------------+
| approx_top_count        |
+-------------------------+
| [{pear, 3}, {apple, 2}] |
+-------------------------+

Tratamento de NULL

APPROX_TOP_COUNT não ignora NULLs na entrada. Exemplo:

SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST([NULL, "pear", "pear", "pear", "apple", NULL]) as x;

+------------------------+
| approx_top_count       |
+------------------------+
| [{pear, 3}, {NULL, 2}] |
+------------------------+

APPROX_TOP_SUM

APPROX_TOP_SUM(
  expression, weight, number
)

Descrição

Retorna os elementos principais aproximados de expression, com base na soma de um weight atribuído. O parâmetro number especifica o número de elementos retornados.

Se a entrada weight for negativa ou NaN, esta função retornará um erro.

Para saber mais sobre os argumentos opcionais nessa função e como usá-los, consulte Chamadas de função agregadas.

Tipos de argumentos compatíveis

expression pode ser de qualquer tipo de dado compatível com a cláusula GROUP BY.

weight precisa atender a uma das seguintes condições:

  • INT64
  • NUMERIC
  • BIGNUMERIC
  • FLOAT64

number precisa ser INT64.

Tipos de dados retornados

Uma ARRAY do tipo STRUCT. O STRUCT contém dois campos: value e sum. O campo value contém o valor da expressão de entrada. O campo sum é do mesmo tipo que weight e é a soma aproximada do peso de entrada associado ao campo value.

Retorna NULL, se houver linhas de entrada igual a zero.

Exemplos

SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([
  STRUCT("apple" AS x, 3 AS weight),
  ("pear", 2),
  ("apple", 0),
  ("banana", 5),
  ("pear", 4)
]);

+--------------------------+
| approx_top_sum           |
+--------------------------+
| [{pear, 6}, {banana, 5}] |
+--------------------------+

Tratamento de NULL

APPROX_TOP_SUM não ignora valores NULL para os parâmetros expression e weight.

SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, NULL AS weight), ("pear", 0), ("pear", NULL)]);

+----------------------------+
| approx_top_sum             |
+----------------------------+
| [{pear, 0}, {apple, NULL}] |
+----------------------------+
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, 2)]);

+-------------------------+
| approx_top_sum          |
+-------------------------+
| [{NULL, 2}, {apple, 0}] |
+-------------------------+
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, NULL)]);

+----------------------------+
| approx_top_sum             |
+----------------------------+
| [{apple, 0}, {NULL, NULL}] |
+----------------------------+

Funções HyperLogLog++

O algoritmo do HyperLogLog ++ (HLL ++) estima a cardinalidade (em inglês) dos sketches.

As funções HLL ++ são funções agregadas aproximadas. A agregação aproximada normalmente requer menos memória do que a função de agregação exataCOUNT(DISTINCT), como , mas também apresenta incerteza estatística. Isso torna as funções HLL ++ apropriadas para grandes streamings de dados para os quais o uso de memória linear é impraticável, bem como para dados já aproximados.

Se você não precisar de esboços materializadas, use uma função de agregação aproximada com precisão definida pelo sistema, como APPROX_COUNT_DISTINCT. No entanto, APPROX_COUNT_DISTINCT não permite agregações parciais, agregações novas e precisão personalizada.

O BigQuery é compatível com as seguintes funções do HLL++:

HLL_COUNT.INIT

HLL_COUNT.INIT(input [, precision])

Descrição

Uma função de agregação que usa um ou mais valores input e os agrega em um sketch de HLL++ (em inglês). Cada sketch é representado usando o tipo de dados BYTES. Em seguida, é possível mesclar os sketches usando HLL_COUNT.MERGE ou HLL_COUNT.MERGE_PARTIAL. Se nenhuma mesclagem for necessária, será possível extrair do rascunho a contagem final de valores distintos usando HLL_COUNT.EXTRACT.

Essa função é compatível com um parâmetro opcional, precision. Ele define a precisão da estimativa ao custo da memória adicional necessária para processar os sketches ou armazená-los no disco. O intervalo desse valor é de 10 a 24. O valor padrão é 15. Para mais informações sobre precisão, consulte Precisão para esboços.

Se a entrada for NULL, essa função retornará NULL.

Para mais informações, consulte HyperLogLog in Practice: Algorithmic Engineering of a State of The Art Cardinality Estimation Algorithm.

Tipos de entradas compatíveis

  • INT64
  • NUMERIC
  • BIGNUMERIC
  • STRING
  • BYTES

Tipo de retorno

BYTES

Exemplo

A consulta a seguir cria esboços do HLL++ que contam o número de usuários diferentes com pelo menos uma fatura por país.

SELECT
  country,
  HLL_COUNT.INIT(customer_id, 10)
    AS hll_sketch
FROM
  UNNEST(
    ARRAY<STRUCT<country STRING, customer_id STRING, invoice_id STRING>>[
      ('UA', 'customer_id_1', 'invoice_id_11'),
      ('CZ', 'customer_id_2', 'invoice_id_22'),
      ('CZ', 'customer_id_2', 'invoice_id_23'),
      ('BR', 'customer_id_3', 'invoice_id_31'),
      ('UA', 'customer_id_2', 'invoice_id_24')])
GROUP BY country;

+---------+------------------------------------------------------------------------------------+
| country | hll_sketch                                                                         |
+---------+------------------------------------------------------------------------------------+
| UA      | "\010p\020\002\030\002 \013\202\007\r\020\002\030\n \0172\005\371\344\001\315\010" |
| CZ      | "\010p\020\002\030\002 \013\202\007\013\020\001\030\n \0172\003\371\344\001"       |
| BR      | "\010p\020\001\030\002 \013\202\007\013\020\001\030\n \0172\003\202\341\001"       |
+---------+------------------------------------------------------------------------------------+

HLL_COUNT.MERGE

HLL_COUNT.MERGE(sketch)

Descrição

Uma função de agregação que retorna a cardinalidade de vários sketches de conjunto do HLL ++ calculando a união deles.

Cada sketch precisa ser inicializado no mesmo tipo. As tentativas de mesclar sketches de tipos diferentes resultam em um erro. Por exemplo, não é possível mesclar um sketch inicializado nos dados de INT64 com um inicializado nos dados de STRING.

Se os sketches mesclados forem inicializados com precisões diferentes, a precisão será reduzida para o menor nível envolvido na mesclagem.

Essa função ignora os valores NULL ao mesclar sketches. Se a mesclagem acontecer em linhas de zero ou somente em valores NULL, a função retornará 0.

Tipos de entradas compatíveis

BYTES

Tipo de retorno

INT64

Exemplo

A consulta a seguir conta o número de usuários distintos em todos os países que têm pelo menos uma fatura.

SELECT HLL_COUNT.MERGE(hll_sketch) AS distinct_customers_with_open_invoice
FROM
  (
    SELECT
      country,
      HLL_COUNT.INIT(customer_id) AS hll_sketch
    FROM
      UNNEST(
        ARRAY<STRUCT<country STRING, customer_id STRING, invoice_id STRING, invoice_status STRING>>[
          ('UA', 'customer_id_1', 'invoice_id_11'),
          ('BR', 'customer_id_3', 'invoice_id_31'),
          ('CZ', 'customer_id_2', 'invoice_id_22'),
          ('CZ', 'customer_id_2', 'invoice_id_23'),
          ('BR', 'customer_id_3', 'invoice_id_31'),
          ('UA', 'customer_id_2', 'invoice_id_24')])
    GROUP BY country
  );

+--------------------------------------+
| distinct_customers_with_open_invoice |
+--------------------------------------+
|                                    3 |
+--------------------------------------+

HLL_COUNT.MERGE_PARTIAL

HLL_COUNT.MERGE_PARTIAL(sketch)

Descrição

Uma função de agregação que usa uma ou mais entradas sketch do HLL ++ (em inglês) e as mescla em um novo sketch.

Cada sketch precisa ser inicializado no mesmo tipo. As tentativas de mesclar sketches de tipos diferentes resultam em um erro. Por exemplo, não é possível mesclar um sketch inicializado nos dados de INT64 com um inicializado nos dados de STRING.

Se os sketches mesclados forem inicializados com precisões diferentes, a precisão será reduzida para o menor nível envolvido na mesclagem. Por exemplo, se MERGE_PARTIAL encontrar sketches de precisão 14 e 15, o novo sketch retornado terá precisão 14.

Essa função retornará NULL se não houver entrada ou se todas as entradas forem NULL.

Tipos de entradas compatíveis

BYTES

Tipo de retorno

BYTES

Exemplo

A consulta a seguir retorna um esboço do HLL++ que conta o número de usuários distintos que têm pelo menos uma fatura em todos os países.

SELECT HLL_COUNT.MERGE_PARTIAL(HLL_sketch) AS distinct_customers_with_open_invoice
FROM
  (
    SELECT
      country,
      HLL_COUNT.INIT(customer_id) AS hll_sketch
    FROM
      UNNEST(
        ARRAY<STRUCT<country STRING, customer_id STRING, invoice_id STRING, invoice_status STRING>>[
          ('UA', 'customer_id_1', 'invoice_id_11'),
          ('BR', 'customer_id_3', 'invoice_id_31'),
          ('CZ', 'customer_id_2', 'invoice_id_22'),
          ('CZ', 'customer_id_2', 'invoice_id_23'),
          ('BR', 'customer_id_3', 'invoice_id_31'),
          ('UA', 'customer_id_2', 'invoice_id_24')])
    GROUP BY country
  );

+----------------------------------------------------------------------------------------------+
| distinct_customers_with_open_invoice                                                         |
+----------------------------------------------------------------------------------------------+
| "\010p\020\006\030\002 \013\202\007\020\020\003\030\017 \0242\010\320\2408\352}\244\223\002" |
+----------------------------------------------------------------------------------------------+

HLL_COUNT.EXTRACT

HLL_COUNT.EXTRACT(sketch)

Descrição

Uma função escalar que extrai uma estimativa da cardinalidade de um único sketch do HLL++ (em inglês).

Se sketch for NULL, essa função retornará uma estimativa de cardinalidade de 0.

Tipos de entradas compatíveis

BYTES

Tipo de retorno

INT64

Exemplo

A consulta a seguir retorna o número de usuários distintos para cada país que tenham pelo menos uma fatura.

SELECT
  country,
  HLL_COUNT.EXTRACT(HLL_sketch) AS distinct_customers_with_open_invoice
FROM
  (
    SELECT
      country,
      HLL_COUNT.INIT(customer_id) AS hll_sketch
    FROM
      UNNEST(
        ARRAY<STRUCT<country STRING, customer_id STRING, invoice_id STRING, invoice_status STRING>>[
          ('UA', 'customer_id_1', 'invoice_id_11'),
          ('BR', 'customer_id_3', 'invoice_id_31'),
          ('CZ', 'customer_id_2', 'invoice_id_22'),
          ('CZ', 'customer_id_2', 'invoice_id_23'),
          ('BR', 'customer_id_3', 'invoice_id_31'),
          ('UA', 'customer_id_2', 'invoice_id_24')])
    GROUP BY country
  );

+---------+--------------------------------------+
| country | distinct_customers_with_open_invoice |
+---------+--------------------------------------+
| UA      |                                    2 |
| BR      |                                    1 |
| CZ      |                                    1 |
+---------+--------------------------------------+

Funções de numeração

As seções a seguir descrevem as funções de numeração compatíveis com o BigQuery. As funções de numeração são um subconjunto de funções da janela. Para criar uma chamada de função de janela e saber mais sobre a sintaxe das funções de janela, consulte Function_calls da janela.

As funções de numeração atribuem valores inteiros a cada linha com base na posição dentro da janela especificada. A sintaxe da cláusula OVER varia entre as funções de navegação.

RANK

RANK()
OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  ORDER BY expression [ { ASC | DESC }  ] [, ...]

Descrição

Retorna a classificação de ordinal (baseada em 1) de cada linha dentro da partição ordenada. Todas as linhas dos pares recebem o mesmo valor da classificação. A linha ou o grupo de linhas de pares seguintes recebem um valor da classificação que aumenta baseado no número de pares com o valor da classificação anterior, em vez de DENSE_RANK, que sempre aumenta em 1.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função Window.

Tipo de retorno

INT64

Exemplos

WITH Numbers AS
 (SELECT 1 as x
  UNION ALL SELECT 2
  UNION ALL SELECT 2
  UNION ALL SELECT 5
  UNION ALL SELECT 8
  UNION ALL SELECT 10
  UNION ALL SELECT 10
)
SELECT x,
  RANK() OVER (ORDER BY x ASC) AS rank
FROM Numbers

+-------------------------+
| x          | rank       |
+-------------------------+
| 1          | 1          |
| 2          | 2          |
| 2          | 2          |
| 5          | 4          |
| 8          | 5          |
| 10         | 6          |
| 10         | 6          |
+-------------------------+
WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 2:59:01', 'F30-34')
SELECT name,
  finish_time,
  division,
  RANK() OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;

+-----------------+------------------------+----------+-------------+
| name            | finish_time            | division | finish_rank |
+-----------------+------------------------+----------+-------------+
| Sophia Liu      | 2016-10-18 09:51:45+00 | F30-34   | 1           |
| Meghan Lederer  | 2016-10-18 09:59:01+00 | F30-34   | 2           |
| Nikki Leith     | 2016-10-18 09:59:01+00 | F30-34   | 2           |
| Jen Edwards     | 2016-10-18 10:06:36+00 | F30-34   | 4           |
| Lisa Stelzner   | 2016-10-18 09:54:11+00 | F35-39   | 1           |
| Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39   | 2           |
| Desiree Berry   | 2016-10-18 10:05:42+00 | F35-39   | 3           |
| Suzy Slane      | 2016-10-18 10:06:24+00 | F35-39   | 4           |
+-----------------+------------------------+----------+-------------+

DENSE_RANK

DENSE_RANK()
OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  ORDER BY expression [ { ASC | DESC }  ] [, ...]

Descrição

Retorna a classificação de ordinal (baseada em 1) de cada linha dentro da partição da janela. Todas as linhas de pares recebem o mesmo valor da classificação. O valor seguinte é incrementado em um.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função Window.

Tipo de retorno

INT64

Exemplos

WITH Numbers AS
 (SELECT 1 as x
  UNION ALL SELECT 2
  UNION ALL SELECT 2
  UNION ALL SELECT 5
  UNION ALL SELECT 8
  UNION ALL SELECT 10
  UNION ALL SELECT 10
)
SELECT x,
  DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank
FROM Numbers

+-------------------------+
| x          | dense_rank |
+-------------------------+
| 1          | 1          |
| 2          | 2          |
| 2          | 2          |
| 5          | 3          |
| 8          | 4          |
| 10         | 5          |
| 10         | 5          |
+-------------------------+
WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 2:59:01', 'F30-34')
SELECT name,
  finish_time,
  division,
  DENSE_RANK() OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;

+-----------------+------------------------+----------+-------------+
| name            | finish_time            | division | finish_rank |
+-----------------+------------------------+----------+-------------+
| Sophia Liu      | 2016-10-18 09:51:45+00 | F30-34   | 1           |
| Meghan Lederer  | 2016-10-18 09:59:01+00 | F30-34   | 2           |
| Nikki Leith     | 2016-10-18 09:59:01+00 | F30-34   | 2           |
| Jen Edwards     | 2016-10-18 10:06:36+00 | F30-34   | 3           |
| Lisa Stelzner   | 2016-10-18 09:54:11+00 | F35-39   | 1           |
| Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39   | 2           |
| Desiree Berry   | 2016-10-18 10:05:42+00 | F35-39   | 3           |
| Suzy Slane      | 2016-10-18 10:06:24+00 | F35-39   | 4           |
+-----------------+------------------------+----------+-------------+

PERCENT_RANK

PERCENT_RANK()
OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  ORDER BY expression [ { ASC | DESC }  ] [, ...]

Descrição

Retorna a classificação do percentil de uma linha definida como (RK-1)/(NR-1), na qual RK é o RANK da linha e NR é o número de linhas na partição. Retorna 0 se NR = 1.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função Window.

Tipo de retorno

FLOAT64

Exemplo

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 2:59:01', 'F30-34')
SELECT name,
  finish_time,
  division,
  PERCENT_RANK() OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;

+-----------------+------------------------+----------+---------------------+
| name            | finish_time            | division | finish_rank         |
+-----------------+------------------------+----------+---------------------+
| Sophia Liu      | 2016-10-18 09:51:45+00 | F30-34   | 0                   |
| Meghan Lederer  | 2016-10-18 09:59:01+00 | F30-34   | 0.33333333333333331 |
| Nikki Leith     | 2016-10-18 09:59:01+00 | F30-34   | 0.33333333333333331 |
| Jen Edwards     | 2016-10-18 10:06:36+00 | F30-34   | 1                   |
| Lisa Stelzner   | 2016-10-18 09:54:11+00 | F35-39   | 0                   |
| Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39   | 0.33333333333333331 |
| Desiree Berry   | 2016-10-18 10:05:42+00 | F35-39   | 0.66666666666666663 |
| Suzy Slane      | 2016-10-18 10:06:24+00 | F35-39   | 1                   |
+-----------------+------------------------+----------+---------------------+

CUME_DIST

CUME_DIST()
OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  ORDER BY expression [ { ASC | DESC }  ] [, ...]

Descrição

Retorna a classificação relativa de uma linha definida como NP/NR. NP é definido como o número de linhas que precedem ou são pares da linha atual. NR é o número de linhas da partição.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função Window.

Tipo de retorno

FLOAT64

Exemplo

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 2:59:01', 'F30-34')
SELECT name,
  finish_time,
  division,
  CUME_DIST() OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;

+-----------------+------------------------+----------+-------------+
| name            | finish_time            | division | finish_rank |
+-----------------+------------------------+----------+-------------+
| Sophia Liu      | 2016-10-18 09:51:45+00 | F30-34   | 0.25        |
| Meghan Lederer  | 2016-10-18 09:59:01+00 | F30-34   | 0.75        |
| Nikki Leith     | 2016-10-18 09:59:01+00 | F30-34   | 0.75        |
| Jen Edwards     | 2016-10-18 10:06:36+00 | F30-34   | 1           |
| Lisa Stelzner   | 2016-10-18 09:54:11+00 | F35-39   | 0.25        |
| Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39   | 0.5         |
| Desiree Berry   | 2016-10-18 10:05:42+00 | F35-39   | 0.75        |
| Suzy Slane      | 2016-10-18 10:06:24+00 | F35-39   | 1           |
+-----------------+------------------------+----------+-------------+

NTILE

NTILE(constant_integer_expression)
OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  ORDER BY expression [ { ASC | DESC }  ] [, ...]

Descrição

Essa função divide as linhas em intervalos constant_integer_expression com base na ordenação de linhas e retorna o número do intervalo com base em 1 que é atribuído a cada linha. O número de linhas nos intervalos pode diferir em 1, no máximo. Os valores restantes, isto é, o número de linhas divididas por intervalos, são distribuídos um para cada intervalo, começando com o intervalo 1. Se constant_integer_expression for avaliado como NULL, 0 ou negativo, um erro será fornecido.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função Window.

Tipo de retorno

INT64

Exemplo

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 2:59:01', 'F30-34')
SELECT name,
  finish_time,
  division,
  NTILE(3) OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;

+-----------------+------------------------+----------+-------------+
| name            | finish_time            | division | finish_rank |
+-----------------+------------------------+----------+-------------+
| Sophia Liu      | 2016-10-18 09:51:45+00 | F30-34   | 1           |
| Meghan Lederer  | 2016-10-18 09:59:01+00 | F30-34   | 1           |
| Nikki Leith     | 2016-10-18 09:59:01+00 | F30-34   | 2           |
| Jen Edwards     | 2016-10-18 10:06:36+00 | F30-34   | 3           |
| Lisa Stelzner   | 2016-10-18 09:54:11+00 | F35-39   | 1           |
| Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39   | 1           |
| Desiree Berry   | 2016-10-18 10:05:42+00 | F35-39   | 2           |
| Suzy Slane      | 2016-10-18 10:06:24+00 | F35-39   | 3           |
+-----------------+------------------------+----------+-------------+

ROW_NUMBER

ROW_NUMBER()
OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]

Descrição

Não requer a cláusula ORDER BY. Retorna o ordinal da linha sequencial (baseado em 1) de cada linha de cada partição ordenada. Se a cláusula ORDER BY não for especificada, o resultado será não determinístico.

Para saber mais sobre a cláusula OVER e como usá-la, consulte Chamadas de função Window.

Tipo de retorno

INT64

Exemplos

WITH Numbers AS
 (SELECT 1 as x
  UNION ALL SELECT 2
  UNION ALL SELECT 2
  UNION ALL SELECT 5
  UNION ALL SELECT 8
  UNION ALL SELECT 10
  UNION ALL SELECT 10
)
SELECT x,
  ROW_NUMBER() OVER (ORDER BY x) AS row_num
FROM Numbers

+-------------------------+
| x          | row_num    |
+-------------------------+
| 1          | 1          |
| 2          | 2          |
| 2          | 3          |
| 5          | 4          |
| 8          | 5          |
| 10         | 6          |
| 10         | 7          |
+-------------------------+
WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 2:59:01', 'F30-34')
SELECT name,
  finish_time,
  division,
  ROW_NUMBER() OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;

+-----------------+------------------------+----------+-------------+
| name            | finish_time            | division | finish_rank |
+-----------------+------------------------+----------+-------------+
| Sophia Liu      | 2016-10-18 09:51:45+00 | F30-34   | 1           |
| Meghan Lederer  | 2016-10-18 09:59:01+00 | F30-34   | 2           |
| Nikki Leith     | 2016-10-18 09:59:01+00 | F30-34   | 3           |
| Jen Edwards     | 2016-10-18 10:06:36+00 | F30-34   | 4           |
| Lisa Stelzner   | 2016-10-18 09:54:11+00 | F35-39   | 1           |
| Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39   | 2           |
| Desiree Berry   | 2016-10-18 10:05:42+00 | F35-39   | 3           |
| Suzy Slane      | 2016-10-18 10:06:24+00 | F35-39   | 4           |
+-----------------+------------------------+----------+-------------+

Funções de bit

O BigQuery é compatível com as funções Bit a seguir.

BIT_COUNT

BIT_COUNT(expression)

Descrição

A entrada, expression, precisa ser um número inteiro ou BYTES.

Retorna o número de bits que estão definidos na entrada expression. No caso de números inteiros com sinal, isso significa o número de bits na forma de complemento de dois.

Tipo de dados retornados

INT64

Exemplo

SELECT a, BIT_COUNT(a) AS a_bits, FORMAT("%T", b) as b, BIT_COUNT(b) AS b_bits
FROM UNNEST([
  STRUCT(0 AS a, b'' AS b), (0, b'\x00'), (5, b'\x05'), (8, b'\x00\x08'),
  (0xFFFF, b'\xFF\xFF'), (-2, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFE'),
  (-1, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF'),
  (NULL, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF')
]) AS x;

+-------+--------+---------------------------------------------+--------+
| a     | a_bits | b                                           | b_bits |
+-------+--------+---------------------------------------------+--------+
| 0     | 0      | b""                                         | 0      |
| 0     | 0      | b"\x00"                                     | 0      |
| 5     | 2      | b"\x05"                                     | 2      |
| 8     | 1      | b"\x00\x08"                                 | 1      |
| 65535 | 16     | b"\xff\xff"                                 | 16     |
| -2    | 63     | b"\xff\xff\xff\xff\xff\xff\xff\xfe"         | 63     |
| -1    | 64     | b"\xff\xff\xff\xff\xff\xff\xff\xff"         | 64     |
| NULL  | NULL   | b"\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff" | 80     |
+-------+--------+---------------------------------------------+--------+

Funções de conversão

O BigQuery é compatível com as seguintes funções de conversão. Essas conversões de tipo de dados são explícitas, mas algumas podem acontecer implicitamente. Saiba mais sobre conversões implícitas e explícitas aqui.

Visão geral do CAST

CAST(expression AS typename [format_clause])

Descrição

Usa-se a sintaxe de cast em uma consulta para indicar que é preciso converter o tipo do resultado de uma expressão em algum outro tipo.

Quando CAST é usado, pode ocorrer falha na consulta se o BigQuery não conseguir executar o cast. Caso queira proteger as consultas contra esses tipos de erros, use SAFE_CAST.

Os casts entre tipos compatíveis que não são mapeados com sucesso do valor original para o domínio de destino produzem erros de tempo de execução. Por exemplo, o cast de BYTES para STRING em que a sequência de bytes não é um UTF-8 válido resulta em um erro de ambiente de execução.

Alguns casts podem incluir uma cláusula de formato, que fornece instruções sobre como conduzir o cast. Por exemplo, é possível instruir um cast para converter uma sequência de bytes em uma string codificada em BASE64 em vez de uma string codificada em UTF-8.

A estrutura da cláusula de formato é exclusiva para cada tipo de cast e mais informações estão disponíveis na seção desse cast.

Exemplos

A consulta a seguir resulta em "true" se x for 1, "false" para qualquer outro valor que não seja NULL e NULL se x for NULL.

CAST(x=1 AS STRING)

CAST COMO ARRAY

CAST(expression AS ARRAY<element_type>)

Descrição

O BigQuery é compatível com cast para ARRAY. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • ARRAY

Regras de conversão

De Até Regras ao fazer o cast x
ARRAY ARRAY Precisa ser exatamente do mesmo tipo ARRAY.

CAST COMO BIGNUMERIC

CAST(expression AS BIGNUMERIC)

Descrição

O BigQuery é compatível com cast para BIGNUMERIC. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • INT64
  • FLOAT64
  • NUMERIC
  • BIGNUMERIC
  • STRING

Regras de conversão

De Até Regras ao fazer o cast x
FLOAT64 BIGNUMERIC O número de ponto flutuante será arredondado para metade de zero. Fazer cast de NaN, +inf ou -inf retornará um erro. Fazer cast de um valor fora do intervalo de BIGNUMERIC retornará um erro de estouro.
STRING BIGNUMERIC O literal numérico contido na STRING não pode exceder a precisão máxima ou o intervalo do tipo BIGNUMERIC, ou ocorrerá um erro. Se o número de dígitos depois da vírgula decimal exceder 38, o valor BIGNUMERIC resultante será arredondado para metade de zero para ter 38 dígitos após a vírgula decimal.

CAST COMO BOOL

CAST(expression AS BOOL)

Descrição

O BigQuery é compatível com o cast para BOOL. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • INT64
  • BOOL
  • STRING

Regras de conversão

De Até Regras ao fazer o cast x
INT64 BOOL Retorna FALSE se x for 0. Caso contrário, retorna TRUE.
STRING BOOL Retorna TRUE se x for "true" e FALSE se x for "false"
Todos os outros valores de x são inválidos e lançam um erro em vez de fazer o cast para BOOL.
As STRINGs não diferenciam maiúsculas de minúsculas durante a conversão para BOOL.

CAST COMO BYTES

CAST(expression AS BYTES [format_clause])

Descrição

O BigQuery é compatível com cast para BYTES. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • BYTES
  • STRING

Cláusula de formatação

Quando uma expressão de um tipo é convertida em outro tipo, você pode usar a cláusula de formato para fornecer instruções sobre como conduzir o cast. Use a cláusula de formato nesta seção se expression for um STRING.

Regras de conversão

De Até Regras ao fazer o cast x
STRING BYTES O cast das STRINGs para BYTES é feito usando a codificação UTF-8. Por exemplo, a STRING "©", após o cast para BYTES, torna-se uma sequência de dois bytes com os valores hexadecimais C2 e A9.

CAST COMO DATE

CAST(expression AS DATE [format_clause])

Descrição

O BigQuery é compatível com cast para DATE. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • STRING
  • TIME
  • DATETIME
  • TIMESTAMP

Cláusula de formatação

Quando uma expressão de um tipo é convertida em outro tipo, você pode usar a cláusula de formato para fornecer instruções sobre como conduzir o cast. Use a cláusula de formato nesta seção se expression for um STRING.

Regras de conversão

De Até Regras ao fazer o cast x
STRING DATE No caso do cast da string para data, a string precisa seguir o formato literal data compatível, independentemente do fuso horário. Se a expressão da string for inválida ou representar uma data fora do intervalo mínimo/máximo aceito, ocorrerá um erro.
TIMESTAMP DATE O cast de um carimbo de data/hora para uma data trunca efetivamente o carimbo de data/hora a partir do fuso horário padrão.

CAST COMO DATETIME

CAST(expression AS DATETIME [format_clause])

Descrição

O BigQuery é compatível com cast para DATETIME. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • STRING
  • TIME
  • DATETIME
  • TIMESTAMP

Cláusula de formatação

Quando uma expressão de um tipo é convertida em outro tipo, você pode usar a cláusula de formato para fornecer instruções sobre como conduzir o cast. Use a cláusula de formato nesta seção se expression for um STRING.

Regras de conversão

De Até Regras ao fazer o cast x
STRING DATETIME No cast da string para a data e hora, a string precisa seguir o formato literal de data e hora compatível, independentemente do fuso horário. Se a expressão da string for inválida ou representar uma data e hora fora do intervalo mínimo/máximo aceito, ocorrerá um erro.
TIMESTAMP DATETIME O cast de um carimbo de data/hora para uma data trunca efetivamente o carimbo de data/hora a partir do fuso horário padrão.

CAST COMO FLOAT64

CAST(expression AS FLOAT64)

Descrição

O BigQuery é compatível com cast para tipos de ponto flutuante. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • INT64
  • FLOAT64
  • NUMERIC
  • BIGNUMERIC
  • STRING

Regras de conversão

De Até Regras ao fazer o cast x
INT64 FLOAT64 Retorna um valor de ponto flutuante próximo, mas possivelmente não exato.
NUMERIC FLOAT64 NUMERIC será convertido no número de ponto flutuante mais próximo com uma possível perda de precisão.
BIGNUMERIC FLOAT64 BIGNUMERIC será convertido no número de ponto flutuante mais próximo com uma possível perda de precisão.
STRING FLOAT64 Retorna x como um valor de ponto flutuante, interpretando-o como tendo a mesma forma de um literal de ponto flutuante válido. Também é compatível com casts de "[+,-]inf" para [,-]Infinity, "[+,-]infinity" para [,-]Infinity e "[+,-]nan" para NaN. As conversões não diferenciam maiúsculas de minúsculas.

CAST COMO INT64

CAST(expression AS INT64)

Descrição

O BigQuery é compatível com cast para tipos inteiros. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • INT64
  • FLOAT64
  • NUMERIC
  • BIGNUMERIC
  • BOOL
  • STRING

Regras de conversão

De Até Regras ao fazer o cast x
FLOAT64 INT64 Retorna o valor inteiro mais próximo.
Casos em que haja metades, como 1,5 ou -0,5, são arredondados para metade de zero.
BOOL INT64 Retorna 1 se x for TRUE. Caso contrário, retorna 0.
STRING INT64 Uma string hexadecimal pode ser convertida em um número inteiro. Por exemplo, de 0x123 para 291 ou -0x123 para -291.

Exemplos

Se não estiver trabalhando com strings hex (0x123), faça o cast dessas strings como inteiras:

SELECT '0x123' as hex_value, CAST('0x123' as INT64) as hex_to_int;

+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| 0x123     | 291        |
+-----------+------------+
SELECT '-0x123' as hex_value, CAST('-0x123' as INT64) as hex_to_int;

+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| -0x123    | -291       |
+-----------+------------+

CAST AS INTERVAL

CAST(expression AS INTERVAL)

Descrição

O BigQuery é compatível com a transmissão para INTERVAL. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • STRING

Regras de conversão

De Até Regras ao fazer o cast x
STRING INTERVAL Ao fazer o cast da string para o intervalo, a string precisa estar em conformidade com o padrão Duração ISO 8601 ou para formatar o formato literal 'A-M D H:M:S.F'. Os formatos literais de intervalo parcial também são aceitos quando não são ambíguos, por exemplo, "H:M:S". Se a expressão da string for inválida ou representar um intervalo fora do intervalo mínimo/máximo aceito, ocorrerá um erro.

Exemplos

SELECT input, CAST(input AS INTERVAL) AS output
FROM UNNEST([
  '1-2 3 10:20:30.456',
  '1-2',
  '10:20:30',
  'P1Y2M3D',
  'PT10H20M30,456S'
]) input

+--------------------+--------------------+
| input              | output             |
+--------------------+--------------------+
| 1-2 3 10:20:30.456 | 1-2 3 10:20:30.456 |
| 1-2                | 1-2 0 0:0:0        |
| 10:20:30           | 0-0 0 10:20:30     |
| P1Y2M3D            | 1-2 3 0:0:0        |
| PT10H20M30,456S    | 0-0 0 10:20:30.456 |
+--------------------+--------------------+

CAST COMO NUMERIC

CAST(expression AS NUMERIC)

Descrição

O BigQuery é compatível com cast para NUMERIC. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • INT64
  • FLOAT64
  • NUMERIC
  • BIGNUMERIC
  • STRING

Regras de conversão

De Até Regras ao fazer o cast x
FLOAT64 NUMERIC O número de ponto flutuante será arredondado para metade de zero. Fazer cast de NaN, +inf ou -inf retornará um erro. Fazer cast de um valor fora do intervalo de NUMERIC retornará um erro de estouro.
STRING NUMERIC O literal numérico contido em STRING não pode exceder a precisão máxima ou o intervalo do tipo NUMERIC, ou ocorrerá um erro. Se o número de dígitos depois da vírgula decimal exceder nove, o valor resultante NUMERIC será arredondado para metade de zero para ter nove dígitos depois da vírgula decimal.

CAST COMO STRING

CAST(expression AS STRING [format_clause [AT TIME ZONE timezone_expr]])

Descrição

O BigQuery é compatível com cast para STRING. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • INT64
  • FLOAT64
  • NUMERIC
  • BIGNUMERIC
  • BOOL
  • BYTES
  • TIME
  • DATE
  • DATETIME
  • TIMESTAMP
  • INTERVAL
  • STRING

Cláusula de formatação

Quando uma expressão de um tipo é convertida em outro, é possível usar a cláusula de formato para fornecer instruções de como realizar o cast. É possível usar a cláusula de formato nesta seção se expression for um destes tipos de dados:

  • INT64
  • FLOAT64
  • NUMERIC
  • BIGNUMERIC
  • BYTES
  • TIME
  • DATE
  • DATETIME
  • TIMESTAMP

A cláusula de formato de STRING tem uma cláusula opcional extra chamada AT TIME ZONE timezone_expr, que pode ser usada para especificar um fuso horário específico a ser usado durante a formatação de um TIMESTAMP. Se essa cláusula opcional não for incluída ao formatar um TIMESTAMP, o fuso horário atual será usado.

Para mais informações, consulte os tópicos a seguir:

Regras de conversão

De Até Regras ao fazer o cast x
FLOAT64 STRING Retorna uma representação de string aproximada. Um NaN ou 0 retornado não será assinado.
BOOL STRING Retorna "true" se x for TRUE. Caso contrário, retorna "false".
BYTES STRING Retorna x interpretado como uma STRING UTF-8.
Por exemplo, o literal BYTES b'\xc2\xa9', após o cast para STRING, é interpretado como UTF-8 e se torna o caractere unicode “©”.
Um erro ocorre se x não for um UTF-8 válido.
TIME STRING O cast de um tipo de horário para uma string segue o formato HH:MM:SS, independentemente do fuso horário.
DATE STRING O cast de um tipo de data para uma string segue o formato YYYY-MM-DD, independentemente do fuso horário.
DATETIME STRING O cast de um tipo de data e hora para uma string segue o formato YYYY-MM-DD HH:MM:SS, independentemente do fuso horário.
TIMESTAMP STRING No cast de tipos de carimbo de data/hora para string, o carimbo é interpretado com o fuso horário padrão, que é o UTC. O número de dígitos de subsegundos produzidos depende do número de zeros à direita na parte de subsegundo. A função CAST truncará nenhum, três ou seis dígitos.
INTERVAL STRING A transmissão de um intervalo para uma string tem o formato Y-M D H:M:S.

Exemplos

SELECT CAST(CURRENT_DATE() AS STRING) AS current_date

+---------------+
| current_date  |
+---------------+
| 2021-03-09    |
+---------------+
SELECT CAST(CURRENT_DATE() AS STRING FORMAT 'DAY') AS current_day

+-------------+
| current_day |
+-------------+
| MONDAY      |
+-------------+
SELECT CAST(
  TIMESTAMP '2008-12-25 00:00:00+00:00'
  AS STRING FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM') AS date_time_to_string

-- Results depend upon where this query was executed.
+------------------------------+
| date_time_to_string          |
+------------------------------+
| 2008-12-24 16:00:00 -08:00   |
+------------------------------+
SELECT CAST(
  TIMESTAMP '2008-12-25 00:00:00+00:00'
  AS STRING FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM'
  AT TIME ZONE 'Asia/Kolkata') AS date_time_to_string

-- Because the time zone is specified, the result is always the same.
+------------------------------+
| date_time_to_string          |
+------------------------------+
| 2008-12-25 05:30:00 +05:30   |
+------------------------------+
SELECT CAST(INTERVAL 3 DAY AS STRING) AS interval_to_string

+--------------------+
| interval_to_string |
+--------------------+
| 0-0 3 0:0:0        |
+--------------------+
SELECT CAST(
  INTERVAL "1-2 3 4:5:6.789" YEAR TO SECOND
  AS STRING) AS interval_to_string

+--------------------+
| interval_to_string |
+--------------------+
| 1-2 3 4:5:6.789    |
+--------------------+

CAST COMO STRUCT

CAST(expression AS STRUCT)

Descrição

O BigQuery é compatível com cast para STRUCT. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • STRUCT

Regras de conversão

De Até Regras ao fazer o cast x
STRUCT STRUCT Permitido, se as condições a seguir forem atendidas:
  1. Os dois STRUCTs têm o mesmo número de campos.
  2. Os tipos originais de campos STRUCT podem passar por cast explicitamente para os tipos de campos de destino STRUCT correspondentes (definidos pela ordem e não pelo nome do campo).

CAST COMO TIME

CAST(expression AS TIME [format_clause])

Descrição

O BigQuery é compatível com cast para TIME. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • STRING
  • TIME
  • DATETIME
  • TIMESTAMP

Cláusula de formatação

Quando uma expressão de um tipo é convertida em outro tipo, você pode usar a cláusula de formato para fornecer instruções sobre como conduzir o cast. Use a cláusula de formato nesta seção se expression for um STRING.

Regras de conversão

De Até Regras ao fazer o cast x
STRING TIME Ao fazer o cast da string para o horário, a string precisa seguir o formato literal do horário compatível, independentemente do fuso horário. Se a expressão da string for inválida ou representar um horário fora do intervalo mínimo/máximo aceito, ocorrerá um erro.

CAST COMO TIMESTAMP

CAST(expression AS TIMESTAMP [format_clause [AT TIME ZONE timezone_expr]])

Descrição

O BigQuery é compatível com cast para TIMESTAMP. O parâmetro expression pode representar uma expressão para estes tipos de dados:

  • STRING
  • TIME
  • DATETIME
  • TIMESTAMP

Cláusula de formatação

Quando uma expressão de um tipo é convertida em outro tipo, você pode usar a cláusula de formato para fornecer instruções sobre como conduzir o cast. Use a cláusula de formato nesta seção se expression for um STRING.

A cláusula de formato de TIMESTAMP tem uma cláusula opcional extra chamada AT TIME ZONE timezone_expr, que pode ser usada para especificar um fuso horário específico a ser usado durante a formatação. Se essa cláusula opcional não for incluída, o fuso horário atual será usado.

Regras de conversão

De Até Regras ao fazer o cast x
STRING TIMESTAMP No cast de string para carimbo de data/hora, string_expression precisa seguir os formatos compatíveis de literal do carimbo de data/hora. Caso contrário, ocorrerá um erro no ambiente de execução. O próprio string_expression pode conter um fuso horário.

Se houver um fuso horário no string_expression, ele será usado para conversão. Caso contrário, será usado o fuso horário padrão, UTC. Se a string tiver menos de seis dígitos, ela será ampliada implicitamente.

Um erro será produzido se string_expression for inválido, tiver mais de seis dígitos de subsegundos (isto é, precisão maior que microssegundos) ou representar um horário fora do intervalo aceito do carimbo de data/hora.
DATE TIMESTAMP O cast de uma data para um carimbo de data/hora interpreta date_expression a partir da meia-noite (início do dia) no fuso horário UTC padrão.
DATETIME TIMESTAMP A transmissão de uma data e hora para um carimbo de data/hora interpreta datetime_expression no fuso horário padrão, UTC.

A maioria dos valores de data e hora válidos tem exatamente um carimbo de data/hora correspondente em cada fuso horário. No entanto, há determinadas combinações de valores de data e hora válidos e fusos horários que têm zero ou dois valores de carimbo de data/hora correspondentes. Isso acontece em um fuso horário em que os relógios estão adiantados ou atrasados, como no horário de verão. Quando houver dois carimbos de data/hora válidos, o mais antigo será usado. Quando não há um carimbo de data/hora válido, a duração do intervalo (geralmente uma hora) é adicionada à data e hora.

Exemplos

O exemplo a seguir transmite um carimbo de data/hora formatado em string como um carimbo de data/hora:

SELECT CAST("2020-06-02 17:00:53.110+00:00" AS TIMESTAMP) AS as_timestamp

-- Results depend upon where this query was executed.
+-----------------------------+
| as_timestamp                |
+-----------------------------+
| 2020-06-03 00:00:53.110 UTC |
+-----------------------------+

Os exemplos a seguir convertem uma data e hora formatadas em string como um carimbo de data/hora. Esses exemplos retornam a mesma saída do exemplo anterior.

SELECT CAST("06/02/2020 17:00:53.110" AS TIMESTAMP FORMAT 'MM/DD/YYYY HH:MI:SS' AT TIME ZONE 'America/Los_Angeles') AS as_timestamp
SELECT CAST("06/02/2020 17:00:53.110" AS TIMESTAMP FORMAT 'MM/DD/YYYY HH:MI:SS' AT TIME ZONE '00') AS as_timestamp
SELECT CAST('06/02/2020 17:00:53.110 +00' AS TIMESTAMP FORMAT 'YYYY-MM-DD HH:MI:SS TZH') AS as_timestamp

PARSE_BIGNUMERIC

PARSE_BIGNUMERIC(string_expression)

Descrição

Converte uma string em um valor BIGNUMERIC.

O literal numérico contido na string não pode exceder a precisão máxima ou o intervalo do tipo BIGNUMERIC, ou ocorrerá um erro. Se o número de dígitos depois da vírgula decimal exceder 38, o valor BIGNUMERIC resultante será arredondado para metade de zero para ter 38 dígitos depois da vírgula decimal.


-- This example shows how a string with a decimal point is parsed.
SELECT PARSE_BIGNUMERIC("123.45") AS parsed

+--------+
| parsed |
+--------+
| 123.45 |
+--------+

-- This example shows how a string with an exponent is parsed.
SELECT PARSE_BIGNUMERIC("123.456E37") AS parsed

+-----------------------------------------+
| parsed                                  |
+-----------------------------------------+
| 123400000000000000000000000000000000000 |
+-----------------------------------------+

-- This example shows the rounding when digits after the decimal point exceeds 38.
SELECT PARSE_BIGNUMERIC("1.123456789012345678901234567890123456789") as parsed

+------------------------------------------+
| parsed                                   |
+------------------------------------------+
| 1.12345678901234567890123456789012345679 |
+------------------------------------------+

Essa função é semelhante a usar a função CAST AS BIGNUMERIC, mas a função PARSE_BIGNUMERIC aceita apenas entradas de string e permite o seguinte na string:

  • Espaços entre o sinal (+/-) e o número
  • Sinais (+/-) após o número

Regras para strings de entrada válidas:

Regra Entrada de exemplo Saída
A string pode ter apenas dígitos, vírgulas, pontos decimais e sinais. "- 12,34567,89.0" -123456789
Espaços em branco são permitidos em qualquer lugar, exceto entre dígitos. " - 12.345 " -12.345
Somente dígitos e vírgulas são permitidos antes do ponto decimal. " 12,345,678" 12345678
Apenas dígitos são permitidos após o ponto decimal. "1.234 " 1.234
Use E ou e para expoentes. Depois do e, os dígitos e um indicador de sinal inicial são permitidos. " 123.45e-1" 12.345
Se a parte do número inteiro não estiver vazia, ela deverá conter pelo menos um dígito. " 0,.12 -" -0.12
Se a string tiver um ponto decimal, ele precisará conter pelo menos um dígito. " .1" 0,1
A string não pode conter mais de um sinal. " 0.5 +" 0,5

Tipo de dados retornados

BIGNUMERIC

Exemplos

Este exemplo mostra uma entrada com espaços antes, depois e entre o sinal e o número:

SELECT PARSE_BIGNUMERIC("  -  12.34 ") as parsed;

+--------+
| parsed |
+--------+
| -12.34 |
+--------+

Este exemplo mostra uma entrada com um expoente, bem como o sinal após o número:

SELECT PARSE_BIGNUMERIC("12.34e-1-") as parsed;

+--------+
| parsed |
+--------+
| -1.234 |
+--------+

Este exemplo mostra uma entrada com várias vírgulas na parte inteira do número:

SELECT PARSE_BIGNUMERIC("  1,2,,3,.45 + ") as parsed;

+--------+
| parsed |
+--------+
| 123.45 |
+--------+

Este exemplo mostra uma entrada com um ponto decimal e nenhum dígito na parte do número inteiro:

SELECT PARSE_BIGNUMERIC(".1234  ") as parsed;

+--------+
| parsed |
+--------+
| 0.1234 |
+--------+

Exemplos de entradas inválidas

Este exemplo é inválido porque a parte do número não contém dígitos:

SELECT PARSE_BIGNUMERIC(",,,.1234  ") as parsed;

Este exemplo é inválido porque há espaços em branco entre os dígitos:

SELECT PARSE_BIGNUMERIC("1  23.4 5  ") as parsed;

Este exemplo é inválido porque o número está vazio, exceto por um expoente:

SELECT PARSE_BIGNUMERIC("  e1 ") as parsed;

Este exemplo é inválido porque a string contém vários sinais:

SELECT PARSE_BIGNUMERIC("  - 12.3 - ") as parsed;

Este exemplo é inválido porque o valor do número está fora do intervalo de BIGNUMERIC:

SELECT PARSE_BIGNUMERIC("12.34E100 ") as parsed;

Este exemplo é inválido porque a string contém caracteres inválidos:

SELECT PARSE_BIGNUMERIC("$12.34") as parsed;

PARSE_NUMERIC

PARSE_NUMERIC(string_expression)

Descrição

Converte uma string em um valor NUMERIC.

O literal numérico contido na string não pode exceder a precisão máxima ou o intervalo do tipo NUMERIC, ou ocorrerá um erro. Se o número de dígitos depois do ponto decimal exceder nove, o valor resultando NUMERIC será arredondado para metade de zero para ter nove dígitos depois da vírgula decimal.


-- This example shows how a string with a decimal point is parsed.
SELECT PARSE_NUMERIC("123.45") AS parsed

+--------+
| parsed |
+--------+
| 123.45 |
+--------+

-- This example shows how a string with an exponent is parsed.
SELECT PARSE_NUMERIC("12.34E27") as parsed

+-------------------------------+
| parsed                        |
+-------------------------------+
| 12340000000000000000000000000 |
+-------------------------------+

-- This example shows the rounding when digits after the decimal point exceeds 9.
SELECT PARSE_NUMERIC("1.0123456789") as parsed

+-------------+
| parsed      |
+-------------+
| 1.012345679 |
+-------------+

Essa função é semelhante a usar a função CAST AS NUMERIC, exceto que a função PARSE_NUMERIC aceita apenas entradas de string e permite o seguinte na string:

  • Espaços entre o sinal (+/-) e o número
  • Sinais (+/-) após o número

Regras para strings de entrada válidas:

Regra Entrada de exemplo Saída
A string pode ter apenas dígitos, vírgulas, pontos decimais e sinais. "- 12,34567,89.0" -123456789
Espaços em branco são permitidos em qualquer lugar, exceto entre dígitos. " - 12.345 " -12.345
Somente dígitos e vírgulas são permitidos antes do ponto decimal. " 12,345,678" 12345678
Apenas dígitos são permitidos após o ponto decimal. "1.234 " 1.234
Use E ou e para expoentes. Depois do e, os dígitos e um indicador de sinal inicial são permitidos. " 123.45e-1" 12.345
Se a parte do número inteiro não estiver vazia, ela deverá conter pelo menos um dígito. " 0,.12 -" -0.12
Se a string tiver um ponto decimal, ele precisará conter pelo menos um dígito. " .1" 0,1
A string não pode conter mais de um sinal. " 0.5 +" 0,5

Tipo de dados retornados

NUMERIC

Exemplos

Este exemplo mostra uma entrada com espaços antes, depois e entre o sinal e o número:

SELECT PARSE_NUMERIC("  -  12.34 ") as parsed;

+--------+
| parsed |
+--------+
| -12.34 |
+--------+

Este exemplo mostra uma entrada com um expoente, bem como o sinal após o número:

SELECT PARSE_NUMERIC("12.34e-1-") as parsed;

+--------+
| parsed |
+--------+
| -1.234 |
+--------+

Este exemplo mostra uma entrada com várias vírgulas na parte inteira do número:

SELECT PARSE_NUMERIC("  1,2,,3,.45 + ") as parsed;

+--------+
| parsed |
+--------+
| 123.45 |
+--------+

Este exemplo mostra uma entrada com um ponto decimal e nenhum dígito na parte do número inteiro:

SELECT PARSE_NUMERIC(".1234  ") as parsed;

+--------+
| parsed |
+--------+
| 0.1234 |
+--------+

Exemplos de entradas inválidas

Este exemplo é inválido porque a parte do número não contém dígitos:

SELECT PARSE_NUMERIC(",,,.1234  ") as parsed;

Este exemplo é inválido porque há espaços em branco entre os dígitos:

SELECT PARSE_NUMERIC("1  23.4 5  ") as parsed;

Este exemplo é inválido porque o número está vazio, exceto por um expoente:

SELECT PARSE_NUMERIC("  e1 ") as parsed;

Este exemplo é inválido porque a string contém vários sinais:

SELECT PARSE_NUMERIC("  - 12.3 - ") as parsed;

Este exemplo é inválido porque o valor do número está fora do intervalo de BIGNUMERIC:

SELECT PARSE_NUMERIC("12.34E100 ") as parsed;

Este exemplo é inválido porque a string contém caracteres inválidos:

SELECT PARSE_NUMERIC("$12.34") as parsed;

SAFE_CAST

SAFE_CAST(expression AS typename [format_clause])

Descrição

Quando CAST é usado, pode ocorrer falha na consulta se o BigQuery não conseguir executar o cast. Por exemplo, a consulta a seguir gera um erro:

SELECT CAST("apple" AS INT64) AS not_a_number;

Caso queira proteger suas consultas contra esses tipos de erros, use SAFE_CAST. SAFE_CAST é idêntico a CAST, exceto que retorna NULL em vez de gerar um erro.

SELECT SAFE_CAST("apple" AS INT64) AS not_a_number;

+--------------+
| not_a_number |
+--------------+
| NULL         |
+--------------+

Se você estiver fazendo o cast de bytes para strings, também poderá usar a função SAFE_CONVERT_BYTES_TO_STRING. Todos os caracteres UTF-8 inválidos são substituídos pelo caractere de substituição unicode, U+FFFD. Consulte SAFE_CONVERT_BYTES_TO_STRING para mais informações.

Outras funções de conversão

Saiba mais sobre essas funções de conversão em outras partes da documentação:

Função de conversão De Até
ARRAY_TO_STRING ARRAY STRING
BOOL JSON BOOL
DATA Vários tipos de dados DATE
DATETIME Vários tipos de dados DATETIME
FLOAT64 JSON FLOAT64
FROM_BASE32 STRING BYTES
FROM_BASE64 STRING BYTES
FROM_HEX STRING BYTES
INT64 JSON INT64
PARSE_DATE STRING DATE
PARSE_DATETIME STRING DATETIME
PARSE_JSON STRING JSON
PARSE_TIME STRING TIME
PARSE_TIMESTAMP STRING TIMESTAMP
SAFE_CONVERT_BYTES_TO_STRING BYTES STRING
STRING TIMESTAMP STRING
STRING JSON STRING
TIME Vários tipos de dados TIME
TIMESTAMP Vários tipos de dados TIMESTAMP
TO_BASE32 BYTES STRING
TO_BASE64 BYTES STRING
TO_HEX BYTES STRING
TO_JSON Todos os tipos de dados. JSON
TO_JSON_STRING Todos os tipos de dados. STRING

Cláusula de formato para CAST

format_clause:
  FORMAT format_model

format_model:
  format_string_expression

A cláusula de formato pode ser usada em algumas funções CAST. Você usa uma cláusula de formato para fornecer instruções sobre como conduzir uma transmissão. Por exemplo, é possível instruir um cast para converter uma sequência de bytes em uma string codificada em BASE64 em vez de uma string codificada em UTF-8.

A cláusula de formato inclui um modelo de formato. O modelo pode conter elementos de formato combinados como uma string de formato.

Formatar bytes como string

CAST(bytes_expression AS STRING FORMAT format_string_expression)

É possível converter uma sequência de bytes em uma string com um elemento de formato na string de formato. Se os bytes não puderem ser formatados com um elemento de formato, um erro será retornado. Se a sequência de bytes for NULL, o resultado será NULL. Os elementos de formato não diferenciam maiúsculas de minúsculas.

Elemento de formato Retorna Exemplo
HEX Converte uma sequência de bytes em uma string hexadecimal. Entrada: b'\x00\x01\xEF\xFF'
Saída: 0001efff
BASEX Converte uma sequência de bytes em uma string codificada em BASEX. X representa um destes números: 2, 8, 16, 32, 64. Entrada como BASE8: b'\x02\x11\x3B'
Saída: 00410473
BASE64M Converte uma sequência de bytes em uma string codificada em BASE64 com base na RFC 2045 para MIME. Gera um caractere de nova linha ("\n") a cada 76 caracteres. Entrada: b'\xde\xad\xbe\xef'
Saída: 3q2+7w==
ASCII Converte uma sequência de bytes que são valores ASCII em uma string. Se a entrada contiver bytes que não são uma codificação ASCII válida, um erro será retornado. Entrada: b'\x48\x65\x6c\x6c\x6f'
Saída: Hello
UTF-8 Converte uma sequência de bytes que são valores UTF-8 em uma string. Se a entrada contiver bytes que não são uma codificação UTF-8 válida, um erro será retornado. Entrada: b'\x24'
Saída: $
UTF8 Mesmo comportamento de UTF-8.

Tipo de retorno

STRING

Exemplo

SELECT CAST(b'\x48\x65\x6c\x6c\x6f' AS STRING FORMAT 'ASCII') AS bytes_to_string;

+-----------------+
| bytes_to_string |
+-----------------+
| Hello           |
+-----------------+

Formatar string como bytes

CAST(string_expression AS BYTES FORMAT format_string_expression)

É possível converter uma string em bytes com um elemento de formato na string de formato. Se a string não puder ser formatada com o elemento de formato, um erro será retornado. Os elementos de formato não diferenciam maiúsculas de minúsculas.

Na expressão de string, os caracteres de espaço em branco, como \n, serão ignorados se o elemento de formato BASE64 ou BASE64M for usado.

Elemento de formato Retorna Exemplo
HEX Converte uma string codificada em hexadecimal em bytes. Se a entrada contiver caracteres que não fazem parte do alfabeto de codificação HEX (0~9, indiferente a maiúsculas), um erro será retornado. Entrada: '0001efff'
Saída: b'\x00\x01\xEF\xFF'
BASEX Converte uma string codificada em BASEX em bytes. X representa um destes números: 2, 8, 16, 32, 64. Um erro será retornado se a entrada contiver caracteres que não fazem parte do alfabeto de codificação BASEX, exceto caracteres de espaço em branco se o elemento de formato for BASE64. Entrada como BASE8: "00410473"
Saída: b'\x02\x11\x3B'
BASE64M Converte uma string codificada em BASE64 em bytes. Se a entrada contiver caracteres que não sejam espaços em branco e não façam parte do alfabeto de codificação BASE64 definido na rfc 2045, será retornado um erro. A decodificação de BASE64M e BASE64 têm o mesmo comportamento. Entrada: '3q2+7w=='
Saída: b'\xde\xad\xbe\xef'
ASCII Converte uma string com apenas caracteres ASCII em bytes. Se a entrada contiver caracteres que não são ASCII, será retornado um erro. Entrada: "Hello"
Saída: b'\x48\x65\x6c\x6c\x6f'
UTF-8 Converte uma string em uma sequência de bytes UTF-8. Entrada: "$"
Saída: b'\x24'
UTF8 Mesmo comportamento de UTF-8.

Tipo de retorno

BYTES

Exemplo

SELECT CAST('Hello' AS BYTES FORMAT 'ASCII') AS string_to_bytes

+-------------------------+
| string_to_bytes         |
+-------------------------+
| b'\x48\x65\x6c\x6c\x6f' |
+-------------------------+

Formatar data e hora como string

É possível formatar essas partes de data e hora como uma string:

A correspondência de maiúsculas e minúsculas é compatível quando você formata algumas partes de data ou hora como uma string e a saída contém letras. Para saber mais, consulte Correspondência de maiúsculas e minúsculas.

Correspondência de maiúsculas e minúsculas

Quando a saída de algum elemento de formato contém letras, os estojos de letra da saída são correspondidos com as letras maiúsculas do elemento de formato, o que significa que as palavras na saída são capitalizadas de acordo com a forma como o elemento de formato está em letras maiúsculas. Isso é chamado de correspondência de maiúsculas e minúsculas. As regras são:

  • Se as duas primeiras letras do elemento forem maiúsculas, as palavras na saída estarão em letras maiúsculas. Por exemplo, DAY = THURSDAY.
  • Se a primeira letra do elemento for maiúscula e a segunda letra for minúscula, a primeira letra de cada palavra na saída será maiúscula e outras letras serão minúsculas. Por exemplo, Day = Thursday.
  • Se a primeira letra do elemento for minúscula, todas as letras na saída serão minúsculas. Por exemplo, day = thursday.

Formatar parte do ano como string

CAST(expression AS STRING FORMAT format_string_expression)

Converte um tipo de dados que contém a parte do ano em uma string. Inclui elementos de formato, que fornecem instruções sobre como conduzir o cast.

  • expression: essa expressão contém o tipo de dados com o ano que você precisa formatar.
  • format_string_expression: uma string que contém elementos de formato, incluindo o elemento de formato do ano.

Esses tipos de dados incluem uma parte do ano:

  • DATE
  • DATETIME
  • TIMESTAMP

Se expression ou format_string_expression for NULL, o valor de retorno será NULL. Se format_string_expression for uma string vazia, a saída será uma string vazia. Um erro será gerado se um valor que não for um elemento de formato compatível aparecer em format_string_expression ou expression não contiver um valor especificado por um elemento de formato.

Elemento de formato Retorna Exemplo
AAAA Ano, quatro dígitos ou mais. Entrada: DATE '2018-01-30'
Saída: 2018
Entrada: DATE '76-01-30'
Saída: 0076
Entrada: DATE '10000-01-30'
Saída: 10000
YYY Ano, últimos três dígitos. Entrada: DATE '2018-01-30'
Saída: 018
Entrada: DATE '98-01-30'
Saída: 098
YY Ano e últimos dois dígitos. Entrada: DATE '2018-01-30'
Saída: 18
Entrada: DATE '8-01-30'
Entrada: 08
Y Ano e último dígito. Entrada: DATE '2018-01-30'
Saída: 8
RRRR Mesmo comportamento de YYYY.
RR Mesmo comportamento de YY.

Tipo de retorno

STRING

Exemplo

SELECT CAST(DATE '2018-01-30' AS STRING FORMAT 'YYYY') AS date_time_to_string;

+---------------------+
| date_time_to_string |
+---------------------+
| 2018                |
+---------------------+

Formatar parte do mês como string

CAST(expression AS STRING FORMAT format_string_expression)

Converte um tipo de dados que contém a parte do mês em uma string. Inclui elementos de formato, que fornecem instruções sobre como conduzir o cast.

  • expression: essa expressão contém o tipo de dados com o mês que você precisa formatar.
  • format_string_expression: uma string que contém elementos de formato, incluindo o elemento de formato de mês.

Esses tipos de dados incluem uma parte do mês:

  • DATE
  • DATETIME
  • TIMESTAMP

Se expression ou format_string_expression for NULL, o valor de retorno será NULL. Se format_string_expression for uma string vazia, a saída será uma string vazia. Um erro será gerado se um valor que não for um elemento de formato compatível aparecer em format_string_expression ou expression não contiver um valor especificado por um elemento de formato.

Elemento de formato Retorna Exemplo
MM Mês, 2 dígitos. Entrada: DATE '2018-01-30'
Saída: 01
SEG Nome abreviado de três caracteres do mês. Os nomes de meses abreviados para localidade em inglês são: JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEZ. A correspondência de maiúsculas e minúsculas é compatível. Entrada: DATE '2018-01-30'
Saída: JAN
MÊS Nome do mês. Há suporte para a correspondência de maiúsculas e minúsculas. Entrada: DATE '2018-01-30'
Saída: JANUARY

Tipo de retorno

STRING

Exemplo

SELECT CAST(DATE '2018-01-30' AS STRING FORMAT 'MONTH') AS date_time_to_string;

+---------------------+
| date_time_to_string |
+---------------------+
| JANUARY             |
+---------------------+

Formatar parte do dia como string

CAST(expression AS STRING FORMAT format_string_expression)

Converte um tipo de dados que contém a parte do dia em uma string. Inclui elementos de formato, que fornecem instruções sobre como conduzir o cast.

  • expression: essa expressão contém o tipo de dados com o dia que você precisa formatar.
  • format_string_expression: uma string que contém elementos de formato, incluindo o elemento de formato "dia".

Esses tipos de dados incluem uma parte do dia:

  • DATE
  • DATETIME
  • TIMESTAMP

Se expression ou format_string_expression for NULL, o valor de retorno será NULL. Se format_string_expression for uma string vazia, a saída será uma string vazia. Um erro será gerado se um valor que não for um elemento de formato compatível aparecer em format_string_expression ou expression não contiver um valor especificado por um elemento de formato.

Elemento de formato Retorna Exemplo
DIA Nome do dia da semana, localizado. Os espaços são preenchidos no lado direito para tornar a saída exatamente 9. Há suporte para a correspondência de maiúsculas e minúsculas. Entrada: DATE '2020-12-31'
Saída: THURSDAY
DY Nome abreviado de três caracteres do dia da semana (localizado). Os nomes abreviados dos dias da semana para a localidade en-US são: MON, TUE, WED, THU, FRI, SAT, SUN. Há suporte para a correspondência de maiúsculas e minúsculas. Entrada: DATE '2020-12-31'
Saída: THU
D Dia da semana (de 1 a 7), começando com domingo como 1. Entrada: DATE '2020-12-31'
Saída: 4
DD Dia do mês com dois dígitos. Entrada: DATE '2018-12-02'
Saída: 02
DDD Dia do ano com três dígitos. Entrada: DATE '2018-02-03'
Saída: 034

Tipo de retorno

STRING

Exemplo

SELECT CAST(DATE '2018-02-15' AS STRING FORMAT 'DD') AS date_time_to_string;

+---------------------+
| date_time_to_string |
+---------------------+
| 15                  |
+---------------------+

Formatar parte da hora como string

CAST(expression AS STRING FORMAT format_string_expression)

Converte um tipo de dados que contém a parte da hora em uma string. Inclui elementos de formato, que fornecem instruções sobre como conduzir o cast.

  • expression: essa expressão contém o tipo de dados com a hora que você precisa formatar.
  • format_string_expression: uma string que contém elementos de formato, incluindo o elemento de hora.

Esses tipos de dados incluem uma parte da hora:

  • TIME
  • DATETIME
  • TIMESTAMP

Se expression ou format_string_expression for NULL, o valor de retorno será NULL. Se format_string_expression for uma string vazia, a saída será uma string vazia. Um erro será gerado se um valor que não for um elemento de formato compatível aparecer em format_string_expression ou expression não contiver um valor especificado por um elemento de formato.

Elemento de formato Retorna Exemplo
HH Hora do dia, relógio de 12 horas, 2 dígitos. Entrada: TIME '21:30:00'
Saída: 09
HH12 Hora do dia em formato 12 horas. Entrada: TIME '21:30:00'
Saída: 09
HH24 Hora do dia, relógio de 24 horas, dois dígitos. Entrada: TIME '21:30:00'
Saída: 21

Tipo de retorno

STRING

Exemplos

SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'HH24') AS date_time_to_string;

+---------------------+
| date_time_to_string |
+---------------------+
| 21                  |
+---------------------+
SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'HH12') AS date_time_to_string;

+---------------------+
| date_time_to_string |
+---------------------+
| 09                  |
+---------------------+

Formatar parte do minuto como string

CAST(expression AS STRING FORMAT format_string_expression)

Converte um tipo de dados que contém a parte do minuto em uma string. Inclui elementos de formato, que fornecem instruções sobre como conduzir o cast.

  • expression: essa expressão contém o tipo de dados com o minuto que você precisa formatar.
  • format_string_expression: uma string que contém elementos de formato, incluindo o elemento de formato de minuto.

Esses tipos de dados incluem uma parte de um minuto:

  • TIME
  • DATETIME
  • TIMESTAMP

Se expression ou format_string_expression for NULL, o valor de retorno será NULL. Se format_string_expression for uma string vazia, a saída será uma string vazia. Um erro será gerado se um valor que não for um elemento de formato compatível aparecer em format_string_expression ou expression não contiver um valor especificado por um elemento de formato.

Elemento de formato Retorna Exemplo
MI Minuto, 2 dígitos Entrada: TIME '01:02:03'
Saída: 02

Tipo de retorno

STRING

Exemplo

SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'MI') AS date_time_to_string;

+---------------------+
| date_time_to_string |
+---------------------+
| 30                  |
+---------------------+

Formatar parte do segundo como string

CAST(expression AS STRING FORMAT format_string_expression)

Converte um tipo de dados que contém a segunda parte em uma string. Inclui elementos de formato, que fornecem instruções sobre como conduzir o cast.

  • expression: essa expressão contém o tipo de dados com o segundo que você precisa formatar.
  • format_string_expression: uma string que contém elementos de formato, incluindo o segundo elemento de formato.

Esses tipos de dados incluem uma segunda parte:

  • TIME
  • DATETIME
  • TIMESTAMP

Se expression ou format_string_expression for NULL, o valor de retorno será NULL. Se format_string_expression for uma string vazia, a saída será uma string vazia. Um erro será gerado se um valor que não for um elemento de formato compatível aparecer em format_string_expression ou expression não contiver um valor especificado por um elemento de formato.

Elemento de formato Retorna Exemplo
SS Segundos do minuto, dois dígitos. Entrada: TIME '01:02:03'
Saída: 03
SSSSS Segundos do dia, 5 dígitos. Entrada: TIME '01:02:03'
Saída: 03723
FFn Parte fracionária do segundo, n dígitos. Substitua n por um valor de 1 a 9. Por exemplo, FF5. A parte fracional do segundo é arredondada para se ajustar ao tamanho da saída. Entrada para: TIME '01:05:07.16'
Saída: 1
Entrada para FF2: TIME '01:05:07.16'
Saída: 16
Entrada para FF3: TIME '01:05:07.16'
Saída: 016

Tipo de retorno

STRING

Exemplos

SELECT CAST(TIME '21:30:25.16' AS STRING FORMAT 'SS') AS date_time_to_string;

+---------------------+
| date_time_to_string |
+---------------------+
| 25                  |
+---------------------+
SELECT CAST(TIME '21:30:25.16' AS STRING FORMAT 'FF2') AS date_time_to_string;

+---------------------+
| date_time_to_string |
+---------------------+
| 16                  |
+---------------------+

Formatar parte do indicador de meridiano como string

CAST(expression AS STRING FORMAT format_string_expression)

Converte um tipo de dados que contém a parte do indicador meridiano em uma string. Inclui elementos de formato, que fornecem instruções sobre como conduzir o cast.

  • expression: essa expressão contém o tipo de dados com o indicador meridiano que você precisa formatar.
  • format_string_expression: uma string que contém elementos de formato, incluindo o elemento indicador de meridiano.

Esses tipos de dados incluem uma parte do indicador de meridiano:

  • TIME
  • DATETIME
  • TIMESTAMP

Se expression ou format_string_expression for NULL, o valor de retorno será NULL. Se format_string_expression for uma string vazia, a saída será uma string vazia. Um erro será gerado se um valor que não for um elemento de formato compatível aparecer em format_string_expression ou expression não contiver um valor especificado por um elemento de formato.

Elemento de formato Retorna Exemplo
A.M. AM se o tempo for menor que 12. Caso contrário, o uso de letras maiúsculas na caixa de saída será determinado pela primeira letra do elemento do formato. Entrada para o AM: TIME '01:02:03'
Saída: AM
Entrada para AM: TIME '16:02:03'
Saída: P.M.
Entrada para a.m.: TIME '01:02:03'
Saída: a.m.
Entrada para a.M.: TIME '01:02:03'
Saída: a.m.
AM AM se o horário for menor que 12h. Caso contrário, será PM. A capitalização da saída é determinada pela primeira letra do elemento do formato. Entrada para AM: TIME '01:02:03'
Saída: AM
Entrada para AM: TIME '16:02:03'
Saída: PM
Entrada para am: TIME '01:02:03'
Saída: am
Entrada para aM: TIME '01:02:03'
Saída: am
P.M. A saída é o mesmo que o elemento de formato AM.
PM A saída é o mesmo que o elemento de formato AM.

Tipo de retorno

STRING

Exemplos

SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'AM') AS date_time_to_string;
SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'PM') AS date_time_to_string;

+---------------------+
| date_time_to_string |
+---------------------+
| PM                  |
+---------------------+
SELECT CAST(TIME '01:30:00' AS STRING FORMAT 'AM') AS date_time_to_string;
SELECT CAST(TIME '01:30:00' AS STRING FORMAT 'PM') AS date_time_to_string;

+---------------------+
| date_time_to_string |
+---------------------+
| AM                  |
+---------------------+

Formatar parte do fuso horário como string

CAST(expression AS STRING FORMAT format_string_expression)

Converte um tipo de dados que contém a parte do fuso horário em uma string. Inclui elementos de formato, que fornecem instruções sobre como conduzir o cast.

  • expression: essa expressão contém o tipo de dados com o fuso horário que você precisa formatar.
  • format_string_expression: uma string que contém elementos de formato, incluindo o elemento de formato de fuso horário.

Esses tipos de dados incluem uma parte do fuso horário:

  • DATE
  • TIME
  • DATETIME
  • TIMESTAMP

Se expression ou format_string_expression for NULL, o valor de retorno será NULL. Se format_string_expression for uma string vazia, a saída será uma string vazia. Um erro será gerado se um valor que não for um elemento de formato compatível aparecer em format_string_expression ou expression não contiver um valor especificado por um elemento de formato.

Elemento de formato Retorna Exemplo
TZH Deslocamento de hora para um fuso horário. Isso inclui o sinal +/- e a hora com dois dígitos. Entrada: TIMESTAMP '2008-12-25 05:30:00+00' Saída: −08
TZM Deslocamento de minutos para um fuso horário. Isso inclui apenas os dois dígitos do minuto. Carimbo de entrada: TIMESTAMP '2008-12-25 05:30:00+00' Saída: 00

Tipo de retorno

STRING

Exemplos

SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'TZH') AS date_time_to_string;

-- Results depend upon where this query was executed.
+---------------------+
| date_time_to_string |
+---------------------+
| -08                 |
+---------------------+
SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'TZH' AT TIME ZONE 'Asia/Kolkata')
AS date_time_to_string;

-- Because the time zone is specified, the result is always the same.
+---------------------+
| date_time_to_string |
+---------------------+
| +05                 |
+---------------------+
SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'TZM') AS date_time_to_string;

-- Results depend upon where this query was executed.
+---------------------+
| date_time_to_string |
+---------------------+
| 00                  |
+---------------------+
SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'TZM' AT TIME ZONE 'Asia/Kolkata')
AS date_time_to_string;

-- Because the time zone is specified, the result is always the same.
+---------------------+
| date_time_to_string |
+---------------------+
| 30                  |
+---------------------+

Formatar literal como string

CAST(expression AS STRING FORMAT format_string_expression)
Elemento de formato Retorna Exemplo
- A saída é igual à entrada. -
. A saída é igual à entrada. .
/ A saída é igual à entrada. /
, A saída é igual à entrada. ,
' A saída é igual à entrada. '
; A saída é igual à entrada. ;
: A saída é igual à entrada. :
Espaço em branco A saída é igual à entrada. Espaço em branco significa o caractere de espaço ASCII 32. Isso não significa outros tipos de espaço, como tabulação ou nova linha. Qualquer caractere de espaço em branco que não seja ASCII 32 no modelo de formato gera um erro.
"text" Saída é o valor entre aspas duplas. Para preservar aspas duplas ou caracteres de barra invertida, use a sequência de escape \" ou \\. Outras sequências de escape não são compatíveis. Entrada: "abc"
Saída: abc
Entrada: "a\"b\\c"
Saída: a"b\c

Formatar a string como data e hora

É possível formatar uma string com estas partes de data e hora:

Ao formatar uma string com partes de data e hora, siga as regras de modelo de formato.

Formatar regras de modelo

Ao fazer o cast de uma string para as partes de data e hora, é preciso garantir que o modelo de formato seja válido. O modelo de formato representa os elementos passados para CAST(string_expression AS type FORMAT format_string_expression) como o format_string_expression e é validado de acordo com as seguintes regras:

  • Ele contém, no máximo, uma das seguintes partes: indicador meridiano, ano, mês, dia, hora.
  • Um elemento de formato não literal, sem espaço em branco, não pode aparecer mais de uma vez.
  • Se ele contiver o elemento de formato de dia do ano, DDD, ele não poderá conter o mês.
  • Se ele contiver o elemento de formato de 24 horas, HH24, ele não poderá conter o elemento de formato de 12 horas ou um indicador meridiano.
  • Se ele contiver o elemento de formato de 12 horas, HH12 ou HH, ele também deverá conter um indicador meridiano.
  • Se ela contiver um indicador de meridiano, também deverá conter um elemento de formato de 12 horas.
  • Se ele contiver o segundo elemento do formato do dia, SSSSS, ele não poderá conter nenhum dos seguintes itens: hora, minuto, segundo ou indicador meridiano.
  • Ele não pode conter um elemento de formato de modo que o valor definido não exista no tipo de destino. Por exemplo, um elemento de formato de hora, como HH24, não pode aparecer em uma string que você está transmitindo como um DATE.

Formatar string como parte do ano

CAST(string_expression AS type FORMAT format_string_expression)

Converte um ano formatado em string em um tipo de dados que contém a parte do ano. Inclui elementos de formato, que fornecem instruções sobre como conduzir a transmissão.

  • string_expression: essa expressão contém a string com o ano que você precisa formatar.
  • type: o tipo de dados para o qual você está transmitindo. Precisa incluir a parte do ano.
  • format_string_expression: uma string que contém elementos de formato, incluindo o elemento de formato do ano. Os elementos de formato nessa string são definidos coletivamente como o modelo de formato, que precisa seguir estas regras.

Esses tipos de dados incluem uma parte do ano:

  • DATE
  • DATETIME
  • TIMESTAMP

Se a parte YEAR estiver ausente em string_expression e o tipo de retorno incluir essa parte, YEAR será definido como o ano atual.

Um erro será gerado se um valor que não for um elemento de formato compatível aparecer em format_string_expression ou string_expression não contiver um valor especificado por um elemento de formato.

Elemento de formato Retorna Exemplo
AAAA Se estiver delimitado, corresponde a 1 a 5 dígitos. Se não estiver delimitado, corresponde a 4 dígitos. Define a parte do ano como o número correspondente. Entrada para MM-DD-YYYY: '03-12-2018'
Saída como DATE: 2018-12-03
Entrada para YYYY-MMDD: '10000-1203'
Saída como DATE: 10000-12-03
Entrada para YYYY: '18'
Saída como DATE: 2018-03-01 (presuma que a data atual é 23 de março de 2021)
YYY Corresponde a 3 dígitos. Define os últimos três dígitos da parte do ano para o número correspondente. Entrada para YYY-MM-DD: '018-12-03'
Saída como DATE: 2018-12-03
Entrada para YYY-MM-DD: '038-12-03'
Saída como DATE: 2038-12-03
YY Corresponde a 2 dígitos. Define os últimos dois dígitos da parte do ano para o número correspondente. Entrada para YY-MM-DD: '18-12-03'
Saída como DATE: 2018-12-03
Entrada para YY-MM-DD: '38-12-03'
Saída como DATE: 2038-12-03
Y Corresponde a um dígito. Define o último dígito da parte do ano como o número correspondente. Entrada para Y-MM-DD: "8-12-03"
Saída como DATE: 2008-12-03
Y,YYY Corresponde ao padrão de 1 a 2 dígitos, vírgula e exatamente 3 dígitos. Define a parte do ano como o número correspondente. Entrada para Y,YYY-MM-DD: '2,018-12-03'
Saída como DATE: 2008-12-03
RRRR Mesmo comportamento de YYYY.
RR

Corresponde a 2 dígitos.

Se os dois dígitos inseridos estiverem entre 00 e 49 e os últimos dois dígitos do ano atual estiverem entre 00 e 49, o ano retornado terá os mesmos dois primeiros dígitos do ano atual. Se os dois últimos dígitos do ano atual estiverem entre 50 e 99, os dois primeiros dígitos do ano retornado serão 1 maiores que os dois primeiros dígitos do ano atual.

Se os dois dígitos inseridos estiverem entre 50 e 99 e os dois últimos dígitos do ano atual estiverem entre 00 e 49, os dois primeiros dígitos do ano retornado serão 1 menores que os dois primeiros 2 dígitos do ano atual. Se os últimos dois dígitos do ano atual estiverem entre 50 e 99, o ano retornado terá os mesmos dois dígitos do ano atual.

Entrada para RR-MM-DD: '18-12-03'
Saída como DATE: 2018-12-03 (executada no ano de 2021) Saída como DATE: 2118-12-03 (executada no ano de 2050)
Entrada para RR-MM-DD: '50-12-03'
Saída como DATE: 2050-12-03 (executada no ano de 2021) Output as DATE: 2050-12-03 (executada no ano de 2050)

Tipo de retorno

O tipo de dados em que a string foi transmitida. Pode ser o seguinte:

  • DATE
  • DATETIME
  • TIMESTAMP

Exemplos

SELECT CAST('18-12-03' AS DATE FORMAT 'YY-MM-DD') AS string_to_date

+----------------+
| string_to_date |
+----------------+
| 2018-02-03     |
+----------------+

Formatar string como parte do mês

CAST(string_expression AS type FORMAT format_string_expression)

Converte um mês formatado em string em um tipo de dados que contém a parte do mês. Inclui elementos de formato, que fornecem instruções sobre como conduzir a transmissão.

  • string_expression: essa expressão contém a string com o mês que você precisa formatar.
  • type: o tipo de dados para o qual você está transmitindo. Precisa incluir a parte do mês.
  • format_string_expression: uma string que contém elementos de formato, incluindo o elemento de formato de mês. Os elementos de formato nessa string são definidos coletivamente como o modelo de formato, que precisa seguir estas regras.

Esses tipos de dados incluem uma parte do mês:

  • DATE
  • DATETIME
  • TIMESTAMP

Se a parte MONTH estiver ausente em string_expression e o tipo de retorno incluir essa parte, MONTH será definido como o mês atual.

Um erro será gerado se um valor que não for um elemento de formato compatível aparecer em format_string_expression ou string_expression não contiver um valor especificado por um elemento de formato.

Elemento de formato Retorna Exemplo
MM Corresponde a 2 dígitos. Define a parte do mês para o número correspondente. Entrada para MM-DD-YYYY: '03-12-2018'
Saída como DATE: 2018-12-03
SEG Corresponde a três letras. Define a parte do mês como a string correspondente interpretada como o nome abreviado do mês. Entrada para MON DD, AAAA: 'DEC 03, 2018'
Saída como DATE: 2018-12-03
MÊS Corresponde a nove letras. Define a parte do mês como a string correspondente interpretada como o nome do mês. Entrada para MONTH DD, YYYY: 'DECEMBER 03, 2018'
Saída como DATE: 2018-12-03

Tipo de retorno

O tipo de dados em que a string foi transmitida. Pode ser o seguinte:

  • DATE
  • DATETIME
  • TIMESTAMP

Exemplos

SELECT CAST('DEC 03, 2018' AS DATE FORMAT 'MON DD, YYYY') AS string_to_date

+----------------+
| string_to_date |
+----------------+
| 2018-12-03     |
+----------------+

Formatar string como parte do dia

CAST(string_expression AS type FORMAT format_string_expression)

Converte um dia formatado em string em um tipo de dados que contém a parte do dia. Inclui elementos de formato, que fornecem instruções sobre como conduzir a transmissão.

  • string_expression: essa expressão contém a string com o dia que você precisa formatar.
  • type: o tipo de dados para o qual você está transmitindo. Precisa incluir a parte do dia.
  • format_string_expression: uma string que contém elementos de formato, incluindo o elemento de formato "dia". Os elementos de formato nessa string são definidos coletivamente como o modelo de formato, que precisa seguir estas regras.

Esses tipos de dados incluem uma parte do dia:

  • DATE
  • DATETIME
  • TIMESTAMP

Se a parte DAY estiver ausente de string_expression e o tipo de retorno incluir essa parte, DAY será definido como 1.

Um erro será gerado se um valor que não for um elemento de formato compatível aparecer em format_string_expression ou string_expression não contiver um valor especificado por um elemento de formato.

Elemento de formato Retorna Exemplo
DD Corresponde a 2 dígitos. Define a parte do dia para o número correspondente. Entrada para MONTH DD, YYYY: 'DECEMBER 03, 2018'
Saída como DATE: 2018-12-03

Tipo de retorno

O tipo de dados em que a string foi transmitida. Pode ser o seguinte:

  • DATE
  • DATETIME
  • TIMESTAMP

Exemplos

SELECT CAST('DECEMBER 03, 2018' AS DATE FORMAT 'MONTH DD, YYYY') AS string_to_date

+----------------+
| string_to_date |
+----------------+
| 2018-12-03     |
+----------------+

Formatar a string como parte da hora

CAST(string_expression AS type FORMAT format_string_expression)

Converte uma hora formatada em string em um tipo de dados que contém a parte da hora. Inclui elementos de formato, que fornecem instruções sobre como conduzir a transmissão.

  • string_expression: essa expressão contém a string com a hora que você precisa formatar.
  • type: o tipo de dados para o qual você está transmitindo. Precisa incluir a parte da hora.
  • format_string_expression: uma string que contém elementos de formato, incluindo o elemento de hora. Os elementos de formato nessa string são definidos coletivamente como o modelo de formato, que precisa seguir estas regras.

Esses tipos de dados incluem uma parte da hora:

  • TIME
  • DATETIME
  • TIMESTAMP

Se a parte HOUR estiver ausente em string_expression e o tipo de retorno incluir essa parte, HOUR será definido como 0.

Um erro será gerado se um valor que não for um elemento de formato compatível aparecer em format_string_expression ou string_expression não contiver um valor especificado por um elemento de formato.

Elemento de formato Retorna Exemplo
HH Corresponde a 2 dígitos. Se o número correspondente n for 12, define temp = 0; caso contrário, define temp = n. Se o valor correspondente do elemento de formato AM/PM for PM, definirá temp = n + 12. Define a parte da hora como temp. Um indicador meridiano precisa estar presente no modelo de formato quando HH estiver presente. Entrada para HH:MI PM: '03:30 P.M.'
Saída como TIME: 15:30:00
HH12 Mesmo comportamento que HH.
HH24 Corresponde a 2 dígitos. Define a parte da hora para o número correspondente. Entrada para HH24:MI: '15:30'
Saída como TIME: 15:30:00

Tipo de retorno

O tipo de dados em que a string foi transmitida. Pode ser o seguinte:

  • TIME
  • DATETIME
  • TIMESTAMP

Exemplos

SELECT CAST('15:30' AS TIME FORMAT 'HH24:MI') AS string_to_date_time

+---------------------+
| string_to_date_time |
+---------------------+
| 15:30:00            |
+---------------------+

Formatar string como parte do minuto

CAST(string_expression AS type FORMAT format_string_expression)

Converte um minuto formatado em string em um tipo de dados que contém a parte do minuto. Inclui elementos de formato, que fornecem instruções sobre como conduzir a transmissão.

  • string_expression: essa expressão contém a string com o minuto que você precisa formatar.
  • type: o tipo de dados para o qual você está transmitindo. Precisa incluir a parte do minuto.
  • format_string_expression: uma string que contém elementos de formato, incluindo o elemento de formato de minuto. Os elementos de formato nessa string são definidos coletivamente como o modelo de formato, que precisa seguir estas regras.

Esses tipos de dados incluem uma parte de um minuto:

  • TIME
  • DATETIME
  • TIMESTAMP

Se a parte MINUTE estiver ausente de string_expression e o tipo de retorno incluir essa parte, MINUTE será definido como 0.

Um erro será gerado se um valor que não for um elemento de formato compatível aparecer em format_string_expression ou string_expression não contiver um valor especificado por um elemento de formato.

Elemento de formato Retorna Exemplo
MI Corresponde a 2 dígitos. Define a parte do minuto para o número correspondente. Entrada para HH:MI PM: '03:30 P.M.'
Saída como TIME: 15:30:00

Tipo de retorno

O tipo de dados em que a string foi transmitida. Pode ser o seguinte:

  • TIME
  • DATETIME
  • TIMESTAMP

Exemplos

SELECT CAST('03:30 P.M.' AS TIME FORMAT 'HH:MI P.M.') AS string_to_date_time

+---------------------+
| string_to_date_time |
+---------------------+
| 15:30:00            |
+---------------------+

Formatar string como parte do segundo

CAST(string_expression AS type FORMAT format_string_expression)

Converte um segundo formatado em string para um tipo de dados que contém a segunda parte. Inclui elementos de formato, que fornecem instruções sobre como conduzir a transmissão.

  • string_expression: essa expressão contém a string com a segunda que você precisa formatar.
  • type: o tipo de dados para o qual você está transmitindo. É necessário incluir a segunda parte.
  • format_string_expression: uma string que contém elementos de formato, incluindo o segundo elemento de formato. Os elementos de formato nessa string são definidos coletivamente como o modelo de formato, que precisa seguir estas regras.

Esses tipos de dados incluem uma segunda parte:

  • TIME
  • DATETIME
  • TIMESTAMP

Se a parte SECOND estiver ausente em string_expression e o tipo de retorno incluir essa parte, SECOND será definido como 0.

Um erro será gerado se um valor que não for um elemento de formato compatível aparecer em format_string_expression ou string_expression não contiver um valor especificado por um elemento de formato.

Elemento de formato Retorna Exemplo
SS Corresponde a 2 dígitos. Define a segunda parte para o número correspondente. Entrada para HH:MI:SS PM: '03:30:02 P.M.'
Saída como TIME: 15:30:02
SSSSS Corresponde a 5 dígitos. Define a parte da hora, do minuto e do segundo, interpretando o número correspondente como o número de segundos após a meia-noite. Entrada para SSSSS: '03723'
Saída como TIME: 01:02:03
FFn Corresponde a n dígitos, em que n é o número após FF no elemento de formato. Define a parte fracionária da segunda parte para o número correspondente. Entrada para HH24:MI:SS.FF1: '01:05:07.16'
Saída como TIME: 01:05:07.2
Entrada para HH24:MI:SS.FF2: '01:05:07.16'
Saída como: 01:05:07.16
Entrada para HH24:MI:SS.FF3: 'FF3: 01:05:07.16'
Output as TIME: 01:05:07.160

Tipo de retorno

O tipo de dados em que a string foi transmitida. Pode ser o seguinte:

  • TIME
  • DATETIME
  • TIMESTAMP

Exemplos

SELECT CAST('01:05:07.16' AS TIME FORMAT 'HH24:MI:SS.FF1') AS string_to_date_time

+---------------------+
| string_to_date_time |
+---------------------+
| 01:05:07.2          |
+---------------------+

Formatar a string como parte do indicador meridiano

CAST(string_expression AS type FORMAT format_string_expression)

Converte um indicador meridiano formatado em string para um tipo de dados que contém a parte do indicador meridiano. Inclui elementos de formato, que fornecem instruções sobre como conduzir a transmissão.

  • string_expression: essa expressão contém a string com o indicador meridiano que você precisa formatar.
  • type: o tipo de dados para o qual você está transmitindo. Precisa incluir a parte do indicador meridiano.
  • format_string_expression: uma string que contém elementos de formato, incluindo o elemento indicador de meridiano. Os elementos de formato nessa string são definidos coletivamente como o modelo de formato, que precisa seguir estas regras.

Esses tipos de dados incluem uma parte do indicador de meridiano:

  • TIME
  • DATETIME
  • TIMESTAMP

Um erro será gerado se um valor que não for um elemento de formato compatível aparecer em format_string_expression ou string_expression não contiver um valor especificado por um elemento de formato.

Elemento de formato Retorna Exemplo
AM ou PM Corresponde usando a expressão regular '(A|P)\.M\.'. Entrada para HH:MI AM: '03:30 A.M.'
Saída como TIME: 03:30:00
Entrada para HH:MI PM: '03:30 P.M.'
Saída como TIME: 15:30:00
Entrada para HH:MI P.M.: '03:30 A.M.'
Saída como TIME: 03:30:00
Entrada para HH:MI A.M.: '03:30 P.M.'
Saída como TIME: 15:30:00
Entrada para HH:MI a.m.: '03:30 a.m.'
Saída como TIME: 03:30:00

Tipo de retorno

O tipo de dados em que a string foi transmitida. Pode ser o seguinte:

  • TIME
  • DATETIME
  • TIMESTAMP

Exemplos

SELECT CAST('03:30 P.M.' AS TIME FORMAT 'HH:MI A.M.') AS string_to_date_time

+---------------------+
| string_to_date_time |
+---------------------+
| 15:30:00            |
+---------------------+

Formatar string como parte do fuso horário

CAST(string_expression AS type FORMAT format_string_expression)

Converte um fuso horário formatado em string para um tipo de dados que contém a parte do fuso horário. Inclui elementos de formato, que fornecem instruções sobre como conduzir a transmissão.

  • string_expression: essa expressão contém a string com o fuso horário que você precisa formatar.
  • type: o tipo de dados para o qual você está transmitindo. Precisa incluir a parte do fuso horário.
  • format_string_expression: uma string que contém elementos de formato, incluindo o elemento de formato de fuso horário. Os elementos de formato nessa string são definidos coletivamente como o modelo de formato, que precisa seguir estas regras.

Esses tipos de dados incluem uma parte do fuso horário:

  • DATE
  • TIME
  • DATETIME
  • TIMESTAMP

Um erro será gerado se um valor que não for um elemento de formato compatível aparecer em format_string_expression ou string_expression não contiver um valor especificado por um elemento de formato.

Elemento de formato Retorna Exemplo
TZH Corresponde usando a expressão regular '(\+|\-| )[0-9]{2}'. Define o fuso horário e as partes da hora como o sinal e o número correspondentes. Define o sinal de fuso horário como a primeira letra da string correspondente. O número 2 significa corresponder até 2 dígitos para correspondência não exata e exatamente 2 dígitos para correspondência exata. Entrada para AAAA-MM-DD HH:MI:SSTZH: '2008-12-25 05:30:00-08'
Saída como TIMESTAMP: 2008-12-25 05:30:00-08
TZM Corresponde a 2 dígitos. Deixe n ser o número correspondente. Se o sinal de fuso horário for o sinal de menos, define a parte do minuto do fuso horário como -n. Caso contrário, define a parte do minuto do fuso horário como n. Entrada para AAAA-MM-DD HH:MI:SSTZH: '2008-12-25 05:30:00+05.30'
Saída como TIMESTAMP: 2008-12-25 05:30:00+05.30

Tipo de retorno

O tipo de dados em que a string foi transmitida. Pode ser o seguinte:

  • DATE
  • TIME
  • DATETIME
  • TIMESTAMP

Exemplos

SELECT CAST('2020.06.03 00:00:53+00' AS TIMESTAMP FORMAT 'YYYY.MM.DD HH:MI:SSTZH') AS string_to_date_time

+-----------------------------+
| as_timestamp                |
+-----------------------------+
| 2020-06-03 00:00:53.110 UTC |
+-----------------------------+

Formatar string como literal

CAST(string_expression AS data_type FORMAT format_string_expression)
Elemento de formato Retorna Exemplo
- A saída é igual à entrada.
. A saída é igual à entrada. .
/ A saída é igual à entrada. /
, A saída é igual à entrada. ,
' A saída é igual à entrada. '
; A saída é igual à entrada. ;
: A saída é igual à entrada. :
Espaço em branco Uma sequência consecutiva de um ou mais espaços no modelo de formato é correspondida com um ou mais caracteres de espaço em branco Unicode consecutivos na entrada. Espaço significa o caractere de espaço ASCII 32. Ele não significa um espaço em branco geral, como tabulação ou nova linha. Qualquer caractere de espaço em branco que não seja ASCII 32 no modelo de formato gera um erro.
"text" Saída gerada pelo elemento de formatação na formatação, usando esta expressão regular, com s representando a entrada de string: regex.escape(s). Entrada: "abc"
Saída: abc
Entrada: "a\"b\\c"
Saída: a"b\c

Formatar tipo numérico como string

CAST(numeric_expression AS STRING FORMAT format_string_expression)

Você pode transmitir um tipo numérico para uma string combinando os seguintes elementos de formato:

Exceto pelo elemento de form