如何用SQL实现带向前查找（look ahead）与向后查找（look back）的表格数据修改与重计算——周末收入金额归集至对应工作日

Let's tackle this problem head-on—you need to shift weekend revenue to the next available workday (or the previous workday if the weekend falls on month-end) without relying on slow loops, which is critical for large datasets. Here's a performant, set-based approach using SQL that avoids row-by-row processing.

First, Recap Your Scenario #

You have a table with:

• date_col (A): Transaction date
• amount (B): Revenue amount
• is_workday (C): 1 = weekday, 0 = weekend

Your core goals:

• For weekdays: Keep revenue assigned to the original date
• For weekends: Move revenue to the next workday, unless the weekend is the last day of the month—then move it to the previous workday
• Optional: Track the original date (for testing/audit) in a C1 column

The Solution: Set-Based SQL with Window Functions #

This approach uses window functions (supported in PostgreSQL, MySQL 8+, SQL Server, etc.) to efficiently find the nearest workday for each row. Window functions are far faster than loops because they operate on entire datasets in bulk.

Full Query Code

Assuming your table is named revenue_data, here's the complete implementation:

WITH date_metadata AS (
    -- Flag if a date is the last day of the month to handle edge cases
    SELECT
        date_col,
        amount,
        is_workday,
        CASE WHEN LAST_DAY(date_col) = date_col THEN 1 ELSE 0 END AS is_month_end
    FROM revenue_data
),
workday_lookups AS (
    -- Calculate next and previous workdays using window functions
    SELECT
        *,
        -- Find first workday AFTER current date (ignore non-workdays)
        FIRST_VALUE(CASE WHEN is_workday = 1 THEN date_col ELSE NULL END) OVER (
            ORDER BY date_col
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
            IGNORE NULLS
        ) AS next_workday,
        -- Find last workday BEFORE current date (ignore non-workdays)
        LAST_VALUE(CASE WHEN is_workday = 1 THEN date_col ELSE NULL END) OVER (
            ORDER BY date_col
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            IGNORE NULLS
        ) AS prev_workday
    FROM date_metadata
)
-- Generate final output matching your desired format
SELECT
    -- Assign the target workday for each entry
    CASE
        WHEN is_workday = 1 THEN date_col
        WHEN is_month_end = 1 THEN prev_workday
        ELSE next_workday
    END AS A1,
    amount AS B1,
    -- Show original date only for weekend entries (for testing)
    CASE WHEN is_workday = 0 THEN date_col ELSE NULL END AS C1
FROM workday_lookups
ORDER BY A1, date_col;

How This Works #

1. date_metadata CTE: Adds a flag for month-end dates to handle the special case where a weekend falls on the last day of the month.
2. workday_lookups CTE: Uses FIRST_VALUE and LAST_VALUE with IGNORE NULLS to quickly locate the next/previous workday for every row. This scans the table once, making it extremely efficient for large datasets.
3. Final SELECT: Maps each row to its target workday, adds the optional original date for weekends, and sorts the output to match your expected format.

Testing with Your Sample Data #

For your input:

The query outputs exactly what you need:

Optional: Aggregate Revenue by Target Workday #

If you don't need individual original rows and just want total revenue per workday, modify the final SELECT to aggregate:

SELECT
    CASE
        WHEN is_workday = 1 THEN date_col
        WHEN is_month_end = 1 THEN prev_workday
        ELSE next_workday
    END AS target_workday,
    SUM(amount) AS total_revenue
FROM workday_lookups
GROUP BY target_workday
ORDER BY target_workday;

Why This Beats Loops #

Set-based operations (like window functions) are optimized by databases to handle millions of rows efficiently. Unlike loops, which process one row at a time, this query processes the entire dataset in a few passes, drastically reducing performance overhead.

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