如何在AWS Redshift中比较两个数组？判断数组元素是否存在于另一数组

一、判断array1所有元素都存在于array2中（替代@>操作符） #

方案1：用NOT EXISTS检查无遗漏元素 #

通过展开array1，验证其中没有元素不在array2内：

WITH array1 AS (SELECT ARRAY('a','b','c') AS arr),
     array2 AS (SELECT ARRAY('a','b','c','d') AS arr)
SELECT 
  NOT EXISTS (
    SELECT 1 FROM array1, UNNEST(arr) AS elem
    WHERE elem NOT IN (SELECT elem2 FROM array2, UNNEST(arr) AS elem2)
  ) AS is_all_contained;

方案2：对比匹配元素的数量 #

展开两个数组后，统计array1中能匹配到array2的元素数量，和array1总元素数对比：

WITH array1 AS (SELECT ARRAY('a','b','c') AS arr),
     array2 AS (SELECT ARRAY('a','b','c','d') AS arr)
SELECT 
  CASE 
    WHEN (SELECT COUNT(DISTINCT elem) FROM array1, UNNEST(arr) AS elem) = 
         (SELECT COUNT(DISTINCT elem) FROM array1, UNNEST(arr) AS elem
          JOIN array2, UNNEST(array2.arr) AS elem2 ON elem = elem2)
    THEN TRUE 
    ELSE FALSE 
  END AS is_all_contained;

二、判断两个数组存在交集（替代&&操作符） #

方案1：用EXISTS检查共同元素 #

通过展开数组并关联，判断是否存在匹配的元素：

WITH array1 AS (SELECT ARRAY('a','b','c') AS arr),
     array2 AS (SELECT ARRAY('a','b','c','d') AS arr)
SELECT 
  EXISTS (
    SELECT 1 FROM array1, UNNEST(arr) AS elem
    JOIN array2, UNNEST(arr) AS elem2 ON elem = elem2
  ) AS has_intersection;

方案2：统计匹配元素数量 #

统计array1中存在于array2的元素数量，若大于0则说明有交集：

WITH array1 AS (SELECT ARRAY('a','b','c') AS arr),
     array2 AS (SELECT ARRAY('a','b','c','d') AS arr)
SELECT 
  CASE 
    WHEN (SELECT COUNT(1) FROM array1, UNNEST(arr) AS elem
          WHERE elem IN (SELECT elem2 FROM array2, UNNEST(arr) AS elem2)) > 0
    THEN TRUE 
    ELSE FALSE 
  END AS has_intersection;

内容的提问来源于stack exchange，提问作者Hemant Sah