Sum of number of records per year and month

Question

Sum of number of records per year and month

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

I need to generate a Json file that will feed a graphic. I need to load the following information. I have a form that receives date (dd / mm / yyyy).

My chart shows the number of visits per year x month.

I make the following conference in my bank:

        visitYearGraphic.year = moment(visit.date).format('YYYY');
        visitYearGraphic.month = moment(visit.date).format('MM');
        visitYearGraphics.push(visitYearGraphic);
        var ano13 = 0;
        var ano15 = 0;
        var ano15 = 0;
        _.each(visitYearGraphics, function (visitYearGraphic) {
          if(visitYearGraphic.year=="2015" && visitYearGraphic.month=="01"){
           armazeno qtde de registros
          }

          ... e assim para os demais meses de 2015
        });

But I wanted a method with a clean code to create this function. Because the above way, it will get giant.

node.js mongodb express mongoose

asked by anonymous 01.09.2015 / 19:43

2 answers

1

Pre-aggregation

If you already know the form you want to consult the data, here is a use case that should solve: link

However, if you want to query in different ways, I suggest adding at the time of the query:

Aggregation

I have a similar situation, and I used the MongoDB aggregation , with $ sum bucket . Here's an example:

Considering a "visits" collection with the following documents:

{ "_id" : 1, "url" : "http://a", "date" : ISODate("2014-01-01T08:00:00Z") }
{ "_id" : 2, "url" : "http://b", "date" : ISODate("2014-02-03T09:00:00Z") }
{ "_id" : 3, "url" : "http://a", "date" : ISODate("2014-02-03T09:05:00Z") }
{ "_id" : 4, "url" : "http://a", "date" : ISODate("2014-02-15T08:00:00Z") }
{ "_id" : 5, "url" : "http://b", "date" : ISODate("2014-02-15T09:05:00Z") }

Getting total visits per day

db.visitas.aggregate([
  {
    $group: {
      _id: {
        year: {$year: "$date"},
        month: {$month: "$date"},
        day: {$dayOfMonth: "$date"}
      },
      total: {$sum: 1}
    }
  }
])

The result would be:

{ "_id": { "year": 2014, "month": 1, "day": 1 }, "total": 1 }
{ "_id": { "year": 2014, "month": 2, "day": 3 }, "total": 2 }
{ "_id": { "year": 2014, "month": 2, "day": 15 }, "total": 2 }

Getting total visits per URL in a given period

db.visitas.aggregate([
  {
    $match: {
      date: {$gt: ISODate("2013-01-01T00:00:00Z")}
    },
    $group: {
      _id: "$url",
      total: {$sum: 1}
    }
  }
])

The result would be:

{ "_id": "http://a", "total": 3 }
{ "_id": "http://b", "total": 2 }

See more examples here: link

And other useful operators: link link

In mongoose it would be something like this:

Visitas.aggregate()
  .match({date: {$gt: ISODate("2013-01-01T00:00:00Z")}})
  .group({_id: "$url", total: {$sum: 1}})