CODEWITHSUNDEEP
javascriptdictionaryviewcouchdbreduce
Diego
Diego

Reputation: 65

CouchDB, how calculate two values in one view

I have documents like these:

{"user": "A","year": "2016","price": 3,"litres": 19900}
{"user": "B","year": "2016","price": 2.3,"litres": 16000}
{"user": "C","year": "2016","price": 2.9,"litres": 15000}
{"user": "A","year": "2015","price": 4,"litres": 12030}
{"user": "B","year": "2015","price": 3,"litres": 20002}
{"user": "C","year": "2015","price": 2.9,"litres": 22000}

I need to calculate the sum of all values and the sum of one of the users by year, the goal is to compare a global average against one user.

For example, if I call the view with "A" as key to sum litres I need to get after reducing something like:

["A","2016"] {global: 50900, user: 19900}
["A","2015"] {global: 54032, user: 12030}

Thank you,

Diego

Upvotes: 3

Views: 177

Answers (2)

okurow
okurow

Reputation: 969

well.. here is my idea:

  1. you can use POST to set two Keys.

    function(doc) {
  var total={};
  total={year:doc.year,litres:doc.litres};
  emit(doc.user, {user:total,years:[total]});
  emit("", {user:"",years:[total]});
}

Query: 

     curl -X POST  http://localhost:5984/test_a/_design/test/_view/test -H "Content-type:application/json" -d '{"keys":["A",""]}'

Result (shortened): 

 "key":"A","value":{"user":{"year":"2016","litres":19900},"years":    [{"year":"2016","litres":19900}]}},
"key":"A","value":{"user":{"year":"2015","litres":12030},"years":[{"year":"2015","litres":12030}]}},
"key":"","value":{"user":"","years":[{"year":"2016","litres":19900}]}},
"key":"","value":{"user":"","years":[{"year":"2016","litres":16000}]}},
"key":"","value":{"user":"","years":[{"year":"2016","litres":15000}]}},
"key":"","value":{"user":"","years":[{"year":"2015","litres":12030}]}},
"key":"","value":{"user":"","years":[{"year":"2015","litres":20002}]}},
"key":"","value":{"user":"","years":[{"year":"2015","litres":22000}]}}
if you THEN make a reduce that collects the data from both keys:

function(keys, values, rereduce) {
  var user_sum=0;
  var total_a=[];
  var v_values={};
  var curUser="";
  for (var i=0;i<keys.length;i++) {
    if (keys[i]!="") {
      curUser=keys[i][0];
    }
    values[i].years.forEach(function(year) {
      if (v_values["year_"+year.year]) {
        v_values["year_"+year.year]=
          {year:year.year,  
           litres:v_values["year_"+year.year].litres+year.litres
          };
      }else{
        v_values["year_"+year.year]={year:year.year,litres:year.litres};
      }
   });

 }
 return ({user:curUser,years:v_values})

}

you get 

{"rows":[
  {"key":"A","value":
    {"user":"A",
     "years":{
       "year_2015":   {"year":"2015","litres":12030},
       "year_2016":{"year":"2016","litres":19900}
     }
    }
  },
 {"key":"","value":
   {"user":"",
    "years":{
      "year_2015":{"year":"2015","litres":54032},
      "year_2016":{"year":"2016","litres":50900}
    }
   }
 }

] }

i am a little bit lazy.. so check reduce / rereduce on you own...

Upvotes: 1

Kerr
Kerr

Reputation: 2812

You can't do this with a single call. If you swap your key order, so that year is first, then user, you can make two calls to the same view with different group levels. Group level 1 would give you the global year totals and group level 2 would give you the year by user totals.

Upvotes: 1

Related Questions