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;