Cloudant Custom Sort

Question

I have my data as follows

{
    "key":"adasd",
    "col1"::23, 
    "col2":3
}

I want to see the results sorted in descending order of the ratio of col1/sum(col2)

where sum(col2) refers to the sum of all values of col2. I am a bit new to cloudant so I don't know what the best way to approach this is. I can think of a few options.

1. Create a new column for sum(col2) and keep updating it with each new value of col2
2. For each record,also create a new column col1/sum(col2). Then i can sort on this column.
3. Use Views to calculate the ratio and sum on the fly. This way I don't have to store new columns plus I don't have to perform costly calculations on each update.

I tried to create a view and the map function is easy enough

function (doc) {
  emit(doc._id, {"col1_value":doc.col1,"col2_value":doc.col2});
}

but I am confused by the reduce template

function (keys, values, rereduce) {
  if (rereduce) {
    return sum(values);
  } else {
    return values.length;
  }
}

I have no idea on how to access the values of the two columns and then aggregate here. Is this even possible? Is there any other way to achieve the result I need?

Answer 1

Two comments:

1. Ordering by X/sum(Y) is the same as ordering by X (or by -X if sum(Y) is negative). So for ordering purposes, just order by X and save yourself a bunch of hassle.

2. Assuming you actually want to know the value of X/sum(Y), and not just order by it, there's no one-step way to accomplish this in CouchDB. The best I can think of is to create a map/reduce view that gives you the global sum(Y). Then you can fetch that sum with a simple query, and do the math in your application, when fetching your documents.