Redshift是否有数组交集函数？有无高效替代实现方案？

Amazon Redshift没有内置的array_intersect数组交集函数，以下是几种无需拆分数组后进行关联操作的可行实现思路：

方法1：利用字符串匹配+数组重构 #

将数组转换为带分隔符的字符串，通过模糊匹配判断元素是否存在于另一个数组中，最后重构出交集数组。适用于元素不含自定义分隔符的场景：

WITH sample_data AS (
    SELECT 
        ARRAY['A', 'B'] AS arr1,
        ARRAY['B', 'C'] AS arr2
)
SELECT 
    ARRAY(
        SELECT unnest(arr1)
        WHERE '|' || array_to_string(arr2, '|') || '|' LIKE '%|' || unnest(arr1) || '|%'
    ) AS array_intersection
FROM sample_data;

输出结果：["B"]

如果数组元素包含|，可以替换为其他无冲突的分隔符（比如不可见字符chr(0)），避免匹配错误。

方法2：封装自定义SQL函数 #

将上述逻辑封装为自定义函数，方便重复调用：

CREATE OR REPLACE FUNCTION array_intersect(arr1 VARCHAR(MAX)[], arr2 VARCHAR(MAX)[])
RETURNS VARCHAR(MAX)[]
IMMUTABLE
AS $$
SELECT ARRAY(
    SELECT unnest(arr1)
    WHERE '|' || array_to_string(arr2, '|') || '|' LIKE '%|' || unnest(arr1) || '|%'
)
$$ LANGUAGE sql;

-- 调用示例
SELECT array_intersect(ARRAY['A', 'B'], ARRAY['B', 'C']);

方法3：利用array_position筛选元素 #

通过array_position函数检查元素是否存在于目标数组中，筛选后聚合为交集数组：

WITH sample_data AS (
    SELECT 
        ARRAY['A', 'B'] AS arr1,
        ARRAY['B', 'C'] AS arr2
)
SELECT 
    ARRAY_AGG(element) AS array_intersection
FROM (
    SELECT 
        unnest(arr1) AS element,
        array_position(arr2, unnest(arr1)) AS exists_flag
    FROM sample_data
) t
WHERE exists_flag IS NOT NULL;

以上方法均未涉及多表关联操作，仅通过数组函数和基础筛选逻辑实现交集计算，符合你的需求。

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