sql - 混合字数串的人性化或自然数排序

Question

跟进 this question通过 Sivaram Chintalapudi , 我感兴趣的是在 PostgreSQL 中做 natural - or "humanized" - sorting 是否可行"包含多位数字和单词/字母混合的字符串。字符串中没有固定的单词和数字模式，并且一个字符串中可能有多个多位数字。

我见过的唯一经常这样做的地方是在 Mac 操作系统的 Finder 中，它自然地对包含混合数字和单词的文件名进行排序，将“20”放在“3”之后，而不是在它之前。

所需的排序规则将由一种算法生成，该算法将每个字符串在字母数字边界处分成 block ，然后对每个部分进行排序，将具有正常排序规则的字母 block 和数字 block 视为用于排序目的的整数。所以:

'AAA2fred'会变成('AAA',2,'fred')和 'AAA10bob'会变成('AAA',10,'bob') .然后可以根据需要对这些进行排序:

regress=# WITH dat AS ( VALUES ('AAA',2,'fred'), ('AAA',10,'bob') )
regress-# SELECT dat FROM dat ORDER BY dat;
     dat      
--------------
 (AAA,2,fred)
 (AAA,10,bob)
(2 rows)

与通常的字符串排序规则相比:

regress=# WITH dat AS ( VALUES ('AAA2fred'), ('AAA10bob') )
regress-# SELECT dat FROM dat ORDER BY dat;
    dat     
------------
 (AAA10bob)
 (AAA2fred)
(2 rows)

但是，记录比较方法并没有推广，因为 Pg 不会比较 ROW(..) 结构或不相等条目数的记录。

给定样本数据 in this SQLFiddle默认的 en_AU.UTF-8 排序规则产生排序:

1A, 10A, 2A, AAA10B, AAA11B, AAA1BB, AAA20B, AAA21B, X10C10, X10C2, X1C1, X1C10, X1C3, X1C30, X1C4, X2C1

但我想要:

1A, 2A, 10A, AAA1BB, AAA10B, AAA11B, AAA20B, AAA21B, X1C1, X1C3, X1C4, X1C10, X1C30, X2C1, X10C10, X10C2

我目前正在使用 PostgreSQL 9.1，但仅 9.2 的建议就可以了。我对有关如何实现高效的字符串拆分方法以及如何在所描述的交替字符串然后数字排序规则中比较生成的拆分数据的建议很感兴趣。或者，当然，采用不需要拆分字符串的完全不同且更好的方法。

PostgreSQL 似乎不支持比较器函数，否则这可以通过递归比较器和类似 ORDER USING comparator_fn 的东西很容易地完成。和一个 comparator(text,text)功能。唉，这种语法是虚构的。

更新: Blog post on the topic .

Answer 1

基于您的测试数据，但这适用于任意数据。这适用于字符串中任意数量的元素。

注册一个由一个text组成的复合类型和一个integer每个数据库值一次。我称之为ai :

CREATE TYPE ai AS (a text, i int);

诀窍是形成一个 ai 的数组来自列中的每个值。

regexp_matches()带有图案 (\D*)(\d*)和 g选项为字母和数字的每种组合返回一行。加上一个不相关的悬垂行和两个空字符串 '{"",""}'过滤或抑制它只会增加成本。在将空字符串 ( '' ) 替换为 0 之后，将其聚合到一个数组中在integer组件(因为 '' 不能转换为 integer )。

NULL值首先排序 - 或者你必须对它们进行特殊处理 - 或者在 STRICT 中使用整个 shebang像@Craig 提议的功能。

Postgres 9.4 或更高版本

SELECT data
FROM   alnum
ORDER  BY ARRAY(SELECT ROW(x[1], CASE x[2] WHEN '' THEN '0' ELSE x[2] END)::ai
                FROM regexp_matches(data, '(\D*)(\d*)', 'g') x)
        , data;

db<> fiddle here

Postgres 9.1(原始答案)

使用 PostgreSQL 9.1.5 测试，其中 regexp_replace()行为略有不同。

SELECT data
FROM  (
    SELECT ctid, data, regexp_matches(data, '(\D*)(\d*)', 'g') AS x
    FROM   alnum
    ) x
GROUP  BY ctid, data   -- ctid as stand-in for a missing pk
ORDER  BY regexp_replace (left(data, 1), '[0-9]', '0')
        , array_agg(ROW(x[1], CASE x[2] WHEN '' THEN '0' ELSE x[2] END)::ai)
        , data         -- for special case of trailing 0

添加regexp_replace (left(data, 1), '[1-9]', '0')作为第一个ORDER BY项目来处理前导数字和空字符串。

如果特殊字符像 {}()"',可能会发生，你必须相应地逃避那些。
@Craig 建议使用 ROW表达式负责这一点。

<罢工>顺便说一句，这不会在 sqlfiddle 中执行，但会在我的数据库集群中执行。 JDBC 无法胜任。 sqlfiddle 提示:

Method org.postgresql.jdbc3.Jdbc3Array.getArrayImpl(long,int,Map) is not yet implemented.

此问题已得到修复:http://sqlfiddle.com/#!17/fad6e/1