0

I have following schema:

Customer ID
Location Name
Time of Visit

The above stores the information of all customer's visit at various locations.

I would like to know if there's a way to write an aggregate query in MongoDB, so that it gives the Total Visitor information by different sections of the day, per day per location.

Sections of the day:

EDIT:

12 am  -  8 am
8 am   -  11 am
11 am  -  1 pm
1 pm   -  4 pm
4 pm   -  8 pm
8 pm   -  12 pm

If a customer visits a location on the same day and same section of the day more than once, it should be counted just once. However, if that customer visits a location on the same day but for different sections of the day, it should be counted exactly once for each of the section of the day he has appeared in.

Example:

Customer 1 visits store A on day 1 at 9:30 AM
Customer 1 visits store A on day 1 at 10:30 PM
Customer 1 visits store B on day 2 at 9:30 AM
Customer 1 visits store B on day 2 at 11:30 AM
Customer 1 visits store B on day 2 at 2:45 PM

Customer 2 visits store A on day 1 at 9:45 AM
Customer 2 visits store B on day 1 at 11:00 AM
Customer 2 visits store B on day 2 at 9:45 AM

Final output of repeat visits:

Store B, Day 1, Section (00:00 - 08:00) : 0 Visitors
Store B, Day 1, Section (08:00 - 16:00) : 2 Visitors
Store B, Day 1, Section (16:00 - 24:00) : 1 Visitors
Store B, Day 2, Section (00:00 - 08:00) : 0 Visitors
Store B, Day 2, Section (08:00 - 16:00) : 2 Visitors
Store B, Day 2, Section (16:00 - 24:00) : 0 Visitors

Is there any way the above kind of query could be done using aggregation framework for MongoDB?

Improve this question

1 Answer 1

Reset to default
1

Yes, this can be done quite simply. It's very similar to the query that I describe in the answer to your previous question, but rather than aggregating by day, you need to aggregate by day-hour-combinations.

To start with, rather than doing a group you will need to project a new part of date where you need to transform your "Time of Visit" field to the appropriate hour form. Let's look at one way to do it:

{$project : { newDate: {
                  y:{$year:"$tov"}, m:{$month:"$tov"}, d:{$dayOfMonth:"$tov"}, 
                  h: { $subtract : 
                          [ { $hour : "$tov" }, 
                            { $mod : [ { $hour : "$tov" }, 8 ] } 
                          ] 
                     }
             },
             customerId:1, locationId:1 
           }
}

As you can see this generates year, month, day and hour but the hour is truncated to mod 8 (so you get 0, 8(am), or 16 aka 4pm.

Next we can do the same steps we did before, but now we are aggregating to a different level of time granularity.

There are other ways of achieving the same thing, you can see some examples of date manipulation on my blog.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

by the way, {$project} is also useful to create nicer format of the file result. For example, you can use {$project:{Time:{$cond:{$eq:["newDate.h",0]}, "Midnight-8:00am", etc] type of expression to turn 0 , 8, 16 into nice looking ranges. :)
Thanks. How would the aggregation change if the sections of the day change to (a) 12 AM - 8 AM (b) 8 AM - 11 AM (c) 11 AM - 1 PM (d) 1 PM - 4 PM (e) 4 PM - 8 PM and (f) 8 PM - 12 PM?
you would have to write a long and tedius looking {$cond: } expression similar to one I use here to pick day of the week string based on number: kamsky.org/1/post/2013/03/… Even intervals make it easier to take advantage of "$mod" operator.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.