时间戳

首页 > 解决方案 > 需要查找每周的登录次数

mongodb - 需要查找每周的登录次数

问题描述

我有两个系列。我正在尝试根据 LoggedTime 字段查找登录次数。我想获得过去五周内每周的登录次数。

我有两个系列。

集合 1：角色
字段：角色、用户名、登录时间

集合 2：mysite
字段：用户名，用户电子邮件，名称

在集合 1 中，例如：

{
'Role' :"admin"
'UserName' : "abc.efg",
'loggedTime' : 2020-06-24T18:12:03.455Z,
}

在集合 2 中，例如：

{
'userName' : "abc Mr, efg" ,
'userEmail' : "abc.efg@company.com" ,
'name' : 'orgname'
}

有什么方法可以让我们拥有一个新集合，其中包含过去 5 周每个组织名称每周登录次数（基于loggedTime）的计数？

基本上试图获得像集合3这样的结果：

{
name :'orgname',
'Logins current week':'no of logins'
'Logins previous week' : 'no of logins'
'Logins 3rd week' :'no of logins'
'Logins 4th week' :'no of logins'
'Logins 5th week': 'no of logins'
}

标签： mongodbmongodb-query

解决方案

您需要使用各种聚合管道阶段来转换数据。

MongoDB 的聚合框架以数据处理管道的概念为蓝本。文档进入一个多阶段管道，将文档转换为聚合结果。

您可以使用聚合管道中的以下阶段和运算符来实现您的用例：

  1. 执行$lookup以加入两个集合
  2. 使用$group，通过基于周和组织名称的分组来查找登录数
  3. $subtract用于获取当前周与可用周之间的周差loggedTime
  4. $facet用于根据过去 5 周的每周进行分组
  5. 最后，将数据转换为所需的格式

请参阅工作示例：MongoDB Playground

db.Role.aggregate([
  {
    "$lookup": {
      "from": "mysite",
      let: {
        "userName": "$UserName"
      },
      "pipeline": [
        {
          $match: {
            "$expr": {
              "$ne": [
                {
                  "$indexOfCP": [
                    "$userEmail",
                    "$$userName"
                  ]
                },
                -1
              ]
            }
          }
        }
      ],
      "as": "mysite"
    }
  },
  {
    "$unwind": "$mysite"
  },
  {
    $group: {
      _id: {
        week: {
          $week: "$loggedTime"
        },
        org: "$mysite.name"
      },
      documentCount: {
        $sum: 1
      }
    }
  },
  {
    "$addFields": {
      "currentWeek": {
        $week: "$$NOW"
      }
    }
  },
  {
    "$addFields": {
      dateDifference: {
        $subtract: [
          "$currentWeek",
          "$_id.week"
        ]
      }
    }
  },
  {
    $facet: {
      current: [
        {
          $match: {
            dateDifference: 0
          }
        },
        {
          $project: {
            "name": "$_id.org",
            _id: 0,
            "Loginscurrentweek": "$documentCount"
          }
        }
      ],
      previous: [
        {
          $match: {
            dateDifference: 1
          }
        },
        {
          $project: {
            "name": "$_id.org",
            _id: 0,
            "Loginspreviousweek": "$documentCount"
          }
        }
      ],
      third: [
        {
          $match: {
            dateDifference: 2
          }
        },
        {
          $project: {
            "name": "$_id.org",
            _id: 0,
            "Logins3rdweek": "$documentCount"
          }
        }
      ],
      fourth: [
        {
          $match: {
            dateDifference: 3
          }
        },
        {
          $project: {
            "name": "$_id.org",
            _id: 0,
            "Logins4thweek": "$documentCount"
          }
        }
      ],
      fifth: [
        {
          $match: {
            dateDifference: 4
          }
        },
        {
          $project: {
            "name": "$_id.org",
            _id: 0,
            "Logins5thweek": "$documentCount"
          }
        }
      ]
    }
  },
  {
    $project: {
      "givenWeeks": {
        "$concatArrays": [
          "$current",
          "$fifth",
          "$fourth",
          "$third",
          "$previous"
        ]
      }
    }
  },
  {
    $unwind: "$givenWeeks"
  },
  {
    $group: {
      _id: "$givenWeeks.name",
      val: {
        $push: {
          "Loginscurrentweek": "$givenWeeks.Loginscurrentweek",
          "Loginspreviousweek": "$givenWeeks.Loginspreviousweek",
          "Logins3rdweek": "$givenWeeks.Logins3rdweek",
          "Logins4thweek": "$givenWeeks.Logins4thweek",
          "Logins5thweek": "$givenWeeks.Logins5thweek",
          
        }
      }
    }
  },
  {
    $project: {
      "name": "$_id",
      _id: 0,
      val: {
        "$mergeObjects": "$val"
      }
    }
  },
  {
    "$project": {
      name: 1,
      "Logins current week": "$val.Loginscurrentweek",
      "Logins previous week": "$val.Loginspreviousweek",
      "Logins 3rd week": "$val.Logins3rdweek",
      "Logins 4th week": "$val.Logins4thweek",
      "Logins 5th week": "$val.Logins5thweek"
    }
  }
])

推荐阅读