MongoDB分组并从不同文档中减去值

本文介绍了MongoDB分组并从不同文档中减去值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的申请中有以下文件

{ "timestamp": ISODate("2015-09-17T21:14:35.0Z"), "sensor": "gas-in", "value": 2.5, }, { "timestamp": ISODate("2015-09-17T21:14:35.0Z"), "sensor": "gas-out", "value": 2.0, }, { "timestamp": ISODate("2015-09-17T21:20:35.0Z"), "sensor": "gas-in", "value": 6.3, }, { "timestamp": ISODate("2015-09-17T21:20:35.0Z"), "sensor": "gas-out", "value": 0.8, }

...等等...

我如何返回按时间戳分组的(加油)-(加油)差异? 我正在尝试编写一个将其返回如下的函数:

How can I return the difference (gas-in) - (gas-out) grouped per timestamp? I am trying to write a function that returns it like this:

{ "timestamp": ISODate("2015-09-17T21:14:35.0Z"), "sensor": "gas-difference", "value": 0.5, // is calculated by 2.5 - 2.0 }, { "timestamp": ISODate("2015-09-17T21:20:35.0Z"), "sensor": "gas-difference", "value": 5.5, // is calculated by 6.3 - 0.8 },

...等等...

我玩过聚合函数和$ subtract，读过其他SO问题，但是它们似乎并没有解决我的问题.在其他问题中，他们似乎知道要相互减去2个文档，但就我而言，我不知道文档的数量，$ timestamp列"是两个文档的匹配标识符.

I have played around with aggregation functions and $subtract, read other SO questions but they do not seem to have the answer to my question. In the other questions they seem to know which to 2 document to subtract from each other, but in my case I don't know the number of documents and the $timestamp "column" is the matching identifier for the two documents.

有人可以帮助我解决这个问题吗?谢谢！

Can anybody help me with solving this? Thanks!

推荐答案

您首先需要基于条件 $sum 在每个值的分组中的 $cond 运算符上.然后，您可以分别 $subtract :

What you first need is a conditional $sum based on the $cond operator in the grouping for each value. Then you can separately $subtract:

db.gas.aggregate([ { "$group": { "_id": "$timestamp", "gas-in": { "$sum": { "$cond": [ { "$eq": [ "$sensor", "gas-in" ] }, "$value", 0 ] } }, "gas-out": { "$sum": { "$cond": [ { "$eq": [ "$sensor", "gas-out"] }, "$value", 0 ] } }, }}, { "$project": { "gasdifference": { "$subtract": [ "$gas-in", "$gas-out" ] } }} ])

哪个给出结果:

{ "_id" : ISODate("2015-09-17T21:20:35Z"), "gasdifference" : 5.5 } { "_id" : ISODate("2015-09-17T21:14:35Z"), "gasdifference" : 0.5 }

另一种方法是将排气"值设为单个负值:

The alternate is just make the "gas-out" values negative for a single stage:

db.gas.aggregate([ { "$group": { "_id": "$timestamp", "gasdifference": { "$sum": { "$cond": [ { "$eq": [ "$sensor", "gas-in" ] }, "$value", { "$subtract": [ 0, "$value" ] } ] } } }} ])

这将更加高效.

如果您有两个以上的传感器"值，则只需嵌套" $cond语句:

If you have more than two possible "sensor" values then you just "nest" the $cond statements:

db.gas.aggregate([ { "$group": { "_id": "$timestamp", "gasdifference": { "$sum": { "$cond": [ { "$eq": [ "$sensor", "gas-in" ] }, "$value", { "$cond": [ { "$eq": [ "$sensor", "gas-out" ] }, { "$subtract": [ 0, "$value" ] }, 0 ]} ] } } }} ])

由于它们是三元"运算符(if-then-else)，因此任何进一步的逻辑都属于"else"条件.

Since they are "ternary" operators ( if-then-else ) then any further logic falls within the "else" condition.