PostgreSQL技术问答17 - 复杂聚合

在PostgreSQL的数据处理中，聚合函数（如SUM、AVG、COUNT）是基础且强大的工具。然而，当业务需求涉及多维分析、动态条件过滤或自定义计算逻辑时，简单的聚合函数往往难以满足需求。本文将深入探讨PostgreSQL中的复杂聚合技术，包括FILTER子句、窗口函数、自定义聚合函数等高级特性，帮助开发者应对复杂的数据分析场景。

一、FILTER子句：条件聚合的优雅实现

1.1 传统条件聚合的局限性

在PostgreSQL 9.4之前，实现条件聚合通常需要借助CASE WHEN表达式，例如计算某表中“男性”和“女性”的平均工资：

SELECT 
    AVG(CASE WHEN gender = 'M' THEN salary END) AS avg_male_salary,
    AVG(CASE WHEN gender = 'F' THEN salary END) AS avg_female_salary
FROM employees;

这种方法虽然可行，但存在以下问题：

• 代码冗余：每个条件都需要重复CASE WHEN结构。
• 可读性差：复杂的条件逻辑会使SQL变得臃肿。
• 性能隐患：嵌套表达式可能影响查询优化器的决策。

1.2 FILTER子句的引入与优势

PostgreSQL 9.4引入了FILTER子句，允许直接在聚合函数后添加条件过滤，语法更简洁：

SELECT 
    AVG(salary) FILTER (WHERE gender = 'M') AS avg_male_salary,
    AVG(salary) FILTER (WHERE gender = 'F') AS avg_female_salary
FROM employees;

优势：

• 语义清晰：条件与聚合函数直接关联，逻辑更直观。
• 性能优化：PostgreSQL可以针对FILTER条件进行专项优化。
• 扩展性强：支持复杂条件组合（如AND、OR、NOT）。

1.3 实际应用场景

场景1：多维度统计
统计订单表中不同客户等级的消费金额及退款金额：

SELECT 
    customer_level,
    SUM(amount) AS total_amount,
    SUM(amount) FILTER (WHERE status = 'refunded') AS refunded_amount
FROM orders
GROUP BY customer_level;

场景2：动态阈值过滤
计算销售额超过1000的订单的平均折扣率：

SELECT 
    AVG(discount) FILTER (WHERE amount > 1000) AS high_value_avg_discount
FROM sales;

二、窗口函数：聚合与行级数据的结合

2.1 窗口函数的核心概念

窗口函数（Window Functions）允许在保留原始行数据的同时，计算基于分组的聚合值。其核心语法为：

<window_function> OVER (
    [PARTITION BY <partition_expression>]
    [ORDER BY <order_expression> [ASC|DESC]]
    [frame_clause]
)

• PARTITION BY：将数据划分为多个分组（类似GROUP BY）。
• ORDER BY：定义分组内的排序规则。
• frame_clause：定义当前行的计算范围（如ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING）。

2.2 常用窗口函数分类

类型	函数示例	用途
聚合类	SUM(), AVG(), COUNT()	计算分组内的聚合值
排名类	ROW_NUMBER(), RANK()	为行分配排名（处理并列情况）
取值类	FIRST_VALUE(), LAG()	获取分组内特定位置的行值

2.3 实际应用案例

案例1：计算移动平均
计算某股票每日收盘价的3日移动平均：

SELECT 
    date,
    price,
    AVG(price) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM stock_prices;

案例2：排名与分组统计
统计各部门员工工资的排名及部门内工资占比：

SELECT 
    employee_id,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    salary / SUM(salary) OVER (PARTITION BY department) * 100 AS dept_salary_pct
FROM employees;

三、自定义聚合函数：满足个性化需求

3.1 为什么需要自定义聚合？

PostgreSQL内置的聚合函数虽丰富，但无法覆盖所有场景。例如：

• 计算几何图形的面积中位数。
• 实现特定业务逻辑的加权平均（如时间衰减权重）。
• 处理非数值类型的数据聚合（如文本拼接）。

3.2 自定义聚合的实现步骤

步骤1：创建状态转换函数

CREATE OR REPLACE FUNCTION median_accum(numeric[], numeric)
RETURNS numeric[] AS $$
BEGIN
    $1[array_length($1, 1) + 1] := $2;
    RETURN $1;
END;
$$ LANGUAGE plpgsql;

步骤2：创建最终计算函数

CREATE OR REPLACE FUNCTION median_final(numeric[])
RETURNS numeric AS $$
DECLARE
    len INTEGER;
    sorted_arr NUMERIC[];
BEGIN
    IF array_length($1, 1) IS NULL THEN
        RETURN NULL;
    END IF;
    sorted_arr := ARRAY(SELECT unnest($1) ORDER BY 1);
    len := array_length(sorted_arr, 1);
    IF len % 2 = 1 THEN
        RETURN sorted_arr[(len + 1)/2];
    ELSE
        RETURN (sorted_arr[len/2] + sorted_arr[len/2 + 1]) / 2;
    END IF;
END;
$$ LANGUAGE plpgsql;

步骤3：创建聚合函数

CREATE AGGREGATE median(numeric) (
    SFUNC = median_accum,
    STYPE = numeric[],
    FINALFUNC = median_final,
    INITCOND = '{}'
);

步骤4：使用自定义聚合

SELECT median(salary) FROM employees;

3.3 性能优化建议

• 状态类型选择：优先使用原生数组类型（如numeric[]）而非复杂类型。
• 并行聚合：通过PARALLEL SAFE标记支持并行计算（PostgreSQL 9.6+）。
• 增量计算：设计状态转换函数时避免全量数据重排。

四、复杂聚合的最佳实践

4.1 索引优化策略

• 复合索引：为PARTITION BY和ORDER BY列创建复合索引。

  CREATE INDEX idx_orders_date_customer ON orders (order_date, customer_id);

• 部分索引：对FILTER条件中的高频查询创建部分索引。

  CREATE INDEX idx_high_value_orders ON orders (amount) WHERE amount > 1000;

4.2 查询重写技巧

• 避免重复计算：将公共子查询提取为CTE（Common Table Expression）。

  WITH dept_stats AS (
      SELECT 
          department,
          AVG(salary) AS avg_salary,
          PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
      FROM employees
      GROUP BY department
  )
  SELECT * FROM dept_stats WHERE avg_salary > median_salary * 1.2;

4.3 扩展模块推荐

• pg_stat_statements：监控复杂聚合查询的性能。
• PostGIS：处理地理空间数据的聚合（如计算多边形面积和）。
• hstore：实现基于键值对的灵活聚合。

五、总结与展望

PostgreSQL的复杂聚合功能为数据分析提供了强大的工具集：

• FILTER子句：简化了条件聚合的实现。
• 窗口函数：实现了聚合与行级数据的无缝结合。
• 自定义聚合：满足了高度个性化的业务需求。

未来，随着PostgreSQL的演进，我们可以期待：

• 更智能的查询优化器对复杂聚合的支持。
• 机器学习集成（如内置统计函数）。
• 更高效的并行聚合算法。

开发者应深入理解这些特性，结合实际业务场景选择合适的技术方案，以充分发挥PostgreSQL在数据分析领域的潜力。