laravel 如何按时间段对数据进行分组？

gj3fmq9x 于 2022-11-18 发布在其他

关注(0)|答案(1)|浏览(226)

我有发票数据，我想按时间段墙对它们进行分组。例如0-10和11-20：
我需要用mongoDB中的$aggregate来解决它们。

{
    '_id': '1',
    'value': 10,
    'due_date': '20221001'
},
{
    '_id': '2',
    'value': 10,
    'due_date': '20221012'
},
{
    '_id': '2',
    'value': 10,
    'due_date': '20221030'
},

我需要按时间段0-10天、11-20天进行分组，并对值求和：对于上面的示例，结果为：

[{
    "_id": '0-10 days',
    "total": 10,
},
{
    "_id": '11-20 days',
    "total": 10,
},
{
    "_id": '>20 days',
    "total": 10,
}]

我试着：

['$facet' => [
  ['due_date_one' => [
      ['$match' => [
         'due_date' => [
           '$gt'    => new UTCDateTime((new Carbon())-> subDays(100) -> getTimestamp()),
            '$lte'  => date('Y-m-d', strtotime('now'))
         ]
         ]
     ]],
]]

laravel

来源：https://stackoverflow.com/questions/74398939/how-to-group-data-by-time-period

1条答案

按热度按时间

9gm1akwq1#

您可以使用$bucket或添加几个$addFields和$group级：
蒙戈Playground示例-https://mongoplayground.net/p/B0zl_GGH4sG
示例文档：

[
  {
    "_id": "1a",
    "value": 10,
    "due_date": "20221001"
  },
  {
    "_id": "1b",
    "value": 5,
    "due_date": "20221102"
  },
  {
    "_id": "1c",
    "value": 7,
    "due_date": "20221102"
  },
  {
    "_id": "2a",
    "value": 10,
    "due_date": "20221012"
  },
  {
    "_id": "2b",
    "value": 7,
    "due_date": "20221113"
  },
  {
    "_id": "2c",
    "value": 8,
    "due_date": "20221113"
  },
  {
    "_id": "3a",
    "value": 10,
    "due_date": "20221030"
  },
  {
    "_id": "3b",
    "value": 9,
    "due_date": "20221131"
  },
  {
    "_id": "3c",
    "value": 11,
    "due_date": "20221131"
  }
]

汇总查询：

db.collection.aggregate([
  {
    $addFields: {
      day: {
        $toInt: { $substr: [ "$due_date", 6, 2 ] }
      }
    }
  },
  {
    $addFields: {
      bucketDate: {
        $switch: {
          branches: [
            { case: { $gt: [ "$day", 20 ] }, then: ">20 days" },
            { case: { $gt: [ "$day", 10 ] }, then: "11-20 days" }
          ],
          "default": "0-10 days"
        }
      }
    }
  },
  {
    $addFields: {
      bucketDateWithMonth: {
        $concat: [
          { $substr: [ "$due_date", 0, 6 ] },
          " ",
          "$bucketDate"
        ]
      }
    }
  },
  {
    $group: {
      //_id: "$bucketDate", //No grouped month
      _id: "$bucketDateWithMonth", //With grouped month
      count: { $sum: 1 },
      value: { $sum: "$value" }
    }
  }
])

输出：（分组月份）

[
  {
    "_id": "202210 0-10 days",
    "count": 1,
    "value": 10
  },
  {
    "_id": "202211 0-10 days",
    "count": 2,
    "value": 12
  },
  {
    "_id": "202210 11-20 days",
    "count": 1,
    "value": 10
  },
  {
    "_id": "202211 11-20 days",
    "count": 2,
    "value": 15
  },
  {
    "_id": "202210 \u003e20 days",
    "count": 1,
    "value": 10
  },
  {
    "_id": "202211 \u003e20 days",
    "count": 2,
    "value": 20
  }
]

输出：（无分组月份）

[
  {
    "_id": "\u003e20 days",
    "count": 3,
    "value": 30
  },
  {
    "_id": "0-10 days",
    "count": 3,
    "value": 22
  },
  {
    "_id": "11-20 days",
    "count": 3,
    "value": 25
  }
]

赞(0）回复(0）举报 2022-11-18

我来回答

laravel 如何按时间段对数据进行分组？

1条答案

相关问题

热门标签

最新问答