How To Simulating The Aggregate Functions Avg, Sum, Max, Min, And Count On Pouchdb?

Does anyone know how to create aggregate functions, for example avg, sum, max and min on PouchDB database. I created a simple application to test the PouchDB. I'm still not figured

Solution 1:

The highest and lowest values of the numbers fields are retrievable using the built-in _stats reduce function.

var myMapReduceFun = {
  map: function (doc) {
    emit(doc._id, doc.number);
  },
  reduce: '_stats'
};

db.query(myMapReduceFun, {reduce: true}).then(function (result) {
  // handle result
}).catch(function (err) {
  // handle errors
});

The result looks similar to this:

{"sum":35,"count":3,"min":10,"max":13,"sumsqr":214}

The highest value is in the "max"-field, the lowest in the "min"-field. Now you just have to calculate your desired average, for example the mean average:

var meanAverage = result.sum / result.count;

Other built-in reduce functions in PouchDB are _count and _sum.

The PouchDB documentation says the following about reduce functions:

Tip: if you’re not using a built-in, you’re probably doing it wrong.

Solution 2:

You can use the map/reduce functions of the db.query() method from PouchDB to get the average, sum, largest or any other kind of aggregation of the docs.

I have created a demo JSBin fiddle with a running example. I added the explanation of the functions directly into the code (below) as comments, as I thought it'd be simpler.

var db = newPouchDB('friendsdb');
var docs = [
      {'_id': '1', 'number': 10, 'values': '1, 2, 3', 'loto': 'fooloto'},
      {'_id': '2', 'number': 12, 'values': '4, 7, 9', 'loto': 'barloto'},
      {'_id': '3', 'number': 13, 'values': '9, 4, 5', 'loto': 'fooloto'}
];

db.bulkDocs(docs).then(function(result) {
  querySum();
  queryLargest();
  querySmallest();
  queryAverage();
}).catch(function(err) {
  console.log(err);
});

functionquerySum() {
  functionmap(doc) {
    // the function emit(key, value) takes two arguments// the key (first) arguments will be sent as an array to the reduce() function as KEYS// the value (second) arguments will be sent as an array to the reduce() function as VALUESemit(doc._id, doc.number);
  }
  functionreduce(keys, values, rereduce) {
    // keys://   here the keys arg will be an array containing everything that was emitted as key in the map function...//   ...plus the ID of each doc (that is included automatically by PouchDB/CouchDB).//   So each element of the keys array will be an array of [keySentToTheEmitFunction, _idOfTheDoc]//// values//   will be an array of the values emitted as valueconsole.info('keys ', JSON.stringify(keys));
    console.info('values ', JSON.stringify(values));
    // check for more info: http://couchdb.readthedocs.io/en/latest/couchapp/views/intro.html// So, since we want the sum, we can just sum all items of the values array// (there are several ways to sum an array, I'm just using vanilla for to keep it simple)var i = 0, totalSum = 0;
    for(; i < values.length; i++){
        totalSum += values[i];
    }
    return totalSum;
  }
  db.query({map: map, reduce: reduce}, function(err, response) {
    console.log('sum is ' + response.rows[0].value);
  });
}

functionqueryLargest() {
  functionmap(doc) {
    emit(doc._id, doc.number);
  }
  functionreduce(keys, values, rereduce) {
    // everything same as before (see querySum() above)// so, this time we want the larger element of the values array// http://stackoverflow.com/a/1379560/1850609returnMath.max.apply(Math, values);
  }
  db.query({map: map, reduce: reduce}, function(err, response) {
    console.log('largest is ' + response.rows[0].value);
  });
}

functionquerySmallest() {
  functionmap(doc) {
    emit(doc._id, doc.number);
  }
  functionreduce(keys, values, rereduce) {
    // all the same... now the looking for the minreturnMath.min.apply(Math, values);
  }
  db.query({map: map, reduce: reduce}, function(err, response) {
    console.log('smallest is ' + response.rows[0].value);
  });
}

functionqueryAverage() {
  functionmap(doc) {
    emit(doc._id, doc.number);
  }
  functionreduce(keys, values, rereduce) {
    // now simply calculating the averagevar i = 0, totalSum = 0;
    for(; i < values.length; i++){
        totalSum += values[i];
    }
    return totalSum/values.length;
  }
  db.query({map: map, reduce: reduce}, function(err, response) {
    console.log('average is ' + response.rows[0].value);
  });
}

Note: This is just one way to do it. There are several other possibilities (not emitting IDs as keys, using groups and different reduce functions, using built-in reduce functions, such as _sum, ...), I just thought this was the simpler alternative generally speaking.

Solution 3:

I'm a fan of views in PouchDB for problems like this.

https://pouchdb.com/2014/05/01/secondary-indexes-have-landed-in-pouchdb.html

It is possible to create a stored view that allows you to requery the same index multiple times: meaning while the first time through is slow (full scan), later queries will be much faster as the data has already been indexed.

var db = newPouchDB('friendsdb');

var view = {
  '_id':'_design/metrics',
  'views':{
    'metrics':{
      'map':function(doc){
        // collect up all the data we are looking foremit(doc._id, doc.number);
      }.toString(),
      'reduce':function(keys, values, rereduce){
        var metrics = {
          sum:0,
          max:Number.MIN_VALUE,
          min:Number.MAX_VALUE,
          count:0
        };
        // aggregate up the valuesfor(var i=values.length-1; i>=0; i--){
          var v = values[i];
          metrics.sum += v;
          metrics.max = (metrics.max < v) ? v : metrics.max;
          metrics.min = (metrics.min < v) ? metrics.min : v;
          metrics.count += v.count || 1;
        }
        metrics.avg = metrics.sum/metrics.count;
        return metrics;
      }.toString()
    }
  }
};

// alternately you could use a built in reduce// if one already exists for the aggregation // you are looking for//view.reduce = '_stats';// note the addition of the viewvar docs = [view
  ,{'_id':'1','number':10,'values':[1,2,3],'loto':'fooloto'}
  ,{'_id':'2','number':12,'values':[4,7,9],'loto':'barloto'}
  ,{'_id':'3','number':13,'values':[9,4,5],'loto':'fooloto'}
];

db.bulkDocs(docs).then(function(result) {
  db.query('metrics',{reduce:true},function(err, response) {
    var m = response.rows[0].value;
    console.log('smallest.: ' + m.min);
    console.log('largest..: ' + m.max);
    console.log('average..: ' + m.avg);
    console.log('count....: ' + m.count);
    console.log('Total ...: ' + m.sum);
  });
}).catch(function(err) {
  console.log(err);
});

Note the addition of the view to the data you load into your database, as well as the fact that the map and reduce are requried to be converted to strings (the .toString() at the end of the function)