ARRAYS_OVERLAP

기능

ARRAYS_OVERLAP은 두 배열에 공통 요소가 하나 이상 있는지 확인하는 데 사용됩니다. 공통 요소가 있으면 true를 반환하고, 없으면 false를 반환합니다.

구문

ARRAYS_OVERLAP(arr1, arr2)

매개변수

• arr1: 첫 번째 배열로, 타입은 ARRAY<T>입니다.

• arr2: 두 번째 배열로, 타입은 ARRAY<T>입니다.

  • 두 배열의 요소 타입 T는 같거나 서로 암시적으로 변환할 수 있어야 합니다.
  • 요소 타입 T는 숫자, 문자열, 날짜/시간, IP 또는 UUID 타입일 수 있습니다.

반환 값

• BOOLEAN 타입을 반환합니다:

  • 두 배열에 교집합이 있으면 true를 반환합니다.
  • 교집합이 없으면 false를 반환합니다.

사용 시 참고 사항

1. 요소의 동등성(= 연산자)을 기준으로 비교합니다.
2. 이 함수에서는 NULL과 NULL을 같은 것으로 간주합니다 (예제 참조).
3. 테이블 생성문에서 역색인을 지정하여 실행 속도를 높일 수 있습니다 (예제 참조).
   • 함수를 조건절의 조건으로 사용하면 역색인이 실행 속도를 높여 줍니다.
   • 함수를 쿼리 결과에 사용하면 역색인이 실행 속도를 높여 주지 않습니다.
4. 데이터 정제, 태그 매칭, 사용자 행동의 교집합을 구하는 시나리오에서 주로 사용됩니다.

예제

1. 간단한 예제

   SELECT ARRAYS_OVERLAP(ARRAY('hello', 'aloha'), ARRAY('hello', 'hi', 'hey'));
   +----------------------------------------------------------------------+
   | ARRAYS_OVERLAP(ARRAY('hello', 'aloha'), ARRAY('hello', 'hi', 'hey')) |
   +----------------------------------------------------------------------+
   |                                                                    1 |
   +----------------------------------------------------------------------+
   
   SELECT ARRAYS_OVERLAP(ARRAY('Pinnacle', 'aloha'), ARRAY('hi', 'hey'));
   +----------------------------------------------------------------+
   | ARRAYS_OVERLAP(ARRAY('Pinnacle', 'aloha'), ARRAY('hi', 'hey')) |
   +----------------------------------------------------------------+
   |                                                              0 |
   +----------------------------------------------------------------+

2. 잘못된 매개변수 타입: 지원하지 않는 타입을 전달하면 INVALID_ARGUMENT를 반환합니다

   -- [INVALID_ARGUMENT] execute failed, unsupported types for function arrays_overlap
   SELECT ARRAYS_OVERLAP(ARRAY(ARRAY('hello', 'aloha'), ARRAY('hi', 'hey')), ARRAY(ARRAY('hello', 'hi', 'hey'), ARRAY('aloha', 'hi')));

3. 입력 ARRAY가 NULL이면 반환 값은 NULL입니다

   SELECT ARRAYS_OVERLAP(ARRAY('HELLO', 'ALOHA'), NULL);
   +-----------------------------------------------+
   | ARRAYS_OVERLAP(ARRAY('HELLO', 'ALOHA'), NULL) |
   +-----------------------------------------------+
   |                                          NULL |
   +-----------------------------------------------+
   
   SELECT ARRAYS_OVERLAP(NULL, NULL);
   +----------------------------+
   | ARRAYS_OVERLAP(NULL, NULL) |
   +----------------------------+
   |                        NULL |
   +----------------------------+

4. 입력 ARRAY에 NULL이 포함된 경우, NULL과 NULL은 같은 것으로 간주됩니다

    SELECT ARRAYS_OVERLAP(ARRAY('HELLO', 'ALOHA'), ARRAY('HELLO', NULL));
    +---------------------------------------------------------------+
    | ARRAYS_OVERLAP(ARRAY('HELLO', 'ALOHA'), ARRAY('HELLO', NULL)) |
    +---------------------------------------------------------------+
    |                                                             1 |
    +---------------------------------------------------------------+
   
    SELECT ARRAYS_OVERLAP(ARRAY('PICKLE', 'ALOHA'), ARRAY('HELLO', NULL));
    +----------------------------------------------------------------+
    | ARRAYS_OVERLAP(ARRAY('PICKLE', 'ALOHA'), ARRAY('HELLO', NULL)) |
    +----------------------------------------------------------------+
    |                                                             0  |
    +----------------------------------------------------------------+
   
    SELECT ARRAYS_OVERLAP(ARRAY(NULL), ARRAY('HELLO', NULL));
    +---------------------------------------------------+
    | ARRAYS_OVERLAP(ARRAY(NULL), ARRAY('HELLO', NULL)) |
    +---------------------------------------------------+
    |                                                 1 |
    +---------------------------------------------------+

5. 역색인을 사용하여 쿼리 속도 높이기

   -- Create table with inverted index
   CREATE TABLE IF NOT EXISTS arrays_overlap_table (
       id INT,
       array_column ARRAY<STRING>,
       INDEX idx_array_column (array_column) USING INVERTED -- only non-tokenized inverted indexes are allowed
   ) ENGINE=OLAP
   DUPLICATE KEY(id)
   DISTRIBUTED BY HASH(id) BUCKETS 1
   PROPERTIES (
   "replication_num" = "1"
   );
   
   -- Insert two rows
   INSERT INTO arrays_overlap_table (id, array_column) VALUES (1, ARRAY('HELLO', 'ALOHA')), (2, ARRAY('NO', 'WORLD'));

• 함수를 조건절의 조건으로 사용하면 역색인이 실행 속도를 높여 줍니다

  SELECT * from arrays_overlap_table WHERE ARRAYS_OVERLAP(array_column, ARRAY('HELLO', 'PICKLE')); 
  +------+--------------------+
  | id   | array_column       |
  +------+--------------------+
  |    1 | ["HELLO", "ALOHA"] |
  +------+--------------------+
  

• 함수를 쿼리 결과에 사용하면 역색인이 실행 속도를 높여 주지 않습니다

  SELECT ARRAYS_OVERLAP(array_column, ARRAY('HELLO', 'PICKLE')) FROM arrays_overlap_table;
  +--------------------------------------------------------+
  | ARRAYS_OVERLAP(array_column, ARRAY('HELLO', 'PICKLE')) |
  +--------------------------------------------------------+
  |                                                      1 |
  |                                                      0 |
  +--------------------------------------------------------+