如何在不使用循环的情况下计算依赖前值的当前值？——SQL中替代PL/SQL(或PLPGSQL)循环实现递推计算的方案咨询

Absolutely! Ditching row-by-row PL/SQL loops for set-based operations like recursive CTEs (Common Table Expressions) will drastically improve your performance here. This kind of iterative calculation—where each row depends on the previous one in the same group—is exactly what recursive CTEs are designed for.

Breakdown of Your Logic #

First, let's recap the core dependencies to make sure we map them correctly:

• Each GROUP is an independent calculation partition.
• Period defines the sequence of rows (we process them in order from 1 to N).
• For Period = 1, all calculated fields use fixed initial values.
• For subsequent periods:
  1. Group-Return is the sum of the product of each type's return and the previous period's actual value for that type.
  2. Group-AA-Actual (and BB/CC) is the target value divided by (1 + Group-Return) from the current period.
  3. Group-Growth is the previous period's growth multiplied by (1 + Group-Return).

Recursive CTE Solution #

Here's how to implement this in SQL (works in PostgreSQL, Oracle 11g+, SQL Server, and most modern databases):

WITH RECURSIVE group_metrics AS (
    -- Anchor Member: Initialize Period 1 for each group
    SELECT
        "GROUP",
        Period,
        "Type-AA-Return",
        "Type-BB-Return",
        0::NUMERIC AS "Group-Return",
        1000::NUMERIC AS "Group-Growth",
        "AA-Target",
        "BB-Target",
        "CC-Target",
        "AA-Target"::NUMERIC AS "Group-AA-Actual",
        "BB-Target"::NUMERIC AS "Group-BB-Actual",
        "CC-Target"::NUMERIC AS "Group-CC-Actual"
    FROM your_table_name
    WHERE Period = 1

    UNION ALL

    -- Recursive Member: Calculate values for subsequent periods
    SELECT
        curr."GROUP",
        curr.Period,
        curr."Type-AA-Return",
        curr."Type-BB-Return",
        -- Compute Group-Return: SUMPRODUCT of returns and previous actuals
        ROUND(
            (curr."Type-AA-Return" * prev."Group-AA-Actual") + 
            (curr."Type-BB-Return" * prev."Group-BB-Actual"),
            9
        ) AS "Group-Return",
        -- Compute Group-Growth: Previous growth * (1 + current Group-Return)
        ROUND(
            prev."Group-Growth" * (1 + (
                (curr."Type-AA-Return" * prev."Group-AA-Actual") + 
                (curr."Type-BB-Return" * prev."Group-BB-Actual")
            )),
            6
        ) AS "Group-Growth",
        curr."AA-Target",
        curr."BB-Target",
        curr."CC-Target",
        -- Compute Group-AA-Actual
        ROUND(
            curr."AA-Target" / (1 + (
                (curr."Type-AA-Return" * prev."Group-AA-Actual") + 
                (curr."Type-BB-Return" * prev."Group-BB-Actual")
            )),
            9
        ) AS "Group-AA-Actual",
        -- Compute Group-BB-Actual
        ROUND(
            curr."BB-Target" / (1 + (
                (curr."Type-AA-Return" * prev."Group-AA-Actual") + 
                (curr."Type-BB-Return" * prev."Group-BB-Actual")
            )),
            9
        ) AS "Group-BB-Actual",
        -- Compute Group-CC-Actual
        ROUND(
            curr."CC-Target" / (1 + (
                (curr."Type-AA-Return" * prev."Group-AA-Actual") + 
                (curr."Type-BB-Return" * prev."Group-BB-Actual")
            )),
            9
        ) AS "Group-CC-Actual"
    FROM your_table_name curr
    INNER JOIN group_metrics prev
        ON curr."GROUP" = prev."GROUP"
        AND curr.Period = prev.Period + 1
)
-- Final output: all calculated rows ordered by group and period
SELECT *
FROM group_metrics
ORDER BY "GROUP", Period;

Key Notes #

1. Quoted Identifiers: Your column names contain hyphens, so we wrap them in double quotes ("Group-Return") to avoid syntax errors (adjust to brackets [Group-Return] if using SQL Server).
2. Data Types: We cast values to NUMERIC to maintain precision; adjust based on your database's numeric type preferences.
3. Rounding: The ROUND() functions match the precision in your sample data—remove or adjust the decimal places as needed for your use case.
4. Performance: Add an index on ("GROUP", Period) to speed up the recursive join operation. This will make the query far faster than a procedural loop, especially with large datasets.

Verification #

Let's cross-check with your sample data for GROUP-ONE Period 2:

• Group-Return = (-0.0040289 * 0.8) + (-0.0040209 * 0.19) = -0.003987091 (matches your sample)
• Group-AA-Actual = 0.8 / (1 - 0.003987091) ≈ 0.799966419 (matches your sample)

This confirms the formula works as expected.

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