postgresql----聚合函数

最新推荐文章于 2025-04-20 15:21:41 发布
最新推荐文章于 2025-04-20 15:21:41 发布
阅读量283 收藏 1
点赞数
CC 4.0 BY-SA版权
文章标签： 数据库 json 数据结构与算法
原文链接：http://www.cnblogs.com/alianbog/p/5674838.html
本文详细介绍了SQL中的聚合函数，包括通用聚合函数、统计聚合函数和顺序集聚合函数等，并通过实例展示了如何使用这些函数进行数据汇总和分析。

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

聚合函数是从一组输入中计算出一个结果的函数。

测试表

test=# \d tbl_test
          Table "public.tbl_test"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 name   | character varying(32) | 
 sex    | character varying(1)  | 

test=# select * from tbl_test;
 id | name | sex 
----+------+-----
  1 | 张三 | m
  2 | 李四 | m
  3 | 王五 | f
(3 rows)

 

通用聚合函数

函数参数类型返回类型描述示例结果
array_agg(expression)任意非数组类型参数类型的数组将入参包括NULL连接成一个数组select array_agg(id) from tbl_test;{1,2,3}
array_agg(expression)任意数组类型入参数据类型

将入参数组连接成更高维度的数组，输入的数组必须是相同的维度，且不允许是空或NULL

select array_agg(array['b','c','a']); {{b,c,a}}
avg(expression) smallint, int, bigint, real, double precision, numeric, or interval整形返回numeric，浮点型返回double precision，其他和入参类型相同平均值select avg(id) from tbl_test;2.0000000000000000
bit_and(expression)smallint, int, bigint, or bit和入参类型相同所有非NULL输入值的按位与，如果全为NULL则返回NULLselect bit_and(id) from tbl_test;0
bit_or(expression)smallint, int, bigint, or bit和入参类型相同所有非NULL输入值的按位或，如果全为NULL则返回NULLselect bit_or(id) from tbl_test;3
bool_and(expression)boolbool如果输入全是true则返回true，否则为falseselect bool_or(id::bool) from tbl_test;t
bool_or(expression)boolbool如果输入至少一个true，则返回true，否则返回falseselect bool_or((id-1)::bool) from tbl_test;t
count(*) bigint输入行数select count(*) from tbl_test;3
count(expression)anybigint输入行中非NULL的行数select count(id) from tbl_test;3
every(expression)boolbool功能同bool_and  
json_agg(expression)anyjson将输入聚合成一个json数组select json_agg(id) from tbl_test;[1, 2, 3]
jsonb_agg(expression)anyjsonb将输入聚合成一个json数组select jsonb_agg(id) from tbl_test;[1, 2, 3]
json_object_agg(name,value)(any, any)json将输入组成一个key/value对的json对象select json_object_agg('a','one');{ "a" : "one" }
jsonb_object_agg(name,value)(any, any)jsonb将输入组成一个key/value对的json对象select jsonb_object_agg('a','one');{"a": "one"}
max(expression)  输入最大值

select max(id) from tbl_test;

3
min(expression)  输入最小值select min(id) from tbl_test;1
string_agg(expression,delimiter)(text, text) or (bytea, bytea)同参数类型将输入使用delimiter连接成一个textselect string_agg(name,',') from tbl_test; 张三,李四,王五
sum(expression)smallint, int, bigint, real, double precision, numeric, interval, or money 输入和select sum(id) from tbl_test;6
xmlagg(expression)xmlxml 请参考xml类型及其函数 

 

修改表

test=# alter table tbl_test add column id1 int default 1;
ALTER TABLE
test=# select * from tbl_test;
 id | name | sex | id1 
----+------+-----+-----
  1 | 张三 | m   |   1
  2 | 李四 | m   |   1
  3 | 王五 | f   |   1
(3 rows)

 

统计聚合函数

函数参数类型返回类型描述示例结果
corr(Y, X)double precisiondouble precision相关系数select corr(id,id) from tbl_test;1
covar_pop(Y, X)double precisiondouble precision总体协方差select covar_pop(id,id) from tbl_test;0.666666666666667
covar_samp(Y, X)double precisiondouble precision样本协方差select covar_samp(id,id1) from tbl_test;0
regr_avgx(Y, X)double precisiondouble precision自变量平均值(sum(X)/N)select regr_avgx(id,id1) from tbl_test;1
regr_avgy(Y, X)double precisiondouble precision因变量平均值(sum(Y)/N)select regr_avgy(id,id1) from tbl_test;2
regr_count(Y, X)double precisionbigint两个参数都不为NULL的行数 select regr_count(id,id1) from tbl_test;3
regr_intercept(Y, X)double precisiondouble precision根据所有输入点(X,Y)利用最小二乘法计算一个线性方程式。然后返回该直线的Y轴截距select regr_intercept(id,id) from tbl_test;0
regr_r2(Y, X)double precisiondouble precision相关系数平方select regr_r2(id,id) from tbl_test;1
regr_slope(Y, X)double precisiondouble precision根据所有输入点(X,Y)利用最小二乘法计算一个线性方程式。然后返回该直线的斜率select regr_slope(id,id) from tbl_test;1
regr_sxx(Y, X)double precisiondouble precisionsum(X^2) - sum(X)^2/N select regr_sxx(id,id) from tbl_test;2
regr_sxy(Y, X)double precisiondouble precisionsum(X*Y) - sum(X) * sum(Y)/N select regr_sxy(id,id) from tbl_test;2
regr_syy(Y, X)double precisiondouble precisionsum(Y^2) - sum(Y)^2/N select regr_syy(id,id) from tbl_test;2
stddev(expression)

smallint, int, bigint, real,

double precision, or numeric

double precision for floating-point arguments,

otherwise numeric

同stddev_samp  
stddev_pop(expression)

smallint, int, bigint, real,

double precision, or numeric

double precision for floating-point arguments,

otherwise numeric

总体标准差select stddev_pop(id) from tbl_test;0.81649658092772603273
stddev_samp(expression)

smallint, int, bigint, real,

double precision, or numeric

double precision for floating-point arguments,

otherwise numeric

样本标准差select stddev_samp(id) from tbl_test;1.00000000000000000000
variance(expression)

smallint, int, bigint, real,

double precision, or numeric

double precision for floating-point arguments,

otherwise numeric

同var_samp  
var_pop(expression)

smallint, int, bigint, real,

double precision, or numeric

double precision for floating-point arguments,

otherwise numeric

总体方差select var_pop(id) from tbl_test;0.66666666666666666667
var_samp(expression)

smallint, int, bigint, real,

double precision, or numeric

double precision for floating-point arguments,

otherwise numeric

样本方差select var_samp(id) from tbl_test;1.00000000000000000000
test=# insert into tbl_test values (2,'ww','f');
INSERT 0 1
test=# select * from tbl_test;
 id | name | sex | id1 
----+------+-----+-----
  1 | 张三 | m   |   1
  2 | 李四 | m   |   1
  3 | 王五 | f   |   1
  2 | ww   | f   |   1
(4 rows)

 

顺序集聚合函数

函数直接参数类型聚合参数类型返回类型描述示例结果
mode() WITHIN GROUP (ORDER BYsort_expression) 任意可排序类型同排序类型

返回最频繁的输入值（如果有

多个同样频繁的结果，则返回第一个）

select mode() within group (order by id) from tbl_test;2
percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression)double precisiondouble precisionor interval同排序类型continuous percentile: returns a value corresponding to the specified fraction in the ordering, interpolating between adjacent input items if neededselect percentile_cont(0.25) WITHIN GROUP (ORDER BY id) from tbl_test;1.75
percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression)double precision[]double precisionor intervalarray of sort expression's typemultiple continuous percentile: returns an array of results matching the shape of the fractionsparameter, with each non-null element replaced by the value corresponding to that percentile  
percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression)double precisionany sortable typesame as sort expressiondiscrete percentile: returns the first input value whose position in the ordering equals or exceeds the specified fraction  
percentile_disc(fractions) WITHIN GROUP (ORDER BY sort_expression)double precision[]any sortable typearray of sort expression's typemultiple discrete percentile: returns an array of results matching the shape of the fractions parameter, with each non-null element replaced by the input value corresponding to that percentile  

转载于:https://www.cnblogs.com/alianbog/p/5674838.html

在 PostgreSQL 中，如何处理大数据量的聚合操作？
发现生活，分享智慧，一起成长！
07-07 1227
处理 PostgreSQL 中的大数据量聚合操作需要综合考虑数据库设计、查询语法的选择、分区、并行查询、临时表和视图的使用以及性能优化的技术。通过合理地应用这些方法，并根据具体的业务需求和数据特点进行调整，可以显著提高大数据聚合操作的性能和效率。实际应用中，需要结合具体的数据特点和业务场景来选择最适合的方法，并不断进行测试和优化，以达到最佳的性能效果。请注意，以上示例代码和讨论仅为一般性的指导，实际应用中的数据和需求可能会有所不同，需要根据具体情况进行适当的调整和优化。🎉相关推荐🍅关注博主🎗️。
postgresql-聚合函数string_agg、array_agg的妙用
此后如竟没有炬火我便是唯一的光
01-04 1万+
string_agg(ex1, d1):直接把一个表达式变成字符串 如果需要对ord进行分组，但是num值要在一个格子里： SELECT ord,string_agg(num||'','-' ORDER BY num) from cx.over_test group by ord; array_agg(e1):把表达式变成一个数组 一般配合 array_to_string() 函数使用; select ord, array_agg(num ORDER BY num) from cx.over_test
在 Postgres 中使用聚合函数
网络技术联盟站
08-13 1231
聚合函数在 SQL 查询中用于计算和汇总数据，以便于分析和报告。它们通常与GROUP BY子句结合使用，将数据分组后对每组进行计算。聚合函数的结果是一个单一的值，代表了分组数据的汇总信息。：要应用的聚合函数及其参数列。table_name：要查询的表名。condition：可选的条件子句，用于筛选数据。：用于分组的列，按该列对数据进行分组计算。
PostgreSQL聚合函数
yeyaozhifengqi的博客
04-24 1359
SQL 函数还有一类，叫做聚合（或聚集、分组）函数，它是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。
PostgreSQL笔记-聚合函数
黑发少年1994的博客
11-23 554
row_number -- 分组加编号 sql： select b.eps400, b.max_report_id, row_number() over (partition by b.eps400 order by b.max_report_id desc) nums from ( select eps400, transfer_num, max(report_id) max_report_id from t_report_num_seat_bind_approve a
PostgreSQL 聚合函数讲解 - 1 常用聚合函数
weixin_33939843的博客
12-14 2650
PostgreSQL支持较多的聚合函数, 以PostgreSQL 9.4为例, 支持例如一般性的聚合, 统计学科的聚合, 排序集聚合, 假象集聚合等. 本文将对一般性聚合函数举例说明其功能和用法. 聚合函数有哪些，见 : http://www.postgresql.org/docs/9.4/static/functions-aggregate.html ...
postgresql-9.6.19-1-linux-x64-binaries.tar.gz
10-29
1. **并行查询**：9.6版本引入了并行查询功能，允许在扫描、聚合、排序和哈希联接等操作中利用多核处理器，显著提高了大数据量处理的效率。 2. **更快的索引访问**：改进了B-Tree索引的性能，尤其是对于有大量唯一...
postgresql-9.5.0-1-windows 32位
09-27
7. **SQL标准兼容性提升**：PostgreSQL一直致力于遵循SQL标准，9.5版本进一步增强了对标准的支持，包括窗口函数和JSON函数的增强。 8. **性能优化**：包括改进的VACUUM操作，减少了磁盘空间占用，并提高了查询速度...
postgresql-12.3-1-windows-x64-binaries.zip
05-20
其中，改进了并行查询性能，特别是对于复杂的联接操作和聚合查询。此外，此版本还引入了对大对象的并行索引扫描，提高了处理大量数据时的速度。同时，它也增强了存储过程的编写体验，支持更多的SQL标准，如窗口函数...
postgresql-9.6.3.tar.gz 下载
07-03
7. **SQL查询**：遵循SQL标准，PostgreSQL支持SELECT、INSERT、UPDATE、DELETE等基本操作，还支持复杂的JOIN、子查询、窗口函数和聚合函数。 8. **索引与性能优化**：通过`CREATE INDEX`语句创建索引，提升查询效率...
timescaledb-postgresql-13_2.3.0-windows-amd64.zip
08-10
此外，TimescaleDB还提供了强大的聚合函数，如HYPERTABLE，用于快速计算时间窗口内的聚合操作。 4. **应用场景**： TimescaleDB适用于需要大量收集和分析时间敏感数据的场景，如物联网(IoT)设备数据、金融交易记录...
PostgreSQL 聚集函数
hyoka的博客
08-26 2270
0.最近就是到处填坑 pgSQL 中文文档 聚集函数 1.聚集函数也叫聚合函数 聚合操作之高效这里就不多谈了。 其特点在 窗口函数的文章中 做了些比较，所以…也不多谈了。 结合文档来说，聚集函数有一些巧思的，剩下的基本都是些较为专业的科学统计api，带过了。(有点“随笔”的感觉了…) 2. 一些场景 having 中使用聚合函数作为过滤条件 较where子句的逐行过滤来说，效率更优 grouping、sets、cube、rollup 用作分组集(分组聚集)，也就是将分组字段以组合的方式罗列出来 借助arra
详解PostgreSQL聚合函数： 精准统计数据，提升效率 附代码示例
陈书予
03-19 1万+
PostgreSQL 是一款强大的开源数据库管理系统，具有丰富的特性和功能。其中，聚合查询是一项非常重要的功能之一。通过聚合查询，我们可以在数据库中对数据进行统计和分析，获取有用的信息。在本文中，我们将深入探讨 PostgreSQL 的聚合查询功能，包括常见的聚合函数、GROUP BY 子句、HAVING 子句、CASE 表达式、聚合函数进行数据分析、WITH 子句、窗口函数、GROUPING SETS、CUBE、ROLLUP 和过滤器等，以及相关的示例代码。
postgresql 系列之聚合函数string_agg 和 array_agg
热门推荐
uzong
11-11 1万+
本文是《postgresql实战》的读书笔记；具体可以参考这本书相关章节。 一、 string_agg 函数 语法格式如下： string_agg(expression,delimiter) string_agg 函数能将结果集某个字段的所有行连接成字符串，并以delimiter分隔符分隔，expression 表示要处理的字符类型数据； 案例： CREATE TABLE city( coun...
PG系列5-SQL高级特性2——聚合函数和窗口函数
Ghost_0110
07-15 4299
文章目录1、聚合函数1.1 string_agg函数1.2 array_agg函数 1、聚合函数 聚合函数可以对结果集进行计算，常用的聚合函数有 avg（）、sum（）、min（）、max（）、count（）等，本节 将介绍PostgreSQL两个特殊功能的聚合函数并给出测试示例。 在介绍两个聚合函数之前，先来看一个应用场景，假如一 张表有以下数据： 中国 台北 中国 香港 中国 上海 日本 东京 日本 大阪 要求得到如下结果集： 中国 台北，香港，上海 日本 .
Postgresql的高级聚合函数使用实例(超详细)
全栈技术分享，项目分享，资料分享
06-16 2103
高级聚合函数是PostgreSQL中一类非常强大的聚合函数，可以通过自定义的方式进行一些高级的数据计算，例如按照给定的索引或条件计算平均值、累加值或者其他的复杂的汇总值。这样，我们就使用了两个高级聚合函数来计算了一些高级的汇总指标。这些函数借助自定义的排序和计算方式，可以计算各种复杂的汇总指标，让我们更好地了解和分析数据。可以看到，结果中累加销售额的计算是正确的，每个日期的累加值都是前面日期的总和加上当前日期的销售额。现在，假设需要计算每个日期的累加销售额，以及每个日期的销售额平均值。子句对结果进行排序。
postgres 聚集函数编写 教程
DeleteElf的专栏
11-28 2135
CREATE AGGREGATE 名称 CREATE AGGREGATE -- 定义一个新的聚集函数 语法 CREATE AGGREGATE name ( input_data_type [ , ... ] ) ( SFUNC = sfunc, STYPE = state_data_type [ , FINALFUNC = ffunc ]
Postgresql几个常用的聚合函数
rocklee的专栏
04-20 148
【代码】Postgresql几个常用的聚合函数。
PostgreSQL里实现计算多个数字的排列组合
小怪兽的博客
02-19 798
在进行排列组合的时候，每一次需要知道是否有重复的值，并过滤出已经排列过的值。这个可以创建支持可变参数的函数来实现。下边的函数用到了聚合判断，并且可变参数使用variadic标记的数组。然后下边使用创建的判断是否有重复数据的函数以及使用SQL实现获取所有的排列组合。根据4的阶乘可以得到，总共应该有24种，阶乘可以使用factorial函数。然后是如何使用这个函数结合查询语句对一组数据进行排列组合。例如，一张表里有1234这四个值。想使用四个值做排列组合。先创建一个测试的表，里边存放要进行排列组合的数据。
PostgreSQL Z-Score
最新发布
05-21
### 计算 Z-Score 的方法 在 PostgreSQL 数据库中，可以通过 SQL 查询来实现 Z-Score 的计算。Z-Score 是一种标准化技术，用于衡量某个数值距离均值的标准差数。其公式如下： \[ Z = \frac{x - \mu}{\sigma} \] 其中： - \( x \) 表示单个数据点， - \( \mu \) 表示总体平均值， - \( \sigma \) 表示标准偏差。 以下是具体的实现方式： #### 使用窗口函数计算 Z-Score 可以利用 PostgreSQL 提供的窗口函数 `AVG()` 和 `STDDEV_POP()` 来分别计算均值和标准差，并通过嵌套查询完成 Z-Score 的计算[^2]。 ```sql WITH stats AS ( SELECT value, AVG(value) OVER () AS mean_value, STDDEV_POP(value) OVER () AS std_deviation FROM your_table ) SELECT value, (value - mean_value) / NULLIF(std_deviation, 0) AS z_score FROM stats; ``` 在此代码片段中： 1. 首先创建了一个名为 `stats` 的临时视图，该视图包含了原始数据 (`value`)、整体均值 (`mean_value`) 和标准差 (`std_deviation`)。 2. 接着，在外部查询中计算了每个数据点相对于均值的偏离程度并除以标准差得到 Z-Score 值。这里使用了 `NULLIF` 函数防止分母为零的情况发生[^2]。 #### 创建自定义聚合函数 如果需要频繁执行此类操作，则可考虑构建一个专门针对此目的设计的用户定义聚合函数(UDA)[^2]: ```plpgsql CREATE OR REPLACE FUNCTION calc_zscore(float8[], float8[]) RETURNS TABLE(mean float8, stddev float8, values float8[]) AS $$ BEGIN RETURN QUERY SELECT avg(v), stddev_pop(v), array_agg(v ORDER BY i) INTO STRICT mean, stddev, values FROM unnest($1,$2) WITH ORDINALITY t(v,i); END;$$ LANGUAGE plpgsql IMMUTABLE; -- Example Usage: SELECT * FROM calc_zscore(array(SELECT value FROM mydata)); ``` 以上脚本定义了一种新的聚合形式——`calc_zscore`—它接受两组浮点型数组作为输入参数，并返回三部分结果：样本集合的整体平均值(`mean`)、标准方差(`stddev`)以及重新整理后的原始数据列表(`values`)。注意这里的例子仅展示概念验证用途的实际部署可能还需要进一步调整优化性能等方面因素的影响[^2]。 --- ### 注意事项 当处理大数据集时，应特别留意内存消耗情况因为某些复杂运算可能会占用较多资源。此外还需确保所选算法适合特定应用场景下的精度需求等问题存在差异性因此建议依据实际情况做出适当权衡取舍决定最终实施方案。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值