arrays - Postgresql unnest 与序数以及看到的时间

Question

假设我有一个可能重复的字符串数组

[a,a,b,c,a,b,c,d]

我想取消嵌套数组，以便我可以获得索引，以及该元素已在数组中取消嵌套的次数。我的所有数组都相对较短(25 个元素或更少)。

我的预期输出是:

a, 1, 1
a, 2, 2
b, 3, 1
c, 4, 1
a, 5, 3
b, 6, 2
c, 7, 2
d, 8, 1

我可以很容易地完成前两位 left join lateral unnest(arr) with ordinality as a(element, idx)，但我也想要 times_seen 部分。

Answer 1

使用COUNT解析函数

SQL Fiddle

PostgreSQL 9.6 架构设置:

CREATE TABLE t
    (arr TEXT[])
;

INSERT  
    INTO t (arr)
         VALUES ('{a,a,b,c,a,b,c,d}'::TEXT[])
;

查询 1:

SELECT element
    ,idx
    ,COUNT(element) OVER (
        PARTITION BY element ORDER BY idx
        ) AS times_seen
FROM t
    ,unnest(arr) WITH ordinality AS a(element, idx)
ORDER BY idx

Results :

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

如果您可能有多行包含数组并且每个数组都有一个 ID 列，您可以使用

PARTITION BY ID,element 获取数组在该特定 id 处的计数值。