Maîtrisez le framework d'agrégation à travers des cas pratiques
Vous travaillez pour "MarocMart", une chaîne de supermarchés marocaine. Analysons les ventes de la collection sales.
{
"_id": ObjectId("..."),
"date": ISODate("2024-03-15T10:30:00Z"),
"store": {
"id": "STR001",
"name": "MarocMart Maarif",
"city": "Casablanca"
},
"items": [
{
"product": "Lait demi-écrémé",
"category": "Produits laitiers",
"price": 7.50,
"quantity": 2
},
{
"product": "Pain complet",
"category": "Boulangerie",
"price": 3.50,
"quantity": 1
}
],
"total": 18.50,
"paymentMethod": "cash"
}db.sales.aggregate([
// Stage 1 : Filtrer Mars 2024
{ $match: {
date: {
$gte: ISODate("2024-03-01T00:00:00Z"),
$lt: ISODate("2024-04-01T00:00:00Z")
}
}},
// Stage 2 : Calculer les statistiques
{ $group: {
_id: null, // Regrouper tous les documents
totalRevenue: { $sum: "$total" },
avgSale: { $avg: "$total" },
minSale: { $min: "$total" },
maxSale: { $max: "$total" },
transactionCount: { $sum: 1 },
// Collecter les méthodes de paiement uniques
paymentMethods: { $addToSet: "$paymentMethod" }
}},
// Stage 3 : Formater le résultat
{ $project: {
_id: 0,
"Statistiques Mars 2024": {
"Chiffre d'affaires total": { $round: ["$totalRevenue", 2] },
"Vente moyenne": { $round: ["$avgSale", 2] },
"Vente minimale": "$minSale",
"Vente maximale": "$maxSale",
"Nombre de transactions": "$transactionCount",
"Méthodes de paiement": "$paymentMethods"
}
}}
])
// Résultat attendu :
{
"Statistiques Mars 2024": {
"Chiffre d'affaires total": 458750.50,
"Vente moyenne": 76.46,
"Vente minimale": 3.50,
"Vente maximale": 890.00,
"Nombre de transactions": 6000,
"Méthodes de paiement": ["cash", "card", "mobile"]
}
}Créez un rapport montrant pour chaque magasin :
db.sales.aggregate([
// Stage 1 : Décomposer les articles
{ $unwind: "$items" },
// Stage 2 : Calculer le montant par article
{ $addFields: {
"items.totalAmount": {
$multiply: ["$items.price", "$items.quantity"]
}
}},
// Stage 3 : Regrouper par magasin et catégorie
{ $group: {
_id: {
storeId: "$store.id",
storeName: "$store.name",
storeCity: "$store.city",
category: "$items.category"
},
categoryRevenue: { $sum: "$items.totalAmount" },
products: {
$push: {
name: "$items.product",
quantity: "$items.quantity"
}
},
transactions: { $addToSet: "$_id" }
}},
// Stage 4 : Calculer les top produits par catégorie
{ $addFields: {
topProducts: {
$slice: [
{ $sortArray: {
input: "$products",
sortBy: { quantity: -1 }
}},
3
]
}
}},
// Stage 5 : Regrouper par magasin
{ $group: {
_id: {
storeId: "$_id.storeId",
storeName: "$_id.storeName",
storeCity: "$_id.storeCity"
},
categoriesBreakdown: {
$push: {
category: "$_id.category",
revenue: { $round: ["$categoryRevenue", 2] }
}
},
totalRevenue: { $sum: "$categoryRevenue" },
transactionCount: {
$sum: { $size: "$transactions" }
}
}},
// Stage 6 : Jointure pour les méthodes de paiement
{ $lookup: {
from: "sales",
let: { storeId: "$_id.storeId" },
pipeline: [
{ $match: {
$expr: { $eq: ["$store.id", "$$storeId"] }
}},
{ $group: {
_id: "$paymentMethod",
count: { $sum: 1 }
}}
],
as: "paymentMethods"
}},
// Stage 7 : Formater le résultat final
{ $project: {
_id: 0,
store: {
id: "$_id.storeId",
name: "$_id.storeName",
city: "$_id.storeCity"
},
totalRevenue: { $round: ["$totalRevenue", 2] },
transactionCount: 1,
categoriesBreakdown: {
$sortArray: {
input: "$categoriesBreakdown",
sortBy: { revenue: -1 }
}
},
paymentMethods: {
$map: {
input: "$paymentMethods",
as: "pm",
in: {
method: "$$pm._id",
percentage: {
$round: [
{ $multiply: [
{ $divide: ["$$pm.count", "$transactionCount"] },
100
]},
1
]
}
}
}
}
}},
{ $sort: { totalRevenue: -1 } }
])
// Exemple de résultat pour un magasin :
{
"store": {
"id": "STR001",
"name": "MarocMart Maarif",
"city": "Casablanca"
},
"totalRevenue": 125430.75,
"transactionCount": 1543,
"categoriesBreakdown": [
{ "category": "Produits laitiers", "revenue": 34521.50 },
{ "category": "Fruits et légumes", "revenue": 28900.25 },
{ "category": "Boulangerie", "revenue": 22100.00 }
],
"paymentMethods": [
{ "method": "card", "percentage": 45.5 },
{ "method": "cash", "percentage": 42.3 },
{ "method": "mobile", "percentage": 12.2 }
]
}La direction de MarocMart veut un dashboard complet pour analyser les patterns de vente.
Créez un pipeline unique qui fournit :
db.sales.aggregate([
// Filtrer l'année 2024
{ $match: {
date: {
$gte: ISODate("2024-01-01"),
$lt: ISODate("2025-01-01")
}
}},
// Analyses multiples avec $facet
{ $facet: {
// Analyse 1 : Par jour de la semaine
"ventesParJourSemaine": [
{ $group: {
_id: { $dayOfWeek: "$date" },
totalVentes: { $sum: "$total" },
nombreTransactions: { $sum: 1 },
ticketMoyen: { $avg: "$total" }
}},
{ $project: {
jour: {
$switch: {
branches: [
{ case: { $eq: ["$_id", 1] }, then: "Dimanche" },
{ case: { $eq: ["$_id", 2] }, then: "Lundi" },
{ case: { $eq: ["$_id", 3] }, then: "Mardi" },
{ case: { $eq: ["$_id", 4] }, then: "Mercredi" },
{ case: { $eq: ["$_id", 5] }, then: "Jeudi" },
{ case: { $eq: ["$_id", 6] }, then: "Vendredi" },
{ case: { $eq: ["$_id", 7] }, then: "Samedi" }
]
}
},
totalVentes: { $round: ["$totalVentes", 2] },
nombreTransactions: 1,
ticketMoyen: { $round: ["$ticketMoyen", 2] }
}},
{ $sort: { "_id": 1 } }
],
// Analyse 2 : Par heure de la journée
"ventesParHeure": [
{ $group: {
_id: { $hour: "$date" },
totalVentes: { $sum: "$total" },
nombreTransactions: { $sum: 1 }
}},
{ $project: {
heure: {
$concat: [
{ $toString: "$_id" },
"h00"
]
},
totalVentes: { $round: ["$totalVentes", 2] },
nombreTransactions: 1,
// Indicateur de période de pointe
periodePointe: {
$cond: {
if: {
$or: [
{ $and: [{ $gte: ["$_id", 11] }, { $lte: ["$_id", 14] }] },
{ $and: [{ $gte: ["$_id", 18] }, { $lte: ["$_id", 20] }] }
]
},
then: true,
else: false
}
}
}},
{ $sort: { "_id": 1 } }
],
// Analyse 3 : Évolution mensuelle
"evolutionMensuelle": [
{ $group: {
_id: {
year: { $year: "$date" },
month: { $month: "$date" }
},
totalVentes: { $sum: "$total" },
nombreTransactions: { $sum: 1 },
ticketMoyen: { $avg: "$total" }
}},
{ $project: {
mois: {
$dateToString: {
format: "%Y-%m",
date: {
$dateFromParts: {
year: "$_id.year",
month: "$_id.month"
}
}
}
},
totalVentes: { $round: ["$totalVentes", 2] },
nombreTransactions: 1,
ticketMoyen: { $round: ["$ticketMoyen", 2] },
// Calcul de la croissance (nécessite une jointure ou window function)
croissance: null
}},
{ $sort: { mois: 1 } }
],
// Analyse 4 : Top 5 jours
"top5Jours": [
{ $group: {
_id: {
$dateToString: {
format: "%Y-%m-%d",
date: "$date"
}
},
totalVentes: { $sum: "$total" },
nombreTransactions: { $sum: 1 }
}},
{ $sort: { totalVentes: -1 } },
{ $limit: 5 },
{ $project: {
date: "$_id",
totalVentes: { $round: ["$totalVentes", 2] },
nombreTransactions: 1,
// Ajouter le jour de la semaine pour contexte
jourSemaine: {
$dayOfWeek: {
$dateFromString: {
dateString: "$_id"
}
}
}
}}
],
// Bonus : Statistiques globales
"statistiquesGlobales": [
{ $group: {
_id: null,
totalAnnuel: { $sum: "$total" },
moyenneJournaliere: { $avg: "$total" },
totalTransactions: { $sum: 1 }
}},
{ $project: {
_id: 0,
totalAnnuel: { $round: ["$totalAnnuel", 2] },
moyenneJournaliere: { $round: ["$moyenneJournaliere", 2] },
totalTransactions: 1,
previsionAnnuelle: {
$round: [
{ $multiply: [
"$moyenneJournaliere",
365
]},
2
]
}
}}
]
}}
]){
"ventesParJourSemaine": [
{ "jour": "Lundi", "totalVentes": 125000, "nombreTransactions": 1650, "ticketMoyen": 75.76 },
{ "jour": "Samedi", "totalVentes": 198000, "nombreTransactions": 2450, "ticketMoyen": 80.82 }
// ...
],
"ventesParHeure": [
{ "heure": "9h00", "totalVentes": 45000, "nombreTransactions": 580, "periodePointe": false },
{ "heure": "12h00", "totalVentes": 89000, "nombreTransactions": 1120, "periodePointe": true }
// ...
],
"evolutionMensuelle": [
{ "mois": "2024-01", "totalVentes": 850000, "nombreTransactions": 11200, "ticketMoyen": 75.89 }
// ...
],
"top5Jours": [
{ "date": "2024-12-24", "totalVentes": 45600, "nombreTransactions": 890 }
// ...
],
"statistiquesGlobales": [{
"totalAnnuel": 12500000,
"moyenneJournaliere": 34246.58,
"totalTransactions": 165000
}]
}MarocMart a trois collections liées :
sales : Les ventescustomers : Les clients (programme fidélité)products : Le catalogue produits{
"_id": "CUST001",
"name": "Fatima Benali",
"email": "f.benali@email.ma",
"loyaltyCard": "GOLD",
"points": 2450,
"registeredDate": ISODate("2022-03-15")
}{
"_id": "PROD001",
"name": "Huile d'olive extra vierge",
"category": "Épicerie",
"supplier": "Coopérative Atlas",
"price": 45.00,
"stock": 150
}db.sales.aggregate([
// Limiter pour l'exemple
{ $limit: 100 },
// Jointure avec customers
{ $lookup: {
from: "customers",
localField: "customerId",
foreignField: "_id",
as: "customer"
}},
// Dérouler le customer (0 ou 1)
{ $unwind: {
path: "$customer",
preserveNullAndEmptyArrays: true // Garder les ventes sans client fidélité
}},
// Décomposer les items pour enrichir chaque produit
{ $unwind: "$items" },
// Jointure avancée avec products
{ $lookup: {
from: "products",
let: {
productName: "$items.product",
quantity: "$items.quantity"
},
pipeline: [
// Matcher sur le nom du produit
{ $match: {
$expr: { $eq: ["$name", "$$productName"] }
}},
// Vérifier le stock
{ $project: {
_id: 1,
name: 1,
category: 1,
supplier: 1,
currentStock: "$stock",
stockStatus: {
$cond: {
if: { $lte: ["$stock", "$$quantity"] },
then: "Rupture proche",
else: "Stock OK"
}
}
}}
],
as: "productInfo"
}},
{ $unwind: {
path: "$productInfo",
preserveNullAndEmptyArrays: true
}},
// Calculer les points de fidélité
{ $addFields: {
"items.loyaltyPoints": {
$cond: {
if: { $ne: ["$customer", null] },
then: {
$floor: {
$divide: [
{ $multiply: ["$items.price", "$items.quantity"] },
10
]
}
},
else: 0
}
},
"items.productDetails": "$productInfo"
}},
// Regrouper les items
{ $group: {
_id: "$_id",
date: { $first: "$date" },
store: { $first: "$store" },
customer: { $first: "$customer" },
total: { $first: "$total" },
paymentMethod: { $first: "$paymentMethod" },
items: { $push: "$items" },
totalLoyaltyPoints: { $sum: "$items.loyaltyPoints" },
// Collecter les produits en rupture
lowStockItems: {
$push: {
$cond: {
if: {
$eq: ["$productInfo.stockStatus", "Rupture proche"]
},
then: {
product: "$items.product",
stockRestant: "$productInfo.currentStock"
},
else: null
}
}
}
}},
// Nettoyer les valeurs null dans lowStockItems
{ $addFields: {
lowStockItems: {
$filter: {
input: "$lowStockItems",
as: "item",
cond: { $ne: ["$$item", null] }
}
}
}},
// Projection finale
{ $project: {
saleId: "$_id",
date: 1,
store: "$store.name",
// Info client enrichie
customer: {
$cond: {
if: { $ne: ["$customer", null] },
then: {
name: "$customer.name",
loyaltyCard: "$customer.loyaltyCard",
currentPoints: "$customer.points",
newPointsEarned: "$totalLoyaltyPoints",
totalPointsAfter: {
$add: ["$customer.points", "$totalLoyaltyPoints"]
}
},
else: "Client non fidélisé"
}
},
// Résumé de la vente
sale: {
total: "$total",
itemCount: { $size: "$items" },
paymentMethod: "$paymentMethod"
},
// Alertes stock
alerts: {
$cond: {
if: { $gt: [{ $size: "$lowStockItems" }, 0] },
then: {
hasLowStock: true,
items: "$lowStockItems"
},
else: {
hasLowStock: false
}
}
}
}}
])
// Exemple de résultat enrichi :
{
"saleId": ObjectId("..."),
"date": ISODate("2024-03-15T10:30:00Z"),
"store": "MarocMart Maarif",
"customer": {
"name": "Fatima Benali",
"loyaltyCard": "GOLD",
"currentPoints": 2450,
"newPointsEarned": 18,
"totalPointsAfter": 2468
},
"sale": {
"total": 185.50,
"itemCount": 5,
"paymentMethod": "card"
},
"alerts": {
"hasLowStock": true,
"items": [
{
"product": "Huile d'argan bio",
"stockRestant": 3
}
]
}
}MarocMart veut analyser les paniers clients pour optimiser les promotions et le cross-selling.
// Pipeline pour l'analyse d'association de produits
db.sales.aggregate([
// Filtrer les paniers avec au moins 2 articles
{ $match: {
$expr: { $gte: [{ $size: "$items" }, 2] }
}},
// Créer toutes les paires possibles de produits dans chaque panier
{ $project: {
store: 1,
date: 1,
// Générer les paires avec indices
productPairs: {
$reduce: {
input: { $range: [0, { $size: "$items" }] },
initialValue: [],
in: {
$concatArrays: [
"$$value",
{
$map: {
input: {
$range: [
{ $add: ["$$this", 1] },
{ $size: "$items" }
]
},
as: "j",
in: {
product1: { $arrayElemAt: ["$items.product", "$$this"] },
product2: { $arrayElemAt: ["$items.product", "$$j"] },
category1: { $arrayElemAt: ["$items.category", "$$this"] },
category2: { $arrayElemAt: ["$items.category", "$$j"] },
revenue: {
$add: [
{ $multiply: [
{ $arrayElemAt: ["$items.price", "$$this"] },
{ $arrayElemAt: ["$items.quantity", "$$this"] }
]},
{ $multiply: [
{ $arrayElemAt: ["$items.price", "$$j"] },
{ $arrayElemAt: ["$items.quantity", "$$j"] }
]}
]
}
}
}
}
]
}
}
},
// Garder les détails du panier pour analyses
basketDetails: {
totalItems: { $size: "$items" },
totalRevenue: "$total",
categories: { $setUnion: ["$items.category", []] }
}
}},
// Décomposer les paires
{ $unwind: "$productPairs" },
// Normaliser l'ordre des paires (A,B) = (B,A)
{ $project: {
store: 1,
date: 1,
basketDetails: 1,
pair: {
$cond: {
if: { $lte: ["$productPairs.product1", "$productPairs.product2"] },
then: {
products: ["$productPairs.product1", "$productPairs.product2"],
categories: ["$productPairs.category1", "$productPairs.category2"]
},
else: {
products: ["$productPairs.product2", "$productPairs.product1"],
categories: ["$productPairs.category2", "$productPairs.category1"]
}
}
},
pairRevenue: "$productPairs.revenue"
}},
// Analyser les associations
{ $group: {
_id: "$pair",
// Métriques d'association
frequency: { $sum: 1 },
totalRevenue: { $sum: "$pairRevenue" },
avgRevenue: { $avg: "$pairRevenue" },
// Contexte des paniers
avgBasketSize: { $avg: "$basketDetails.totalItems" },
basketsContainingPair: { $addToSet: "$_id" },
// Analyse temporelle
firstSeen: { $min: "$date" },
lastSeen: { $max: "$date" },
// Distribution par magasin
storeDistribution: {
$push: "$store.city"
}
}},
// Calculer des métriques avancées
{ $project: {
products: "$_id.products",
categories: "$_id.categories",
// KPIs d'association
metrics: {
frequency: "$frequency",
totalRevenue: { $round: ["$totalRevenue", 2] },
avgRevenue: { $round: ["$avgRevenue", 2] },
avgBasketSize: { $round: ["$avgBasketSize", 1] }
},
// Score de confiance (simplifié)
confidenceScore: {
$round: [
{ $multiply: [
{ $divide: ["$frequency", 100] }, // Normaliser
{ $divide: ["$avgRevenue", 50] } // Normaliser
]},
3
]
},
// Analyse géographique
topStores: {
$slice: [
{ $sortArray: {
input: {
$objectToArray: {
$reduce: {
input: "$storeDistribution",
initialValue: {},
in: {
$mergeObjects: [
"$$value",
{
$literal: {
$concat: ["$$this", ""]
}: {
$add: [
{ $ifNull: ["$$value.$$this", 0] },
1
]
}
}
]
}
}
}
},
sortBy: { v: -1 }
}},
3
]
},
// Suggestion de promotion
promotionSuggestion: {
$switch: {
branches: [
{
case: { $gte: ["$frequency", 500] },
then: "Bundle haute fréquence - Réduction 10%"
},
{
case: { $and: [
{ $gte: ["$avgRevenue", 100] },
{ $gte: ["$frequency", 200] }
]},
then: "Bundle premium - Réduction 15%"
},
{
case: { $gte: ["$frequency", 100] },
then: "Cross-sell opportunité"
}
],
default: "Surveiller"
}
}
}},
// Top associations
{ $sort: { "metrics.frequency": -1 } },
{ $limit: 20 }
])
// Exemple de résultat :
{
"products": ["Pain complet", "Beurre fermier"],
"categories": ["Boulangerie", "Produits laitiers"],
"metrics": {
"frequency": 1234,
"totalRevenue": 15420.50,
"avgRevenue": 12.50,
"avgBasketSize": 5.2
},
"confidenceScore": 2.468,
"topStores": [
{ "k": "Casablanca", "v": 456 },
{ "k": "Rabat", "v": 389 },
{ "k": "Tanger", "v": 234 }
],
"promotionSuggestion": "Bundle haute fréquence - Réduction 10%"
}Le PDG de MarocMart veut un dashboard exécutif avec des KPIs avancés incluant des tendances et comparaisons.
// Solution avec MongoDB 5.0+ (Window Functions)
db.sales.aggregate([
// Agrégation mensuelle par magasin
{ $group: {
_id: {
year: { $year: "$date" },
month: { $month: "$date" },
storeId: "$store.id",
storeName: "$store.name",
storeCity: "$store.city"
},
monthlyRevenue: { $sum: "$total" },
transactionCount: { $sum: 1 },
avgTransaction: { $avg: "$total" },
uniqueProducts: { $addToSet: "$items.product" }
}},
// Créer un champ date pour le tri
{ $addFields: {
monthDate: {
$dateFromParts: {
year: "$_id.year",
month: "$_id.month",
day: 1
}
},
productDiversity: { $size: "$uniqueProducts" }
}},
// Window Functions pour analyses avancées
{ $setWindowFields: {
partitionBy: {
storeId: "$_id.storeId"
},
sortBy: { monthDate: 1 },
output: {
// Chiffre d'affaires cumulé
cumulativeRevenue: {
$sum: "$monthlyRevenue",
window: {
documents: ["unbounded", "current"]
}
},
// Revenu du mois précédent
previousMonthRevenue: {
$shift: {
output: "$monthlyRevenue",
by: -1
}
},
// Moyenne mobile 3 mois
movingAvg3Months: {
$avg: "$monthlyRevenue",
window: {
documents: [-2, 0]
}
},
// Rang dans le mois (tous magasins)
monthlyRank: {
$rank: {}
}
}
}},
// Calculer les métriques dérivées
{ $addFields: {
// Croissance mois sur mois
monthlyGrowth: {
$cond: {
if: { $gt: ["$previousMonthRevenue", 0] },
then: {
$round: [
{ $multiply: [
{ $divide: [
{ $subtract: ["$monthlyRevenue", "$previousMonthRevenue"] },
"$previousMonthRevenue"
]},
100
]},
2
]
},
else: null
}
},
// Écart par rapport à la moyenne mobile
deviationFromTrend: {
$round: [
{ $subtract: [
"$monthlyRevenue",
"$movingAvg3Months"
]},
2
]
},
// Performance relative (base 100)
performanceIndex: {
$round: [
{ $multiply: [
{ $divide: ["$monthlyRevenue", "$movingAvg3Months"] },
100
]},
1
]
}
}},
// Enrichir avec des statistiques globales
{ $facet: {
// Données détaillées
"detailsByStore": [
{ $sort: { "_id.storeId": 1, monthDate: 1 } },
{ $project: {
_id: 0,
store: {
id: "$_id.storeId",
name: "$_id.storeName",
city: "$_id.storeCity"
},
period: {
$dateToString: {
format: "%Y-%m",
date: "$monthDate"
}
},
revenue: {
monthly: { $round: ["$monthlyRevenue", 2] },
cumulative: { $round: ["$cumulativeRevenue", 2] },
movingAvg: { $round: ["$movingAvg3Months", 2] }
},
growth: {
percentage: "$monthlyGrowth",
trend: {
$switch: {
branches: [
{ case: { $gt: ["$monthlyGrowth", 10] }, then: "↗️ Forte croissance" },
{ case: { $gt: ["$monthlyGrowth", 0] }, then: "↗️ Croissance" },
{ case: { $lt: ["$monthlyGrowth", -10] }, then: "↘️ Forte baisse" },
{ case: { $lt: ["$monthlyGrowth", 0] }, then: "↘️ Baisse" }
],
default: "→ Stable"
}
}
},
performance: {
index: "$performanceIndex",
rank: "$monthlyRank",
status: {
$switch: {
branches: [
{ case: { $gte: ["$performanceIndex", 110] }, then: "🌟 Excellent" },
{ case: { $gte: ["$performanceIndex", 100] }, then: "✅ Bon" },
{ case: { $gte: ["$performanceIndex", 90] }, then: "⚠️ À surveiller" },
{ case: { $lt: ["$performanceIndex", 90] }, then: "🔴 Critique" }
]
}
}
},
operations: {
transactions: "$transactionCount",
avgTicket: { $round: ["$avgTransaction", 2] },
productDiversity: "$productDiversity"
}
}}
],
// Résumé exécutif
"executiveSummary": [
{ $group: {
_id: null,
totalRevenue2024: { $sum: "$monthlyRevenue" },
avgMonthlyRevenue: { $avg: "$monthlyRevenue" },
topPerformingMonth: { $max: {
revenue: "$monthlyRevenue",
month: "$monthDate"
}},
// Identifier les magasins stars
storePerformances: {
$push: {
store: "$_id.storeName",
avgRevenue: "$monthlyRevenue",
lastGrowth: "$monthlyGrowth"
}
}
}},
{ $project: {
_id: 0,
year2024: {
total: { $round: ["$totalRevenue2024", 2] },
monthlyAverage: { $round: ["$avgMonthlyRevenue", 2] },
bestMonth: "$topPerformingMonth"
},
// Top 3 magasins
topStores: {
$slice: [
{ $sortArray: {
input: "$storePerformances",
sortBy: { avgRevenue: -1 }
}},
3
]
}
}}
]
}}
])
// Alternative sans Window Functions (MongoDB < 5.0)
// Utilisation de self-lookup pour calculer les métriques
db.sales.aggregate([
// Étape 1 : Agrégation mensuelle
{ $group: {
_id: {
yearMonth: {
$dateToString: {
format: "%Y-%m",
date: "$date"
}
},
storeId: "$store.id",
storeName: "$store.name"
},
revenue: { $sum: "$total" },
transactions: { $sum: 1 }
}},
// Étape 2 : Self-lookup pour historique
{ $lookup: {
from: "sales",
let: {
currentMonth: "$_id.yearMonth",
currentStore: "$_id.storeId"
},
pipeline: [
{ $match: {
$expr: {
$and: [
{ $eq: ["$store.id", "$$currentStore"] },
{ $lte: [
{ $dateToString: {
format: "%Y-%m",
date: "$date"
}},
"$$currentMonth"
]}
]
}
}},
{ $group: {
_id: {
$dateToString: {
format: "%Y-%m",
date: "$date"
}
},
revenue: { $sum: "$total" }
}},
{ $sort: { _id: 1 } }
],
as: "historicalData"
}},
// Étape 3 : Calculer les métriques cumulatives
{ $addFields: {
cumulativeRevenue: { $sum: "$historicalData.revenue" },
// Trouver le mois précédent
previousMonth: {
$let: {
vars: {
sortedHistory: {
$filter: {
input: "$historicalData",
cond: { $lt: ["$$this._id", "$_id.yearMonth"] }
}
}
},
in: { $last: "$$sortedHistory" }
}
}
}}
]){
"detailsByStore": [
{
"store": {
"id": "STR001",
"name": "MarocMart Maarif",
"city": "Casablanca"
},
"period": "2024-03",
"revenue": {
"monthly": 458750.50,
"cumulative": 1234567.89,
"movingAvg": 425000.00
},
"growth": {
"percentage": 8.5,
"trend": "↗️ Croissance"
},
"performance": {
"index": 108,
"rank": 2,
"status": "✅ Bon"
},
"operations": {
"transactions": 6543,
"avgTicket": 70.15,
"productDiversity": 234
}
}
],
"executiveSummary": [{
"year2024": {
"total": 15678900.50,
"monthlyAverage": 1306575.04,
"bestMonth": {
"revenue": 1890000.00,
"month": "2024-12"
}
},
"topStores": [
{ "store": "MarocMart Marina", "avgRevenue": 567000 },
{ "store": "MarocMart Maarif", "avgRevenue": 458000 },
{ "store": "MarocMart Anfa", "avgRevenue": 445000 }
]
}]
}Créez un système de recommandation complet pour MarocMart qui suggère des produits personnalisés aux clients.
// Système de recommandation hybride
function getRecommendations(customerId) {
return db.sales.aggregate([
// Phase 1 : Profil du client cible
{ $facet: {
// Historique d'achat du client
"customerProfile": [
{ $match: { customerId: customerId } },
{ $unwind: "$items" },
{ $group: {
_id: null,
purchasedProducts: {
$addToSet: "$items.product"
},
favoriteCategories: {
$push: "$items.category"
},
avgSpending: { $avg: "$total" },
totalSpent: { $sum: "$total" },
purchaseCount: { $sum: 1 },
lastPurchase: { $max: "$date" }
}},
{ $addFields: {
// Top 3 catégories
topCategories: {
$slice: [
{ $sortArray: {
input: {
$reduce: {
input: "$favoriteCategories",
initialValue: [],
in: {
$concatArrays: [
{ $filter: {
input: "$$value",
cond: {
$ne: ["$$this.category", "$$this"]
}
}},
[{
category: "$$this",
count: {
$size: {
$filter: {
input: "$favoriteCategories",
cond: {
$eq: ["$$this", "$$this"]
}
}
}
}
}]
]
}
}
},
sortBy: { count: -1 }
}},
3
]
}
}}
],
// Clients similaires (Collaborative Filtering)
"similarCustomers": [
{ $match: { customerId: customerId } },
{ $unwind: "$items" },
{ $group: {
_id: "$items.product",
buyers: { $addToSet: "$customerId" }
}},
// Trouver qui a acheté les mêmes produits
{ $lookup: {
from: "sales",
let: { product: "$_id" },
pipeline: [
{ $unwind: "$items" },
{ $match: {
$expr: {
$and: [
{ $eq: ["$items.product", "$$product"] },
{ $ne: ["$customerId", customerId] }
]
}
}},
{ $group: {
_id: "$customerId",
commonProducts: { $sum: 1 }
}}
],
as: "otherBuyers"
}},
{ $unwind: "$otherBuyers" },
{ $group: {
_id: "$otherBuyers._id",
similarity: { $sum: "$otherBuyers.commonProducts" }
}},
{ $sort: { similarity: -1 } },
{ $limit: 50 } // Top 50 clients similaires
]
}},
// Phase 2 : Générer les recommandations
{ $project: {
customerData: { $first: "$customerProfile" },
similarCustomerIds: "$similarCustomers._id"
}},
// Produits achetés par clients similaires
{ $lookup: {
from: "sales",
let: {
similarIds: "$similarCustomerIds",
purchasedProducts: "$customerData.purchasedProducts"
},
pipeline: [
{ $match: {
$expr: {
$in: ["$customerId", "$$similarIds"]
}
}},
{ $unwind: "$items" },
// Exclure les produits déjà achetés
{ $match: {
$expr: {
$not: {
$in: ["$items.product", "$$purchasedProducts"]
}
}
}},
{ $group: {
_id: {
product: "$items.product",
category: "$items.category"
},
purchaseCount: { $sum: 1 },
avgPrice: { $avg: "$items.price" },
buyers: { $addToSet: "$customerId" }
}}
],
as: "candidateProducts"
}},
// Phase 3 : Scorer et classer les recommandations
{ $unwind: "$candidateProducts" },
{ $project: {
product: "$candidateProducts._id.product",
category: "$candidateProducts._id.category",
// Calcul du score de recommandation
recommendationScore: {
$add: [
// Score de popularité (0-40 points)
{ $multiply: [
{ $min: [
{ $divide: ["$candidateProducts.purchaseCount", 10] },
1
]},
40
]},
// Score de catégorie (0-30 points)
{ $cond: {
if: {
$in: [
"$candidateProducts._id.category",
"$customerData.topCategories.category"
]
},
then: 30,
else: 10
}},
// Score de prix (0-20 points)
{ $multiply: [
{ $abs: {
$subtract: [
1,
{ $divide: [
{ $abs: {
$subtract: [
"$candidateProducts.avgPrice",
"$customerData.avgSpending"
]
}},
"$customerData.avgSpending"
]}
]
}},
20
]},
// Score de diversité (0-10 points)
{ $cond: {
if: { $gte: [
{ $size: "$candidateProducts.buyers" },
5
]},
then: 10,
else: 5
}}
]
},
// Métadonnées pour explication
metadata: {
popularityScore: "$candidateProducts.purchaseCount",
buyerCount: { $size: "$candidateProducts.buyers" },
avgPrice: { $round: ["$candidateProducts.avgPrice", 2] },
inFavoriteCategory: {
$in: [
"$candidateProducts._id.category",
"$customerData.topCategories.category"
]
}
}
}},
// Enrichir avec détails produits
{ $lookup: {
from: "products",
localField: "product",
foreignField: "name",
as: "productDetails"
}},
{ $unwind: {
path: "$productDetails",
preserveNullAndEmptyArrays: true
}},
// Finaliser les recommandations
{ $project: {
productName: "$product",
category: "$category",
score: { $round: ["$recommendationScore", 1] },
confidence: {
$switch: {
branches: [
{ case: { $gte: ["$recommendationScore", 80] }, then: "Très élevée" },
{ case: { $gte: ["$recommendationScore", 60] }, then: "Élevée" },
{ case: { $gte: ["$recommendationScore", 40] }, then: "Moyenne" }
],
default: "Faible"
}
},
reason: {
$concat: [
"Acheté par ",
{ $toString: "$metadata.buyerCount" },
" clients similaires",
{ $cond: {
if: "$metadata.inFavoriteCategory",
then: " • Dans vos catégories préférées",
else: ""
}}
]
},
price: "$metadata.avgPrice",
inStock: { $ifNull: ["$productDetails.stock", "N/A"] }
}},
// Top 10 recommandations
{ $sort: { score: -1 } },
{ $limit: 10 }
], { allowDiskUse: true });
}
// Utilisation
const recommendations = getRecommendations("CUST001");
// Résultat exemple :
[
{
"productName": "Huile d'argan bio",
"category": "Épicerie",
"score": 85.5,
"confidence": "Très élevée",
"reason": "Acheté par 23 clients similaires • Dans vos catégories préférées",
"price": 89.90,
"inStock": 45
},
{
"productName": "Thé vert à la menthe Premium",
"category": "Boissons",
"score": 78.2,
"confidence": "Élevée",
"reason": "Acheté par 18 clients similaires • Dans vos catégories préférées",
"price": 34.50,
"inStock": 120
}
// ...
]