一种选择是使用递归 cte 来准备数据，为您需要的所有月份创建行，获取月份的最后日期并在每个特定月份结束时记录最后的交易日期。

WITH    --  R e c u r s i v e    C T E : 
   months ( mnth, account, balance, tx_date, last_date_mnth, last_tx_date ) AS 
     ( Select 1, account, balance, tx_date, 
              LAST_DAY(To_Date( '01.' || Lpad('1', 2, '0') || '.' || EXTRACT(Year From Sysdate), 'dd.mm.yyyy' ) ),
              Max(Case When tx_date <= LAST_DAY(To_Date( '01.' || Lpad('1', 2, '0') || '.' || EXTRACT(Year From Sysdate), 'dd.mm.yyyy' ) )
                       Then tx_date
                  End) Over(Partition By account, 1 
                            Order By  tx_date
                            Rows Between Unbounded Preceding and Current Row)
       From   tbl
      UNION ALL
       Select mnth + 1, account, balance, tx_date,
              LAST_DAY( ADD_MONTHS(last_date_mnth, 1) ),
              Max(Case When tx_date <= LAST_DAY( ADD_MONTHS(last_date_mnth, 1) ) 
                       Then tx_date 
                  End) Over(Partition By account, mnth 
                            Order By tx_date
                            Rows Between Unbounded Preceding and Current Row)
       From   months
       Where  mnth < 12
     )

…通过收集所有月份的数据，您可以获取每月最后一笔交易的余额，包括上个月的余额（如果那是最后一笔交易）…

--    M a i n    S Q L : 
Select mnth_str "MONTH", account "ACCOUNT", Max(balance) "BALANCE"
FRom  ( Select  To_Char(last_date_mnth, 'mm/yyyy') mnth_str, account, 
                Case When Max(last_tx_date) Over(Partition By account, last_date_mnth) = tx_date 
                     Then balance 
                Else 0 
                End "BALANCE"
        From   months
        Where  mnth Between 7 and 9
  )
Group By mnth_str, account
Order By mnth_str, account

/*    R e s u l t : 
MONTH   ACCOUNT BALANCE
------- ------- -------
07/2024 a             0
07/2024 b             0
07/2024 c           200
08/2024 a            75
08/2024 b            50
08/2024 c           200
09/2024 a           100
09/2024 b            50
09/2024 c           200      */

注意：

要获取从第一笔交易的月份到当前月份的所有数据，请检查代码和结果。

如果实际数据中的余额小于零，则（在主 SQL 的 Select 子句中）“BALANCE”列的代码应如下所示：

Nvl(Max(Case When balance != 0 Then balance End), 0) "BALANCE"

您好，这是我在 Oracle 21c 中的解决方案：

首先，我生成月份和帐户的唯一组合，然后对月份-帐户组合对与原始数据进行左连接，使用每月和帐户的dense_rank选择最新的余额。

最后的选择包括案例语句，根据交易日期的月份是否是该帐户的最早月份来计算总余额。

CREATE TABLE balance_history  (
    account VARCHAR2(10),   
    balance NUMBER,     
    transaction_date DATE
);

INSERT INTO balance_history (account, balance, transaction_date) VALUES ('a', 100, TO_DATE('2024-09-29', 'YYYY-MM-DD'));
INSERT INTO balance_history (account, balance, transaction_date) VALUES ('a', 75, TO_DATE('2024-08-20', 'YYYY-MM-DD'));
INSERT INTO balance_history (account, balance, transaction_date) VALUES ('a', 65, TO_DATE('2024-08-18', 'YYYY-MM-DD'));
INSERT INTO balance_history (account, balance, transaction_date) VALUES ('b', 50, TO_DATE('2024-08-18', 'YYYY-MM-DD'));
INSERT INTO balance_history (account, balance, transaction_date) VALUES ('c', 200, TO_DATE('2024-07-10', 'YYYY-MM-DD'));

    WITH monthly_account_pairs AS (
 select month, a.account from (
 SELECT unique TO_CHAR(ADD_MONTHS((select MIN(transaction_date) from balance_history), LEVEL - 1), 'YYYY-MM') AS month
    FROM balance_history
    CONNECT BY LEVEL <= MONTHS_BETWEEN((select MAX(transaction_date) from balance_history), 
    (select MIN(transaction_date)from balance_history) )
    + 1)
        CROSS JOIN (SELECT DISTINCT account FROM balance_history) a
    ) , monthly_account_balances AS (
        SELECT 
            map.month, 
            map.account, 
            COALESCE(bh.balance, 0) AS balance, 
            DENSE_RANK() OVER (
                PARTITION BY TO_CHAR(bh.transaction_date, 'YYYY-MM'), bh.account 
                ORDER BY bh.transaction_date DESC
            ) AS rank_order, 
            bh.transaction_date
        FROM monthly_account_pairs map 
        LEFT JOIN balance_history bh 
            ON map.month = TO_CHAR(bh.transaction_date, 'YYYY-MM') 
            AND map.account = bh.account
    )
    SELECT 
        month,
        account,
        CASE 
            WHEN EXTRACT(MONTH FROM COALESCE(transaction_date, TO_DATE(month || '-01', 'YYYY-MM-DD'))) <>
                (
                SELECT MIN(EXTRACT(MONTH FROM COALESCE(transaction_date, TO_DATE(month || '-01', 'YYYY-MM-DD')))) 
                 FROM monthly_account_balances
                 )
            THEN MAX(balance) OVER (PARTITION BY account ORDER BY COALESCE(transaction_date, TO_DATE(month || '-01', 'YYYY-MM-DD')))
            ELSE balance
        END AS balance
    FROM monthly_account_balances mab
    WHERE rank_order = 1
    ORDER BY month, account;

希望有帮助。