问题描述
PostgreSQL可以处理数组下标开始任何地方工作。结果
下面这个例子创建与标3元5阵列,以7:
PostgreSQL can work with array subscripts starting anywhere.
Consider this example that creates an array with 3 elements with subscripts from 5 to 7:
SELECT ('[5:7]={1,2,3}'::int[]);
返回:
[5:7]={1,2,3}
含义,比如,你得到的第一个元素以
Meaning, for instance, that you get the first element with
SELECT ('[5:7]={1,2,3}'::int[])[5];
我要的正常化 的的给予1维数组为开始使用数组下标1 。结果
尽我所能想出了:
I want to normalize any given 1-dimensional array to start with array subscript 1.
The best I could come up with:
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)]
或者,同样的,更容易读:
Or, the same, easier the read:
WITH x(a) AS (
SELECT '[5:7]={1,2,3}'::int[]
)
SELECT a[array_lower(a, 1):array_upper(a, 1)]
FROM x
你知道一个更简单/更快或至少更优雅的方式?
Do you know a simpler / faster or at least more elegant way?
有关的性能测试的目的我刮起了这个快速基准。结果
表10万行,1之间任意长度的简单的整数数组11:
For the purpose of testing performance I whipped up this quick benchmark.
Table with 100k rows, simple integer array of random length between 1 an 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
所以,是的,丹尼尔@的想法是稍快。结果
@凯文的文本转换工作,太多,但不赚多点。
So, yes, @Daniel's idea is slightly faster.
@Kevin's text conversion works, too, but doesn't earn many points.
任何其他的想法?
推荐答案
有一个是丑陋的更简单的方法,但我相信技术上是正确的:提取最大可能切片出数组,而不是确切的切片与计算界限。
它避免了两个函数调用
There is a simpler method that is ugly, but I believe technically correct: extract the largest possible slice out of the array, as opposed to the exact slice with computed bounds.It avoids the two function calls.
例如:
select ('[5:7]={1,2,3}'::int[])[-2147483648:2147483647];
结果:
int4
---------
{1,2,3}
这篇关于规格化数组下标为1维数组,使他们从1开始的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!