PostgreSQL：按投资者、按日计算总投资占比及对应每日收益

注意：本文全程使用Y-m-d日期格式。

现有表结构 #

当前数据库包含3张表：

• investors：用户表，存储投资者的邮箱、姓名等基础信息
• investments：投资记录表，字段包括start_date（投资起始日期）、end_date（投资结束日期）、amount（投资金额）、percent（投资者可获得的投资收益分成比例，剩余部分作为手续费扣除）
• daily_stats：每日收益统计表，存储每日所有在途投资的总盈利数据

需求说明 #

需要为每位投资者生成其每日投资收益的数据集，输出格式参考：

计算逻辑 #

每日收益按以下规则计算：

1. 先统计当日所有在投的总金额
2. 计算单个投资者当日在投金额占总金额的比例
3. 当日总盈利 × 投资者占比 × 投资者分成比例 = 该投资者当日实际收益

举个例子：2021-02-03 当日总盈利为$339，当日总投资金额为$25000

• John 当日在投金额$5000，占比20%，分成比例20%：收益 = 339 * 0.2 * 0.2 = 13.56
• Mike 当日在投金额$20000，占比80%，分成比例40%：收益 = 339 * 0.8 * 0.4 = 108.48

实现方案 #

以下是PostgreSQL环境的查询代码（兼容generate_series用法）：

WITH query_date_range AS (
    -- 可自定义查询的时间区间，这里默认取所有投资记录覆盖的日期范围
    SELECT generate_series(
        (SELECT MIN(start_date) FROM investments),
        (SELECT MAX(end_date) FROM investments),
        '1 day'::interval
    )::date AS report_date
),
daily_total_investment AS (
    -- 计算每日全量在投总金额
    SELECT
        qdr.report_date,
        SUM(i.amount) AS total_amount
    FROM query_date_range qdr
    LEFT JOIN investments i
        ON qdr.report_date BETWEEN i.start_date AND i.end_date
    GROUP BY qdr.report_date
),
investor_daily_investment AS (
    -- 计算每个投资者每日在投金额和对应加权分成比例
    SELECT
        qdr.report_date,
        i.investor_id,
        iv.name AS investor_name,
        SUM(i.amount) AS investor_total_amount,
        -- 同一投资者多笔在投时按金额加权计算平均分成比例
        SUM(i.amount * i.percent) / SUM(i.amount) AS investor_avg_percent
    FROM query_date_range qdr
    LEFT JOIN investments i
        ON qdr.report_date BETWEEN i.start_date AND i.end_date
    LEFT JOIN investors iv ON i.investor_id = iv.id
    WHERE i.id IS NOT NULL
    GROUP BY qdr.report_date, i.investor_id, iv.name
)
-- 最终关联计算每日收益
SELECT
    idi.report_date AS date,
    ROUND(ds.total_profit * (idi.investor_total_amount / dti.total_amount) * idi.investor_avg_percent, 2) AS profit,
    idi.investor_name
FROM investor_daily_investment idi
JOIN daily_total_investment dti ON idi.report_date = dti.report_date
JOIN daily_stats ds ON idi.report_date = ds.stat_date
-- 如需查询单个投资者的收益列表，可添加筛选条件，示例：WHERE idi.investor_id = 1
ORDER BY idi.report_date, idi.investor_name;

代码说明 #

1. 第一层CTEquery_date_range生成统计日期区间，也可以手动替换成指定的起止日期
2. 第二层CTEdaily_total_investment统计每日所有在投的总金额，用于计算投资占比
3. 第三层CTEinvestor_daily_investment统计每个投资者每日的在投总金额，同时兼容同一投资者同一天有多笔在投的场景，按金额加权计算平均分成比例
4. 最后关联每日总盈利表daily_stats，按规则计算出投资者当日收益，ROUND函数可按需保留小数位数

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