嘿，我来帮你搞定这个已实现盈亏的计算问题！首先得明确核心逻辑：只有卖出交易才会产生已实现盈亏，每笔卖出的盈亏 = (卖出价格 - 该证券的平均买入成本) × 卖出数量，最后按证券汇总所有卖出的盈亏总和即可。

第一步：模拟交易数据表 #

先把你提供的交易数据用CTE（公共表表达式）模拟成一个trades表，方便直接运行测试：

WITH trades AS (
    SELECT 'AAPL' AS security, 'BUY' AS side, 2000 AS quantity, 110.00 AS price UNION ALL
    SELECT 'MSFT' AS security, 'BUY' AS side, 1000 AS quantity, 40.00 AS price UNION ALL
    SELECT 'MSFT' AS security, 'SELL' AS side, 500 AS quantity, 38.00 AS price
)

第二步：计算每只证券的平均买入成本 #

我们需要先统计每只证券的累计买入数量和总成本，从而算出平均买入价——这是计算盈亏的核心基准：

, buy_summary AS (
    SELECT
        security,
        SUM(quantity) AS total_bought,
        SUM(quantity * price) AS total_cost,
        SUM(quantity * price) / SUM(quantity) AS avg_buy_price
    FROM trades
    WHERE side = 'BUY'
    GROUP BY security
)

第三步：汇总已实现盈亏 #

把卖出交易和对应的买入汇总数据关联，计算每笔卖出的盈亏，最后按证券分组求和；同时还要处理只有买入没有卖出的证券（这类证券的已实现盈亏为0）：

SELECT
    t.security,
    ROUND(SUM((t.price - bs.avg_buy_price) * t.quantity), 2) AS realized_p&l
FROM trades t
JOIN buy_summary bs ON t.security = bs.security
WHERE t.side = 'SELL'
GROUP BY t.security
UNION ALL
-- 补充只有买入无卖出的证券，盈亏为0
SELECT
    security,
    0.00 AS realized_p&l
FROM buy_summary
WHERE security NOT IN (SELECT DISTINCT security FROM trades WHERE side = 'SELL')
ORDER BY security;

最终运行结果 #

执行上述完整SQL后，会得到符合预期的结果：

security | realized_p&l
---------|-------------
AAPL     | 0.00
MSFT     | -1000.00

简单解释下MSFT的盈亏计算：平均买入价是40.00，卖出500股的价格是38.00，每股亏损2元，总盈亏为500 × (38 - 40) = -1000.00；AAPL只有买入记录，没有卖出，所以已实现盈亏为0。

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