统计分析里希望可以有资产趋势图

[shawnlianq]

Before You Submit

• I've already checked this request hasn't been raised in issues

Feature Description

统计分析里面希望可以加入资产的趋势图，谢谢。

Additional information

No response

[mayswind]

在账户的对账单里有每个账户的余额/未清余额趋势图表

[shawnlianq]

在账户的对账单里有每个账户的余额/未清余额趋势图表

是的，这个知道，就是总资产的趋势图如果有就更好了

[Tabll]

在账户的对账单里有每个账户的余额/未清余额趋势图表

是的，这个知道，就是总资产的趋势图如果有就更好了

我让AI写了一个Mysql的查询，可以放到Metabase里面做可视化：

WITH RECURSIVE dates AS (
    -- 生成从最早交易日期到当前日期的连续日期序列
    SELECT 
        MIN(DATE(FROM_UNIXTIME(transaction_time / 1000))) AS dt
    FROM transaction
    WHERE deleted = false
    UNION ALL
    SELECT 
        dt + INTERVAL 1 DAY
    FROM dates
    WHERE dt < CURDATE()
),
account_init AS (
    -- 获取每个账户的初始余额（来自type=1的交易）
    SELECT 
        t.account_id,
        t.amount AS init_balance
    FROM transaction t
    JOIN account a ON t.account_id = a.account_id AND a.deleted = false
    WHERE t.type = 1 AND t.deleted = false
),
daily_net AS (
    -- 计算每个账户每天的净交易额（收入为正，支出为负）
    SELECT 
        t.account_id,
        DATE(FROM_UNIXTIME(t.transaction_time / 1000)) AS trans_date,
        SUM(CASE 
            WHEN t.type = 2 THEN t.amount  -- 收入
            WHEN t.type = 3 THEN -t.amount -- 支出
        END) AS net_amount
    FROM transaction t
    JOIN account a ON t.account_id = a.account_id AND a.deleted = false
    WHERE t.type IN (2, 3) AND t.deleted = false
    GROUP BY t.account_id, trans_date
),
daily_balance AS (
    -- 计算每个账户每天的余额（初始余额 + 到当日的累计净交易额）
    SELECT 
        d.dt AS balance_date,
        ai.account_id,
        ai.init_balance + SUM(COALESCE(dn.net_amount, 0)) OVER (
            PARTITION BY ai.account_id 
            ORDER BY d.dt
        ) AS daily_balance
    FROM dates d
    CROSS JOIN account_init ai
    LEFT JOIN daily_net dn ON ai.account_id = dn.account_id AND dn.trans_date = d.dt
)
-- 汇总所有账户的每日总余额
SELECT 
    balance_date,
    SUM(daily_balance) / 100 AS total_balance
FROM daily_balance
GROUP BY balance_date
ORDER BY balance_date;