# 实验:聚合查询

# 简介

测试数据中模拟了2019年1月1日~2019年10月31日之间的订单和订单行数据,总计100000条。这些数据中包括以下主要字段:

  • userId: 下单人ID;
  • name: 订单人联系姓名;
  • orderDate: 下单日期;
  • shippingFee: 运费;
  • total: 订单物品总金额(不包括运费);
  • status: 订单状态,取值包括["created", "cancelled", "shipping", "fulfilled", "completed"]
  • orderLines: 订单包含的物品;
    • price: 物品售价;
    • cost: 物品成本;
    • qty: 购买件数;
    • sku: 产品唯一编号;

# 准备工作

# 导入实验数据

>mongorestore -u admin -p=123456  --dir 'E:\Program Files\MongoDB\dump'

样本数据地址

# MongoDB数据恢复

mongodb使用 mongorestore 命令来恢复备份的数据。

# 语法

mongorestore 命令脚本语法如下:

>mongorestore -h <hostname><:port> -d dbname <path>
  • --host <:port>, -h <:port>
    • MongoDB所在服务器地址,默认为: localhost:27017
  • --db , -d
    • 需要恢复的数据库实例,例如:test,当然这个名称也可以和备份时候的不一样,比如test2
  • --drop
    • 恢复的时候,先删除当前数据,然后恢复备份的数据。就是说,恢复后,备份后添加修改的数据都会被删除,慎用哦!
  • <path>
    • mongorestore 最后的一个参数,设置备份数据所在位置,例如:c:\data\dump\test
    • 你不能同时指定 <path>--dir 选项,--dir也可以设置备份目录。
  • --dir
    • 指定备份的目录
    • 你不能同时指定 <path>--dir 选项。

# 验证导入结果

use mock
db.orders.count()
// 执行结果:100000
db.orders.findOne()
// 执行结果:单条数据示例

# 实验内容

# 实验一:总销量

计算到目前为止的总销售额

  • 无论订单状态
  • 不限制时间范围
  • 不算运费
db.orders.aggregate([
    {
        $group: {
            _id: null,
            total: {
                $sum: "$total"
            }
        }
    }
])
// 结果:
// { "_id" : null, "total" : NumberDecimal("44019609") }

上面的查询语句中,_id 代表按照哪个字段进行分组。例如,可以按照用户,地区,订单种类等等进行分组。

在我们的需求中,要计算目前为止的总销售额,所以就不涉及到分组,所以 _id 设置为 null

同时,指定一个新的字段 total,通过 $sum: "$total",将每个订单的总额加起来,得到目前为止的总销售额。赋值给 total

# 实验二:订单金额汇总

查询2019年第一季度(1月1日~3月31日)订单中已完成(completed)状态的总金额(订单总金额+总运费)和总数量:

db.orders.aggregate([
    {
        // 步骤1:匹配条件
        $match: {
            status: "completed",
            orderDate: {
                $gte: ISODate("2019-01-01"),
                $lt: ISODate("2019-04-01")
            }
        }
    }, {
        $group: {
            // 步骤二:聚合订单总金额、总运费、总数量
            _id: null,
            total: {
                $sum: "$total"
            },
            shippingFee: {
                $sum: "$shippingFee"
            },
            count: {
                $sum: 1
            }
        }
    }, {
        $project: {
            // 计算总金额 订单总金额、总运费
            grandTotal: {
                $add: ["$total", "$shippingFee"]
            },
            count: 1,
            _id: 0
        }
    }
])
// 结果:
// { "count" : 5875, "grandTotal" : NumberDecimal("2636376.00") }

上面的查询语句中,通过步骤一和步骤二,我们可以得到 订单总金额总运费订单总数

要想得到 总金额(订单总金额+总运费),可以通过 project 步骤来实现,可以在 project 投影中增加一个新的字段。这个字段的值可以是基于已有字段的计算。

所以可以通过 $add: ["$total", "$shippingFee"] 可以得到总金额。

其中 $total 指向步骤二中计算得到的订单总金额 total$shippingFee 指向步骤二中计算得到的总运费 shippingFee

count: 1,表示输出的数据,包括步骤二中的 count 字段。

_id: 0,表示输出的数据,不包括步骤二中的 _id 字段。

# 实验三:计算月销量

计算前半年每个月的销售额和总订单数。

  • 不算运费
  • 不算取消(cancelled)状态的订单
db.orders.aggregate([
    {
        // 步骤1:匹配条件
        $match: {
            status: {
                $ne: "cancelled"
            },
            orderDate: {
                $gte: ISODate("2019-01-01"),
                $lt: ISODate("2019-07-01")
            }
        }
    }, {
        // 步骤2:取出年月
        $project: {
            month: {
                $dateToString: {
                    date: "$orderDate",
                    format: "%G年%m月"
                }
            },
            total: 1
        }
    }, {
        // 步骤3:按年月分组汇总
        $group: {
            _id: "$month",
            total: {
                $sum: "$total"
            },
            count: {
                $sum: 1
            }
        }
    }
])
// 结果:
// { "_id" : "2019年01月", "total" : NumberDecimal("3620936"), "count" : 8249 }
// { "_id" : "2019年04月", "total" : NumberDecimal("3551291"), "count" : 8038 }
// { "_id" : "2019年06月", "total" : NumberDecimal("3496645"), "count" : 7942 }
// { "_id" : "2019年05月", "total" : NumberDecimal("3590503"), "count" : 8163 }
// { "_id" : "2019年02月", "total" : NumberDecimal("3258201"), "count" : 7387 }
// { "_id" : "2019年03月", "total" : NumberDecimal("3574185"), "count" : 8167 }

# 实验四:地区销量top1

计算第一季度每个州(state)销量最多的sku第一名。

  • 只算complete订单;
db.orders.aggregate([
    {
        // 步骤1:匹配条件
        $match: {
            status: "completed",
            orderDate: {
                $gte: ISODate("2019-01-01"),
                $lt: ISODate("2019-04-01")
            }
        }
    }, {
        // 步骤2:按订单行展开
        $unwind: "$orderLines"
    }, {
        // 步骤3:按sku汇总
        $group: {
            _id: {
                state: "$state",
                sku: "$orderLines.sku"
            },
            count: {
                $sum: "$orderLines.qty"
            }
        }
    }, {
        // 步骤4:按州和销量排序
        $sort: {
            "_id.state": 1,
            "count": -1
        }
    }, {
        // 步骤4:取每个州top1
        $group: {
            _id: "$_id.state",
            sku: {
                $first: "$_id.sku"
            },
            count: {
                $first: "$count"
            }
        }
    }
])
// 结果:
// { "_id" : "Wyoming", "sku" : "8181", "count" : 183 }
// { "_id" : "Wisconsin", "sku" : "9684", "count" : 195 }
// { "_id" : "West Virginia", "sku" : "9376", "count" : 170 }
// { "_id" : "North Dakota", "sku" : "2411", "count" : 243 }
// ...

# 实验五:统计SKU销售件数

统计每个sku在第一季度销售的次数。

  • 不算取消(cancelled)状态的订单;
  • 按销售数量降序排列;
db.orders.aggregate([
    {
        // 步骤1:匹配条件
        $match: {
            status: {
                $ne: "cancelled"
            },
            orderDate: {
                $gte: ISODate("2019-01-01"),
                $lt: ISODate("2019-04-01")
            }
        }
    }, {
        // 步骤2:按订单行展开
        $unwind: "$orderLines"
    }, {
        // 步骤3:按sku汇总
        $group: {
            _id: "$orderLines.sku",
            count: {
                $sum: "$orderLines.qty"
            }
        }
    }, {
        $sort: {
            count: -1
        }
    }
])
// 结果:
// { "_id" : "4751", "count" : 2115 }
// { "_id" : "798", "count" : 1945 }
// { "_id" : "3863", "count" : 1913 }
// { "_id" : "2558", "count" : 1896 }
// ...

# 问题解决

# Failed: mock.orders: error reading database: command listCollections requires authentication

由于之前设置了账户权限,所以导入数据库时,需要提供用户名和密码。解决方法:

>mongorestore -u admin -p=123456  --dir 'E:\Program Files\MongoDB\dump'

# 参考

更新时间: 6/25/2020, 3:13:41 AM