postgres存储过程开发-（重载函数、聚合函数）

addresstool

于 2024-04-17 11:25:55 发布

阅读量121

点赞数 3

文章标签： postgres 存储过程

本文链接：https://blog.csdn.net/u011024436/article/details/137865757

版权

本文介绍了在PostgreSQL中创建特定数据类型`t_point`和`t_point_kv`，以及如何操作这些类型创建表、插入数据、使用自定义聚合函数和按时间窗口或间隔查询数据的方法。

摘要由CSDN通过智能技术生成

创建t_point数据类型

CREATE TYPE t_point AS (
  time bigint,
  point point
);
CREATE TYPE t_point_kv AS (
  time bigint,
  point t_point
);

创建表

create table table1 (
   id int,
   tpt t_point
)

create table table2 (
   id int,
   guiji t_point[]
)

写入数据

insert into table1 
select 0,ROW(1698804407020,point'(116.39088,39.90763)')::t_point
union all 
select 1,ROW(1698804408000,point'(116.39188,39.90763)')::t_point
union all 
select 2,ROW(1698804409000,point'(116.39288,39.90763)')::t_point
union all 
select 3,ROW(1698804417000,point'(116.39388,39.90763)')::t_point
union all 
select 4,ROW(1698804427000,point'(116.39488,39.90763)')::t_point
union all 
select 5,ROW(1698804437000,point'(116.39588,39.90763)')::t_point
union all 
select 6,ROW(1698804447000,point'(116.39688,39.90763)')::t_point
union all 
select 7,ROW(1698804457000,point'(116.39788,39.90763)')::t_point
union all 
select 8,ROW(1698804467000,point'(116.39888,39.90763)')::t_point
union all 
select 1,ROW(1698804410000,point'(111.39188,39.90763)')::t_point
union all 
select 1,ROW(1698804412000,point'(112.39288,39.90763)')::t_point
union all 
select 1,ROW(1698804414000,point'(113.39388,39.90763)')::t_point
union all 
select 1,ROW(1698804416000,point'(114.39488,39.90763)')::t_point
union all 
select 1,ROW(1698804418000,point'(115.39588,39.90763)')::t_point
union all 
select 1,ROW(1698804437000,point'(116.39688,39.90763)')::t_point
union all 
select 1,ROW(1698804457000,point'(117.39788,39.90763)')::t_point
union all 
select 1,ROW(1698804488000,point'(118.39888,39.90763)')::t_point 
union all 
select 2,ROW(1698804486000,point'(110.39888,39.90763)')::t_point  
union all 
select 2,ROW(1698804487000,point'(111.39888,39.90763)')::t_point 
union all 
select 2,ROW(1698804484000,point'(116.39888,39.90763)')::t_point 
union all 
select 2,ROW(1698804489000,point'(114.39888,39.90763)')::t_point  
union all 
select 2,ROW(1698804481000,point'(113.39888,39.90763)')::t_point 
union all 
select 2,ROW(1698804484000,point'(111.39888,39.90763)')::t_point 


insert into table2
select id,t_point_agg(tpt) as tpts from table1 group by id;

创建聚合函数

CREATE OR REPLACE FUNCTION t_point_add(tpt t_point[],tm bigint,pt point) 
    RETURNS t_point[] AS 
$$ 
SELECT 
   array_append(tpt,ROW(tm,pt)::t_point)
$$ 
LANGUAGE SQL;
重载函数
CREATE OR REPLACE FUNCTION t_point_add(tpts t_point[],tpt t_point) 
    RETURNS t_point[] AS 
$$ 
SELECT 
   array_append(tpts,tpt)
$$ 
LANGUAGE SQL;

CREATE AGGREGATE t_point_agg(bigint,point) (
    SFUNC = t_point_add,
    STYPE = t_point[],
    INITCOND ='{}'
);
select id,t_point_agg((guiji).time,(guiji).point) as tpts from table1 group by id;

重载函数

CREATE AGGREGATE t_point_agg(t_point) (
    SFUNC = t_point_add,
    STYPE = t_point[],
    INITCOND ='{}'
);
select id,t_point_agg(guiji) as tpts from table1 group by id;

按照时间窗口查询（取第一个）,num为时间段

CREATE FUNCTION getTPointByWin(t_point[],bigint) RETURNS t_point[] AS $$
DECLARE
  s bigint := 0;
  tpt t_point;
  ks bigint[];
  res t_point[];
BEGIN
   FOREACH tpt IN ARRAY $1
   LOOP 
      s := tpt.time/$2;
      IF array_position(ks,s)>0 THEN
      else 
         ks = array_append(ks,s);
         res = array_append(res,tpt);
      END IF;
  END LOOP;
  return res;
END;
$$ LANGUAGE plpgsql;

例子：select * from table2;
在这里插入图片描述

select id,getTPointByWin(guiji,50000) from table2;
在这里插入图片描述

按照间隔次数取t_point

CREATE FUNCTION getTPointByTimes(t_point[],int) RETURNS t_point[] AS $$
DECLARE
  s bigint := 0;
  tpt t_point;
  ks bigint[];
  res t_point[];
BEGIN
   FOREACH tpt IN ARRAY $1
   LOOP 
      s := s + 1;
      IF mod(s,$2)=0 THEN
      else 
         res = array_append(res,tpt);
      END IF;
  END LOOP;
  return res;
END;
$$ LANGUAGE plpgsql;