| Line 6... |
Line 6... |
| 6 |
ALTER TABLE transaction.userwallethistory
|
6 |
ALTER TABLE transaction.userwallethistory
|
| 7 |
ADD COLUMN running_balance BIGINT NOT NULL DEFAULT 0;
|
7 |
ADD COLUMN running_balance BIGINT NOT NULL DEFAULT 0;
|
| 8 |
|
8 |
|
| 9 |
-- Step 2: Backfill running_balance for all existing rows
|
9 |
-- Step 2: Backfill running_balance for all existing rows
|
| 10 |
-- Computes cumulative sum of amount per wallet_id ordered by id (chronological)
|
10 |
-- Computes cumulative sum of amount per wallet_id ordered by id (chronological)
|
| - |
|
11 |
-- Uses session variables (MySQL 5.7 compatible, no window functions)
|
| - |
|
12 |
SET @running := 0;
|
| - |
|
13 |
SET @prev_wallet := 0;
|
| - |
|
14 |
|
| 11 |
UPDATE transaction.userwallethistory uwh
|
15 |
UPDATE transaction.userwallethistory uwh
|
| 12 |
JOIN (
|
16 |
JOIN (
|
| 13 |
SELECT id,
|
17 |
SELECT id,
|
| 14 |
SUM(amount) OVER (PARTITION BY wallet_id ORDER BY id) AS cumulative_balance
|
18 |
@running := IF(@prev_wallet = wallet_id, @running + amount, amount) AS cumulative_balance,
|
| - |
|
19 |
@prev_wallet := wallet_id
|
| 15 |
FROM transaction.userwallethistory
|
20 |
FROM transaction.userwallethistory
|
| - |
|
21 |
ORDER BY wallet_id, id
|
| 16 |
) calc ON uwh.id = calc.id
|
22 |
) calc ON uwh.id = calc.id
|
| 17 |
SET uwh.running_balance = calc.cumulative_balance;
|
23 |
SET uwh.running_balance = calc.cumulative_balance;
|
| 18 |
|
24 |
|
| 19 |
-- Step 3: Add index for efficient lookups by wallet_id + timestamp (used by wallet statement)
|
25 |
-- Step 3: Add index for efficient lookups by wallet_id + timestamp (used by wallet statement)
|
| 20 |
-- Not strictly required since the existing query already works, but helps if you ever
|
26 |
-- Not strictly required since the existing query already works, but helps if you ever
|