postgresql function 返回查询结果

aileen5150

于 2021-05-28 11:20:52 发布

阅读量635

点赞数

分类专栏： postgresql 文章标签： postgresql

本文链接：https://blog.youkuaiyun.com/aileen5150/article/details/117356678

版权

postgresql 专栏收录该内容

10 篇文章

订阅专栏

此博客介绍了一个名为test_xxxx的创建或替换函数，它接受deduces_id、no和threshold参数，通过PL/pgSQL查询处理数据，计算并返回样本的聚合特征。函数首先从samples2a表中根据条件筛选数据，然后计算三个维度(min_x, min_y, min_z)和(max_x, max_y, max_z)，最后合并结果为包含id、ts、deduces_id、no和特征数组的数据表。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE OR REPLACE FUNCTION test_xxxx(in_deduces_id bigint,in_no bigint,threshold float)
RETURNS table(id bigint,ts TIMESTAMP,deduces_id bigint ,no bigint ,data float[]) AS $$
BEGIN
   RETURN QUERY
   with cte_x as (
   SELECT min(a.data[3]) as min_x,max(a.data[3]) as max_x from samples2 a where a.deduces_id = in_deduces_id and a.no=in_no
   ),
   cte_y as (
   SELECT min(a.data[2]) as min_y,max(a.data[2]) as max_y from samples2 a where a.deduces_id = in_deduces_id and a.no=in_no
   ),
   cte_z as(
   SELECT min(a.data[1]) as min_z,max(a.data[1]) as max_z from samples2 a where a.deduces_id = in_deduces_id and a.no=in_no
   ),
   cte_samples as (
   SELECT a.id,a.ts,a.deduces_id,a.no,a.data from samples2 a
   where a.deduces_id = in_deduces_id and a.no=in_no and a.data[5] >threshold
   )
   SELECT * from cte_samples union all
   (SELECT cte_samples.id,cte_samples.ts,cte_samples.deduces_id,cte_samples.no,array[cte_z.min_z,cte_y.min_y,cte_x.min_x] from cte_samples ,cte_x,cte_y,cte_z limit 1) union all
   (SELECT cte_samples.id,cte_samples.ts,cte_samples.deduces_id,cte_samples.no,array[cte_z.max_z,cte_y.max_y,cte_x.max_x] from cte_samples ,cte_x,cte_y,cte_z limit 1);
END;
$$
LANGUAGE plpgsql;

select * from test_xxxx(1,1,0.005);//