MongoDB按日期/小时聚合统计 需基于conversationId去重
2026-8-21
MongoDB 动态聚合：按日期/小时统计去重后的conversationId数量
需求说明
- 当查询日期范围超过1天时，按日期聚合统计去重后的conversationId数量（同一conversationId在同一天仅计1次）
- 当查询日期为单日时，按小时聚合统计同一维度的数量
示例数据
[ { "_id":"c438a671-2391-4b85-815c-ecfcb3d2bb54", "status":"INTERNAL_UPDATE", "conversationId":"ac44781d-caab-4410-a708-9d6db8480fc3", "messageIds":[], "messageId":"4dc02026-ac06-4eb1-aa59-e385fcce4a36", "responseId":"0c00c83d-61c5-4937-846c-2e6a46aae857", "conversation":{}, "message":{}, "params":{}, "timestamp":"2021-05-04T11:40:06.552Z", "source":{} }, { "_id":"98370ddf-9ff8-4347-bab7-1f7777ab9e9d", "status":"NEW", "conversationId":"b5dc39d2-56a1-4eb6-a728-cdbe33dca580", "messageIds":[], "messageId":"ba94b839-f795-44f2-aea0-173d26006f14", "responseId":"a2b75364-447b-4345-8008-2beccd6cbb34", "conversation":{}, "message":{}, "params":{}, "timestamp":"2021-05-05T11:40:30.897Z", "source":{} }, { "_id":"db1eae2b-62d9-455c-ab46-dbfc5baf8b67", "status":"INTERNAL_UPDATE", "conversationId":"b5dc39d2-56a1-4eb6-a728-cdbe33dcb584", "messageIds":[], "messageId":"b83c743b-d36e-4fdd-9c03-21988af47263", "responseId":"97198c09-0130-48dc-a225-6d0faeff3116", "conversation":{}, "message":{}, "params":{}, "timestamp":"2021-05-05T11:40:31.418Z", "source":{} }, { "_id":"12a21495-f857-4f18-a06e-f8ba0b951ade", "status":"NEW", "conversationId":"8e37c704-add8-4f9f-8e70-d630c24f653b", "messageIds":[], "messageId":"51a48362-545c-4f9f-930b-42e4841fc974", "responseId":"4691468b-a43b-41d1-83df-1349fb554bfa", "conversation":{}, "message":{}, "params":{}, "timestamp":"2021-05-06T11:43:58.174Z", "source":{} }, { "_id":"4afaa735-4618-40cf-8b4f-00ee83b2c3c5", "status":"INTERNAL_UPDATE", "conversationId":"8e37c704-add8-4f9f-8e70-d630c24f653b", "messageIds":[], "messageId":"7c860126-bf1e-41b2-a7d3-6bcec3e8d5fb", "responseId":"09cec9a1-2621-481d-b527-d98b007ef5be", "conversation":{}, "message":{}, "params":{}, "timestamp":"2021-05-06T11:43:58.736Z", "source":{} }, { "_id":"cf8deeca-2cfd-497e-b92b-03204c84217a", "status":"NEW", "conversationId":"3c6870b5-88d6-4e21-8629-28137dea3fee", "messageIds":[], "messageId":"da84e414-2269-4812-8ddd-e2cd6c9be4fd", "responseId":"ae1014b2-0cc1-41f0-9990-cf724ed67ab7", "conversation":{}, "message":{}, "params":{}, "timestamp":"2021-05-06T13:37:55.060Z", "source":{} } ]
当前尝试的查询
db.documentName.aggregate([ { '$match': { '$and': [ { timestamp: { '$gte': ISODate('2021-05-01T00:00:00.000Z'), '$lte': ISODate('2021-05-10T23:59:59.999Z') } }, { 'source.author': { '$regex': 'user', '$options': 'i' } }, {}, {} ] } }, { '$group': { _id: {'conversationId': '$conversationId'} }, { '$count': 'document_count' } ])
问题：尝试在
$group的_id中添加$hour: '$timestamp'时报错，无法实现按日期/小时的维度统计。
期望结果
日期范围超过一天时
[ {"date": "2021-05-04", "doc_count": 1}, {"date": "2021-05-05", "doc_count": 2}, {"date": "2021-05-06", "doc_count": 2} ]
说明：2021-05-06有3条文档，但其中2条属于同一
conversationId，去重后统计数为2。
单日查询时（如查询2021-05-06）
[ {"hour": "2021-05-06T11:00:00Z", "doc_count": 1}, {"hour": "2021-05-06T13:00:00Z", "doc_count": 1} ]
解决方案
方案1：应用层判断时间范围（推荐，更高效）
先在应用层计算查询开始/结束时间的差值，根据是否超过24小时选择对应的聚合逻辑：
情况1：日期范围超过一天，按日期统计
const startDate = ISODate('2021-05-01T00:00:00.000Z'); const endDate = ISODate('2021-05-10T23:59:59.999Z'); db.documentName.aggregate([ // 过滤符合条件的文档 { $match: { timestamp: { $gte: startDate, $lte: endDate }, 'source.author': { $regex: 'user', $options: 'i' } } }, // 第一步：按conversationId+日期分组，实现去重（同一会话同一天仅保留一条） { $group: { _id: { conversationId: '$conversationId', date: { $dateToString: { format: "%Y-%m-%d", date: "$timestamp" } } } } }, // 第二步：按日期统计去重后的会话数量 { $group: { _id: '$_id.date', doc_count: { $sum: 1 } } }, // 格式化输出字段 { $project: { _id: 0, date: '$_id', doc_count: 1 } }, // 按日期升序排序 { $sort: { date: 1 } } ])
情况2：单日查询，按小时统计
const startDate = ISODate('2021-05-06T00:00:00.000Z'); const endDate = ISODate('2021-05-06T23:59:59.999Z'); db.documentName.aggregate([ { $match: { timestamp: { $gte: startDate, $lte: endDate }, 'source.author': { $regex: 'user', $options: 'i' } } }, // 按conversationId+小时分组去重 { $group: { _id: { conversationId: '$conversationId', // MongoDB 5.0+可用$dateTrunc截断到小时，低版本用$dateToString格式化 hour: { $dateTrunc: { date: "$timestamp", unit: "hour" } } } } }, // 按小时统计数量 { $group: { _id: '$_id.hour', doc_count: { $sum: 1 } } }, // 格式化输出 { $project: { _id: 0, hour: '$_id', doc_count: 1 } }, { $sort: { hour: 1 } } ])
方案2：聚合内动态判断（数据库层处理）
通过$expr计算时间范围差值，用$facet分别处理两种场景后合并结果：
const startDate = ISODate('2021-05-01T00:00:00.000Z'); const endDate = ISODate('2021-05-10T23:59:59.999Z'); db.documentName.aggregate([ { $match: { timestamp: { $gte: startDate, $lte: endDate }, 'source.author': { $regex: 'user', $options: 'i' } } }, // 标记是否为跨天查询 { $addFields: { isMultiDay: { $gt: [ { $subtract: [endDate, startDate] }, 24 * 60 * 60 * 1000 // 一天的毫秒数 ] } } }, // 并行处理两种聚合逻辑 { $facet: { dateAgg: [ { $match: { isMultiDay: true } }, { $group: { _id: { conversationId: '$conversationId', date: { $dateToString: { format: "%Y-%m-%d", date: "$timestamp" } } } } }, { $group: { _id: '$_id.date', doc_count: { $sum: 1 } } }, { $project: { _id: 0, date: '$_id', doc_count: 1 } }, { $sort: { date: 1 } } ], hourAgg: [ { $match: { isMultiDay: false } }, { $group: { _id: { conversationId: '$conversationId', hour: { $dateTrunc: { date: "$timestamp", unit: "hour" } } } } }, { $group: { _id: '$_id.hour', doc_count: { $sum: 1 } } }, { $project: { _id: 0, hour: '$_id', doc_count: 1 } }, { $sort: { hour: 1 } } ] } }, // 合并结果，选择对应场景的聚合数据 { $project: { result: { $cond: { if: { $gt: [ { $size: '$dateAgg' }, 0 ] }, then: '$dateAgg', else: '$hourAgg' } } } }, // 展开结果数组并格式化根文档 { $unwind: '$result' }, { $replaceRoot: { newRoot: '$result' } } ])
错误原因说明
之前的尝试报错是因为：
- 直接在
$group的_id中使用$hour: '$timestamp'时，没有正确处理日期字段的表达式（需确保时间字段是Date类型，且聚合操作符使用正确） - 缺少先按
conversationId+时间维度去重，再按时间维度统计的步骤，导致无法按日期/小时拆分统计结果
内容的提问来源于stack exchange，提问作者Richesh Chouksey
相关产品推荐
相关产品推荐
