PostgreSQL中按性别分组动态生成合同与年份列的汇总表构建

针对你需要按gender分组、动态生成contract_number+year组合列的需求，由于静态CASE WHEN无法适配数据新增的场景，这里提供两种基于PostgreSQL动态SQL的实现方案，无需修改代码即可自动适配新的合同或年份。

方案1：JSONB聚合+动态列展开（无需额外扩展，推荐） #

利用PostgreSQL的JSONB类型先聚合数据，再动态提取所有需要的列名生成透视表。假设你的业务表为customer_loan，包含字段gender、contract_number、year以及需要汇总的指标（比如amount，可替换为其他统计项）。

实现步骤 #

1. 自动获取所有动态列名
   先查询出所有contract_number与year的组合，作为后续的列名：

SELECT string_agg(DISTINCT quote_ident(contract_number || '_' || year), ', ')
FROM customer_loan;

2. 编写动态SQL函数
   创建一个PL/pgSQL函数，自动生成并执行透视查询：

CREATE OR REPLACE FUNCTION get_gender_loan_summary()
RETURNS TABLE(gender text, col_names text[])
LANGUAGE plpgsql AS $$
DECLARE
    cols text;
BEGIN
    -- 提取所有唯一的合同+年份组合列名
    SELECT string_agg(DISTINCT quote_ident(contract_number || '_' || year), ', ')
    INTO cols
    FROM customer_loan;

    -- 动态生成并执行透视SQL
    RETURN QUERY EXECUTE format(
        'SELECT gender, %s
         FROM (
             SELECT gender,
                    contract_number || ''_'' || year AS col_name,
                    SUM(amount) AS total  -- 替换为你需要的汇总逻辑：COUNT(*)、AVG(interest)等
             FROM customer_loan
             GROUP BY gender, col_name
         ) src
         PIVOT (
             SUM(total)
             FOR col_name IN (%s)
         ) pvt',
        cols, cols
    );
END;
$$;

3. 调用函数获取结果

SELECT * FROM get_gender_loan_summary();

方案2：使用tablefunc扩展的crosstab #

如果你习惯用PostgreSQL的crosstab函数实现透视，需要先启用tablefunc扩展：

CREATE EXTENSION IF NOT EXISTS tablefunc;

实现函数 #

同样通过动态SQL自动识别列名：

CREATE OR REPLACE FUNCTION get_gender_loan_crosstab()
RETURNS SETOF record
LANGUAGE plpgsql AS $$
DECLARE
    cols text;
    col_defs text;
BEGIN
    -- 获取列名和对应的类型定义（这里假设汇总值为numeric）
    SELECT string_agg(DISTINCT quote_ident(contract_number || '_' || year), ', '),
           string_agg(DISTINCT quote_ident(contract_number || '_' || year) || ' numeric', ', ')
    INTO cols, col_defs
    FROM customer_loan;

    RETURN QUERY EXECUTE format(
        'SELECT * FROM crosstab(
             ''SELECT gender, contract_number || ''''_'''' || year, SUM(amount)
              FROM customer_loan
              GROUP BY gender, contract_number || ''''_'''' || year
              ORDER BY gender'',
             ''SELECT DISTINCT contract_number || ''''_'''' || year FROM customer_loan ORDER BY 1''
         ) AS ct(gender text, %s)',
        col_defs
    );
END;
$$;

调用方式 #

调用时需要指定返回结构（或转换为JSONB灵活处理）：

SELECT * FROM get_gender_loan_crosstab() AS (gender text, "CN001_2022" numeric, "CN002_2023" numeric);

关键说明 #

• 两种方案都能自动适配新增的contract_number或year，无需修改函数代码。
• 若要处理空值，可将SUM(amount)替换为COALESCE(SUM(amount), 0)，确保空值显示为0。
• 汇总逻辑可根据业务需求调整，比如替换为COUNT(*)统计合同数、AVG(rate)计算平均利率等。

内容的提问来源于stack exchange，提问作者wdad asd