Mongo:如何按 $week 分组但返回每周的开始日期和结束日期? [英] Mongo: How to group by $week but return start date and end date of each week instead?

问题描述

我按 $week 使用 $group.它返回一年中的 #week，但我想要每周的开始日期、结束日期而不是

I am using $group by $week. It's returning the #week in the year, but I want the start date, end date of each week instead of

示例:

当前结果:

[{
    "_id" : 20,
    "averageValue" : null
},
{
    "_id" : 21,
    "averageValue" : 28.1875
}]

期待:

[{
    "_id" : [{ start: "2021-01-01", "end": "2021-01-07" }],
    "averageValue" : null
},
{
    "_id" : [{ start: "2021-01-08", "end": "2021-01-14" }],
    "averageValue" : 28.1875
}]

推荐答案

虽然它是可行的，但使代码可读性降低且更复杂.

Although it is doable, makes code less readable and more complex.

我将重写 $group 阶段以包含 year 和 weeks 并添加 $project 阶段根据要求格式化数据.

I will rewrite the $group stage to include year along with weeks and add the $project stage to format the data as per requirement.

db.collection.aggregate([
  {
    "$group": {
      "_id": {
        "week": {
          "$week": {"$subtract": ["$at", 25200000]}  // <-- Changes timezone to -07:00
        },
        "year": {
          "$year": {"$subtract": ["$at", 25200000]}  // <-- Changes timezone to -07:00
        },
      },
      // <-- Add keys to be added in group along with its logics
      "averageValue": {
        "$avg": "$readings.level_1"
      }
    },
  },
  {
    "$project": {
      "_id": {
        "startDate": {
          "$dateToString": {
            "date": {
              "$dateFromParts": {
                "isoWeekYear": "$_id.year",
                "isoWeek": "$_id.week"
              }
            },
            "format": "%Y-%m-%d",
          },
        },
        "endDate": {
          "$dateToString": {
            "date": {
              "$add": [
                {
                  "$dateFromParts": {
                    "isoWeekYear": "$_id.year",
                    "isoWeek": "$_id.week"
                  }
                },
                518400000,
              ],
            },
            "format": "%Y-%m-%d",
          },
        },
      },
      // <-- Add remaining keys to be projected
      "averageValue": 1,
    },
  },
])

如果您需要解释每个阶段和使用的运算符以及我为什么使用它，请告诉我.

Let me know if you need an explanation of each stage and operator used and why I used it.

Mongo Playground 示例执行

这篇关于Mongo:如何按 $week 分组但返回每周的开始日期和结束日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！