postgresql实战—窗口函数

RETURNING返回更新后的数据

INSERT INTO ……RETURNING  */id

UPSERT：INSERT…ON CONFLICT UPDATE

TABLESAMPLE：数据抽样

SELECT … FROM  table_name  TABLESAMPLE sampling_method

sampling_method抽样方法

SYSTEM：随机抽取表上数据块的数据，抽取的记录的随机性不是很好，但返回的数据以数据块为单位，抽样性能很高，适用于抽样效率优先的场景。

BERNOULLI：随机抽取表的数据行，并返回指定百分比数据。基于数据行，所抽样的数据随机性更好，性能相比SYSTEM差很多

聚合函数：string_agg能将结果集某个字段的所有行连接成字符串

array_agg：与string_agg类似，返回的是数组

窗口函数：row_num、rank、lag等

除了内置的窗口函数外，聚合函数、自定义函数后街OVER属性也可作为窗口函数

avg(score) over  (partition by subject)

row_number：对结果集分组后的数据标注行号

rank与row_number相似，主要区别为当组内某行字段值相同时，行号重复并且行号产生间隙。

dense_rank：与rank窗口函数相似，主要区别当组内字段值相同时，行号重复但行号不产生间隙

first_value：取结果集中每个分组的第一个行数据的字段值。很容易查询分组数据的最大值或最小值

last_value：用来取结果集每个分组的最后一行数据的字段值

nth_value：用来取结果集每一个分组的指定行数据的字段值

nth_value（value any，nth integer）

value：指定表的字段

nth：指定结果集分组数据中的第几行，如果不存在则返回空

创建一个Database时会为这个Database创建一个名为public的默认Schema，每个database可以有多个schema。不同的schema可以有多个相同名称的table，index，view等数据库对象

数据目录用来存放持久化数据

OID：所有的数据库对象都由各自的对象标识符进行内部管理，为无符号的4字节整数

数据库的OID存储在pg_database系统表中

数据库中的表、索引、序列等对象的OID存储在pg_class系统表中

在postgresql中最大的逻辑存储单位是表空间，数据库中创建的对象都保存在表空间中

初始化数据库目录时会自动创建pg_default和pg_global两个表空间

pg_global表空间的物理文件位置在数据目录的global目录中，用来保存系统表

pg_default表空间的物理文件位置在数据目录中的base目录

在数据库中创建对象，例如表索引时，首先会为表和索引分配段，在postgresql中，每个表和索引都用一个文件存储。新创建的表文件以表的OID命名，对于大小超出1gb的表数据文件，自动将其切分为多个文件来存储，切分出的文件用OID.<顺序号＞来命名。但表文件并不是总是OID.<顺序号＞，实际上真正管理表文件的是pg_class的relfilenode字段

在postgresql中，将保存在磁盘中的块称为page，而将内存中的块称为buffer。表和索引称为Relation，行称为Tuple