mongodb - 在开始和结束范围内分组和计数

Question

如果我有以下格式的数据:

[
  {
    _id: 1,
    startDate: ISODate("2017-01-1T00:00:00.000Z"),
    endDate: ISODate("2017-02-25T00:00:00.000Z"),
    type: 'CAR'
  },
  {
    _id: 2,
    startDate: ISODate("2017-02-17T00:00:00.000Z"),
    endDate: ISODate("2017-03-22T00:00:00.000Z"),
    type: 'HGV'
  }
]

是否可以检索按“类型”分组的数据，但也可以检索给定日期范围内每个月的类型计数，例如在 2017/1/1 到 2017/4/1 之间将返回:

[
  {
   _id: 'CAR', 
   monthCounts: [
     /*January*/
     {
       from: ISODate("2017-01-1T00:00:00.000Z"), 
       to: ISODate("2017-01-31T23:59:59.999Z"), 
       count: 1
     },
     /*February*/
     {
       from: ISODate("2017-02-1T00:00:00.000Z"), 
       to: ISODate("2017-02-28T23:59:59.999Z"), 
       count: 1
     },
     /*March*/
     {
       from: ISODate("2017-03-1T00:00:00.000Z"), 
       to: ISODate("2017-03-31T23:59:59.999Z"), 
       count: 0
     },
   ]
  },
  {
   _id: 'HGV', 
   monthCounts: [
     {
       from: ISODate("2017-01-1T00:00:00.000Z"), 
       to: ISODate("2017-01-31T23:59:59.999Z"), 
       count: 0
     },
     {
       from: ISODate("2017-02-1T00:00:00.000Z"), 
       to: ISODate("2017-02-28T23:59:59.999Z"), 
       count: 1
     },
     {
       from: ISODate("2017-03-1T00:00:00.000Z"), 
       to: ISODate("2017-03-31T23:59:59.999Z"), 
       count: 1
     },
   ]
  }
]

返回的格式并不是很重要，但我想要实现的是在单个查询中检索同一分组的多个计数(每月一次)。输入可以只是报告的开始日期和结束日期，或者更有可能是要分组的日期范围数组。

Answer 1

此算法基本上是在两个值的区间之间“迭代”值。 MongoDB 有几种方法来处理这个问题，即 mapReduce() 一直存在的方法。以及 aggregate() 可用的新功能方法。

我将扩展您的选择以故意显示重叠的月份，因为您的示例没有月份。这将导致“HGV”值出现在“三个月”的输出中。

{
        "_id" : 1,
        "startDate" : ISODate("2017-01-01T00:00:00Z"),
        "endDate" : ISODate("2017-02-25T00:00:00Z"),
        "type" : "CAR"
}
{
        "_id" : 2,
        "startDate" : ISODate("2017-02-17T00:00:00Z"),
        "endDate" : ISODate("2017-03-22T00:00:00Z"),
        "type" : "HGV"
}
{
        "_id" : 3,
        "startDate" : ISODate("2017-02-17T00:00:00Z"),
        "endDate" : ISODate("2017-04-22T00:00:00Z"),
        "type" : "HGV"
}

聚合 - 需要 MongoDB 3.4

db.cars.aggregate([
  { "$addFields": {
    "range": {
      "$reduce": {
        "input": { "$map": {
          "input": { "$range": [ 
            { "$trunc": { 
              "$divide": [ 
                { "$subtract": [ "$startDate", new Date(0) ] },
                1000
              ]
            }},
            { "$trunc": {
              "$divide": [
                { "$subtract": [ "$endDate", new Date(0) ] },
                1000
              ]
            }},
            60 * 60 * 24
          ]},
          "as": "el",
          "in": {
            "$let": {
              "vars": {
                "date": {
                  "$add": [ 
                    { "$multiply": [ "$$el", 1000 ] },
                    new Date(0)
                  ]
                },
                "month": {
                }
              },
              "in": {
                "$add": [
                  { "$multiply": [ { "$year": "$$date" }, 100 ] },
                  { "$month": "$$date" }
                ]
              }
            }
          }
        }},
        "initialValue": [],
        "in": {
          "$cond": {
            "if": { "$in": [ "$$this", "$$value" ] },
            "then": "$$value",
            "else": { "$concatArrays": [ "$$value", ["$$this"] ] }
          }
        }
      }
    }
  }},
  { "$unwind": "$range" },
  { "$group": {
    "_id": {
      "type": "$type",
      "month": "$range"
    },
    "count": { "$sum": 1 }
  }},
  { "$sort": { "_id": 1 } },
  { "$group": {
    "_id": "$_id.type",
    "monthCounts": { 
      "$push": { "month": "$_id.month", "count": "$count" }
    }
  }}
])

完成这项工作的关键是 $range运算符，它采用“开始”和“结束”的值以及要应用的“间隔”。结果是一个从“开始”开始并递增直到到达“结束”的值数组。

我们将其与 startDate 和 endDate 一起使用，以生成介于这些值之间的可能日期。您会注意到我们需要在这里做一些数学运算，因为 $range 只需要一个 32 位整数，但是我们可以从时间戳值中去掉毫秒数，这样就没问题了。

因为我们想要“月”，应用的操作从生成的范围中提取月份和年份值。我们实际上将范围生成为介于两者之间的“天”，因为“月”在数学上很难处理。后续$reduce操作仅需要日期范围内的“不同月份”。

因此，第一个聚合管道阶段的结果是文档中的一个新字段，它是 startDate 和 endDate 之间涵盖的所有不同月份的“数组”。这为其余操作提供了一个“迭代器”。

我所说的“迭代器”是指当我们应用 $unwind 时我们会在间隔内的每个不同月份获得一份原始文件的副本。然后这允许以下两个 $group阶段首先将分组应用于“月份”和“类型”的公共(public)键，以便通过$sum“总计”计数。 ，然后是 $group使键只是“类型”，并通过 $push 将结果放入数组中.

这给出了上述数据的结果:

{
        "_id" : "HGV",
        "monthCounts" : [
                {
                        "month" : 201702,
                        "count" : 2
                },
                {
                        "month" : 201703,
                        "count" : 2
                },
                {
                        "month" : 201704,
                        "count" : 1
                }
        ]
}
{
        "_id" : "CAR",
        "monthCounts" : [
                {
                        "month" : 201701,
                        "count" : 1
                },
                {
                        "month" : 201702,
                        "count" : 1
                }
        ]
}

请注意，“月份”的覆盖范围仅在有实际数据的情况下出现。虽然可以在一个范围内产生零值，但这样做需要相当多的争论，而且不太实用。如果您想要零值，那么最好在检索到结果后在客户端的后处理中添加它。

如果你真的很在意零值，那么你应该单独查询 $min和 $max值，并将它们传递给“暴力破解”管道，为每个提供的可能范围值生成副本。

因此这次“范围”是在所有文档的外部进行的，然后您使用 $cond语句放入累加器以查看当前数据是否在生成的分组范围内。此外，由于生成是“外部的”，我们确实不需要 $range 的 MongoDB 3.4 运算符，因此这也可以应用于早期版本:

// Get min and max separately 
var ranges = db.cars.aggregate(
 { "$group": {
   "_id": null,
   "startRange": { "$min": "$startDate" },
   "endRange": { "$max": "$endDate" }
 }}
).toArray()[0]

// Make the range array externally from all possible values
var range = [];
for ( var d = new Date(ranges.startRange.valueOf()); d <= ranges.endRange; d.setUTCMonth(d.getUTCMonth()+1)) {
  var v = ( d.getUTCFullYear() * 100 ) + d.getUTCMonth()+1;
  range.push(v);
}

// Run conditional aggregation
db.cars.aggregate([
  { "$addFields": { "range": range } },
  { "$unwind": "$range" },
  { "$group": {
    "_id": {
      "type": "$type",
      "month": "$range"
    },
    "count": { 
      "$sum": {
        "$cond": {
          "if": {
            "$and": [
              { "$gte": [
                "$range",
                { "$add": [
                  { "$multiply": [ { "$year": "$startDate" }, 100 ] },
                  { "$month": "$startDate" }
                ]}
              ]},
              { "$lte": [
                "$range",
                { "$add": [
                  { "$multiply": [ { "$year": "$endDate" }, 100 ] },
                  { "$month": "$endDate" }
                ]}
              ]}
            ]
          },
          "then": 1,
          "else": 0
        }
      }
    }
  }},
  { "$sort": { "_id": 1 } },
  { "$group": {
    "_id": "$_id.type",
    "monthCounts": { 
      "$push": { "month": "$_id.month", "count": "$count" }
    }
  }}
])

它为所有分组的所有可能月份生成一致的零填充:

{
        "_id" : "HGV",
        "monthCounts" : [
                {
                        "month" : 201701,
                        "count" : 0
                },
                {
                        "month" : 201702,
                        "count" : 2
                },
                {
                        "month" : 201703,
                        "count" : 2
                },
                {
                        "month" : 201704,
                        "count" : 1
                }
        ]
}
{
        "_id" : "CAR",
        "monthCounts" : [
                {
                        "month" : 201701,
                        "count" : 1
                },
                {
                        "month" : 201702,
                        "count" : 1
                },
                {
                        "month" : 201703,
                        "count" : 0
                },
                {
                        "month" : 201704,
                        "count" : 0
                }
        ]
}

MapReduce

所有版本的 MongoDB 都支持 mapReduce，上面提到的“迭代器”的简单情况由映射器中的 for 循环处理。我们可以通过简单地执行以下操作来获得生成到第一个 $group 的输出:

db.cars.mapReduce(
  function () {
    for ( var d = this.startDate; d <= this.endDate;
      d.setUTCMonth(d.getUTCMonth()+1) )
    { 
      var m = new Date(0);
      m.setUTCFullYear(d.getUTCFullYear());
      m.setUTCMonth(d.getUTCMonth());
      emit({ id: this.type, date: m},1);
    }
  },
  function(key,values) {
    return Array.sum(values);
  },
  { "out": { "inline": 1 } }
)

产生:

{
        "_id" : {
                "id" : "CAR",
                "date" : ISODate("2017-01-01T00:00:00Z")
        },
        "value" : 1
},
{
        "_id" : {
                "id" : "CAR",
                "date" : ISODate("2017-02-01T00:00:00Z")
        },
        "value" : 1
},
{
        "_id" : {
                "id" : "HGV",
                "date" : ISODate("2017-02-01T00:00:00Z")
        },
        "value" : 2
},
{
        "_id" : {
                "id" : "HGV",
                "date" : ISODate("2017-03-01T00:00:00Z")
        },
        "value" : 2
},
{
        "_id" : {
                "id" : "HGV",
                "date" : ISODate("2017-04-01T00:00:00Z")
        },
        "value" : 1
}

所以它没有第二个分组来组合成数组，但我们确实产生了相同的基本聚合输出。