PostgreSQL SQL Advance

database
,

SQL進階

分組集 GROUP BY

  1. 分組集 group by field
    group by rollup(field),
    group by rollup(field1, field2),
    group by cube(field1, field2),
    group by grouping sets((), field1, field2)

    select region, avg(production) from t_oil group by region;
    select region, avg(production) from t_oil group by rollup(region)
    select region, country, avg(production) from t_oil where country in (‘USA’,’Canada’,’Iram’,’Oman’) group by rollup(region, country)
    select region, country, avg(production) from t_oil where country in (‘USA’,’Canada’,’Iran’,’Oman’) group by cube(region,country)
    select region, country, avg(production) from t_oil where country in (‘USA’,’Canada’,’Iran’,’Oman’) group by grouping sets((),region,country)

  2. 組合分組集 與 filter(where condition)

    select region, avg(production) as all, avg(production) filter(where year<1990) as old, avg(production) filter(where year >=1990) as new from t_oil group by rollup(region)

有序集 WITHIN GROUP

  1. 中位數
    select region, percentile_disc(0.5) within group (order by production) from t_oil group by 1;
    select region, percentile_disc(0.5) within group (order by production) from t_oil group by rollup(1);

  2. 有序集與分組集一起使用
    select region, percentile_disc(0.5) within group (order by production) from t_oil group by rollup(1)

HAVING從句

1
2
3
4
5
6
7
SELECT count(*), town 
FROM customer 
GROUP BY town HAVING count(*) > 1;

Count(DISTINCT 列名)
SELECT count(DISTINCT town) AS "distinct", count(town) AS "all" 
FROM customer;

循环语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
v_curr_wrkst_no := 'start'
while v_curr_wrkst_no <> 'end'
loop
select ...
if not fund then
    o_resultcode := '500';
    o_resultmsg  := '错误';
    return;
end if;
end loop;

for temprow in (
    select * from tablename where a=1
)
loop
if a <> b then 
    ....
    return;
end if;
end loop;

递归查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create table digui_test(id int , pid int , name varchar(10)); 
insert into digui_test values(2 , 0 , 'a'); 
insert into digui_test values(1 , 0 , 'b'); 
insert into digui_test values(3 , 2 , 'c');  
insert into digui_test values(4 , 2 , 'd') ; 
insert into digui_test values(5 , 2 , 'e');  
insert into digui_test values(6 , 2 , 'f') ; 
insert into digui_test values(7 , 3 , 'g');  
insert into digui_test values(8 , 3 , 'h') ; 
insert into digui_test values(9 , 4 , 'i');  
insert into digui_test values(10 , 5 , 'j') ; 
insert into digui_test values(11 , 7 , 'k');  
insert into digui_test values(12 , 2 , 'l') ; 
insert into digui_test values(13 , 9 , 'm');  
insert into digui_test values(14 , 9 , 'n') ; 
insert into digui_test values(15 , 4 , 'o');  
1
2
3
4
5
6
7
8
9
10
--给出一个数据id号，求出其下所有节点的信息
begin;
with RECURSIVE t as
(
select a.id,a.name,a.pid from digui_test a where id=3
 union all
select k.id,k.name,k.pid  from digui_test k , t c where c.id = k.pid
 )
select id,name,pid from t;
end;
1
2
3
4
5
6
7
8
9
10
--求出树深度，路径，是否为环数据
begin;
with RECURSIVE t(id,name,pid,depth,path,cycle) as
(
select a.id,a.name,a.pid,1,array[a.id],false from digui_test a where id=3
union all
select k.id,k.name,k.pid,c.depth+1,path||k.id,k.id=any(path) from digui_test k , t c where c.id = k.pid and not cycle
)
select * from t;
end;

Explain

select *
from sfc_scan_master_params_d
where request_id in (
select t2.request_id
from del_code_info t1
inner join sfc_scan_master_params t2 on t1.bar_uuid = t2.bar_uuid
where del_lot = ‘5’
);

Gather (cost=9423.40..36679638.18 rows=439194 width=71)
Workers Planned: 2
-> Parallel Hash Semi Join (cost=9423.40..36679638.18 rows=182998 width=71)
Hash Cond: (sfc_scan_master_params_d.request_id = (t2.request_id)::text)
-> Parallel Seq Scan on sfc_scan_master_params_d (cost=0.00..34446288.70 rows=846434370 width=71)
-> Parallel Hash (cost=9300.61..9300.61 rows=9823 width=38)
-> Nested Loop (cost=48.51..9300.61 rows=9823 width=38)
-> Parallel Bitmap Heap Scan on del_code_info t1 (cost=47.95..2054.67 rows=1178 width=8)
Recheck Cond: ((del_lot)::text = ‘5’::text)
-> Bitmap Index Scan on indx_del_code_04 (cost=0.00..47.45 rows=2002 width=0)
Index Cond: ((del_lot)::text = ‘5’::text)
-> Index Scan using idx_sfc_scan_master_params_01 on sfc_scan_master_params t2 (cost=0.57..6.07 rows=8 width=46)
Index Cond: ((bar_uuid)::text = (t1.bar_uuid)::text)

set enable_seqscan = off;
set enable_parallel_hash = off;
Nested Loop (cost=9343.06..363482435.95 rows=439198 width=71)
-> HashAggregate (cost=9342.36..9509.35 rows=16699 width=38)
Group Key: (t2.request_id)::text
-> Gather (cost=48.51..9300.61 rows=16699 width=38)
Workers Planned: 1
-> Nested Loop (cost=48.51..9300.61 rows=9823 width=38)
-> Parallel Bitmap Heap Scan on del_code_info t1 (cost=47.95..2054.67 rows=1178 width=8)
Recheck Cond: ((del_lot)::text = ‘5’::text)
-> Bitmap Index Scan on indx_del_code_04 (cost=0.00..47.45 rows=2002 width=0)
Index Cond: ((del_lot)::text = ‘5’::text)
-> Index Scan using idx_sfc_scan_master_params_01 on sfc_scan_master_params t2 (cost=0.57..6.07 rows=8 width=46)
Index Cond: ((bar_uuid)::text = (t1.bar_uuid)::text)
-> Index Scan using idx_sfc_scan_master_params_d_01 on sfc_scan_master_params_d (cost=0.70..21364.42 rows=40173 width=71)
Index Cond: (request_id = (t2.request_id)::text)

  1. 執行查詢時，內存調用模型
  2. 流複製的配置和原理