Categorias:

Funções de dados semiestruturados e estruturados (Matriz/objeto)

ARRAY_INTERSECTION

Retorna uma matriz com os elementos correspondentes nas duas matrizes de entrada.

A função é NULL-safe, ou seja, trata NULLs como valores conhecidos para comparar a igualdade.

Consulte também:

ARRAY_EXCEPT , ARRAYS_OVERLAP

Sintaxe

ARRAY_INTERSECTION( <array1> , <array2> )

Argumentos

array1

Uma matriz que contém elementos a serem comparados.

array2

Uma matriz que contém elementos a serem comparados.

Retornos

Esta função retorna um ARRAY que contém os elementos das matrizes de entrada que correspondem.

Se não houver sobreposição de elementos, a função retorna uma matriz vazia.

Se um ou ambos os argumentos forem NULL, a função retorna NULL.

A ordem dos valores dentro da matriz retornada não está especificada.

Notas de uso

  • Ao comparar dados do tipo OBJECT, os objetos devem ser idênticos para serem considerados correspondentes. Para obter mais detalhes, consulte Exemplos (neste tópico).

  • A diferença entre a função ARRAY_INTERSECTION e a função ARRAYS_OVERLAP relacionada é que a função ARRAYS_OVERLAP simplesmente retorna TRUE ou FALSE, enquanto que a função ARRAY_INTERSECTION retorna os valores reais sobrepostos.

  • No Snowflake, as matrizes são conjuntos múltiplos, não conjuntos. Em outras palavras, as matrizes podem conter várias cópias do mesmo valor. ARRAY_INTERSECTION compara as matrizes usando semânticas de múltiplos conjuntos (às vezes chamadas “semânticas de saco”), o que significa que a função pode retornar múltiplas cópias do mesmo valor. Se uma matriz tiver N cópias de um valor, e a outra matriz tiver M cópias do mesmo valor, então o número de cópias na matriz retornada será o menor de N ou M. Por exemplo, se N for 4 e M for 2, então o valor retornado terá 2 cópias.

  • Se você estiver passando ARRAYs estruturadas:

    • A função retorna uma ARRAY de um tipo que pode acomodar ambos os tipos de entrada.

    • A ARRAY no segundo argumento deve ser comparável à ARRAY no primeiro argumento.

Exemplos

Este exemplo mostra o uso simples da função:

SELECT array_intersection(ARRAY_CONSTRUCT('A', 'B'), 
                          ARRAY_CONSTRUCT('B', 'C'));
+------------------------------------------------------+
| ARRAY_INTERSECTION(ARRAY_CONSTRUCT('A', 'B'),        |
|                           ARRAY_CONSTRUCT('B', 'C')) |
|------------------------------------------------------|
| [                                                    |
|   "B"                                                |
| ]                                                    |
+------------------------------------------------------+

Os conjuntos podem ter mais de um valor correspondente:

SELECT array_intersection(ARRAY_CONSTRUCT('A', 'B', 'C'), 
                          ARRAY_CONSTRUCT('B', 'C'));
+------------------------------------------------------+
| ARRAY_INTERSECTION(ARRAY_CONSTRUCT('A', 'B', 'C'),   |
|                           ARRAY_CONSTRUCT('B', 'C')) |
|------------------------------------------------------|
| [                                                    |
|   "B",                                               |
|   "C"                                                |
| ]                                                    |
+------------------------------------------------------+

Pode haver mais de uma instância com o mesmo valor correspondente. Por exemplo, na consulta abaixo, uma matriz tem três cópias da letra ‘B’, e a outra matriz tem duas cópias da letra ‘B’. O resultado contém duas correspondências:

SELECT array_intersection(ARRAY_CONSTRUCT('A', 'B', 'B', 'B', 'C'), 
                          ARRAY_CONSTRUCT('B', 'B'));
+---------------------------------------------------------------+
| ARRAY_INTERSECTION(ARRAY_CONSTRUCT('A', 'B', 'B', 'B', 'C'),  |
|                           ARRAY_CONSTRUCT('B', 'B'))          |
|---------------------------------------------------------------|
| [                                                             |
|   "B",                                                        |
|   "B"                                                         |
| ]                                                             |
+---------------------------------------------------------------+

Este exemplo utiliza uma quantidade maior de dados:

CREATE OR REPLACE TABLE array_demo (ID INTEGER, array1 ARRAY, array2 ARRAY, tip VARCHAR);

INSERT INTO array_demo (ID, array1, array2, tip)
    SELECT 1, ARRAY_CONSTRUCT(1, 2), ARRAY_CONSTRUCT(3, 4), 'non-overlapping';
INSERT INTO array_demo (ID, array1, array2, tip)
    SELECT 2, ARRAY_CONSTRUCT(1, 2, 3), ARRAY_CONSTRUCT(3, 4, 5), 'value 3 overlaps';
INSERT INTO array_demo (ID, array1, array2, tip)
    SELECT 3, ARRAY_CONSTRUCT(1, 2, 3, 4), ARRAY_CONSTRUCT(3, 4, 5), 'values 3 and 4 overlap';
INSERT INTO array_demo (ID, array1, array2, tip)
    SELECT 4, ARRAY_CONSTRUCT(NULL, 102, NULL), ARRAY_CONSTRUCT(NULL, NULL, 103), 'NULLs overlap';
INSERT INTO array_demo (ID, array1, array2, tip)
    SELECT 5, array_construct(object_construct('a',1,'b',2), 1, 2),
              array_construct(object_construct('a',1,'b',2), 3, 4), 
              'the objects in the array match';
INSERT INTO array_demo (ID, array1, array2, tip)
    SELECT 6, array_construct(object_construct('a',1,'b',2), 1, 2),
              array_construct(object_construct('b',2,'c',3), 3, 4), 
              'neither the objects nor any other values match';
INSERT INTO array_demo (ID, array1, array2, tip)
    SELECT 7, array_construct(object_construct('a',1, 'b',2, 'c',3)),
              array_construct(object_construct('c',3, 'b',2, 'a',1)), 
              'the objects contain the same values, but in different order';

SELECT ID, array1, array2, tip, ARRAY_INTERSECTION(array1, array2) 
    FROM array_demo
    WHERE ID <= 3
    ORDER BY ID;
+----+--------+--------+------------------------+------------------------------------+
| ID | ARRAY1 | ARRAY2 | TIP                    | ARRAY_INTERSECTION(ARRAY1, ARRAY2) |
|----+--------+--------+------------------------+------------------------------------|
|  1 | [      | [      | non-overlapping        | []                                 |
|    |   1,   |   3,   |                        |                                    |
|    |   2    |   4    |                        |                                    |
|    | ]      | ]      |                        |                                    |
|  2 | [      | [      | value 3 overlaps       | [                                  |
|    |   1,   |   3,   |                        |   3                                |
|    |   2,   |   4,   |                        | ]                                  |
|    |   3    |   5    |                        |                                    |
|    | ]      | ]      |                        |                                    |
|  3 | [      | [      | values 3 and 4 overlap | [                                  |
|    |   1,   |   3,   |                        |   3,                               |
|    |   2,   |   4,   |                        |   4                                |
|    |   3,   |   5    |                        | ]                                  |
|    |   4    | ]      |                        |                                    |
|    | ]      |        |                        |                                    |
+----+--------+--------+------------------------+------------------------------------+

Isso mostra o uso com valores NULL:

SELECT ID, array1, array2, tip, ARRAY_INTERSECTION(array1, array2) 
    FROM array_demo
    WHERE ID = 4
    ORDER BY ID;
+----+--------------+--------------+---------------+------------------------------------+
| ID | ARRAY1       | ARRAY2       | TIP           | ARRAY_INTERSECTION(ARRAY1, ARRAY2) |
|----+--------------+--------------+---------------+------------------------------------|
|  4 | [            | [            | NULLs overlap | [                                  |
|    |   undefined, |   undefined, |               |   undefined,                       |
|    |   102,       |   undefined, |               |   undefined                        |
|    |   undefined  |   103        |               | ]                                  |
|    | ]            | ]            |               |                                    |
+----+--------------+--------------+---------------+------------------------------------+

Este exemplo mostra o uso com o tipo de dados OBJECT:

SELECT ID, array1, array2, tip, ARRAY_INTERSECTION(array1, array2) 
    FROM array_demo
    WHERE ID >= 5 and ID <= 7
    ORDER BY ID;
+----+-------------+-------------+-------------------------------------------------------------+------------------------------------+
| ID | ARRAY1      | ARRAY2      | TIP                                                         | ARRAY_INTERSECTION(ARRAY1, ARRAY2) |
|----+-------------+-------------+-------------------------------------------------------------+------------------------------------|
|  5 | [           | [           | the objects in the array match                              | [                                  |
|    |   {         |   {         |                                                             |   {                                |
|    |     "a": 1, |     "a": 1, |                                                             |     "a": 1,                        |
|    |     "b": 2  |     "b": 2  |                                                             |     "b": 2                         |
|    |   },        |   },        |                                                             |   }                                |
|    |   1,        |   3,        |                                                             | ]                                  |
|    |   2         |   4         |                                                             |                                    |
|    | ]           | ]           |                                                             |                                    |
|  6 | [           | [           | neither the objects nor any other values match              | []                                 |
|    |   {         |   {         |                                                             |                                    |
|    |     "a": 1, |     "b": 2, |                                                             |                                    |
|    |     "b": 2  |     "c": 3  |                                                             |                                    |
|    |   },        |   },        |                                                             |                                    |
|    |   1,        |   3,        |                                                             |                                    |
|    |   2         |   4         |                                                             |                                    |
|    | ]           | ]           |                                                             |                                    |
|  7 | [           | [           | the objects contain the same values, but in different order | [                                  |
|    |   {         |   {         |                                                             |   {                                |
|    |     "a": 1, |     "a": 1, |                                                             |     "a": 1,                        |
|    |     "b": 2, |     "b": 2, |                                                             |     "b": 2,                        |
|    |     "c": 3  |     "c": 3  |                                                             |     "c": 3                         |
|    |   }         |   }         |                                                             |   }                                |
|    | ]           | ]           |                                                             | ]                                  |
+----+-------------+-------------+-------------------------------------------------------------+------------------------------------+

Embora os valores NULL em uma matriz sejam tratados como valores comparáveis, se você passar NULL em vez de uma matriz, então o resultado será NULL:

SELECT array_intersection(ARRAY_CONSTRUCT('A', 'B'), 
                          NULL);
+------------------------------------------------+
| ARRAY_INTERSECTION(ARRAY_CONSTRUCT('A', 'B'),  |
|                           NULL)                |
|------------------------------------------------|
| NULL                                           |
+------------------------------------------------+