I have following stat data stored daily for users.

{
  "_id": {
    "$oid": "638df4e42332386e0e06d322"
  },
  "appointment_count": 1,
  "item_id": 2,
  "item_type": "user",
  "company_id": 5,
  "created_date": "2022-12-05",
  "customer_count": 1,
  "lead_count": 1,
  "door_knocks": 10
}

{
  "_id": {
    "$oid": "638f59a9bf33442a57c3aa99"
  },
  "lead_count": 2,
  "item_id": 2,
  "item_type": "user",
  "company_id": 5,
  "created_date": "2022-12-06",
  "video_viewed": 2,
  "door_knocks": 9
}

And I'm using the following query to get the items by rank

user_stats_2022_12.aggregate([{"$match":{"company_id":5,"created_date":{"$gte":"2022-12-04","$lte":"2022-12-06"}}},{"$setWindowFields":{"partitionBy":"$company_id","sortBy":{"door_knocks":-1},"output":{"item_rank":{"$denseRank":{}},"stat_sum":{"$sum":"$door_knocks"}}}},{"$facet":{"metadata":[{"$count":"total"}],"data":[{"$skip":0},{"$limit":100},{"$sort":{"item_rank":1}}]}}])

It's giving me the rank but with the above data, the record with item_id: 2 are having different rank for same item_id. So I wanted to group them by item_id and then applied rank.

CodePudding user response：

It's a little messy, but here's a playground - https://mongoplayground.net/p/JrJOo4cl9X1.

If you're going to sort by knocks after grouping, I'm assuming that you'll want the sum of door_knocks for a given item_id for this sort.

db.collection.aggregate([
  {
    $match: {
      company_id: 5,
      created_date: {
        "$gte": "2022-12-04",
        "$lte": "2022-12-06"
      }
    }
  },
  {
    $group: {
      _id: {
        item_id: "$item_id",
        company_id: "$company_id"
      },
      docs: {
        $push: "$$ROOT"
      },
      total_door_knocks: {
        $sum: "$door_knocks"
      }
    }
  },
  {
    $setWindowFields: {
      partitionBy: "$company_id",
      sortBy: {
        total_door_knocks: -1
      },
      output: {
        item_rank: {
          "$denseRank": {}
        },
        stat_sum: {
          "$sum": "$total_door_knocks"
        }
      }
    }
  },
  {
    $unwind: "$docs"
  },
  {
    $project: {
      _id: "$docs._id",
      appointment_count: "$docs.appointment_count",
      company_id: "$docs.company_id",
      created_date: "$docs.created_date",
      customer_count: "$docs.customer_count",
      door_knocks: "$docs.door_knocks",
      item_id: "$docs.item_id",
      item_type: "$docs.item_type",
      lead_count: "$docs.lead_count",
      item_rank: 1,
      stat_sum: 1,
      total_door_knocks: 1
    }
  },
  {
    $facet: {
      metadata: [
        {
          "$count": "total"
        }
      ],
      data: [
        {
          "$skip": 0
        },
        {
          "$limit": 100
        },
        {
          "$sort": {
            "item_rank": 1
          }
        }
      ]
    }
  }
])