首先,谢谢您的阅读以及可能提出解决方案。
目前,我正在尝试弄清楚如何汇总和总和事件基础用户日期时间事件。假设存储的数据如下:
export type TimeTrackingEvents = "start" | "pause" | "resume" | "end";
export interface ITimeTracking {
user: string;
event: TimeTrackingEvents;
timestamp: Date;
}
用户工作日后,我们在DB中有以下数据集:
[{
_id: 5c87f24d15e381003334f26f,
user: 5c87bb4aad3c21e90aeda6f7,
timestamp: 2019-03-14T07:00:00.000Z,
event: 'start'
},
{
_id: 5c8a5ceea708844edc9e1d31,
user: 5c87bb4aad3c21e90aeda6f7,
timestamp: 2019-03-14T08:00:00.000Z,
event: 'pause'
},
{
_id: 5c8a5d02a708844edc9e1d33,
user: 5c87bb4aad3c21e90aeda6f7,
timestamp: 2019-03-14T09:00:00.000Z,
event: 'resume'
},
{
_id: 5c8a5cf9a708844edc9e1d32,
user: 5c87bb4aad3c21e90aeda6f7,
timestamp: 2019-03-14T10:00:00.000Z,
event: 'end',
}];
基于用户今天工作2H的数据。这引发了我们的最后一个问题:如何根据给出的数据集实现以下输出?
{
_id: 5c87f24d15e381003334f26f,
user: 5c87bb4aad3c21e90aeda6f7,
duration: 2, // this represents the 2 hours the user has been working
}
这意味着我想将事件持续时间汇总到"暂停"one_answers"简历"到"结束",而没有"暂停"one_answers"简历"之间的持续时间。
我一直在这里和网络上搜索,但找不到合理的解决方案。
到目前为止,这是我的聚合:
[
{
$match: {
user: userId,
// only today
timestamp: {
$gte: today,
$lt: tomorrow,
},
},
},
{ $sort: { timestamp: -1 } },
// above works, insert magic here... ;)
]
我的mongodb在版本4上。
再次感谢你们的时间。欢呼toni
编辑
所以这是现状:@anthonywinzlet非常感谢您。您提供的链接确实有帮助!
所以ATM我正在添加以下内容:
{
$addFields: {
timeOfDay: { // The result from '2019-03-14T10:00:00.000+00:00' is 36.000.000 which equals 10 hours
$mod: [
{ $toLong: "$timestamp" },
1000 * 60 * 60 * 24,
],
},
},
},
我获得了代表10个小时的timeOfDay:36000000
之类的字段。
我无法工作的是@JonasWilms提到的$switch
。结果/" totaltimeinminutes"表示始终代表122.400.000(34小时),即使我将MS转换为负值:
// ...
{
$group: {
_id: "result",
totalTimeInMinutes: {
// $sum: { $divide: ["$timeOfDay", 60 * 1000] },
$sum: {
$switch: {
branches: [
{ case: "end", then: { $multiply: ["$timeOfDay", 1] } },
{ case: "start", then: { $multiply: ["$timeOfDay", -1] } },
{ case: "resume", then: { $multiply: ["$timeOfDay", -1] } },
{ case: "pause", then: { $multiply: ["$timeOfDay", 1] } },
],
default: 0,
},
},
},
},
},
// ...
这是因为10 9 8 7等于34,但是必须是10 (-9) 8 (-7),它等于2。
我想您可以将数组分组为一个对象并总结时间(假设所有时间匹配):
{
$group: {
_id: "result", // you could directly group by user id here
duration: { $sum: { // in rounded / floored hours
$switch: { branches: [
{ case: "start", then: { $multiply: [{ $hour: "$timestamp"}, -1] },
{ case: "end", then: { $multiply: [{ $hour: "$timestamp" }, 1] },
{ case: "pause", then: { $multiply: [{ $hour: "$timestamp" }, -1] },
{ case: "resume", then: { $multiply: [{ $hour: "$timestamp" }, 1] }
]}
}}
}
}
感谢您将我的头朝正确的方向带来。我想分享我的最终解决方案。我想会有一个更优雅的版本,而边缘案例尚未进行测试,但可以这样工作:
[
{
$match: {
// this is just for testing purpose atm
timestamp: {
$gte: new Date(today),
$lt: new Date(tomorrow),
},
},
},
{ $sort: { timestamp: -1 } },
{
$addFields: {
timeOfDay: { // The result from '2019-03-14T10:00:00.000+00:00' is 36.000.000 which equals 10 hours
$mod: [
{ $toLong: "$timestamp" },
1000 * 60 * 60 * 24,
],
},
},
},
{
$project: {
timeOfDay: {
$cond: {
if: { $in: ["$event", ["start", "resume"]] },
then: { $multiply: ["$timeOfDay", -1] },
else: "$timeOfDay",
},
},
},
},
{
$group: {
_id: "result",
workingMinutesToday: {
$sum: { $divide: ["$timeOfDay", { $multiply: [60, 1000] }] },
},
},
},
]
我很想看到你们更优雅的方法,但我希望这可以节省某人的时间
欢呼toni