PostgreSQL技术问答13 - Select

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

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

本文主要讨论的内容是关系型数据库的一个基本操作: Select，即选择操作。

如何理解Select这个功能和语句

笔者的系列文章写到这里，才来讨论这个Select，是因为自认为这个系列的目的并不是一个学习教程或者技术手册，而是一个技术理解和认知的分享，所以就没有按照普通的教程有着比较清晰的内容框架和讲解路径，来进行内容的编排，而是比较随机的，按照笔者的认知和想法，直到比较成熟或者合适的时间，才将相关的内容分享和表达出来。

Select，英文原意是“选择”，是一个非常基础的SQL语句和功能。从比较高的抽象层面上，数据库系统中对于数据操作可以分为增-查-改-删四大类，或者更进一步可以分为“查询”和“修改”这两类操作，也对应着一个相对完整的数据管理的生命周期。其中显然查询就是这中间最常见、应用也最为广泛和频繁的一个操作。从另一个意义上来说，它甚至是数据库系统构建的一个重要目的之一，因为不能访问和查询的数据，本身是没有意义的。

在SQL语言标准中，查询实现的基础功能就是使用Select语句。它也是SQL中最重要和最复杂的一个特性和功能。我们通过学习和使用Select语句，也可以更有效和深入的理解SQL这种数据库操作的语言和范式。

要理解Select，我们可能需要对SQL有一定的了解。SQL全称为Structure Query Language。即所谓的结构化查询语言。和普通的计算机编程语言不同，SQL语言的设计，就是模拟人类的“自然语言”，更确切的说，就是使用英语的单词、语法和结构。这是一个比较理想的状态，人们只需要使用自然语言对操作和行为进行描述，就可以指导数据库系统进行相关的操作，来达到存取数据的目的。所以，通常认为SQL语句是一种比一般高级编程语言“更高级”的编程语言，即所谓的指令性语言。它只需要告知要执行的操作和目的，指令解析器(这里就是数据库系统)就可以接收指令，自行对其进行解析和运行，并返回执行的结果。

但实际上，由于一些实现技术上的限制，在真正的AGI实现之前，对人类语言的解析，在计算机系统上并不能做到随心所欲。所以真正的实现，是对其进行所谓的“结构化”。它定义了一系列结构、标准、语法和关键字，人们只能在这个框架内，来构造符合规范并且能由数据库系统正确理解和解析的SQL语句。

了解了SQL这个结构化概念之后，我们就可以来进一步了解，Select是怎样实现和使用这个模式，来执行数据库查询这个工作的。

Select的基本结构是什么样的

首先，我们先从普通人类思考的角度，来构想一下，从很多数据中，如何查找并有效的组织所需要的信息。笔者认为，它通常需要考虑以下一些因素：

• 数据从哪里来？可能是一个表，也可以是视图、子查询、外部数据等记录集的形式
• 数据如何呈现，就是需要查询那些字段，因为数据表可以提供很多字段和内容，但我们可能只需要其中的一部分
• 可能还需要对字段进行计算，需要使用表达式，或者使用常数和表达式来构造逻辑字段
• 对于在不同数据表中，但有关联关系的数据，可以通过它们间的关系，来关联这些数据，以表达如一对一、一对多和多对多等逻辑概念
• 我们可能并不需要所有的记录，而是其中的一部分，这时，需要基于某些条件来进行数据的筛选和过滤
• 在查询后，我们可能还需要对结果进行排序，来方便展示和后续处理
• 由于无法控制查询规模(即使使用了条件过滤)，可能需要强制限制结果记录集的大小
• 对于分页展示结果的应用需求，在限制结果集的基础上，还需要设置起始位置
• 如果查询的结果用于后续的更新操作，可能需要对这些记录进行暂时的锁定，以避免数据变更冲突
• 我们可能还需要简单的数据分析功能，最常见的就是分组技术了，即所谓的“聚合计算”
• 所以聚合查询可能还需要根据聚合计算的结果来对统计结果进行过滤
• 对于多个结构相同的结果记录集，我们希望可以将它们合并起来，甚至能做简单的集合计算，包含合并、求交、求差等

上面就是我们对于关系数据库中的数据进行查询和处理的一般流程和方式。另外，从内容的角度而言，我们对于数据库中的数据和信息的查询，其实可以分为下列三种情况：

• 原始记录和数据

就是对数据库中的原始信息和记录进行查询。其他可以稍微扩展一下如常数、简单的计算(记录值的数学计算、字符串分隔合并等)也可以归为这个类型。

• 记录集合统计和分析

就是基于原始数据的特性，来对数据进行分组并对集合进行相关的统计分析。简单的集合分析包括计数、求和、均值、极值等的计算和提取，稍微复杂一点的会使用经典的集合统计方法如点位值、方程等等。SQL语言中一般使用聚合函数结合Group By语句来进行操作。

• 原始记录结合记录集的统计分析

这一类是更高级的统计功能。就是我们不仅仅需要从集合的角度来对数据和信息进行汇总分析，还想要分析记录和记录集之间的关系，以及记录集中，记录和记录之间的关系。对于这一类的应用需求，SQL语言中通常使用Windows窗口函数相关的操作来满足和实现。

以上这些内存，应该能够覆盖绝大多数数据查询的场景和需求了。在Postgres官方技术文档中，专门有select语句相关的章节：

www.postgresql.org/docs/16/sql…

它提供的结构和功能，对于上面的需求，都是可以满足的。在文中，一个标准的Select语句的结构和范式如下：

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
    [ HAVING condition ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]

下面来简单的了解一下这个结构，包括如下的部分：

• With, 这是一个可选的CTE构造语句，用于构造可选的数据源表，笔者有另外的文章专门讨论这部分的内容
• SELECT，这是select查询语句的关键字，声明当前需要执行查询操作
• 选择内容，有几种模式，包括字段内容，表达式和常量等，本质上是对查询结果在横向上进行过滤
• From子句，表示从何种数据源来查询数据，包括CTE、表、视图(包括实体化视图)、子查询等等，支持多个来源(会进行笛卡尔积计算)
• Where子句，对查询结果，在纵向上进行过滤，依据是各种查询条件
• Group By，对查询的数据进行聚合统计计算，然后生成统计结果作为结果记录集
• Having，用于聚合计算时使用的过滤条件
• Window，用于定义在SQL主语句中引用的窗口
• Union/对多个查询结果记录集进行合并、求交、排除操作，得到新的结果集
• Order By，对结果集进行排序，可以定义(多个)排序字段和排序方式
• Limit，对结果集进行裁剪和限制
• Offset，裁剪和限制时，可以指定裁剪的开始位置
• Fetch，定义数据输出的方式
• For, 定义在查询操作时，使用的锁定方式和策略

按照SQL的规范，这个语法结构和框架是相对固定的，即所有的板块次序，和相关的搭配和次序是不能改变的。一般的功能子句，都实现相应的处理功能，并且有特定的关键字引领和标识。

我们从这个结构中也可以感到，在长时间的发展和演进过程中，select语句的设计和结构也越来越复杂。而这个复杂性，主要体现在它试图用一种通用的形式，来包容各种各样的应用需求和数据操作模式，这样就会带来很多细节性的语法、选项和结构，都需要开发者对其有一定的了解，才能更好的正确编写相应的指令，来满足特别的业务需求。这些都提高了相关的学习和记忆成本。

作为SQL数据查询功能的核心，Select语句涉及的内容非常丰富和广博，本文显然是不能全部覆盖的。本文主要的内容是讨论其基本结构和相关的问题，其他更深入的主题如聚合计算、窗口计算、子查询、数据关联、锁定等等另有专门的章节和内容探讨。

举例说明

下面是几个简单的例子，应该可以体现和展示上一章节中，Select语句的典型结构和应用场景了。

// 标准查询语句结构
select id, name from users where dept = 'Sales' order by name, age desc;

// 简单聚合
select dept, count(1) from users group by 1 order by 1;

// 窗口函数
select studclass, stuid, name, course, score, 
rank() over (partition by stuclass, course order by score desc) rnk 
from scores join students using(stuid) order by 1,2;

这里只是展示select的主要结构和要素，其他相关和技术方面的细节，笔者另有博文讨论。下面我们探讨我们可能在使用Select语句中常见的需求和问题。

Select如何限定选择的内容字段

如果将查询的目标结果数据集看成一个表的话，显然可以将其内容抽象成为表的列和行两个维度。那么，首先要确定的事情就是从业务角度而言，具体需要查询哪些数据列的问题。

数据库的初学者可能非常熟悉select * from xxx的操作方式，但实际接触过实际业务系统设计和实现的开发人员应该知道，这绝对不是一个良好的实践。星号在这里代表所有相关的字段(有选择和关联的数据源记录集确定)；首先，有很多字段内容，对于当前这个业务是不必要的；其次，这个字段范围，是不确定的，对执行性能的影响也是不确定的。所以，在真正的应用和生产环境中，不使用星号的可以大幅度并降低这些不确定性带来的性能和安全风险。一个比较好的方式就是使用代码根据业务需求来构建所需要的字段，明确选择数据的内容。

还有一个常见的应用，是在选择字段的时候，可以使用各种各样的函数，可以对查询的字段进行计算和组合，这显然可以方便满足一些业务方面的需求。但在这里需要注意对这些功能应当有节制的使用，因为这会增加宝贵的数据库系统处理能力的负担，特别是考虑到数据库系统可能需要为很多客户端提供共享的计算能力。

Select语句如何限定选择内容的范围

简单而言，一般情况下，在Select语句可以使用Where子句来限定和过滤要输出的记录集合，即所谓的纵向限定。

Where子句的具体内容，是一个条件表达式。即一个表达式，它的执行结果是布尔值。查询的时候，执行引擎会对每个候选记录使用这个条件表达式进行计算和检查，满足条件的，就加入结果集。

顺便说一下，Where子句，不仅可以用于Select查询语句，使用相同的概念和作用，它还可以用于Update和Delete等数据操作的语句和操作。

此外，对于聚合操作，如果需要使用聚合操作的结果来作为过滤条件，就不能或者不限于使用Where子句了，需要配合Having子句来进行过滤。我们可以简单的理解Having子句就是针对聚合操作的“Where”子句，这个笔者在聚合查询相关的章节已经有详细的讨论。

为什么有了Where，还需要Limit

因为其实两种操作的基础概念有所区别。Where是查询业务的需求，它确实需要从当前业务管理和应用的角度出发，需要对结果集进行一种限制或者过滤。所以Where中的条件组合，都是根据业务要求来编写的。而Limit相对而言，是一种技术和工程的处理，一般出于性能方面的考虑，需要对输出结果的规模进行控制。因为Where本身完全是一个逻辑概念，它本身的结果也是不能限制和预测的，但真实应用中，又需要对于应用和数据的稳定性进行控制，这时就需要结合Limit来使用了。

当然 在实际操作中，两者也是可以结合起来使用的。举个例子，常见的分页操作，通常就是这样实现的。首先通过Where子句来查询所有符合业务需求的记录，然后使用Limit配合Offset，来进一步选择出特定页面(排序后记录的位置和范围)的记录。

Limit和Fetch有什么区别

看起来是没有，就是一个兼容性的设计，让Postgres和其他数据库系统能够更好的兼容。它们是等效的。

Limit和Fetch，都可以用于所谓分页查询。但这里要稍微注意一下，如果配合使用Offset，标准语法中的方式，或者是limit ... offset，或者 offset ... fetch ...，两者只能选择其中一种方式，并且要注意offset关键字的位置。

如何正确理解和使用Offset

Offset即“偏置”，它可以和limit配置使用，从一个大的数据结果集出发，从一个特定的位置，选择一组记录来输出。Offset为Limit操作提供了一个很大的灵活性，来帮助完成如分页这种数据集处理的需求。

Limit结合Offset看起来强大，但对于一个比较大的数据集进行操作的时候，我们要认真评估这个操作，对于性能的影响。因为从执行的原理角度而言，它的大致过程是先将数据结果集进行排序，然后对排序后的集合进行遍历，跳过offset设置的行数，然后输出limit指定的行的数量的记录，得到最终的结果。显然，对于比较大的结果集合，如果offset也比较大，它需要处理的行数比较多而且无法高效的省略，还是需要进行相关处理的，只不过最终输出的结果集比较小而已。结论就是，如果对于很大的记录集，在频繁的操作中，应当尽量避免使用Offset来进行分页。

如果不用Offset，如何高效分页呢

那么，我们看到Offset在大记录集中，分页操作效率不高，但确实有相应的业务需求，应当如何处理呢？

答案是重新设计和优化分页的流程。首先，需要对分页所需要的排序依据进行规划，并在相应的字段上建立索引；其次，分页不采用分页的偏移值，只采用上个分页时，最后一个记录的哪个值；然后使用这个值作为过滤依据，并使用limit来限定当前页面记录的数量。

这种方法可以处理大部分常见的分页应用场景，比如人们通常从第一页开始，然后向后遍历页面，这样我们总是可以知道，当前页面的最后一个记录的索引值，并可以作为下一页面的起始值。另外，人们对于分页的精确性要求也不会过高，这个记录到底是第5页的还是第6页的，这个问题其实并不重要，我们只需要能够严格按照分页的排序规则，不漏掉记录就可以了。

如果分页是一个重要的需求，那么对分页排序所使用的字段和索引，就需要进行特别的处理。比如一般情况下，分页是按照时间倒序排列的，那么如果使用ID作为分页依据，就应当设计这个ID可以按照时间顺序生成，并在数据库中进行索引保证排序的性能，然后在下个页面请求的时候，使用上一个页面最后那条记录的ID作为起始参数，就可以保证这个操作的高效。

这里又会出现一个新的问题，就是如果需要直接跳转到一个特定的页面，又需要如何处理呢？

当然我们可能就不得不使用Limit...Offset的方法，也可以考虑使用两阶段查询，首先使用窗口函数或者Limit 1 Offset的方法获取排名对应的ID，然后使用ID进行子记录集的查询。

还有，就是如何获取分页记录集的大小，因为这个可能会提供给前端或者页面，来显示页面编号。这个可能需要先使用count方法，对分页所有的记录进行计数然后返回给前端。 前端依次来构造所有的页面编号，但其实只需要根据页面大小和记录总数，就可以生成逻辑的编号，而不需要进行数据库的实际查询。

对于前后端分离的系统，如果看重应用体验的话，对于分页，请求的时候，可以考虑响应更大范围的数据集，然后在前端进行逻辑分页，这样可以减少网络请求的操作和次数。对于一些有限的记录集，比如不频繁变化的列表，几千条记录以内，这种处理方法就比较合适。

关于分页这个操作应该如何理解

最后，其实我们也可以来深入的思考和分析一下所谓“分页”这个业务需求。

先说观点，笔者认为，在当下这个Web技术已经非常成熟的技术条件下，这个需求和场景的必要性，并不是很大，而且应该有更好的技术方案，就是搜索。

分页这个设计，原来是给以Web页面为主要呈现方式的应用模式来准备的。在那个时间点，无论是客户端、服务端还是网络，处理性能都比较低下，需要最大程度的减少需要处理和传输的信息。所以，对于一个比较大的数据，进行分页，完全是一种强行的工程化的处理方式。现在的分页操作，很大程度上就是这个方式的一种自然延续，和提供给用户一种能够不断得到新信息的心理满足，或者也可以理解称为路径依赖。

但计算机和互联网技术发展到了现在，其信息处理能力已经远远超过人类能够处理的上限。一次性的高效处理和传输上万条数据，对于软硬件而言，而不是一个非常困难的事情。但对于人类而言，对信息进行分页，或者分批的处理，这却是一个很大的限制。人类自身的信息处理能力相对而言其实非常低效，几乎没有人能够按照顺序精确的处理几千条分页的数据。因为它本身除了条目数字之外，对信息的组织其实是没有逻辑的，这对于人类阅读和处理的负担很大。

所以，分页操作，已经主要变成需要照顾人类接收信息能力的一种方式。而在技术上，也完全可以通过超过请求局限的数据，在前端进行处理来提供更好的应用体验，就是所谓的虚拟分页。当然，更合理的方式，应该是重新组织数据提供的方式，如提供标签、分类和搜索功能，来更高效的进行数据的业务逻辑分类和处理。特别是搜索，通过合理的设置和提示，信息搜索可以帮助用户用最快的速度找到需要的信息，是应当被最优先的提供和使用的。

在实践的角度，我们也可以看到这个技术的发展趋势。很多手机APP应用，已经不使用分页这种内容加载的方式了，而是使用“加载更多”这种操作，就是直接在原来的列表上，扩展新的内容，而不是基于下一页的内容来重新构建列表。来满足用户渐进式加载和消费数据的需求。

什么是Lateral

LATERAL是PostgreSQL中一个强大而灵活的特性，它允许在FROM子句中定义一个子查询，并且在对应的Select主语句中引用它。从形式看来，这个子查询就是一种“延迟(Lateral)”的定义，但不影响到在前面的引用。Lateral可以有效解决引用简化名称，在语句前面未定义的问题，有利于编写更加简洁并且容易理解的SQL代码。

下面是一个简单的例子：

with 
D(id,name) as (values (1, 'HR'), (2, 'IT'), (3, 'Finance')),
E(id,name,dept) as (values (1, 'Alice', 1), (2, 'Bob', 2), (3, 'Charlie', 2), (4, 'David', 3))
SELECT D.name dname, E1.name ename FROM D,
LATERAL (SELECT name FROM E WHERE E.id = D.id ORDER BY E.id DESC LIMIT 1) E1;

// 等效于
SELECT D.name dname, (SELECT name FROM E WHERE E.id = D.id ORDER BY E.id DESC LIMIT 1) E1 
FROM D;

这个查询，可以返回每个部门和其最新进(ID值最大)的员工。这里我们可以看到使用Leteral定义的逻辑子查询记录集，可以在前面的语句中引用。虽然我们也可以写出等效的子查询，但前者的结构看起来更清晰，并且如果这个逻辑记录集被前面多次引用，或者在更复杂的查询构造中，也会更加简洁和易于理解一点。

关于排序有什么需要注意的问题

笔者有另一篇博文专门讨论了相关的问题，这里只是作为内容完整性进行列举，不再赘述：

PostgreSQL中的Select包括SQL语句，有哪些特点

在长期的Postgres系统操作和开发的过程中，笔者认为，虽然作为一种标准的SQL语言实现，PG和其他系统相比，也是有一定的特点的。在这个方面的核心就是简单、实用、健壮。下面是几个体会和实例：

• 可选和省略项目

作为SQL语法，本来是有其标准性和规范性的。比如要定义结果记录集字段的别名，包括定义表的别名，一般需要使用AS关键字。但其实，在PG中，如果不会引起歧义，这个AS是可以省略的。这些都可以大大简化PG中SQL语句的编写。在Postgres中，这样的例子还不少，开发者可以在日常使用中看到和体会。

• 常量和方法

PG支持在表达式中，直接使用常量和方法作为字段来使用。笔者原来记得，在Oracle中，好像需要使用一个“虚表(dual)”的概念，但在Postgres中可以直接表达，笔者也看不出来有那些地方可能造成误解和歧义。只能理解为PG的开发者，更趋向于用更简单直接的方式，而不是更规范的方式来解决问题了。下面是两者之间一个简单的例子，读者可以理解一下：

// oracle
select 1 from dual;

// postgres 
select 1, now();

• 灵活性

PG中，有很多地方可以体现灵活性的设计。有很多看起来不太规范，但比较直观和简洁的写法，Postgres也可以接受和兼容。前面Literal的例子应该可以说明这一点。这个感觉很难用实例和量化来进行表述，只是从笔者的经验和体会而言，觉得PG的SQL编写比较自由，限制比较少，不容易错，或者出现错误也比较容易判断和排除。这个特点有点像JS语言，也许也是PosgreSQL比较受Web应用开发者和社区欢迎的原因吧。

小结

本文探讨了关系数据库和SQL语言中，最基本和常用的Select查询语言方面的内容。包括如何理解数据查询、其一般需求和类型，然后分析了Select语句的标准结构和范式，以及实际如何处理和实现查询的相关操作和需求，并简单分析了Postgres作为一种关系性数据库系统，相关语句实现的设计和特点。