基于其他表公式的列计算：支持该需求的数据库引擎咨询

Absolutely, this is totally doable with a bunch of modern database engines! Let me walk you through how this works, and which tools can handle your use case—since you’re still picking a DB engine, I’ll break down the most popular options and how to implement your dynamic calculated columns.

PostgreSQL #

PostgreSQL is a fantastic choice here thanks to its flexible procedural language (plpgsql) and support for dynamic SQL. You can build a custom function that:

1. Pulls the stored formula text from your formulas table
2. Replaces all #value_id placeholders with the actual numeric values from your values table
3. Executes the parsed formula to get the result

Here’s a quick example function:

CREATE OR REPLACE FUNCTION calculate_formula(p_formula_id INT)
RETURNS NUMERIC AS $$
DECLARE
    v_formula TEXT;
    v_result NUMERIC;
BEGIN
    -- Grab the raw formula text
    SELECT formula_text INTO v_formula FROM formulas WHERE formula_id = p_formula_id;
    
    -- Replace #XXX with the corresponding value from the values table
    SELECT string_agg(
        CASE WHEN substring(token FROM '#(\d+)') IS NOT NULL 
             THEN (SELECT value FROM values WHERE value_id = substring(token FROM '#(\d+)')::INT)::TEXT
             ELSE token
        END, ''
    ) INTO v_formula
    FROM regexp_split_to_table(v_formula, '(\#\d+)') AS token;
    
    -- Run the calculated formula
    EXECUTE 'SELECT ' || v_formula INTO v_result;
    RETURN v_result;
END;
$$ LANGUAGE plpgsql;

Then you can use it in a SELECT to get your calculated column:

SELECT 
    f.formula_id,
    calculate_formula(f.formula_id) AS calculated_result
FROM formulas f;

MySQL #

MySQL also handles dynamic formula execution well, using PREPARE and EXECUTE statements in a stored function. For MySQL 8.0+, you can leverage regex replacement to swap out those #value_id placeholders easily:

DELIMITER //
CREATE FUNCTION calculate_formula(p_formula_id INT)
RETURNS DECIMAL(18,4)
DETERMINISTIC
BEGIN
    DECLARE v_formula TEXT;
    DECLARE v_result DECIMAL(18,4);
    DECLARE v_sql TEXT;
    
    -- Fetch the stored formula
    SELECT formula_text INTO v_formula FROM formulas WHERE formula_id = p_formula_id;
    
    -- Replace #XXX with a subquery that pulls the matching value
    SET v_formula = REGEXP_REPLACE(v_formula, '#(\\d+)', '(SELECT value FROM values WHERE value_id = \\1)');
    
    -- Build and run the dynamic SQL
    SET v_sql = CONCAT('SELECT ', v_formula);
    PREPARE stmt FROM v_sql;
    EXECUTE stmt INTO v_result;
    DEALLOCATE PREPARE stmt;
    
    RETURN v_result;
END //
DELIMITER ;

Query it like this:

SELECT formula_id, calculate_formula(formula_id) AS calculated_result FROM formulas;

SQL Server #

SQL Server supports dynamic SQL via sp_executesql, which you can wrap into a scalar-valued function. If you’re on SQL Server 2016 or later, regex replacement simplifies swapping placeholders:

CREATE FUNCTION dbo.calculate_formula(@formula_id INT)
RETURNS DECIMAL(18,4)
AS
BEGIN
    DECLARE @formula NVARCHAR(MAX);
    DECLARE @result DECIMAL(18,4);
    DECLARE @sql NVARCHAR(MAX);
    
    -- Get the formula text
    SELECT @formula = formula_text FROM formulas WHERE formula_id = @formula_id;
    
    -- Replace #XXX with the corresponding value query
    SET @formula = REGEXP_REPLACE(@formula, '#(\d+)', '(SELECT value FROM values WHERE value_id = \1)');
    
    -- Execute the dynamic calculation
    SET @sql = N'SELECT @result = ' + @formula;
    EXEC sp_executesql @sql, N'@result DECIMAL(18,4) OUTPUT', @result OUTPUT;
    
    RETURN @result;
END;

Use it in your SELECT statement:

SELECT formula_id, dbo.calculate_formula(formula_id) AS calculated_result FROM formulas;

BigQuery (Cloud Database) #

If you’re leaning toward a cloud solution, BigQuery supports EXECUTE IMMEDIATE for dynamic SQL, and you can build a custom function to handle the formula parsing:

CREATE OR REPLACE FUNCTION calculate_formula(p_formula_id INT64)
RETURNS NUMERIC
AS (
    WITH formula AS (
        SELECT formula_text FROM formulas WHERE formula_id = p_formula_id
    ),
    processed_formula AS (
        SELECT REGEXP_REPLACE(formula_text, r'#(\d+)', (SELECT CAST(value AS STRING) FROM values WHERE value_id = CAST(REGEXP_EXTRACT(formula_text, r'#(\d+)') AS INT64))) AS parsed_formula
        FROM formula
    )
    EXECUTE IMMEDIATE CONCAT('SELECT ', (SELECT parsed_formula FROM processed_formula))
);

Query example:

SELECT formula_id, calculate_formula(formula_id) AS calculated_result FROM formulas;

• Security: Dynamic SQL carries a SQL injection risk. Since you mentioned formulas are manually written by your team, this is low-risk—but if you ever open this up to user input, make sure to validate and sanitize formulas rigorously.
• Performance: Dynamic SQL has to be parsed every time it runs, which can add overhead for large datasets. Consider caching results in a separate table or using materialized views to precompute values if performance becomes an issue.
• Syntax Consistency: Make sure all stored formulas follow the SQL syntax rules of your chosen database (e.g., operator precedence, data type compatibility) to avoid runtime errors.

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