PostgreSQL中用unnest with ordinality统计元素出现次数的问题

嗨，这个需求其实用PostgreSQL的窗口函数就能完美解决！你已经通过LEFT JOIN LATERAL unnest(arr) WITH ORDINALITY拿到了元素和原数组索引，接下来只需要用ROW_NUMBER()窗口函数按元素分组、按索引排序，就能得到每个元素在展开序列中的累计出现次数。

完整SQL示例 #

先构造你的测试数组，然后执行查询：

WITH sample_arr AS (
  SELECT ARRAY['a','a','b','c','a','b','c','d'] AS arr
)
SELECT
  elem.element,
  elem.idx,
  ROW_NUMBER() OVER (PARTITION BY elem.element ORDER BY elem.idx) AS occurrence_count
FROM sample_arr
LEFT JOIN LATERAL unnest(arr) WITH ORDINALITY AS elem(element, idx) ON true;

逻辑解释 #

• PARTITION BY elem.element：把展开后的结果按元素值分组，相同元素会被分到同一组
• ORDER BY elem.idx：保证每组内的元素按照原数组的顺序排列（因为idx是原数组的ordinality索引）
• ROW_NUMBER()：对每个分组内的元素按顺序编号，这个编号就是该元素在已展开序列中已经出现的次数

输出结果 #

执行后会得到完全符合你预期的结果：

element | idx | occurrence_count
---------+-----+------------------
 a       |   1 |                1
 a       |   2 |                2
 b       |   3 |                1
 c       |   4 |                1
 a       |   5 |                3
 b       |   6 |                2
 c       |   7 |                2
 d       |   8 |                1

因为你的数组长度最多25个元素，这个方法完全不用担心性能问题，窗口函数在小数据集上的效率非常高。

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