通过多个字段对收集数据进行分组 [英] group collection data by multiple fields mongodb

问题描述

集合结构

Order = new Schema
    index:          { type: Number, unique: true }
    number:         Date
    status:         { type: String, enum: ['success', 'failure'] }
    created_at:     { type: Date, default: Date.now }
    updated_at:     { type: Date, default: Date.now }

我需要一个查询的帮助，该查询返回给我一个对象数组，该数组具有按日期分组的成功计数和失败计数的数据.

I need help with a query that returns me an array of objects having data as success count and failure count grouped by date. Ex-

orders = {
              28-10-2016:{
               success_count: 10, 
               failure_count: 10
              },
              29-10-2016: {
               success_count: 10, 
               failure_count: 10
              }
          }

推荐答案

使用聚合框架，结果将与您的所需"输出略有不同，因为您使用了_id键，其值表示您按字段分组.例如，代替

With the aggregation framework, the result will be slightly different from your "desired" output as instead of having hash keys, you get an array of objects with the _id key having a value that represents you group by field. For instance, instead of

{
    "28-10-2016":{
        "success_count": 10, 
        "failure_count": 10
    },
    "29-10-2016": {
        "success_count": 10, 
        "failure_count": 10
    }
}

您将拥有更好的结构，例如

you'd have a better structure like

[
    {
        "_id": "28-10-2016",
        "success_count": 10, 
        "failure_count": 10
    },
        "_id": "29-10-2016",
        "success_count": 10, 
        "failure_count": 10
    }
]

要实现上述结果，需要使用 $cond >运算符将基于其第一个参数(if)评估逻辑条件，然后在评估为true时返回第二个参数(然后)，或者在第三个参数为false时返回(否则).这会将对/错逻辑转换为1和0数值，并馈入 $sum :

Accomplishing the above result would require using the $cond operator in the $sum accumulator operator. The $cond operator will evaluate a logical condition based on its first argument (if) and then returns the second argument where the evaluation is true (then) or the third argument where false (else). This converts the true/false logic into 1 and 0 numerical values that feed into $sum respectively:

"success_count": {
    "$sum": {
        "$cond": [ { "$eq": [ "$status", "success" ] }, 1, 0 ]
    }
}

作为结果的管道，需要运行使用

As a resulting pipeline, one needs to run the aggregation operation which uses the $dateToString operator in the _id key expression for the $group pipeline:

Orders.aggregate([
    {
        "$group": {
            "_id": {
                "$dateToString": { 
                    "format": "%Y-%m-%d", 
                    "date": "$created_at" 
                }
            },
            "success_count": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$status", "success" ] }, 1, 0 ]
                }
            },
            "failure_count": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$status", "failure" ] }, 1, 0 ]
                }
            }
        }
    }
], function (err, orders){
    if (err) throw err;
    console.log(orders);
})

---

但是，有一种更灵活，性能更好的方法，其执行速度比上述方法快得多，在这种方法中，用于聚合结果的最有效的数据结构遵循以下模式:

---

However, there is a more flexible and better performant approach which executes much faster than the above, where the most efficient data structure for your aggregation result follows the schema for example:

orders = [
    {
        "_id": "28-10-2016",
        "counts": [
            { "status": "success", "count": 10 },
            { "status": "failure", "count": 10 }
        ]
    },
    {
        "_id": "29-10-2016",
        "counts": [
            { "status": "success", "count": 10 },
            { "status": "failure", "count": 10 }
        ]
    }
]

然后考虑按以下方式运行替代管道

Then consider running an alternative pipeline as follows

Orders.aggregate([
    { 
        "$group": {
            "_id": { 
                "date":  {
                    "$dateToString": { 
                        "format": "%Y-%m-%d", 
                        "date": "$created_at" 
                    }
                },
                "status": { "$toLower": "$status" }
            },
            "count": { "$sum": 1 }
        }
    },
    { 
        "$group": {
            "_id": "$_id.date",
            "counts": {
                "$push": {
                    "status": "$_id.status",
                    "count": "$count"
                }
            }
        }
    }
], function (err, orders){
    if (err) throw err;
    console.log(orders);
})

这篇关于通过多个字段对收集数据进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！