SQL Server 2017基于FIFO规则的欠款账龄计算与分桶实现问询

Migrating SAS RETAIN logic to SQL Server for large datasets requires leveraging window functions and set-based operations to avoid slow row-by-row processing. Here's a scalable solution that implements FIFO payment application and calculates aging buckets as requested:

Step-by-Step Approach #

1. Isolate & Order Bills: For each account, list bills in ascending order of Bill Due Date (and Transaction Number for ties) to enforce FIFO priority.
2. Calculate Cumulative Bill Totals: Compute running totals of bill amounts per account to track how much is owed up to each bill.
3. Sum Total Payments per Account: Aggregate all payments made on or before your chosen as-of date (since we're calculating balances as of that point).
4. Apply Payments to Bills: Use the cumulative bill totals and total payments to determine how much of each bill is paid (fully, partially, or not at all).
5. Calculate Aging & Bucket Balances: For outstanding bill amounts, compute days past due and group into your required aging buckets.

Full SQL Code #

DECLARE @AsOfDate DATE = '2019-06-01'; -- Adjust this to your desired as-of date

WITH CTE_Bills AS (
    -- Get all bills with cumulative running totals per account
    SELECT 
        [Account Number] AS AccountNumber,
        [Transaction Number] AS TransactionNumber,
        [Bill Due Date] AS BillDueDate,
        [Transaction Amount] AS BillAmount,
        SUM([Transaction Amount]) OVER (
            PARTITION BY [Account Number] 
            ORDER BY [Bill Due Date], [Transaction Number]
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS CumulativeBill
    FROM YourTransactionTable
    WHERE [Transaction Type] = 'B'
),
CTE_Payments AS (
    -- Total payments per account made on or before the as-of date
    SELECT 
        [Account Number] AS AccountNumber,
        SUM([Transaction Amount]) AS TotalPayments
    FROM YourTransactionTable
    WHERE [Transaction Type] = 'P'
        AND [Transaction Date] <= @AsOfDate
    GROUP BY [Account Number]
),
CTE_BillBalances AS (
    -- Calculate remaining balance for each bill after applying payments
    SELECT 
        b.AccountNumber,
        b.BillDueDate,
        b.BillAmount,
        COALESCE(p.TotalPayments, 0) AS TotalPayments,
        -- Compute how much of this bill was paid
        CASE
            -- All payments went to earlier bills
            WHEN b.CumulativeBill - b.BillAmount >= COALESCE(p.TotalPayments, 0) THEN 0
            -- This bill is fully covered by payments
            WHEN b.CumulativeBill <= COALESCE(p.TotalPayments, 0) THEN b.BillAmount
            -- Partial payment applied to this bill
            ELSE COALESCE(p.TotalPayments, 0) - (b.CumulativeBill - b.BillAmount)
        END AS PaidAmount,
        -- Remaining outstanding balance
        b.BillAmount - 
        CASE
            WHEN b.CumulativeBill - b.BillAmount >= COALESCE(p.TotalPayments, 0) THEN 0
            WHEN b.CumulativeBill <= COALESCE(p.TotalPayments, 0) THEN b.BillAmount
            ELSE COALESCE(p.TotalPayments, 0) - (b.CumulativeBill - b.BillAmount)
        END AS RemainingBalance
    FROM CTE_Bills b
    LEFT JOIN CTE_Payments p ON b.AccountNumber = p.AccountNumber
),
CTE_AgeBuckets AS (
    -- Assign aging buckets to outstanding balances
    SELECT 
        AccountNumber,
        RemainingBalance,
        DATEDIFF(DAY, BillDueDate, @AsOfDate) AS DaysPastDue,
        CASE
            WHEN DATEDIFF(DAY, BillDueDate, @AsOfDate) <= 0 THEN 'Current'
            WHEN DATEDIFF(DAY, BillDueDate, @AsOfDate) <= 30 THEN '0-30 Days'
            WHEN DATEDIFF(DAY, BillDueDate, @AsOfDate) <= 60 THEN '31-60 Days'
            WHEN DATEDIFF(DAY, BillDueDate, @AsOfDate) <= 90 THEN '61-90 Days'
            ELSE '90+ Days'
        END AS AgeBucket
    FROM CTE_BillBalances
    WHERE RemainingBalance > 0 -- Only include unpaid amounts
)
-- Final aggregated results per account and aging bucket
SELECT 
    AccountNumber,
    AgeBucket,
    SUM(RemainingBalance) AS OutstandingAmount
FROM CTE_AgeBuckets
GROUP BY AccountNumber, AgeBucket
ORDER BY 
    AccountNumber,
    -- Ensure bucket order is correct in results
    CASE AgeBucket
        WHEN 'Current' THEN 1
        WHEN '0-30 Days' THEN 2
        WHEN '31-60 Days' THEN 3
        WHEN '61-90 Days' THEN 4
        WHEN '90+ Days' THEN 5
    END;

Performance Optimization Tips #

Given your large dataset (2M unique accounts, 500M transactions), these tweaks will help the query run efficiently:

1. Index Tuning:
   • Add a covering index for bills: CREATE NONCLUSTERED INDEX IX_Bills ON YourTransactionTable ([Account Number], [Bill Due Date], [Transaction Number]) INCLUDE ([Transaction Amount]);
   • Add a covering index for payments: CREATE NONCLUSTERED INDEX IX_Payments ON YourTransactionTable ([Account Number], [Transaction Date]) INCLUDE ([Transaction Amount]);
   • These indexes eliminate sorting in the window function and speed up the payment aggregation.
2. Partitioning: If your table isn't already partitioned, consider partitioning by Account Number or Transaction Date to reduce I/O.
3. Statistics: Ensure up-to-date statistics on your table so the query optimizer can choose the best execution plan.

Key Notes #

• FIFO Enforcement: The ORDER BY [Bill Due Date], [Transaction Number] in the window function ensures payments are applied to the oldest bills first, even if multiple bills share the same due date.
• As-of Date: The @AsOfDate variable lets you calculate balances for any point in time—adjust it to match your reporting needs.
• Set-Based Processing: This approach avoids cursors or loops, which are prohibitively slow for 500M rows. Window functions and aggregations are optimized for large datasets in SQL Server 2017.

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