Groupin Consecutive Data in MongoDb

user_7826037 0
Viewed 79

I have Ignition Data against time(in ASC order) stored in MongoDB like:

1. **V_no**              **Ign**           **time** 
2. 001                     On                 1536721212 
3. 001                     On                 1536721213
4. 001                     On                 1536721220
5. 001                     Off                1536721221
6. 001                     Off                1536721222 
7. 001                     On                 1536721234
8. 001                     On                 1536721256
9. 001                     Off                1536721299

what I am looking for,is grouping consecutive ignition session and output be like:

1. **V_no**              **Ign**             **sTime**        **eTime**
2. 001                     On                 1536721212      1536721220
3. 001                     Off                1536721221      1536721222 
4. 001                     On                 1536721234      1536721256
5. 001                     Off                1536721299      1536721299

In R programming, the grouping of consecutive ignition data can be done using rleid(Ignition) like:

data %>% group(rleid(Ign)) %>% mutate(sTime=first(time),eTime=last(time))

I want the same type of solution in MongoDB or Mysql.

mongodb grouping sql
2 Answers

If you have latest version of MySQL, then you can use row_number() :

select v_no, ign, min(time), max(time)
from (select t.*, row_number() over (partition by v_no order by time) as seq1,
                  row_number() over (partition by v_no, ign order by time) as seq2
      from table t
     ) t
group by v_no, Ign, (seq1- seq2);
user_55294450

Following is the query in MongoDB for this

//Populate the data
db.example.insert({v_no:'001',ign:'On',time:'1536721212'})
db.example.insert({v_no:'001',ign:'On',time:'1536721213'})
db.example.insert({v_no:'001',ign:'On',time:'1536721220'})
db.example.insert({v_no:'001',ign:'Off',time:'1536721221'})
db.example.insert({v_no:'001',ign:'Off',time:'1536721222'})
db.example.insert({v_no:'001',ign:'On',time:'1536721234'})
db.example.insert({v_no:'001',ign:'On',time:'1536721256'})
db.example.insert({v_no:'001',ign:'Off',time:'1536721299'})

var v_no = '',ign = '', stime = '', etime = '';
db.example.find().forEach(function(doc){
    if(v_no == doc.v_no && ign == doc.ign) {
        etime = doc.time;
    } else {
        if(v_no != null && v_no.length > 0) {
            db.example_output.updateOne({v_no:v_no,ign:ign,stime:stime},{$set:{etime:etime}});
        }
        v_no = doc.v_no;
        ign = doc.ign;
        stime = doc.time;
        etime = doc.time;
        db.example_output.insert({v_no:v_no,ign:ign,stime:stime,etime:etime})
    }
})

//to get the 'grouping consecutive ignition session'
db.example_output.find()

Do take note that this query will be expensive if you have huge data.

user_15712290
Related