PostgreSQL进阶操作

发布时间:2023年12月17日

PostgreSQL进阶操作

SQL执行顺序
(9) SELECT (10) DISTINCT col1,
[OVER()] (6) AGG_FUNC(col2)
(1) FROM table1
(3) JOIN table2
(2) ON table1.col = table2.col
(4) WHERE constraint_expression
(5) GROUP BY col
(7) WITH CUBE|ROLLUP
(8) HAVING constraint_expression
(11) ORDER BY col ASC |DESC
(12) LIMIT count OFFSET count
复制表结构(不复制表注释,其他都复制)
CREATE TABLE emp_copy (LIKE emp including all);
分组聚合

grouping sets扩展 (单个)

select os,device, city ,count(*)
from requests
group by grouping sets((os, device), (city), ());
上述语句等效于如下语句:
select os, device, NULL, count(*)
from requests group by os, device
union all
select NULL, NULL, NULL, count(*)
from requests
union all
select null, null, city, count(*)
from requests group by city;

rollup(嵌套)

select os,device, city ,count(*) from requests 
group by grouping sets((city), ROLLUP(os, device));
上述语句等效于如下语句:
select os,device, city ,count(*) from requests 
group by grouping sets((city), (os), (os, device), ());

cube(组合)

select os,device, city, count(*)
from requests 
group by cube (os, device, city);
上述语句等效于如下语句:
select os,device, city, count(*)
from requests 
group by grouping sets ((os, device, city),(os, device),(os, city),(device,city),(os),(device),(city),());
递归
with recursive tmp as (
    select id,name from user where id=1
    union all
    select test.id,test.name from test join tmp on test.id = tmp.id
) select * from tmp;
注意点
#1.修改表名,视图的表名也会跟着修改,重新备份表数据要特别注意
#2. col !='yyds' and col is null 需要判断null值(高斯数据库)
开窗
first_value() --第1个值
last_value() --最后1个值
lead(col,n,default) --往下n行
lag(col,n,default) --往上n行
row_number() --行序号
dense_rank() --并列,不占位
rank()  --并列,占位

#<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名> rows/range窗口子句)
rows/range between unbounded preceding and current row
preceding:往前
following:往后
current row:当前行
unbounded:无界限(起点或终点)
unbounded preceding:表示从前面的起点
unbounded following:表示到后面的终点
特殊函数
regexp_split_to_table(col,';')
decode(gender,1,'男',2,'女','未知')
nvl(col1,col2) 
文章来源:https://blog.csdn.net/weixin_44033210/article/details/134961772
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。