PostgreSQL ORDER BY 问题 - 自然排序

Question

我在下表中遇到了 Postgres ORDER BY 问题：

em_code  name
EM001    AAA
EM999    BBB
EM1000   CCC

要向表中插入新记录，

1. 我使用 SELECT * FROMEmployees ORDER BY em_code DESC
2. 使用 reg exp 从 em_code 中剥离字母并存储在 ec_alpha 中
3. 将重新匹配部分转换为整数 ec_num
4. 递增 1 ec_num++
5. 再次填充足够的 zeors 和前缀 ec_alpha

当 em_code 达到 EM1000 时，上述算法失败。

第一步将返回 EM999 而不是 EM1000，并且它将再次生成 EM1000 作为新的 em_code，从而打破唯一键约束。

您知道如何选择 EM1000 吗？

Answer 1

从 Postgres 9.6 开始，可以指定一个排序规则来自然地对带有数字的列进行排序。

https://www.postgresql.org/docs/10/collat​​ion.html

-- First create a collation with numeric sorting
CREATE COLLATION numeric (provider = icu, locale = 'en@colNumeric=yes');

-- Alter table to use the collation
ALTER TABLE "employees" ALTER COLUMN "em_code" type TEXT COLLATE numeric;

现在只需像其他方式一样查询即可。

SELECT * FROMEmployees ORDER BY em_code

在我的数据上，我按以下顺序得到结果（请注意，它还会对外国数字进行排序）：

值
0
0001
001
1
06
6
13
13
14

Answer 2

您可以采取的一种方法是为此创建一个 naturalsort 函数。这是一个由 Postgres legend RhodiumToad 编写的示例。

create or replace function naturalsort(text)
    returns bytea language sql immutable strict as $f$
    select string_agg(convert_to(coalesce(r[2], length(length(r[1])::text) || length(r[1])::text || r[1]), 'SQL_ASCII'),'\x00')
    from regexp_matches($1, '0*([0-9]+)|([^0-9]+)', 'g') r;
$f$;

来源：http://www.rhodiumtoad.org.uk/junk/naturalsort.sql

要使用它，只需按以下方式调用您的订单中的函数：

SELECT * FROM employees ORDER BY naturalsort(em_code) DESC

Answer 3

原因是字符串按字母顺序排序（而不是像您希望的那样按数字排序），并且 1 排序在 9 之前。
您可以这样解决：

SELECT * FROM employees
ORDER  BY substring(em_code, 3)::int DESC;

如果可以的话，从您的 em_code 中删除多余的“EM”并首先保存一个整数会更有效。

评论中问题的答案

要从字符串中删除所有非数字：

SELECT regexp_replace(em_code, E'\\D','','g')
FROM   employees;

\D 是正则表达式 class-shorthand 表示“非数字”。
'g' 作为第四个参数是“全局”开关，用于将替换应用于字符串中的每个匹配项，而不仅仅是第一个匹配项。

将所有非数字替换为空字符串后，仅保留数字。

Answer 4

在我自己的发展中，这总是会出现问题，我最终厌倦了这样做的棘手方法。我最终崩溃并将其实现为 PostgreSQL 扩展：

https://github.com/Bjond/pg_natural_sort_order

它是免费使用的，麻省理工学院许可证。

基本上，它只是规范化字符串中的数字（零前置数字），以便您可以创建一个索引列以进行自然的全速排序。自述文件解释了。

优点是您可以让触发器来完成工作，而不是您的应用程序代码。它将在 PostgreSQL 服务器上以机器速度进行计算，并且添加列的迁移变得简单而快速。

Answer 5

你可以只使用这一行
“按长度排序（子字符串（em_code FROM '[0-9]+'）），em_code”

Answer 6

我在这个相关问题中详细介绍了这一点：

混合单词和数字字符串的人性化或自然数排序

（我发布此答案仅作为有用的交叉引用，因此它是社区维基）。

Answer 7

我想出了一些稍微不同的东西。

基本思想是创建一个元组数组(integer, string)，然后按它们排序。幻数 2147483647 是 int32_max，用于将字符串排序在数字之后。

  ORDER BY ARRAY(
    SELECT ROW(
      CAST(COALESCE(NULLIF(match[1], ''), '2147483647') AS INTEGER),
      match[2]
    )
    FROM REGEXP_MATCHES(col_to_sort_by, '(\d*)|(\D*)', 'g')
    AS match
  )

Answer 8

以下解决方案结合了另一个问题中提出的各种想法，以及经典解决方案：

create function natsort(s text) returns text immutable language sql as $
  select string_agg(r[1] || E'\x01' || lpad(r[2], 20, '0'), '')
  from regexp_matches(s, '(\D*)(\d*)', 'g') r;
$;

该函数的设计目标是简单和纯字符串操作（没有自定义类型，也没有数组），因此它可以很容易地用作嵌入式解决方案，并且索引起来很简单。

注意：如果您期望数字超过 20 位，则必须将函数中的硬编码最大长度 20 替换为合适的较大长度。请注意，这将直接影响结果字符串的长度，因此不要使该值大于所需的值。