基于Python实现收益追踪器的结转收益计算方案咨询

1. 数据库表设计 #

先建两张表分离收益明细与平台规则，避免硬编码逻辑：

平台规则表 (platform_settings) #

CREATE TABLE platform_settings (
    platform_id INTEGER PRIMARY KEY AUTOINCREMENT,
    platform_name TEXT NOT NULL,
    min_withdraw DECIMAL(10,2) NOT NULL, -- 最低提现门槛
    freeze_days INT NOT NULL -- 冻结天数（如21天）
);

收益明细表 (platform_earnings) #

CREATE TABLE platform_earnings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    platform_id INTEGER NOT NULL,
    earn_date DATE NOT NULL, -- 收益所属周的基准日期（比如每周一）
    amount DECIMAL(10,2) NOT NULL, -- 当周收益金额
    settled BOOLEAN DEFAULT 0, -- 是否已完成提现结算
    FOREIGN KEY(platform_id) REFERENCES platform_settings(platform_id)
);

2. 核心查询逻辑（实时计算可提现金额） #

没必要单独维护unpaid字段，用SQL窗口函数实时计算累计结转金额，同时自动排除21天冻结期内的收益：

WITH valid_earnings AS (
    -- 筛选已过冻结期、未结算的收益，按周分组汇总
    SELECT
        strftime('%Y-%W', pe.earn_date) AS week,
        SUM(pe.amount) AS weekly_amount,
        ps.min_withdraw
    FROM platform_earnings pe
    JOIN platform_settings ps ON pe.platform_id = ps.platform_id
    WHERE
        pe.platform_id = 1 -- 替换为目标平台ID
        AND pe.settled = 0
        AND pe.earn_date <= date('now', '-21 days') -- 排除21天内的冻结收益
    GROUP BY strftime('%Y-%W', pe.earn_date), ps.min_withdraw
),
cumulative_data AS (
    -- 计算累计收益，判断是否达标提现门槛
    SELECT
        week,
        weekly_amount,
        min_withdraw,
        SUM(weekly_amount) OVER (ORDER BY week) AS running_total,
        -- 当前周可提现金额：累计达标则取门槛值，否则为0
        CASE WHEN SUM(weekly_amount) OVER (ORDER BY week) >= min_withdraw THEN min_withdraw ELSE 0 END AS withdrawable
    FROM valid_earnings
)
SELECT
    week,
    weekly_amount,
    withdrawable,
    -- 结转剩余金额：达标则剩余为累计减门槛，否则剩余为累计总额
    CASE WHEN withdrawable > 0 THEN running_total - min_withdraw ELSE running_total END AS carry_over
FROM cumulative_data;

3. 示例场景验证 #

针对你给出的平台1数据：

• 第1周：200美元（已过冻结期）
• 第2周：100美元（已过冻结期）
• 第3周：200美元（已过冻结期）

查询结果如下：

完全匹配你要求的“第1、2周显示0，第3周显示500”的需求。

4. 提现后的结算处理 #

当确认提现后，执行以下SQL标记对应周期的收益为已结算：

UPDATE platform_earnings
SET settled = 1
WHERE
    platform_id = 1
    AND strftime('%Y-%W', earn_date) = '2023-51' -- 替换为达标周的标识
    AND settled = 0;

如果有结转剩余（比如累计600美元，提现500后剩100），剩余部分会自动在后续查询中继续累计，因为未结算的收益仍会被纳入计算。

5. 性能优化建议 #

• 给platform_earnings表的platform_id、earn_date、settled字段加索引，提升查询速度：

  CREATE INDEX idx_platform_earnings ON platform_earnings(platform_id, earn_date, settled);
  

• 可每天运行脚本，自动清理已结算超过N天的冗余数据（若不需要长期留存）。

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