RedShift无array_position函数，如何获取数组中指定字符串的位置？

Redshift原生不支持array_position函数，但可以通过以下几种方式实现相同效果：

方法一：UNNEST结合窗口函数 #

这是最通用的方案，支持所有数组类型，还能处理重复元素的场景。通过UNNEST展开数组，用窗口函数ROW_NUMBER()标记元素位置，再筛选目标值：

-- 示例：查找test_table中array_col数组里'目标字符串'的位置
SELECT 
  original_id,
  element_position
FROM (
  SELECT 
    original_id,
    unnest_element,
    ROW_NUMBER() OVER (PARTITION BY original_id ORDER BY unnest_element) AS element_position
  FROM test_table,
       UNNEST(array_col) AS t(unnest_element)
) AS sub
WHERE unnest_element = '目标字符串';

如果只需要第一个匹配的位置，可改用MIN(element_position)并按主键分组：

SELECT 
  original_id,
  MIN(element_position) AS first_match_position
FROM (
  SELECT 
    original_id,
    ROW_NUMBER() OVER (PARTITION BY original_id ORDER BY unnest_element) AS element_position
  FROM test_table,
       UNNEST(array_col) AS t(unnest_element)
  WHERE unnest_element = '目标字符串'
) AS sub
GROUP BY original_id;

方法二：创建自定义函数 #

如果需要频繁使用该功能，可以封装成自定义SQL函数，简化调用：

-- 创建自定义函数，返回目标元素在数组中的第一个位置（未找到则返回0）
CREATE OR REPLACE FUNCTION array_pos(arr VARCHAR[], target VARCHAR)
RETURNS INT
STABLE
AS $$
SELECT COALESCE(MIN(element_position), 0)
FROM (
  SELECT 
    ROW_NUMBER() OVER () AS element_position
  FROM UNNEST(arr) AS elem
  WHERE elem = target
) AS sub;
$$ LANGUAGE sql;

-- 调用示例
SELECT array_pos(ARRAY['apple', 'banana', 'cherry'], 'banana'); -- 返回2

可根据需求调整返回值（比如未找到时返回NULL），或修改函数参数支持其他数组类型（如INT[]）。

方法三：字符串操作（仅限简单场景） #

如果数组元素不包含分隔符（比如逗号），可以将数组转为字符串后用POSITION计算位置，这种写法更简洁但有局限性：

SELECT 
  -- 前后加逗号避免部分匹配，计算后得到位置
  POSITION(',' || '目标字符串' || ',' IN ',' || array_to_string(array_col, ',') || ',') / (LENGTH('目标字符串') + 2) + 1 AS element_position
FROM test_table;

注意：若元素本身包含分隔符，该方法会返回错误结果，仅适用于元素无特殊符号的场景。

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