PostgreSQL教程FG051-PG聚合函数进阶：自定义聚合函数基础
本文档风哥主要介绍PostgreSQL教程051相关内容。风哥教程参考PostgreSQL官方文档Server Administration, SQL Language内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
更多学习教程公众号风哥教程itpux_com
1. 内置聚合函数回顾
PostgreSQL提供了丰富的内置聚合函数。
CREATE TABLE fgedu_fgfgfgfgsales_data (
id SERIAL PRIMARY KEY,
region VARCHAR(50),
product VARCHAR(50),
quantity INTEGER,
price NUMERIC(10, 2),
sale_date DATE DEFAULT CURRENT_DATE
);
— 插入测试数据
INSERT INTO fgedu_fgfgfgfgsales_data (region, product, quantity, price, sale_date) VALUES
(‘华东’, ‘产品A’, 100, 50.00, ‘2026-04-01’),
(‘华东’, ‘产品B’, 150, 30.00, ‘2026-04-01’),
(‘华东’, ‘产品A’, 80, 50.00, ‘2026-04-02’),
(‘华东’, ‘产品C’, 200, 20.00, ‘2026-04-02’),
(‘华南’, ‘产品A’, 120, 50.00, ‘2026-04-01’),
(‘华南’, ‘产品B’, 90, 30.00, ‘2026-04-01’),
(‘华南’, ‘产品C’, 180, 20.00, ‘2026-04-02’),
(‘华北’, ‘产品A’, 60, 50.00, ‘2026-04-01’),
(‘华北’, ‘产品B’, 110, 30.00, ‘2026-04-02’),
(‘华北’, ‘产品C’, 140, 20.00, ‘2026-04-02’);
— 基本聚合函数
SELECT
region,
COUNT(*) AS total_orders,
SUM(quantity) AS total_quantity,
SUM(quantity * price) AS total_amount,
AVG(price) AS avg_price,
MIN(price) AS min_price,
MAX(price) AS max_price
FROM fgedu_fgfgfgfgsales_data
GROUP BY region
ORDER BY total_amount DESC;
执行结果：
更多视频教程www.fgedu.net.cn
——–+————–+—————-+————–+——————-+———–+———–
华东 | 4 | 530 | 21500.00 | 37.50000000000000 | 20.00 | 50.00
华南 | 3 | 390 | 14400.00 | 33.33333333333333 | 20.00 | 50.00
华北 | 3 | 310 | 8500.00 | 33.33333333333333 | 20.00 | 50.00
(3 rows)
高级聚合函数：
SELECT
region,
STRING_AGG(product, ‘, ‘ ORDER BY product) AS products_list,
ARRAY_AGG(product ORDER BY quantity DESC) AS products_by_qty,
JSON_AGG(json_build_object(‘product’, product, ‘qty’, quantity)) AS products_json,
STDDEV(quantity) AS qty_stddev,
VARIANCE(quantity) AS qty_variance
FROM fgedu_fgfgfgfgsales_data
GROUP BY region;
执行结果：
——–+——————————+————————–+——————————————————————+———————–+——————–
华东 | 产品A, 产品A, 产品B, 产品C | {产品C,产品B,产品A,产品A} | [{“product”:”产品A”,”qty”:100},{“product”:”产品B”,”qty”:150},…] | 52.75252316519467 | 2783.3333333333333
华南 | 产品A, 产品B, 产品C | {产品C,产品A,产品B} | [{“product”:”产品A”,”qty”:120},{“product”:”产品B”,”qty”:90},…] | 45.8257569495584 | 2100.0000000000000
华北 | 产品A, 产品B, 产品C | {产品C,产品B,产品A} | [{“product”:”产品A”,”qty”:60},{“product”:”产品B”,”qty”:110},…] | 40.00000000000000 | 1600.0000000000000
(3 rows)
2. 聚合函数与窗口函数结合
SELECT
region,
product,
quantity,
price,
quantity * price AS amount,
SUM(quantity * price) OVER (PARTITION BY region) AS region_total,
ROUND(
quantity * price / SUM(quantity * price) OVER (PARTITION BY region) * 100,
2
) AS percent_of_region,
RANK() OVER (PARTITION BY region ORDER BY quantity * price DESC) AS amount_rank,
SUM(quantity * price) OVER (
PARTITION BY region
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM fgedu_fgfgfgfgsales_data
ORDER BY region, sale_date;
执行结果：
——–+———+———-+——–+———+————–+——————-+————-+—————
华东 | 产品A | 100 | 50.00 | 5000.00 | 21500.00 | 23.26 | 2 | 5000.00
华东 | 产品B | 150 | 30.00 | 4500.00 | 21500.00 | 20.93 | 3 | 9500.00
华东 | 产品A | 80 | 50.00 | 4000.00 | 21500.00 | 18.60 | 4 | 13500.00
华东 | 产品C | 200 | 20.00 | 4000.00 | 21500.00 | 18.60 | 4 | 17500.00
华南 | 产品A | 120 | 50.00 | 6000.00 | 14400.00 | 41.67 | 1 | 6000.00
华南 | 产品B | 90 | 30.00 | 2700.00 | 14400.00 | 18.75 | 3 | 8700.00
华南 | 产品C | 180 | 20.00 | 3600.00 | 14400.00 | 25.00 | 2 | 12300.00
华北 | 产品A | 60 | 50.00 | 3000.00 | 8500.00 | 35.29 | 2 | 3000.00
华北 | 产品B | 110 | 30.00 | 3300.00 | 8500.00 | 38.82 | 1 | 6300.00
华北 | 产品C | 140 | 20.00 | 2800.00 | 8500.00 | 32.94 | 3 | 9100.00
(10 rows)
3. 自定义聚合函数原理
自定义聚合函数需要定义以下组件：
- STYPE: 状态值的数据类型
- SFUNC: 状态转换函数，处理每一行数据
- FINALFUNC: 最终计算函数，返回聚合结果
- INITCOND: 初始状态值
SELECT
aggfnoid::regprocedure AS aggregate_function,
aggtransfn::regproc AS transition_function,
aggfinalfn::regproc AS final_function,
aggtranstype::regtype AS state_type,
agginitval AS initial_value
FROM pg_aggregate
WHERE aggfnoid::regprocedure::text LIKE ‘%sum%’
LIMIT 5;
执行结果：
from oracle:www.itpux.com
—————————-+———————+—————-+————+—————
sum(integer) | int4_sum | – | bigint | 0
sum(bigint) | int8_sum | – | numeric |
sum(smallint) | int2_sum | – | bigint | 0
sum(numeric) | numeric_add | – | numeric |
sum(real) | float4pl | – | real |
(5 rows)
4. 创建简单自定义聚合函数
4.1 创建字符串连接聚合
CREATE OR REPLACE FUNCTION fgedu_string_agg_trans(
state TEXT,
value TEXT,
delimiter TEXT DEFAULT ‘,’
) RETURNS TEXT AS $$
BEGIN
IF state IS NULL THEN
RETURN value;
ELSIF value IS NULL THEN
RETURN state;
ELSE
RETURN state || delimiter || value;
END IF;
END;
$$ LANGUAGE plpgsql;
— 创建自定义聚合函数
CREATE AGGREGATE fgedu_string_concat(TEXT, TEXT) (
SFUNC = fgedu_string_agg_trans,
STYPE = TEXT,
INITCOND = ”
);
— 测试自定义聚合函数
SELECT
region,
fgedu_string_concat(product, ‘ | ‘) AS products_concat
FROM fgedu_fgfgfgfgsales_data
GROUP BY region;
执行结果：
学习交流加群风哥微信: itpux-com
——–+——————————-
华东 | | 产品A | 产品B | 产品A | 产品C
华南 | | 产品A | 产品B | 产品C
华北 | | 产品A | 产品B | 产品C
(3 rows)
4.2 创建几何平均值聚合
CREATE OR REPLACE FUNCTION fgedu_geo_mean_trans(
state NUMERIC[],
value NUMERIC
) RETURNS NUMERIC[] AS $$
BEGIN
IF state IS NULL THEN
RETURN ARRAY[0, 1]; — [count, product]
END IF;
IF value IS NULL OR value <= 0 THEN RETURN state; END IF; RETURN ARRAY[ state[1] + 1, state[2] * value ]; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION fgedu_geo_mean_final( state NUMERIC[] ) RETURNS NUMERIC AS $$ BEGIN IF state IS NULL OR state[1] = 0 THEN RETURN NULL; END IF; RETURN power(state[2], 1.0 / state[1]); END; $$ LANGUAGE plpgsql; CREATE AGGREGATE fgedu_geometric_mean(NUMERIC) ( SFUNC = fgedu_geo_mean_trans, STYPE = NUMERIC[], FINALFUNC = fgedu_geo_mean_final, INITCOND = '{0, 1}' ); -- 测试几何平均值 SELECT region, AVG(price) AS arithmetic_mean, fgedu_geometric_mean(price) AS geometric_mean FROM fgedu_fgfgfgfgsales_data GROUP BY region;
执行结果：
——–+———————-+——————-
华东 | 37.5000000000000000 | 33.43701524883031
华南 | 33.3333333333333333 | 31.08021704998095
华北 | 33.3333333333333333 | 31.08021704998095
(3 rows)
5. 自定义聚合函数进阶
5.1 创建中位数聚合
CREATE OR REPLACE FUNCTION fgedu_median_trans(
state NUMERIC[],
value NUMERIC
) RETURNS NUMERIC[] AS $$
BEGIN
IF state IS NULL THEN
RETURN ARRAY[value];
END IF;
IF value IS NULL THEN
RETURN state;
END IF;
RETURN array_fgappend(state, value);
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION fgedu_median_final(
state NUMERIC[]
) RETURNS NUMERIC AS $$
DECLARE
sorted_arr NUMERIC[];
len INTEGER;
mid INTEGER;
BEGIN
IF state IS NULL OR array_length(state, 1) = 0 THEN
RETURN NULL;
END IF;
sorted_arr := array(SELECT unnest(state) ORDER BY 1);
len := array_length(sorted_arr, 1);
mid := len / 2;
IF len % 2 = 1 THEN
RETURN sorted_arr[mid + 1];
ELSE
RETURN (sorted_arr[mid] + sorted_arr[mid + 1]) / 2.0;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE AGGREGATE fgedu_median(NUMERIC) (
SFUNC = fgedu_median_trans,
STYPE = NUMERIC[],
FINALFUNC = fgedu_median_final
);
— 测试中位数
SELECT
region,
AVG(price) AS avg_price,
fgedu_median(price) AS median_price,
MIN(price) AS min_price,
MAX(price) AS max_price
FROM fgedu_fgfgfgfgsales_data
GROUP BY region;
执行结果：
——–+——————-+————–+———–+———–
华东 | 37.50000000000000 | 40.00 | 20.00 | 50.00
华南 | 33.33333333333333 | 30.00 | 20.00 | 50.00
华北 | 33.33333333333333 | 30.00 | 20.00 | 50.00
(3 rows)
5.2 创建众数聚合
CREATE OR REPLACE FUNCTION fgedu_mode_trans(
state JSONB,
value NUMERIC
) RETURNS JSONB AS $$
BEGIN
IF state IS NULL THEN
state := ‘{}’::JSONB;
END IF;
IF value IS NULL THEN
RETURN state;
END IF;
RETURN jsonb_set(
state,
ARRAY[value::TEXT],
COALESCE((state ->> value::TEXT)::INTEGER, 0) + 1
);
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION fgedu_mode_final(
state JSONB
) RETURNS NUMERIC AS $$
DECLARE
max_count INTEGER := 0;
mode_value NUMERIC;
key TEXT;
value INTEGER;
BEGIN
IF state IS NULL OR state = ‘{}’::JSONB THEN
RETURN NULL;
END IF;
FOR key, value IN SELECT * FROM jsonb_each_text(state)
LOOP
IF value::INTEGER > max_count THEN
max_count := value::INTEGER;
mode_value := key::NUMERIC;
END IF;
END LOOP;
RETURN mode_value;
END;
$$ LANGUAGE plpgsql;
CREATE AGGREGATE fgedu_mode(NUMERIC) (
SFUNC = fgedu_mode_trans,
STYPE = JSONB,
FINALFUNC = fgedu_mode_final,
INITCOND = ‘{}’
);
— 测试众数
SELECT
region,
fgedu_mode(price) AS mode_price,
COUNT(*) AS total_count
FROM fgedu_fgfgfgfgsales_data
GROUP BY region;
执行结果：
——–+————+————-
华东 | 50.00 | 4
华南 | 50.00 | 3
华北 | 50.00 | 3
(3 rows)
6. 实战案例：统计分析
SELECT
region,
COUNT(*) AS order_count,
SUM(quantity) AS total_qty,
ROUND(AVG(price)::NUMERIC, 2) AS avg_price,
fgedu_median(price) AS median_price,
fgedu_mode(price) AS mode_price,
fgedu_geometric_mean(price) AS geo_mean,
MIN(price) AS min_price,
MAX(price) AS max_price,
ROUND(STDDEV(price)::NUMERIC, 2) AS std_dev,
ROUND(VARIANCE(price)::NUMERIC, 2) AS variance
FROM fgedu_fgfgfgfgsales_data
GROUP BY region
ORDER BY total_qty DESC;
执行结果：
——–+————-+———–+———–+————–+————+—————–+———–+———–+———+———-
华东 | 4 | 530 | 37.50 | 40.00 | 50.00 | 33.437015248830 | 20.00 | 50.00 | 12.91 | 1666.67
华南 | 3 | 390 | 33.33 | 30.00 | 50.00 | 31.080217049981 | 20.00 | 50.00 | 15.28 | 2333.33
华北 | 3 | 310 | 33.33 | 30.00 | 50.00 | 31.080217049981 | 20.00 | 50.00 | 15.28 | 2333.33
(3 rows)
7. 清理环境
DROP AGGREGATE IF EXISTS fgedu_string_concat(TEXT, TEXT);
DROP AGGREGATE IF EXISTS fgedu_geometric_mean(NUMERIC);
DROP AGGREGATE IF EXISTS fgedu_median(NUMERIC);
DROP AGGREGATE IF EXISTS fgedu_mode(NUMERIC);
— 删除辅助函数
DROP FUNCTION IF EXISTS fgedu_string_agg_trans(TEXT, TEXT, TEXT);
DROP FUNCTION IF EXISTS fgedu_geo_mean_trans(NUMERIC[], NUMERIC);
DROP FUNCTION IF EXISTS fgedu_geo_mean_final(NUMERIC[]);
DROP FUNCTION IF EXISTS fgedu_median_trans(NUMERIC[], NUMERIC);
DROP FUNCTION IF EXISTS fgedu_median_final(NUMERIC[]);
DROP FUNCTION IF EXISTS fgedu_mode_trans(JSONB, NUMERIC);
DROP FUNCTION IF EXISTS fgedu_mode_final(JSONB);
— 删除测试表
DROP TABLE IF EXISTS fgedu_fgfgfgfgsales_data;
执行结果：
风哥提示：学习交流加群风哥QQ113257174
DROP AGGREGATE
DROP AGGREGATE
DROP AGGREGATE
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP TABLE
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
