按账户按日期计算变动：创建Daily_Change列技术咨询

Alright, let's figure out how to compute that Daily_Change column you need. The core requirement is to calculate the daily change in Debit_Balance per account, where the first date for each account has a change of 0, and all subsequent dates use the difference between the current day's balance and the prior day's balance for the same account.

The key here is to:

• Group data by account so we only compare balances within the same account
• Sort each account's records chronologically by Date
• For each record, pull the previous day's Debit_Balance (if it exists) and compute the difference
• Fall back to 0 when there's no prior balance (the first date for an account)

Most modern SQL databases (MySQL 8.0+, PostgreSQL, SQL Server, BigQuery, etc.) support window functions, which make this task trivial. We'll use the LAG() function to fetch the previous row's Debit_Balance within each account group.

Query Code #

SELECT
    account,
    Debit_Balance,
    Long,
    Short,
    Date,
    -- Calculate change: current balance minus prior balance, default to 0 if no prior balance
    COALESCE(Debit_Balance - LAG(Debit_Balance) OVER (
        PARTITION BY account 
        ORDER BY STR_TO_DATE(Date, '%m/%d/%Y') -- Ensure date is sorted chronologically
    ), 0) AS Daily_Change
FROM
    your_table_name
ORDER BY
    account,
    STR_TO_DATE(Date, '%m/%d/%Y');

Breakdown of Key Components #

• PARTITION BY account: Splits the dataset into groups for each unique account, so we only compare balances within the same account.
• ORDER BY STR_TO_DATE(Date, '%m/%d/%Y'): Converts the string date to a proper date type to ensure chronological sorting (critical if your dates are stored as strings).
• LAG(Debit_Balance): Fetches the Debit_Balance value from the immediately preceding row in the sorted account group.
• COALESCE(..., 0): If there's no preceding row (the first date for an account), LAG() returns NULL—we replace this with 0 to match your expected results.

If you're working with an older database that doesn't support window functions, you can use a self-join to fetch the prior day's balance.

Query Code #

SELECT
    t1.account,
    t1.Debit_Balance,
    t1.Long,
    t1.Short,
    t1.Date,
    -- Use IFNULL to default to 0 when no prior balance exists
    IFNULL(t1.Debit_Balance - t2.Debit_Balance, 0) AS Daily_Change
FROM
    your_table_name t1
LEFT JOIN
    your_table_name t2 
    ON t1.account = t2.account 
    AND t2.Date = (
        SELECT MAX(Date) 
        FROM your_table_name 
        WHERE account = t1.account 
        AND STR_TO_DATE(Date, '%m/%d/%Y') < STR_TO_DATE(t1.Date, '%m/%d/%Y')
    )
ORDER BY
    t1.account,
    STR_TO_DATE(t1.Date, '%m/%d/%Y');

Breakdown #

• The subquery inside the join finds the most recent date that's earlier than the current row's date for the same account.
• We left-join to ensure even the first date (with no prior date) is included, then use IFNULL to set the change to 0.

Let's check with your sample data to confirm:

• For account 716-05, 4/11/2018 balance is 12861700, minus 4/10/2018's 18981100 gives -6119400 (matches expected).
• For account 716-07, 4/11/2018 balance is 7900390 minus 4/10/2018's 6596930 gives 1303460 (matches expected).
• All first-date records return 0 as required.

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