在 MongoDB 聚合中添加缺失的日期 [英] Adding Missing Dates in MongoDB Aggregation

问题描述

在这个问题中，我想获取以下时间范围内的用户订阅

• 今天
• 本周
• 本月
• 今年

对于这个时间范围，我想显示所有可能日期的 $group.

<预><代码>[{ 日期:'2020-05-21'，计数:3，AverageIncome:30 }，{ 日期:'2020-05-22'，计数:10，AverageIncome:30 }，{ 日期:'2020-05-24'，计数:1，AverageIncome:37 }，{ 日期:'2020-05-25'，计数:1，AverageIncome:30 }，{ 日期:'2020-05-26'，计数:2，平均收入:65 }]

我想在累加器数据设置为 0 的情况下添加缺失的日期.例如，它应该是

<预><代码>[{ 日期:'2020-05-21'，计数:3，AverageIncome:30 }，{ 日期:'2020-05-22'，计数:10，AverageIncome:30 }，{ 日期:'2020-05-23'，计数:0，平均收入:0}，{ 日期:'2020-05-24'，计数:1，AverageIncome:37 }，{ 日期:'2020-05-25'，计数:1，AverageIncome:30 }，{ 日期:'2020-05-26'，计数:2，AverageIncome:65 }，{ 日期:'2020-05-27'，计数:0，平均收入:0}，{ 日期:'2020-05-28'，计数:2，AverageIncome:0}，]

PS:此时 UTC 时间为 5 月 28 日晚上 11:45

我正在使用的聚合管道阶段

const $agg = await Subscription.aggregate([{$匹配:{创建于: {$gte: 时刻(req.query.from).toDate(),$lte: 时刻(req.query.to).toDate()},isCancelled: 假}},{$组:{_ID: {$dateToString: {格式:"%Y-%m-%d",日期:$createdOn"}},计数: { $sum: 1 },avgAmount: { $avg: "$amountPaid" }}},{$项目:{_id:假，排序键:{$dateFromString: {日期字符串:$_id"}},日期:$_id"，计数:$count"，平均收入:$avgAmount"}},{$排序:{排序键:1}},{$项目:{排序键:假}}]);

解决方案

前段时间我也遇到过类似的问题，以下是我遵循的方法:

function fillEmptyDates($agg, $defaultData, from, to) {const $aggObj = {};//以this为对象，方便检查$agg.map((agg) => {$aggObj[agg.Date] = agg;//从以Date为键的数组条目中创建一个对象});const $loopDate = moment(from);const $endDate = 时刻(到)；//从日期开始到日期，检查$aggObje中是否有任何日期没有条目而 ($endDate.isSameOrAfter($loopDate)) {让 $aggDate = $loopDate.format("YYYY-MM-DD");如果 (!$aggObj.hasOwnProperty($aggDate)) {//如果任何日期没有条目，则从默认聚合创建一个新条目并将其指定为当前日期$aggObj[$aggDate] = {...$defaultData，日期:$aggDate};}$loopDate.add(1, "day");//递增聚合日期}//转换回数组并按日期排序return Object.values($aggObj).sort((a, b) =>时刻(a.Date).isBefore(moment(b.Date)) ?-1 : 1);};//你可以像这样调用这个函数$agg = fillEmptyDates($agg,{计数:0，平均收入:0}，req.query.from,req.query.to,)

In this problem, I want to get the user subscription for the following time range

• Today
• This Week
• This Month
• This Year

For this time range, i want to display the $group of all possible dates.

[
  { Dates: '2020-05-21', Count: 3, AverageIncome: 30 },
  { Dates: '2020-05-22', Count: 10, AverageIncome: 30 },
  { Dates: '2020-05-24', Count: 1, AverageIncome: 37 },
  { Dates: '2020-05-25', Count: 1, AverageIncome: 30 },
  { Dates: '2020-05-26', Count: 2, AverageIncome: 65 }
]

I want to add the missing dates as well with accumulator data set to 0. For example, it should be

[
  { Dates: '2020-05-21', Count: 3, AverageIncome: 30 },
  { Dates: '2020-05-22', Count: 10, AverageIncome: 30 },
  { Dates: '2020-05-23', Count: 0, AverageIncome: 0 },
  { Dates: '2020-05-24', Count: 1, AverageIncome: 37 },
  { Dates: '2020-05-25', Count: 1, AverageIncome: 30 },
  { Dates: '2020-05-26', Count: 2, AverageIncome: 65 },
  { Dates: '2020-05-27', Count: 0, AverageIncome: 0 },
  { Dates: '2020-05-28', Count: 2, AverageIncome: 0 },
]

PS: At this time the UTC time would be 28th may 11:45 pm

The aggregation pipeline stages i am using

const $agg = await Subscription.aggregate([
    {
      $match: {
        createdOn: {
          $gte: moment(req.query.from).toDate(),
          $lte: moment(req.query.to).toDate()
        },
        isCancelled: false
      }
    },

    {
      $group: {
        _id: {
          $dateToString: {
            format: "%Y-%m-%d",
            date: "$createdOn"
          }
        },
        count: { $sum: 1 },
        avgAmount: { $avg: "$amountPaid" }
      }
    },
    {
      $project: {
        _id: false,
        sortKey: {
          $dateFromString: {
            dateString: "$_id"
          }
        },
        Dates: "$_id",
        Count: "$count",
        AverageIncome: "$avgAmount"
      }
    },
    {
      $sort: {
        sortKey: 1
      }
    },
    {
      $project: {
        sortKey: false
      }
    }
  ]);

解决方案

I had faced the similar issue some time back, and here is the approach that I followed:

function fillEmptyDates($agg, $defaultData, from, to) {
  const $aggObj = {}; //Taking this as an object for easy checking

  $agg.map((agg) => {
    $aggObj[agg.Date] = agg; //Making an object from array entries with Date as key
  });

  const $loopDate = moment(from);
  const $endDate = moment(to);

  //Starting from from date to to date, checking if any date does not have entry in $aggObje
  while ($endDate.isSameOrAfter($loopDate)) {
    let $aggDate = $loopDate.format("YYYY-MM-DD");

    if (!$aggObj.hasOwnProperty($aggDate)) {
      //If any date does not have entry, creating a new entry from default aggregates and giving it the date as current date
      $aggObj[$aggDate] = {
        ...$defaultData,
        Date: $aggDate
      };
    }
    $loopDate.add(1, "day"); //Incrementing aggregate date
  }

  //Converting back to array and sorting by date
  return Object.values($aggObj).sort((a, b) =>
    moment(a.Date).isBefore(moment(b.Date)) ? -1 : 1
  );
};

//You can call this function like so
$agg = fillEmptyDates(
            $agg, 
            { Count: 0, AverageIncome: 0 },
            req.query.from,
            req.query.to,
        )

这篇关于在 MongoDB 聚合中添加缺失的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！