Postgres带WHERE条件查询视图时double precision数组乱序问题求解

• 核心问题是你在视图中使用array_agg聚合函数时没有显式指定排序规则：PostgreSQL的聚合函数默认不保证返回元素的顺序，结果顺序完全取决于执行计划，无过滤条件时顺序正确只是巧合，不具备可靠性。
• 加WHERE条件后执行计划变化（比如优化器选择索引扫描、谓词下推等逻辑），导致jsonb_array_elements展开的行顺序发生改变，array_agg聚合生成的数组自然就出现了你看到的乱序偏移。
• 新增ticker过滤条件后对应结果消失：不同过滤条件会触发不同执行计划，数组顺序再次发生变化，原本碰巧满足过滤逻辑的数组顺序不再符合条件，结果自然消失。
• 额外注意：你当前的视图定义缺失GROUP BY ticker子句，会导致所有股票的指标被聚合到同一行结果，这也是严重的逻辑错误。

使用WITH ORDINALITY获取JSON数组元素的原始下标，在array_agg中指定按该下标排序即可保证数组顺序永远和原last5stats字段的顺序一致，修复后的视图定义如下：

SELECT 
    ticker, 
    array_agg((obj->>'EMA20')::float ORDER BY ordinality) AS EMA20,
    array_agg((obj->>'EMA50')::float ORDER BY ordinality) AS EMA50,
    array_agg((obj->>'low')::float ORDER BY ordinality) AS ll, 
    array_agg((obj->>'close')::float ORDER BY ordinality) AS cc
FROM symbols, 
     -- 展开JSON数组同时获取元素原始下标
     jsonb_array_elements(symbols.last5stats) WITH ORDINALITY AS obj(obj, ordinality)
-- 按股票代码分组，保证每个股票生成独立的指标数组
GROUP BY ticker;

若你底层last5stats字段是json类型而非jsonb类型，将jsonb_array_elements替换为json_array_elements即可。

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