优享资讯 | 如何在 MongoDB 中进行多字段联表与聚合查询

掘金后端 ( ) • 2024-05-08 16:00

前言

在现代数据处理领域，数据库查询优化和数据联表查询是提高应用性能和数据分析有效性的关键。本文将通过一个具体的业务场景，详细介绍如何在 MongoDB 中进行多字段联表和聚合查询。

需求背景

假设我们有一个银行监控系统，该系统需要跟踪和分析多个银行在特定日期的警告统计和警告点数。系统存储了两种类型的数据：警告计数（count_warn）和警告点数（point_warn）。每个银行在不同的日期可能会有多条警告记录，系统需要对这些数据进行汇总和分析，以便银行管理部门可以快速获取关键日期的警告总数和警告点数总和。

数据模型

我们有两个集合：

count_warn 存储每个银行特定日期的警告次数。
point_warn 存储对应银行和日期的警告点数。

下面是数据准备语句。

db.count_warn.insertMany([
    {
        "bank": "0001",
        "count": 1,
        "warn_date": ISODate("2024-05-08T16:00:00.000Z")
    },
    {
        "bank": "0002",
        "count": 2,
        "warn_date": ISODate("2024-05-08T16:00:00.000Z")
    },
    {
        "bank": "0002",
        "count": 22,
        "warn_date": ISODate("2024-05-08T16:00:00.000Z")
    },
    {
        "bank": "0002",
        "count": 0,
        "warn_date": ISODate("2024-05-09T16:00:00.000Z")
    },
    {
        "bank": "0003",
        "count": 99,
        "warn_date": ISODate("2024-05-09T16:00:00.000Z")
    },
    {
        "bank": "0003",
        "count": 55,
        "warn_date": ISODate("2024-05-09T16:00:00.000Z")
    },
]);

db.point_warn.insertMany([
    {
        "bank": "0001",
        "point": 1,
        "warn_date": ISODate("2024-05-08T16:00:00.000Z")
    },
    {
        "bank": "0002",
        "point": 21,
        "warn_date": ISODate("2024-05-08T16:00:00.000Z")
    },
    {
        "bank": "0002",
        "point": 221,
        "warn_date": ISODate("2024-05-08T16:00:00.000Z")
    },
    {
        "bank": "0002",
        "point": 10,
        "warn_date": ISODate("2024-05-09T16:00:00.000Z")
    },
    {
        "bank": "0003",
        "point": 991,
        "warn_date": ISODate("2024-05-09T16:00:00.000Z")
    },
    {
        "bank": "0003",
        "point": 551,
        "warn_date": ISODate("2024-05-09T16:00:00.000Z")
    },
]);

具体需求

对于特定银行和日期范围，聚合每天的警告次数总和。
同时，联表查询相关日期的警告点数总和。
输出结果需要包括银行标识、日期、当天的警告总次数和总点数。

MongoDB 查询解析

为了满足以上需求，我们构建了一个 MongoDB 聚合查询，涉及 $match, $lookup, 和 $group 等操作。下面是查询的详细解析：

查询步骤

筛选数据：使用 $match 筛选 count_warn 集合中 bank 为 "0002" 且 warn_date 在指定范围内的记录。
联表查询：通过 $lookup 与 point_warn 集合联表。这里使用到了 let 来定义在联表查询中使用的局部变量（即 bank 和 warn_date），然后在 pipeline 中通过 $match 和 $expr 进行精确匹配。
聚合点数：在 $lookup 的内部管道中，采用 $group 操作来计算每个匹配记录的点数总和。
汇总数据：最外层的 $group 用于聚合 count_warn 的数据，汇总 count 字段，并从联表查询结果中提取点数总和。

查询语句

下面是真正的 mongo 查询语句:

db.count_warn.aggregate([
    {
        $match: {
            bank: "0002",
            warn_date: {"$gte": ISODate("2024-05-07T16:00:00.000Z"), "$lte": ISODate("2024-05-08T16:00:00.000Z")}
        }
    },
    {
        $lookup: {
            from: "point_warn",
            let: { bank_var: "$bank", warn_date_var: "$warn_date" },
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $and: [
                                { $eq: ["$bank", "$$bank_var"] },
                                { $eq: ["$warn_date", "$$warn_date_var"] }
                            ]
                        }
                    }
                },
                {
                    $group: {
                        _id: null,
                        totalPoint: { $sum: "$point" }
                    }
                }
            ],
            as: "point_info"
        }
    },
    {
        $group: {
            _id: { bank: "$bank", warn_date: "$warn_date" },
            count: { $sum: "$count" },
            point: { $first: { $arrayElemAt: [ "$point_info.totalPoint", 0]} }
        }
    }
]);

查询结果

查询结果将返回每个银行在特定日期的警告次数和警告点数的总和。例如，对于银行 "0002" 在 "2024-05-08" 的数据，警告次数为 24，点数为 242。

[
  {
    "_id": {
      "bank": "0002",
      "warn_date": {"$date": "2024-05-08T16:00:00.000Z"}
    },
    "count": 24,
    "point": 242
  }
]

总结

本文通过一个具体的业务案例，展示了如何在 MongoDB 中进行多字段联表和聚合查询，这对于处理复杂的数据关系和进行高效的数据分析非常关键。MongoDB 的强大聚合框架提供了灵活的查询能力，能够满足多样化的业务需求。