likes
comments
collection
share

PostgerSQL技术问答12 - 排序

作者站长头像
站长
· 阅读数 57

本文是《PostgreSQL技术问答》系列文章中的一篇。关于这个系列的由来，可以参阅开篇文章：

文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。读者进行阅读时，不用太关注这个方面。

本文将要讨论的主题是在Postgres查询中，如何对结果进行排序。读者可能认为，不就是使用标准的SQL Order By子句吗？很简单方便。但笔者在查阅PG技术文档无意中发现的一个特性，就是在排序的时候，可以使用自定义的排序方法，这个特性，在某些应用场景中有这样的需求，并扩展讨论一些相关的解决方案。

为什么要排序

对查询的记录集进行排序，是一个非常常见的操作，常见到可能很多开发者并没有认真的思考，这样操作是为了什么。实际上，它确实可以有很多理由和原因。在此之前，我们可能需要来先来理解一下，对数据进行排序的实质，其实就是按照排序规则，对数据进行分类和分组，并将这些分组后的数据，按照顺序重新组织和呈现出来。这样，我们就更容易的发现排序的目的和应用的场景了。

  • 阅读习惯

人类(其实主要是工程师)喜欢将信息和数据按照某种顺序进行展示，以达到其“井井有条，尽在掌握”的心理满足。在现实的生活中，这能够帮助我们在一个字典中，快速的定位词条所在的位置，类似的，在计算机屏幕上，也可以帮助我们在列表文本中，快速的进行滚动和查找文本目标。

  • 数据管理

但是，通常对于计算机系统而言，“混乱”的数据对它们并不会造成困扰。因为它们对数据进行查找和寻址的原理和人类完全不同，通常情况下，它们的数据访问需求和工作方式是“随机”的，为了提高访问性能，它们也会专门使用特定的索引系统来对访问进行优化和加速。所以通常情况下，计算机程序并不特别强调和限制数据的顺序，包括呈现顺序和存储顺序。

当然，在特定的情况下，在计算机系统中，将数据能够按照顺序进行存放和管理，也不是毫无意义。起码在应用中最常使用的二分搜索方式，其工作的原理和前提就是需要将数据先进行排序。对于特定的需求，将数据按照顺序在物理位置上进行有序的排列，可能有助于访问性能的提升。

  • 后续操作

有些排序是为了后续的操作方便。最常见的就是对数据进行过滤和分组。这些操作的依据，都是需要对数据进行排序，然后按照排序作为依据的后续处理，才能达到一个更高的处理效率。这个其实才是在应用中最常见的排序应用需求和场景。

在这个方面，笔者的思考是，排序会带来一些性能影响，如果没有确定的应用或者业务需求，应当尽量减少排序操作，或者进行某种方式的变通。比如在前后端分离的系统中，可以在查询数据之后，考虑在前端进行排序和展示，这样可以简化后端数据操作和计算。

如何进行排序

简单而言，在PG数据操作时进行排序，需要使用Order By子句。Order By 子句的标准范式和语法如下：

SELECT select_list FROM table_expression

ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]

[, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]

基于上面的范式，有一些需要理解的要点：

  • Order By 是Select语句的子句，需要配合其使用

  • Order By 是SQL标准语法和关键字

  • Order By 后面的内容是字段列表，可以支持多个字段，数据库会按照字段定义的顺序作为优先级进行排序。如前序字段值一致的时候，在按照后续字段值进行排序

  • 排序支持正排(升序)和倒排，倒排需要在字段后使用关键字 "desc" 指定，正常的排序就是asc，通常可以省略

  • 默认情况下，排序的依据基于数值的大小，和字符串的编码顺序(默认情况)

  • 对于Null值，可以指定其排在最前面(默认情况)还是最后面

如下列语句指定null排在最后：

SELECT * FROM users ORDER BY email NULLS LAST;

  • 除了字段之外，对于表达式也是可以排序的

SELECT product, quantity, price, quantity * price AS total FROM orders ORDER BY total DESC;

  • 除了引用字段名，也可以使用字段序号

SELECT * FROM users ORDER BY 1,2;

  • 排序字段和要查询和显示的字段可以完全无关，除了表达式引用外，两个可以没有逻辑关联

这一点比较重要，因为它可以帮助实现一些自定义排序规则的操作。

字段的排序默认顺序是否可以修改

当然是可以的。通常在不同的语言和字符集应用中有这个需求，因为即使是相同的字符，在不同的语言和字符集中的次序也可能不同。在PG中使用 Collate (修改排序规则collation)关键字来标注实现。

// 在查询时指定
SELECT col1 FROM table1 ORDER BY col1 COLLATE "fr_FR";

// 在定义表结构时设置
CREATE TABLE table1 (
    column1 TEXT COLLATE "en_US" 
);

PG系统在安装的时候，基于相关的操作系统、语言和配置，就是有一个默认的排序规则的。

可以完全使用自定义的排序规则来进行排序吗？

这确实也是一个常见的业务应用需求，就是排序的列表，可能不能使用默认或者一些既定的规则，而需要使用业务自己定义的规则，来实现业务特定的排序操作。这时，就需要系统可以提供某种扩展性，能够注入或者兼容自定义的排序规则。一个正面的典型就是JS的sort方法，除了默认的排序方法之外，它可以注入一个自定义的排序方法作为参数，排序时使用这个方法的返回值，作为排序参考。这里用户就可以自己来定义排序规则和返回值，来控制排序的最终结果了。

笔者原来以为PG也是支持这种方式的，但经过仔细阅读技术文档，发现自己的理解可能有一些偏差，这个实现并不是那么的简单和理想。当然，即使不是这样简单方便，我们还是有一些方法可以实现自定义的排序操作的，这里简单总结一下。

  • 排序权重值列

如果默认的排序方式不能满足业务的需求，我们可以专门为排序需求在数据表中设计一个专门用于排序的字段，在这个字段中，如可以是一个整数。可以手动的为每条记录都设置排序权重，然后在查询时，使用这个字段作为主排序依据就可以了。

  • 排序关联表

思路和前面的排序权重值列差不多，但使用一个外部的排序权重值表。在进行排序的时候，将原始表和这个表相关联，然后使用权重字段和关联的权重值作为排序依据。这个方案的优势在于不需要修改原始记录表，容易解耦和维护，但需要关联操作，效率稍差。

  • Case When

排序权重列需要修改数据表结构，并且需要手动的维护记录的排序权重，在实际使用中不是很方便。对于一个比较有限的排序集合，我们可以考虑使用一种动态赋予排序权限的方式，来解决这个问题。这里可以使用case when来给当前记录的排序权重进行赋值，其实就是第一种排序方式的改进方式。下面是一个简单的例子：

with O(v) as ( values 
('D'),('B'),('A'),('C')
) select O.v, case v when 'B' then 1 when 'C' then 2 when 'A' then 3 when 'D' then 4 else 100 end vo  from O order by vo;
 v | vo 
---+----
 B |  1
 C |  2
 A |  3
 D |  4
(4 rows)

上面的例子中，我们完全重新定义了记录值的排序方法，来实现了需要的排序结果。这种方法，比使用排序字段的方式要灵活一点，因为这个排序SQL是可以动态编写和使用的。

  • array_position

前面使用case when的方式来定义当前字段值的排序规则的书写方法还是稍显繁琐。基于相同的构想，我们可以借助array_position方法，来达到相同的目的，参考代码如下：

 with O(v) as ( values 
('D'),('B'),('A'),('C')
) 
select O.v from O order by array_position(array['C','B','A','D'], O.v);
 v 
---
 C
 B
 A
 D
(4 rows)

这个方法现在更加直观和优雅。它巧妙的利用了PG Array中内容和索引之间的对应关系，来定义数组的排序权重，用于排序操作。

排序完成后，如何显示和使用排序的序号呢

Order By 排序操作完成后，记录集确实就是一个有序(基于排序规则定义的)的记录集合。但有时候，我们还希望获取每一条记录，在这个记录集中的序号，就是行号。一个典型的应用场景就是当我们为班级中的学生按照成绩进行排序后，需要获取每个学生的排名。

对于这个需求，不知道是何种原因，PG并没有提供简单的系统逻辑row()方法，只能使用比较复杂窗口函数row_number()并且结合over子句来进行操作，参考的代码如下：

with 
O(v) as ( values ('D'),('B'),('A'),('C')),
N as (select O.v from O order by array_position(array['C','B','A','D'], O.v))
select N.v, row_number() over() r from N ;
 v | r 
---+---
 C | 1
 B | 2
 A | 3
 D | 4
(4 rows)

这里需要特别注意的是，row_number的使用，必须是在已经排序的记录集的基础之上。相当于需要嵌套一个子查询，否则可能不能达到所预期的结果：

// 不符合预期的结果

with O(v) as ( values 
('D'),('B'),('A'),('C'),('A')
) 
select O.v, array_position(array['C','B','A','D'], v) r, row_number() over()  from O order by r;
 v | r | row_number 
---+---+------------
 C | 1 |          4
 B | 2 |          2
 A | 3 |          3
 A | 3 |          5
 D | 4 |          1
(5 rows)

笔者不是特别理解，为什么在这种情况下会出现这个查询的结果(行号基本上是倒排的)，如果哪位读者有兴趣或者水平可以解答的，欢迎指教。

我想对查询结果随机排序，应该怎么做

如果不指定任何排序规则，其实PG也不会对结果集进行随机的排序，而是按照一种“系统”级的顺序来输出结果。这个顺序应当就是存储在磁盘上的记录访问的顺序。那么，如果我们想要确实完全的进行随机的排序，应该怎么做呢。

答案很简单，就是使用一个随机数作为排序权重就可以了：

select * from udata order by random();

这个操作，还可以引申出另外一个实现场景，就是随机选取几个记录，可以这一操作：

select * from udata order by random() limit 10;

小结

本文深入探讨了Postgres中，对于查询结果进行排序的相关问题。包括相关的需求和场景，实现方式，可以使用的选项，需要注意的问题，还讨论了自定义排序的相关技术方案。

转载自:https://juejin.cn/post/7376511981573308427
评论
请登录