如何在PostgreSQL中基于另一表存储的公式执行算术运算？

前提准备 #

首先确保启用tablefunc扩展（PostgreSQL默认不启用，用于透视表功能）：

CREATE EXTENSION IF NOT EXISTS tablefunc;

1. 自定义公式计算函数 #

创建PL/pgSQL函数，用于解析statement_sequences中的公式，从financials表提取对应字段值并计算结果：

CREATE OR REPLACE FUNCTION calculate_financial_formula(
    p_company_id INT,
    p_dateEnd DATE,
    p_formula TEXT
) RETURNS NUMERIC AS $$
DECLARE
    v_result NUMERIC;
    v_parsed_formula TEXT;
BEGIN
    -- 将公式中的field_id替换为对应的field_value查询语句
    -- 假设field_id格式为field_1、field_2，可根据实际格式调整正则规则
    v_parsed_formula := regexp_replace(
        p_formula,
        'field_(\d+)',
        '(SELECT field_value FROM financials WHERE company_id = $1 AND dateEnd = $2 AND field_id = ''field_\1'')',
        'g'
    );

    -- 动态执行解析后的公式
    EXECUTE format('SELECT %s', v_parsed_formula)
    INTO v_result
    USING p_company_id, p_dateEnd;

    RETURN v_result;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL; -- 计算出错时返回NULL，可按需调整
END;
$$ LANGUAGE plpgsql STABLE;

2. 生成基础计算数据集 #

关联两张表，计算每个报表项在各期末的数值：

SELECT
    s.statement_id,
    s.sequence,
    s.label,
    f.company_id,
    f.dateEnd,
    calculate_financial_formula(f.company_id, f.dateEnd, s.formula) AS item_value
FROM
    statement_sequences s
CROSS JOIN
    (SELECT DISTINCT company_id, dateEnd FROM financials) f
-- 可选：添加WHERE条件筛选特定报表，比如WHERE s.statement_id = 'income_statement'
ORDER BY
    s.statement_id, s.sequence, f.company_id, f.dateEnd;

3. 使用crosstab生成透视报表（dateEnd转为列） #

用crosstab函数将dateEnd转为列，生成最终报表格式：

SELECT
    *
FROM
    crosstab(
        -- 基础数据查询：返回行标识、列标识、计算值
        'SELECT
             s.statement_id || ''_'' || s.sequence || ''_'' || f.company_id AS row_key,
             s.statement_id,
             s.sequence,
             s.label,
             f.company_id,
             f.dateEnd,
             calculate_financial_formula(f.company_id, f.dateEnd, s.formula) AS item_value
         FROM
             statement_sequences s
         CROSS JOIN
             (SELECT DISTINCT company_id, dateEnd FROM financials) f
         ORDER BY
             row_key, f.dateEnd',
        -- 指定列顺序：所有唯一的dateEnd值
        'SELECT DISTINCT dateEnd FROM financials ORDER BY dateEnd'
    ) AS ct(
        row_key TEXT,
        statement_id INT, -- 替换为你的实际数据类型
        sequence INT,
        label TEXT,
        company_id INT,
        -- 替换为你实际存在的dateEnd列，比如：
        "2023-12-31" NUMERIC,
        "2022-12-31" NUMERIC,
        "2021-12-31" NUMERIC
    );

注意：crosstab需要明确列定义，若dateEnd动态变化，可通过PL/pgSQL编写动态SQL自动生成列名。

4. 创建物化视图存储结果 #

将透视查询结果存入物化视图：

CREATE MATERIALIZED VIEW financial_statement_pivot AS
SELECT
    *
FROM
    crosstab(
        'SELECT
             s.statement_id || ''_'' || s.sequence || ''_'' || f.company_id AS row_key,
             s.statement_id,
             s.sequence,
             s.label,
             f.company_id,
             f.dateEnd,
             calculate_financial_formula(f.company_id, f.dateEnd, s.formula) AS item_value
         FROM
             statement_sequences s
         CROSS JOIN
             (SELECT DISTINCT company_id, dateEnd FROM financials) f
         ORDER BY
             row_key, f.dateEnd',
        'SELECT DISTINCT dateEnd FROM financials ORDER BY dateEnd'
    ) AS ct(
        row_key TEXT,
        statement_id INT,
        sequence INT,
        label TEXT,
        company_id INT,
        "2023-12-31" NUMERIC,
        "2022-12-31" NUMERIC,
        "2021-12-31" NUMERIC
    );

-- 可选：创建索引提升后续查询性能
CREATE INDEX idx_mv_financial_statement ON financial_statement_pivot (statement_id, company_id);

数据更新后需刷新物化视图：REFRESH MATERIALIZED VIEW financial_statement_pivot;

补充说明 #

• 若dateEnd数量不固定，可编写PL/pgSQL函数动态生成透视列，无需手动维护列定义。
• 公式需符合PostgreSQL语法，如需自定义财务计算逻辑，可在calculate_financial_formula函数中扩展。
• 切换至MySQL等兼容数据库时，可用PIVOT语法替代crosstab，公式计算逻辑类似，需用存储过程实现。

内容的提问来源于stack exchange，提问作者Vedant Zatakiya