# 1. 创建索引
fgedudb> db.fgedu_orders.createIndex({ status: 1, created_at: -1 })
fgedudb> db.fgedu_orders.createIndex({ user_id: 1, status: 1 })

# 2. 统计总销售额和订单数量
fgedudb> db.fgedu_orders.aggregate([
{ $match: { status: “completed” } },
{ $group: {
_id: null,
total_sales: { $sum: “$amount” },
total_orders: { $sum: 1 },
avg_order_value: { $avg: “$amount” }
}
}
])

# 3. 按日期统计销售趋势
fgedudb> db.fgedu_orders.aggregate([
{ $match: { status: “completed”, created_at: { $gte: new Date(“2026-04-01”), $lte: new Date(“2026-04-30”) } } },
{ $project: {
date: { $dateToString: { format: “%Y-%m-%d”, date: “$created_at” } },
amount: 1
}
},
{ $group: {
_id: “$date”,
daily_sales: { $sum: “$amount” },
order_count: { $sum: 1 }
}
},
{ $sort: { _id: 1 } }
])

# 4. 按用户统计销售额
fgedudb> db.fgedu_orders.aggregate([
{ $match: { status: “completed” } },
{ $lookup: {
from: “fgedu_users”,
localField: “user_id”,
foreignField: “_id”,
as: “user_info”
}
},
{ $unwind: “$user_info” },
{ $group: {
_id: “$user_info.name”,
total_sales: { $sum: “$amount” },
order_count: { $sum: 1 }
}
},
{ $sort: { total_sales: -1 } },
{ $limit: 10 }
])

# 5. 按产品类别统计销售额
fgedudb> db.fgedu_orders.aggregate([
{ $match: { status: “completed” } },
{ $unwind: “$items” },
{ $lookup: {
from: “fgedu_products”,
localField: “items.product_id”,
foreignField: “_id”,
as: “product_info”
}
},
{ $unwind: “$product_info” },
{ $group: {
_id: “$product_info.category”,
total_sales: { $sum: { $multiply: [“$items.quantity”, “$items.price”] } },
product_count: { $sum: “$items.quantity” }
}
},
{ $sort: { total_sales: -1 } }
])

# 1. 创建索引
fgedudb> db.fgedu_user_actions.createIndex({ user_id: 1, action_type: 1, action_time: -1 })

# 2. 统计用户行为
fgedudb> db.fgedu_user_actions.aggregate([
{ $match: { action_time: { $gte: new Date(“2026-04-01”), $lte: new Date(“2026-04-30”) } } },
{ $group: {
_id: { user_id: “$user_id”, action_type: “$action_type” },
count: { $sum: 1 }
}
},
{ $group: {
_id: “$user_id”,
actions: { $push: { action_type: “$action_type”, count: “$count” } }
}
},
{ $lookup: {
from: “fgedu_users”,
localField: “_id”,
foreignField: “_id”,
as: “user_info”
}
},
{ $unwind: “$user_info” },
{ $project: {
user_id: “$_id”,
user_name: “$user_info.name”,
actions: 1,
_id: 0
}
}
])

# 3. 统计活跃用户
fgedudb> db.fgedu_user_actions.aggregate([
{ $match: { action_time: { $gte: new Date(Date.now() – 7 * 24 * 60 * 60 * 1000) } } },
{ $group: {
_id: “$user_id”,
action_count: { $sum: 1 }
}
},
{ $match: { action_count: { $gte: 5 } } },
{ $lookup: {
from: “fgedu_users”,
localField: “_id”,
foreignField: “_id”,
as: “user_info”
}
},
{ $unwind: “$user_info” },
{ $project: {
user_id: “$_id”,
user_name: “$user_info.name”,
action_count: 1,
_id: 0
}
},
{ $sort: { action_count: -1 } }
])

# 4. 分析用户购买行为
fgedudb> db.fgedu_user_actions.aggregate([
{ $match: { action_type: “purchase”, action_time: { $gte: new Date(“2026-04-01”), $lte: new Date(“2026-04-30”) } } },
{ $lookup: {
from: “fgedu_orders”,
localField: “order_id”,
foreignField: “_id”,
as: “order_info”
}
},
{ $unwind: “$order_info” },
{ $group: {
_id: “$user_id”,
purchase_count: { $sum: 1 },
total_amount: { $sum: “$order_info.amount” }
}
},
{ $lookup: {
from: “fgedu_users”,
localField: “_id”,
foreignField: “_id”,
as: “user_info”
}
},
{ $unwind: “$user_info” },
{ $project: {
user_id: “$_id”,
user_name: “$user_info.name”,
purchase_count: 1,
total_amount: 1,
avg_purchase: { $divide: [“$total_amount”, “$purchase_count”] },
_id: 0
}
},
{ $sort: { total_amount: -1 } }
])

# 1. 创建索引
fgedudb> db.fgedu_products.createIndex({ category: 1, stock: 1 })
fgedudb> db.fgedu_orders.createIndex({ status: 1, created_at: -1 })

# 2. 分析库存水平
fgedudb> db.fgedu_products.aggregate([
{ $project: {
name: 1,
category: 1,
stock: 1,
stock_status: {
$cond: {
if: { $lte: [“$stock”, 0] },
then: “out_of_stock”,
else: {
$cond: {
if: { $lte: [“$stock”, 10] },
then: “low_stock”,
else: “in_stock”
}
}
}
}
}
},
{ $group: {
_id: { category: “$category”, stock_status: “$stock_status” },
product_count: { $sum: 1 }
}
},
{ $sort: { “_id.category”: 1, “_id.stock_status”: 1 } }
])

# 3. 分析库存周转率
fgedudb> db.fgedu_orders.aggregate([
{ $match: { status: “completed”, created_at: { $gte: new Date(Date.now() – 30 * 24 * 60 * 60 * 1000) } } },
{ $unwind: “$items” },
{ $group: {
_id: “$items.product_id”,
sold_quantity: { $sum: “$items.quantity” }
}
},
{ $lookup: {
from: “fgedu_products”,
localField: “_id”,
foreignField: “_id”,
as: “product_info”
}
},
{ $unwind: “$product_info” },
{ $project: {
product_id: “$_id”,
product_name: “$product_info.name”,
category: “$product_info.category”,
stock: “$product_info.stock”,
sold_quantity: 1,
turnover_rate: { $divide: [“$sold_quantity”, { $add: [“$product_info.stock”, “$sold_quantity”] }] }
}
},
{ $sort: { turnover_rate: -1 } }
])

# 4. 分析缺货情况
fgedudb> db.fgedu_products.aggregate([
{ $match: { stock: { $lte: 0 } } },
{ $group: {
_id: “$category”,
out_of_stock_count: { $sum: 1 }
}
},
{ $sort: { out_of_stock_count: -1 } }
])

# 5. 预测库存需求
fgedudb> db.fgedu_orders.aggregate([
{ $match: { status: “completed”, created_at: { $gte: new Date(Date.now() – 90 * 24 * 60 * 60 * 1000) } } },
{ $unwind: “$items” },
{ $project: {
product_id: “$items.product_id”,
quantity: “$items.quantity”,
month: { $dateToString: { format: “%Y-%m”, date: “$created_at” } }
}
},
{ $group: {
_id: { product_id: “$product_id”, month: “$month” },
monthly_sales: { $sum: “$quantity” }
}
},
{ $group: {
_id: “$product_id”,
avg_monthly_sales: { $avg: “$monthly_sales” },
max_monthly_sales: { $max: “$monthly_sales” }
}
},
{ $lookup: {
from: “fgedu_products”,
localField: “_id”,
foreignField: “_id”,
as: “product_info”
}
},
{ $unwind: “$product_info” },
{ $project: {
product_id: “$_id”,
product_name: “$product_info.name”,
current_stock: “$product_info.stock”,
avg_monthly_sales: 1,
max_monthly_sales: 1,
recommended_stock: { $multiply: [“$max_monthly_sales”, 1.5] },
stock_shortage: {
$cond: {
if: { $lt: [“$product_info.stock”, { $multiply: [“$avg_monthly_sales”, 0.5] }] },
then: “Yes”,
else: “No”
}
}
}
},
{ $sort: { stock_shortage: 1 } }
])