如何用PostgreSQL函数实现Bowley七数汇总以消除代码冗余？

核心需求 #

针对多数据集计算Bowley七数汇总（最小值、最大值、第1/3四分位数、第1/10十分位数、中位数），消除percentile_disc重复代码，兼顾大数据量性能，用first聚合获取极值样本，支持多表多数据类型，输出可复用函数而非合并结果。

解决方案：创建通用SQL函数 #

1. 自定义聚合函数示例（以PostgreSQL为例） #

利用数据库的动态SQL能力，编写通用函数接收表名、列名及可选过滤条件，直接返回结构化的七数汇总结果：

CREATE OR REPLACE FUNCTION calculate_bowley_summary(
    p_table_name TEXT,
    p_column_name TEXT,
    p_extra_filter TEXT DEFAULT ''
) RETURNS TABLE(
    min_val NUMERIC,
    min_sample RECORD,
    decile_1 NUMERIC,
    quartile_1 NUMERIC,
    median NUMERIC,
    quartile_3 NUMERIC,
    decile_10 NUMERIC,
    max_val NUMERIC,
    max_sample RECORD
) AS $$
DECLARE
    v_sql TEXT;
BEGIN
    v_sql := format('
        WITH ordered_data AS (
            SELECT %I, * 
            FROM %I 
            %s
            ORDER BY %I
        ),
        quantiles AS (
            SELECT
                percentile_disc(0.1) WITHIN GROUP (ORDER BY %I) AS decile_1,
                percentile_disc(0.25) WITHIN GROUP (ORDER BY %I) AS quartile_1,
                percentile_disc(0.5) WITHIN GROUP (ORDER BY %I) AS median,
                percentile_disc(0.75) WITHIN GROUP (ORDER BY %I) AS quartile_3,
                percentile_disc(0.9) WITHIN GROUP (ORDER BY %I) AS decile_10
            FROM %I %s
        )
        SELECT
            (SELECT %I FROM ordered_data LIMIT 1) AS min_val,
            (SELECT row(*) FROM ordered_data LIMIT 1) AS min_sample,
            q.decile_1,
            q.quartile_1,
            q.median,
            q.quartile_3,
            q.decile_10,
            (SELECT %I FROM ordered_data ORDER BY %I DESC LIMIT 1) AS max_val,
            (SELECT row(*) FROM ordered_data ORDER BY %I DESC LIMIT 1) AS max_sample
        FROM quantiles q;
    ', 
    p_column_name, p_table_name, 
    CASE WHEN p_extra_filter <> '' THEN 'WHERE ' || p_extra_filter ELSE '' END,
    p_column_name,
    p_column_name, p_column_name, p_column_name, p_column_name, p_column_name,
    p_table_name,
    CASE WHEN p_extra_filter <> '' THEN 'WHERE ' || p_extra_filter ELSE '' END,
    p_column_name,
    p_column_name, p_column_name, p_column_name
    );
    
    RETURN QUERY EXECUTE v_sql;
END;
$$ LANGUAGE plpgsql STABLE;

2. 函数调用示例 #

无需重复编写分位数逻辑，直接针对不同表和列调用：

-- 对sales表的amount列计算七数汇总，附加日期过滤条件
SELECT * FROM calculate_bowley_summary('sales', 'amount', 'sale_date >= ''2023-01-01''');

-- 对users表的age列计算七数汇总
SELECT * FROM calculate_bowley_summary('users', 'age');

3. 性能优化要点 #

• 为目标列创建排序索引，降低大数据集下ORDER BY的开销：

  CREATE INDEX idx_sales_amount ON sales(amount);
  

• 动态SQL中复用过滤条件，避免重复扫描表；
• 若允许近似分位数，可替换percentile_disc为percentile_cont，提升计算速度（注意两者分位数定义差异）。

4. 多数据类型兼容 #

如需支持日期等非数值类型，可修改函数返回字段类型，确保percentile_disc能正确处理目标类型的排序逻辑，例如将NUMERIC替换为TEXT或对应数据类型。

内容的提问来源于stack exchange，提问作者Richard Wheeldon