Mongoid按日期分组 [英] Mongoid Grouping by date

问题描述

这是我所拥有的文件类型

this is the kind of document I have:

{
  event_type: 'click',
  created_at: '2015-02-15 10:00:00',
  user_id: 100
}

我需要使用event_type = click每天对唯一身份用户进行汇总. 要获得类似的东西:

I need to sum unique users per day with event_type = click. To get something like:

{
  count: 320,
  date: '2015-02-15'
}

{
  count: 451,
  date: '2015-02-16'
}

(请注意日期格式)

我尝试了许多在SO上发现的不同东西，这几乎对我有用: 与Mongoid按日期分组的最佳方法 我只是不知道如何修改它以完全满足我的需求.

I've tried many different things I found on SO, this one almost worked for me: Best way to group by date with Mongoid I just couldn't figure out how to modify it to do exactly what I need.

这是原始查询，几乎可以完成我想用Mongoid实现的工作:

This is the raw query that's almost doing what I want to achieve with Mongoid:

db.events.aggregate([
  {$match: {event_type: 'click', created_at: {
    $gte: ISODate('2015-01-01T00:00:00.000Z'),
    $lt: ISODate('2016-01-01T00:00:00.000Z')
  }}},
  {$group: {
      _id: {user_id: '$user_id', account_id: '$account_id', created_at: {
           day: { $dayOfMonth: '$created_at' },
           month: { $month: '$created_at' },
           year: { $year: '$created_at'}
       }},
      'count': {'$sum': 1}
      }
  },
  {$sort: {created_at: -1}},
])

有什么建议吗?

推荐答案

在mongo 2.8

in mongo 2.8 $dateToString provide facility to convert ISO date to string format so below query may solve your problem

    db.collectionName.aggregate({
    "$match": {
    "event_type": "click"
    }
}, {
    "$project": {
    "yearMonthDay": {
        "$dateToString": {
            "format": "%Y-%m-%d",
            "date": "$created_at"
        }
    },
    "user_id": "$user_id"
    }
}, {
    "$group": {
    "_id": {
        "user_id": "$user_id",
        "date": "$yearMonthDay"
    }
    }
}, {
    "$group": {
    "_id": "$_id.date",
    "count": {
        "$sum": "$_id.user_id"
    }
    }
}, {
    "$project": {
    "date": "$_id",
    "count": "$count",
    "_id": 0
    }
})

在上面的查询中，第一组创建日期和用户名的组，第二组再次根据日期对user_id的总和进行计数

In above query first group create group of date wise and user_id and second group again count sum of user_id according to date

这篇关于Mongoid按日期分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！