MongoDB聚合查询, 以电商数据为例:
db.orders.findOne()
/*
{
"_id" : ObjectId("5dbe7a545368f69de2b4d36e"),
"street" : "493 Hilll Curve",
"city" : "Champlinberg",
"state" : "Texas",
"country" : "Malaysia",
"zip" : "24344-1715",
"phone" : "425.956.7743 x4621",
"name" : "Destinee Schneider",
"userId" : 3573,
"orderDate" : ISODate("2019-03-26T03:20:08.805Z"),
"status" : "created",
"shippingFee" : NumberDecimal("8.00"),
"orderLines" : [
{
"product" : "Refined Fresh Tuna",
"sku" : "2057",
"qty" : 25,
"price" : NumberDecimal("56.00"),
"cost" : NumberDecimal("46.48")
},
{
"product" : "Refined Concrete Ball",
"sku" : "1738",
"qty" : 61,
"price" : NumberDecimal("47.00"),
"cost" : NumberDecimal("47")
},
{
"product" : "Rustic Granite Towels",
"sku" : "500",
"qty" : 62,
"price" : NumberDecimal("74.00"),
"cost" : NumberDecimal("62.16")
},
{
"product" : "Refined Rubber Salad",
"sku" : "1400",
"qty" : 73,
"price" : NumberDecimal("93.00"),
"cost" : NumberDecimal("87.42")
},
{
"product" : "Intelligent Wooden Towels",
"sku" : "5674",
"qty" : 72,
"price" : NumberDecimal("84.00"),
"cost" : NumberDecimal("68.88")
},
{
"product" : "Refined Steel Bacon",
"sku" : "5009",
"qty" : 8,
"price" : NumberDecimal("53.00"),
"cost" : NumberDecimal("50.35")
}
],
"total" : NumberDecimal("407")
}
*/
// 一：总销量
// 计算到目前为止的总销售额
db.orders.aggregate([{ $group: { _id: null, total: { $sum: "$total" } } }])
// { "_id" : null, "total" : NumberDecimal("44019609") }
// 二：订单金额汇总
//查询2019年第一季度（1月1日~3月31日）订单中已完成（completed）状态的总金额和总数量：
db.orders.aggregate(
[
{
$match: {
status: "completed",
orderDate: { $gte: ISODate("2019-01-01"), $lt: ISODate("2019-04-01") }
}
},
{
$group: {
_id: null,
total: { $sum: "$total" },
count: { $sum: 1 },
fee: { $sum: "$shippingFee" }
}
},
{
$project: {
grandTotal: {
$add: ["$total", "$fee"]
},
_id: 0,
count: 1
}
}
]
)
// 结果 { "count" : 5875, "grandTotal" : NumberDecimal("2636376.00") }
// 三：计算月销量
// 计算前半年每个月的销售额和总订单数。
// 不算运费; 不算取消状态的订单
db.orders.aggregate([
{
$match: {
status: "completed",
orderDate: {
$gte: ISODate("2019-01-01"),
$lt: ISODate("2019-07-01")
}
}
},
{
$group: {
_id: { $month: "$orderDate" },
total: { $sum: "$total" },
count: { $sum: 1 }
}
},
{
$sort: {
_id: 1
}
},
{
$project: {
_id: 0,
"month": "$_id",
total: 1,
count: 1
}
}
])
/* 结果
{ "total" : NumberDecimal("921183"), "count" : 2068, "month" : 1 }
{ "total" : NumberDecimal("800748"), "count" : 1813, "month" : 2 }
{ "total" : NumberDecimal("870391"), "count" : 1994, "month" : 3 }
{ "total" : NumberDecimal("886846"), "count" : 2001, "month" : 4 }
{ "total" : NumberDecimal("893243"), "count" : 2060, "month" : 5 }
{ "total" : NumberDecimal("873638"), "count" : 1996, "month" : 6 }
*/
// 四: 地区销量top1
//计算第一季度每个州（state）销量最多的sku第一名。
db.orders.aggregate([
{
$match: {
status: "completed",
orderDate: { "$gte": ISODate("2019-01-01"), "$lt": ISODate("2019-04-01") }
}
},
{
$unwind: "$orderLines"
},
{
$group: {
_id: { state: "$state", sku: "$orderLines.sku" },
count: { $sum: "$orderLines.qty" }
}
},
{
$sort: {
"_id.state": 1,
"count": -1
}
},
{
$group: {
_id: "$_id.state",
sku: { $first: "$_id.sku" },
count: { $first: "$count" }
}
}
])
/*
{ "_id" : "Florida", "sku" : "1715", "count" : 158 }
{ "_id" : "California", "sku" : "905", "count" : 195 }
{ "_id" : "Ohio", "sku" : "7490", "count" : 192 }
{ "_id" : "Kansas", "sku" : "5056", "count" : 182 }
{ "_id" : "Arizona", "sku" : "1615", "count" : 189 }
{ "_id" : "Oklahoma", "sku" : "1474", "count" : 184 }
{ "_id" : "Illinois", "sku" : "2244", "count" : 188 }
{ "_id" : "Arkansas", "sku" : "2669", "count" : 171 }
{ "_id" : "Massachusetts", "sku" : "3778", "count" : 196 }
{ "_id" : "Minnesota", "sku" : "1225", "count" : 164 }
{ "_id" : "Missouri", "sku" : "8049", "count" : 192 }
{ "_id" : "South Carolina", "sku" : "4525", "count" : 161 }
{ "_id" : "Georgia", "sku" : "7106", "count" : 192 }
{ "_id" : "Iowa", "sku" : "7960", "count" : 167 }
{ "_id" : "Utah", "sku" : "4525", "count" : 180 }
{ "_id" : "Delaware", "sku" : "769", "count" : 183 }
{ "_id" : "New Jersey", "sku" : "3254", "count" : 232 }
{ "_id" : "New Mexico", "sku" : "5966", "count" : 170 }
{ "_id" : "Colorado", "sku" : "7298", "count" : 182 }
{ "_id" : "Virginia", "sku" : "1570", "count" : 173 }
Type "it" for more
*/
// 五: 统计SKU销售件数
/*
统计每个sku在第一季度销售的次数。
不算取消（cancelled）状态的订单；
按销售数量降序排列；
*/
db.orders.aggregate([
{
$match: {
status: { $ne: "cancelled" },
orderDate: { "$gte": ISODate("2019-01-01"), "$lt": ISODate("2019-04-01") }
}
},
{
$unwind: "$orderLines"
},
{
$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 }
{ "_id" : "2049", "count" : 1859 }
{ "_id" : "7239", "count" : 1854 }
{ "_id" : "6687", "count" : 1847 }
{ "_id" : "3844", "count" : 1807 }
{ "_id" : "7602", "count" : 1805 }
{ "_id" : "7519", "count" : 1803 }
{ "_id" : "3220", "count" : 1801 }
{ "_id" : "6202", "count" : 1794 }
{ "_id" : "8769", "count" : 1793 }
{ "_id" : "8972", "count" : 1790 }
{ "_id" : "4075", "count" : 1776 }
{ "_id" : "6759", "count" : 1772 }
{ "_id" : "8311", "count" : 1769 }
{ "_id" : "9530", "count" : 1766 }
{ "_id" : "9625", "count" : 1744 }
{ "_id" : "6829", "count" : 1743 }
Type "it" for more
*/