sql - 每次两个表值的总和，并在 SQL-Server 中找到总和结果的最小值

Question

我有两个数据库。在数据库一中，我有一个表（一天数据的 507.000 条记录）

-- insert data from database_1
DROP TABLE IF EXISTS #AccountBalance;
CREATE TABLE #AccountBalance
( AccountNumber VARCHAR(20),AccountBalance MONEY,TranTime DATETIME);

样本数据是 AccountBalance：

BankAccountNumber   AccountBalance  transactiontime
01003930510         42006.00        2021-03-20
45033323462         4682.00         2021-03-20
23035469562         3388.00         2021-03-20
23005168662         617.00          2021-03-20
01004829050         44640.00        2021-03-20

TransactionCards 的示例数据

BankAccountNumber   Balance     TransactionTime
45033323462         245428.00   2021-03-21 00:06:47.000
23038201062         140983.00   2021-03-21 00:06:49.000
45019249962         60416.00    2021-03-21 00:07:46.000
45004876662         588154.00   2021-03-21 00:10:46.000
45004876662         627867.00   2021-03-22 00:17:44.000

在数据库二中，我有一个包含 1800 万条记录的表

目标：找到一个值并调用它Minimum Value Of Sum(balance) per record

I trreid : 0. 在一个带有临时表和链接服务器的数据库中插入数据。

1. 像下面这样写光标（从 CardTransaction 中获取 BankAccountNumber，如果不存在，则将相同的 BankAccountNumber 插入#Account balance，如果存在则更新余额）
2. 计算每次提取（记录或每次）的所有 SUM(AccountBalance) 的总和并将结果插入#Result（游标中的业务清晰）
3. 从#Result 中选择 Min(AccountBalance )

-- Create tables for calculate
DROP TABLE IF EXISTS #AccountBalance;
CREATE TABLE #AccountBalance
( BankAccountNumber VARCHAR(20),AccountBalance MONEY,TranTime DATETIME); -- I inserted 507.000 row  record data in this table

DROP TABLE IF EXISTS #Result 
CREATE TABLE #Result (SumOfBalance MONEY, BankAccountNumber VARCHAR(20), TranTime DATETIME)

-- variable for cursor procces
DECLARE @BankAccountNumber VARCHAR(20);
DECLARE @TransactionBalance MONEY;
DECLARE @TranTime DATETIME;

DECLARE @OldBankAccountNumber VARCHAR(20);
DECLARE @OldAccountBalance MONEY;
DECLARE @OldTranTime DATETIME = '2021-03-20';

-- start cursor
DECLARE CR CURSOR FOR
    SELECT rt.BankAccountNumber,rt.Balance,rt.TransactionTime
    FROM RawData.dbo.CardTransaction rt;

PRINT '-------Sum of all AccountBalance Report per time------';

OPEN CR;
FETCH NEXT FROM CR
INTO @BankAccountNumber,
     @TransactionBalance,
     @TranTime;

-- insert sum of account balanace into result table
INSERT INTO #Result (SumOfBalance,BankAccountNumber,TranTime)
SELECT SUM(AccountBalance),@BankAccountNumber,@TranTime FROM #AccountBalance 

WHILE @@FETCH_STATUS = 0 AND dbo.DoContinue() = 1
BEGIN
    SELECT BankAccountNumber = @OldBankAccountNumber , AccountBalance = @OldAccountBalance FROM dbo.AccountBalance WHERE BankAccountNumber = @BankAccountNumber
    IF @OldBankAccountNumber=@BankAccountNumber -- if exists record in account balance
    BEGIN
        -- update account balance with new balance
        UPDATE #AccountBalance
        SET AccountBalance = @TransactionBalance
        WHERE BankAccountNumber = @BankAccountNumber

        -- insert new sum of account balanace into result table
        INSERT INTO #Result (SumOfBalance,BankAccountNumber,TranTime)
        SELECT SUM(AccountBalance),@BankAccountNumber,@TranTime FROM #AccountBalance 
    END;

    ELSE
    BEGIN
        -- 
        INSERT INTO #AccountBalance (BankAccountNumber,AccountBalance,TranTime)
        VALUES (@BankAccountNumber, @TransactionBalance, @TranTime);

        -- insert new sum of account balanace into result table
        INSERT INTO #Result (SumOfBalance,BankAccountNumber,TranTime)
        SELECT SUM(AccountBalance),@BankAccountNumber,@TranTime FROM #AccountBalance  
    END;

    PRINT @BankAccountNumber

    FETCH NEXT FROM CR
    INTO @BankAccountNumber,@TransactionBalance,@TranTime;
END;

CLOSE CR;
DEALLOCATE CR;

问题：工作非常缓慢，我等不及要运行光标了。我看不到结果，但我猜值不可靠（我检查了 2000 年的记录）

我需要什么：我需要快速可靠的解决方案

预期如下表：

SumOfAccountBalance             transactiontime
98,721                          2021-03-21 10:01:00
339,464                         2021-04-22 01:01:00
480,447                         2021-04-23 01:01:00
540,863                         2021-04-23 02:01:00
1,129,017                       2021-04-23 03:01:00
1,168,730                       2021-04-23 15:01:00

最终预期：

MinCriticalPointAccountBalance  transactiontime
98,721                          2021-03-21 10:01:00

标签： sqlsql-servertsqlcursorreport