Sum number of records per year and month

Asked

Viewed 250 times

1

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

My chart shows the number of registered 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 way above, it will become giant.

node.js mongodb express mongoose

  • What object format does this graph expect?

  • Can you explain or give an example of what you mean by "go giant"? I still haven’t figured out what data you want to use and how.

  • [{"_id":"55e5e6440e5a7a3017552acc","Qtde":"4","Month":"02","year".

  • This is the return of the bank, right? You have to turn it into an object that the graph understands, right?

  • Sergio, already there is the example, if I count using if(visitYearGraphic.year="2015" && visitYearGraphic.Month="01"). It will be huge. Impossible to do, at the moment my record is small, but in 2 years will not be more!

  • @Dontvotemedown, yes, this is the return of the bank. However I did it using that formula above, comparing month and year. I want something different from this, because suppose I am 6 years, 12 months for every 6 years and n records.

  • @Dontvotemedown, my chart already understands this record. The problem is in the way I am using to receive this data which is not feasible. As I said, my record is small, in the future it will be no more. I can not buy year by year and month by month of each year. I wanted a better way to do it.

  • But what you’re going to do within that comparison(do if), that’s not very clear, I think.

  • So I’m comparing the amount of records per year and month. Ex: Year 2015 and month January, I check how many records you have and store in the variable Qtde. However, I do this for each because there are few records, but in 2 years will be many. I want a better way to build the algorithm, without having to do it like this, one by one.

Show 4 more comments

2 answers

1


If I understand correctly you want a collection to count the accesses, right?

var acessos = {};

_.each(visitYearGraphics, function (visitYearGraphic) {
    var key = visitYearGraphic.year + "." + visitYearGraphic.month;

    if (!acessos.hasOwnProperty(key)) {
        acessos[key] = 0;
    }

    acessos[key]+= Number(visitYearGraphic.qtde);
});

The above code will result in the following object, for example.:

{
    "2015.01": 4,
    "2015.02": 10,
    ...
}

Serves like this ? Then you can iterate on this object too.

  • 1

    Yes, thank you. I’ll work on it.

1

Pre-aggregation

If you already know the way you want to query the data, here is a use case that should solve: http://docs.mongodb.org/ecosystem/use-cases/pre-aggregated-reports/

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

Aggregation

I have a similar situation, and I used aggregation of Mongodb itself, with the accumulator $sum. Take an example:

Considering a collection "visits" 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 the total number of 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: http://docs.mongodb.org/manual/applications/aggregation/

And other useful operators: http://docs.mongodb.org/manual/reference/operator/aggregation/ http://docs.mongodb.org/manual/reference/operator/aggregation-group/

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}})

More about aggregation in Mongoose: http://mongoosejs.com/docs/api.html#aggregate_Aggregate-group

Browser other questions tagged node.js mongodb express mongoose

You are not signed in. Login or sign up in order to post.