MongoDB aggregateoperation

// aggregate管道basic语法
db.collection.aggregate([
    { "$stage1": { /* 阶段1operation */ } },
    { "$stage2": { /* 阶段2operation */ } },
    { "$stage3": { /* 阶段3operation */ } }
])

// example: 计算每个class别 产品数量 and 平均价格
db.products.aggregate([
    { "$match": { "stock": { "$gt": 0 } } },  // 筛选 has library存 产品
    { "$group": {                            // 按class别group
        "_id": "$category",
        "count": { "$sum": 1 },            // 计算产品数量
        "averagePrice": { "$avg": "$price" }  // 计算平均价格
    } },
    { "$sort": { "count": -1 } }             // 按产品数量降序sort
])

// 准备testdata
db.orders.insertMany([
    {
        "userId": ObjectId("5f8d0f9a9b2c3d4e5f6a7b8c"),
        "products": [
            { "productId": ObjectId("5f8d0f9a9b2c3d4e5f6a7b8d"), "quantity": 2, "price": 100 },
            { "productId": ObjectId("5f8d0f9a9b2c3d4e5f6a7b8e"), "quantity": 1, "price": 200 }
        ],
        "totalAmount": 400,
        "orderDate": ISODate("2020-10-01T08:00:00Z")
    },
    {
        "userId": ObjectId("5f8d0f9a9b2c3d4e5f6a7b8c"),
        "products": [
            { "productId": ObjectId("5f8d0f9a9b2c3d4e5f6a7b8d"), "quantity": 1, "price": 100 }
        ],
        "totalAmount": 100,
        "orderDate": ISODate("2020-10-15T09:00:00Z")
    },
    {
        "userId": ObjectId("5f8d0f9a9b2c3d4e5f6a7b8f"),
        "products": [
            { "productId": ObjectId("5f8d0f9a9b2c3d4e5f6a7b8e"), "quantity": 2, "price": 200 },
            { "productId": ObjectId("5f8d0f9a9b2c3d4e5f6a7b90"), "quantity": 1, "price": 300 }
        ],
        "totalAmount": 700,
        "orderDate": ISODate("2020-11-05T10:00:00Z")
    }
])

// example1: 按月份group计算销售额 and 订单数
db.orders.aggregate([
    {
        "$group": {
            "_id": {
                "year": { "$year": "$orderDate" },
                "month": { "$month": "$orderDate" }
            },
            "totalSales": { "$sum": "$totalAmount" },
            "orderCount": { "$sum": 1 },
            "averageOrderAmount": { "$avg": "$totalAmount" }
        }
    },
    {
        "$sort": {
            "_id.year": 1,
            "_id.month": 1
        }
    }
])

// example2: 计算每个产品 销售数量 and 总销售额
db.orders.aggregate([
    {
        "$unwind": "$products"
    },
    {
        "$group": {
            "_id": "$products.productId",
            "totalQuantity": { "$sum": "$products.quantity" },
            "totalSales": { "$sum": { "$multiply": ["$products.quantity", "$products.price"] } }
        }
    },
    {
        "$sort": {
            "totalSales": -1
        }
    }
])

// example3: 计算每个user 订单数 and 总consume
db.orders.aggregate([
    {
        "$group": {
            "_id": "$userId",
            "orderCount": { "$sum": 1 },
            "totalSpent": { "$sum": "$totalAmount" }
        }
    },
    {
        "$lookup": {
            "from": "users",
            "localField": "_id",
            "foreignField": "_id",
            "as": "userInfo"
        }
    },
    {
        "$unwind": "$userInfo"
    },
    {
        "$project": {
            "userId": "$_id",
            "userName": "$userInfo.name",
            "orderCount": 1,
            "totalSpent": 1,
            "_id": 0
        }
    }
])

// MapReducebasic语法
db.collection.mapReduce(
    function() { /* mapfunction */ },  // mapfunction
    function(key, values) { /* reducefunction */ },  // reducefunction
    {
        out: "resultCollection",  // 输出collection
        query: { /* query条件 */ },  // 可选, filterdocumentation
        sort: { /* sort条件 */ },  // 可选, sortdocumentation
        limit: 100,  // 可选, 限制documentation数量
        finalize: function(key, reducedValue) { /* finalizefunction */ }  // 可选, 最终processingfunction
    }
)

// example: usingMapReduce计算每个class别 产品数量 and 平均价格
db.products.mapReduce(
    // mapfunction
    function() {
        emit(this.category, { count: 1, totalPrice: this.price });
    },
    // reducefunction
    function(key, values) {
        let result = { count: 0, totalPrice: 0 };
        values.forEach(function(value) {
            result.count += value.count;
            result.totalPrice += value.totalPrice;
        });
        return result;
    },
    // 选项
    {
        out: "categoryStats",
        query: { stock: { $gt: 0 } },
        finalize: function(key, reducedValue) {
            reducedValue.averagePrice = reducedValue.totalPrice / reducedValue.count;
            return reducedValue;
        }
    }
)

// 查看结果
db.categoryStats.find()

// 推荐 管道顺序
[   // 1. filterdata, reducing after 续processing data量
    { "$match": { /* filter条件 */ } },
    // 2. sortdata (such as果需要) 
    { "$sort": { /* sort条件 */ } },
    // 3. 限制data量
    { "$limit": 100 },
    // 4. unfoldarray
    { "$unwind": "$arrayField" },
    // 5. groupdata
    { "$group": { /* group条件 */ } },
    // 6. 投影data
    { "$project": { /* 投影条件 */ } }
]