按小时分组并计算MongoDB [英] Group by hour and count MongoDB

问题描述

我正在一个项目中处理自行车共享服务的数据.每次旅行都有以下信息

I'm on a project working with data of a bike-sharing service. Each trip has the following info

> db.bikes.find({bike:9990}).pretty()
{
    "_id" : ObjectId("5bb59fd8e9fb374bf0cd5c1c"),
    "gender" : "M",
    "userAge" : 49,
    "bike" : 9990,
    "depStation" : 150,
    "depDate" : "01/08/2018",
    "depHour" : "0:00:13",
    "arrvStation" : 179,
    "arrvDate" : "01/08/2018",
    "arrvHour" : "0:23:38"
}

如何对一天中的每个小时进行分组，并计算该小时内的旅行次数? 我正在尝试使用此查询

How do I group for each hour of the day and count the number of trips made in that specific hour? I'm trying with this query

db.bikes.aggregate(
  { 
     $group:{_id:{$hour: "$depHour"}, trips:{$sum: 1}}
  }
)

但是它抛出了这个错误

    "ok" : 0,
    "errmsg" : "can't convert from BSON type string to Date",
    "code" : 16006,
    "codeName" : "Location16006"

推荐答案

depDate和depHour字段都是分别表示日期和小时的字符串值，因此无需使用日期运算符可将字段转换为日期对象，这是您所需要的是使用 $substrCP提取小时部分 ，然后将其直接用作$group _id中的表达式，如下所示:

The depDate and depHour fields are all string values that denote the day and the hour respectively so there is no need to use the date operators to convert the fields to date objects, all you need is to extract the hour part using $substrCP and then use them directly as expressions in your $group _id as:

db.bikes.aggregate([
    { '$group': {
        '_id': {
            'day': '$depDate',
            'hour': { '$substrCP': [ '$depHour', 0, 2 ] }
        }, 
        'trips': { '$sum': 1 }
    } }
])

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