架構：
const orderSchema = mongoose.Schema(
{
orderStatus: {
type: String,
enum: ["pending", "preparing", "completed", "declined"],
default: "pending",
},
products: [
{
product: {
productId: {
type: mongoose.Schema.Types.ObjectId,
ref: "Product",
},
productName: String,
productPrice: Number,
categoryName: String,
},
quantity: {
type: Number,
required: true,
}
},
],
totalPrice: { type: Number },
acceptDeclineTime: {
type: Date,
default: Date.now,
},
}
);
我想要一份年度銷售報告，其中包含接受和拒絕的訂單數量，以及每個訂單的總價。
我試過了：
orderSchema.aggregate(
[
{
$unwind: {
path: "$products",
},
},
{
$group: {
_id: { $year: { date: "$acceptDeclineTime", timezone: " 03:00" } },
totalCompletedPrice: {
$sum: {
$cond: [{ $eq: ["$orderStatus", "completed"] }, "$totalPrice", 0],
},
},
totalDeclinedPrice: {
$sum: {
$cond: [{ $eq: ["$orderStatus", "declined"] }, "$totalPrice", 0],
},
},
totalItems: {
$sum: "$products.quantity",
},
completedSales: {
$sum: {
$cond: [{ $eq: ["$orderStatus", "completed"] }, "$products.quantity", 0],
},
},
cancelledSales: {
$sum: {
$cond: [{ $eq: ["$orderStatus", "declined"] }, "$products.quantity", 0],
},
},
},
},
]);
但是價格計算是錯誤的，因為$unwind階段復制了產品的總價格，這會給$sum操作帶來問題。
uj5u.com熱心網友回復：
我認為您必須分組兩次，類似于：
orderSchema.aggregate([
{ $unwind: { path: "$products" } },
{
$group: {
_id: {
year: { $year: { date: "$acceptDeclineTime", timezone: " 03:00" } },
orderStatus: "$orderStatus"
},
products: { $push: "$products" },
totalPrice: { $sum: "$totalPrice" }
}
},
{
$group: {
_id: "$_id.year",
...
}
}
]);
uj5u.com熱心網友回復：
來自 reddit 的 pugro 建議的簡單解決方案是在 $unwind 操作之前將總價格除以 products 陣列的大小，然后重新組合時它會加起來。
$addFields:{
totalPrice:{$divide:['$totalPrice',{$size:'$products'}]}
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/533122.html