cloudant index: count number of unique users per time period

Question

A very similar post was made about this issue here. In cloudant, I have a document structure storing when users access an application, that looks like the following:

{"username":"one","timestamp":"2015-10-07T15:04:46Z"}---| same day {"username":"one","timestamp":"2015-10-07T19:22:00Z"}---^
{"username":"one","timestamp":"2015-10-25T04:22:00Z"}
{"username":"two","timestamp":"2015-10-07T19:22:00Z"}

What I want to know is to count the # of unique users for a given time period. Ex:

2015-10-07 = {"count": 2} two different users accessed on 2015-10-07
2015-10-25 = {"count": 1} one different user accessed on 2015-10-25
2015 = {"count" 2} two different users accessed in 2015

This all just becomes tricky because for example on 2015-10-07, username: one has two records of when they accessed, but it should only return a count of 1 to the total of unique users.

I've tried:

function(doc) {
    var time = new Date(Date.parse(doc['timestamp'])); 
    emit([time.getUTCFullYear(),time.getUTCMonth(),time.getUTCDay(),doc.username], 1);
}

This suffers from several issues, which are highlighted by Jesus Alva who commented in the post I linked to above.

Thanks!

Answer 1

There's probably a better way of doing this, but off the top of my head ...

You could try emitting an index for each level of granularity:

function(doc) {
    var time = new Date(Date.parse(doc['timestamp'])); 
    var year = time.getUTCFullYear();
    var month = time.getUTCMonth()+1;
    var day = time.getUTCDate();

    // day granularity
    emit([year,month,day,doc.username], null);

    // year granularity
    emit([year,doc.username], null);
}

// reduce function - `_count`

Day query (2015-10-07):

inclusive_end=true&
start_key=[2015, 10, 7, "\u0000"]&
end_key=[2015, 10, 7, "\uefff"]&
reduce=true&
group=true

Day query result - your application code would count the number of rows:

{"rows":[
  {"key":[2015,10,7,"one"],"value":2},
  {"key":[2015,10,7,"two"],"value":1}
]}

Year query:

inclusive_end=true&
start_key=[2015, "\u0000"]&
end_key=[2015, "\uefff"]&
reduce=true&
group=true

Query result - your application code would count the number of rows:

{"rows":[
  {"key":[2015,"one"],"value":3},
  {"key":[2015,"two"],"value":1}
]}