Trabaja con la intercalación

Organízate con las colecciones Guarda y clasifica el contenido según tus preferencias.

SQL estándar de Google para BigQuery admite la intercalación. Puedes obtener más información sobre la intercalación en este tema.

Acerca de la intercalación

La intercalación determina cómo se ordenan y comparan las strings en operaciones compatibles con la intercalación. Si deseas personalizar la intercalación de una operación compatible con la intercalación, debes asignar una especificación de la intercalación a una string en la operación como mínimo. Algunas operaciones no pueden usar la intercalación, pero pueden pasar la intercalación a través de ellas.

Operaciones afectadas por la intercalación

Cuando una operación se ve afectada por la intercalación, esto significa que la operación tiene en cuenta la intercalación durante la operación. Estas operaciones de consulta se ven afectadas por la intercalación cuando se ordenan y comparan strings:

Operations Notas
Operaciones de comparación compatibles con la intercalación
Operaciones de unión
ORDER BY En la cláusula WINDOW, ORDER BY no es compatible con la intercalación.
GROUP BY
WINDOW para funciones analíticas En la cláusula WINDOW, ORDER BY y PARTITION BY no son compatibles con la intercalación.
Funciones escalares compatibles con la intercalación
Funciones de agregación compatibles con la intercalación
Operaciones Set

Operaciones que propagan la intercalación

La intercalación puede pasar algunas operaciones de consulta a otras partes de una consulta. Cuando la intercalación pasa por una operación en una consulta, esto se conoce como propagación. Durante la propagación:

  • Si una entrada no contiene una especificación de la intercalación o contiene una especificación de intercalación vacía y otra entrada contiene una intercalación definida de forma explícita, la intercalación definida de forma explícita se usa para todas las entradas.
  • Todas las entradas con una especificación de intercalación definida de forma explícita y no vacía deben tener el mismo tipo de especificación de a intercalación; de lo contrario, se arroja un error.

SQL estándar de Google tiene varias funciones, operadores y expresiones que pueden propagar la intercalación.

En el siguiente ejemplo, la especificación de la intercalación 'und:ci' se propaga de la columna character a la operación ORDER BY.

-- With collation
SELECT *
FROM UNNEST([
  COLLATE('B', 'und:ci'),
  'b',
  'a'
]) AS character
ORDER BY character

+-----------+
| character |
+-----------+
| a         |
| B         |
| b         |
+-----------+
-- Without collation
SELECT *
FROM UNNEST([
  'B',
  'b',
  'a'
]) AS character
ORDER BY character

+-----------+
| character |
+-----------+
| B         |
| a         |
| b         |
+-----------+

Functions

Estas funciones permiten que la intercalación se propague a través de ellas:

Función Notas
AEAD.DECRYPT_STRING
ANY_VALUE
ARRAY_AGG La intercalación en los argumentos de entrada se propaga como intercalación en el elemento del array.
ARRAY_TO_STRING La intercalación en los elementos del array se propaga al resultado.
COLLATE
CONCAT
FORMAT Se propaga la intercalación de format_string a la string que se muestra.
FORMAT_DATE Se propaga la intercalación de format_string a la string que se muestra.
FORMAT_DATETIME Se propaga la intercalación de format_string a la string que se muestra.
FORMAT_TIME Se propaga la intercalación de format_string a la string que se muestra.
FORMAT_TIMESTAMP Se propaga la intercalación de format_string a la string que se muestra.
GREATEST
LAG
LEAD
LEAST
LEFT
LOWER
LPAD
MAX
MIN
NET.HOST
NET.PUBLIC_SUFFIX
NET.REG_DOMAIN
NORMALIZE
NORMALIZE_AND_CASEFOLD
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
SOUNDEX
SPLIT La intercalación en los argumentos de entrada se propaga como intercalación en el elemento del array.
STRING_AGG
SUBSTR
UPPER

Operadores

Estos operadores permiten que la intercalación se propague a través de ellos:

Operador Notas
Operador de concatenación ||
Operador subíndice de array Se propaga al resultado.
Configurar operadores La intercalación de una columna de resultado se decide mediante las intercalaciones de las columnas de entrada en la misma posición.
Operador de acceso de campos STRUCT Cuando se obtiene STRUCT, la intercalación en el campo STRUCT se propaga como la intercalación de salida.
UNNEST La intercalación en el elemento del array de entrada se propaga al resultado.

Expresiones

Estas expresiones permiten la intercalación a través de ellas:

Expresión Notas
ARRAY Cuando construyes ARRAY, la intercalación en los argumentos de entrada se propaga en los elementos en ARRAY.
CASE
CASE expr
COALESCE
IF
IFNULL
NULLIF
STRUCT Cuando construyes STRUCT, la intercalación de los argumentos de entrada se propaga en los campos de STRUCT.

Dónde puedes asignar una especificación de intercalación

Se puede asignar una especificación de la intercalación a estos tipos compatibles con la intercalación:

  • A STRING
  • Un campo STRING en STRUCT
  • Un elemento STRING en ARRAY

Además, ten en cuenta lo siguiente:

  • Puedes asignar una especificación de la intercalación predeterminada a un esquema cuando la creas o modificas. Esto asigna una especificación de la intercalación predeterminada a todas las tablas futuras que se agregan al esquema si las tablas no tienen sus propias especificaciones de intercalación predeterminadas.
  • Puedes asignar una especificación de la intercalación predeterminada a una tabla cuando la creas o modificas. Esto asigna una especificación de la intercalación a todas las columnas futuras compatibles con la intercalación que se agreguen a la tabla si las columnas no tienen especificaciones de intercalación. Esto anula una especificación de la intercalación predeterminada en un esquema.
  • Puedes asignar una especificación de la intercalación a un tipo compatible con la intercalación en una columna. Una columna que contiene un tipo compatible con la intercalación en su esquema de columna es una columna compatible con la intercalación. Esto anula una especificación de la intercalación predeterminada en una tabla.
  • Puedes asignar una especificación de la intercalación a una operación de consulta compatible con la intercalación.
  • Puedes asignar una especificación de la intercalación a una expresión compatible con la intercalación con la función COLLATE. Esto anula las especificaciones de intercalación establecidas con anterioridad.

Resumen:

Puedes definir una especificación de la intercalación predeterminada para un esquema. Por ejemplo:

CREATE SCHEMA (...)
DEFAULT COLLATE 'und:ci'

Puedes definir una especificación de la intercalación predeterminada para una tabla. Por ejemplo:

CREATE TABLE (...)
DEFAULT COLLATE 'und:ci'

Puedes definir una especificación de la intercalación para una columna compatible con la intercalación. Por ejemplo:

CREATE TABLE (
  case_insensitive_column STRING COLLATE 'und:ci'
)

Puedes especificar una especificación de la intercalación para una expresión compatible con la intercalación con la función COLLATE. Por ejemplo:

SELECT COLLATE('a', 'und:ci') AS character

Declaraciones DDL

Location Asistencia Notas
Schema CREATE SCHEMA Crea un esquema y, de forma opcional, agrega una especificación de la intercalación predeterminada al esquema.
Schema ALTER SCHEMA Actualiza la especificación de la intercalación predeterminada para un esquema.
Tabla CREATE TABLE Crea una tabla y, de forma opcional, agrega una especificación de la intercalación predeterminada a una tabla o una especificación de la intercalación a un tipo compatible con la intercalación en una columna.

No puedes tener intercalación en una columna que se usa con CLUSTERING.

Puedes usar funciones compatibles con intercalaciones con AS SELECT cuando creas una tabla.
Tabla ALTER TABLE Actualiza la especificación de la intercalación predeterminada para el tipo compatible con la intercalación en una tabla.
Columna ADD COLUMN Agrega una especificación de la intercalación a un tipo compatible con la intercalación en una columna nueva de una tabla existente.

Tipos de datos

Tipo Asistencia Notas
Data type STRING Puedes aplicar una especificación de la intercalación directamente a este tipo de datos.
Data type STRUCT Puedes aplicar una especificación de la intercalación a un campo STRING en STRUCT. Una STRUCT puede tener campos STRING con diferentes especificaciones de intercalación.
Data type ARRAY Puedes aplicar una especificación de la intercalación a un elemento STRING en ARRAY. Una ARRAY puede tener elementos STRING con diferentes especificaciones de intercalación.

Usa la función COLLATE para aplicar una especificación de la intercalación a las expresiones compatibles con la intercalación.

Funciones, operadores y expresiones condicionales

Functions

Tipo Asistencia Notas
Escalar COLLATE
Escalar ENDS_WITH
Escalar GREATEST
Escalar INSTR
Escalar LEAST
Escalar REPLACE
Escalar SPLIT
Escalar STARTS_WITH
Escalar STRPOS
Aggregate COUNT Este operador solo se ve afectado por la intercalación cuando la entrada incluye el argumento DISTINCT.
Aggregate MAX
Aggregate MIN

Operadores

Asistencia Notas
<
<=
>
>=
=
!=
[NOT] BETWEEN
[NOT] IN Por lo general, este operador admite la intercalación, sin embargo, no se admite [NOT] IN UNNEST. Si se usa con una lista, requiere al menos un elemento en la lista.
[NOT] IN

Expresiones condicionales

Asistencia
CASE
CASE expr
NULLIF

Las operaciones anteriores compatibles con la intercalación (funciones, operadores y expresiones condicionales) pueden incluir entradas con especificaciones de intercalación definidas de forma explícita para tipos compatibles con la intercalación. En una operación compatible con la intercalación, sucede lo siguiente:

  • Todas las entradas con una especificación de intercalación definida de forma explícita y no vacía deben ser iguales; de lo contrario, se arroja un error.
  • Si una entrada no contiene una intercalación definida de manera explícita y otra entrada contiene una intercalación definida de forma explícita, la intercalación definida de forma explícita se usa para ambos.

Por ejemplo:

-- Assume there is a table with this column declaration:
CREATE TABLE table_a
(
    col_a STRING COLLATE 'und:ci',
    col_b STRING COLLATE '',
    col_c STRING,
    col_d STRING COLLATE 'und:ci'
);

-- This runs. Column 'b' has a collation specification and the
-- column 'c' does not.
SELECT STARTS_WITH(col_b_expression, col_c_expression)
FROM table_a;

-- This runs. Column 'a' and 'd' have the same collation specification.
SELECT STARTS_WITH(col_a_expression, col_d_expression)
FROM table_a;

-- This runs. Even though column 'a' and 'b' have different
-- collation specifications, column 'b' is considered the default collation
-- because it's assigned to an empty collation specification.
SELECT STARTS_WITH(col_a_expression, col_b_expression)
FROM table_a;

-- This works. Even though column 'a' and 'b' have different
-- collation specifications, column 'b' is updated to use the same
-- collation specification as column 'a'.
SELECT STARTS_WITH(col_a_expression, COLLATE(col_b_expression, 'und:ci'))
FROM table_a;

-- This runs. Column 'c' does not have a collation specification, so it uses the
-- collation specification of column 'd'.
SELECT STARTS_WITH(col_c_expression, col_d_expression)
FROM table_a;

Detalles de la especificación de intercalación

Una especificación de la intercalación determina cómo se ordenan y comparan las strings en operaciones compatibles con la intercalación. Puedes definir la especificación de la intercalación Unicode, und:ci, para los tipos compatibles con la intercalación.

Si una especificación de la intercalación no está definida, se usa la especificación de la intercalación predeterminada. Para obtener más información, consulta la siguiente sección.

Especificación de la intercalación predeterminada

Cuando una especificación de la intercalación no se asigna o está vacía, se usa la intercalación 'binary'. La intercalación de objetos binarios indica que la operación debe mostrar los datos en orden de puntos de código Unicode. No puedes establecer la intercalación de objetos binarios de forma explícita.

Especificación de la intercalación Unicode

collation_specification:
  'language_tag:collation_attribute'

Una especificación de la intercalación Unicode indica que la operación debe usar el algoritmo de intercalación Unicode para ordenar y comparar strings. La especificación de la intercalación puede ser un literal STRING o un parámetro de búsqueda.

La etiqueta de idioma

La etiqueta de idioma determina cómo se ordenan y comparan, por lo general, las strings. Los valores permitidos para language_tag son los siguientes:

  • und: una string de configuración regional que representa la configuración regional indeterminada. und es una etiqueta de idioma especial definida en el registro de subetiquetas de idioma de IANA y se usa para indicar una configuración regional indeterminada. Esto también se conoce como configuración regional raíz y se puede considerar la intercalación Unicode predeterminada. Define una intercalación razonable independiente de la configuración regional.

El atributo de intercalación

Además de la etiqueta de idioma, la especificación de la intercalación Unicode debe tener collation_attribute, que habilita reglas adicionales para ordenar y comparar strings. Los valores permitidos son los siguientes:

  • ci: la intercalación no distingue mayúsculas de minúsculas.

Ejemplo de especificación de intercalación

Así se ve el atributo de intercalación ci cuando se usa con la etiqueta de idioma und en la función COLLATE:

COLLATE('orange1', 'und:ci')

Advertencias

  • Las diferentes strings se pueden considerar iguales. Por ejemplo, (LATIN CAPITAL LETTER SHARP S) se considera igual a 'SS' en el nivel principal 'ẞ1' < 'SS2'. Esto es similar a la forma en que funciona cuando no se distingue mayúsculas de minúsculas.
  • Existe una amplia variedad de puntos de código Unicode (puntuación, símbolos, etc.) que se tratan como si no estuvieran allí. Por lo tanto, las strings con y sin ellos se ordenan de manera idéntica. Por ejemplo, el punto de código de control de formato U+2060 se ignora cuando se ordenan las siguientes strings:

    SELECT *
    FROM UNNEST([
      COLLATE('oran\u2060ge1', 'und:ci'),
      COLLATE('\u2060orange2', 'und:ci'),
      COLLATE('orange3', 'und:ci')
    ]) AS fruit
    ORDER BY fruit
    
    +---------+
    | fruit   |
    +---------+
    | orange1 |
    | orange2 |
    | orange3 |
    +---------+
    
  • El orden puede cambiar. La especificación de Unicode de la intercalación und puede cambiar de vez en cuando, lo que puede afectar el orden de clasificación.

Limitaciones

Las limitaciones de las funciones compatibles se capturan en las secciones anteriores, pero aquí hay algunas limitaciones generales que debes tener en cuenta:

  • Se admiten und:ci y la intercalación vacía, pero no otras especificaciones de intercalación.
  • Las operaciones y las funciones que no admiten la intercalación producen un error si se encuentran valores recopilados.
  • No puedes establecer una intercalación no vacía en un campo de agrupamiento en clústeres mediante una llamada a la API o una consulta de DDL.

    CREATE TABLE mydataset.mytable
    (
      word STRING COLLATE 'und:ci',
      number INT64
    )
    CLUSTER BY word;
    
    -- User error:
    -- “CLUSTER BY STRING column word with
    -- collation und:ci is not supported"
    
  • No se admite una vista materializada con columnas de resultados recopiladas.

    CREATE MATERIALIZED VIEW bigquery.public.data.samples.shakespeare
    AS SELECT COLLATE('abc', 'und:ci') x;
    
    -- User error:
    -- “Creating MATERIALIZED VIEW with collation
    -- on the output column is not supported”
    
  • No se admite una vista con columnas de resultados recopiladas.

    CREATE VIEW bigquery.public.data.samples.shakespeare
    AS SELECT COLLATE('abc', 'und:ci') x;
    
    -- User error:
    -- "Creating VIEW with collation on the output column
    -- is not supported"
    
  • Después de que se crea una vista o una vista materializada, y se modifica una intercalación de columna de la tabla base para que la columna se recopile, la vista o la vista materializada se invalida y no se puede consultar.

    CREATE TABLE dataset.base (
      col_no_collation STRING,
      col INT64
    )
    AS SELECT 'a', 1
    UNION ALL SELECT 'b', 2
    UNION ALL SELECT 'A', 3
    UNION ALL SELECT 'B', 4;
    
    CREATE VIEW/MATERIALIZED VIEW dataset.view AS SELECT * FROM dataset.base;
    
    -- Change the collation of string column of the base table so
    -- that the string column of view/materialized view is collated.
    ALTER TABLE dataset.base ALTER COLUMN col_no_collation SET DATA TYPE STRING COLLATE 'und:ci';
    
    -- Should return an error since the column of view/materialized
    -- contains collation.
    SELECT * FROM dataset.view ORDER BY col;
    
    -- User error:
    -- "Collation on the output column for view dataset.view is
    -- not supported"
    
  • No puedes crear una vista materializada con claves de orden recopiladas en una función agregada.

    CREATE TABLE dataset.base (
      col_no_collation STRING,
      col INT64
    )
    AS SELECT 'a', 1
    UNION ALL SELECT 'b', 2
    UNION ALL SELECT 'A', 3
    UNION ALL SELECT 'B', 4;
    
    CREATE VIEW/MATERIALIZED VIEW dataset.view AS SELECT * FROM dataset.base;
    
    -- Change the collation of string column of the base table so
    -- that the string column of view/materialized view is
    -- collated.
    ALTER TABLE dataset.base ALTER COLUMN col_no_collation SET DATA TYPE STRING COLLATE 'und:ci';
    
    -- Should return an error since the column of view/materialized
    -- contains collation.
    SELECT * FROM dataset.view ORDER BY col;
    
    -- User error:
    -- "Collation on the output column for view dataset.view is
    -- not supported"
    
  • No se admite una función de tabla con columnas de resultados recopiladas.

    CREATE TABLE FUNCTION bigquery.public.data.samples.shakespeare()
    AS SELECT COLLATE('abc', 'und:ci') x;
    
    -- User error:
    -- "Creating TABLE FUNCTION with collation on the output
    -- column is not supported"
    
  • Las funciones definidas por el usuario (UDF) no pueden tomar argumentos recopilados.

    CREATE FUNCTION tmp_dataset.f(x STRING) AS (x);
    
    SELECT tmp_dataset.f(col_ci)
    FROM shared_dataset.table_collation_simple
    
    -- User error:
    -- “Collation is not allowed on argument x ("und:ci").
    -- Use COLLATE(arg, '') to remove collation at [1:8]”
    
  • Se producirá un error si una consulta SELECT con una tabla de destino especificada por el usuario produce columnas de resultado con intercalación.

    -- The following query returns an error since the resulting schema of
    -- the query has a column string_ci with collation ‘und:ci’.
    SELECT string_ci FROM collated_table
    
  • BigQuery BI Engine no es compatible con tablas con intercalación o la función COLLATE.