月级别和年级别的聚合，也可以在 MongoDB 中找到平均值 [英] Aggregation on month level and year level, also find the average in MongoDB

问题描述

说明:我想找到 2018、2021 和 2020 年值的平均值.年和月将使用查询参数传递.另外，如果没有完整的年份，则在月份级别上找到平均值，那么平均值可以在月份级别上，月份也会从查询参数中传递.

Explanation: I want to find the average of the 2018,2021 and 2020 values. years and month will be pass using query parameters. also, find the average on the month level if the complete year is not available, then the average could be on the month level, the month will be also passed from the query parameter.

从查询参数来看，如果用户给出月份作为参数，平均的结果是在月份级别.如果用户给一年或多年聚合将年级别

From the query parameter, if the user gives months as a parameter, the result of the average is on the month level. IF users give one year or multiple years aggregation will year level

查询参数为下拉式

• 时基选择器(月、年)
• 年基选择器 2018、2015、2016....
• 月份基数选择器(所有月份、12、11、...08、..03、01)

如果解释不清楚，请告诉我.提前致谢.

if the explanation is not clear let me know, please. Thanks in advance.

    {
  "data": {
    "2018-05-11": {
      "12": 12.4,
      "00": 11.3
    },
    "2018-12-31": {
      "12": 12.4,
      "06": 13
    },
    "2021-05-10": {
      "00": 11.3,
      "06": 13
    },
    "2021-05-11": {
      "12": 12.4,
      "00": 11.3,
      "06": 13
    },
    "2021-05-13": {
      "12": 6,
      "06": 13
    },
    "2020-06-01": {
      "12": 3.4,
      "09": 1.8
    },
    "2020-06-15": {
      "12": 3.9,
      "09": 11.8
    }
  }
}

推荐答案

• $objectToArray 将 data 对象转换为数组键值格式
• $filter 迭代上述转换数组的循环
• 检查$和条件
• $toDate 从字符串日期获取data
• $month 从日期选择月份和 $year 选择年份
• $in 如果月份在月份数组中并且年份在年份数组中，则检查条件
• $arrayToObject 从键值数组转换回对象
• $objectToArray convert data object to array key-value format
• $filter to iterate loop of above converted array
• check $and conditions
• $toDate to get data from string date
• $month to select month from date and $year to select year
• $in check in condition if month is in array of months and year is in array of years
• $arrayToObject convert back to object from key-value array

var years = [2021];
var months = [5];
db.collection.aggregate([
  {
    $project: {
      data: {
        $arrayToObject: {
          $filter: {
            input: { $objectToArray: "$data" },
            cond: {
              $and: [
                { $in: [{ $year: { $toDate: "$$this.k" } }, years] },
                { $in: [{ $month: { $toDate: "$$this.k" } }, months] }
              ]
            }
          }
        }
      }
    }
  }
])

游乐场

这篇关于月级别和年级别的聚合，也可以在 MongoDB 中找到平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！