归一化一维数组的数组下标，使其以1开头

Question

PostgreSQL可以从任何地方开始使用数组下标。

考虑以下示例，该示例创建一个包含3个元素的数组，下标从5到7：

SELECT ('[5:7]={1,2,3}'::int[]);

返回值：

[5:7]={1,2,3}

例如，这意味着您获得了第一个元素

SELECT ('[5:7]={1,2,3}'::int[])[5];

我想规范化 任何给定的一维数组，以数组下标1开头。

我能想到的最好的方法是：

SELECT ('[5:7]={1,2,3}'::int[])[array_lower('[5:7]={1,2,3}'::int[], 1):array_upper('[5:7]={1,2,3}'::int[], 1)]

或者，同样，更容易阅读：

WITH x(a) AS (

    SELECT '[5:7]={1,2,3}'::int[]

    )

SELECT a[array_lower(a, 1):array_upper(a, 1)]

FROM   x

您知道更简单/更快或更优雅的方式吗？

基准测试

为了测试性能，我提出了这个快速基准测试。

具有100k行的表，随机长度在1到11之间的简单整数数组：

CREATE TEMP TABLE t (a int[]);

INSERT INTO t -- now with actually varying subscripts

SELECT ('[' || g%10 || ':' || 2*(g%10) || ']={1'

            || repeat(','||g::text, g%10) || '}')::int[]

FROM   generate_series(1,100000) g;

EXPLAIN ANALYZE

SELECT 

       substring(a::text, '{.*$')::int[]       -- Total runtime: 949.304 ms

--     a[-2147483648:2147483647]               -- Total runtime: 283.877 ms

--     a[array_lower(a, 1):array_upper(a, 1)]  -- Total runtime: 311.545 ms

FROM   t

因此，是的，@ Daniel的想法稍快一些。

@Kevin的文本转换也可以，但是不会获得很多积分。

还有其他想法吗？

Answer 1

有一个更简单的方法很丑陋，但我认为从技术上讲是正确的：从数组中提取最大可能的切片，而不是具有计算范围的确切切片。它避免了两个函数调用。例：select ('[5:7]={1,2,3}'::int[])[-2147483648:2147483647];结果是：  int4   --------- {1,2,3}

Answer 2

不知道这是否已经涵盖，但是：SELECT array_agg(v) FROM unnest('[5:7]={1,2,3}'::int[]) AS a(v);为了测试性能，我必须id在测试表上添加一列。慢。

Answer 3

最终，Postgres 9.6 弹出了一些更优雅的东西。手册：可以省略lower-bound和/或upper-bound切片说明符；缺少的边界将替换为数组下标的下限或上限。例如：所以现在很简单：SELECT ('[5:7]={1,2,3}'::int[])[:];与Daniel的硬编码max数组下标解决方案具有相同的性能-这仍然是Postgres 9.5或更早版本的方法。