- 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:
Sintaxe¶
ARRAY_INTERSECTION( <array1> , <array2> )
Argumentos¶
array1Uma matriz que contém elementos a serem comparados.
array2Uma 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_INTERSECTIONe a funçãoARRAYS_OVERLAPrelacionada é que a funçãoARRAYS_OVERLAPsimplesmente retornaTRUEouFALSE, enquanto que a funçãoARRAY_INTERSECTIONretorna 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_INTERSECTIONcompara 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.
Ambos os argumentos devem ser ARRAYs estruturadas ou ARRAYs semiestruturadas.
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 | +------------------------------------------------+