Expresiones, funciones y operadores

En esta página, se explican las expresiones de SQL de Cloud Spanner, incluidas las funciones y los operadores.

Reglas de llamada a funciones

Las siguientes reglas se aplican a todas las funciones, a menos que se indique lo contrario de forma explícita en la descripción de la función:

  • Para las funciones que aceptan tipos numéricos, si un operando es de punto flotante y el otro es otro tipo numérico, ambos operandos se convierten en FLOAT64 antes de evaluar la función.
  • Si un operando es NULL, el resultado es NULL, a excepción del operador IS.
  • Para las funciones que son sensibles a la zona horaria (como se indica en la descripción de la función), se utiliza la zona horaria predeterminada (America/Los_Angeles) si no se especifica una.

Prefijo SAFE.

Sintaxis:

SAFE.function_name()

Descripción

Si comienzas una función escalar con el prefijo SAFE., se mostrará NULL en lugar de un error. El prefijo SAFE. solo previene errores de la función con el prefijo en sí: no evita los errores que se producen cuando se evalúan las expresiones de argumento. El prefijo SAFE. solo previene errores que se producen debido al valor de las entradas de función, como los errores “valor fuera de rango”. Aún pueden ocurrir otros errores, como errores internos o del sistema. Si la función no muestra un error, SAFE. no tiene efecto en el resultado. Si la función nunca muestra un error, como RAND, entonces SAFE. no tiene ningún efecto.

Los operadores, como + y =, no admiten el prefijo SAFE.. Para evitar errores de una operación de división, usa SAFE_DIVIDE. Algunos operadores, como IN, ARRAY y UNNEST, se parecen a funciones, pero no admiten el prefijo SAFE.. Las funciones CAST y EXTRACT tampoco admiten el prefijo SAFE.. Para evitar la transmisión de errores, usa SAFE_CAST.

Ejemplo

En el siguiente ejemplo, el primer uso de la función SUBSTR normalmente mostraría un error, porque la función no admite argumentos de longitud con valores negativos. Sin embargo, el prefijo SAFE. hace que la función muestre NULL en su lugar. El segundo uso de la función SUBSTR proporciona el resultado esperado: el prefijo SAFE. no tiene ningún efecto.

SELECT SAFE.SUBSTR('foo', 0, -2) AS safe_output UNION ALL
SELECT SAFE.SUBSTR('bar', 0, 2) AS safe_output;

+-------------+
| safe_output |
+-------------+
| NULL        |
| ba          |
+-------------+

Reglas de conversión

“Conversión” incluye, entre otros, conversión de tipos y coerción.

  • La conversión de tipos es una conversión explícita y usa la función CAST().
  • La coerción es una conversión implícita que SQL de Cloud Spanner realiza automáticamente en las condiciones que se describen a continuación.
  • Existe un tercer grupo de funciones de conversión con sus propios nombres de funciones, como UNIX_DATE().

La siguiente tabla resume todas las posibilidades de coerción y CAST para los tipos de datos de SQL de Cloud Spanner. “Coerción para” se aplica a todas las expresiones de un tipo de datos determinado (p. ej., una columna).

De tipo CONVERSIÓN a Coerción a
INT64 BOOL
INT64
FLOAT64
STRING
FLOAT64
FLOAT64 INT64
FLOAT64
STRING
 
BOOL BOOL
INT64
STRING
 
STRING BOOL
INT64
FLOAT64
STRING
BYTES
DATE
TIMESTAMP
 
BYTES BYTES
STRING
 
DATE DATE
STRING
TIMESTAMP
 
TIMESTAMP DATE
STRING
TIMESTAMP
 
ARRAY ARRAY  
STRUCT STRUCT  

Conversión de tipos

Sintaxis:

CAST(expr AS typename)

La sintaxis de conversión de tipos se usa en una consulta para indicar que el tipo de resultado de una expresión debe convertirse en algún otro tipo.

Ejemplo:

CAST(x=1 AS STRING)

Esto da como resultado "true" si x es 1, "false" para cualquier otro valor que no sea NULL, y NULL si x es NULL.

Las conversiones entre tipos admitidos que no se mapean con éxito del valor original al dominio de destino producen errores en el entorno de ejecución. Por ejemplo, la conversión de BYTES a STRING cuando la secuencia de bytes no es un resultado UTF-8 válido devuelve un error en el entorno de ejecución.

Cuando se convierte el tipo de una expresión x de los siguientes tipos, se aplican estas reglas:

De A Reglas para la conversión de tipos de x
INT64 FLOAT64 Muestra un valor FLOAT64 cercano, pero potencialmente no exacto.
INT64 BOOL Muestra FALSE si x es 0, y TRUE en caso contrario.
FLOAT64 INT64 Muestra el valor INT64 más cercano.
Los casos de punto medio, como 1.5 o -0.5, se redondean en dirección opuesta al cero.
FLOAT64 STRING Muestra una representación aproximada de la string.
BOOL INT64 Muestra 1 si x es TRUE, y 0 en caso contrario.
BOOL STRING Muestra "true" si x es TRUE, y "false" en caso contrario.
STRING FLOAT64 Muestra x como un valor FLOAT64 y lo interpreta como si tuviera el mismo formato que un literal FLOAT64 válido.
También admite conversiones de tipo desde "inf", "+inf", "-inf" y "nan".
Las conversiones no distinguen entre mayúsculas y minúsculas.
STRING BOOL Muestra TRUE si x es "true" y FALSE si x es "false"
Todos los demás valores de x no son válidos y muestran un error en lugar de convertir el tipo en BOOL.
Las STRING no distinguen entre mayúsculas y minúsculas cuando se convierten en BOOL.
STRING BYTES Las STRING se convierten en BYTES mediante la codificación UTF-8. Por ejemplo, la STRING "©", cuando se la transforma en BYTES, se convierte en una secuencia de 2 bytes con valores hexadecimales C2 y A9.
BYTES STRING Muestra x interpretado como una STRING UTF-8.
Por ejemplo, el literal de BYTES b'\xc2\xa9', cuando se convierte su tipo en STRING, se interpreta como UTF-8 y se convierte en el carácter Unicode “©”.
Se produce un error si x no es un valor de UTF-8 válido.
ARRAY ARRAY Debe ser exactamente el mismo tipo de ARRAY.
STRUCT STRUCT Se permite si se cumplen las siguientes condiciones:
  1. Los dos STRUCT tienen la misma cantidad de campos.
  2. Los tipos de campo STRUCT original se pueden transmitir a los tipos de campos STRUCT de destino correspondientes (según se define por orden y no nombre de campo).

Conversión segura

Cuando usas CAST, es posible que la consulta falle si SQL de Cloud Spanner no puede realizar la conversión. Por ejemplo, la siguiente consulta genera un error:

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

Si deseas proteger tus consultas de estos tipos de errores, puedes usar SAFE_CAST. SAFE_CAST es idéntico a CAST, pero muestra NULL en vez de generar un error.

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

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

Si conviertes el tipo de bytes en strings, también puedes usar la función SAFE_CONVERT_BYTES_TO_STRING. Los caracteres UTF-8 no válidos se reemplazan por el carácter de reemplazo de Unicode, U+FFFD. Consulta SAFE_CONVERT_BYTES_TO_STRING para obtener más información.

Cómo convertir strings hexadecimales en números enteros

Si trabajas con strings hexadecimales (0x123), puedes convertir sus tipos en números enteros:

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

Cómo convertir tipos de fechas

SQL de Cloud Spanner admite los tipos de fecha de conversión a/de strings de la siguiente manera:

CAST(date_expression AS STRING)
CAST(string_expression AS DATE)

La conversión de un tipo de fecha a una string es independiente de la zona horaria y tiene la forma YYYY-MM-DD. Cuando se realiza una conversión de string a fecha, la string debe cumplir con el formato de fecha admitido y es independiente de la zona horaria. Se producirá un error si la expresión de la string no es válida o representa una fecha fuera del rango mínimo o máximo aceptado.

Cómo convertir tipos de marcas de tiempo

SQL de Cloud Spanner admite los tipos de marca de tiempo de conversión a/de strings de la siguiente manera:

CAST(timestamp_expression AS STRING)
CAST(string_expression AS TIMESTAMP)

Cuando se realiza una conversión de tipos de marcas de tiempo a strings, la marca de tiempo se interpreta con la zona horaria predeterminada, America/Los_Angeles. La cantidad de dígitos de subsegundos producidos depende de la cantidad de ceros finales en la parte de subsegundos: la función CAST truncará cero, tres o seis dígitos.

Cuando se realiza una conversión de tipo de string a marca de tiempo, string_expression debe cumplir con los formatos de literal de marca de tiempo aceptados o, de lo contrario, ocurrirá un error en el entorno de ejecución. string_expression puede contener un time_zone; consulta la sección sobre zonas horarias. Si hay una zona horaria en string_expression, esa zona horaria se utiliza para la conversión; de lo contrario, se utiliza la zona horaria predeterminada, America/Los_Angeles. Si la string tiene menos de seis dígitos, entonces se ensancha implícitamente.

Se produce un error si string_expression no es válida, tiene más de seis dígitos de subsegundos (es decir, la precisión es superior a microsegundos) o representa un tiempo externo al rango de marca de tiempo aceptado.

Conversión entre tipos de fecha y marca de tiempo

SQL de Cloud Spanner admite la conversión entre tipos de fecha y de marca de tiempo de la siguiente manera:

CAST(date_expression AS TIMESTAMP)
CAST(timestamp_expression AS DATE)

La conversión de una fecha a una marca de tiempo interpreta date_expression a partir de la medianoche (inicio del día) en la zona horaria predeterminada, America/Los_Angeles. La conversión de una marca de tiempo a una fecha trunca de forma efectiva la marca de tiempo desde la zona horaria predeterminada.

Coerción

SQL de Cloud Spanner fuerza el tipo de resultado de una expresión a otro si es necesario para hacer coincidir las firmas de función. Por ejemplo, si la función func() se define para que tenga un solo argumento del tipo INT64 y se usa una expresión como argumento que tiene un tipo de resultado de FLOAT64, entonces el resultado de la expresión se coercionará al tipo INT64 antes de que se calcule la función func().

Funciones de conversión adicionales

SQL de Cloud Spanner proporciona las siguientes funciones de conversión adicionales:

Funciones de agregación

Una función agregada es una función que resume las filas de un grupo en un solo valor. COUNT, MIN y MAX son ejemplos de funciones agregadas.

SELECT COUNT(*) as total_count, COUNT(fruit) as non_null_count,
       MIN(fruit) as min, MAX(fruit) as max
FROM (SELECT NULL as fruit UNION ALL
      SELECT "apple" as fruit UNION ALL
      SELECT "pear" as fruit UNION ALL
      SELECT "orange" as fruit)

+-------------+----------------+-------+------+
| total_count | non_null_count | min   | max  |
+-------------+----------------+-------+------+
| 4           | 3              | apple | pear |
+-------------+----------------+-------+------+

Cuando se usan junto con una cláusula GROUP BY, los grupos resumidos suelen tener al menos una fila. Cuando el SELECT asociado no tiene una cláusula GROUP BY o cuando ciertos modificadores de funciones agregadas filtran filas del grupo para resumirlas, es posible que la función agregada necesite resumir un grupo vacío. En este caso, las funciones COUNT y COUNTIF muestran 0, mientras que todas las demás funciones agregadas muestran NULL.

En las siguientes secciones, se describen las funciones agregadas que admite SQL de Cloud Spanner.

ANY_VALUE

ANY_VALUE(expression [HAVING {MAX | MIN} expression2])

Descripción

Muestra expression para algunas filas que se eligieron del grupo. La elección de la fila no es determinista ni aleatoria. Muestra NULL cuando la entrada no produce filas. Muestra NULL cuando expression es NULL para todas las filas del grupo.

ANY_VALUE se comporta como si se especificara IGNORE NULLS; las filas para las que expression es NULL no se tienen en cuenta y no se seleccionarán.

Tipos de argumentos admitidos

Cualquiera

Cláusula opcional

HAVING MAX o HAVING MIN: restringe el conjunto de filas que la función agrega según un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más información.

Tipos de datos mostrados

Coincide con el tipo de datos de entrada.

Ejemplos

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

+-----------+
| any_value |
+-----------+
| apple     |
+-----------+

ARRAY_AGG

ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS] [HAVING {MAX | MIN} expression2])

Descripción

Muestra un ARRAY de valores expression.

Tipos de argumentos admitidos

Todos los tipos de datos excepto ARRAY.

Cláusulas opcionales

Las cláusulas se aplican en el siguiente orden:

  1. DISTINCT: cada valor distinto de expression se agrega solo una vez en el resultado.
  2. IGNORE NULLS o RESPECT NULLS: si se especifica IGNORE NULLS, los valores NULL se excluyen del resultado. Si se especifica RESPECT NULLS o si no se especifica ninguno, los valores NULL se incluyen en el resultado.
  3. HAVING MAX o HAVING MIN: restringe el conjunto de filas que la función agrega por un valor máximo o mínimo. Para obtener más información, consulta Cláusulas HAVING MAX y HAVING MIN.

Orden del elemento de salida

El orden de los elementos en la salida no es determinista, lo que significa que podrías recibir un resultado diferente cada vez que uses esta función.

Tipos de datos mostrados

ARRAY

Si hay cero filas de entrada, esta función muestra NULL.

Ejemplos

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

ARRAY_CONCAT_AGG

ARRAY_CONCAT_AGG(expression [HAVING {MAX | MIN} expression2])

Descripción

Concatena elementos de expression de tipo ARRAY y muestra un solo ARRAY como resultado. Esta función ignora los arreglos de entrada NULL, pero respeta los elementos NULL en arreglos de entrada que no son NULL.

Tipos de argumentos admitidos

ARRAY

Cláusula opcional

HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega en un valor máximo o mínimo. Consulta la sección Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

Orden del elemento de salida

El orden de los elementos en la salida no es determinista, lo que significa que podrías recibir un resultado diferente cada vez que uses esta función.

Tipos de datos mostrados

ARRAY

Muestra NULL si no hay filas de entrada o si expression se evalúa como NULL en todas las filas.

Ejemplos

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

AVG

AVG([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descripción

Muestra el promedio de valores de entrada que no son NULL, o muestra NaN si la entrada contiene un NaN.

Tipos de argumentos admitidos

Cualquier tipo de entrada numérico, como INT64. Ten en cuenta que para los tipos de entrada de punto flotante, el resultado mostrado es no determinista, lo que significa que podrías recibir un resultado diferente cada vez que uses esta función.

Cláusulas opcionales

Las cláusulas se aplican en el siguiente orden:

  1. DISTINCT: Cada valor distinto de expression se agrega solo una vez en el resultado.
  2. HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega en un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

Tipos de datos mostrados

  • FLOAT64

Ejemplos

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

BIT_AND

BIT_AND([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descripción

Realiza una operación AND a nivel de bits en expression y muestra el resultado.

Tipos de argumentos admitidos

  • INT64

Cláusulas opcionales

Las cláusulas se aplican en el siguiente orden:

  1. DISTINCT: Cada valor distinto de expression se agrega solo una vez en el resultado.
  2. HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega en un valor máximo o mínimo. Para obtener más información, consulta Cláusulas HAVING MAX y HAVING MIN.

Tipos de datos mostrados

INT64

Ejemplos

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

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

BIT_OR

BIT_OR([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descripción

Realiza una operación OR a nivel de bits en expression y muestra el resultado.

Tipos de argumentos admitidos

  • INT64

Cláusulas opcionales

Las cláusulas se aplican en el siguiente orden:

  1. DISTINCT: Cada valor distinto de expression se agrega solo una vez en el resultado.
  2. HAVING MAX o HAVING MIN: restringe el conjunto de filas que la función agrega según un valor máximo o mínimo. Para obtener más información, consulta Cláusulas HAVING MAX y HAVING MIN.

Tipos de datos mostrados

INT64

Ejemplos

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

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

BIT_XOR

BIT_XOR([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descripción

Realiza una operación XOR a nivel de bits en expression y muestra el resultado.

Tipos de argumentos admitidos

  • INT64

Cláusulas opcionales

Las cláusulas se aplican en el siguiente orden:

  1. DISTINCT: Cada valor distinto de expression se agrega solo una vez en el resultado.
  2. HAVING MAX o HAVING MIN: restringe el conjunto de filas que la función agrega según un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

Tipos de datos mostrados

INT64

Ejemplos

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(*)

2. COUNT([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descripción

  1. Muestra la cantidad de filas en la entrada.
  2. Muestra la cantidad de filas con expression evaluado como cualquier valor distinto de NULL.

Tipos de argumentos admitidos

expression puede ser cualquier tipo de datos.

Cláusulas opcionales

Las cláusulas se aplican en el siguiente orden:

  1. DISTINCT: Cada valor distinto de expression se agrega solo una vez en el resultado.
  2. HAVING MAX o HAVING MIN: restringe el conjunto de filas que la función agrega por un valor máximo o mínimo. Para obtener más información, consulta Cláusula HAVING MAX y HAVING MIN.

Tipos de datos mostrados

INT64

Ejemplos

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 COUNT(*) AS count_star, COUNT(x) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;

+------------+---------+
| count_star | count_x |
+------------+---------+
| 5          | 4       |
+------------+---------+

COUNTIF

COUNTIF([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descripción

Muestra el recuento de valores TRUE para expression. Muestra 0 si hay cero filas de entrada o si la expression se evalúa como FALSE o NULL para todas las filas.

Tipos de argumentos admitidos

BOOL

Cláusulas opcionales

Las cláusulas se aplican en el siguiente orden:

  1. DISTINCT: Cada valor distinto de expression se agrega solo una vez en el resultado.
  2. HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega según un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

Tipos de datos mostrados

INT64

Ejemplos

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

LOGICAL_AND

LOGICAL_AND(expression [HAVING {MAX | MIN} expression2])

Descripción

Muestra el AND lógico para todas las expresiones que no son NULL. Muestra NULL si hay cero filas de entrada o si expression se evalúa como NULL para todas las filas.

Tipos de argumentos admitidos

BOOL

Cláusula opcional

HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega por un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más información.

Tipos de datos mostrados

BOOL

Ejemplos

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

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

LOGICAL_OR

LOGICAL_OR(expression [HAVING {MAX | MIN} expression2])

Descripción

Muestra el OR lógico para todas las expresiones que no son NULL. Muestra NULL si hay cero filas de entrada o si expression se evalúa como NULL para todas las filas.

Tipos de argumentos admitidos

BOOL

Cláusula opcional

HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega por un valor máximo o mínimo. Consulta la sección Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

Tipos de datos mostrados

BOOL

Ejemplos

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

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

MAX

MAX(expression [HAVING {MAX | MIN} expression2])

Descripción

Muestra el valor máximo de las expresiones que no son NULL. Muestra NULL si hay cero filas de entrada o si expression se evalúa como NULL para todas las filas. Muestra NaN si la entrada contiene un NaN.

Tipos de argumentos admitidos

Cualquier tipo de datos excepto: ARRAY STRUCT

Cláusula opcional

HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega por un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

Tipos de datos mostrados

Igual al tipo de datos usado como valores de entrada.

Ejemplos

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

+-----+
| max |
+-----+
| 55  |
+-----+

MIN

MIN(expression [HAVING {MAX | MIN} expression2])

Descripción

Muestra el valor mínimo de las expresiones que no son NULL. Muestra NULL si hay cero filas de entrada o si expression se evalúa como NULL para todas las filas. Muestra NaN si la entrada contiene un NaN.

Tipos de argumentos admitidos

Cualquier tipo de datos excepto: ARRAY STRUCT

Cláusula opcional

HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega por un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

Tipos de datos mostrados

Igual al tipo de datos usado como valores de entrada.

Ejemplos

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

+-----+
| min |
+-----+
| 4   |
+-----+

STRING_AGG

STRING_AGG([DISTINCT] expression [, delimiter] [HAVING {MAX | MIN} expression2])

Descripción

Muestra un valor (ya sea STRING o BYTES) obtenido mediante la concatenación de valores no nulos.

Si especificas un delimiter, los valores concatenados se separan con ese delimitador; de lo contrario, se usa una coma.

Tipos de argumentos admitidos

STRING BYTES

Cláusulas opcionales

Las cláusulas se aplican en el siguiente orden:

  1. DISTINCT: Cada valor distinto de expression se agrega solo una vez en el resultado.
  2. HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega según un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

Orden del elemento de salida

El orden de los elementos en la salida no es determinista, lo que significa que podrías recibir un resultado diferente cada vez que uses esta función.

Tipos de datos mostrados

STRING BYTES

Ejemplos

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

SUM

SUM([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descripción

Muestra la suma de valores no nulos.

Si la expresión es un valor de punto flotante, la suma es no determinista, lo que significa que podrías recibir un resultado diferente cada vez que uses esta función.

Tipos de argumentos admitidos

Cualquier tipo de datos numéricos admitido.

Cláusulas opcionales

Las cláusulas se aplican en el siguiente orden:

  1. DISTINCT: Cada valor distinto de expression se agrega solo una vez en el resultado.
  2. HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega según un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

Tipos de datos mostrados

  • Muestra INT64 si la entrada es un número entero.
  • Muestra FLOAT64 si la entrada es un valor de punto flotante.

Muestra NULL si la entrada solo contiene NULL.

Muestra NULL si la entrada no contiene filas.

Muestra Inf si la entrada contiene Inf.

Muestra -Inf si la entrada contiene -Inf.

Muestra NaN si la entrada contiene un NaN.

Muestra NaN si la entrada contiene una combinación de Inf y -Inf.

Ejemplos

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 SUM(x) AS sum
FROM UNNEST([]) AS x;

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

Cláusulas comunes

Cláusulas HAVING MAX y HAVING MIN

La mayoría de las funciones agregadas admiten dos cláusulas opcionales llamadas HAVING MAX y HAVING MIN, que restringen el conjunto de filas que una función agrega a las filas que tienen un valor máximo o mínimo en una columna en particular. Por lo general, la sintaxis se ve de la siguiente manera:

aggregate_function(expression1 [HAVING {MAX | MIN} expression2])
  • HAVING MAX: Restringe el conjunto de filas que la función agrega a las que tienen un valor para expression2 igual al valor máximo para expression2 dentro del grupo. El valor máximo es igual al resultado de MAX(expression2).
  • HAVING MIN: Restringe el conjunto de filas que la función agrega a las que tienen un valor de expression2 igual al valor mínimo de expression2 dentro del grupo. El valor mínimo es igual al resultado de MIN(expression2).

Estas cláusulas ignoran los valores NULL cuando se calcula el valor máximo o mínimo, a menos que expression2 se evalúe como NULL para todas las filas.

Estas cláusulas no admiten los siguientes tipos de datos: ARRAY STRUCT

Ejemplo

En este ejemplo, el promedio de precipitaciones se muestra para el año más reciente, 2001.

WITH Precipitation AS
 (SELECT 2001 as year, 'spring' as season, 9 as inches UNION ALL
  SELECT 2001, 'winter', 1 UNION ALL
  SELECT 2000, 'fall', 3 UNION ALL
  SELECT 2000, 'summer', 5 UNION ALL
  SELECT 2000, 'spring', 7 UNION ALL
  SELECT 2000, 'winter', 2)
SELECT AVG(inches HAVING MAX year) as average FROM Precipitation

+---------+
| average |
+---------+
| 5       |
+---------+

Primero, la consulta obtiene las filas con el valor máximo en la columna year. Hay dos:

+------+--------+--------+
| year | season | inches |
+------+--------+--------+
| 2001 | spring | 9      |
| 2001 | winter | 1      |
+------+--------+--------+

Por último, la consulta promedia los valores en la columna inches (9 y 1) con este resultado:

+---------+
| average |
+---------+
| 5       |
+---------+

Funciones de agregación estadística

SQL de Cloud Spanner admite las siguientes funciones de agregación estadística.

STDDEV_SAMP

STDDEV_SAMP([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descripción

Muestra la desviación estándar muestral (imparcial) de los valores. El resultado que se muestra está entre 0 y +Inf.

Esta función ignora cualquier entrada NULL. Si hay menos de dos entradas que no sean NULL, esta función muestra NULL.

Tipos de entrada admitidos

FLOAT64

Cláusulas opcionales

Las cláusulas se aplican en el siguiente orden:

  1. DISTINCT: Cada valor distinto de expression se agrega solo una vez en el resultado.
  2. HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega según un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

Tipo de datos mostrados

FLOAT64

STDDEV

STDDEV([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descripción

Un alias de STDDEV_SAMP.

VAR_SAMP

VAR_SAMP([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descripción

Muestra la varianza muestral (imparcial) de los valores. El resultado que se muestra está entre 0 y +Inf.

Esta función ignora cualquier entrada NULL. Si hay menos de dos entradas que no sean NULL, esta función muestra NULL.

Tipos de entrada admitidos

FLOAT64

Cláusulas opcionales

Las cláusulas se aplican en el siguiente orden:

  1. DISTINCT: Cada valor distinto de expression se agrega solo una vez en el resultado.
  2. HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega según un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

Tipo de datos mostrados

FLOAT64

VARIANCE

VARIANCE([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descripción

Un alias de VAR_SAMP.

Funciones matemáticas

Todas las funciones matemáticas tienen los siguientes comportamientos:

  • Muestran NULL si alguno de los parámetros de entrada es NULL.
  • Muestran NaN si alguno de los argumentos es NaN.

ABS

ABS(X)

Descripción

Calcula el valor absoluto. Muestra un error si el argumento es un número entero y el valor de resultado no se puede representar como el mismo tipo; esto sucede solo para el valor de entrada negativo más grande, que no tiene una representación positiva. Muestra +inf para un argumento +/-inf.

SIGN

SIGN(X)

Descripción

Muestra -1, 0 o +1 para los argumentos negativos, de cero y positivos, respectivamente. Para argumentos de punto flotante, esta función no distingue entre cero positivo y negativo. Muestra NaN para un argumento NaN.

IS_INF

IS_INF(X)

Descripción

Muestra TRUE si el valor es infinito positivo o negativo. Muestra NULL para las entradas NULL.

IS_NAN

IS_NAN(X)

Descripción

Muestra TRUE si el valor es NaN. Muestra NULL para las entradas NULL.

IEEE_DIVIDE

IEEE_DIVIDE(X, Y)

Descripción

Divide X por Y; esta función nunca presenta errores. Muestra FLOAT64. A diferencia del operador de división (/), esta función no genera errores de divisiones por cero ni se desborda.

Casos especiales:

  • Si el resultado se desborda, muestra +/-inf.
  • Si Y=0 y X=0, muestra NaN.
  • Si Y=0 y X!=0, muestra +/-inf.
  • Si X = +/-inf y Y = +/-inf, muestra NaN.

El comportamiento de IEEE_DIVIDE se muestra con más detalle en la tabla a continuación.

Casos especiales de IEEE_DIVIDE

En la siguiente tabla, se enumeran los casos especiales de IEEE_DIVIDE.

Tipo de datos del numerador (X) Tipo de datos del denominador (Y) Valor del resultado
Todo excepto 0 0 +/-inf
0 0 NaN
0 NaN NaN
NaN 0 NaN
+/-inf +/-inf NaN

SQRT

SQRT(X)

Descripción

Calcula la raíz cuadrada de X. Genera un error si X es menor que 0. Muestra +inf si X es +inf.

POW

POW(X, Y)

Descripción

Muestra el valor de X elevado a la potencia de Y. Si el resultado se subdesborda y no es representable, entonces la función muestra un valor de cero. Muestra un error si uno de los siguientes es verdadero:

  • X es un valor finito menor que 0 y, además, Y es un número no entero.
  • X es 0 y, también, Y es un valor finito menor que 0.

El comportamiento de POW() se muestra con más detalle en la tabla a continuación.

POWER

POWER(X, Y)

Descripción

Sinónimo de POW().

Casos especiales de POW(X, Y) y POWER(X, Y)

Los siguientes son casos especiales de POW(X, Y) y POWER(X, Y).

X Y POW(X, Y) o POWER(X, Y)
1.0 Cualquier valor, incluido NaN 1.0
Cualquiera, incluido NaN 0 1.0
-1.0 +/-inf 1.0
ABS(X) < 1 -inf +inf
ABS(X) > 1 -inf 0
ABS(X) < 1 +inf 0
ABS(X) > 1 +inf +inf
-inf Y < 0 0
-inf Y > 0 -inf si Y es un número entero impar, +inf si es lo contrario
+inf Y < 0 0
+inf Y > 0 +inf

EXP

EXP(X)

Descripción

Calcula e elevado a X, también llamada función exponencial natural. Si el resultado se subdesborda, esta función muestra un cero. Genera un error si el resultado se desborda. Si X es +/-inf, entonces esta función muestra +inf o 0.

LN

LN(X)

Descripción

Calcula el logaritmo natural de X. Genera un error si X es menor o igual que cero. Si X es +inf, entonces esta función muestra +inf.

LOG

LOG(X [, Y])

Descripción

Si solo X está presente, LOG es un sinónimo de LN. Si Y también está presente, LOG calcula el logaritmo de X en base Y. Genera un error en los siguientes casos:

  • X es menor o igual que cero.
  • Y es 1.0.
  • Y es menor o igual que cero.

El comportamiento de LOG(X, Y) se muestra con más detalle en la tabla a continuación.

Casos especiales de LOG(X, Y)

X Y LOG(X, Y)
-inf Cualquier valor NaN
Cualquier valor +inf NaN
+inf 0.0 Y < 1.0 -inf
+inf Y > 1.0 +inf

LOG10

LOG10(X)

Descripción

Similar a LOG, pero calcula el logaritmo en base 10.

GREATEST

GREATEST(X1,...,XN)

Descripción

Muestra NULL si alguna de las entradas es NULL. De lo contrario, muestra NaN si alguna de las entradas es NaN. Si no, muestra el valor más grande entre X1,…,XN según la comparación <.

LEAST

LEAST(X1,...,XN)

Descripción

Muestra NULL si alguna de las entradas es NULL. Muestra NaN si alguna de las entradas es NaN. Si no, muestra el valor más pequeño entre X1,…,XN según la > comparación.

DIV

DIV(X, Y)

Descripción

Muestra el resultado de la división del número entero de X por Y. La división por cero muestra un error. La división por -1 se puede desbordar. Consulta la tabla a continuación para obtener tipos de resultados posibles.

SAFE_DIVIDE

SAFE_DIVIDE(X, Y)

Descripción

Equivale al operador de división (/), pero muestra NULL si se produce un error, como un error de división por cero.

SAFE_MULTIPLY

SAFE_MULTIPLY(X, Y)

Descripción

Equivalente al operador de multiplicación (*), pero muestra NULL si se produce un desbordamiento.

SAFE_NEGATE

SAFE_NEGATE(X)

Descripción

Equivalente al operador menos unario (-), pero muestra NULL si se produce un desbordamiento.

SAFE_ADD

SAFE_ADD(X, Y)

Descripción

Equivale al operador de suma (+), pero muestra NULL si se produce un desbordamiento.

SAFE_SUBTRACT

SAFE_SUBTRACT(X, Y)

Descripción

Equivalente al operador de resta (-), pero muestra NULL si se produce un desbordamiento.

MOD

MOD(X, Y)

Descripción

Función de módulo: muestra el resto de la división de X por Y. El valor que se muestra tiene el mismo signo que X. Se generará un error si Y es 0. Consulta la tabla a continuación para obtener tipos de resultados posibles.

ROUND

ROUND(X [, N])

Descripción

Si solo está presente X, ROUND redondea X al número entero más cercano. Si N está presente, ROUND redondea X a N decimales después del punto decimal. Si N es negativo, ROUND redondeará los dígitos a la izquierda del punto decimal. Se redondean casos de punto medio en dirección opuesta al cero. Genera un error si se produce un desbordamiento.

TRUNC

TRUNC(X [, N])

Descripción

Si solo está presente X, TRUNC redondea X al número entero más cercano cuyo valor absoluto no es mayor que el valor absoluto de X. Si N también está presente, TRUNC se comporta como ROUND(X, N), pero siempre se redondea hacia cero y nunca se desborda.

CEIL

CEIL(X)

Descripción

Muestra el valor integral más pequeño (con el tipo FLOAT64) que no es menor que X.

CEILING

CEILING(X)

Descripción

Sinónimo de CEIL(X)

FLOOR

FLOOR(X)

Descripción

Muestra el valor integral más grande (con el tipo FLOAT64) que no es mayor que X.

Ejemplo del comportamiento de la función de redondeo

Comportamiento de ejemplo de las funciones de redondeo de SQL de Cloud Spanner:

Entrada “X” ROUND(X) TRUNC(X) CEIL(X) FLOOR(X)
2.0 2.0 2.0 2.0 2.0
2.3 2.0 2.0 3.0 2.0
2.8 3.0 2.0 3.0 2.0
2.5 3.0 2.0 3.0 2.0
-2.3 -2.0 -2.0 -2.0 -3.0
-2.8 -3.0 -2.0 -2.0 -3.0
-2.5 -3.0 -2.0 -2.0 -3.0
0 0 0 0 0
+/-inf +/-inf +/-inf +/-inf +/-inf
NaN NaN NaN NaN NaN

COS

COS(X)

Descripción

Calcula el coseno de X, en el que X se especifica en radianes. Nunca falla.

COSH

COSH(X)

Descripción

Calcula el coseno hiperbólico de X, en el que X se especifica en radianes. Genera un error si se produce un desbordamiento.

ACOS

ACOS(X)

Descripción

Calcula el valor principal del coseno inverso de X. El valor de muestra está en el rango [0,π]. Genera un error si X es un valor fuera del rango [-1, 1].

ACOSH

ACOSH(X)

Descripción

Calcula el coseno hiperbólico inverso de X. Genera un error si X es un valor inferior a 1.

SIN

SIN(X)

Descripción

Calcula el seno de X, en el que X se especifica en radianes. Nunca falla.

SINH

SINH(X)

Descripción

Calcula el seno hiperbólico de X, en el que X se especifica en radianes. Genera un error si se produce un desbordamiento.

ASIN

ASIN(X)

Descripción

Calcula el valor principal del seno inverso de X. El valor que se muestra está en el rango [-π/2,π/2]. Genera un error si X está fuera del rango [-1, 1].

ASINH

ASINH(X)

Descripción

Calcula el seno hiperbólico inverso de X. No falla.

TAN

TAN(X)

Descripción

Calcula la tangente de X, en la que X se especifica en radianes. Genera un error si se produce un desbordamiento.

TANH

TANH(X)

Descripción

Calcula la tangente hiperbólica de X, en la que X se especifica en radianes. No falla.

ATAN

ATAN(X)

Descripción

Calcula el valor principal de la tangente inversa de X. El valor que se muestra está en el rango [-π/2,π/2]. No falla.

ATANH

ATANH(X)

Descripción

Calcula la tangente hiperbólica inversa de X. Genera un error si X está fuera del rango [-1, 1].

ATAN2

ATAN2(Y, X)

Descripción

Calcula el valor principal de la tangente inversa de Y/X con los signos de los dos argumentos para determinar el cuadrante. El valor que se muestra está en el rango [-π,π]. El comportamiento de esta función se ilustra con más detalle en la siguiente tabla.

Casos especiales de ATAN2()

Y X ATAN2(Y, X)
NaN Cualquier valor NaN
Cualquier valor NaN NaN
0 0 0, π o -π según el signo de X y de Y
Valor finito -inf π o -π según el signo de Y
Valor finito +inf 0
+/-inf Valor finito π/2 o π/2 según el signo de Y
+/-inf -inf ¾π o -¾π según el signo de Y
+/-inf +inf π/4 o -π/4 según el signo de Y

Casos especiales de funciones de redondeo hiperbólicas y trigonométricas

X COS(X) COSH(X) ACOS(X) ACOSH(X) SIN(X) SINH(X) ASIN(X) ASINH(X) TAN(X) TANH(X) ATAN(X) ATANH(X)
+/-inf NaN =+inf NaN =+inf NaN =+inf NaN =+inf NaN =+1.0 π/2 NaN
-inf NaN =+inf NaN NaN NaN -inf NaN -inf NaN -1.0 -π/2 NaN
NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN

Funciones de generación de hash

FARM_FINGERPRINT

FARM_FINGERPRINT(value)

Descripción

Calcula la huella digital de la entrada de STRING o BYTES con la función Fingerprint64 de la biblioteca de FarmHash de código abierto. El resultado de esta función para una entrada particular nunca cambiará.

Tipo de datos que se muestra

INT64

Ejemplos

WITH example AS (
  SELECT 1 AS x, "foo" AS y, true AS z UNION ALL
  SELECT 2 AS x, "apple" AS y, false AS z UNION ALL
  SELECT 3 AS x, "" AS y, true AS z
)
SELECT
  *,
  FARM_FINGERPRINT(CONCAT(CAST(x AS STRING), y, CAST(z AS STRING)))
    AS row_fingerprint
FROM example;
+---+-------+-------+----------------------+
| x | y     | z     | row_fingerprint      |
+---+-------+-------+----------------------+
| 1 | foo   | true  | -1541654101129638711 |
| 2 | apple | false | 2794438866806483259  |
| 3 |       | true  | -4880158226897771312 |
+---+-------+-------+----------------------+

SHA1

SHA1(input)

Descripción

Calcula el hash de la entrada mediante el uso del algoritmo SHA-1. La entrada puede ser STRING o BYTES. La versión de string trata a la entrada como un arreglo de bytes.

Esta función muestra 20 bytes.

Tipo de datos que se muestra

BYTES

Ejemplo

SELECT SHA1("Hello World") as sha1;

-- Note that the result of SHA1 is of type BYTES, displayed as a base64-encoded string.
+------------------------------+
| sha1                         |
+------------------------------+
| Ck1VqNd45QIvq3AZd8XYQLvEhtA= |
+------------------------------+

SHA256

SHA256(input)

Descripción

Calcula el hash de la entrada mediante el uso del algoritmo SHA-256. La entrada puede ser STRING o BYTES. La versión de string trata a la entrada como un arreglo de bytes.

Esta función muestra 32 bytes.

Tipo de datos que se muestra

BYTES

Ejemplo

SELECT SHA256("Hello World") as sha256;

SHA512

SHA512(input)

Descripción

Calcula el hash de la entrada mediante el uso del algoritmo SHA-512. La entrada puede ser STRING o BYTES. La versión de string trata a la entrada como un arreglo de bytes.

Esta función muestra 64 bytes.

Tipo de datos que se muestra

BYTES

Ejemplo

SELECT SHA512("Hello World") as sha512;

Funciones de string

Estas funciones de string funcionan en dos valores diferentes: tipos de datos STRING y BYTES. Los valores de STRING deben ser UTF-8 y estar bien formados.

Las funciones que muestran valores de posición, como STRPOS, codifican esas posiciones como INT64. El valor 1 se refiere al primer carácter (o byte), 2 se refiere al segundo, y así sucesivamente. El valor 0 indica un índice no válido. Cuando se trabaja en los tipos de STRING, las posiciones mostradas hacen referencia a las posiciones de los caracteres.

Todas las comparaciones de string se realizan byte a byte, sin importar la equivalencia canónica Unicode.

BYTE_LENGTH

BYTE_LENGTH(value)

Descripción

Muestra la longitud del valor en bytes, sin importar si el tipo de valor es STRING o BYTES.

Tipo de datos que se muestra

INT64

Ejemplos


WITH example AS
  (SELECT "абвгд" AS characters, b"абвгд" AS bytes)

SELECT
  characters,
  BYTE_LENGTH(characters) AS string_example,
  bytes,
  BYTE_LENGTH(bytes) AS bytes_example
FROM example;

+------------+----------------+-------+---------------+
| characters | string_example | bytes | bytes_example |
+------------+----------------+-------+---------------+
| абвгд      | 10             | абвгд | 10            |
+------------+----------------+-------+---------------+

CHAR_LENGTH

CHAR_LENGTH(value)

Descripción

Muestra la longitud de la STRING en caracteres.

Tipo de datos que se muestra

INT64

Ejemplos


WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  CHAR_LENGTH(characters) AS char_length_example
FROM example;

+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд      |                   5 |
+------------+---------------------+

CHARACTER_LENGTH

CHARACTER_LENGTH(value)

Descripción

Sinónimo de CHAR_LENGTH.

Tipo de datos que se muestra

INT64

Ejemplos


WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  CHARACTER_LENGTH(characters) AS char_length_example
FROM example;

+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд      |                   5 |
+------------+---------------------+

CODE_POINTS_TO_BYTES

CODE_POINTS_TO_BYTES(ascii_values)

Descripción

Toma un arreglo de puntos de código ASCII extendidos (ARRAY de INT64) y muestra BYTES.

Para convertir BYTES en un arreglo de puntos de código, consulta TO_CODE_POINTS.

Tipo de datos que se muestra

BYTES

Ejemplos

El siguiente es un ejemplo básico que usa CODE_POINTS_TO_BYTES.

SELECT CODE_POINTS_TO_BYTES([65, 98, 67, 100]) AS bytes;

+-------+
| bytes |
+-------+
| AbCd  |
+-------+

En el siguiente ejemplo, se usa un algoritmo de rotación por 13 lugares (ROT13) para codificar una string.

SELECT CODE_POINTS_TO_BYTES(ARRAY(
  (SELECT
      CASE
        WHEN chr BETWEEN b'a' AND b'z'
          THEN TO_CODE_POINTS(b'a')[offset(0)] +
            MOD(code + 13 - TO_CODE_POINTS(b'a')[offset(0)],26)
        WHEN chr BETWEEN b'A' AND b'Z'
          THEN TO_CODE_POINTS(b'A')[offset(0)] +
            MOD(code + 13 - TO_CODE_POINTS(b'A')[offset(0)],26)
        ELSE code
      END
   FROM
     (SELECT code, CODE_POINTS_TO_BYTES(ARRAY[code]) AS chr
      FROM UNNEST(TO_CODE_POINTS(input)) AS code WITH OFFSET
      ORDER BY OFFSET)
   ))) AS encoded_string
FROM UNNEST(ARRAY['Test String!']) AS input;

+----------------+
| encoded_string |
+----------------+
| Grfg Fgevat!   |
+----------------+

CODE_POINTS_TO_STRING

CODE_POINTS_TO_STRING(value)

Descripción

Toma un arreglo de puntos de código (ARRAY de INT64) Unicode y muestra una STRING.

Para convertir una string en un arreglo de puntos de código, consulta TO_CODE_POINTS.

Tipo de datos que se muestra

STRING

Ejemplo

El siguiente es un ejemplo básico que usa CODE_POINTS_TO_STRING.

SELECT CODE_POINTS_TO_STRING([65, 255, 513, 1024]) AS string;

+--------+
| string |
+--------+
| AÿȁЀ   |
+--------+

En el siguiente ejemplo, se calcula la frecuencia de las letras en un conjunto de palabras.

WITH Words AS (
  SELECT word
  FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word
)
SELECT
  CODE_POINTS_TO_STRING([code_point]) AS letter,
  COUNT(*) AS letter_count
FROM Words,
  UNNEST(TO_CODE_POINTS(word)) AS code_point
GROUP BY 1
ORDER BY 2 DESC;

+--------+--------------+
| letter | letter_count |
+--------+--------------+
| a      | 5            |
| f      | 3            |
| r      | 2            |
| b      | 2            |
| l      | 2            |
| o      | 2            |
| g      | 1            |
| z      | 1            |
| e      | 1            |
| m      | 1            |
| i      | 1            |
+--------+--------------+

CONCAT

CONCAT(value1[, ...])

Descripción

Concatena uno o más valores STRING o BYTE en un solo resultado.

La función muestra NULL si algún argumento de entrada es NULL.

Tipo de datos que se muestra

STRING o BYTES

Ejemplos

SELECT CONCAT("T.P.", " ", "Bar") as author;

+---------------------+
| author              |
+---------------------+
| T.P. Bar            |
+---------------------+

With Employees AS
  (SELECT
    "John" AS first_name,
    "Doe" AS last_name
  UNION ALL
  SELECT
    "Jane" AS first_name,
    "Smith" AS last_name
  UNION ALL
  SELECT
    "Joe" AS first_name,
    "Jackson" AS last_name)

SELECT
  CONCAT(first_name, " ", last_name)
  AS full_name
FROM Employees;

+---------------------+
| full_name           |
+---------------------+
| John Doe            |
| Jane Smith          |
| Joe Jackson         |
+---------------------+

ENDS_WITH

ENDS_WITH(value1, value2)

Descripción

Toma dos valores. Muestra TRUE si el segundo valor es un sufijo del primero.

Tipo de datos que se muestra

BOOL

Ejemplos


WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  ENDS_WITH(item, "e") as example
FROM items;

+---------+
| example |
+---------+
|    True |
|   False |
|    True |
+---------+

FORMAT

SQL de Cloud Spanner admite una función FORMAT() para el formato de strings. Esta función es similar a la función printf de C. Produce una STRING a partir de una string de formato que contiene cero o más especificadores de formato, junto con una lista de longitud variable de argumentos adicionales que coincide con los especificadores de formato. Estos son algunos ejemplos:

Descripción Statement Resultado
Número entero simple FORMAT("%d", 10) 10
Número entero con relleno en blanco a la izquierda FORMAT("|%10d|", 11) |           11|
Número entero con relleno de ceros a la izquierda FORMAT("+%010d+", 12) +0000000012+
Número entero con comas FORMAT("%'d", 123456789) 123,456,789
STRING FORMAT("-%s-", 'abcd efg') -abcd efg-
FLOAT64 FORMAT("%f %E", 1.1, 2.2) 1.100000 2.200000E+00
DATE FORMAT("%t", date "2015-09-01") 2015-09-01
TIMESTAMP FORMAT("%t", timestamp "2015-09-01 12:34:56 America/Los_Angeles") 2015‑09‑01 19:34:56+00

La función FORMAT() no proporciona un formato personalizable por completo para todos los tipos y valores, ni un formato sensible a la configuración regional.

Si el formato personalizado es necesario para un tipo, primero debes formatearlo con funciones de formato específicas del tipo, como FORMAT_DATE() o FORMAT_TIMESTAMP(). Por ejemplo:

SELECT FORMAT("date: %s!", FORMAT_DATE("%B %d, %Y", date '2015-01-02'));

Muestra

date: January 02, 2015!

Sintaxis

La sintaxis FORMAT() toma una string de formato y una lista de argumentos de longitud variable, y produce un resultado de STRING:

FORMAT(<format_string>, ...)

La expresión <format_string> puede contener cero o más especificadores de formato. Cada especificador de formato se ingresa mediante el símbolo % y debe asignarse a uno o más de los argumentos restantes. En general, esta es una asignación uno a uno, excepto cuando está presente el especificador *. Por ejemplo, %.*i se asigna a dos argumentos: uno de longitud y uno de número entero con signo. Si el número de argumentos relacionado con los especificadores de formato no es el mismo que el número de argumentos, se genera un error.

Especificadores de formato admitidos

El especificador de formato de función FORMAT() sigue este prototipo:

%[flags][width][.precision]specifier

Los especificadores de formato admitidos se identifican en la siguiente tabla. Las desviaciones de printf() se identifican en cursiva.

Especificador Descripción Ejemplos Tipos
d o i Número entero decimal 392 INT64
o Octal 610
INT64*
x Número entero hexadecimal 7fa
INT64*
X Número entero hexadecimal (mayúsculas) 7FA
INT64*
f Notación decimal, en [-](parte de número entero).(parte fraccionaria) para valores finitos y en minúsculas para valores no finitos 392.650000
INF
NAN

FLOAT64
F Notación decimal, en [-](parte de número entero) (parte de fracciones) para valores finitos y en mayúsculas para valores no finitos 392.650000
INF
NAN

FLOAT64
e Notación científica (mantisa/exponente), minúsculas 3.926500e+02
INF
NAN

FLOAT64
E Notación científica (mantisa/exponente), mayúsculas 3.926500E+02
INF
NAN

FLOAT64
g Notación decimal o científica, según el exponente del valor de entrada y la precisión especificada. Minúscula. Consulta el comportamiento de %g y %G para obtener más detalles. 392.65
3.9265e+07
inf
nan

FLOAT64
G Notación decimal o científica, según el exponente del valor de entrada y la precisión especificada. Mayúscula. Consulta el comportamiento de %g y %G para obtener más detalles. 392.65
3.9265E+07
INF
NAN

FLOAT64
s String de caracteres sample STRING
t Muestra una string imprimible que representa el valor. A menudo, se parece a convertir el tipo del argumento en STRING. Consulta el comportamiento de %t y %T. sample
2014‑01‑01
<any>
T Genera una string que es una constante SQL de Cloud Spanner válida con un tipo similar al tipo del valor (quizás más ancho o tal vez string). Consulta el comportamiento %t y %T. 'sample'
b'bytes sample'
1234
2.3
date '2014‑01‑01'
<any>
% “%%” genera un solo “%” % N/A

* Los especificadores %o, %x y %X generan un error si se usan valores negativos.

El especificador de formato puede contener de forma opcional los subespecificadores identificados antes en el prototipo del especificador.

Estos subespecificadores deben cumplir con las siguientes especificaciones.

Marcas
Marcas Descripción
- Justificar a la izquierda dentro del ancho de campo dado; la justificación a la derecha es la predeterminada (ver subespecificador de ancho)
+ Obliga a anteponer un signo más o menos al resultado (+ o -) incluso para números positivos. De forma predeterminada, solo los números negativos van precedidos de un signo -
<space> Si no se va a escribir ningún signo, se inserta un espacio en blanco antes del valor.
#
  • Para “%o”, “%x” y “%X”, esta marca significa que se debe anteponer 0, 0x o 0X a los valores distintos de cero, de forma respectiva.
  • Para “%f”, “%F”, “%e” y “%E”, esta marca significa que se debe agregar el punto decimal, incluso cuando no hay una parte fraccionaria, a menos que el valor sea no finito.
  • En el caso de “%g” y “%G”, esta marca significa que se debe agregar el punto decimal, incluso cuando no hay una parte fraccionaria, a menos que el valor sea no finito, y nunca quitar los ceros finales después del punto decimal.
0 Rellena a la izquierda del número con ceros (0) en lugar de espacios cuando se especifica el relleno (ver subespecificador de ancho).
'

Formatea números enteros con el carácter de agrupación apropiado. Por ejemplo:

  • FORMAT("%'d", 12345678) muestra 12,345,678
  • FORMAT("%'x", 12345678) muestra bc:614e
  • FORMAT("%'o", 55555) muestra 15,4403
  • Esta marca solo es relevante para valores decimales, hexadecimales y octales.

Las marcas se pueden especificar en cualquier orden. Las marcas duplicadas no son un error. Cuando las marcas no son relevantes para algún tipo de elemento, se ignoran.

Ancho
Ancho Descripción
<number> Número mínimo de caracteres que se deben imprimir. Si el valor que se debe imprimir es más corto que este número, el resultado se rellena con espacios en blanco. El valor no se trunca aunque el resultado sea mayor.
* El ancho no se especifica en la string de formato, sino como un argumento de número entero adicional que precede al argumento que debe formatearse.
Precisión
Precisión Descripción
.<number>
  • Para especificadores de números enteros “%d”, “%i”, “%o”, “%u”, “%x” y “%X”: la precisión especifica la cantidad mínima de dígitos que se escribirán. Si el valor que se debe escribir es más corto que este número, el resultado se rellena con ceros finales. El valor no se trunca aunque el resultado sea más largo. Una precisión de 0 significa que no se escribe ningún carácter para el valor 0.
  • Para los especificadores “%a”, “%A”, “%e”, “%E”, “%f” y “%F”: esta es la cantidad de dígitos que se imprimirán después del punto decimal. El valor predeterminado es 6.
  • Para los especificadores “%g” y “%G”: es la cantidad de dígitos significativos que se imprimirán antes de quitar los ceros finales después del punto decimal. El valor predeterminado es 6.
.* La precisión no se especifica en la string de formato, sino como un argumento de número entero adicional que precede al argumento que debe formatearse.

Comportamiento de %g y %G

Los especificadores de formato %g y %G eligen la notación decimal (como los especificadores %f y %F) o la notación científica (como los especificadores %e y %E), según el exponente del valor de entrada y la precisión especificada.

Supongamos que p representa la precisión especificada (el valor predeterminado es 6; 1 si la precisión especificada es menor que 1). Primero el valor de entrada se convierte en notación científica con precisión = (p - 1). Si la parte x del exponente resultante es menor que -4 o no menor que p, se usa la notación científica con precisión = (p - 1); de lo contrario, se usa la notación decimal con precisión = (p - 1 - x).

A menos que esté presente la marca #, se quitan los ceros finales después del punto decimal y el punto decimal si no hay dígitos después de este.

Comportamiento de %t y %T

Los especificadores de formato %t y %T están definidos para todos los tipos. El ancho, la precisión y las marcas actúan como lo hacen en %s: el ancho es el ancho mínimo y la STRING se rellenará hasta alcanzar ese tamaño, mientras que la precisión es el ancho máximo de contenido para mostrar y la STRING se truncará a ese tamaño, antes del relleno para el ancho.

El especificador %t siempre debe ser una forma legible del valor.

El especificador %T siempre es un literal de SQL válido de un tipo similar, como un tipo numérico más amplio. El literal no incluirá conversiones de tipos o un nombre de tipo, excepto en el caso especial de valores de punto flotante no finitos.

La STRING está formateada de la siguiente manera:

Tipo %t %T
NULL de cualquier tipo NULL NULL
INT64
123 123
FLOAT64 123.0 (siempre con .0)
123e+10
inf
-inf
NaN
123.0 (siempre con .0)
123e+10
CAST("inf" AS <type>)
CAST("-inf" AS <type>)
CAST("nan" AS <type>)
STRING valor de string sin comillas literal de string entre comillas
BYTES bytes escapados sin comillas
, p. ej., abc\x01\x02
literal de bytes entre comillas
, p. ej., b"abc\x01\x02"
DATE 2011-02-03 DATE "2011-02-03"
TIMESTAMP 2011-02-03 04:05:06+00 TIMESTAMP "2011-02-03 04:05:06+00"
ARRAY [value, value, …]
; aquí, los valores se formatean con %t
[value, value, …]
; aquí, los valores se formatean con %T

Condiciones de error

Si un especificador de formato no es válido o no es compatible con el tipo de argumento relacionado, o si se proporciona número o argumentos incorrectos, se genera un error. Por ejemplo, las siguientes expresiones <format_string> no son válidas:

FORMAT('%s', 1)
FORMAT('%')

Manejo de argumentos NULL

Una string de formato NULL da como resultado una STRING de salida NULL. Cualquier otro argumento se ignora en este caso.

Por lo general, la función produce un valor NULL si hay un argumento NULL presente. Por ejemplo, FORMAT('%i', <NULL expression>) produce una STRING NULL como resultado.

Sin embargo, existen algunas excepciones: si el especificador de formato es %t o %T (ambos producen STRINGs que coinciden de forma efectiva con la semántica de valores literales y CAST), un valor NULL produce “NULL” (sin comillas) en la STRING de resultado. Por ejemplo, la siguiente función:

FORMAT('00-%t-00', <NULL expression>);

Muestra

00-NULL-00

Reglas semánticas adicionales

Los valores de FLOAT64 pueden ser +/-inf o NaN. Cuando un argumento tiene uno de esos valores, el resultado de los especificadores de formato %f, %F, %e, %E, %g, %G y %t son inf, -inf o nan (o lo mismo en mayúsculas), según corresponda. Esto es coherente con la forma en que SQL de Cloud Spanner envía estos valores a STRING. En el caso de %T, SQL de Cloud Spanner SQL muestra strings entrecomilladas para los valores FLOAT64 que no tienen representaciones literales que no son de string.

FROM_BASE64

FROM_BASE64(string_expr)

Descripción

Convierte la entrada codificada en base64 string_expr en el formato BYTES. Para convertir BYTES a una STRING codificada en base64, usa TO_BASE64.

Tipo de datos que se muestra

BYTES

Ejemplo

SELECT FROM_BASE64('3q2+7w==') AS byte_data;

-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| 3q2+7w==  |
+-----------+

FROM_HEX

FROM_HEX(string)

Descripción

Convierte una STRING codificada en hexadecimal en el formato BYTES. Muestra un error si la STRING de entrada contiene caracteres fuera del rango (0..9, A..F, a..f). No importa si los caracteres están en mayúsculas o minúsculas. Si la STRING de entrada tiene un número impar de caracteres, la función actúa como si la entrada tuviera un 0 inicial adicional. Para convertir BYTES en una STRING con codificación hexadecimal, usa TO_HEX.

Tipo de datos que se muestra

BYTES

Ejemplo

WITH Input AS (
  SELECT '00010203aaeeefff' AS hex_str UNION ALL
  SELECT '0AF' UNION ALL
  SELECT '666f6f626172'
)
SELECT hex_str, FROM_HEX(hex_str) AS bytes_str
FROM Input;

-- Note that the result of FROM_HEX is of type BYTES, displayed as a base64-encoded string.
+------------------+--------------+
| hex_str          | bytes_str    |
+------------------+--------------+
| 0AF              | AAECA6ru7/8= |
| 00010203aaeeefff | AK8=         |
| 666f6f626172     | Zm9vYmFy     |
+------------------+--------------+

LENGTH

LENGTH(value)

Descripción

Muestra la longitud del valor. El valor mostrado está en caracteres para los argumentos de STRING y en bytes para el argumento de BYTES.

Tipo de datos que se muestra

INT64

Ejemplos


WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  LENGTH(characters) AS string_example,
  LENGTH(CAST(characters AS BYTES)) AS bytes_example
FROM example;

+------------+----------------+---------------+
| characters | string_example | bytes_example |
+------------+----------------+---------------+
| абвгд      |              5 |            10 |
+------------+----------------+---------------+

LPAD

LPAD(original_value, return_length[, pattern])

Descripción

Muestra un valor que consta de original_value con pattern antepuesto. return_length es un INT64 que especifica la longitud del valor mostrado. Si original_value es BYTES, return_length es el número de bytes. Si original_value es STRING, return_length es el número de caracteres.

El valor predeterminado de pattern es un espacio en blanco.

original_value y pattern deben tener el mismo tipo de datos.

Si return_length es menor o igual que la longitud de original_value, esta función muestra el valor original_value, truncado al valor de return_length. Por ejemplo, LPAD("hello world", 7); muestra "hello w".

Si original_value, return_length o pattern es NULL, esta función muestra NULL.

Esta función muestra un error si se da una de estas condiciones:

  • return_length es negativo.
  • pattern está vacía.

Tipo de datos que se muestra

STRING o BYTES

Ejemplos

SELECT t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
  STRUCT('abc' AS t, 5 AS len),
  ('abc', 2),
  ('例子', 4)
]);
t len LPAD
abc 5 "  abc"
abc 2 "ab"
例子 4 "  例子"
SELECT t, len, pattern, FORMAT("%T", LPAD(t, len, pattern)) AS LPAD FROM UNNEST([
  STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
  ('abc', 5, '-'),
  ('例子', 5, '中文')
]);
t len pattern LPAD
abc 8 def "defdeabc"
abc 5 - "--abc"
例子 5 中文 "中文中例子"
SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
  STRUCT(b'abc' AS t, 5 AS len),
  (b'abc', 2),
  (b'\xab\xcd\xef', 4)
]);
t len LPAD
b"abc" 5 b"  abc"
b"abc" 2 b"ab"
b"\xab\xcd\xef" 4 b" \xab\xcd\xef"
SELECT
  FORMAT("%T", t) AS t,
  len,
  FORMAT("%T", pattern) AS pattern,
  FORMAT("%T", LPAD(t, len, pattern)) AS LPAD
FROM UNNEST([
  STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
  (b'abc', 5, b'-'),
  (b'\xab\xcd\xef', 5, b'\x00')
]);
t len pattern LPAD
b"abc" 8 b"def" b"defdeabc"
b"abc" 5 b"-" b"--abc"
b"\xab\xcd\xef" 5 b"\x00" b"\x00\x00\xab\xcd\xef"

LOWER

LOWER(value)

Descripción

Para los argumentos de STRING, muestra la string original con todos los caracteres alfabéticos en minúsculas. La asignación entre minúsculas y mayúsculas se realiza de acuerdo con la base de datos de caracteres Unicode, sin tener en cuenta las asignaciones específicas del lenguaje.

Para los argumentos de BYTES, el argumento se trata como texto ASCII, con todos los bytes mayores a 127 intactos.

Tipo de datos que se muestra

STRING o BYTES

Ejemplos


WITH items AS
  (SELECT
    "FOO" as item
  UNION ALL
  SELECT
    "BAR" as item
  UNION ALL
  SELECT
    "BAZ" as item)

SELECT
  LOWER(item) AS example
FROM items;

+---------+
| example |
+---------+
| foo     |
| bar     |
| baz     |
+---------+

LTRIM

LTRIM(value1[, value2])

Descripción

Es idéntico a TRIM, pero solo quita caracteres iniciales.

Tipo de datos que se muestra

STRING o BYTES

Ejemplos


WITH items AS
  (SELECT "   apple   " as item
  UNION ALL
  SELECT "   banana   " as item
  UNION ALL
  SELECT "   orange   " as item)

SELECT
  CONCAT("#", LTRIM(item), "#") as example
FROM items;

+-------------+
| example     |
+-------------+
| #apple   #  |
| #banana   # |
| #orange   # |
+-------------+

WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  LTRIM(item, "*") as example
FROM items;

+-----------+
| example   |
+-----------+
| apple***  |
| banana*** |
| orange*** |
+-----------+

WITH items AS
  (SELECT "xxxapplexxx" as item
  UNION ALL
  SELECT "yyybananayyy" as item
  UNION ALL
  SELECT "zzzorangezzz" as item
  UNION ALL
  SELECT "xyzpearxyz" as item)

SELECT
  LTRIM(item, "xyz") as example
FROM items;

+-----------+
| example   |
+-----------+
| applexxx  |
| bananayyy |
| orangezzz |
| pearxyz   |
+-----------+

REGEXP_CONTAINS

REGEXP_CONTAINS(value, regexp)

Descripción

Muestra TRUE si value es una coincidencia parcial para la expresión regular, regexp.

Si el argumento regexp no es válido, la función muestra un error.

Puedes buscar una coincidencia completa con ^ (principio del texto) y $ (final del texto). Debido a la precedencia del operador de expresión regular, es una buena práctica encerrar entre paréntesis todo lo que está entre ^ y $.

Tipo de datos que se muestra

BOOL

Ejemplos

SELECT
  email,
  REGEXP_CONTAINS(email, r"@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+") AS is_valid
FROM
  (SELECT
    ["foo@example.com", "bar@example.org", "www.example.net"]
    AS addresses),
  UNNEST(addresses) AS email;

+-----------------+----------+
| email           | is_valid |
+-----------------+----------+
| foo@example.com | true     |
| bar@example.org | true     |
| www.example.net | false    |
+-----------------+----------+

# Performs a full match, using ^ and $. Due to regular expression operator
# precedence, it is good practice to use parentheses around everything between ^
# and $.
SELECT
  email,
  REGEXP_CONTAINS(email, r"^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$")
    AS valid_email_address,
  REGEXP_CONTAINS(email, r"^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$")
    AS without_parentheses
FROM
  (SELECT
    ["a@foo.com", "a@foo.computer", "b@bar.org", "!b@bar.org", "c@buz.net"]
    AS addresses),
  UNNEST(addresses) AS email;

+----------------+---------------------+---------------------+
| email          | valid_email_address | without_parentheses |
+----------------+---------------------+---------------------+
| a@foo.com      | true                | true                |
| a@foo.computer | false               | true                |
| b@bar.org      | true                | true                |
| !b@bar.org     | false               | true                |
| c@buz.net      | false               | false               |
+----------------+---------------------+---------------------+

REGEXP_EXTRACT

REGEXP_EXTRACT(value, regexp)

Descripción

Muestra la primera substring de value que coincide con la expresión regular, regexp. Muestra NULL si no hay coincidencia.

Si la expresión regular contiene un grupo de captura, la función muestra la substring que coincide con él. Si la expresión no contiene un grupo de captura, la función muestra toda la substring coincidente.

Muestra un error si se da una de estas condiciones:

  • La expresión regular no es válida.
  • La expresión regular tiene más de un grupo de captura.

Tipo de datos que se muestra

STRING o BYTES

Ejemplos


WITH email_addresses AS
  (SELECT "foo@example.com" as email
  UNION ALL
  SELECT "bar@example.org" as email
  UNION ALL
  SELECT "baz@example.net" as email)

SELECT
  REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+")
  AS user_name
FROM email_addresses;

+-----------+
| user_name |
+-----------+
| foo       |
| bar       |
| baz       |
+-----------+

WITH email_addresses AS
  (SELECT "foo@example.com" as email
  UNION ALL
  SELECT "bar@example.org" as email
  UNION ALL
  SELECT "baz@example.net" as email)

SELECT
  REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.([a-zA-Z0-9-.]+$)")
  AS top_level_domain
FROM email_addresses;

+------------------+
| top_level_domain |
+------------------+
| com              |
| org              |
| net              |
+------------------+

REGEXP_EXTRACT_ALL

REGEXP_EXTRACT_ALL(value, regexp)

Descripción

Muestra un arreglo de todas las substrings de value que coinciden con la expresión regular, regexp.

La función REGEXP_EXTRACT_ALL solo muestra coincidencias no superpuestas. Por ejemplo, usar esta función para extraer ana de banana muestra solo una substring, no dos.

Tipo de datos que se muestra

Un ARRAY de STRING o BYTES

Ejemplos


WITH code_markdown AS
  (SELECT "Try `function(x)` or `function(y)`" as code)

SELECT
  REGEXP_EXTRACT_ALL(code, "`(.+?)`") AS example
FROM code_markdown;

+----------------------------+
| example                    |
+----------------------------+
| [function(x), function(y)] |
+----------------------------+

REGEXP_REPLACE

REGEXP_REPLACE(value, regexp, replacement)

Descripción

Muestra una STRING en la que todas las substrings de value que coinciden con la expresión regular regexp se reemplazan por replacement.

Puedes usar dígitos con escape de barras invertidas (de \1 a \9) dentro del argumento replacement para insertar texto que coincida con el grupo entre paréntesis correspondiente en el patrón regexp. Usa \0 para hacer referencia a todo el texto coincidente.

La función REGEXP_REPLACE solo reemplaza las coincidencias no superpuestas. Por ejemplo, si se reemplaza ana dentro de banana, ocurre un solo reemplazo, no dos.

Si el argumento regexp no es una expresión regular válida, esta función muestra un error.

Tipo de datos que se muestra

STRING o BYTES

Ejemplos


WITH markdown AS
  (SELECT "# Heading" as heading
  UNION ALL
  SELECT "# Another heading" as heading)

SELECT
  REGEXP_REPLACE(heading, r"^# ([a-zA-Z0-9\s]+$)", "<h1>\\1</h1>")
  AS html
FROM markdown;

+--------------------------+
| html                     |
+--------------------------+
| <h1>Heading</h1>         |
| <h1>Another heading</h1> |
+--------------------------+

REPLACE

REPLACE(original_value, from_value, to_value)

Descripción

Reemplaza todos los casos de from_value por to_value en original_value. Si from_value está vacío, no se realiza ningún reemplazo.

Tipo de datos que se muestra

STRING o BYTES

Ejemplos


WITH desserts AS
  (SELECT "apple pie" as dessert
  UNION ALL
  SELECT "blackberry pie" as dessert
  UNION ALL
  SELECT "cherry pie" as dessert)

SELECT
  REPLACE (dessert, "pie", "cobbler") as example
FROM desserts;

+--------------------+
| example            |
+--------------------+
| apple cobbler      |
| blackberry cobbler |
| cherry cobbler     |
+--------------------+

REPEAT

REPEAT(original_value, repetitions)

Descripción

Muestra un valor que consta de original_value, repetido. El parámetro repetitions especifica la cantidad de veces que se debe repetir original_value. Muestra NULL si original_value o repetitions son NULL.

Esta función muestra un error si el valor de repetitions es negativo.

Tipo de datos que se muestra

STRING o BYTES

Ejemplos

SELECT t, n, REPEAT(t, n) AS REPEAT FROM UNNEST([
  STRUCT('abc' AS t, 3 AS n),
  ('例子', 2),
  ('abc', null),
  (null, 3)
]);
t n REPEAT
abc 3 abcabcabc
例子 2 例子例子
abc NULL NULL
NULL 3 NULL

REVERSE

REVERSE(value)

Descripción

Muestra la inversa de la STRING o BYTES de entrada.

Tipo de datos que se muestra

STRING o BYTES

Ejemplos

WITH example AS (
  SELECT "foo" AS sample_string, b"bar" AS sample_bytes UNION ALL
  SELECT "абвгд" AS sample_string, b"123" AS sample_bytes
)
SELECT
  sample_string,
  REVERSE(sample_string) AS reverse_string,
  sample_bytes,
  REVERSE(sample_bytes) AS reverse_bytes
FROM example;

+---------------+----------------+--------------+---------------+
| sample_string | reverse_string | sample_bytes | reverse_bytes |
+---------------+----------------+--------------+---------------+
| foo           | oof            | bar          | rab           |
| абвгд         | дгвба          | 123          | 321           |
+---------------+----------------+--------------+---------------+

RPAD

RPAD(original_value, return_length[, pattern])

Descripción

Muestra un valor que consta de original_value con pattern agregado. return_length es un INT64 que especifica la longitud del valor mostrado. Si original_value es BYTES, return_length es el número de bytes. Si original_value es STRING, return_length es el número de caracteres.

El valor predeterminado de pattern es un espacio en blanco.

original_value y pattern deben tener el mismo tipo de datos.

Si return_length es menor o igual que la longitud de original_value, esta función muestra el valor original_value, truncado al valor de return_length. Por ejemplo, RPAD("hello world", 7); muestra "hello w".

Si original_value, return_length o pattern es NULL, esta función muestra NULL.

Esta función muestra un error si se da una de estas condiciones:

  • return_length es negativo.
  • pattern está vacía.

Tipo de datos que se muestra

STRING o BYTES

Ejemplos

SELECT t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
  STRUCT('abc' AS t, 5 AS len),
  ('abc', 2),
  ('例子', 4)
]);
t len RPAD
abc 5 "abc  "
abc 2 "ab"
例子 4 "例子  "
SELECT t, len, pattern, FORMAT("%T", RPAD(t, len, pattern)) AS RPAD FROM UNNEST([
  STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
  ('abc', 5, '-'),
  ('例子', 5, '中文')
]);
t len pattern RPAD
abc 8 def "abcdefde"
abc 5 - "abc--"
例子 5 中文 "例子中文中"
SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
  STRUCT(b'abc' AS t, 5 AS len),
  (b'abc', 2),
  (b'\xab\xcd\xef', 4)
]);
t len RPAD
b"abc" 5 b"abc  "
b"abc" 2 b"ab"
b"\xab\xcd\xef" 4 b"\xab\xcd\xef "
SELECT
  FORMAT("%T", t) AS t,
  len,
  FORMAT("%T", pattern) AS pattern,
  FORMAT("%T", RPAD(t, len, pattern)) AS RPAD
FROM UNNEST([
  STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
  (b'abc', 5, b'-'),
  (b'\xab\xcd\xef', 5, b'\x00')
]);
t len pattern RPAD
b"abc" 8 b"def" b"abcdefde"
b"abc" 5 b"-" b"abc--"
b"\xab\xcd\xef" 5 b"\x00" b"\xab\xcd\xef\x00\x00"

RTRIM

RTRIM(value1[, value2])

Descripción

Es idéntico a TRIM, pero solo quita caracteres finales.

Tipo de datos que se muestra

STRING o BYTES

Ejemplos


WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  RTRIM(item, "*") as example
FROM items;

+-----------+
| example   |
+-----------+
| ***apple  |
| ***banana |
| ***orange |
+-----------+

WITH items AS
  (SELECT "applexxx" as item
  UNION ALL
  SELECT "bananayyy" as item
  UNION ALL
  SELECT "orangezzz" as item
  UNION ALL
  SELECT "pearxyz" as item)

SELECT
  RTRIM(item, "xyz") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
| pear    |
+---------+

SAFE_CONVERT_BYTES_TO_STRING

SAFE_CONVERT_BYTES_TO_STRING(value)

Descripción

Convierte una secuencia de bytes en una string. Los caracteres UTF-8 no válidos se reemplazan por el carácter de reemplazo de Unicode, U+FFFD.

Tipo de datos que se muestra

STRING

Ejemplos

La siguiente instrucción muestra el carácter de reemplazo de Unicode, �.

SELECT SAFE_CONVERT_BYTES_TO_STRING(b'\xc2') as safe_convert;

DIVIDIR

SPLIT(value[, delimiter])

Descripción

Divide value mediante el argumento delimiter.

Para STRING, el delimitador predeterminado es la coma ,.

Para BYTES, debes especificar un delimitador.

La división en un delimitador vacío produce un arreglo de caracteres UTF-8 para los valores de STRING y un arreglo de BYTES para los valores de BYTES.

Dividir una STRING vacía muestra un ARRAY con una sola STRING vacía.

Tipo de datos que se muestra

ARRAY de tipo STRING o ARRAY de tipo BYTES

Ejemplos


WITH letters AS
  (SELECT "" as letter_group
  UNION ALL
  SELECT "a" as letter_group
  UNION ALL
  SELECT "b c d" as letter_group)

SELECT SPLIT(letter_group, " ") as example
FROM letters;

+----------------------+
| example              |
+----------------------+
| []                   |
| [a]                  |
| [b, c, d]            |
+----------------------+

STARTS_WITH

STARTS_WITH(value1, value2)

Descripción

Toma dos valores. Muestra TRUE si el segundo valor es un prefijo del primero.

Tipo de datos que se muestra

BOOL

Ejemplos


WITH items AS
  (SELECT "foo" as item
  UNION ALL
  SELECT "bar" as item
  UNION ALL
  SELECT "baz" as item)

SELECT
  STARTS_WITH(item, "b") as example
FROM items;

+---------+
| example |
+---------+
|   False |
|    True |
|    True |
+---------+

STRPOS

STRPOS(string, substring)

Descripción

Muestra el índice basado en 1 del primer caso de substring dentro de string. Muestra 0 si no se encuentra substring.

Tipo de datos que se muestra

INT64

Ejemplos


WITH email_addresses AS
  (SELECT
    "foo@example.com" AS email_address
  UNION ALL
  SELECT
    "foobar@example.com" AS email_address
  UNION ALL
  SELECT
    "foobarbaz@example.com" AS email_address
  UNION ALL
  SELECT
    "quxexample.com" AS email_address)

SELECT
  STRPOS(email_address, "@") AS example
FROM email_addresses;

+---------+
| example |
+---------+
|       4 |
|       7 |
|      10 |
|       0 |
+---------+

SUBSTR

SUBSTR(value, position[, length])

Descripción

Muestra una substring del valor proporcionado. El argumento position es un número entero que especifica la posición inicial de la substring, en la que position = 1 indica el primer carácter o byte. El argumento length es la cantidad máxima de caracteres para los argumentos de STRING, o de bytes para los argumentos de BYTES.

Si position es negativo, la función cuenta desde el final de value, y -1 indica el último carácter.

Si position es una posición más allá del extremo izquierdo de la STRING (position = 0 o position < -LENGTH(value)), la función comienza desde position = 1. Si length excede la longitud de value, muestra menos de length caracteres.

Si length es menor que 0, la función muestra un error.

Tipo de datos que se muestra

STRING o BYTES

Ejemplos


WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  SUBSTR(item, 2) as example
FROM items;

+---------+
| example |
+---------+
| pple    |
| anana   |
| range   |
+---------+

WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  SUBSTR(item, 2, 2) as example
FROM items;

+---------+
| example |
+---------+
| pp      |
| an      |
| ra      |
+---------+

WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  SUBSTR(item, -2) as example
FROM items;

+---------+
| example |
+---------+
| le      |
| na      |
| ge      |
+---------+

TO_BASE64

TO_BASE64(bytes_expr)

Descripción

Convierte una secuencia de BYTES en una STRING codificada en base64. Para convertir una STRING codificada en base64 en BYTES, usa FROM_BASE64.

Tipo de datos que se muestra

STRING

Ejemplo

SELECT TO_BASE64(b'\xde\xad\xbe\xef') AS base64_string;

+---------------+
| base64_string |
+---------------+
| 3q2+7w==      |
+---------------+

TO_CODE_POINTS

TO_CODE_POINTS(value)

Descripción

Toma un valor y muestra un arreglo de INT64.

  • Si value es una STRING, cada elemento del arreglo que se muestra representa un punto de código. Cada punto de código está dentro del rango de [0, 0xD7FF] y [0xE000, 0x10FFFF].
  • Si value es BYTES, cada elemento del arreglo es un valor de caracteres ASCII extendido en el rango de [0, 255].

Para convertir un arreglo de puntos de código en STRING o BYTES, consulta CODE_POINTS_TO_STRING o CODE_POINTS_TO_BYTES.

Tipo de datos que se muestra

ARRAY de INT64

Ejemplos

En el siguiente ejemplo, se obtienen los puntos de código para cada elemento de un arreglo de palabras.

SELECT word, TO_CODE_POINTS(word) AS code_points
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word;

+---------+------------------------------------+
| word    | code_points                        |
+---------+------------------------------------+
| foo     | [102, 111, 111]                    |
| bar     | [98, 97, 114]                      |
| baz     | [98, 97, 122]                      |
| giraffe | [103, 105, 114, 97, 102, 102, 101] |
| llama   | [108, 108, 97, 109, 97]            |
+---------+------------------------------------+

En el siguiente ejemplo, se convierten representaciones de números enteros de BYTES en sus valores de caracteres ASCII correspondientes.

SELECT word, TO_CODE_POINTS(word) AS bytes_value_as_integer
FROM UNNEST([b'\x00\x01\x10\xff', b'\x66\x6f\x6f']) AS word;

+------------------+------------------------+
| word             | bytes_value_as_integer |
+------------------+------------------------+
| \x00\x01\x10\xff | [0, 1, 16, 255]        |
| foo              | [102, 111, 111]        |
+------------------+------------------------+

En el siguiente ejemplo, se muestra la diferencia entre un resultado de BYTES y un resultado de STRING.

SELECT TO_CODE_POINTS(b'Ā') AS b_result, TO_CODE_POINTS('Ā') AS s_result;

+------------+----------+
| b_result   | s_result |
+------------+----------+
| [196, 128] | [256]    |
+------------+----------+

Observa que el carácter, Ā, se representa como una secuencia Unicode de dos bytes. Como resultado, la versión BYTES de TO_CODE_POINTS muestra un arreglo con dos elementos, mientras que la versión STRING muestra un arreglo con un solo elemento.

TO_HEX

TO_HEX(bytes)

Descripción

Convierte una secuencia de BYTES en una STRING hexadecimal. Convierte cada byte de la STRING en dos caracteres hexadecimales dentro del rango (0..9, a..f). Para convertir una STRING con codificación hexadecimal en BYTES, usa FROM_HEX.

Tipo de datos que se muestra

STRING

Ejemplo

WITH Input AS (
  SELECT b'\x00\x01\x02\x03\xAA\xEE\xEF\xFF' AS byte_str UNION ALL
  SELECT b'foobar'
)
SELECT byte_str, TO_HEX(byte_str) AS hex_str
FROM Input;

+----------------------------------+------------------+
| byte_string                      | hex_string       |
+----------------------------------+------------------+
| \x00\x01\x02\x03\xaa\xee\xef\xff | 00010203aaeeefff |
| foobar                           | 666f6f626172     |
+----------------------------------+------------------+

TRIM

TRIM(value1[, value2])

Descripción

Quita todos los caracteres iniciales y finales que coinciden con value2. Si no se especifica value2, se quitan todos los caracteres de espacio en blanco iniciales y finales (como se define en el estándar Unicode). Si el primer argumento es de tipo BYTES, el segundo argumento es obligatorio.

Si value2 contiene más de un carácter o byte, la función quita todos los caracteres o bytes iniciales o finales contenidos en value2.

Tipo de datos que se muestra

STRING o BYTES

Ejemplos


WITH items AS
  (SELECT "   apple   " as item
  UNION ALL
  SELECT "   banana   " as item
  UNION ALL
  SELECT "   orange   " as item)

SELECT
  CONCAT("#", TRIM(item), "#") as example
FROM items;

+----------+
| example  |
+----------+
| #apple#  |
| #banana# |
| #orange# |
+----------+

WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  TRIM(item, "*") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
+---------+

WITH items AS
  (SELECT "xxxapplexxx" as item
  UNION ALL
  SELECT "yyybananayyy" as item
  UNION ALL
  SELECT "zzzorangezzz" as item
  UNION ALL
  SELECT "xyzpearxyz" as item)

SELECT
  TRIM(item, "xyz") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
| pear    |
+---------+

UPPER

UPPER(value)

Descripción

Para los argumentos de STRING, muestra la string original con todos los caracteres alfabéticos en mayúsculas. La asignación entre mayúsculas y minúsculas se realiza de acuerdo con la base de datos de caracteres Unicode, sin tener en cuenta las asignaciones específicas del lenguaje.

Para los argumentos de BYTES, el argumento se trata como texto ASCII, con todos los bytes mayores a 127 intactos.

Tipo de datos que se muestra

STRING o BYTES

Ejemplos


WITH items AS
  (SELECT
    "foo" as item
  UNION ALL
  SELECT
    "bar" as item
  UNION ALL
  SELECT
    "baz" as item)

SELECT
  UPPER(item) AS example
FROM items;

+---------+
| example |
+---------+
| FOO     |
| BAR     |
| BAZ     |
+---------+

Funciones JSON

SQL de Cloud Spanner admite funciones que te ayudan a recuperar datos almacenados en strings con formato JSON y funciones que te ayudan a transformar los datos en strings con formato JSON.

JSON_QUERY o JSON_VALUE

JSON_QUERY(json_string_expr, json_path_string_literal), que muestra valores JSON como STRING.

JSON_VALUE(json_string_expr, json_path_string_literal), que muestra valores JSON escalares como STRING.

Descripción

Extrae valores JSON o valores escalares JSON como strings.

  • json_string_expr: Una string con formato JSON. Por ejemplo:

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path_string_literal: El formato JSONpath. Esto identifica el valor o los valores que deseas obtener de la string con formato JSON. Si json_path_string_literal muestra un JSON null, esto se convierte en un SQL NULL.

En los casos en que una clave JSON utiliza caracteres JSONPath no válidos, puedes marcar esos caracteres mediante comillas dobles.

Ejemplos

SELECT JSON_QUERY(json_text, '$') AS json_text_string
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-----------------------------------------------------------+
| json_text_string                                          |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}}                  |
| {"class":{"students":[]}}                                 |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_QUERY(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-----------------+
| first_student   |
+-----------------+
| {"name":"Jane"} |
| NULL            |
| {"name":"John"} |
+-----------------+
SELECT JSON_QUERY(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| NULL              |
| "Jamie"           |
+-------------------+
SELECT JSON_QUERY(json_text, '$.class."students"') AS student_names
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+------------------------------------+
| student_names                      |
+------------------------------------+
| [{"name":"Jane"}]                  |
| []                                 |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
SELECT JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.name') as json_name,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.name') as scalar_name,
  JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.age') as json_age,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.age') as scalar;

+-----------+-------------+----------+--------+
| json_name | scalar_name | json_age | scalar |
+-----------+-------------+----------+--------+
| "Jakob"   | Jakob       | "6"      | 6      |
+-----------+-------------+----------+--------+

En los casos en que una clave JSON utiliza caracteres JSONPath no válidos, puedes marcar esos caracteres mediante comillas dobles. Por ejemplo:

SELECT JSON_VALUE('{"a.b": {"c": "world"}}', '$."a.b".c') as hello;

+-------+
| hello |
+-------+
| world |
+-------+

Formato JSONPath

La mayoría de las funciones JSON pasan en un parámetro json_string_expr y json_path_string_literal. El parámetro json_string_expr pasa una string con formato JSON y el parámetro json_path_string_literal identifica el valor o los valores que deseas obtener de la string con formato JSON.

El parámetro json_string_expr debe ser una string JSON con el siguiente formato:

{"class" : {"students" : [{"name" : "Jane"}]}}

Crea el parámetro json_path_string_literal mediante el formato JSONPath. Como parte de este formato, este parámetro debe comenzar con un símbolo $, que hace referencia al nivel más externo de la string con formato JSON. Puedes identificar los valores secundarios mediante puntos. Si el objeto JSON es un arreglo, puedes usar corchetes para especificar el índice del arreglo. Si las claves contienen $, puntos o corchetes, consulta cada función JSON para saber cómo escaparlas.

JSONPath Descripción Ejemplo Resultado mediante el json_string_expr anterior
$ Objeto o elemento raíz “$” {"class":{"students":[{"name":"Jane"}]}}
. Operador secundario “$.class.students” [{"name":"Jane"}]
[] Operador subíndice “$.class.students[0]” {"name":"Jane"}

Una función JSON muestra NULL si el parámetro json_path_string_literal no coincide con un valor en json_string_expr. Si el valor seleccionado para una función escalar no es escalar, como un objeto o un arreglo, la función muestra NULL.

Si JSONPath no es válido, la función genera un error.

Funciones de arreglo

ARRAY

ARRAY(subquery)

Descripción

La función ARRAY muestra un ARRAY con un elemento para cada fila en una subconsulta.

Si la subquery genera una tabla de SQL, la tabla debe tener una sola columna. Cada elemento en el ARRAY de salida es el valor de la única columna de una fila en la tabla.

Limitaciones

  • Las subconsultas no están ordenadas, por lo que no se garantiza que los elementos del ARRAY de salida conserven el orden en la tabla de origen para la subconsulta. Sin embargo, si la subconsulta incluye una cláusula ORDER BY, la función ARRAY mostrará un ARRAY que respete esa cláusula.
  • Si la subconsulta muestra más de una columna, la función ARRAY muestra un error.
  • Si la subconsulta muestra una columna con tipo especificado ARRAY o filas de tipo ARRAY, la función ARRAY muestra un error: SQL de Cloud Spanner no es compatible con los ARRAY con elementos de tipo ARRAY.
  • Si la subconsulta muestra cero filas, la función ARRAY muestra un ARRAY vacío. Nunca muestra un NULL ARRAY.

Tipo de datos que se muestra

ARRAY

Ejemplos

SELECT ARRAY
  (SELECT 1 UNION ALL
   SELECT 2 UNION ALL
   SELECT 3) AS new_array;

+-----------+
| new_array |
+-----------+
| [1, 2, 3] |
+-----------+

Si deseas construir un ARRAY a partir de una subconsulta que contiene varias columnas, debes modificar la subconsulta para usar SELECT AS STRUCT. Ahora, la función ARRAY mostrará un ARRAY de STRUCT. El ARRAY contendrá un valor STRUCT por cada fila de la subconsulta y cada uno de estos valores STRUCT contendrá un campo por cada columna de esa fila.

SELECT
  ARRAY
    (SELECT AS STRUCT 1, 2, 3
     UNION ALL SELECT AS STRUCT 4, 5, 6) AS new_array;

+------------------------+
| new_array              |
+------------------------+
| [{1, 2, 3}, {4, 5, 6}] |
+------------------------+

De la misma forma, para construir un ARRAY a partir de una subconsulta que contiene uno o más ARRAY, cambia la subconsulta para usar SELECT AS STRUCT.

SELECT ARRAY
  (SELECT AS STRUCT [1, 2, 3] UNION ALL
   SELECT AS STRUCT [4, 5, 6]) AS new_array;

+----------------------------+
| new_array                  |
+----------------------------+
| [{[1, 2, 3]}, {[4, 5, 6]}] |
+----------------------------+

ARRAY_CONCAT

ARRAY_CONCAT(array_expression_1 [, array_expression_n])

Descripción

Concatena uno o más arreglos con el mismo tipo de elemento en un solo arreglo.

Tipo de datos que se muestra

ARRAY

Ejemplos

SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;

+--------------------------------------------------+
| count_to_six                                     |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6]                               |
+--------------------------------------------------+

ARRAY_LENGTH

ARRAY_LENGTH(array_expression)

Descripción

Muestra el tamaño del arreglo. Muestra 0 para un arreglo vacío. Muestra NULL si la array_expression es NULL.

Tipo de datos que se muestra

INT64

Ejemplos

WITH items AS
  (SELECT ["coffee", NULL, "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)

SELECT list, ARRAY_LENGTH(list) AS size
FROM items
ORDER BY size DESC;

+---------------------------------+------+
| list                            | size |
+---------------------------------+------+
| [coffee, NULL, milk]            | 3    |
| [cake, pie]                     | 2    |
+---------------------------------+------+

ARRAY_TO_STRING

ARRAY_TO_STRING(array_expression, delimiter[, null_text])

Descripción

Muestra una concatenación de los elementos en la array_expression como una STRING. El valor de la array_expression puede ser un arreglo de tipos de datos STRING o BYTES.

Si se usa el parámetro null_text, la función reemplaza cualquier valor NULL en el arreglo por el valor de null_text.

Si no se usa el parámetro null_text, la función omite el valor NULL y su delimitador anterior.

Ejemplos

WITH items AS
  (SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie", NULL] as list)

SELECT ARRAY_TO_STRING(list, '--') AS text
FROM items;

+--------------------------------+
| text                           |
+--------------------------------+
| coffee--tea--milk              |
| cake--pie                      |
+--------------------------------+
WITH items AS
  (SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie", NULL] as list)

SELECT ARRAY_TO_STRING(list, '--', 'MISSING') AS text
FROM items;

+--------------------------------+
| text                           |
+--------------------------------+
| coffee--tea--milk              |
| cake--pie--MISSING             |
+--------------------------------+

GENERATE_ARRAY

GENERATE_ARRAY(start_expression, end_expression[, step_expression])

Descripción

Muestra un arreglo de valores. Los parámetros start_expression y end_expression determinan el inicio y el final inclusivos del arreglo.

La función GENERATE_ARRAY acepta los siguientes tipos de datos como entradas:

  • INT64
  • FLOAT64

El parámetro step_expression determina el incremento que se usa para generar valores de arreglo. El valor predeterminado para este parámetro es 1.

Esta función muestra un error si step_expression se establece en 0 o si alguna entrada es NaN.

Si algún argumento es NULL, la función mostrará un arreglo NULL.

Tipo de datos mostrados

ARRAY

Ejemplos

En el siguiente ejemplo, se muestra un arreglo de números enteros, con un paso predeterminado de 1.

SELECT GENERATE_ARRAY(1, 5) AS example_array;

+-----------------+
| example_array   |
+-----------------+
| [1, 2, 3, 4, 5] |
+-----------------+

A continuación, se muestra un arreglo con un tamaño del paso especificado por el usuario.

SELECT GENERATE_ARRAY(0, 10, 3) AS example_array;

+---------------+
| example_array |
+---------------+
| [0, 3, 6, 9]  |
+---------------+

A continuación, se muestra un arreglo con un valor negativo, -3, como su tamaño del paso.

SELECT GENERATE_ARRAY(10, 0, -3) AS example_array;

+---------------+
| example_array |
+---------------+
| [10, 7, 4, 1] |
+---------------+

A continuación, se muestra un arreglo con el mismo valor para start_expression y end_expression.

SELECT GENERATE_ARRAY(4, 4, 10) AS example_array;

+---------------+
| example_array |
+---------------+
| [4]           |
+---------------+

En el siguiente ejemplo, se muestra un arreglo vacío porque start_expression es mayor que end_expression, y el valor step_expression es positivo.

SELECT GENERATE_ARRAY(10, 0, 3) AS example_array;

+---------------+
| example_array |
+---------------+
| []            |
+---------------+

En el siguiente ejemplo, se muestra un arreglo NULL porque la end_expression es NULL.

SELECT GENERATE_ARRAY(5, NULL, 1) AS example_array;

+---------------+
| example_array |
+---------------+
| NULL          |
+---------------+

En el siguiente ejemplo, se muestran múltiples arreglos.

SELECT GENERATE_ARRAY(start, 5) AS example_array
FROM UNNEST([3, 4, 5]) AS start;

+---------------+
| example_array |
+---------------+
| [3, 4, 5]     |
| [4, 5]        |
| [5]           |
+---------------+

GENERATE_DATE_ARRAY

GENERATE_DATE_ARRAY(start_date, end_date[, INTERVAL INT64_expr date_part])

Descripción

Muestra un arreglo de fechas. Los parámetros start_date y end_date determinan el inicio y el final inclusivos del arreglo.

La función GENERATE_DATE_ARRAY acepta los siguientes tipos de datos como entradas:

  • start_date debe ser DATE.
  • end_date debe ser DATE.
  • INT64_expr debe ser INT64.
  • date_part debe ser DAY, WEEK, MONTH, QUARTER o YEAR.

El parámetro INT64_expr determina el incremento que se usa para generar fechas. El valor predeterminado para este parámetro es de 1 día.

Esta función muestra un error si INT64_expr se establece en 0.

Tipo de datos mostrados

Un ARRAY que contiene 0 o más valores DATE.

Ejemplos

A continuación, se muestra un arreglo de fechas con un paso predeterminado de 1.

SELECT GENERATE_DATE_ARRAY('2016-10-05', '2016-10-08') AS example;

+--------------------------------------------------+
| example                                          |
+--------------------------------------------------+
| [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] |
+--------------------------------------------------+

A continuación, se muestra un arreglo con un tamaño del paso especificado por el usuario.

SELECT GENERATE_DATE_ARRAY(
 '2016-10-05', '2016-10-09', INTERVAL 2 DAY) AS example;

+--------------------------------------+
| example                              |
+--------------------------------------+
| [2016-10-05, 2016-10-07, 2016-10-09] |
+--------------------------------------+

A continuación, se muestra un arreglo con un valor negativo, -3, como su tamaño del paso.

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-01', INTERVAL -3 DAY) AS example;

+--------------------------+
| example                  |
+--------------------------+
| [2016-10-05, 2016-10-02] |
+--------------------------+

En el siguiente ejemplo, se muestra un arreglo con el mismo valor para start_date y end_date.

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-05', INTERVAL 8 DAY) AS example;

+--------------+
| example      |
+--------------+
| [2016-10-05] |
+--------------+

En el siguiente ejemplo, se muestra un arreglo vacío porque start_date es mayor que end_date, y el valor step es positivo.

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-01', INTERVAL 1 DAY) AS example;

+---------+
| example |
+---------+
| []      |
+---------+

En el siguiente ejemplo, se muestra un arreglo NULL porque una de sus entradas es NULL.

SELECT GENERATE_DATE_ARRAY('2016-10-05', NULL) AS example;

+---------+
| example |
+---------+
| NULL    |
+---------+

En el siguiente ejemplo, se muestra un arreglo de fechas con MONTH como el intervalo date_part:

SELECT GENERATE_DATE_ARRAY('2016-01-01',
  '2016-12-31', INTERVAL 2 MONTH) AS example;

+--------------------------------------------------------------------------+
| example                                                                  |
+--------------------------------------------------------------------------+
| [2016-01-01, 2016-03-01, 2016-05-01, 2016-07-01, 2016-09-01, 2016-11-01] |
+--------------------------------------------------------------------------+

En el siguiente ejemplo, se usan fechas no constantes para generar un arreglo.

SELECT GENERATE_DATE_ARRAY(date_start, date_end, INTERVAL 1 WEEK) AS date_range
FROM (
  SELECT DATE '2016-01-01' AS date_start, DATE '2016-01-31' AS date_end
  UNION ALL SELECT DATE "2016-04-01", DATE "2016-04-30"
  UNION ALL SELECT DATE "2016-07-01", DATE "2016-07-31"
  UNION ALL SELECT DATE "2016-10-01", DATE "2016-10-31"
) AS items;

+--------------------------------------------------------------+
| date_range                                                   |
+--------------------------------------------------------------+
| [2016-01-01, 2016-01-08, 2016-01-15, 2016-01-22, 2016-01-29] |
| [2016-04-01, 2016-04-08, 2016-04-15, 2016-04-22, 2016-04-29] |
| [2016-07-01, 2016-07-08, 2016-07-15, 2016-07-22, 2016-07-29] |
| [2016-10-01, 2016-10-08, 2016-10-15, 2016-10-22, 2016-10-29] |
+--------------------------------------------------------------+

OFFSET y ORDINAL

array_expression[OFFSET(zero_based_offset)]
array_expression[ORDINAL(one_based_offset)]

Descripción

Accede a un elemento ARRAY por posición y lo muestra. OFFSET significa que la numeración comienza en cero, ORDINAL significa que comienza en uno.

Un arreglo dado puede interpretarse como basado en 0 o en 1. Cuando accedes a un elemento de arreglo, debes anteponer la posición del arreglo con OFFSET o con ORDINAL, de forma respectiva; no hay comportamiento predeterminado.

OFFSET y ORDINAL generan un error si el índice está fuera de rango.

Tipo de datos que se muestra

Varía según los elementos en el ARRAY.

Ejemplos

WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)

SELECT list, list[OFFSET(1)] as offset_1, list[ORDINAL(1)] as ordinal_1
FROM items;

+----------------------------------+-----------+-----------+
| list                             | offset_1  | ordinal_1 |
+----------------------------------+-----------+-----------+
| [apples, bananas, pears, grapes] | bananas   | apples    |
| [coffee, tea, milk]              | tea       | coffee    |
| [cake, pie]                      | pie       | cake      |
+----------------------------------+-----------+-----------+

ARRAY_REVERSE

ARRAY_REVERSE(value)

Descripción

Muestra el ARRAY de entrada con los elementos en orden inverso.

Tipo de datos que se muestra

ARRAY

Ejemplos

WITH example AS (
  SELECT [1, 2, 3] AS arr UNION ALL
  SELECT [4, 5] AS arr UNION ALL
  SELECT [] AS arr
)
SELECT
  arr,
  ARRAY_REVERSE(arr) AS reverse_arr
FROM example;

+-----------+-------------+
| arr       | reverse_arr |
+-----------+-------------+
| [1, 2, 3] | [3, 2, 1]   |
| [4, 5]    | [5, 4]      |
| []        | []          |
+-----------+-------------+

SAFE_OFFSET y SAFE_ORDINAL

array_expression[SAFE_OFFSET(zero_based_offset)]
array_expression[SAFE_ORDINAL(one_based_offset)]

Descripción

Idéntico a OFFSET y ORDINAL, excepto que muestra NULL si el índice está fuera de rango.

Tipo de datos que se muestra

Varía según los elementos en el ARRAY.

Ejemplo

WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)

SELECT list,
  list[SAFE_OFFSET(3)] as safe_offset_3,
  list[SAFE_ORDINAL(3)] as safe_ordinal_3
FROM items;

+----------------------------------+---------------+----------------+
| list                             | safe_offset_3 | safe_ordinal_3 |
+----------------------------------+---------------+----------------+
| [apples, bananas, pears, grapes] | grapes        | pears          |
| [coffee, tea, milk]              | NULL          | milk           |
| [cake, pie]                      | NULL          | NULL           |
+----------------------------------+---------------+----------------+

Funciones de fecha

SQL de Cloud Spanner admite las siguientes funciones DATE.

CURRENT_DATE

CURRENT_DATE([time_zone])

Descripción

Muestra la fecha actual de la zona horaria especificada o predeterminada.

Esta función admite un parámetro time_zone opcional. Este parámetro es una string que representa la zona horaria que se debe usar. Si no se especifica una zona horaria, se utiliza la zona horaria predeterminada, America/Los_Angeles. Consulta las definiciones de la zona horaria para obtener información sobre cómo especificar una zona horaria.

Si el parámetro time_zone se evalúa como NULL, esta función muestra NULL.

Tipo de datos mostrados

DATE

Ejemplo

SELECT CURRENT_DATE() as the_date;

+--------------+
| the_date     |
+--------------+
| 2016-12-25   |
+--------------+

EXTRACT

EXTRACT(part FROM date_expression)

Descripción

Muestra el valor correspondiente a la parte de fecha especificada. El valor part debe ser uno de los siguientes:

  • DAYOFWEEK: muestra valores en el rango [1,7] con el domingo como primer día de la semana.
  • DAY
  • DAYOFYEAR
  • WEEK: muestra el número de semana de la fecha en el rango [0, 53]. Las semanas comienzan con el domingo y las fechas anteriores al primer domingo del año están en la semana 0.
  • ISOWEEK: muestra el número de semana ISO 8601 de date_expression. Los valores ISOWEEK comienzan el lunes. Los valores de muestra están en el rango [1, 53]. El primer valor ISOWEEK de cada año ISO comienza el lunes anterior al primer jueves del año del calendario gregoriano.
  • MONTH
  • QUARTER: muestra valores en el rango [1,4].
  • YEAR
  • ISOYEAR: muestra el año de numeración de la semana ISO 8601, que es el año calendario gregoriano que contiene el jueves de la semana a la que pertenece date_expression.

Tipo de datos mostrados

INT64

Ejemplos

En el siguiente ejemplo, EXTRACT muestra un valor que corresponde a la parte DAY de la fecha.

SELECT EXTRACT(DAY FROM DATE '2013-12-25') as the_day;

+---------+
| the_day |
+---------+
| 25      |
+---------+

En el siguiente ejemplo, EXTRACT muestra valores correspondientes a diferentes partes de la fecha de una columna de fechas cerca del final del año.

SELECT
  date,
  EXTRACT(ISOYEAR FROM date) AS isoyear,
  EXTRACT(ISOWEEK FROM date) AS isoweek,
  EXTRACT(YEAR FROM date) AS year,
  EXTRACT(WEEK FROM date) AS week
FROM UNNEST(GENERATE_DATE_ARRAY('2015-12-23', '2016-01-09')) AS date
ORDER BY date;
+------------+---------+---------+------+------+
| date       | isoyear | isoweek | year | week |
+------------+---------+---------+------+------+
| 2015-12-23 | 2015    | 52      | 2015 | 51   |
| 2015-12-24 | 2015    | 52      | 2015 | 51   |
| 2015-12-25 | 2015    | 52      | 2015 | 51   |
| 2015-12-26 | 2015    | 52      | 2015 | 51   |
| 2015-12-27 | 2015    | 52      | 2015 | 52   |
| 2015-12-28 | 2015    | 53      | 2015 | 52   |
| 2015-12-29 | 2015    | 53      | 2015 | 52   |
| 2015-12-30 | 2015    | 53      | 2015 | 52   |
| 2015-12-31 | 2015    | 53      | 2015 | 52   |
| 2016-01-01 | 2015    | 53      | 2016 | 0    |
| 2016-01-02 | 2015    | 53      | 2016 | 0    |
| 2016-01-03 | 2015    | 53      | 2016 | 1    |
| 2016-01-04 | 2016    | 1       | 2016 | 1    |
| 2016-01-05 | 2016    | 1       | 2016 | 1    |
| 2016-01-06 | 2016    | 1       | 2016 | 1    |
| 2016-01-07 | 2016    | 1       | 2016 | 1    |
| 2016-01-08 | 2016    | 1       | 2016 | 1    |
| 2016-01-09 | 2016    | 1       | 2016 | 1    |
+------------+---------+---------+------+------+

DATE

1. DATE(year, month, day)
2. DATE(timestamp_expression[, timezone])

Descripción

  1. Construye un valor DATE a partir de los valores INT64 que representan el año, mes y día.
  2. Extrae la DATE de una expresión de TIMESTAMP. Admite un parámetro opcional para especificar una zona horaria. Si no se especifica una zona horaria, se utiliza la zona horaria predeterminada, America/Los_Angeles.

Tipo de datos mostrados

DATE

Ejemplo

SELECT
  DATE(2016, 12, 25) as date_ymd,
  DATE(TIMESTAMP "2016-12-25 05:30:00+07", "America/Los_Angeles") as date_tstz;

+------------+------------+
| date_ymd   | date_tstz  |
+------------+------------+
| 2016-12-25 | 2016-12-24 |
+------------+------------+

DATE_ADD

DATE_ADD(date_expression, INTERVAL int64_expression date_part)

Descripción

Agrega un intervalo de tiempo especificado a un valor DATE.

DATE_ADD admite los siguientes valores date_part:

  • DAY
  • WEEK, que equivale a 7 DAY
  • MONTH
  • QUARTER
  • YEAR

Se requiere un manejo especial para las partes MONTH, QUARTER y YEAR cuando la fecha es el último día del mes o está cerca de este. Si el mes del resultado tiene menos días que el de la fecha original, el día del resultado será el último día del nuevo mes.

Tipo de datos mostrados

DATE

Ejemplo

SELECT DATE_ADD(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_later;

+--------------------+
| five_days_later    |
+--------------------+
| 2008-12-30         |
+--------------------+

DATE_SUB

DATE_SUB(date_expression, INTERVAL int64_expression date_part)

Descripción

Resta un intervalo de tiempo especificado a un valor DATE.

DATE_SUB admite los siguientes valores date_part:

  • DAY
  • WEEK, que equivale a 7 DAY
  • MONTH
  • QUARTER
  • YEAR

Se requiere un manejo especial para las partes MONTH, QUARTER y YEAR cuando la fecha es el último día del mes o está cerca de este. Si el mes del resultado tiene menos días que el de la fecha original, el día del resultado será el último día del nuevo mes.

Tipo de datos mostrados

DATE

Ejemplo

SELECT DATE_SUB(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_ago;

+---------------+
| five_days_ago |
+---------------+
| 2008-12-20    |
+---------------+

DATE_DIFF

DATE_DIFF(date_expression_a, date_expression_b, date_part)

Descripción

Muestra el número de intervalos date_part completos especificados entre dos objetos DATE (date_expression_a y date_expression_b). Si el primer DATE es anterior al segundo, el resultado es negativo.

DATE_DIFF admite los siguientes valores date_part:

  • DAY
  • WEEK: esta parte de fecha comienza el domingo.
  • ISOWEEK: Usa los límites de la semana ISO 8601. Las semanas ISO comienzan el lunes.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: Usa el límite del año de numeración de semana ISO 8601. El límite del año ISO es el lunes de la primera semana cuyo jueves pertenece al año calendario gregoriano correspondiente.

Tipo de datos mostrados

INT64

Ejemplo

SELECT DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY) as days_diff;

+-----------+
| days_diff |
+-----------+
| 559       |
+-----------+
SELECT
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', DAY) as days_diff,
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', WEEK) as weeks_diff;

+-----------+------------+
| days_diff | weeks_diff |
+-----------+------------+
| 1         | 1          |
+-----------+------------+

En el ejemplo anterior, se muestra el resultado de DATE_DIFF durante dos días seguidos. DATE_DIFF con la parte de fecha WEEK muestra 1 porque DATE_DIFF cuenta el número de límites de parte de fecha en este rango de fechas. Cada WEEK comienza el domingo, por lo que hay un límite de parte de fecha entre el sábado, 2017-10-14, y el domingo, 2017-10-15.

En el siguiente ejemplo, se muestra el resultado de DATE_DIFF para dos fechas en años diferentes. DATE_DIFF con la parte de fecha YEAR muestra 3 porque cuenta el número de límites del año calendario gregoriano entre las dos fechas. DATE_DIFF con la parte de fecha ISOYEAR muestra 2 porque la segunda fecha pertenece al año ISO 2015. El primer jueves del año calendario 2015 fue 2015-01-01, por lo que el año ISO 2015 comienza el lunes anterior, 2014-12-29.

SELECT
  DATE_DIFF('2017-12-30', '2014-12-30', YEAR) AS year_diff,
  DATE_DIFF('2017-12-30', '2014-12-30', ISOYEAR) AS isoyear_diff;

+-----------+--------------+
| year_diff | isoyear_diff |
+-----------+--------------+
| 3         | 2            |
+-----------+--------------+

En el siguiente ejemplo, se muestra el resultado de DATE_DIFF para dos días seguidos. La primera fecha es un lunes y la segunda es un domingo. DATE_DIFF con la parte WEEK de la fecha muestra 0 porque esta parte de la fecha usa semanas que comienzan el domingo. DATE_DIFF con la parte ISOWEEK de la fecha muestra 1 porque las semanas ISO comienzan el lunes.

SELECT
  DATE_DIFF('2017-12-18', '2017-12-17', WEEK) AS week_diff,
  DATE_DIFF('2017-12-18', '2017-12-17', ISOWEEK) AS isoweek_diff;

+-----------+--------------+
| week_diff | isoweek_diff |
+-----------+--------------+
| 0         | 1            |
+-----------+--------------+

DATE_TRUNC

DATE_TRUNC(date_expression, date_part)

Descripción

Trunca la fecha al nivel de detalle especificado.

DATE_TRUNC admite los siguientes valores para date_part:

  • DAY
  • WEEK
  • ISOWEEK: trunca date_expression al límite de la semana ISO 8601 anterior. Los valores ISOWEEK comienzan el lunes. El primer ISOWEEK de cada año ISO contiene el primer jueves del año calendario gregoriano correspondiente. Cualquier valor date_expression anterior a esto se truncará al lunes anterior.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: trunca date_expression al límite de año de numeración de la semana ISO 8601. El límite del año ISO es el lunes de la primera semana cuyo jueves pertenece al año calendario gregoriano correspondiente.

Tipo de datos mostrados

DATE

Ejemplos

SELECT DATE_TRUNC(DATE '2008-12-25', MONTH) as month;

+------------+
| month      |
+------------+
| 2008-12-01 |
+------------+

En el siguiente ejemplo, el valor date_expression original está en el año calendario gregoriano 2015. Sin embargo, DATE_TRUNC con la parte de fecha ISOYEAR trunca el valor date_expression al comienzo del año ISO, no el año calendario gregoriano. El primer jueves del año calendario 2015 fue 2015-01-01, por lo que el año ISO 2015 comienza el lunes anterior, 2014-12-29. Por lo tanto, el límite del año ISO que precede al valor date_expression 2015-06-15 es 2014-12-29.

SELECT
  DATE_TRUNC('2015-06-15', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM DATE '2015-06-15') AS isoyear_number;

+------------------+----------------+
| isoyear_boundary | isoyear_number |
+------------------+----------------+
| 2014-12-29       | 2015           |
+------------------+----------------+

DATE_FROM_UNIX_DATE

DATE_FROM_UNIX_DATE(int64_expression)

Descripción

Interpreta int64_expression como el número de días desde 1970-01-01.

Tipo de datos mostrados

DATE

Ejemplo

SELECT DATE_FROM_UNIX_DATE(14238) as date_from_epoch;

+-----------------+
| date_from_epoch |
+-----------------+
| 2008-12-25      |
+-----------------+

FORMAT_DATE

FORMAT_DATE(format_string, date_expr)

Descripción

Da formato a date_expr de acuerdo con el format_string especificado.

Consulta la sección sobre elementos de formato admitidos para DATE a fin de obtener una lista de los elementos de formato que admite esta función.

Tipo de datos mostrados

STRING

Ejemplos

SELECT FORMAT_DATE("%x", DATE "2008-12-25") as US_format;

+------------+
| US_format  |
+------------+
| 12/25/08   |
+------------+
SELECT FORMAT_DATE("%b-%d-%Y", DATE "2008-12-25") AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec-25-2008 |
+-------------+
SELECT FORMAT_DATE("%b %Y", DATE "2008-12-25") AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec 2008    |
+-------------+

PARSE_DATE

PARSE_DATE(format_string, date_string)

Descripción

Usa format_string y una representación de string de una fecha para mostrar un objeto DATE.

Cuando uses PARSE_DATE, ten en cuenta lo siguiente:

  • Campos sin especificar. Todo campo sin especificar se inicializa desde 1970-01-01.
  • Nombres sin distinción entre mayúsculas y minúsculas. Los nombres, como Monday, February, etc., no distinguen entre mayúsculas y minúsculas.
  • Espacio en blanco. Uno o más espacios en blanco consecutivos en la string que coinciden con cero o más espacios en blanco consecutivos en la string de fecha. Además, los espacios en blanco iniciales y finales en la string de fecha siempre están permitidos, incluso si no están en la string de formato.
  • Precedencia del formato. Cuando dos (o más) elementos de formato tienen información superpuesta (por ejemplo, si %F y %Y afectan el año), por lo general, el último anula los anteriores.

Tipo de datos mostrados

DATE

Ejemplo

SELECT PARSE_DATE("%x", "12/25/08") as parsed;

+------------+
| parsed     |
+------------+
| 2008-12-25 |
+------------+

UNIX_DATE

UNIX_DATE(date_expression)

Descripción

Muestra el número de días desde 1970-01-01.

Tipo de datos mostrados

INT64

Ejemplo

SELECT UNIX_DATE(DATE "2008-12-25") as days_from_epoch;

+-----------------+
| days_from_epoch |
+-----------------+
| 14238           |
+-----------------+

Elementos de formato admitidos para DATE

A menos que se indique lo contrario, las funciones de DATE que usan strings de formato admiten los siguientes elementos:

Elemento de formato Descripción
%A El nombre completo del día de la semana
%a El nombre abreviado del día de la semana
%B El nombre completo del mes
%b o %h El nombre del mes abreviado
%C El siglo (un año dividido por 100 y truncado a un número entero) como un número decimal (00-99)
%D La fecha en el formato %m/%d/%y
%d El día del mes como número decimal (01-31)
%e El día del mes como número decimal (1-31); los dígitos individuales están precedidos por un espacio
%F La fecha en el formato %Y-%m-%d
%G El año ISO 8601 con siglo como número decimal. Cada año ISO comienza el lunes anterior al primer jueves del año calendario gregoriano. Ten en cuenta que %G y %Y pueden producir resultados diferentes cerca de los límites del año gregoriano, en donde el año gregoriano y el año ISO pueden divergir.
%g El año ISO 8601 sin el siglo como número decimal (00-99). Cada año ISO comienza el lunes anterior al primer jueves del año calendario gregoriano. Ten en cuenta que %y y %g pueden producir resultados diferentes cerca de los límites del año gregoriano, en los que el año gregoriano y el año ISO pueden divergir.
%j El día del año como número decimal (001-366).
%m El mes como número decimal (01-12)
%n Un carácter de salto de línea
%t Un tabulador
%U El número de la semana del año (con el domingo como primer día de la semana) como número decimal (00-53)
%u El día de la semana (lunes como primer día de la semana) como número decimal (1-7)
%V El número de semana ISO 8601 del año (el lunes como el primer día de la semana) como un número decimal (01-53). Si la semana que contiene el 1 de enero tiene cuatro o más días en el año nuevo, entonces es la semana 1; de lo contrario, es la semana 53 del año anterior, y la próxima semana es la semana 1.
%W El número de la semana del año (con el lunes como primer día de la semana) como número decimal (00-53)
%w El día de la semana (domingo como primer día de la semana) como número decimal (0-6)
%x La representación de la fecha en formato MM/DD/YY
%Y El año con siglo como número decimal
%y El año sin siglo como número decimal (00-99), con un cero inicial opcional. Se puede combinar con %C. Si no se especifica %C, los años 00-68 son los años 2000, mientras que los años 69-99 son los 1900.
%E4Y Los años de cuatro caracteres (0001 … 9999). Ten en cuenta que %Y genera tantos caracteres como sean necesarios para procesar el año completo.

Funciones de marca de tiempo

SQL de Cloud Spanner admite las siguientes funciones TIMESTAMP.

NOTA: Estas funciones muestran un error de entorno de ejecución si se produce un desbordamiento; los valores de los resultados están delimitados por la fecha definida y los valores mínimo/máximo de la marca de tiempo.

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

Descripción

Los paréntesis son opcionales. Esta función maneja los segundos bisiestos; para ello, los coloca en una ventana de 20 horas alrededor del segundo bisiesto insertado. CURRENT_TIMESTAMP() produce un valor TIMESTAMP que es continuo, no es ambiguo, tiene 60 segundos por minuto exactos y no repite los valores durante el segundo bisiesto.

Tipos de entrada admitidos

No aplicable

Tipo de datos del resultado

TIMESTAMP

Ejemplo

SELECT CURRENT_TIMESTAMP() as now;

+--------------------------------+
| now                            |
+--------------------------------+
| 2020-06-02T23:58:40.347847393Z |
+--------------------------------+

EXTRACT

EXTRACT(part FROM timestamp_expression [AT TIME ZONE tz_spec])

Descripción

Muestra un valor que corresponde a la part especificada de un timestamp_expression proporcionado.

Los valores part permitidos son los siguientes:

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAYOFWEEK
  • DAY
  • DAYOFYEAR
  • WEEK: muestra el número de semana de la fecha en el rango [0, 53]. Las semanas comienzan con el domingo y las fechas anteriores al primer domingo del año están en la semana 0.
  • ISOWEEK: muestra el número de semana ISO 8601 de datetime_expression. Los valores ISOWEEK comienzan el lunes. Los valores de muestra están en el rango [1, 53]. El primer valor ISOWEEK de cada año ISO comienza el lunes anterior al primer jueves del año del calendario gregoriano.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: muestra el año de numeración de la semana ISO 8601, que es el año calendario gregoriano que contiene el jueves de la semana a la que pertenece date_expression.
  • DATE

Los valores mostrados truncan los períodos de tiempo de orden inferior. Por ejemplo, cuando se extraen segundos, EXTRACT trunca los valores de milisegundos y microsegundos.

Consulta las definiciones de la zona horaria para obtener información sobre cómo especificar una zona horaria.

Tipo de datos mostrados

INT64, excepto cuando:

  • part es DATE; se muestra un objeto DATE.

Ejemplos

En el siguiente ejemplo, EXTRACT muestra un valor que corresponde a la parte de tiempo DAY.

WITH Input AS (SELECT TIMESTAMP("2008-12-25 05:30:00+00") AS timestamp_value)
SELECT
  EXTRACT(DAY FROM timestamp_value AT TIME ZONE "UTC") AS the_day_utc,
  EXTRACT(DAY FROM timestamp_value AT TIME ZONE "America/Los_Angeles") AS the_day_california
FROM Input

+-------------+--------------------+
| the_day_utc | the_day_california |
+-------------+--------------------+
| 25          | 24                 |
+-------------+--------------------+

En el siguiente ejemplo, EXTRACT muestra los valores que corresponden a diferentes partes de tiempo de una columna de marcas de tiempo.

WITH Timestamps AS (
  SELECT TIMESTAMP("2005-01-03 12:34:56+00") AS timestamp_value UNION ALL
  SELECT TIMESTAMP("2007-12-31 12:00:00+00") UNION ALL
  SELECT TIMESTAMP("2009-01-01 12:00:00+00") UNION ALL
  SELECT TIMESTAMP("2009-12-31 12:00:00+00") UNION ALL
  SELECT TIMESTAMP("2017-01-02 12:00:00+00") UNION ALL
  SELECT TIMESTAMP("2017-05-26 12:00:00+00")
)
SELECT
  timestamp_value,
  EXTRACT(ISOYEAR FROM timestamp_value) AS isoyear,
  EXTRACT(ISOWEEK FROM timestamp_value) AS isoweek,
  EXTRACT(YEAR FROM timestamp_value) AS year,
  EXTRACT(WEEK FROM timestamp_value) AS week
FROM Timestamps
ORDER BY timestamp_value;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+---------+---------+------+------+
| timestamp_value        | isoyear | isoweek | year | week |
+------------------------+---------+---------+------+------+
| 2005-01-03T12:34:56Z   | 2005    | 1       | 2005 | 1    |
| 2007-12-31T12:00:00Z   | 2008    | 1       | 2007 | 52   |
| 2009-01-01T12:00:00Z   | 2009    | 1       | 2009 | 0    |
| 2009-12-31T12:00:00Z   | 2009    | 53      | 2009 | 52   |
| 2017-01-02T12:00:00Z   | 2017    | 1       | 2017 | 1    |
| 2017-05-26T12:00:00Z   | 2017    | 21      | 2017 | 21   |
+------------------------+---------+---------+------+------+

STRING

STRING(timestamp_expression[, timezone])

Descripción

Convierte un valor timestamp_expression en un tipo de datos STRING. Admite un parámetro opcional para especificar una zona horaria. Consulta las definiciones de la zona horaria para obtener información sobre cómo especificar una zona horaria.

Tipo de datos mostrados

STRING

Ejemplo

SELECT STRING(TIMESTAMP "2008-12-25 15:30:00+00", "UTC") AS string;

+-------------------------------+
| string                        |
+-------------------------------+
| 2008-12-25 15:30:00+00        |
+-------------------------------+

TIMESTAMP

TIMESTAMP(string_expression[, timezone])
TIMESTAMP(date_expression[, timezone])

Descripción

  • string_expression[, timezone]: convierte una expresión STRING en un tipo de datos TIMESTAMP. string_expression debe incluir un literal de marca de tiempo. Si string_expression incluye una zona horaria en literal de marca de tiempo, no incluyas un argumento explícito timezone.
  • date_expression[, timezone]: convierte un objeto DATE en un tipo de datos TIMESTAMP.

Esta función admite un parámetro opcional para especificar una zona horaria. Si no se especifica ninguna zona horaria, se utiliza la zona horaria predeterminada, America/Los_Angeles.

Tipo de datos mostrados

TIMESTAMP

Ejemplos

SELECT TIMESTAMP("2008-12-25 15:30:00+00") AS timestamp_str;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+----------------------+
| timestamp_str        |
+----------------------+
| 2008-12-25T15:30:00Z |
+----------------------+
SELECT TIMESTAMP("2008-12-25 15:30:00", "America/Los_Angeles") AS timestamp_str;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+----------------------+
| timestamp_str        |
+----------------------+
| 2008-12-25T23:30:00Z |
+----------------------+
SELECT TIMESTAMP("2008-12-25 15:30:00 UTC") AS timestamp_str;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+----------------------+
| timestamp_str        |
+----------------------+
| 2008-12-25T15:30:00Z |
+----------------------+
SELECT TIMESTAMP(DATE "2008-12-25") AS timestamp_date;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+----------------------+
| timestamp_str        |
+----------------------+
| 2008-12-25T08:00:00Z |
+----------------------+

TIMESTAMP_ADD

TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression date_part)

Descripción

Agrega int64_expression unidades de date_part a la marca de tiempo, sin importar la zona horaria.

TIMESTAMP_ADD admite los siguientes valores para date_part:

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR. Equivalente a 60 MINUTE.
  • DAY. Equivalente a 24 HOUR.

Tipos de datos mostrados

TIMESTAMP

Ejemplo

SELECT
  TIMESTAMP("2008-12-25 15:30:00+00") AS original,
  TIMESTAMP_ADD(TIMESTAMP "2008-12-25 15:30:00+00", INTERVAL 10 MINUTE) AS later;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+------------------------+
| original               | later                  |
+------------------------+------------------------+
| 2008-12-25T15:30:00Z   | 2008-12-25T15:40:00Z   |
+------------------------+------------------------+

TIMESTAMP_SUB

TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression date_part)

Descripción

Resta int64_expression unidades de date_part de la marca de tiempo, sin importar la zona horaria.

TIMESTAMP_SUB admite los siguientes valores para date_part:

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR. Equivalente a 60 MINUTE.
  • DAY. Equivalente a 24 HOUR.

Tipo de datos mostrados

TIMESTAMP

Ejemplo

SELECT
  TIMESTAMP("2008-12-25 15:30:00+00") AS original,
  TIMESTAMP_SUB(TIMESTAMP "2008-12-25 15:30:00+00", INTERVAL 10 MINUTE) AS earlier;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+------------------------+
| original               | earlier                |
+------------------------+------------------------+
| 2008-12-25T15:30:00Z   | 2008-12-25T15:20:00Z   |
+------------------------+------------------------+

TIMESTAMP_DIFF

TIMESTAMP_DIFF(timestamp_expression_a, timestamp_expression_b, date_part)

Descripción

Muestra el número de intervalos date_part completos especificados entre dos objetos TIMESTAMP (timestamp_expression_a y timestamp_expression_b). Si el primer TIMESTAMP es anterior al segundo, el resultado es negativo. Genera un error si el cálculo desborda el tipo de resultado, por ejemplo, si la diferencia en nanosegundos entre los dos objetos TIMESTAMP desbordaría un valor INT64.

TIMESTAMP_DIFF admite los siguientes valores para date_part:

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR. Equivalente a 60 MINUTE.
  • DAY. Equivalente a 24 HOUR.

Tipo de datos mostrados

INT64

Ejemplo

SELECT
  TIMESTAMP("2010-07-07 10:20:00+00") AS later_timestamp,
  TIMESTAMP("2008-12-25 15:30:00+00") AS earlier_timestamp,
  TIMESTAMP_DIFF(TIMESTAMP "2010-07-07 10:20:00+00", TIMESTAMP "2008-12-25 15:30:00+00", HOUR) AS hours;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+------------------------+-------+
| later_timestamp        | earlier_timestamp      | hours |
+------------------------+------------------------+-------+
| 2010-07-07T10:20:00Z   | 2008-12-25T15:30:00Z   | 13410 |
+------------------------+------------------------+-------+

En el siguiente ejemplo, la primera marca de tiempo se produce antes de la segunda marca de tiempo, lo que se traduce en un resultado negativo.

SELECT TIMESTAMP_DIFF(TIMESTAMP "2018-08-14", TIMESTAMP "2018-10-14", DAY);

+---------------+
| negative_diff |
+---------------+
| -61           |
+---------------+

En este ejemplo, el resultado es 0 porque solo se incluye el número de intervalos HOUR especificados.

SELECT TIMESTAMP_DIFF("2001-02-01 01:00:00", "2001-02-01 00:00:01", HOUR)

+---------------+
| negative_diff |
+---------------+
| 0             |
+---------------+

TIMESTAMP_TRUNC

TIMESTAMP_TRUNC(timestamp_expression, date_part[, time_zone])

Descripción

Trunca una marca de tiempo con el nivel de detalle de date_part.

TIMESTAMP_TRUNC admite los siguientes valores para date_part:

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • ISOWEEK: trunca timestamp_expression al límite de la semana ISO 8601 anterior. Los valores ISOWEEK comienzan el lunes. El primer ISOWEEK de cada año ISO contiene el primer jueves del año calendario gregoriano correspondiente. Cualquier valor date_expression anterior a esto se truncará al lunes anterior.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: trunca timestamp_expression al límite de año de numeración de la semana ISO 8601. El límite del año ISO es el lunes de la primera semana cuyo jueves pertenece al año calendario gregoriano correspondiente.

La función TIMESTAMP_TRUNC admite un parámetro opcional time_zone. Este parámetro se aplica a los siguientes valores date_parts:

  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR

Usa este parámetro si deseas usar una zona horaria distinta de la predeterminada, America/Los_Angeles, como parte de la operación para truncar.

Cuando se trunca un valor TIMESTAMP a MINUTE o HOUR, TIMESTAMP_TRUNC determina la hora civil de TIMESTAMP en la zona horaria especificada (o predeterminada) y resta los minutos y los segundos (cuando se trunca a HOUR) o los segundos (cuando se trunca a MINUTE) de TIMESTAMP. Si bien esto proporciona resultados intuitivos en la mayoría de los casos, el resultado es una transición no intuitiva cerca del horario de verano que no está alineada con la hora.

Tipo de datos mostrados

TIMESTAMP

Ejemplos

SELECT
  TIMESTAMP_TRUNC(TIMESTAMP "2008-12-25 15:30:00+00", DAY, "UTC") AS utc,
  TIMESTAMP_TRUNC(TIMESTAMP "2008-12-25 15:30:00+00", DAY, "America/Los_Angeles") AS la;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+------------------------+
| utc                    | la                     |
+------------------------+------------------------+
| 2008-12-25T00:00:00Z   | 2008-12-25T08:00:00Z   |
+------------------------+------------------------+

En el siguiente ejemplo, el valor timestamp_expression original está en el año calendario gregoriano 2015. Sin embargo, TIMESTAMP_TRUNC con la parte de fecha ISOYEAR trunca el valor timestamp_expression al comienzo del año ISO, no el año calendario gregoriano. El primer jueves del año calendario 2015 fue 2015-01-01, por lo que el año ISO 2015 comienza el lunes anterior, 2014-12-29. Por lo tanto, el límite del año ISO que precede al valor timestamp_expression 2015-06-15 00:00:00 + 00 es 2014-12-29.

SELECT
  TIMESTAMP_TRUNC("2015-06-15 00:00:00+00", ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM TIMESTAMP "2015-06-15 00:00:00+00") AS isoyear_number;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+----------------+
| parsed                 | isoyear_number |
+------------------------+----------------+
| 2014-12-29T08:00:00Z   | 2015           |
+------------------------+----------------+

FORMAT_TIMESTAMP

FORMAT_TIMESTAMP(format_string, timestamp[, time_zone])

Descripción

Formatea una marca de tiempo de acuerdo con el valor format_string especificado.

Consulta los elementos de formato admitidos para TIMESTAMP a fin de obtener una lista de los elementos de formato que admite esta función.

Tipo de datos mostrados

STRING

Ejemplo

SELECT FORMAT_TIMESTAMP("%c", TIMESTAMP "2008-12-25 15:30:00+00", "UTC") AS formatted;

+--------------------------+
| formatted                |
+--------------------------+
| Thu Dec 25 15:30:00 2008 |
+--------------------------+
SELECT FORMAT_TIMESTAMP("%b-%d-%Y", TIMESTAMP "2008-12-25 15:30:00+00") AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec-25-2008 |
+-------------+
SELECT FORMAT_TIMESTAMP("%b %Y", TIMESTAMP "2008-12-25 15:30:00+00")
  AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec 2008    |
+-------------+

PARSE_TIMESTAMP

PARSE_TIMESTAMP(format_string, string[, time_zone])

Descripción

Usa un valor format_string y una representación de string de una marca de tiempo para mostrar un objeto TIMESTAMP.

Cuando uses PARSE_TIMESTAMP, ten en cuenta lo siguiente:

  • Campos sin especificar. Todo campo sin especificar se inicializa desde 1970-01-01 00:00:00.0. Este valor de inicialización usa la zona horaria que especifica el argumento de la zona horaria de la función, si está presente. Si no es así, el valor de inicialización usa la zona horaria predeterminada, America/Los_Angeles. Por ejemplo, si no se especifica el año, el valor predeterminado es 1970, y así sucesivamente.
  • Nombres sin distinción entre mayúsculas y minúsculas. Los nombres, como Monday, February, etc., no distinguen entre mayúsculas y minúsculas.
  • Espacio en blanco. Uno o más espacios en blanco consecutivos en la string de formato no coinciden con ningún espacio en blanco consecutivo o coinciden con más espacios en blanco consecutivos en la string de marca de tiempo. Además, los espacios en blanco iniciales y finales en la string de marca de tiempo siempre están permitidos, incluso si no están en la string de formato.
  • Precedencia del formato. Cuando dos (o más) elementos de formato tienen información superpuesta (por ejemplo, tanto %F como %Y afectan el año), el último suele anular los anteriores, con algunas excepciones (consulta las descripciones de %s, %C y %y).

Tipo de datos mostrados

TIMESTAMP

Ejemplo

SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008") AS parsed;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+
| parsed                 |
+------------------------+
| 2008-12-25T15:30:00Z   |
+------------------------+

TIMESTAMP_SECONDS

TIMESTAMP_SECONDS(int64_expression)

Descripción

Interpreta int64_expression como la cantidad de segundos desde 1970-01-01 00:00:00 UTC.

Tipo de datos mostrados

TIMESTAMP

Ejemplo

SELECT TIMESTAMP_SECONDS(1230219000) AS timestamp_value;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+
| timestamp_value        |
+------------------------+
| 2008-12-25T15:30:00Z   |
+------------------------+

TIMESTAMP_MILLIS

TIMESTAMP_MILLIS(int64_expression)

Descripción

Interpreta int64_expression como el número de milisegundos desde 1970-01-01 00:00:00 UTC.

Tipo de datos mostrados

TIMESTAMP

Ejemplo

SELECT TIMESTAMP_MILLIS(1230219000000) AS timestamp_value;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+
| timestamp_value        |
+------------------------+
| 2008-12-25T15:30:00Z   |
+------------------------+

TIMESTAMP_MICROS

TIMESTAMP_MICROS(int64_expression)

Descripción

Interpreta int64_expression como la cantidad de microsegundos desde 1970-01-01 00:00:00 UTC.

Tipo de datos mostrados

TIMESTAMP

Ejemplo

SELECT TIMESTAMP_MICROS(1230219000000000) AS timestamp_value;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+
| timestamp_value        |
+------------------------+
| 2008-12-25T15:30:00Z   |
+------------------------+

UNIX_SECONDS

UNIX_SECONDS(timestamp_expression)

Descripción

Muestra el número de segundos desde 1970-01-01 00:00:00 UTC. Trunca los niveles más altos de precisión.

Tipo de datos mostrados

INT64

Ejemplo

SELECT UNIX_SECONDS(TIMESTAMP "2008-12-25 15:30:00+00") AS seconds;

+------------+
| seconds    |
+------------+
| 1230219000 |
+------------+

UNIX_MILLIS

UNIX_MILLIS(timestamp_expression)

Descripción

Muestra el número de milisegundos desde 1970-01-01 00:00:00 UTC. Trunca los niveles más altos de precisión.

Tipo de datos mostrados

INT64

Ejemplo

SELECT UNIX_MILLIS(TIMESTAMP "2008-12-25 15:30:00+00") AS millis;

+---------------+
| millis        |
+---------------+
| 1230219000000 |
+---------------+

UNIX_MICROS

UNIX_MICROS(timestamp_expression)

Descripción

Muestra el número de microsegundos desde 1970-01-01 00:00:00 UTC. Trunca los niveles más altos de precisión.

Tipo de datos mostrados

INT64

Ejemplo

SELECT UNIX_MICROS(TIMESTAMP "2008-12-25 15:30:00+00") AS micros;

+------------------+
| micros           |
+------------------+
| 1230219000000000 |
+------------------+

PENDING_COMMIT_TIMESTAMP

PENDING_COMMIT_TIMESTAMP()

Descripción

Usa la función PENDING_COMMIT_TIMESTAMP() en una declaración INSERT o UPDATE DML para escribir la marca de tiempo de confirmación pendiente, es decir, la marca de tiempo de confirmación de la escritura cuando se confirma, en una columna de tipo TIMESTAMP.

SQL de Cloud Spanner selecciona la marca de tiempo de confirmación cuando la transacción se confirma. La función PENDING_COMMIT_TIMESTAMP solo se puede usar como un valor para INSERT o UPDATE de una columna con el tipo especificado. No se puede usar en SELECT ni como una entrada para cualquier otra expresión escalar.

Tipo de datos mostrados

TIMESTAMP

Ejemplo

La siguiente declaración DML actualiza la columna LastUpdated en la tabla Singers con la marca de tiempo de confirmación.

UPDATE Performances SET LastUpdated = PENDING_COMMIT_TIMESTAMP()
   WHERE SingerId=1 AND VenueId=2 AND EventDate="2015-10-21"

Elementos de formato admitidos para TIMESTAMP

A menos que se indique lo contrario, las funciones de TIMESTAMP que usan strings de formato admiten los siguientes elementos:

Elemento de formato Descripción
%A El nombre completo del día de la semana
%a El nombre abreviado del día de la semana
%B El nombre completo del mes
%b o %h El nombre del mes abreviado
%C El siglo (un año dividido por 100 y truncado a un número entero) como un número decimal (00-99).
%c La representación de la fecha y la hora en el formato %a %b %e %T %Y.
%D La fecha en el formato %m/%d/%y
%d El día del mes como número decimal (01-31)
%e El día del mes como número decimal (1-31); los dígitos individuales están precedidos por un espacio
%F La fecha en el formato %Y-%m-%d
%G El año ISO 8601 con siglo como número decimal. Cada año ISO comienza el lunes anterior al primer jueves del año calendario gregoriano. Ten en cuenta que %G y %Y pueden producir resultados diferentes cerca de los límites del año gregoriano, en donde el año gregoriano y el año ISO pueden divergir.
%g El año ISO 8601 sin el siglo como número decimal (00-99). Cada año ISO comienza el lunes anterior al primer jueves del año calendario gregoriano. Ten en cuenta que %g y %y pueden producir resultados diferentes cerca de los límites del año gregoriano, en donde el año gregoriano y el año ISO pueden divergir.
%H La hora (formato de 24 horas) como número decimal (00-23).
%I La hora (formato de 12 horas) como número decimal (01-12).
%j El día del año como número decimal (001-366).
%k La hora (formato de 24 horas) como número decimal (0-23); los dígitos individuales están precedidos por un espacio.
%l La hora (formato de 12 horas) como número decimal (1-12); los dígitos individuales están precedidos por un espacio.
%M El minuto como número decimal (00-59).
%m El mes como número decimal (01-12)
%n Un carácter de salto de línea.
%P Ya sea a.m. o p.m.
%p Ya sea A.M. o P.M.
%R La hora en el formato %H:%M.
%r La hora en el formato de 12 horas con la notación A.M./P.M.
%S El segundo como número decimal (00-60).
%s El número de segundos desde 1970-01-01 00:00:00 UTC. Siempre anula todos los demás elementos de formato, sin importar dónde aparezca %s en la string. Si aparecen varios elementos %s, el último tiene prioridad.
%T La hora en el formato %H:%M:%S.
%t Un tabulador
%U El número de la semana del año (con el domingo como primer día de la semana) como número decimal (00-53)
%u El día de la semana (lunes como primer día de la semana) como número decimal (1-7)
%V El número de semana ISO 8601 del año (el lunes como el primer día de la semana) como un número decimal (01-53). Si la semana que contiene el 1 de enero tiene cuatro o más días en el año nuevo, entonces es la semana 1; de lo contrario, es la semana 53 del año anterior, y la próxima semana es la semana 1.
%W El número de la semana del año (con el lunes como primer día de la semana) como número decimal (00-53)
%w El día de la semana (domingo como primer día de la semana) como número decimal (0-6).
%X La representación de la hora en formato HH:MM:SS.
%x La representación de la fecha en formato MM/DD/YY
%Y El año con siglo como número decimal
%y El año sin siglo como número decimal (00-99), con un cero inicial opcional. Se puede combinar con %C. Si no se especifica %C, los años 00-68 son los años 2000, mientras que los años 69-99 son los 1900.
%Z El nombre de la zona horaria.
%z La compensación desde el primer meridiano en el formato +HHMM o -HHMM según corresponda, con valores positivos que representan ubicaciones al este de Greenwich.
%% Un solo carácter %.
%Ez Zona horaria numérica compatible con RFC 3339 (+HH:MM o -HH:MM).
%E#S Segundos con # dígitos de precisión fraccional.
%E*S Segundos con precisión fraccional completa (un literal '*').
%E4Y Los años de cuatro caracteres (0001 … 9999). Ten en cuenta que %Y produce tantos caracteres como sea necesario para procesar el año por completo.

Definiciones de la zona horaria

Ciertas funciones de fecha y marca de tiempo te permiten anular la zona horaria predeterminada y especificar una diferente. Puedes proporcionar el nombre de zona horaria para especificar una (por ejemplo, America/Los_Angeles) o el desplazamiento de zona horaria desde UTC (por ejemplo, -08).

Si decides usar un desplazamiento de zona horaria, aplica el siguiente formato:

(+|-)H[H][:M[M]]

Las siguientes marcas de tiempo son equivalentes porque el desplazamiento de la zona horaria de America/Los_Angeles es -08 en la fecha y hora especificadas.

SELECT UNIX_MILLIS(TIMESTAMP "2008-12-25 15:30:00 America/Los_Angeles") as millis;
SELECT UNIX_MILLIS(TIMESTAMP "2008-12-25 15:30:00-08:00") as millis;

Operadores

Los operadores se representan mediante caracteres especiales o palabras clave; no se usa la sintaxis de llamada a funciones. Un operador manipula cualquier cantidad de entradas de datos, también llamados operandos, y muestra un resultado.

Convenciones comunes:

  • A menos que se especifique lo contrario, todos los operadores muestran NULL cuando uno de los operandos es NULL.
  • Todos los operadores mostrarán un error si el cálculo arroja demasiados resultados.
  • Para todas las operaciones de punto flotante, +/-inf y NaN solo se pueden mostrar si uno de los operandos es +/-inf o NaN. En otros casos, se muestra un error.

En la siguiente tabla se enumeran todos los operadores de SQL de Cloud Spanner de mayor a menor prioridad, es decir, el orden en el que se evaluarán en una declaración.

Orden de prioridad Operador Tipos de datos de entrada Nombre Aridad del operador
1 . STRUCT
Operador de acceso de campo del miembro Binario
  [ ] ARRAY Posición del arreglo. Se debe usar con OFFSET o con ORDINAL; consulta Funciones de arreglo. Binario
2 - Todos los tipos numéricos Menos unario Unario
  ~ Número entero o BYTES NOT a nivel de bits Unario
3 * Todos los tipos numéricos Multiplicación Binario
  / Todos los tipos numéricos División Binario
  || STRING, BYTES o ARRAY <T> Operador de concatenación Binario
4 + Todos los tipos numéricos Suma Binario
  - Todos los tipos numéricos Resta Binario
5 << Número entero o BYTES Desplazamiento de bits a la izquierda Binario
  >> Número entero o BYTES Desplazamiento de bits a la derecha Binario
6 & Número entero o BYTES AND a nivel de bits Binario
7 ^ Número entero o BYTES XOR a nivel de bits Binario
8 | Número entero o BYTES OR a nivel de bits Binario
9 (Operadores de comparación) = Cualquier tipo comparable. Consulta Tipos de datos para ver una lista completa. Igual Binario
  < Cualquier tipo comparable. Consulta Tipos de datos para ver una lista completa. Menor que Binario
  > Cualquier tipo comparable. Consulta Tipos de datos para ver una lista completa. Mayor que Binario
  <= Cualquier tipo comparable. Consulta Tipos de datos para ver una lista completa. Menor o igual que Binario
  >= Cualquier tipo comparable. Consulta Tipos de datos para ver una lista completa. Mayor o igual que Binario
  !=, <> Cualquier tipo comparable. Consulta Tipos de datos para ver una lista completa. No igual Binario
  [NOT] LIKE STRING y byte El valor [no] coincide con el patrón especificado Binario
  [NOT] BETWEEN Cualquier tipo comparable. Consulta Tipos de datos para ver una lista completa. El valor [no] está dentro del rango especificado Binario
  [NOT] IN Cualquier tipo comparable. Consulta Tipos de datos para ver una lista completa. El valor [no] está en el conjunto de valores especificado Binario
  IS [NOT] NULL Todos El valor [no] es NULL Unario
  IS [NOT] TRUE BOOL El valor [no] es VERDADERO Unario
  IS [NOT] FALSE BOOL El valor [no] es FALSO Unario
10 NOT BOOL NOT lógico Unario
11 AND BOOL AND lógico Binario
12 OR BOOL OR lógico Binario

Los operadores con la misma prioridad son asociativos a la izquierda. Esto significa que esos operadores se agrupan juntos de izquierda a derecha. Por ejemplo, la expresión:

x AND y AND z

se interpreta como

( ( x AND y ) AND z )

La expresión:

x * y / z

se interpreta como:

( ( x * y ) / z )

Todos los operadores de comparación tienen la misma prioridad, pero no son asociativos. Por lo tanto, se requieren paréntesis para resolver la ambigüedad. Por ejemplo:

(x < y) IS FALSE

Operadores de acceso a elementos

Operador Sintaxis Tipos de datos de entrada Tipo de datos del resultado Descripción
. expression.fieldname1… STRUCT
Tipo T almacenado en fieldname1 Operador de punto. Puede usarse para acceder a campos anidados, por ejemplo, expression.fieldname1.fieldname2...
[ ] array_expression [position_keyword (int_expression ) ] Consulta las funciones de ARRAY Tipo T almacenado en ARRAY position_keyword es OFFSET o bien ORDINAL. Consulta Funciones de ARRAY para ver las dos funciones que usan este operador.

Operadores aritméticos

Todos los operadores aritméticos aceptan la entrada del tipo numérico T, y el tipo de resultado tiene el tipo T, a menos que se indique lo contrario en la siguiente descripción:

Nombre Sintaxis
Suma X + Y
Resta X - Y
Multiplicación X * Y
División X / Y
Menos unario - X

NOTA: Las operaciones de dividir por cero muestran un error. Para que se muestre un resultado diferente considera usar la funciones IEEE_DIVIDE o SAFE_DIVIDE.

Tipos de resultados para Suma y Multiplicación:

 INT64FLOAT64
INT64INT64FLOAT64
FLOAT64FLOAT64FLOAT64

Tipos de resultados para Resta:

 INT64FLOAT64
INT64INT64FLOAT64
FLOAT64FLOAT64FLOAT64

Tipos de resultados para División:

 INT64FLOAT64
INT64FLOAT64FLOAT64
FLOAT64FLOAT64FLOAT64

Tipos de resultados para Menos unario:

Tipo de datos de entrada Tipo de datos del resultado
INT64 INT64
FLOAT64 FLOAT64

Operadores a nivel de bits

Todos los operadores a nivel de bits muestran el mismo tipo y la misma longitud que el primer operando.

Nombre Sintaxis Tipo de datos de entrada Descripción
NOT a nivel de bits ~ X Número entero o BYTES Realiza una negación lógica en cada bit y forma el complemento a uno del valor binario dado.
OR a nivel de bits X | Y X: número entero o BYTES
Y: mismo tipo que X
Toma dos patrones de bits de igual longitud y realiza la operación OR inclusiva lógica en cada par de los bits correspondientes. Este operador muestra un error si Y y X son BYTES de diferentes longitudes.
XOR a nivel de bits X ^ Y X: número entero o BYTES
Y: mismo tipo que X
Toma dos patrones de bits de igual longitud y realiza la operación OR exclusiva lógica en cada par de los bits correspondientes. Este operador muestra un error si Y y X son BYTES de diferentes longitudes.
AND a nivel de bits X & Y X: número entero o BYTES
Y: mismo tipo que X
Toma dos patrones de bits de igual longitud y realiza la operación lógica AND en cada par de los bits correspondientes. Este operador muestra un error si Y y X son BYTES de diferentes longitudes.
Desplazamiento a la izquierda X << Y X: Número entero o BYTES
Y: INT64
Desplaza el primer operando X a la izquierda. Este operador muestra 0 o una secuencia de bytes de b'\x00' si el segundo operando Y es igual o mayor que el largo de bits del primer operando X (por ejemplo, 64 si X tiene el tipo INT64). Este operador muestra un error si Y es negativo.
Desplazamiento a la derecha X >> Y X: Número entero o BYTES
Y: INT64
Desplaza el primer operando X a la derecha. Este operador no realiza la extensión de signo con un tipo de signo (es decir, llena los bits vacantes de la izquierda con 0). Este operador muestra 0 o una secuencia de bytes de b'\x00' si el segundo operando Y es igual o mayor que el largo de bits del primer operando X (por ejemplo, 64 si X tiene el tipo INT64). Este operador muestra un error si Y es negativo.

Operadores lógicos

Todos los operadores lógicos permiten solo la entrada BOOL.

Nombre Sintaxis Descripción
NOT lógico NOT X Muestra FALSE si la entrada es TRUE. Muestra TRUE si la entrada es FALSE. De lo contrario, muestra NULL.
AND lógico X AND Y Muestra FALSE si al menos una entrada es FALSE. Muestra TRUE si tanto X como Y son TRUE. De lo contrario, muestra NULL.
OR lógico X OR Y Muestra FALSE si tanto X como Y son FALSE. Muestra TRUE si al menos una entrada es TRUE. De lo contrario, muestra NULL.

Operadores de comparación

Las comparaciones siempre muestran BOOL. Por lo general, las comparaciones requieren que ambos operandos sean del mismo tipo. Si los operandos son de tipos diferentes, y si SQL de Cloud Spanner puede convertir los valores de esos tipos en un tipo común sin pérdida de precisión, SQL de Cloud Spanner por lo general los forzará a ese tipo común para la comparación; SQL de Cloud Spanner generalmente forzará los literales al tipo de no literales, donde estén presentes. Los tipos de datos comparables se definen en Tipos de datos.

Los STRUCT solo admiten 4 operadores de comparación: igual (=), no igual (!= Y <>) y también IN.

Las siguientes reglas se aplican cuando se comparan estos tipos de datos:

  • FLOAT64: todas las comparaciones con NaN muestran FALSE, excepto != y <>, que muestran TRUE.
  • BOOL: FALSE es menor que TRUE.
  • STRING: las strings se comparan punto de código por punto de código, lo que significa que solo se garantiza que las strings canónicamente equivalentes se comparen como iguales si se normalizaron primero.
  • NULL: aquí se mantiene la convención; cualquier operación con una entrada NULL mostrará el valor NULL.
Nombre Sintaxis Descripción
Menor que X < Y Muestra TRUE si X es menor que Y.
Menor o igual que X <= Y Muestra TRUE si X es menor o igual que Y.
Mayor que X > Y Muestra TRUE si X es mayor que Y.
Mayor o igual que X >= Y Muestra TRUE si X es mayor o igual que Y.
Igual X = Y Muestra TRUE si X es igual que Y.
No igual X != Y
X <> Y
Muestra TRUE si X no es igual que Y.
BETWEEN X [NOT] BETWEEN Y AND Z Muestra TRUE si X [no] está dentro del rango especificado. El resultado de "X BETWEEN Y AND Z" es equivalente a "Y <= X AND X <= Z", pero X se evalúa solo una vez en el primer caso.
LIKE X [NOT] LIKE Y Comprueba si la STRING del primer operando X coincide con un patrón especificado por el segundo operando Y. Las expresiones pueden contener estos caracteres:
  • Un signo de porcentaje "%" coincide con cualquier número de caracteres o bytes
  • Un guion bajo "_" coincide con un solo carácter o byte
  • Puedes marcar "\", "_" o "%" con dos barras invertidas. Por ejemplo, "\\%". Si usas strings sin procesar, se requiere una sola barra invertida. Por ejemplo, r"\%".
IN Múltiple: ver a continuación Muestra FALSE si el operando derecho está vacío. Muestra NULL si el operando izquierdo es NULL. Muestra TRUE o NULL, nunca FALSE, si el operando derecho contiene NULL. Los argumentos a ambos lados de IN son expresiones generales. No se requiere que ninguno de los operandos sea literal, aunque lo más común es usar un literal del lado derecho. X se evalúa una sola vez.

Cuando se evalúan valores que tienen un tipo de datos STRUCT para determinar la igualdad, es posible que uno o más campos sea NULL. En esos casos:

  • Si todos los valores que no son NULL son iguales, la comparación muestra NULL.
  • Si el valor de algún campo no NULL no es igual, la comparación muestra FALSE.

En la siguiente tabla, se muestra cómo los tipos de datos STRUCT se comparan cuando tienen campos con 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

Operadores IN

El operador IN es compatible con las siguientes sintaxis:

x [NOT] IN (y, z, ... ) # Requires at least one element
x [NOT] IN (<subquery>)
x [NOT] IN UNNEST(<array expression>) # analysis error if the expression
                                      # does not return an ARRAY type.

Los argumentos a ambos lados del operador IN son expresiones generales. Es habitual el uso de literales en la expresión de la derecha; sin embargo, esto no es obligatorio.

La semántica de

x IN (y, z, ...)

se define como equivalente a

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

y las formas de subconsulta y arreglo se definen de forma similar.

x NOT IN ...

es equivalente a esto:

NOT(x IN ...)

El formulario UNNEST trata un análisis de arreglo como UNNEST en la cláusula FROM:

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

Este formulario se suele usar con parámetros ARRAY. Por ejemplo:

x IN UNNEST(@array_parameter)

Nota: Un ARRAY NULL se tratará de forma equivalente a un ARRAY vacío.

Consulta el tema arreglos para obtener información sobre cómo usar esta sintaxis.

Cuando se usa el operador IN, se aplica la siguiente semántica:

  • IN con una expresión vacía del lado derecho siempre es FALSE
  • IN con una expresión NULL del lado izquierdo y una expresión no vacía del lado derecho siempre es NULL
  • IN con NULL en la lista de IN solo puede mostrar TRUE o NULL, nunca FALSE
  • NULL IN (NULL) muestra NULL
  • IN UNNEST(<NULL array>) muestra FALSE (no NULL)
  • NOT IN con un NULL en la lista IN solo puede mostrar FALSE o NULL, nunca TRUE

IN se puede usar con claves de varias partes mediante la sintaxis del constructor de struct. Por ejemplo:

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

Consulta la sección Tipo Struct del tema Tipos de datos para obtener más información sobre la sintaxis.

Operadores IS

Los operadores IS muestran TRUE o FALSE para la condición que prueban. Nunca muestran NULL, ni siquiera para entradas NULL, a diferencia de la función IS_INF y la función IS_NAN que se definen en Funciones matemáticas. Si NOT está presente, el valor BOOL de salida se invierte.

Sintaxis de la función Tipo de datos de entrada Tipo de datos del resultado Descripción

X IS [NOT] NULL
Cualquier tipo de valor BOOL Muestra TRUE si el operando X se evalúa como NULL; de lo contrario, muestra FALSE.

X IS [NOT] TRUE
BOOL BOOL Muestra TRUE si el operando BOOL se evalúa como TRUE. De lo contrario, muestra FALSE.

X IS [NOT] FALSE
BOOL BOOL Muestra TRUE si el operando BOOL se evalúa como FALSE. De lo contrario, muestra FALSE.

Operador de concatenación

El operador de concatenación combina varios valores en uno.

Sintaxis de la función Tipo de datos de entrada Tipo de datos del resultado

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

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

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

Expresiones condicionales

Las expresiones condicionales imponen restricciones en el orden de evaluación de sus entradas. En síntesis, se evalúan de izquierda a derecha, con cortocircuito, y solo evalúan el valor de resultado elegido. Por el contrario, todas las entradas a las funciones normales se evalúan antes de llamar a la función. El cortocircuito en expresiones condicionales puede explotarse para el manejo de errores o el ajuste del rendimiento.

CASE expr

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

Descripción

Compara expr con expr_to_match de cada cláusula WHEN sucesiva y muestra el primer resultado en el que esta comparación sea verdadera. Las cláusulas WHEN restantes y else_result no se evalúan. Si la comparación expr = expr_to_match da como resultado “falso” o NULL en todas las cláusulas WHEN, muestra else_result si está presente; si no lo está, muestra NULL.

expr y expr_to_match pueden ser de cualquier tipo. Deben ser coercibles de forma implícita a un supertipo común; las comparaciones de igualdad se realizan en valores forzados. Puede haber varios tipos de result. Las expresiones result y else_result deben ser coercibles a un supertipo común.

Tipo de datos mostrados

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

Ejemplo

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

Descripción

Evalúa la condición de cada cláusula WHEN sucesiva y muestra el primer resultado en el que la condición sea verdadera; las cláusulas WHEN restantes y else_result no se evalúan. Si todas las condiciones son falsas o NULL, se muestra else_result si está presente; si no lo está, se muestra NULL.

condition debe ser una expresión booleana. Puede haber varios tipos de result. Las expresiones result y else_result deben ser coercibles de manera implícita en un supertipo común.

Tipo de datos mostrados

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

Ejemplo

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[, ...])

Descripción

Muestra el valor de la primera expresión no nula. Las expresiones restantes no se evalúan. Una expresión de entrada puede ser de cualquier tipo. Puede haber varios tipos de expresión de entrada. Todas las expresiones de entrada deben ser implícitamente coercibles a un supertipo común.

Tipo de datos mostrados

Supertipo de expr[, ...].

Ejemplos

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)

Descripción

Si expr es verdadero, se muestra true_result; de lo contrario, se muestra else_result. else_result no se evalúa si expr es verdadero. true_result no se evalúa si expr es falso o NULL.

expr debe ser una expresión booleana. true_result y else_result deben ser coercibles a un supertipo común.

Tipo de datos mostrados

Supertipo de true_result y else_result.

Ejemplo

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)

Descripción

Si expr es NULL, se muestra null_result. De lo contrario, muestra expr. Si expr no es NULL, no se evalúa null_result.

expr y null_result pueden ser cualquier tipo y deben ser coercibles de manera implícita a un supertipo común. Sinónimo de COALESCE(expr, null_result).

Tipo de datos mostrados

Supertipo de expr o null_result.

Ejemplos

SELECT IFNULL(NULL, 0) as result

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

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

NULLIF

NULLIF(expr, expr_to_match)

Descripción

Se muestra NULL si expr = expr_to_match es verdadero; de lo contrario, se muestra expr.

expr y expr_to_match deben ser coercibles de manera implícita a un supertipo común y deben ser comparables.

NULLIF no admite tipos STRUCT.

Tipo de datos mostrados

Supertipo de expr y expr_to_match.

Ejemplo

SELECT NULLIF(0, 0) as result

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

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

Subconsultas de expresión

Hay cuatro tipos de subconsultas de expresión (es decir, subconsultas que se usan como expresiones). Las subconsultas de expresión muestran NULL o un solo valor (no una columna o tabla) y deben estar entre paréntesis. Para ver un análisis más completo de las subconsultas, ve a Subconsultas.

Tipo de subconsulta Tipo de datos del resultado Descripción
Escalar Cualquier tipo T Una subconsulta entre paréntesis dentro de una expresión (p. ej., en la lista SELECT o la cláusula WHERE) se interpreta como una subconsulta escalar. La lista SELECT de una subconsulta escalar debe tener solo un campo. Si la subconsulta muestra exactamente una fila, ese único valor es el resultado de la subconsulta escalar. Si la subconsulta muestra cero filas, el valor de la subconsulta escalar es NULL. Si la subconsulta muestra más de una fila, la consulta genera un error de tiempo de ejecución. Cuando la subconsulta se escribe con SELECT AS STRUCT, puede incluir varias columnas y el valor mostrado será el STRUCT construido. Seleccionar varias columnas sin usar SELECT AS es un error.
ARRAY ARRAY Se puede usar SELECT AS STRUCT para crear arreglos de structs; a su vez, seleccionar varias columnas sin usar SELECT AS es un error. Muestra un ARRAY vacío si el resultado de la subconsulta son cero filas. Nunca muestra un ARRAY NULL.
IN BOOL Ocurre en una expresión que sigue al operador IN. La subconsulta debe producir una sola columna cuyo tipo admita la comparación de igualdad con la expresión que está en el lado izquierdo del operador IN. El resultado es FALSE si la subconsulta muestra cero filas. x IN () es equivalente a x IN (value, value, ...). Consulta el operador IN en Operadores de comparación para obtener la semántica completa.
EXISTS BOOL Muestra TRUE si la subconsulta produce una o más filas. Muestra FALSE si la subconsulta produce cero filas. Nunca muestra NULL. A diferencia de todas las demás subconsultas de expresión, no hay reglas que se apliquen a la lista de columnas. Se puede seleccionar cualquier número de columnas sin que esto afecte el resultado de la consulta.

Ejemplos

En los siguientes ejemplos de subconsultas de expresión, se supone que t.int_array tiene el tipo ARRAY<INT64>.

Tipo Subconsulta Tipo de datos del resultado Notas
Escalar (SELECT COUNT(*) FROM t.int_array) INT64  
(SELECT DISTINCT i FROM t.int_array i) INT64, posible error de entorno de ejecución  
(SELECT i FROM t.int_array i WHERE i=5) INT64, posible error de entorno de ejecución  
(SELECT ARRAY_AGG(i) FROM t.int_array i) ARRAY Utiliza la función de agregación ARRAY_AGG para mostrar un ARRAY.
(SELECT 'xxx' a) STRING  
(SELECT 'xxx' a, 123 b) Error Muestra un error porque hay más de una columna.
(SELECT AS STRUCT 'xxx' a, 123 b) STRUCT  
(SELECT AS STRUCT 'xxx' a) STRUCT  
ARRAY ARRAY(SELECT COUNT(*) FROM t.int_array) ARRAY de tamaño 1  
ARRAY(SELECT x FROM t) ARRAY  
ARRAY(SELECT 5 a, COUNT(*) b FROM t.int_array) Error Muestra un error porque hay más de una columna.
ARRAY(SELECT AS STRUCT 5 a, COUNT(*) b FROM t.int_array) ARRAY  
ARRAY(SELECT AS STRUCT i FROM t.int_array i) ARRAY Genera un ARRAY de STRUCT de un campo.
ARRAY(SELECT AS STRUCT 1 x, 2, 3 x) ARRAY Muestra un ARRAY de STRUCT con campos anónimos o duplicados.
ARRAY(SELECT AS TypeName SUM(x) a, SUM(y) b, SUM(z) c from t) array<TypeName> Selección de un tipo con nombre. Supone que TypeName es un tipo de STRUCT con los campos a, b y c.
STRUCT (SELECT AS STRUCT 1 x, 2, 3 x) STRUCT Construye un STRUCT con campos anónimos o duplicados.
EXISTS EXISTS(SELECT x,y,z FROM table WHERE y=z) BOOL  
NOT EXISTS(SELECT x,y,z FROM table WHERE y=z) BOOL  
IN x IN (SELECT y FROM table WHERE z) BOOL  
x NOT IN (SELECT y FROM table WHERE z) BOOL  

Funciones de depuración

SQL de Cloud Spanner admite las siguientes funciones de depuración.

ERROR

ERROR(error_message)

Descripción

Muestra un error. El argumento error_message es un STRING.

SQL de Cloud Spanner trata ERROR de la misma manera que cualquier expresión que pueda generar un error: no existe una garantía especial de orden de evaluación.

Tipo de datos mostrados

SQL de Cloud Spanner infiere el tipo de datos que se muestra en contexto.

Ejemplos

En el siguiente ejemplo, la consulta muestra un mensaje de error si el valor de la fila no coincide con uno de los dos valores definidos.

SELECT
  CASE
    WHEN value = 'foo' THEN 'Value is foo.'
    WHEN value = 'bar' THEN 'Value is bar.'
    ELSE ERROR(concat('Found unexpected value: ', value))
  END AS new_value
FROM (
  SELECT 'foo' AS value UNION ALL
  SELECT 'bar' AS value UNION ALL
  SELECT 'baz' AS value);

Found unexpected value: baz

En el siguiente ejemplo, SQL de Cloud Spanner puede evaluar la función ERROR antes o después de la condición x > 0, porque SQL de Cloud Spanner generalmente no ofrece garantías de pedido entre condiciones de la cláusula WHERE, y no hay garantías especiales para la función ERROR.

SELECT *
FROM (SELECT -1 AS x)
WHERE x > 0 AND ERROR('Example error');

En el siguiente ejemplo, la cláusula WHERE evalúa una condición IF, que garantiza que SQL de Cloud Spanner solo evalúe la función ERROR si la condición falla.

SELECT *
FROM (SELECT -1 AS x)
WHERE IF(x > 0, true, ERROR(FORMAT('Error: x must be positive but is %t', x)));'

Error: x must be positive but is -1