Postgresql几个常用的聚合函数

OK_boom

于 2025-04-20 15:21:41 发布

阅读量144

点赞数 1

CC 4.0 BY-SA版权

分类专栏： postgreSQL 语言设计文章标签：数据库开发

本文链接：https://blog.csdn.net/rocklee/article/details/147365878

postgreSQL 语言设计专栏收录该内容

33 篇文章

订阅专栏

将记录集的某个字段转为数组用array_agg

select array_agg(task_name) from dyna_flow_task dft

将记录集的某个字段拼接，用string_agg

select string_agg(task_name,',')  from dyna_flow_task

将数组转为记录集，用unnest

SELECT * from unnest(ARRAY[1, 2, 3, 4, 5]) AS value;

拼接两个数组

SELECT ARRAY[1, 2, 3] || ARRAY[4, 5, 6] AS merged_array;
或者
SELECT array_agg(val) 
FROM (SELECT unnest(ARRAY[1,2,3]) AS val
      UNION ALL
      SELECT unnest(ARRAY[4,5])) AS all_vals;

数组去重

select array_agg(distinct value)
from unnest(array[1,1,1,1,2,2,2,3,3]) value
group by value

a 数组中去除b数组的元素

select array_agg(distinct value.value)
from unnest(array[1,1,1,1,2,2,2,3,3]) value
left join (select * from unnest(array[2]) as value) v2 on v2.value=value.value
where v2.value is null
group by value.value