PostgreSQL中的字母数字排序

Question

在数据库中，我有各种以以下格式表示的字母数字字符串：

10_asdaasda
100_inkskabsjd
11_kancaascjas
45_aksndsialcn
22_dsdaskjca
100_skdnascbka

我希望它们可以按照字符串前面的数字和字符串名称本身进行排序，但是显然，字符逐个比较，因此按名称排序的结果如下：

10_asdaasda
100_inkskabsjd
100_skdnascbka
11_kancaascjas
22_dsdaskjca
45_aksndsialcn

与其说我希望的顺序，不如说我想要的是：

10_asdaasda
11_kancaascjas
22_dsdaskjca
45_aksndsialcn
100_inkskabsjd
100_skdnascbka

说实话，如果字符串只是按照前面的数字排序，我也没问题。我对PostgreSQL不太熟悉，所以不确定最好的方法是什么。我会感激任何帮助！

- user1464055

---

不幸的是，PostgreSQL没有提供自动数字排序和日期排序（“Jan” <“Feb <“Apr”等）。这真的很复杂，必须针对每个列、每个查询或每个排序进行设置，即使通常不需要也会很昂贵。据我所知，还没有人实现它。使用类似于citext的自定义text数据类型变体是可能的，就像用于大小写不敏感性一样，只是需要有人关心并完成（大量）编码工作。 - Craig Ringer

Answer 1

最理想的方式是规范化您的设计并将列的两个组件拆分为两个单独的列。一个类型为整数，一个为文本。

对于当前的表格，您可以：

SELECT col
FROM   tbl
ORDER  BY (substring(col, '^[0-9]+'))::int  -- cast to integer
         , substring(col, '[^0-9_].*$');    -- works as text

相同的substring()表达式可用于拆分列。

这些正则表达式有一定的容错性：
第一个正则表达式从左侧选择最长的数字字符串，如果没有找到数字，则为NULL，因此转换为integer不会出错。
第二个正则表达式从第一个不是数字或下划线的字符选择其余的字符串。

如果下划线(_)是一个明确的分隔符，则split_part()更快：

SELECT col
FROM   tbl
ORDER  BY split_part(col, '_', 1)::int
        , split_part(col, '_', 2);

db<>fiddle 这里

参见:

• 将逗号分隔的列数据拆分到其他列中

- Erwin Brandstetter

---

那么，如果我有一个查询，比如...SELECT name FROM nametable，我应该怎样把它放进去呢？如果这些名字都是字符串，那么应该像WITH x(t) AS name这样吗？ - user1464055

@user1464055：这里的CTE仅用于演示和简单测试。如果x是一个表，t是其中的一列（没有CTE），它也会起作用。我添加了确切的语法和更多细节。 - Erwin Brandstetter

非常感谢，你的回答对我的问题非常有帮助。我想做类似的事情，但是我就是无法理解语法。再次感谢！ - user1464055

@user1464055：正则表达式非常强大，但也很棘手。我又加入了一个微小的改进。 - Erwin Brandstetter

是的，我一直在尝试将所有东西都转换类型，但显然失败了。现在我理解这两个表达式了，感谢您为我提供了一个完美的解决方案来添加另一列！哈哈，说实话，我并没有创建这个数据库，添加一列可能会很有挑战性。 - user1464055

Answer 2

您可以使用正则表达式与子字符串一起使用。

   order by substring(column, '^[0-9]+')::int, substring(column, '[^0-9]*$')

- D.J.

Answer 3

有一种方法可以通过表达式索引来实现。这不是我的首选解决方案（我会选择Brad的），但您可以在以下表达式上创建索引（还有其他方法可以实现）：

CREATE INDEX idx_name ON table (CAST(SPLIT_PART(columname, '_', 1) AS integer));  

那么，每次您需要下划线字符之前的数字时，您可以通过CAST(SPLIT_PART(columname, '_', 1) AS integer)进行搜索和排序，例如：

SELECT * FROM table ORDER BY CAST(SPLIT_PART(columname, '_', 1) AS integer);  

您可以通过在SPLIT_PART（columname，'_'，2）上创建索引来对字符串部分进行相同的操作，然后进行相应的排序。
但是，正如我所说，我认为这种解决方案非常丑陋。 我肯定会选择另外两列（一个用于数字，一个用于字符串），然后甚至可能删除您在此提到的列。

- Marcelo Zabani

---

+1 在表达式上创建一个索引是提高排序性能的好方法。更快的方法是：在匹配顺序中对这两个表达式创建一个多列索引。 （重新发布评论以修复链接格式）。 - Erwin Brandstetter

Answer 4

您应该向数据库添加一个新列，其数据类型为数字，并在持久化新记录时将其设置为与字符串值前缀相同的值。

然后，您可以在正确类型的数字列上创建索引以进行排序。

- Brad

---

是的，那正是我所想的。否则，要弄清楚如何对这些进行排序就非常具有挑战性。我只是希望有另一种方法，因为这个格式已经有大约600个元素了 :/ - user1464055