GroupBy 聚合，包括 mongo 中缺少的日期 [英] GroupBy Aggregation including missing dates in mongo
问题描述
我有一个聚合查询，它在传递日期范围时为我提供按日期分组的结果.示例聚合管道看起来像这样.
<代码>[{$匹配:{订购日期: {$gte: 新日期('2021-08-01T00:00:00+05:30'),$lte: 新日期('2021-08-06T23:59:59+05:30'),}},{$组:{_ID: {$dateToString: {格式:'%Y-%m-%d',日期: '$orderDate',}}}}]这将获取与下面类似的结果
<预><代码>[{_id: '2021-08-01',},{_id: '2021-08-02',},{_id: '2021-08-04',},{_id: '2021-08-05',},]因此，请注意缺少 03 和 06 天，因为它们不在集合中，但包含在聚合管道中传递的日期范围.
有没有办法通过调整上面的查询来直接查询和获取这些缺失的日期?这将导致正确的价值
也许下面就是你想要的.
1) 创建一个带有计数器的集合，例如这里 1-10 10 天
[{日期":0},{日期":1},{日期":2},{日期":3},{日期":4},{日期":5},{日期":6},{日期":7},{日期":8},{日期":9}]- 更新此集合并使用 $add 生成数据、10 个日期这里我们从日期2021-08-01T00:00:00+00:00"开始我们将结束日期2021-08-10T00:00:00+00:00"
这个更新会做(q=过滤器，u=更新，这里是管道更新)
{更新":日期"，更新":[{q":{}，你":[{$addFields":{日期":{$add":[2021-08-01T00:00:00Z"，{$乘":[$日期"，24,60,60000]}]}}}],多":真}]}结果为(10 个日期，1 天差异)
{_id":{$oid":610c4bf99ccb15d9c9d67b55"},日期":{$date":2021-08-01T00:00:00Z"}},{_id":{$oid":610c4bf99ccb15d9c9d67b56"},日期":{$date":2021-08-02T00:00:00Z"}},{_id":{$oid":610c4bf99ccb15d9c9d67b57"},日期":{$date":2021-08-03T00:00:00Z"}}...3) 现在我们有 10 个日期的日期集合，所有日期都相差 1 天我们将对订单集合进行 $lookup
订单收集
[{_id":1，日期":2021-08-01T00:00:00Z"},{_id":2，日期":2021-08-01T00:00:00Z"},{_id":3，日期":2021-08-05T00:00:00Z"},{_id":4，日期":2021-08-03T00:00:00Z"}]查询(走管道，先过滤想要的日期，只有当天下单的才加入)
{聚合":日期"，管道":[{$匹配":{$expr":{$和":[{$gte":[$日期"，{$dateFromString":{dateString":2021-08-01T00:00:00"}}]},{$lte":[$日期"，{$dateFromString":{dateString":2021-08-05T00:00:00"}}]}]}}},{$查找":{来自":订单"，让":{datesDate":$date"},管道":[{$匹配":{$expr":{$eq":[{$dateToString":{格式":%Y-%m-%d"，日期":$$datesDate"}},{$dateToString":{格式":%Y-%m-%d"，日期":$日期"}}]}}}],作为":找到订单"；}},{$项目":{_id":0}}],光标":{}，maxTimeMS":1200000}结果
[{日期":2021-08-01T00:00:00Z"，发现订单":[{_id":1，日期":2021-08-01T00:00:00Z"},{_id":2，日期":2021-08-01T00:00:00Z"}]},{日期":2021-08-02T00:00:00Z"，找到订单":[]},{日期":2021-08-03T00:00:00Z"，发现订单":[{_id":4，日期":2021-08-03T00:00:00Z"}]},{日期":2021-08-04T00:00:00Z"，找到订单":[]},{日期":2021-08-05T00:00:00Z"，发现订单":[{_id":3，日期":2021-08-05T00:00:00Z"}]}]这给出了每个日期(对于每一天)，当天下的订单.如果某一天没有订单，则数组为空.这里只有 10 天，您可以根据需要进行 5 年的日期收集.
I have an aggregation query that gives me a result grouped by dates when a date range is passed. The sample aggregate pipeline looks something like this.
[{
$match: {
orderDate: {
$gte: new Date('2021-08-01T00:00:00+05:30'),
$lte: new Date('2021-08-06T23:59:59+05:30'),
}
},
{
$group: {
_id: {
$dateToString: {
format: '%Y-%m-%d',
date: '$orderDate',
}
}
}
}]
This fetches the result that is similar to the one below
[
{
_id: '2021-08-01',
},
{
_id: '2021-08-02',
},
{
_id: '2021-08-04',
},
{
_id: '2021-08-05',
},
]
So, notice the day 03, and 06 is missing since they are not in the collection but is inclusive of the date range passed in the aggregate pipeline.
Is there any way to directly query and get these missing dates by tweaking the above query? Which will result in the right value
Maybe the bellow is what you want.
1)Create one collection with a counter for example here 1-10 for 10 days
[
{
"date": 0
},
{
"date": 1
},
{
"date": 2
},
{
"date": 3
},
{
"date": 4
},
{
"date": 5
},
{
"date": 6
},
{
"date": 7
},
{
"date": 8
},
{
"date": 9
}
]
- Update this collection and make the data,10 dates using $add Here we start from date "2021-08-01T00:00:00+00:00" and we will end on date "2021-08-10T00:00:00+00:00"
This update will do it(q=the filter,u=the update,here its pipeline update)
{
"update": "dates",
"updates": [
{
"q": {},
"u": [
{
"$addFields": {
"date": {
"$add": [
"2021-08-01T00:00:00Z",
{
"$multiply": [
"$date",
24,
60,
60000
]
}
]
}
}
}
],
"multi": true
}
]
}
Results in (10 dates,1 day difference)
{
"_id": {
"$oid": "610c4bf99ccb15d9c9d67b55"
},
"date": {
"$date": "2021-08-01T00:00:00Z"
}
},{
"_id": {
"$oid": "610c4bf99ccb15d9c9d67b56"
},
"date": {
"$date": "2021-08-02T00:00:00Z"
}
},{
"_id": {
"$oid": "610c4bf99ccb15d9c9d67b57"
},
"date": {
"$date": "2021-08-03T00:00:00Z"
}
}
...
3)Now we have the dates collection with 10 dates,all with 1 day difference we will make a $lookup with the orders collection
Orders collection
[
{
"_id": 1,
"date": "2021-08-01T00:00:00Z"
},
{
"_id": 2,
"date": "2021-08-01T00:00:00Z"
},
{
"_id": 3,
"date": "2021-08-05T00:00:00Z"
},
{
"_id": 4,
"date": "2021-08-03T00:00:00Z"
}
]
Query(take the pipeline,first filter the wanted dates,and then join only if order is made the same day)
{
"aggregate": "dates",
"pipeline": [
{
"$match": {
"$expr": {
"$and": [
{
"$gte": [
"$date",
{
"$dateFromString": {
"dateString": "2021-08-01T00:00:00"
}
}
]
},
{
"$lte": [
"$date",
{
"$dateFromString": {
"dateString": "2021-08-05T00:00:00"
}
}
]
}
]
}
}
},
{
"$lookup": {
"from": "orders",
"let": {
"datesDate": "$date"
},
"pipeline": [
{
"$match": {
"$expr": {
"$eq": [
{
"$dateToString": {
"format": "%Y-%m-%d",
"date": "$$datesDate"
}
},
{
"$dateToString": {
"format": "%Y-%m-%d",
"date": "$date"
}
}
]
}
}
}
],
"as": "found-orders"
}
},
{
"$project": {
"_id": 0
}
}
],
"cursor": {},
"maxTimeMS": 1200000
}
Results
[
{
"date": "2021-08-01T00:00:00Z",
"found-orders": [
{
"_id": 1,
"date": "2021-08-01T00:00:00Z"
},
{
"_id": 2,
"date": "2021-08-01T00:00:00Z"
}
]
},
{
"date": "2021-08-02T00:00:00Z",
"found-orders": []
},
{
"date": "2021-08-03T00:00:00Z",
"found-orders": [
{
"_id": 4,
"date": "2021-08-03T00:00:00Z"
}
]
},
{
"date": "2021-08-04T00:00:00Z",
"found-orders": []
},
{
"date": "2021-08-05T00:00:00Z",
"found-orders": [
{
"_id": 3,
"date": "2021-08-05T00:00:00Z"
}
]
}
]
This gives for each date(for each day),the orders that were made that day. If one date no orders were made the array is empty. Here is only 10 days,you can make like 5 years date collection based on your needs.
这篇关于GroupBy 聚合，包括 mongo 中缺少的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
