PG的SQL高级特性

PG的SQL高级特性

---

前言

需要先了解sql语法再看这个会比较简单易懂

---

with查询

with子句提供定义临时关系的方法

如下建立临时表并不会在数据库中真正创建

递归查询使用CTE

recursive

它其实做到的是从x=1开始循环到5结束，计算出x的总和

string_agg函数实现字段合并

巧妙利用select和union

批量插入

通过表数据批量插入

insert into ... select....

一键插入好方法

一次输入n条数据

上述插入数据那里有了。

copy命令或者\copy命令

将数据导入文件

returning返回修改数据

就是正常来说呢，修改成功插入成功删除成功是只有一个显示修改多少条的信息

并不会返回数据表给我们看，而returning就实现了修改以后返回修改的数据

放在insert后

insert into 关系 values（） returning 属性；

放在update后

update 关系 set 属性=操作 returning 想看的属性；

放在delete后

delete from 关系 where 条件 returning 想看的属性；

upsert

如果我们设置了主键或者设置了unique的约束时，批量插入数据，当这一批数据有一个错误时，全部数据都不能成功插入，所以要解决这种问题

upsert就是来解决这类问题

在冲突的时候设定规矩

insert into 关系 values

ON CONFLICT()

do update set .....

或者 do nothing；

数据抽样

当数据量很大的时候查询语句随机返回指定行数据

order by random() limit x;

这种方法 性能极低 ，因为他是遍历了整张表的

system抽样

这种抽样方式返回的数据是以数据块为单位的

访问预计150条记录，实际放回214条或者107条，性能还可以

一共占了14019块

为什么不是返回完整的150条数据呢？因为它是按照数据块访问的意思就是，这个数据块有多少条就有多少条数据返回。

bernoulli 抽样

随机抽取表中的数据和，返回指定百分比数据

与上面相似操作

发现按道理应该是返回我要多少返回多少，可实际却不是，同时时间花费很多

因为它返回的数据不是在同一个数据块上的，随机性更大。

聚合函数

常用的上面说完了。现有两个特殊功能的：

string_agg函数

将结果集的某些字段所有行连接成字符串

array_agg函数

和string_agg相似，但是是以数组形式返回

可以将数组转换成字符串，并用分隔符分割，使用array_to_string即可

窗口函数

基于结果集进行计算，但是它是将计算的结果合并输出到结果集上，返回多行

就是在每一行的最后都给出结果，而不是像聚集函数一样把结果放一行

partition by 按照分组

order by 按照排序

ROW_NUMBER

对结果集数据进行标号

select 属性名1，属性名2，...,属性名n，row_number() over(partition by 想要按照的分组属性 order by 想要排序的属性名） 新建属性名 from 关系

如果没有partition by的话就将全部记录视为一个组

RANK

计算一组数值中的排序值。

DENSE_RANK

这个和rank的区别在于rank最后一名一定等于这个分组的数量，而dense_rank不一定，因为如果dense_rank遇到并列时，如出现排序的对象是（55，55，57），它的结果是（1，1，2）。

LEAD

lead(属性名，偏移行，超出结果后出现的值）

它会出现该值后出现的值（如果没有设置偏移行，也没有设置超出结果后出现的值，最后一个值的后面是null)

LAG

lead(属性名，偏移行，超出结果后出现的值）

它会出现该值前出现的值（如果没有设置偏移行，也没有设置超出结果后出现的值，第一个值的前面是null）

FIRST_VALUE

返回排序中的第一个值

LAST_VALUE

返回排序中最后一个值