MySQL按投资组合分组计算股票付款累计值技术问询

我来帮你搞定这个需求：把同一投资组合下同一股票的多笔购买交易合并成一行，汇总交易数量和付款金额，同时计算每个投资组合内的累计付款总额（running total）。下面是完整的实现步骤和代码示例：

1. 表结构补充与说明 #

先把你给出的表结构补全（结合需求补充了交易数量和付款金额字段）：

CREATE TABLE `test` ( 
  `code` varchar(10) DEFAULT NULL,       -- 股票代码
  `portfolio` varchar(30) DEFAULT NULL, -- 投资组合名称
  `pdate` date DEFAULT NULL,             -- 交易日期
  `dividend` decimal(12,2) DEFAULT NULL, -- 股息（如果有的话）
  `quantity` int(11) DEFAULT NULL,       -- 购买数量
  `payment` decimal(12,2) DEFAULT NULL   -- 付款金额
);

2. 示例测试数据 #

为了方便演示，我准备了一些测试数据，包含不同投资组合下的多笔股票交易：

INSERT INTO test VALUES
('AAPL', 'Portfolio1', '2023-01-05', 0.50, 10, 1500.00),
('AAPL', 'Portfolio1', '2023-02-10', 0.50, 5, 760.00),
('MSFT', 'Portfolio1', '2023-01-15', 0.75, 8, 2400.00),
('AAPL', 'Portfolio2', '2023-03-01', 0.50, 12, 1850.00),
('MSFT', 'Portfolio2', '2023-02-20', 0.75, 6, 1800.00);

3. 核心查询语句 #

这里用分组聚合 + 窗口函数来实现需求：先按投资组合和股票代码分组，汇总数量和付款；再用窗口函数计算每个投资组合内的累计付款总额。

SELECT 
  portfolio,
  code,
  SUM(quantity) AS total_quantity,       -- 汇总该股票在组合内的总购买数量
  SUM(payment) AS total_payment,         -- 汇总该股票在组合内的总付款金额
  SUM(SUM(payment)) OVER (
    PARTITION BY portfolio 
    ORDER BY code                        -- 可根据需求调整排序字段，比如交易日期
  ) AS running_total_payment             -- 投资组合内的累计付款总额
FROM test
GROUP BY portfolio, code
ORDER BY portfolio, code;

4. 查询结果展示 #

执行上面的SQL后，会得到如下结果（已经合并了同一组合同一股票的交易，并计算了累计值）：

5. 灵活调整：按交易日期顺序计算累计 #

如果你希望累计值是按照股票首次购买的日期顺序来计算，而不是股票代码，可以调整窗口函数的排序字段：

SELECT 
  portfolio,
  code,
  MIN(pdate) AS first_purchase_date,     -- 该股票在组合内的首次购买日期
  SUM(quantity) AS total_quantity,
  SUM(payment) AS total_payment,
  SUM(SUM(payment)) OVER (
    PARTITION BY portfolio 
    ORDER BY MIN(pdate)                  -- 按首次购买日期排序计算累计
  ) AS running_total_payment
FROM test
GROUP BY portfolio, code
ORDER BY portfolio, first_purchase_date;

这样结果会按照每个投资组合内股票的购买先后顺序来累计付款金额，更贴合实际的投资时间线。

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