查找每天的最后一条记录 [英] Find last record of each day
问题描述
我使用mongodb(我是mongodb的新手)来存储有关功耗的数据，每分钟都有一条新记录，这是一个示例:
{"id":"5309d4cae4b0fbd904cc00e1","adco":"O","hchc":7267599,"hchp":10805900,"hhphc":"g","ptec":"c","iinst":13,"papp":3010,"imax":58,"optarif":"s","isousc":60,"motdetat":"Á","date":1393156826114}
所以我每天大约有1440条记录.
我想按天计算成本，但是这样做的问题是我需要一天的最后一条记录，因为该记录可以给我绝对的kWh(kiloWatt-Hour).因此，如果我删除了昨天最后一条记录的千瓦时电量，那么我将获得当天的千瓦时电量.
字段hchp给我这个绝对kWh.字段date对应于以毫秒为单位的测量时间.
当天消费=一天结束时的绝对消费-昨天结束时的绝对消费.
如何获取mongodb中每一天的最后一条记录?
注意:我在Spring Java中使用mongodb，所以我需要这样的查询:
获得所有衡量指标的示例:
@Query("{ 'date' : { $gt : ?0 }}")
public List<Mesure> findByDateGreaterThan(Date date, Sort sort);
比原始答案要现代一些:
db.collection.aggregate([
{ "$sort": { "date": 1 } },
{ "$group": {
"_id": {
"$subtract": ["$date",{"$mod": ["$date",86400000]}]
},
"doc": { "$last": "$$ROOT" }
}},
{ "$replaceRoot": { "newDocument": "$doc" } }
])
同样的原则适用于您 $sort 集合，然后 $group 从分组边界中获取 $last 数据.
让事情变得更清楚一点，因为原始的写作是您可以使用$$ROOT而不是指定每个文档属性，当然 $sort 首先，然后在所需的公用密钥上 $group 保留 $last 或 $first ，具体取决于出现在分组边界上的排序顺序(对于所需属性而言). /p>
还可以将BSON日期(而不是问题中的时间戳)用于BSON日期，请参见按15分钟的时间间隔在MongoDb中分组结果关于如何实际使用和返回BSON日期值在不同时间间隔进行累加的不同方法.
不太清楚您要在这里做什么，但是如果我的理解正确的话，您可以总体上做到这一点.因此，要获取每天的最新记录:
db.collection.aggregate([
// Sort in date order as ascending
{"$sort": { "date": 1 } },
// Date math converts to whole day
{"$project": {
"adco": 1,
"hchc": 1,
"hchp": 1,
"hhphc": 1,
"ptec": 1,
"iinst": 1,
"papp": 1,
"imax": 1,
"optarif": 1,
"isousc": 1,
"motdetat": 1,
"date": 1,
"wholeDay": {"$subtract": ["$date",{"$mod": ["$date",86400000]}]}
}},
// Group on wholeDay ( _id insertion is monotonic )
{"$group":
"_id": "$wholeDay",
"docId": {"$last": "$_id" },
"adco": {"$last": "$adco" },
"hchc": {"$last": "$hchc" },
"hchp": {"$last": "$hchp" },
"hhphc": {"$last": "$hhphc" },
"ptec": {"$last": "$ptec" },
"iinst": {"$last": "$iinst" },
"papp": {"$last": "$papp" },
"imax": {"$last": "$imax" },
"optarif": {"$last": "$optarif",
"isousc": {"$last": "$isouc" },
"motdetat": {"$last": "$motdetat" },
"date": {"$last": "$date" },
}}
])
因此，这里的原理是给定时间戳记值，进行日期数学运算以将其投影为每天开始时的午夜时间.然后，由于文档上的_id键已经是单调的(一直在增加)，因此只需对wholeDay值进行分组，同时将$last文档从分组边界中拉出即可.
如果不需要所有字段，则仅对所需的字段进行投影和分组.
是的，您可以在spring数据框架中执行此操作.我确定里面有一个包装的命令.但是否则，获取本机命令的咒语是这样的:
mongoOps.getCollection("yourCollection").aggregate( ... )
为便于记录，如果您实际上有BSON日期类型而不是数字作为时间戳，则可以跳过日期数学运算:
db.collection.aggregate([
{ "$group": {
"_id": {
"year": { "$year": "$date" },
"month": { "$month": "$date" },
"day": { "$dayOfMonth": "$date" }
},
"hchp": { "$last": "$hchp" }
}}
])
I use mongodb (I'm new in mongodb) to store data about my power consumption , each minute there is a new record, here is an example :
{"id":"5309d4cae4b0fbd904cc00e1","adco":"O","hchc":7267599,"hchp":10805900,"hhphc":"g","ptec":"c","iinst":13,"papp":3010,"imax":58,"optarif":"s","isousc":60,"motdetat":"Á","date":1393156826114}
so I have around 1440 records a day.
I want to compute the cost by day, but the problem for this is that I need the last record of the day, because this record can give me the amount of absolute kWh (kiloWatt-Hour). So if I remove the kwh amount of the last record of the yesterday, I have the amount of kWh for the day.
The field hchp gives me this absolute kWh. The field date corresponds to the time of the measure in millisecond.
consumption of the day = absolute consumption of the end of the day - absolute consumption of the end of yesterday.
How is it possible to get the last record of each day in mongodb ?
Note : I use mongodb in spring java, so I need a query like this :
Example to get all measures :
@Query("{ 'date' : { $gt : ?0 }}")
public List<Mesure> findByDateGreaterThan(Date date, Sort sort);
A bit more modern than the original answer:
db.collection.aggregate([
{ "$sort": { "date": 1 } },
{ "$group": {
"_id": {
"$subtract": ["$date",{"$mod": ["$date",86400000]}]
},
"doc": { "$last": "$$ROOT" }
}},
{ "$replaceRoot": { "newDocument": "$doc" } }
])
The same principle applies that you essentially $sort the collection and then $group on the required grouping key picking up the $last data from the grouping boundary.
Making things a bit clearer since the original writing is that you can use $$ROOT instead of specifying every document property, and of course the $replaceRoot stage allows you to restore that data fully as the original document form.
But the general solution is still $sort first, then $group on the common key that is required and keep the $last or $first depending on sort order occurrences from the grouping boundary for the properties that are required.
Also for BSON Dates as opposed to a timestamp value as in the question, see Group result by 15 minutes time interval in MongoDb for different approaches on how to accumulate for different time intervals actually using and returning BSON Date values.
Not quite sure what you are going for here but you could do this in aggregate if my understanding is right. So to get the last record for each day:
db.collection.aggregate([
// Sort in date order as ascending
{"$sort": { "date": 1 } },
// Date math converts to whole day
{"$project": {
"adco": 1,
"hchc": 1,
"hchp": 1,
"hhphc": 1,
"ptec": 1,
"iinst": 1,
"papp": 1,
"imax": 1,
"optarif": 1,
"isousc": 1,
"motdetat": 1,
"date": 1,
"wholeDay": {"$subtract": ["$date",{"$mod": ["$date",86400000]}]}
}},
// Group on wholeDay ( _id insertion is monotonic )
{"$group":
"_id": "$wholeDay",
"docId": {"$last": "$_id" },
"adco": {"$last": "$adco" },
"hchc": {"$last": "$hchc" },
"hchp": {"$last": "$hchp" },
"hhphc": {"$last": "$hhphc" },
"ptec": {"$last": "$ptec" },
"iinst": {"$last": "$iinst" },
"papp": {"$last": "$papp" },
"imax": {"$last": "$imax" },
"optarif": {"$last": "$optarif",
"isousc": {"$last": "$isouc" },
"motdetat": {"$last": "$motdetat" },
"date": {"$last": "$date" },
}}
])
So the principle here is that given the timestamp value, do the date math to project that as the midnight time at the beginning of each day. Then as the _id key on the document is already monotonic (always increasing), then simply group on the wholeDay value while pulling the $last document from the grouping boundary.
If you don't need all the fields then only project and group on the ones you want.
And yes you can do this in the spring data framework. I'm sure there is a wrapped command in there. But otherwise, the incantation to get to the native command goes something like this:
mongoOps.getCollection("yourCollection").aggregate( ... )
For the record, if you actually had BSON date types rather than a timestamp as a number, then you can skip the date math:
db.collection.aggregate([
{ "$group": {
"_id": {
"year": { "$year": "$date" },
"month": { "$month": "$date" },
"day": { "$dayOfMonth": "$date" }
},
"hchp": { "$last": "$hchp" }
}}
])
这篇关于查找每天的最后一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
