MongoDB同集合聚合查询：添加后续交易日涨跌幅及汇总字段

问题背景 #

我有存储单只股票每日数据的MongoDB文档，示例结构如下：

{
    "_id" : ObjectId("63a02b76de39412a1ce52d0c"),
    "Date" : ISODate("2022-12-13T00:00:00.000+0000"),
    "Stock" : "ALFAS",
    "Open" : 286.0,
    "High" : 308.3,
    "Low" : 285.0,
    "Price" : 308.3,
    "Change" : 9.99,
    "MOSC" : NumberInt(0),
    "SHL" : NumberInt(0),
    "DDSM" : NumberInt(0),
    "TF" : "D"
}

{
    "_id" : ObjectId("63a02b76de39412a1ce52cf5"),
    "Date" : ISODate("2022-12-13T00:00:00.000+0000"),
    "Stock" : "ACSEL",
    "Open" : 82.2,
    "High" : 84.0,
    "Low" : 81.0,
    "Price" : 82.15,
    "Change" : 0.18,
    "MOSC" : NumberInt(67),
    "SHL" : NumberInt(0),
    "DDSM" : NumberInt(0),
    "TF" : "D"
}

其中MOSC、SHL、DDSM为信号字段，任意一个值大于0，即代表该股票为正向信号股票。

现有查询 #

目前使用以下查询语句，可以筛选出指定日期的正向信号股票：

{ 
    $or : [ 
        { "MOSC" : { $gt : 0.0 } }, 
        { "SHL" : { $gt : 0.0 } }, 
        { "DDSM" : { $gt : 0.0 } } 
    ], 
    "Date" : ISODate("2022-12-12T00:00:00.000+0000") 
}

需求与期望结果 #

需要给上述查询结果添加**后续1日涨跌幅（Change 1D）、后续2日涨跌幅（Change 2D）、后续3日涨跌幅（Change 3D）以及3日涨跌幅总和（SUM 3D）**字段，期望最终结果示例如下：

{
    "_id" : ObjectId("63a02b76de39412a1ce52cf5"),
    "Date" : ISODate("2022-12-13T00:00:00.000+0000"),
    "Stock" : "ACSEL",
    "Open" : 82.2,
    "High" : 84.0,
    "Low" : 81.0,
    "Price" : 82.15,
    "Change" : 0.18,
    "MOSC" : NumberInt(67),
    "SHL" : NumberInt(0),
    "DDSM" : NumberInt(0),
    "TF" : "D",
    "Change 1D" : 3.83,
    "Change 2D" : -2.93,
    "Change 3D" : -1.09,
    "SUM 3D" : -0.19
}

作为MongoDB新手，希望了解如何构建对应的聚合查询。

聚合查询解决方案 #

核心思路 #

通过聚合管道的多个阶段，依次完成：筛选目标股票、关联后续日期数据、提取对应涨跌幅、计算总和。以下是完整的聚合查询代码及阶段说明：

db.your_collection_name.aggregate([
    // 阶段1：匹配指定日期的正向信号股票
    {
        $match: {
            $or: [
                { "MOSC": { $gt: 0 } },
                { "SHL": { $gt: 0 } },
                { "DDSM": { $gt: 0 } }
            ],
            "Date": ISODate("2022-12-12T00:00:00.000+0000")
        }
    },
    // 阶段2：关联同一只股票的后续3日数据
    {
        $lookup: {
            from: "your_collection_name", // 同一张表关联
            let: { stock: "$Stock", baseDate: "$Date" },
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $and: [
                                { $eq: ["$Stock", "$$stock"] },
                                // 匹配后续1-3天的日期（当前日期+1天到+4天，不包含+4天）
                                { $gte: ["$Date", { $add: ["$$baseDate", 86400000] }] },
                                { $lt: ["$Date", { $add: ["$$baseDate", 86400000 * 4] }] }
                            ]
                        }
                    }
                },
                // 按日期升序排序，确保后续1日、2日、3日的顺序正确
                { $sort: { "Date": 1 } },
                // 只保留需要的Change字段
                { $project: { "Change": 1, "_id": 0 } }
            ],
            as: "futureChanges"
        }
    },
    // 阶段3：提取后续1-3日的涨跌幅，处理无数据的情况（设为0）
    {
        $addFields: {
            "Change 1D": { $ifNull: [{ $arrayElemAt: ["$futureChanges.Change", 0] }, 0] },
            "Change 2D": { $ifNull: [{ $arrayElemAt: ["$futureChanges.Change", 1] }, 0] },
            "Change 3D": { $ifNull: [{ $arrayElemAt: ["$futureChanges.Change", 2] }, 0] }
        }
    },
    // 阶段4：计算3日涨跌幅总和
    {
        $addFields: {
            "SUM 3D": {
                $add: ["$Change 1D", "$Change 2D", "$Change 3D"]
            }
        }
    },
    // 可选：移除临时字段futureChanges
    {
        $project: {
            "futureChanges": 0
        }
    }
])

阶段说明 #

1. $match：复用现有筛选条件，先过滤出指定日期的正向信号股票，减少后续处理的数据量。
2. $lookup：自关联同一张股票数据表，通过let定义当前股票和基准日期，在子管道中匹配后续1-3天的同股票数据，排序后只保留Change字段，结果存入futureChanges数组。
3. $addFields：从futureChanges数组中按顺序提取第0、1、2个元素（对应后续1、2、3日的涨跌幅），用$ifNull处理无后续数据的情况（默认设为0）。
4. $addFields：将三个涨跌幅字段相加，得到SUM 3D。
5. $project（可选）：移除临时的futureChanges字段，让结果更简洁。

注意事项 #

• 替换代码中的your_collection_name为你的实际集合名称。
• 日期计算中86400000是一天的毫秒数，确保MongoDB的日期字段是ISODate类型才能正确计算。
• 如果存在非交易日数据，需要根据实际业务调整日期匹配逻辑（比如只保留TF为"D"的日线数据）。

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