# 实验:聚合查询
# 简介
测试数据中模拟了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
也可以设置备份目录。
- mongorestore 最后的一个参数,设置备份数据所在位置,例如:
--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'