Trabajar con matrices

En Cloud Spanner SQL, una matriz es una lista ordenada con algún valor o ninguno del mismo tipo de datos. Puedes crear matrices de tipos de datos simples, como INT64, y tipos de datos complejos, como STRUCT. La excepción actual es el tipo de datos ARRAY: las matrices de matrices no son compatibles.

Con Cloud Spanner SQL, puedes crear literales de matriz, crear matrices a partir de subconsultas mediante la función ARRAY y acumular valores en una matriz mediante la función ARRAY_AGG.

Crear matrices

Usar literales de matriz

Puedes crear un literal de matriz en Cloud Spanner SQL mediante corchetes ([ y ]). Los elementos de una matriz se separan por comas.

SELECT [1, 2, 3] as numbers;

SELECT ["apple", "pear", "orange"] as fruit;

SELECT [true, false, true] as booleans;

También puedes crear matrices a partir de expresiones que tengan tipos compatibles. Por ejemplo:

SELECT [a, b, c]
FROM
  (SELECT 5 AS a,
          37 AS b,
          406 AS c);

SELECT [a, b, c]
FROM
  (SELECT CAST(5 AS INT64) AS a,
          CAST(37 AS FLOAT64) AS b,
          406 AS c);

Observa que el segundo ejemplo contiene tres expresiones: una que devuelve INT64, una que devuelve FLOAT64 y una que declara un literal. Esta expresión funciona porque las tres tienen FLOAT64 como supertipo común.

Para declarar un tipo de datos específico para una matriz, utiliza corchetes angulares (< y >). Por ejemplo:

SELECT ARRAY<FLOAT64>[1, 2, 3] as floats;

Las matrices de la mayoría de los tipos de datos, como INT64 o STRING, no necesitan que las declares antes.

SELECT [1, 2, 3] as numbers;

Puedes escribir una matriz vacía de un tipo específico mediante ARRAY<type>[]. También puedes escribir una matriz vacía sin tipo mediante [], en cuyo caso, Cloud Spanner SQL intenta deducir el tipo de matriz del contexto de alrededor. Si Cloud Spanner SQL no puede deducir un tipo, se utilizará el predeterminado ARRAY<INT64>.

Acceder a elementos de matriz

Observa la tabla siguiente, sequences:

CREATE TABLE sequences (
  id INT64 NOT NULL,
  some_numbers ARRAY<INT64> NOT NULL
) PRIMARY KEY(id);

Supón que la tabla se rellena con las filas siguientes:

+----+---------------------+
| id | some_numbers        |
+----+---------------------+
| 1  | [0, 1, 1, 2, 3, 5]  |
| 2  | [2, 4, 8, 16, 32]   |
| 3  | [5, 10]             |
+----+---------------------+

Esta tabla contiene la columna some_numbers del tipo de datos ARRAY. Para acceder a los elementos de las matrices en esta columna, debes especificar qué tipo de indexación vas a utilizar: OFFSET, para índices basados en cero o ORDINAL, para índices basados en uno.

En esta consulta puedes ver cómo se utilizan OFFSET() y ORDINAL():

SELECT some_numbers,
       some_numbers[OFFSET(1)] AS offset_1,
       some_numbers[ORDINAL(1)] AS ordinal_1
FROM sequences;

+---------------+----------+-----------+
| some_numbers  | offset_1 | ordinal_1 |
+---------------+----------+-----------+
| [0,1,1,2,3,5] |        1 |         0 |
+---------------+----------+-----------+
| [2,4,8,16,32] |        4 |         2 |
+---------------+----------+-----------+
| [5,10]        |       10 |         5 |
+---------------+----------+-----------+

Búsqueda de longitudes

La función ARRAY_LENGTH() devuelve la longitud de una matriz.

Observa la siguiente consulta de ejemplo con la misma definición de la tabla sequences que antes y las mismas filas de ejemplo:

SELECT some_numbers,
       ARRAY_LENGTH(some_numbers) AS len
FROM sequences;

+---------------+------+
| some_numbers  | len  |
+---------------+------+
| [0,1,1,2,3,5] |    6 |
+---------------+------+
| [2,4,8,16,32] |    5 |
+---------------+------+
| [5,10]        |    2 |
+---------------+------+

Aplanar matrices

Para convertir una ARRAY en un conjunto de filas, proceso denominado "aplanamiento", utiliza el operador UNNEST. UNNEST toma una ARRAY y devuelve una tabla con una sola fila para cada elemento en la ARRAY.

Como UNNEST arrasa con el orden de los elementos de ARRAY, podría interesarte restaurar el orden de la tabla. Para ello, utiliza la cláusula WITH OFFSET para devolver una columna adicional con el desfase de cada elemento de matriz y, después, utiliza la cláusula ORDER BY para ordenar las filas según su desfase.

Ejemplo

SELECT *
FROM UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'])
  AS element
WITH OFFSET AS offset
ORDER BY offset;

+----------+--------+
| element  | offset |
+----------+--------+
| foo      | 0      |
| bar      | 1      |
| baz      | 2      |
| qux      | 3      |
| corge    | 4      |
| garply   | 5      |
| waldo    | 6      |
| fred     | 7      |
+----------+--------+

Para aplanar una columna entera de ARRAYs mientras se conservan los valores de las demás columnas en cada fila, utiliza una CROSS JOIN para unir la tabla que contiene la columna ARRAY con el resultado UNNEST de dicha columna ARRAY.

Se trata de una unión cruzada y correlacionada: el operador UNNEST hace referencia a la columna de ARRAYs de cada fila en la tabla de origen, que aparece previamente en la cláusula FROM. Para cada fila N en la tabla de origen, UNNEST aplana la ARRAY desde la fila N en un conjunto de filas que contienen los elementos de ARRAY. Después, la CROSS JOIN une este nuevo conjunto de filas con la fila única N desde la tabla de origen.

Ejemplo

En el ejemplo siguiente se utiliza UNNEST para devolver una fila para cada elemento en la columna de matrices. Debido a la CROSS JOIN, la columna id contiene los valores de id de la fila en sequences que contiene cada número.

SELECT id, flattened_numbers FROM sequences
CROSS JOIN UNNEST(sequences.some_numbers) AS flattened_numbers;

+------+-------------------+
| id   | flattened_numbers |
+------+-------------------+
|    1 |                 0 |
|    1 |                 1 |
|    1 |                 1 |
|    1 |                 2 |
|    1 |                 3 |
|    1 |                 5 |
|    2 |                 2 |
|    2 |                 4 |
|    2 |                 8 |
|    2 |                16 |
|    2 |                32 |
|    3 |                 5 |
|    3 |                10 |
+------+-------------------+

Consultar matrices anidadas

Si una tabla contiene una ARRAY de STRUCTs, puedes aplanar la ARRAY para consultar los campos de la STRUCT. También puedes aplanar los campos del tipo ARRAY de valores de STRUCT.

Consultar elementos STRUCT en una ARRAY

En el siguiente ejemplo se utiliza UNNEST con CROSS JOIN para aplanar una ARRAY de STRUCTs.

SELECT race,
       participant.name,
       participant.splits
FROM
  (SELECT "800M" AS race,
    [STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] AS splits),
     STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS splits),
     STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS splits),
     STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS splits),
     STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS splits),
     STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS splits),
     STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS splits),
     STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] as splits)]
     AS participants
  ) AS r
CROSS JOIN UNNEST(r.participants) AS participant;

+------+-------------+-----------------------+
| race | name        | splits                |
+------+-------------+-----------------------+
| 800M | Rudisha     | [23.4,26.3,26.4,26.1] |
+------+-------------+-----------------------+
| 800M | Makhloufi   | [24.5,25.4,26.6,26.1] |
+------+-------------+-----------------------+
| 800M | Murphy      | [23.9,26,27,26]       |
+------+-------------+-----------------------+
| 800M | Bosse       | [23.6,26.2,26.5,27.1] |
+------+-------------+-----------------------+
| 800M | Rotich      | [24.7,25.6,26.9,26.4] |
+------+-------------+-----------------------+
| 800M | Lewandowski | [25,25.7,26.3,27.2]   |
+------+-------------+-----------------------+
| 800M | Kipketer    | [23.2,26.1,27.3,29.4] |
+------+-------------+-----------------------+
| 800M | Berian      | [23.7,26.1,27,29.3]   |
+------+-------------+-----------------------+

Puedes encontrar información específica de campos repetidos. Por ejemplo, la siguiente consulta devuelve el corredor más rápido de una carrera de 800 metros.

En este ejemplo no hay aplanamiento de matrices, pero representa un modo habitual de obtener información a partir de un campo repetido.

Ejemplo

SELECT race,
       (SELECT name
        FROM UNNEST(participants)
        ORDER BY (
          SELECT SUM(duration)
          FROM UNNEST(splits) AS duration) ASC
          LIMIT 1) AS fastest_racer
FROM
  (SELECT "800M" AS race,
    [STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] AS splits),
     STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS splits),
     STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS splits),
     STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS splits),
     STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS splits),
     STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS splits),
     STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS splits),
     STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] as splits)]
     AS participants
  ) AS r;

+------+---------------+
| race | fastest_racer |
+------+---------------+
| 800M | Rudisha       |
+------+---------------+

Consultar campos de tipo ARRAY en una STRUCT

También puedes obtener información de campos repetidos anidados. Por ejemplo, la siguiente declaración devuelve el corredor que ha hecho la vuelta más rápida en una carrera de 800 metros.

SELECT race,
       (SELECT name
        FROM UNNEST(participants),
          UNNEST(splits) AS duration
        ORDER BY duration ASC LIMIT 1) AS runner_with_fastest_lap
FROM
  (SELECT "800M" AS race,
    [STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] AS splits),
     STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS splits),
     STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS splits),
     STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS splits),
     STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS splits),
     STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS splits),
     STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS splits),
     STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] as splits)]
     AS participants
  ) AS r;

+------+-------------------------+
| race | runner_with_fastest_lap |
+------+-------------------------+
| 800M | Kipketer                |
+------+-------------------------+

Observa que en la consulta anterior se utiliza el operador de coma (,) para realizar una CROSS JOIN implícita. Equivale al ejemplo siguiente, que utiliza una CROSS JOIN explícita.

SELECT race,
       (SELECT name
        FROM UNNEST(participants)
        CROSS JOIN UNNEST(splits) AS duration
        ORDER BY duration ASC LIMIT 1) AS runner_with_fastest_lap
FROM
  (SELECT "800M" AS race,
    [STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] AS splits),
     STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS splits),
     STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS splits),
     STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS splits),
     STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS splits),
     STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS splits),
     STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS splits),
     STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] as splits)]
     AS participants
  ) AS r;

+------+-------------------------+
| race | runner_with_fastest_lap |
+------+-------------------------+
| 800M | Kipketer                |
+------+-------------------------+

Ten en cuenta que al aplanar las matrices con una CROSS JOIN se excluyen las filas con matrices vacías o NULL. Si quieres incluir estas filas, utiliza una LEFT JOIN.

SELECT
  name, sum(duration) as duration
FROM
  (SELECT "800M" AS race,
    [STRUCT("Rudisha" AS name, [23.4, 26.3, 26.4, 26.1] AS splits),
     STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS splits),
     STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS splits),
     STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS splits),
     STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS splits),
     STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS splits),
     STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS splits),
     STRUCT("Nathan" as name, ARRAY<FLOAT64>[] as splits),
     STRUCT("David" as name, NULL as splits)]
     AS participants) AS races,
  races.participants LEFT JOIN participants.splits duration
GROUP BY name;

+-------------+--------------------+
| name        | duration           |
+-------------+--------------------+
| Murphy      | 102.9              |
| Rudisha     | 102.19999999999999 |
| David       | NULL               |
| Rotich      | 103.6              |
| Makhloufi   | 102.6              |
| Bosse       | 103.4              |
| Kipketer    | 106                |
| Nathan      | NULL               |
| Lewandowski | 104.2              |
+-------------+--------------------+

Crear matrices a partir de subconsultas

Una tarea habitual en el trabajo con matrices consiste en convertir el resultado de una subconsulta en una matriz. En Cloud Spanner SQL, puedes hacerlo con la función

ARRAY().

Por ejemplo, observa la siguiente operación en la tabla sequences:

SELECT some_numbers,
  ARRAY(SELECT x * 2
        FROM UNNEST(some_numbers) AS x) AS doubled
FROM sequences;

+---------------+----------------+
| some_numbers  | doubled        |
+---------------+----------------+
| [0,1,1,2,3,5] | [0,2,2,4,6,10] |
+---------------+----------------+
| [2,4,8,16,32] | [4,8,16,32,64] |
+---------------+----------------+
| [5,10]        | [10,20]        |
+---------------+----------------+

Este ejemplo comienza con una tabla denominada "sequences". Esta tabla contiene una columna, some_numbers, del tipo ARRAY<INT64>.

La propia consulta contiene una subconsulta. Esta selecciona cada fila en la columna some_numbers y utiliza UNNEST para devolver la matriz como un conjunto de filas. A continuación, multiplica cada valor por dos y, después, vuelve a combinar las filas en una matriz con el operador ARRAY().

Filtrar matrices

En el siguiente ejemplo se utiliza una cláusula WHERE en la subconsulta del operador ARRAY() para filtrar las filas devueltas.

Nota: En los ejemplos siguientes, las filas resultantes no están ordenadas.

SELECT
  ARRAY(SELECT x * 2
        FROM UNNEST(some_numbers) AS x
        WHERE x < 5) AS doubled_less_than_five
FROM sequences;

+------------------------+
| doubled_less_than_five |
+------------------------+
| [0,2,2,4,6]            |
+------------------------+
| [4,8]                  |
+------------------------+
| []                     |
+------------------------+

Observa que la tercera fila contiene una matriz vacía porque los elementos de la fila original correspondiente ([5, 10]) no cumplían con el requisito de filtro de x < 5.

También puedes filtrar matrices mediante SELECT DISTINCT para devolver solo elementos únicos dentro de una matriz.

SELECT ARRAY(SELECT DISTINCT x
             FROM UNNEST(some_numbers) AS x) AS unique_numbers
FROM sequences
WHERE id = 1;

+----------------+
| unique_numbers |
+----------------+
| [0,1,2,3,5]    |
+----------------+

Además, puedes filtrar filas de matrices mediante la palabra clave IN. Esta palabra clave filtra las filas que contienen las matrices mediante la determinación de si un valor específico coincide con un elemento en la matriz.

SELECT
   ARRAY(SELECT x
         FROM UNNEST(some_numbers) AS x
         WHERE 2 IN UNNEST(some_numbers)) AS contains_two
FROM sequences;

+---------------+
| contains_two  |
+---------------+
| [0,1,1,2,3,5] |
+---------------+
| [2,4,8,16,32] |
+---------------+
| []            |
+---------------+

Observa de nuevo que la tercera fila contiene una matriz vacía porque la matriz de la fila original correspondiente ([5, 10]) no contenía 2.

Análisis de matrices

Para comprobar si una matriz contiene un valor específico, utiliza el operador IN con UNNEST. Para comprobar si una matriz contiene un valor que coincida con una condición, utiliza la función EXISTS con UNNEST.

Análisis en busca de valores específicos

Para analizar una matriz de un valor específico, utiliza el operador IN con UNNEST.

Ejemplo

En el ejemplo siguiente se devuelve true si la matriz contiene el número 2.

SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value;

+----------------+
| contains_value |
+----------------+
| true           |
+----------------+

Para devolver las filas de una tabla donde la columna de la matriz contiene un valor específico, filtra los resultados de IN UNNEST mediante la cláusula WHERE.

Ejemplo

En el ejemplo siguiente se devuelve el valor de id de las filas donde la columna de la matriz contiene el valor 2.

SELECT id AS matching_rows
FROM (SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
      UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
      UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
WHERE 2 IN UNNEST(some_numbers)
ORDER BY matching_rows;

+---------------+
| matching_rows |
+---------------+
| 1             |
| 2             |
+---------------+

Análisis en busca de valores que cumplen con una condición

Para analizar una matriz en busca de valores que cumplen con una condición, utiliza UNNEST para devolver una tabla de los elementos en la matriz, utiliza WHERE para filtrar la tabla resultante en una subconsulta y utiliza EXISTS para comprobar si la tabla filtrada contiene filas.

Ejemplo

En el ejemplo siguiente se devuelve el valor de id de las filas donde la columna de la matriz contiene valores superiores a 5.

SELECT id AS matching_rows
FROM (SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
      UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
      UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
WHERE EXISTS (SELECT *
              FROM UNNEST(some_numbers) AS x
              WHERE x > 5);

+---------------+
| matching_rows |
+---------------+
| 2             |
| 3             |
+---------------+

Análisis en busca de valores del campo STRUCT que cumplen con una condición

Para buscar una matriz de STRUCTs para un campo cuyo valor cumple con una condición, utiliza UNNEST para devolver una tabla con una columna para cada campo de STRUCT; después, filtra las filas no coincidentes de la tabla mediante WHERE EXISTS.

Ejemplo

En el ejemplo siguiente se devuelven las filas donde la columna de la matriz contiene una STRUCT cuyo campo b tiene un valor superior a 3.

SELECT id AS matching_rows
FROM (SELECT 1 AS id, [STRUCT(0 AS a, 1 AS b)] AS some_numbers
      UNION ALL SELECT 2 AS id, [STRUCT(2 AS a, 4 AS b)] AS some_numbers
      UNION ALL SELECT 3 AS id, [STRUCT(5 AS a, 3 AS b), STRUCT(7 AS a, 4 AS b)]
        AS some_numbers)
WHERE EXISTS (
  SELECT 1
  FROM UNNEST(some_numbers)
  WHERE b > 3
);

+---------------+
| matching_rows |
+---------------+
| 2             |
| 3             |
+---------------+

Matrices y agregación

Con Cloud Spanner SQL, puedes agregar valores en una matriz mediante ARRAY_AGG().

Observa la tabla siguiente, fruits:

CREATE TABLE fruits (
  fruit STRING(MAX),
  id INT64 NOT NULL
) PRIMARY KEY(id);

Supón que la tabla se rellena con los datos siguientes:

+----+--------------+
| id | fruit        |
+----+--------------+
| 1  | "apple"      |
| 2  | "pear"       |
| 3  | "banana"     |
+----+--------------+

En esta consulta puedes ver cómo se utiliza ARRAY_AGG():

SELECT ARRAY_AGG(fruit) AS fruit_basket
FROM fruits;

+---------------------+
| fruit_basket        |
+---------------------+
| [apple,pear,banana] |
+---------------------+

La matriz que devuelve ARRAY_AGG() se muestra en un orden arbitrario, ya que no se garantiza el orden en el que la función concatena los valores.

También puedes aplicar funciones de agregación, como SUM(), a los elementos de una matriz. Por ejemplo, la siguiente consulta devuelve la suma de elementos de matriz para cada fila de la tabla sequences.

SELECT some_numbers,
  (SELECT SUM(x)
   FROM UNNEST(s.some_numbers) x) AS sums
FROM sequences s;

+---------------+------+
| some_numbers  | sums |
+---------------+------+
| [0,1,1,2,3,5] |   12 |
+---------------+------+
| [2,4,8,16,32] |   62 |
+---------------+------+
| [5,10]        |   15 |
+---------------+------+

Construir matrices de matrices

Cloud Spanner SQL no es compatible con la creación de matrices de matrices directamente. En su lugar, debes crear una matriz de estructuras y cada una de ellas con un campo del tipo ARRAY. Para ilustrarlo, observa la tabla points siguiente:

CREATE TABLE points (
  point ARRAY<INT64>,
  id INT64 NOT NULL
) PRIMARY KEY(id);

Supón que la tabla se rellena con las filas siguientes:

+----+----------+
| id | point    |
+----+----------+
| 1  | [1, 5]   |
| 2  | [2, 8]   |
| 3  | [3, 7]   |
| 4  | [4, 1]   |
| 5  | [5, 7]   |
+----+----------+

Ahora, supongamos que querías crear una matriz compuesta de cada point en la tabla points. Para ello, encapsula la matriz devuelta desde cada fila en una STRUCT, tal y como se muestra a continuación.

SELECT ARRAY(
  SELECT STRUCT(point)
  FROM points)
  AS coordinates;

+--------------+
| coordinates  |
+--------------+
| point: [1,5] |
| point: [2,8] |
| point: [3,7] |
| point: [4,1] |
| point: [5,7] |
+--------------+