通过

$topN

适用对象： MongoDB vCore

$topN累加器运算符根据指定的排序顺序返回组中的前 N 个元素。 它通过允许检索多个顶级元素而不是单个排名最高的项来扩展功能 $top 。

语法

累加器运算符的 $topN 语法如下所示：

{
  $group: {
    _id: <expression>,
    <field>: { 
      $topN: {
        n: <number>,
        sortBy: { <field1>: <sort order>, <field2>: <sort order>, ... },
        output: <expression>
      }
    }
  }
}

参数

DESCRIPTION
n 要返回的元素数。 必须是正整数。
sortBy 使用具有字段名称和排序方向的文档指定排序顺序（1 表示升序，-1 降序）。
output 指定要从前 N 个文档返回的字段或值的表达式。

示例：

让我们了解 stores 数据集中的示例 json 的用法。

{
  "_id": "40d6f4d7-50cd-4929-9a07-0a7a133c2e74",
  "name": "Proseware, Inc. | Home Entertainment Hub - East Linwoodbury",
  "sales": {
    "totalSales": 151864,
    "salesByCategory": [
      {
        "categoryName": "Sound Bars",
        "totalSales": 2120
      },
      {
        "categoryName": "Home Theater Projectors",
        "totalSales": 45004
      },
      {
        "categoryName": "Game Controllers",
        "totalSales": 43522
      },
      {
        "categoryName": "Remote Controls",
        "totalSales": 28946
      },
      {
        "categoryName": "VR Games",
        "totalSales": 32272
      }
    ]
  },
  "promotionEvents": [
    {
      "eventName": "Massive Markdown Mania",
      "discounts": [
        {
          "categoryName": "DVD Players",
          "discountPercentage": 14
        },
        {
          "categoryName": "Media Players",
          "discountPercentage": 21
        },
        {
          "categoryName": "Televisions",
          "discountPercentage": 22
        }
      ]
    }
  ]
}

示例 1：获取每个商店的前三大销售类别

查找每个商店最畅销的三大类别。

db.stores.aggregate([
  { $unwind: "$sales.salesByCategory" },
  {
    $group: {
      _id: "$_id",
      storeName: { $first: "$name" },
      top3Categories: {
        $topN: {
          n: 3,
          sortBy: { "sales.salesByCategory.totalSales": -1 },
          output: {
            categoryName: "$sales.salesByCategory.categoryName",
            totalSales: "$sales.salesByCategory.totalSales"
          }
        }
      }
    }
  }
])

这会生成输出，其中显示每个商店的前三个销售类别：

[
  {
    _id: '8c8f23c9-1893-4ddd-97ad-dd57088058a5',
    storeName: 'Proseware, Inc. | Camera Haven - North Jerroldville',
    top3Categories: [
      { categoryName: 'Waterproof Camcorders', totalSales: 25237 },
      { categoryName: 'Camera Lenses', totalSales: 21189 },
      { categoryName: 'Action Camcorders', totalSales: 19467 }
    ]
  },
  {
    _id: '7f0b0454-e22b-4646-8eb4-32ad5eb48042',
    storeName: 'First Up Consultants | Tool Boutique - Paoloberg',
    top3Categories: [
      { categoryName: 'Drills', totalSales: 40686 },
      { categoryName: 'Screwdrivers', totalSales: 30155 },
      { categoryName: 'Chisels', totalSales: 15762 }
    ]
  },
.
.
.
]

示例 2：获取前两个最近促销事件

根据开始日期查找每个商店的最新促销事件。

db.stores.aggregate([
  { $unwind: "$promotionEvents" },
  {
    $group: {
      _id: "$_id",
      storeName: { $first: "$name" },
      top2RecentPromotions: {
        $topN: {
          n: 2,
          sortBy: { 
            "promotionEvents.promotionalDates.startDate.Year": -1,
            "promotionEvents.promotionalDates.startDate.Month": -1,
            "promotionEvents.promotionalDates.startDate.Day": -1
          },
          output: {
            eventName: "$promotionEvents.eventName",
            startDate: "$promotionEvents.promotionalDates.startDate"
          }
        }
      }
    }
  }
])

这会为每个商店返回两个最新的促销事件：

[
  {
    _id: '4a99546f-a1d2-4e61-ae9f-b8c7c1faf73c',
    storeName: 'Lakeshore Retail | Stationery Nook - West Van',
    top2RecentPromotions: [
      {
        eventName: 'Crazy Markdown Madness',
        startDate: { Year: 2024, Month: 9, Day: 21 }
      },
      {
        eventName: 'Flash Sale Fiesta',
        startDate: { Year: 2024, Month: 6, Day: 23 }
      }
    ]
  },
  {
    _id: 'e0c47a06-4fe0-46b7-a309-8971bbb3978f',
    storeName: 'VanArsdel, Ltd. | Baby Products Bargains - Elainamouth',
    top2RecentPromotions: [
      {
        eventName: 'Crazy Deal Days',
        startDate: { Year: 2024, Month: 9, Day: 21 }
      }
    ]
  },
.
.
.
]

示例 3：按类别获取前五大最高折扣

查找每个商店的所有促销活动中具有最高折扣百分比的前五个类别。

db.stores.aggregate([
  { $unwind: "$promotionEvents" },
  { $unwind: "$promotionEvents.discounts" },
  {
    $group: {
      _id: "$_id",
      storeName: { $first: "$name" },
      top5Discounts: {
        $topN: {
          n: 5,
          sortBy: { "promotionEvents.discounts.discountPercentage": -1 },
          output: {
            categoryName: "$promotionEvents.discounts.categoryName",
            discountPercentage: "$promotionEvents.discounts.discountPercentage",
            eventName: "$promotionEvents.eventName"
          }
        }
      }
    }
  }
])

这显示了每个商店最高折扣百分比的前五个类别：

[
  {
    _id: '4a99546f-a1d2-4e61-ae9f-b8c7c1faf73c',
    storeName: 'Lakeshore Retail | Stationery Nook - West Van',
    top5Discounts: [
      {
        categoryName: 'Rulers',
        discountPercentage: 24,
        eventName: 'Markdown Madness'
      },
      {
        categoryName: 'Notebooks',
        discountPercentage: 21,
        eventName: 'Markdown Madness'
      },
      {
        categoryName: 'Paper Clips',
        discountPercentage: 17,
        eventName: 'Flash Sale Fiesta'
      },
      {
        categoryName: 'Pencils',
        discountPercentage: 15,
        eventName: 'Bargain Blitz Bash'
      },
      {
        categoryName: 'Erasers',
        discountPercentage: 14,
        eventName: 'Crazy Markdown Madness'
      }
    ]
  },
.
.
.
]

相关内容