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
返回排序中最后一个值