时间戳

首页 > 解决方案 > 具有序数和时间的 Postgresql 不嵌套

arrays - 具有序数和时间的 Postgresql 不嵌套

问题描述

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

[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 部分。

标签： arrayspostgresql

解决方案

使用COUNT解析函数

SQL小提琴

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

结果：

| 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 的数组的计数值。

推荐阅读