Redshift中如何将同一邮箱的allowed_id聚合为单个数组

原SQL问题分析 #

你之前的SQL错误在于GROUP BY同时包含了email和allowed_id，这会让每个(email, allowed_id)组合单独成为一个分组，每个分组仅对应一条记录，因此ARRAY(allowed_id)只能生成单元素数组。正确的分组逻辑应该只按email聚合。

可行解决方案 #

由于Redshift不支持原生ArrayAgg，且ListAgg存在64K长度限制，以下两种方法可以满足需求：

方案一：创建自定义数组聚合UDAF（推荐）

通过Redshift支持的用户定义聚合函数（UDAF）实现数组聚合，无长度限制，适合大规模数据：

1. 先创建三个辅助函数和聚合函数：

-- 初始化函数
CREATE OR REPLACE FUNCTION array_agg_init_func(state anyarray)
RETURNS anyarray
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  IF state IS NULL THEN
    RETURN '{}'::anyarray;
  END IF;
  RETURN state;
END;
$$;

-- 聚合步骤函数
CREATE OR REPLACE FUNCTION array_agg_step_func(state anyarray, value anyelement)
RETURNS anyarray
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  RETURN array_append(state, value);
END;
$$;

-- 最终结果函数
CREATE OR REPLACE FUNCTION array_agg_final_func(state anyarray)
RETURNS anyarray
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  RETURN state;
END;
$$;

-- 创建自定义array_agg聚合函数
CREATE AGGREGATE array_agg(anyelement) (
  SFUNC = array_agg_step_func,
  STYPE = anyarray,
  INITCOND = '{}',
  FINALFUNC = array_agg_final_func
);

2. 使用自定义聚合函数查询：

SELECT
  email,
  array_agg(allowed_id) AS user_ids
FROM
  sec_table
GROUP BY
  email;

方案二：递归CTE拼接转数组（无需创建UDAF）

通过递归CTE将同一email的allowed_id拼接为字符串，再转为数组，适合不想创建UDAF的场景（若拼接后的字符串过长仍可能触发限制，但你提到查询耗时不重要，可尝试）：

WITH ranked AS (
  SELECT
    email,
    allowed_id,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY allowed_id) AS rn
  FROM sec_table
),
recursive_agg AS (
  SELECT
    email,
    allowed_id::TEXT AS id_str,
    rn
  FROM ranked
  WHERE rn = 1
  UNION ALL
  SELECT
    r.email,
    ra.id_str || ',' || r.allowed_id::TEXT,
    r.rn
  FROM ranked r
  JOIN recursive_agg ra ON r.email = ra.email AND r.rn = ra.rn + 1
),
max_rn AS (
  SELECT email, MAX(rn) AS max_rn
  FROM ranked
  GROUP BY email
)
SELECT
  email,
  STRING_TO_ARRAY(id_str, ',')::INT[] AS user_ids -- 根据allowed_id的数据类型调整转换逻辑
FROM recursive_agg ra
JOIN max_rn mr ON ra.email = mr.email AND ra.rn = mr.max_rn;

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