PostgreSQL常用函数

谁践踏了优雅 2022-08-24 11:50 242阅读 0赞

1，

with t as (select poi.mid poi\_id, link.link\_id link\_id from   poi, nav\_link\_graphy link where poi.kind not like '%BF00%' and   poi.kind not like '%BB86%' and   poi.kind not like '%2F0105%' and   poi.kind not like '%8401%' and   poi.kind not like '%8085%' and  
   st\_dwithin(poi.geometry::geography, link.geom, 3.0))  select 'poi' tn, t.poi\_id fid, string\_agg(t.link\_id::varchar, '|') r\_\_agg\_\_fid, 'nav\_link' r\_\_agg\_\_tn   from t group by t.poi\_id;

注：string\_agg聚合函数，可以放在select之后，且语句中有groupby分组，对于多行多个要素之间存在关系,需要一起报出时

又如：

select n1.name\_id,string\_agg(n2.name\_id,'|') r\_agg\_fid, 'nav\_name' as r\_agg\_tn from nav\_namen1,nav\_name n2 where n1.name\_py=n2.name\_py and

n1.name\_ch<>n2.name\_chand n1.ctid<n2.ctid group by n1.name\_id

2，

SELECT 'NAV\_LINK' as tn,nav\_link.link\_id as fid,'sp\_class取值错误' err\_msg from nav\_link WHERE sp\_class is not null and sp\_class <>'' and substring(sp\_class,1,1) not similar to '\[012345678\]'  
UNION ALL  
SELECT 'NAV\_LINK' as tn,nav\_link.link\_id as fid,'f\_speed取值错误' err\_msg from nav\_link WHERE f\_speed is not null and f\_speed !='' and f\_speed not similar to '(\\s|\[0-9\]\*),(\\s|0|1|2|3|4|5),(\\s|0|1)'  
UNION ALL  
SELECT 'NAV\_LINK' as tn,nav\_link.link\_id as fid,'t\_speed取值错误' err\_msg from nav\_link WHERE t\_speed is not null and t\_speed !='' and t\_speed not similar to '(\\s|\[0-9\]\*),(\\s|0|1|2|3|4|5),(\\s|0|1)'

3，

WITH t1 as(SELECT s\_nid as nid from nav\_link UNION ALL SELECT e\_nid as nid FROM nav\_link),  
t2 as (SELECT nid from t1 GROUP BY nid HAVING "count"(1)=1)  
SELECT 'NAV\_LINK' as tn,nav\_link.link\_id as fid from nav\_link WHERE nav\_link.s\_nid in (SELECT nid from t2) AND  
nav\_link.e\_nid in (SELECT nid from t2)

4，

SELECT 'NAV\_BRANCH' as tn,branch\_id as fid from NAV\_BRANCH WHERE not EXISTS(  
SELECT 1 from nav\_link WHERE nav\_link.link\_id = nav\_branch.in\_linkid)

5，

SELECT link\_id from nav\_link WHERE string\_to\_array(nav\_link.form,';')@>array\['50'\]

注：@>array\['50'\]表示包含

6，

SELECT 'NAV\_LINK' as tn,link\_id as fid FROM NAV\_LINK  
WHERE string\_to\_array(form,';')&&array\['0','1','2','10','11','12','13','14','15','16','17','18','20','21','22','23','24','30','31','32','33','34','35','36','37','38','39','50','51','52','54','60','80'\]=false

注：&&表示有交集.

例如：@>array\['33','50'\]  ---这个是同时包含两个才返回true  
string\_to\_array(form,';')&&array\['33','50'\]   --&&是包含其中一个就满足  
注意他们的反面：@>array\['33','50'\]=false，这表示不包含其中一个就返回false，而&&array\['33','50'\]=false，这表示同时不包含两个

7，

SELECT 'NAV\_REALIMAGE' as tn,REAL\_ID as fid,'image\_type取值错误' err\_msg from NAV\_REALIMAGE where image\_type::VARCHAR not SIMILAR TO '\[01\]'

8，

SELECT 'NAV\_REALIMAGE' as tn,REAL\_ID as fid from NAV\_REALIMAGE where arr\_code is not null and arr\_code!='' and real\_code is not null and real\_code !=''  
and ("substring"(arr\_code from 2 for "char\_length"(arr\_code)-1) != "substring"(real\_code from 2 for "char\_length"(real\_code))-1)

注：substring(field,start,length) ，field为字段，start为开始点（从1开始），length为长度

9，

selectst\_astext(st\_pointN(geom,5)) as a\_location from nav\_link

注：请转为wkt字符串,比如某个link的第5个形状点处存在折角较小,比较尖,一般在此处会创建一个点几何

10，

"该连接点驶出的道路至少有条LinkID在NAV\_SLOPE表存在，否则报错。"

with a as(SELECT ns.node\_id nid, "string\_agg"(sln.link\_id::VARCHAR,'|') lidfrom nav\_slope ns,shd\_link\_node sln WHERE ns.node\_id = sln.node\_id and ns.link\_id <>sln.link\_id GROUP BY ns.node\_id )  
,b as(SELECT a.nid nid,regexp\_split\_to\_table(a.lid,'\\|')rid from a )  
SELECT 'nav\_slope' tn, a.nid fid, a.lid as m\_\_1\_\_link\_id from b,a,nav\_slope nsp WHERE a.nid=b.nid and b.rid=nsp.link\_id GROUP BY a.nid,a.lid HAVING "count"(1)=0

"string\_agg"先聚合，再使用regexp\_split\_to\_table将聚合在一起使用‘|’分隔的一条记录，拆分成多条记录存于子表中，也可以使用regexp\_split\_to\_array函数拆分成数组，array\_length求得数组长度

11，

select \* from a where a.nid>100 or a.lid<10000   ；       select \* from a,b where a.nid>b.nid  union  select \* from a,b where a.lid< b.lid

or单表内使用时效率还好，多表连接时使用union替换

12，

select f1,f2,... ,分析函数（例如row\_num(),count(1)）over(partition by f3 oder by f4)  from table;

与group by 区别：1，select f1,f2,... ,后面字段不限制   2，count计数结果在一组内的每一条记录均有一个值，而group by会合成一条记录

例句：with a as (  
select DETAIL\_ID,group\_id,link\_id,count(1)over(partition by p.DETAIL\_ID,p.group\_id order by SEQ\_NUM) m\_seq,  
    row\_number()over(partition by p.DETAIL\_ID,p.group\_id order by SEQ\_NUM) seq from  NAV\_RESTRICTION\_PASS p ),  
link as (  
select t1.DETAIL\_ID, t1.link\_id l1,t2.link\_id l2 from a t1, a t2 where t1. DETAIL\_ID=t2.DETAIL\_ID and t1.group\_id=t2.group\_id and t1.seq+1=t2.seq  
union all  
select a.detail\_id, a.link\_id l1, d.out\_linkid l2 from NAV\_RESTRICTION\_DETAIL d, a where d.detail\_id=a.detail\_id and a.seq=a.m\_seq  
union all  
select a.detail\_id, r.in\_linkid l1, a.link\_id  l2 from NAV\_RESTRICTION r,NAV\_RESTRICTION\_DETAIL d, a where r.RESTRIC\_ID=d.RESTRIC\_ID and d.detail\_id=a.detail\_id and a.seq=1)  
select l.detail\_id, l.l1,l.l2 from link l where not exists (select 1 from shd\_link\_go\_link s where l.l1=s.in\_link\_id and l.l2=s.out\_link\_id)

13,

如果一条弧段上制作了坡度信息，则该点连接的其他弧段中至少有一条link上也应该存在坡度信息，否则报log

with b as(SELECT sln2.node\_id,sln2.link\_id,ns.slope\_id fid from nav\_slope ns,shd\_link\_node sln,shd\_link\_node sln2  
WHERE ns.link\_id=sln.link\_id and ns.node\_id= sln.node\_id and sln2.node\_id=sln.node\_id and sln2.link\_id<>sln.link\_id),  
c as(SELECT b.fid,string\_agg(DISTINCT b.link\_id,'|') link\_id,string\_agg(DISTINCT b.node\_id,'|') node\_id FROM b GROUP BY b.fid)  
(  
SELECT 'nav\_slope' tn,c.fid fid from c  
EXCEPT  
SELECT 'nav\_slope' tn,c.fid fid FROM c WHERE EXISTs(SELECT 1 from nav\_slope WHERE string\_to\_array(c.link\_id,'|')@>string\_to\_array(nav\_slope.link\_id,' '))  
)

注意：至少的表达，先求出包含坡度信息的表，然后求差集即错误的

14,查找NAV\_LINK\_LIMIT中TYPE=4的道路两端连接的弧段，如果一个端点连接的弧段只能进入当前节点或只能退出当前节点，则该点连接道路的TYPE！=4则报错

with sg as ( select ln.link\_id, node\_id from shd\_link\_node ln, NAV\_LINK\_LIMIT ll where ln.link\_id=ll.link\_id and ll.type=4 ),  
 tp as (  
 select  
 case when dir=2 and s\_e=1 then 0  
      when dir=2 and s\_e=0 then 1  
      when dir=3 and s\_e=1 then 1  
      when dir=3 and s\_e=0 then 0  
      when dir=1 then 2  
  end mark, ln.link\_id,ln.node\_id, count(1)over(partition by ln.node\_id) gc  
  from shd\_link\_node ln  where exists (select 1 from sg where ln.node\_id=sg.node\_id and ln.link\_id<>sg.link\_id))  
,fx as ( select link\_id, node\_id, mark, count(1)over(partition by node\_id, mark) mc, gc  from tp )  
select 'nav\_link' tn,link\_id fid from fx where mc=gc and mark in (0,1)

15,

获取两个NODE的坐标，如果两个点(图廓点除外)的距离小于3则报错。程序检查时不检查的情况有： 1、排除当立交点之间都是Node点的情况。 4、排除图幅接边的图廓点

with t as (select z1.link\_id lid1, z1.start\_end se1, z2.link\_id lid2, z2.start\_end se2 from NAV\_ZLEVEL\_LINK z1, NAV\_ZLEVEL\_LINK z2  
 where z1.start\_end>0 and z2.start\_end>0  and z1.table\_name='NAV\_LINK' and z2.table\_name=z1.table\_name  and z1.zlevel\_id=z2.zlevel\_id),  
n as (select case t.se1 when 1 then l1.s\_nid else l1.e\_nid end nd1,  
 case t.se2 when 1 then l2.s\_nid else l2.e\_nid  end nd2  from nav\_link l1, t, nav\_link l2  
where t.lid1=l1.link\_id and t.lid2=l2.link\_id),  
sn as (select case when nd1>nd2 then nd1 else nd2 end id1, case when nd1>nd2 then nd2 else nd1 end id2 from n )  
SELECT 'nav\_node' tn,a.node\_id as fid,'nav\_node' as r\_\_1\_\_tn,b.node\_id  as r\_\_1\_\_fid   from SHD\_NODE\_GRAPHY a,SHD\_NODE\_GRAPHY b    
WHERE st\_dwithin(a.geom,b.geom,3)   and a.node\_id>b.node\_id  and a.adjoin\_nid<>b.node\_id   
and not exists(select 1 from sn where a.node\_id=sn.id1 and b.node\_id=sn.id2);

16，

pg存储过程输出（调试）语句,RAISE NOTICE

CREATE FUNCTION somefunc() RETURNS integer AS $$  
    DECLARE  
       quantity integer := 30;  
    BEGIN  
       RAISE NOTICE 'Quantity here is %', quantity;      --在这里的数量是30  
       quantity := 50;  
       --  
       -- 创建一个子块  
       -- [http://www.qinglvfenzu.com][http_www.qinglvfenzu.com]  
       DECLARE  
          quantity integer := 80;  
       BEGIN  
          RAISE NOTICE 'Quantity here is %', quantity;   --在这里的数量是80  
       END;  
       RAISE NOTICE 'Quantity here is %', quantity;      --在这里的数量是50     
       RETURN quantity;  
    END;  
    $$ LANGUAGE plpgsql;  
    \#执行该函数以进一步观察其执行的结果。  
    postgres=\# select somefunc();  
    NOTICE:  Quantity here is 30  
    NOTICE:  Quantity here is 80  
    NOTICE:  Quantity here is 50  
     somefunc  
    ----------  
           50  
    (1 row)

[http_www.qinglvfenzu.com]: http://www.qinglvfenzu.com