如何通过查询参数在mongoose中创建增量范围聚合,例如,通过字段值创建增量范围

9gm1akwq  于 2022-11-13  发布在  Go
关注(0)|答案(2)|浏览(145)

因此,数据集如下所示:screenshot of the data structure

{
  "YearWeekISO": "2020-W53",
  "FirstDose": 0,
  "FirstDoseRefused": "",
  "SecondDose": 0,
  "DoseAdditional1": 0,
  "DoseAdditional2": 0,
  "UnknownDose": 0,
  "NumberDosesReceived": 0,
  "NumberDosesExported": 0,
  "Region": "AT",
  "Population": "8901064",
  "ReportingCountry": "AT",
  "TargetGroup": "ALL",
  "Vaccine": "JANSS",
  "Denominator": 7388778
}, {
  "YearWeekISO": "2020-W53",
  "FirstDose": 0,
  "FirstDoseRefused": "",
  "SecondDose": 0,
  "DoseAdditional1": 0,
  "DoseAdditional2": 0,
  "UnknownDose": 8,
  "NumberDosesReceived": 0,
  "NumberDosesExported": 0,
  "Region": "AT",
  "Population": "8901064",
  "ReportingCountry": "AT",
  "TargetGroup": "ALL",
  "Vaccine": "UNK",
  "Denominator": 7388778
},

link to the data set
查询参数如下所示:
GET /疫苗-总结?c=AT&日期自=2020-W10 &日期至=2020-W53 &范围=5
其中
c,要获取报告国家/地区代码
起始日期,年-周,例如2020-W10(包括)
截止日期,yyyy-Www,例如,2020-W20(不包括)
rangeSize、数字,例如,计算指标的期间
应用聚合后,您应该得到如下所示的转换数据集:

{
  "summary": [{
      "weekStart": "2020-W10",
      "weekEnd": "2020-W15",
      "NumberDosesReceived": 1000
    },
    {
      "weekStart": "2020-W15",
      "weekEnd": "2020-W20"
      "NumberDosesReceived": 2000
    }, …
    till end of range(dateTo)
  ]
}

}
请注意weekStart是如何从2020-W10递增到2020-W15的,与weekEnd类似。
NumberDosesReceived是在该范围内的NumberDosesReceived字段ID的总和

qaxu7uf2

qaxu7uf21#

So was able to come up with a working solution using a mongo aggregate method called bucket, but one of the problem is that if you want an aggregation of like week 1 - week 20 in chunks of 5, i.e, 1-5 (1 included, 5 excluded), 5- 10,10-15 and 15-20, you will have to give it an array like; boundaries: [1,5,10,15,20] as part of the argument and from the question, i have to create a JS function to return an array of numbers between start week and end week with the range given also. Written in typescript, the return array from this question would look like : [2020-W01,2020-W05,2020-W10,2020-W15,2020-W20], Also there are certain edge cases you have to account for since all the parameters are dynamic, like if the week spans more than one year, also, the fact that mongo to the best of my knowledge don't have date format like "2020-W10" makes it a bit more complex

export function customLoop(
  startWeek: number,
  endWeek: number,
  rangeNum: number,
  year: number
): returnData {
  const boundaryRange: string[] = [];
  let skip = 0;
  for (let i = startWeek; i <= endWeek; i += rangeNum) {
    const currentNum: string = i < 10 ? `0${i}` : `${i}`;
    const result = `${year}-W${currentNum}`;
    boundaryRange.push(result);
    //if all the weeks in a year, Check where the last loop stops to determine skip
    if (endWeek === 53 && i + rangeNum > 53) {
      skip = i + rangeNum - 53 - 1;
    }
  }
  return {
    skip,
    theRange: boundaryRange,
  };
}

After this i opened my mongo compass on local to construct and chain aggregate methods and function to satisfy the task given:

const result = await VaccinationModel.aggregate([
      {
        $match: {
          ReportingCountry: c,
        },
      },
      {
        $bucket: {
          groupBy: "$YearWeekISO",
          boundaries: [...boundaryRange],
          default: "others",
          output: {
            NumberDosesReceived: {
              $sum: "$NumberDosesReceived",
            },
          },
        },
      },
      {
        $addFields: {
          range: rangeNum,
        },
      },
      {
        $addFields: {
          weekStart: "$_id",
          weekEnd: {
            $function: {
              body: "function(id,range) {\n      const arr = id.split('-')\n      const year = arr[0];\n      let week;\n      let result=''\n      if(arr[1]){\n       week = arr[1].slice(1);\n        result=`${year}-W${Number(week) + range}`\n\n              }\n\n               return result\n        }",
              args: ["$_id", "$range"],
              lang: "js",
            },
          },
        },
      },
      {
        $unset: ["_id", "range"],
      },
      {
        $match: {
          weekEnd: {
            $ne: "",
          },
        },
      },
      {
        $sort: {
          weekStart: 1,
        },
      },
    ]);

In that aggregation:

  1. Match the country code.
  2. I basically called the bucket aggregation with the array of boundaries, then summing the results of each chunk/range using its NumberDosesReceived field while naming it NumberDosesReceived.
  3. since i needed extra two fields to complete the number of fields to return, namely weekStart and weekEnd that isnt in the dataset, the weekStart is the _id field from the bucket aggregation, to get the weekEnd, i added the range as a field.
  4. If for instance the current mongo iteration is 2020-W5, which would be the in the _id, that means the weekend would be 5 + range = 10, so i used the mongo function method to extract that passing _id and range as argument.
  5. Used the unset method to remove the _id and range field as it wouldn't be part of the return data.
  6. Get this new weekEnd field excluding empty ones.
  7. sort using it.
    here is the link to the repo: link
jucafojl

jucafojl2#

应该可以的
常数年周ISO = { $截止日期:“$年周ISO”};

{
        $project: {
          fiveWeekperiod: {
            $subtract: [
              { $week: YearWeekISO },
              { $mod: [{ $week: YearWeekISO }, 5] },
            ],
          },
          date: YearWeekISO,
          NumberDosesReceived: 1,
        },
      },
      {
        $group: {
          _id: {
            year: { $year: "$date" },
            fiveWeek: "$fiveWeekperiod",
          },
          weekStart: { $min: "$date" },
          weekEnd: { $max: "$date" },
          NumberDosesReceived: { $sum: "$NumberDosesReceived" },
        },
      },
      {
        $project: {
          _id: 0,
          weekStart: {
            $dateToString: {
              date: "$weekStart",
              format: "%G-W%V",
            },
          },
          weekEnd: {
            $dateToString: {
              date: {
                $dateAdd: {
                  startDate: "$weekEnd",
                  unit: "week",
                  amount: 1,
                },
              },
              format: "%G-W%V",
            },
          },
          NumberDosesReceived: 1,
        },
      }

相关问题