PG的SQL高级特性

SQL高级特性

with查询

PG支持的高级SQL特性之一，常称为CTE，with查询在复杂查询中定义一个辅助语句（可理解成在一个查询中定义的临时表），此特性常用于复杂查询或递归查询应用场景

递归查询使用CTE：with查询的一个重要属性就是recursive，使用recursive属性可以引用自己的输出，从而实现递归，一般用于层次结构或树状结构的应用场景

CTE具有以下优点：

*CTE可以简化SQL代码，减少SQL嵌套层数，提高SQL代码可读性

*CTE辅助语句只需要计算一次，在主查询中可以多次使用

*当不需要共享查询结果时，相比视图更轻量

 

批量插入

批量插入是指一次性插入多条数据，主要用于提升数据插入效率，PG有多种方法实现批量插入

 

一、insert into...select...

通过表数据或函数批量插入：insert into table_name select...from source_table

 

二、insert into values (),(),...()

在一条insert语句中通过values关键字插入多条记录

一条SQL插入多行数据，相比一条SQL插入一条数据的方式能减少和数据库的交互，减少数据库WAL日志的生成，提升插入效率

 

三、copy或\copy元命令

copy或\copy元命令能够将一定格式的文件数据导入到数据库中，相比insert命令插入效率更高，通常大数据量的文件导入一般在数据库服务端主机通过PG超级用户使用copy命令导入

 

returning返回修改的数据

PG的returning特性可以返回DML修改的数据：

insert语句后接returning属性返回插入的数据；update语句后接returning属性返回更新后的新值；delete语句后接returning属性返回删除的数据

此特性的优点在于不需要额外的SQL获取这些值，能够方便应用开发

returning *表示返回表插入的所有字段数据，也可以返回指定字段，returning后接字段名即可

 

upsert

PG的upsert特性指insert...on conflict update，解决在数据插入过程中数据冲突的情况，比如违反用户自定义约束，在日志数据应用场景中，通常会在事务中批量插入日志数据，如果其中有一条数据违反表上的约束，整个插入事务将会回滚，PG的upsert特性可以解决此问题

conflict_target指选择仲裁索引判定冲突行为，一般指定被创建约束的字段；conflict_action指冲突动作，可以是do nothing，也可以是用户自定义的update语句

 

数据抽样

PG9.5时提供了tablesample数据抽样功能，9.5之前通常通过order by random()方式实现数据抽样，虽然在功能上满足随机返回指定行数据，但性能很低

9.5版本之后PG支持tablesample数据抽样，语法：

select ... from table_name tablesample sampling_method （ argument [, ...] ) [repeatable (seed) ]

sanpling_method指抽样方法：system和bernoulli，argument指百分比

 

system抽样方式

system抽样方式为随机抽取表上数据块上的数据，理论上被抽样表的每个数据块被检索的概率是一样的，system抽样方式基于数据块级别，后接抽样参数，被选中的块上的所有数据将被检索

 

bernoulli抽样方式

bernoulli抽样方式随机抽取表的数据行，并返回指定百分比数据，bernoulli抽样方式基于数据行级别，理论上被抽样表的每行记录被检索的概率是一样的，因此bernoulli抽样方式抽取的数据相比system抽样方式具有更好的随机性，但性能上相比system抽样方式低很多

 

bernoulli抽样方式返回的数据量非常接近抽样数据的百分比，system抽样方式数据返回以数据块为单位，被抽样的块上的所有数据都被返回，因此system抽样方式返回的数据量偏差较大

system抽样方式基于数据块级别，随机抽取表数据块上的记录，因此此方式抽取的记录随机性不是很好，但返回的数据以数据块为单位，抽样性能很高，适用于抽样效率优先的场景。而bernoulli抽样方式基于数据行，相比system抽样方式所抽样的数据随机性更好，但性能相比system差很多，适用于抽样随机性优先的场景

 

聚合函数

string_agg函数

语法：string_agg (expression,delimiter)

将结果集某个字段的所有行连接成字符串，并用指定delimiter分隔符分隔，expression表示要处理的字符类型数据；参数的类型为（text,text）或（bytea,bytea），函数返回的类型同输入参数类型一致，bytea属于二进制类型，使用情况不多

 

array_agg函数

与string_agg函数类似，主要区别为返回的类型为数组，数组数据类型同输入参数数据类型一致，array_agg函数支持两种语法：

1、array_agg(expression) --输入参数为任何非数组类型

输入参数可以是任何非数组类型，返回结果是一维数组，array_agg函数将结果集某个字段的所有行连接成数组

array_agg函数输出结果为字符类型数组，其他无明显区别，使用array_agg函数主要优点在于可以使用数组相关函数和操作符

2、array_agg(expression) --输入参数为任何数组类型

返回类型为多维数组

 

窗口函数

窗口函数也是基于结果集进行计算，与聚合函数不同的是窗口函数不会将结果集进行分组计算并输出一行，而是将计算出的结果合并到输出的结果集上，并返回多行。使用窗口函数能大幅简化SQL代码

 

窗口函数语法

PG提供内置的窗口函数，例如row_num()、rank()、lag()等，除了内置的窗口函数外，聚合函数、自定义函数后接OVER属性也可作为窗口函数

调用语法：function_name ([expression [,expression ...]]) [ FILTER ( WHERE filter_claues ) ] OVER (window_definition)

window_definition语法：

[ existing_window_name ]

[ PARTITION BY expression [, ...] ]

[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]

[ frame_claues ]

说明：

*OVER表示窗口函数的关键字

*PARTITON BY属性对查询返回的结果集进行分组，之后窗口函数处理分组的数据

*ORDER BY属性设定结果集的分组数据排序

 

avg()OVER()

聚合函数后接over属性的窗口函数表示在一个查询结果集上应用聚合函数，avg()聚合函数后接OVER属性的窗口函数，此窗口函数用来计算分组后数据的平均值

 

row_number()

row_number()窗口函数对结果集分组后的数据标注行号，从1开始

 

rank()

rank()窗口函数和row_number()窗口函数相似，主要区别为当组内某行字段值相同时，行号重复并且行号产生间隙（gaps）

 

dense_rank()

dense_rank()窗口函数和rank()窗口函数相似，主要区别为当组内某行字段值相同时，虽然行号重复，但行号不产生间隙

 

lag()

获取行偏移offset那行某个字段的数据

语法：lag(value anyelement [, offset integer [, default anyelement ]])

其中：

*value指定要返回记录的字段

*offset指行偏移量，可以是正整数或负整数，正整数表示取结果集中向上偏移的记录，负整数表示取结果集中向下偏移的记录，默认值为1

*default是指如果不存在offset偏移的行时用默认值填充，default值默认为null

 

first_value()

first_value()窗口函数用来取结果集每一个分组的第一行数据的字段值

 

last_value()

last_value()窗口函数用来取结果集每一个分组的最后一行数据的字段值

 

nth_value()

nth_value()窗口函数用来取结果集每一个分组的指定行数据的字段值

语法：nth_value(value any,nth integer)

其中：

*value指定表的字段

*nth指定结果集分组数据中的第几行

 

窗口函数别名的使用

如果SQL中需要多次使用窗口函数，可以使用窗口函数别名

语法：select...from...window window_name as ( window_definition) [,...]

window属性指定表的别名为window_name，可以给over属性引用