Oracle PL/SQL：如何用SQL查询实现基于前一行计算值的列计算

Hey there! I get that you need to compute column Z where each value is the product of X and a running value Y—with Y starting at 1 for the first row, then taking the previous row's Z value for all subsequent rows. Cursors work, but they're often slow for large datasets, so let's use a recursive CTE instead—it's cleaner and more efficient.

Let's Break Down the Logic First #

• For the first row: Z = X * 1
• For every row after that: Z = X * (Z value from the immediately preceding row)

Example SQL Implementation #

First, note that you must have a column that defines the order of your rows (like an auto-incrementing id, a timestamp, or a sequence number). SQL tables are inherently unordered, so we need this to correctly link each row to its predecessor.

Here's a working example using a recursive CTE (works in PostgreSQL, MySQL 8+, SQL Server, and most modern databases):

WITH recursive_row_calc AS (
    -- Anchor: Handle the first row (Y = 1)
    SELECT
        row_order_col,  -- Replace with your actual ordering column (e.g., id, created_at)
        X,
        CAST(X * 1 AS DECIMAL(18, 6)) AS Z  -- Use a data type that fits your value scale
    FROM your_table
    WHERE row_order_col = (SELECT MIN(row_order_col) FROM your_table)

    UNION ALL

    -- Recursive part: Link each row to the previous one and compute Z
    SELECT
        t.row_order_col,
        t.X,
        CAST(t.X * rrc.Z AS DECIMAL(18, 6)) AS Z
    FROM your_table t
    INNER JOIN recursive_row_calc rrc
        ON t.row_order_col = rrc.row_order_col + 1  -- Adjust this condition to match your ordering
        -- If using dates: ON t.created_at = DATEADD(day, 1, rrc.created_at)
)
SELECT row_order_col, X, Z
FROM recursive_row_calc
ORDER BY row_order_col;

Key Notes #

1. Data Type Casting: Use CAST to avoid integer overflow or precision loss. Adjust DECIMAL(18,6) to match the scale and precision your data needs.
2. Ordering Column: The join condition between the main table and the recursive CTE must correctly reference the previous row. If your ordering column isn't an incrementing integer, modify the condition to fit your schema (e.g., date-based logic for timestamp columns).
3. Performance: Recursive CTEs are set-based operations, so they'll outperform cursors almost every time—especially as your dataset grows.

For MySQL < 8.0 (No Recursive CTE Support) #

If you're stuck on an older MySQL version, use a user-defined variable to simulate the running product:

SELECT
    row_order_col,
    X,
    @running_product := @running_product * X AS Z
FROM your_table,
     (SELECT @running_product := 1) AS init
ORDER BY row_order_col;

This initializes a variable to 1, then multiplies it by each X in sequence—just ensure the ORDER BY clause keeps rows in the correct order.

内容的提问来源于stack exchange，提问作者S.G