在mongodb中匯總每小時每周每月每年資料

我需要按創建日期和 driverId 過濾資料，然后需要按小時、每周、每月和每年匯總總數。我已經檢查過其他解決方案，但它并沒有太大幫助。

樣本資料：

    [
      {
        id: "1",
        created : "2022-01-04T03:22:18.739Z",
        completed: "2022-01-06T03:53:28.463Z",
        driverId: "B-72653",
        total: 15,
      },
      {
        id: "2",
        created : "2022-01-01T03:22:18.739Z",
        completed: "2022-01-02T03:53:28.463Z",
        driverId: "B-72653",
        total: 33
      },
      {
        id: "3",
        created : "2021-08-26T01:22:18.739Z",
        completed: "2021-08-26T09:53:28.463Z",
        driverId: "B-72653",
        total: 43
      },  
      {
        id: "4",
        created : "2021-03-26T02:22:18.739Z",
        completed: "2021-03-26T07:53:28.463Z",
        driverId: "B-73123",
        total: 35
      },  
    ]

需要回應：

    {
        Hourly:[10,5,5,6,7,8,4,5,6,3,44,2,1,2,3,44,5,6,75,4,3,2,1], // 24 Hours (Each Hour Total)
        Weekly:[10,30,34,45,56,67,78], // 7 days (Each Day Total)
        Monthly:[10,30,34,45,56,67,78,55,44,33,22,12], // 12 Months (Each Month Total)
        Yearly: [10,30] // Year Total (Each Year Total)
    }

uj5u.com熱心網友回復：

您可以使用$groupwith _idbeing $hour///來聚合總和$week。將它們放入一個陣列中以獲得您的預期結果。用于對所有 4 種情況重復該程序。$month$year$push$facet

db.collection.aggregate([
  {
    "$facet": {
      "Hourly": [
        {
          $group: {
            _id: {
              $hour: "$created"
            },
            total: {
              $sum: "$total"
            }
          }
        },
        {
          $sort: {
            _id: 1
          }
        },
        {
          $group: {
            _id: null,
            result: {
              $push: {
                hour: "$_id",
                total: "$total"
              }
            }
          }
        }
      ],
      Weekly: [
        {
          $group: {
            _id: {
              "$week": "$created"
            },
            total: {
              $sum: "$total"
            }
          }
        },
        {
          $sort: {
            _id: 1
          }
        },
        {
          $group: {
            _id: null,
            result: {
              $push: {
                week: "$_id",
                total: "$total"
              }
            }
          }
        }
      ],
      Monthly: [
        {
          $group: {
            _id: {
              $month: "$created"
            },
            total: {
              $sum: "$total"
            }
          }
        },
        {
          $sort: {
            _id: 1
          }
        },
        {
          $group: {
            _id: null,
            result: {
              $push: {
                month: "$_id",
                total: "$total"
              }
            }
          }
        }
      ],
      Yearly: [
        {
          $group: {
            _id: {
              $year: "$created"
            },
            total: {
              $sum: "$total"
            }
          }
        },
        {
          $sort: {
            _id: 1
          }
        },
        {
          $group: {
            _id: null,
            result: {
              $push: {
                year: "$_id",
                total: "$total"
              }
            }
          }
        }
      ]
    }
  },
  {
    "$addFields": {
      "Hourly": {
        "$arrayElemAt": [
          "$Hourly",
          0
        ]
      },
      "Weekly": {
        "$arrayElemAt": [
          "$Weekly",
          0
        ]
      },
      "Monthly": {
        "$arrayElemAt": [
          "$Monthly",
          0
        ]
      },
      "Yearly": {
        "$arrayElemAt": [
          "$Yearly",
          0
        ]
      }
    }
  },
  {
    "$addFields": {
      "Hourly": "$Hourly.result",
      "Weekly": "$Weekly.result",
      "Monthly": "$Monthly.result",
      "Yearly": "$Yearly.result"
    }
  }
])

這是Mongo游樂場供您參考。

標籤：

上一篇：ExpressJS如何將物件推送到子物件陣列

下一篇：使用MulterStorageCloudinary從同一表單上傳音頻和影像