缺少键的 Mongo 组命令 [英] Mongo group command with missing keys
问题描述
我使用 Mongo 作为滴答数据库，我定期将带有时间戳的数据放入其中.后来我用这些数据查询系统的统计信息.
我有一个这样的集合:
<预><代码>[{"日期": ISODate("2014-06-16T03:58:58.152Z"),分钟":4，最大":25},{"日期": ISODate("2014-06-18T03:48:58.152Z"),分钟":3，最大":8},{"日期": ISODate("2014-06-18T03:48:58.152Z"),分钟":8，最大":12}]我使用 group 命令按天对所有文档进行分组.
db.stats.group({keyf:函数(文档){var date = new Date(doc.date);date.setHours(0, 0, 0, 0);返回{日期:日期}；},减少:函数(当前，结果){result.max = Math.max(curr.max, result.max || 0);result.min = Math.min(curr.min, result.min || 0);},最初的: {}});这给了我以下结果:
<预><代码>[{"日期": ISODate("2014-06-16T21:00:00.000Z"),分钟":4，最大":25},{"日期": ISODate("2014-06-18T21:00:00.000Z"),分钟":3，最大":12}]对我来说的问题是，由于 2014-06-17 上没有条目，我需要以某种方式创建一个 min: 0 和 max: 0 所以结果应该是这样的:><预><代码>[{"日期": ISODate("2014-06-16T21:00:00.000Z"),分钟":4，最大":25},{"日期": ISODate("2014-06-17T21:00:00.000Z"),分钟":0，最大":0},{"日期": ISODate("2014-06-18T21:00:00.000Z"),分钟":3，最大":12}]
如果我能以某种方式给 group 命令一个日期数组作为键，这可以工作.如果这可能在 Mongo 中实现?
如果我能以某种方式给 group 命令一个日期数组作为键，这可以工作.如果这可能在 Mongo 中实现?
您不能对不存在的数据进行group()，因此您必须每天插入一些数据.
实际上，您想要采用的方法是预聚合数据 以确保您在日期范围内每天都有样本.
<块引用>对我来说的问题是，由于 2014-06-17 上没有条目，我需要以某种方式创建一个 min: 0 和 max: 0
鉴于您正在计算每日最小值/最大值，一种方法是利用新的 $min 和 $max 更新 MongoDB 2.6 中的操作符并将值更新到每日集合中.
由于您只想获得每天的混合/最大值，因此每日文档可能如下所示:
<代码>{"date": "yyyy-mm-dd",分钟":0，最大":0}注意:在本例中，为了清楚起见，我选择将日期格式化为 ISO8601 字符串，但如果您愿意，也可以使用 Date 字段.完整的 BSON Date 将包括时间和时区信息，因此您需要确保为您的查询/更新进行适当的截断.
以下是 mongo shell 中使用 Moment.js 的示例a>(为了方便日期操作)迭代一系列日期并添加任何缺失值:
var nextDate = moment("2014-06-01");var lastDate = moment("2014-07-31");while (nextDate <= lastDate) {db.daily.findAndModify({询问: {日期:nextDate.format("YYYY-MM-DD")},更新: {$max: { min: 0, max: 0 },},upsert:真的});nextDate.add(1, '天');}因为这是使用 upsert 标志:
- 不存在的日常文档将被插入，默认最小值/最大值为 0
- 确实存在的日常文档将使用其现有
min和max字段值的 $max 进行更新(即，将保留现有值)
您可以在插入新数据时使用相同的更新查询来维护您的每日最小值/最大值(通过提供最小值/最大值而不是 0 的当前值)，或计算每日最小值/使用排序顺序的原始样本的最大值:
//2014-06-18 的每日最小值(假设刻度使用标准的日期"字段)db.ticks.find(//查询条件{ date: { $gte: ISODate("2014-06-18"), $lt: ISODate("2014-06-19") } },//投影{分钟:1，_id:0}).sort({min: 1}).limit(1).
//2014-06-18 的每日最大值db.ticks.find(//查询条件{ date: { $gte: ISODate("2014-06-18"), $lt: ISODate("2014-06-19") } },//投影{最大:1，_id:0}).sort({max: -1}).limit(1)I'm using Mongo as a tick database where I periodically put data in with timestamps. Later I use this data to query statistics about the system.
I have a collection like this:
[
{
"date": ISODate("2014-06-16T03:58:58.152Z"),
"min": 4,
"max": 25
},
{
"date": ISODate("2014-06-18T03:48:58.152Z"),
"min": 3,
"max": 8
},
{
"date": ISODate("2014-06-18T03:48:58.152Z"),
"min": 8,
"max": 12
}
]
I use the group command to group all documents by day.
db.stats.group({
keyf: function (doc) {
var date = new Date(doc.date);
date.setHours(0, 0, 0, 0);
return { date: date };
},
reduce: function (curr, result) {
result.max = Math.max(curr.max, result.max || 0);
result.min = Math.min(curr.min, result.min || 0);
},
initial: {}
});
This gives me the result of:
[
{
"date": ISODate("2014-06-16T21:00:00.000Z"),
"min": 4,
"max": 25
},
{
"date": ISODate("2014-06-18T21:00:00.000Z"),
"min": 3,
"max": 12
}
]
The problem for me is that since there was no entries on 2014-06-17 I need to somehow create one with min: 0 and max: 0 so the result should look like:
[
{
"date": ISODate("2014-06-16T21:00:00.000Z"),
"min": 4,
"max": 25
},
{
"date": ISODate("2014-06-17T21:00:00.000Z"),
"min": 0,
"max": 0
},
{
"date": ISODate("2014-06-18T21:00:00.000Z"),
"min": 3,
"max": 12
}
]
If I could somehow give the group command an array of dates as keys this could work. If this possible in Mongo?
If I could somehow give the group command an array of dates as keys this could work. If this possible in Mongo?
You can't group() on data that doesn't exist, so you will have to insert some data for each day.
Effectively the approach you want to take is pre-aggregating data in order to ensure you have a sample for every day in your date range.
The problem for me is that since there was no entries on 2014-06-17 I need to somehow create one with min: 0 and max: 0
Given you are calculating a daily min/max, one approach would be to take advantage of the new $min and $max update operators in MongoDB 2.6 and upsert values into a daily collection.
Since you only want to have the mix/max value per day, a daily document could look like:
{
"date": "yyyy-mm-dd",
"min": 0,
"max": 0
}
Note: I've chosen to format date as an ISO8601-ish string for clarity in this example, but you could also use a Date field if you prefer. A full BSON Date will include time and timezone information, so you'll need to be sure to truncate appropriately for your queries/updates.
Here's an example in the mongo shell using Moment.js (for convenient date manipulation) to iterate a range of dates and add any missing values:
var nextDate = moment("2014-06-01");
var lastDate = moment("2014-07-31");
while (nextDate <= lastDate) {
db.daily.findAndModify({
query: {
date: nextDate.format("YYYY-MM-DD")
},
update: {
$max: { min: 0, max: 0 },
},
upsert: true
});
nextDate.add(1, 'day');
}
Since this is using an upsert flag:
- daily documents that don't exist will be inserted with default min/max values of 0
- daily documents that do exist will be updated with the $max of their existing
minandmaxfield values (i.e. existing values will be preserved)
You could use the same update query to maintain your daily min/max as new data is inserted (by providing the current values for min/max instead of 0), or calculate the daily min/max from the original samples using a sort order:
// Daily minimum for 2014-06-18 (assuming ticks are using standard `Date` fields)
db.ticks.find(
// query criteria
{ date: { $gte: ISODate("2014-06-18"), $lt: ISODate("2014-06-19") } },
// projection
{ min: 1, _id: 0 }
).sort({min: 1}).limit(1)
.
// Daily maximum for 2014-06-18
db.ticks.find(
// query criteria
{ date: { $gte: ISODate("2014-06-18"), $lt: ISODate("2014-06-19") } },
// projection
{ max: 1, _id: 0 }
).sort({max: -1}).limit(1)
这篇关于缺少键的 Mongo 组命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
