Postgres 9.6同步按原顺序展开两个文本数组列的最优方案

原SQL存在两个不稳定的设计，是导致随机乱序的核心原因：

• SELECT子句中同时调用多个unnest()展开数组的对齐顺序没有官方语法保障，仅在简单执行计划下会按数组原始顺序配对，大规模ETL触发并行执行、复杂执行计划时就可能出现配对错乱
• 窗口函数row_number()未指定ORDER BY子句，数据库不会保证分区内行的返回顺序，序号分配逻辑完全随机，单独查询小批量数据时的正常结果只是巧合

PostgreSQL 9.6 支持unnest() WITH ORDINALITY语法，可以在展开数组的同时返回元素对应的原始位置序号，该序号是官方保证和数组元素顺序一致的。我们可以通过该语法分别展开两个字段的数组，再按位置序号关联，即可100%保证元素配对正确、顺序不乱。

对应实现SQL如下：

SELECT 
  t.target_id,
  t.machine_id,
  t.dateread,
  s.state_val::integer AS state,
  f.ftime_val::integer AS ftime,
  s.step - 1 AS step -- 减1实现从0开始计数，和需求示例对齐
FROM some_tbl t
-- 展开state数组并获取原始序号
CROSS JOIN LATERAL unnest(string_to_array(t.state, '|')) WITH ORDINALITY AS s(state_val, step)
-- 展开ftime数组并按相同序号匹配
CROSS JOIN LATERAL unnest(string_to_array(t.ftime, '|')) WITH ORDINALITY AS f(ftime_val, f_step)
WHERE 
  s.step = f_step -- 核心逻辑：保证两个数组的元素按原始位置一一配对
  AND t.target_id IN (60000) 
  AND t.dateread = '2021-09-29';

如果你的state和ftime字段的数组长度永远一致，这个写法可以直接满足需求，不会再出现乱序问题。如果存在长度不一致的场景，可以将CROSS JOIN改为LEFT JOIN，额外添加空值兼容逻辑即可避免数据丢失。

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