时间戳

首页 > 解决方案 > 来自 mongo 查询的每周数据

mongodb - 来自 mongo 查询的每周数据

问题描述

对每日唯一用户使用以下查询，我需要一周的唯一计数 如何在此查询中使用 $week 运算符来获取唯一的每周计数，在下面添加了架构对象集合，如何修改每日唯一记录计数的查询以获得每周唯一计数记录。

db.custom.aggregate([
    {
        $match: {
            "loginLogoutTime": {
                "$gte": ISODate("2020-04-10T14:30:00.000-0400"),
                "$lt": ISODate("2020-04-11T14:32:00.000-0400")
            }
        }
    },
    {
        $group: {
            _id: {Dates: {$dateToString: {format: "%Y-%m-%d", date: "$loginLogoutTime"}}, Role: "$customROLE"},
            uniqueCount: {$addToSet: "$id"}
        }
    },
    {
        $project: {
            "_id": NumberInt(0),
            "Dates": "$_id.Dates",
            "ROLE": "$_id.Role",
            "TOTALUSERS": {$size: "$uniqueCount"}
        }
    }
])

我当前的输出：

[
    {
        "Dates" : "2020-05-11",
        "ROLE" : "CS_GMR",
        "TOTALUSERS" : 3
    },
    {
        "Dates" : "2020-05-11",
        "ROLE" : "PS_MGR",
        "TOTALUSERS" : 2
    },
    {
        "Dates" : "2020-05-11",
        "ROLE" : "SE_PECH",
        "TOTALUSERS" : 9
    },
    {
        "Dates" : "2020-05-11",
        "ROLE" : "NS_IS",
        "TOTALUSERS" : 130
    }
]

我的 Schema 对象如：

/* 1 */
{
    "_id" : 55,
    "id" : "dm4955",
    "host" : "iefs-las.web.lh.com",
    "Role" : "PS_MGR",
    "event" : "TIMEOUT",
    "timeoutTimeStamp" : ISODate("2018-10-26T14:33:13.000Z"),
    "lastupdatedTimeStamp" : ISODate("2018-10-26T18:33:14.663Z"),
    "recordPublishIndicator" : "Y",
    "__v" : 0
}

/* 2 */
{
    "_id" : 38,
    "attUid" : "mo441b",
    "host" : "iefs-las.web.lh.com",
    "Role" : "CS_GMR",
    "event" : "LOGIN",
    "loginLogoutTime" : ISODate("2018-10-26T14:41:34.000Z"),
    "lastupdatedTimeStamp" : ISODate("2018-10-26T14:41:35.220Z"),
    "recordPublishIndicator" : "Y",
    "__v" : 0
}

标签： mongodbmongodb-query

解决方案

只需在您的阶段替换$dateToString为：$week$group

db.custom.aggregate([
    {
        $match: {
            "loginLogoutTime": {
                "$gte": ISODate("2020-04-10T14:30:00.000-0400"),
                "$lt": ISODate("2020-04-11T14:32:00.000-0400")
            }
        }
    },
    {
        $group: {
            _id: {week: {$week: "$loginLogoutTime"}, Role: "$customROLE"},
            uniqueCount: {$addToSet: "$id"} ,
            Dates: {$addToSet: {$dateToString: {format: "%Y-%m-%d", date: "$loginLogoutTime"}}}
        }
    },
    {
        $project: {
            "_id": NumberInt(0),
            "week": "$_id.week",
            "Dates": "$Dates",
            "ROLE": "$_id.Role",
            "TOTALUSERS": {$size: "$uniqueCount"}
        }
    }
])

推荐阅读