postgrel非常实用的SQL写法
2021/5/3 19:25:24
本文主要是介绍postgrel非常实用的SQL写法,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
1、 序号表或序列表
---序列表 generate_series(0, 100, 5),从0到100,间隔5
create table x_test (rid int8);
用序列表轻松的初始化表:
insert into x_test (rid) select generate_series(1,100, 5);
用序列表更新的示例:
drop table if exists x_test; create table x_test(rid serial8, create_date timestamptz, seq_no int4); insert into x_test(rid, create_date) select x as rid, now() create_date from generate_series(1,1000) as x;
更新seq_no
update x_test set seq_no = x from generate_series(1,1000) x where rid=x;
写程序:
select * from generate_series(1, 10) as x, lateral (select array_agg(y) from generate_series(1, x) as y) as z
2、with as 子句
with x as (select avg(id) from generate_series(1, 10) as id) select *, y-(select avg from x) as diff from generate_series(1, 10) as y where y > (select avg from x);
3、Filter子句
select count(*) as total, count(*) filter (where id%2=0) as half, count(*) filter (where id%3=0) as part_3rd from generate_series(1, 100) as id;
4、窗口函数
(1)应用场景1-去重
create table x_user(id serial8, mobile int8); insert into x_user (mobile) select 13899999900 + ceil(random()*(100-1)+1) from generate_series(0, 100);
查看是否有重复数据,
select mobile, count(*) as num from x_user group by mobile having count(1) > 1;
去重,
with pg as (select id, mobile, ROW_NUMBER() over (partition by mobile) as rownum from x_user order by mobile asc), dup as (select id from pg where rownum >=2) delete from x_user where id in (select * from dup);
(2)应用场景2-pre-next计算
创建指标表,并初始化val
create table x_index(id serial8, v_date timestamptz,val int4, pre_val int4, inc_pct decimal(6,2)); insert into x_index(v_date, val) select (now() + (x || ' mins')::interval)as v_date, ceil(random()*(1000-1)+1) as val from generate_series(1, 100) x;
使用窗口查询,
select * from x_index order by v_date asc; select lag(id, 1) over(order by v_date asc) as pre_id,* from x_index order by v_date asc;
--倒序取pre
update x_index set pre_val = pre.val from (select lag(id, 1) over(order by v_date desc) as pre_id,* from x_index order by v_date desc) pre where x_index.id = pre.pre_id;
--顺序取next
update x_index set pre_val = null; update x_index set pre_val = nex.val from (select lag(id, -1) over(order by v_date asc) as pre_id,* from x_index order by v_date asc) nex where x_index.id = nex.pre_id;
这篇关于postgrel非常实用的SQL写法的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-06-26结对编程到底难不难?答案在这里
- 2024-06-19《2023版Java工程师》课程升级公告
- 2024-06-15matplotlib作图不显示3D图,怎么办?
- 2024-06-1503-Loki 日志监控
- 2024-06-1504-让LLM理解知识 -Prompt
- 2024-06-05做软件测试需要懂代码吗?
- 2024-06-0514-ShardingSphere的分布式主键实现
- 2024-06-03为什么以及如何要进行架构设计权衡?
- 2024-05-31全网首发第二弹!软考2024年5月《软件设计师》真题+解析+答案!(11-20题)
- 2024-05-31全网首发!软考2024年5月《软件设计师》真题+解析+答案!(21-30题)