计算合同中标者与Contract Table次顺位候选者的边际收入差值

业务数据表说明 #

• Customer Table：存储客户标识CustID及所属SegmentID
• Contract Table：按销售优先级rank排序，记录候选者的尝试收入rev_attempted与合同标识attempt_ID
• Winner Data：记录合同中标者的attempt_ID、CustID及中标Revenue（中标者并非总是rank=1）

计算需求 #

需完成以下计算并输出指定结构的结果：

1. 将Winner Data的中标记录关联到Contract Table，获取中标者对应的rank值
2. 找到该中标者rank的下一位候选者的rev_attempted（无下一位则视为0）
3. 用中标Revenue减去该rev_attempted得到边际收入（Marginal Revenue）
4. 计算平均边际收入（Average Marginal Revenue）

预期输出结构 #

实现技术指引 #

核心思路 #

通过窗口函数获取同一合同下中标者的下一位候选收入，结合关联查询完成边际收入计算，最后用窗口函数计算平均边际收入。

分步实现 #

1. 预处理合同表，获取下一位候选收入
   使用LEAD()窗口函数，按attempt_ID分组、rank排序，提前计算每个候选者对应的下一位尝试收入（无下一位则设为0）。

2. 关联中标数据与预处理后的合同表
   通过attempt_ID将Winner Data与预处理后的合同表关联，匹配中标者对应的下一位收入，计算边际收入。

3. 计算平均边际收入
   使用AVG()窗口函数计算全局或分组的平均边际收入（示例为全局平均）。

完整SQL示例（以PostgreSQL为例） #

-- 预处理合同表，获取每个候选者的下一位尝试收入
WITH contract_next_rev AS (
    SELECT
        attempt_ID,
        rank,
        rev_attempted,
        LEAD(rev_attempted, 1, 0) OVER (PARTITION BY attempt_ID ORDER BY rank) AS next_rev
    FROM Contract
),
-- 关联中标数据，计算边际收入
winner_marginal AS (
    SELECT
        w.CustID,
        w.Revenue,
        w.Revenue - c.next_rev AS "Marginal Revenue"
    FROM "Winner Data" w
    JOIN contract_next_rev c 
        ON w.attempt_ID = c.attempt_ID
)
-- 输出结果并计算平均边际收入
SELECT
    CustID,
    Revenue,
    "Marginal Revenue",
    AVG("Marginal Revenue") OVER () AS "Average Marginal Revenue"
FROM winner_marginal;

关键细节说明 #

• LEAD(rev_attempted, 1, 0)：第一个参数为目标字段，第二个参数为偏移量（取第1条下一条），第三个参数为无匹配时的默认值0
• 若需按客户所属SegmentID分组计算平均边际收入，可关联Customer Table并修改窗口函数为AVG("Marginal Revenue") OVER (PARTITION BY cu.SegmentID)
• 不同数据库的窗口函数语法基本一致，仅需微调（如MySQL 8.0+也支持LEAD()）

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