Redshift多列值排序通用解决方案实现技术问询

一、两列通话对的高效解法 #

对于包含rowid, callerid, receiverid, callduration字段的calls表，要忽略主被叫顺序计算平均通话时长，用GREATEST和LEAST函数是最简洁高效的方案，无需额外扫描表：

SELECT
  GREATEST(callerid, receiverid) AS party_a,
  LEAST(callerid, receiverid) AS party_b,
  AVG(callduration) AS avg_call_duration
FROM calls
GROUP BY party_a, party_b;

这种方式会把123-456和456-123统一映射为456和123的组合，确保分组一致性。

二、多列场景的通用扩展方案（以3列为例） #

当需要处理3列及以上的ID（如id1, id2, id3），核心思路是将多列值转为有序的统一标识（字符串或数组），Redshift可以通过数组转行排序+重新聚合实现，以下是两种可行方案：

方法1：数组构造+UNNEST排序+LISTAGG聚合 #

直接将多列转为数组，拆分为单行元素后排序，再拼接为有序字符串：

WITH sorted_id_groups AS (
  SELECT
    rowid,
    -- 将多列转为数组，拆分后按值排序，再拼接为有序字符串
    LISTAGG(elem, ',') WITHIN GROUP (ORDER BY elem) AS sorted_id_key
  FROM your_table
  -- 构造包含所有ID列的数组
  UNNEST(ARRAY[id1, id2, id3]) AS elem
  GROUP BY rowid
)
SELECT
  sorted_id_key,
  -- 替换为你需要聚合的指标，比如平均时长
  AVG(your_metric_column) AS avg_metric
FROM sorted_id_groups
JOIN your_table USING (rowid)
GROUP BY sorted_id_key;

方法2：UNPIVOT转列+排序+LISTAGG聚合 #

如果习惯用列转行的方式，也可以用UNPIVOT将多列转为行数据，再排序聚合：

WITH unpivoted_ids AS (
  SELECT
    rowid,
    id_value,
    -- 按ID值排序生成序号
    ROW_NUMBER() OVER (PARTITION BY rowid ORDER BY id_value) AS sort_order
  FROM your_table
  -- 列出所有需要统一的ID列
  UNPIVOT (id_value FOR id_col IN (id1, id2, id3)) AS unpvt
),
sorted_id_groups AS (
  SELECT
    rowid,
    LISTAGG(id_value, ',') WITHIN GROUP (ORDER BY sort_order) AS sorted_id_key
  FROM unpivoted_ids
  GROUP BY rowid
)
SELECT
  sorted_id_key,
  AVG(your_metric_column) AS avg_metric
FROM sorted_id_groups
JOIN your_table USING (rowid)
GROUP BY sorted_id_key;

方案优势 #

• 支持任意数量的ID列：只需在ARRAY[]或UNPIVOT的列列表中新增对应字段即可，无需修改核心逻辑
• 统一标识稳定：不管原ID列的顺序如何，相同的ID集合都会生成完全一致的sorted_id_key
• 性能可控：基于Redshift的原生函数实现，避免复杂关联，适合大数据量场景

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