如何用SQL窗口函数实现当前行值依赖前一行的计算？

Great question! The calculation you're describing is a recursive, row-dependent computation—each row's result relies on the calculated output of the previous row, not just its original val1 value. Regular window functions like LAG() won't work here because they can only access raw values from prior rows, not dynamically computed results. Instead, you'll need to use a recursive CTE (Common Table Expression) to handle this step-by-step calculation.

How to Implement This #

First, you need a way to define the order of your rows (since the calculation depends on processing rows in sequence). If your table doesn't have an explicit sort column (like an id or timestamp), you can generate one with ROW_NUMBER().

Example 1: Table with a Sequential Sort Column

Assume your table is named your_table with columns val1 and a sequential row_id (used to order rows):

WITH recursive calculated_rows AS (
    -- Anchor member: Initialize the first row's result
    SELECT 
        row_id,
        val1,
        val1 AS calculated_result
    FROM your_table
    WHERE row_id = (SELECT MIN(row_id) FROM your_table)
    
    UNION ALL
    
    -- Recursive member: Calculate each subsequent row using the prior result
    SELECT 
        t.row_id,
        t.val1,
        t.val1 + 0.5 * cr.calculated_result AS calculated_result
    FROM your_table t
    JOIN calculated_rows cr ON t.row_id = cr.row_id + 1
)
SELECT * FROM calculated_rows ORDER BY row_id;

Example 2: Table Without a Sequential Sort Column

If you don't have a built-in sequential column, generate row numbers first to define processing order:

WITH numbered_rows AS (
    -- Assign sequential row numbers to establish order
    SELECT 
        val1,
        ROW_NUMBER() OVER (ORDER BY your_sort_column) AS row_num -- Replace with your actual sort field (e.g., id, date)
    FROM your_table
),
recursive calculated_rows AS (
    SELECT 
        row_num,
        val1,
        val1 AS calculated_result
    FROM numbered_rows
    WHERE row_num = 1
    
    UNION ALL
    
    SELECT 
        nr.row_num,
        nr.val1,
        nr.val1 + 0.5 * cr.calculated_result AS calculated_result
    FROM numbered_rows nr
    JOIN calculated_rows cr ON nr.row_num = cr.row_num + 1
)
SELECT * FROM calculated_rows ORDER BY row_num;

How It Works #

• Anchor Member: Grabs the first row and sets calculated_result to the original val1 (since there's no prior row to reference).
• Recursive Member: Joins the original table to the CTE's results, matching each row to its immediate predecessor. It computes the new result using your formula: current val1 + 0.5 * previous calculated result.

Sample Output #

For your example data:

The query will return:

This matches exactly the calculation logic you described! Recursive CTEs are supported in most modern SQL databases (PostgreSQL, MySQL 8+, SQL Server, etc.)—syntax may vary slightly (e.g., SQL Server omits the recursive keyword), but the core logic stays the same.

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