SQL中基于同账号多行数据计算收款间隔天数的实现方案问询

Got it, let's figure out how to add that Payment_received column you need. The key here is that you need to reference the earliest SMS_Date for each Account Number when calculating the days difference with Payment_Date—something a basic CASE WHEN alone can't do, since it only looks at the current row. Here are two straightforward ways to solve this:

1. Using a Correlated Subquery #

This approach uses a subquery to fetch the minimum SMS_Date for the same Account Number as the current row, then calculates the day difference when Payment_Date isn't null.

SELECT
    SMS_Date,
    "Account Number" AS Account_Number,
    Payment_Date,
    CASE
        WHEN Payment_Date IS NOT NULL
        THEN DATEDIFF(day, (SELECT MIN(SMS_Date) FROM your_table t2 WHERE t2."Account Number" = t1."Account Number"), Payment_Date)
        ELSE NULL
    END AS Payment_received
FROM your_table t1;

How it works: #

• The correlated subquery (SELECT MIN(SMS_Date) FROM your_table t2 WHERE t2."Account Number" = t1."Account Number") grabs the first SMS date for the account in the current row.
• DATEDIFF(day, start_date, end_date) calculates the number of days between the earliest SMS date and the payment date (note: some databases like MySQL reverse the parameter order to DATEDIFF(end_date, start_date)—adjust based on your DB system).
• CASE WHEN ensures we only calculate this value when Payment_Date exists; otherwise, we return NULL.

2. Using Window Functions (More Efficient for Large Datasets) #

Window functions are cleaner and faster for this scenario because they avoid repeated subquery calls. The MIN() OVER (PARTITION BY ...) clause computes the earliest SMS date for each Account Number across all its rows, right in the main query.

SELECT
    SMS_Date,
    "Account Number" AS Account_Number,
    Payment_Date,
    CASE
        WHEN Payment_Date IS NOT NULL
        THEN DATEDIFF(day, MIN(SMS_Date) OVER (PARTITION BY "Account Number"), Payment_Date)
        ELSE NULL
    END AS Payment_received
FROM your_table;

How it works: #

• MIN(SMS_Date) OVER (PARTITION BY "Account Number") creates a "window" of rows grouped by each Account Number, then returns the smallest SMS_Date for that window in every row of the group.
• We use that precomputed minimum date directly in the CASE statement to calculate the day difference—no extra joins or subqueries needed.

Testing with Your Sample Data #

Both queries will return exactly the result you want:

Just replace your_table with the actual name of your table, and adjust the DATEDIFF syntax if you're using a database like PostgreSQL (use DATE_PART('day', Payment_Date - MIN_SMS_DATE) or AGE(Payment_Date, MIN_SMS_DATE) instead) or Oracle (use TRUNC(Payment_Date) - TRUNC(MIN_SMS_DATE)).

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