如何用SQL按股票数量拆分交易行实现买卖匹配？

输入数据表 #

Table 1: Buy表 #

Table 2: Sell表 #

期望输出表 #

需求说明 #

当日累计买入40股，卖出35股，需按买入先后顺序逐笔匹配卖出数量：

• 前两笔买入（10股、20股）全额匹配卖出
• 第三笔10股拆分为两行，其中5股匹配剩余卖出量，另外5股无对应卖出数据

尝试的错误SQL #

SELECT 
    b.buy_Date,
    b.buy_Time_in_hundred_hours AS Buy_Time,
    LEAST(b.Qty, s.Qty) AS Buy_Qty,
    b.Per_Share_Price AS Buy_Per_Share_Price,
    LEAST(b.Qty, s.Qty) * b.Per_Share_Price AS Buy_Total_Value,
    GREATEST(0, s.Qty - LEAST(b.Qty, s.Qty)) AS Sell_Qty,
    s.Per_Share_Price AS Sell_Per_Share_Price,
    GREATEST(0, s.Qty - LEAST(b.Qty, s.Qty)) * s.Per_Share_Price AS Sell_Total_Value
FROM 
    BUY b
LEFT JOIN 
    SELL s ON b.buy_Date = s.sell_Date 
ORDER BY 
    b.buy_Date, b.buy_Time_in_hundred_hours;

解决方案SQL #

通过窗口函数计算累计买入量，结合总卖出量实现逐笔匹配与拆分：

WITH buy_with_running_total AS (
    SELECT 
        SUBSTRING(Buy_Date, 1, 2) AS Date,
        Buy_Time_in_hundred_hours AS Buy_Time,
        Buy_Qty,
        Buy_Per_Share AS Buy_Per_Share_Price,
        Buy_Total_Value,
        -- 计算累计买入量
        SUM(Buy_Qty) OVER (ORDER BY Buy_Time_in_hundred_hours) AS running_total,
        -- 每笔买入对应的数量区间起点
        SUM(Buy_Qty) OVER (ORDER BY Buy_Time_in_hundred_hours) - Buy_Qty + 1 AS start_range,
        -- 每笔买入对应的数量区间终点
        SUM(Buy_Qty) OVER (ORDER BY Buy_Time_in_hundred_hours) AS end_range
    FROM Buy
    WHERE Buy_Date = '15-May'
),
sell_total AS (
    -- 获取当日总卖出量
    SELECT SUM(Sell_Qty) AS total_sell FROM Sell WHERE Sell_Date = '15-May'
)
-- 第一部分：处理匹配的买入行
SELECT 
    brt.Date,
    brt.Buy_Time,
    CASE 
        WHEN brt.end_range <= st.total_sell THEN brt.Buy_Qty
        WHEN brt.start_range > st.total_sell THEN brt.Buy_Qty
        ELSE st.total_sell - brt.start_range + 1
    END AS Buy_Qty,
    brt.Buy_Per_Share_Price,
    CASE 
        WHEN brt.end_range <= st.total_sell THEN brt.Buy_Total_Value
        WHEN brt.start_range > st.total_sell THEN brt.Buy_Total_Value
        ELSE (st.total_sell - brt.start_range + 1) * brt.Buy_Per_Share_Price
    END AS Buy_Total_Value,
    CASE 
        WHEN brt.end_range <= st.total_sell THEN brt.Buy_Qty
        WHEN brt.start_range > st.total_sell THEN NULL
        ELSE st.total_sell - brt.start_range + 1
    END AS Sell_Qty,
    CASE 
        WHEN brt.end_range <= st.total_sell THEN (SELECT Sell_Per_Share FROM Sell WHERE Sell_Date = '15-May')
        WHEN brt.start_range > st.total_sell THEN NULL
        ELSE (SELECT Sell_Per_Share FROM Sell WHERE Sell_Date = '15-May')
    END AS Sell_Per_Share_Price,
    CASE 
        WHEN brt.end_range <= st.total_sell THEN brt.Buy_Qty * (SELECT Sell_Per_Share FROM Sell WHERE Sell_Date = '15-May')
        WHEN brt.start_range > st.total_sell THEN NULL
        ELSE (st.total_sell - brt.start_range + 1) * (SELECT Sell_Per_Share FROM Sell WHERE Sell_Date = '15-May')
    END AS Sell_Total_Value
FROM buy_with_running_total brt
CROSS JOIN sell_total st
UNION ALL
-- 第二部分：添加第三笔买入中未匹配的剩余行
SELECT 
    brt.Date,
    brt.Buy_Time,
    brt.Buy_Qty - (st.total_sell - brt.start_range + 1) AS Buy_Qty,
    brt.Buy_Per_Share_Price,
    (brt.Buy_Qty - (st.total_sell - brt.start_range + 1)) * brt.Buy_Per_Share_Price AS Buy_Total_Value,
    NULL AS Sell_Qty,
    NULL AS Sell_Per_Share_Price,
    NULL AS Sell_Total_Value
FROM buy_with_running_total brt
CROSS JOIN sell_total st
WHERE brt.start_range <= st.total_sell AND brt.end_range > st.total_sell
-- 按要求排序
ORDER BY Date, Buy_Time, Buy_Qty DESC;

逻辑说明 #

1. buy_with_running_total：计算每笔买入的累计数量，标记出该笔买入对应的数量区间（比如第一笔10股对应1-10，第二笔20股对应11-30，第三笔10股对应31-40）
2. sell_total：提取当日总卖出量35
3. 第一个SELECT：根据累计区间与总卖出量的关系，处理每笔买入的匹配情况：全额匹配、部分匹配或无匹配
4. UNION ALL：单独拆分出第三笔买入中未匹配的5股，生成无卖出数据的行
5. 最终按日期、买入时间排序，保证输出顺序符合要求

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