postgresql实战—窗口函数

源代码 · 2024-10-4 03:36:54

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

postgresql实战—窗口函数

所属分类: 问答交流

新帖推荐: 30日

推荐作品