$bottomN(聚合累加器)
定义
语法
{ $bottomN: { n: <expression>, sortBy: { <field1>: <sort order>, <field2>: <sort order> ... }, output: <expression> } }
行为
Null 和缺失值
$bottomN
不筛选空值。$bottomN
将缺失值转换为 null 并保留在输出中。
db.aggregate( [ { $documents: [ { playerId: "PlayerA", gameId: "G1", score: 1 }, { playerId: "PlayerB", gameId: "G1", score: 2 }, { playerId: "PlayerC", gameId: "G1", score: 3 }, { playerId: "PlayerD", gameId: "G1"}, { playerId: "PlayerE", gameId: "G1", score: null } ] }, { $group: { _id: "$gameId", playerId: { $bottomN: { output: [ "$playerId", "$score" ], sortBy: { "score": -1 }, n: 3 } } } } ] )
在本例中:
$documents
创建包含玩家分数的字面文档。$group
按gameId
对文档进行分组。此示例只有一个gameId
、G1
。PlayerD
分数缺失,且PlayerE
的score
为空。这些值都被视为空值。playerId
和score
字段指定为output : ["$playerId"," $score"]
,并以数组值形式返回。由于
sortBy: { "score" : -1 }
,空值被排序到返回的playerId
数组的末尾。
[ { _id: "G1", playerId: [ [ "PlayerA", 1 ], [ "PlayerD", null ], [ "PlayerE", null ] ] } ]
BSON 数据类型排序
对不同类型进行排序时,将使用BSON 数据类型的顺序来确定顺序。 例如,考虑一个其值由字符串和数字组成的collection。
在升序排序中,字符串值排在数值之后。
在降序排序中,字符串值排在数值之前。
db.aggregate( [ { $documents: [ { playerId: "PlayerA", gameId: "G1", score: 1 }, { playerId: "PlayerB", gameId: "G1", score: "2" }, { playerId: "PlayerC", gameId: "G1", score: "" } ] }, { $group: { _id: "$gameId", playerId: { $bottomN: { output: ["$playerId","$score"], sortBy: {"score": -1}, n: 3 } } } } ] )
在本例中:
PlayerA
有一个整数分数。PlayerB
有一个字符串"2"
分数。PlayerC
有一个空字符串分数。
由于按降序{ "score" : -1 }
排序,因此字符串字面值会排序在PlayerA
的数值之前:
[ { _id: "G1", playerId: [ [ "PlayerB", "2" ], [ "PlayerC", "" ], [ "PlayerA", 1 ] ] } ]
限制
窗口函数和聚合表达式支持
$bottomN
不支持作为聚合表达式。
$bottomN
支持作为 window operator
。
内存限制注意事项
$bottomN
聚合管道中的群组受100 MB 限制管道限制的约束。 如果单个群组超过此限制,则聚合失败并显示错误。
示例
请考虑包含以下文档的 gamescores
集合:
db.gamescores.insertMany([ { playerId: "PlayerA", gameId: "G1", score: 31 }, { playerId: "PlayerB", gameId: "G1", score: 33 }, { playerId: "PlayerC", gameId: "G1", score: 99 }, { playerId: "PlayerD", gameId: "G1", score: 1 }, { playerId: "PlayerA", gameId: "G2", score: 10 }, { playerId: "PlayerB", gameId: "G2", score: 14 }, { playerId: "PlayerC", gameId: "G2", score: 66 }, { playerId: "PlayerD", gameId: "G2", score: 80 } ])
找到三个最低值 Scores
您可以使用$bottomN
累加器查找单个游戏中得分最低的玩家。
db.gamescores.aggregate( [ { $match : { gameId : "G1" } }, { $group: { _id: "$gameId", playerId: { $bottomN: { output: ["$playerId", "$score"], sortBy: { "score": -1 }, n:3 } } } } ] )
示例管道:
使用
$match
筛选单个gameId
的结果。在本例中为G1
。使用
$group
按gameId
对结果分组。本例中为G1
。使用 sort by
{ "score": -1 }
按降序对结果进行排序。使用
output : ["$playerId"," $score"]
指定从$bottomN
输出的字段。使用
$bottomN
返回具有n : 3
的G1
游戏中score
最低的最后三个文档。
操作返回以下结果:
[ { _id: "G1", playerId: [ [ "PlayerB", 33 ], [ "PlayerA", 31 ], [ "PlayerD", 1 ] ] } ]
与该查询等效的 SQL 是:
SELECT T3.GAMEID,T3.PLAYERID,T3.SCORE FROM GAMESCORES AS GS JOIN (SELECT TOP 3 GAMEID,PLAYERID,SCORE FROM GAMESCORES WHERE GAMEID = "G1" ORDER BY SCORE) AS T3 ON GS.GAMEID = T3.GAMEID GROUP BY T3.GAMEID,T3.PLAYERID,T3.SCORE ORDER BY T3.SCORE DESC
在多个游戏中查找三个得分最低的文档
您可以使用$bottomN
累加器查找每场游戏中得分最低的玩家。
db.gamescores.aggregate( [ { $group: { _id: "$gameId", playerId: { $bottomN: { output: [ "$playerId","$score" ], sortBy: { "score": -1 }, n: 3 } } } } ] )
示例管道:
使用
$group
按gameId
对结果进行分组。使用
output : ["$playerId", "$score"]
指定从$bottomN
输出的字段。使用 sort by
{ "score": -1 }
按降序对结果进行排序。使用
$bottomN
为每个具有n: 3
的游戏返回score
最低的最后三个文档。
操作返回以下结果:
[ { _id: "G1", playerId: [ [ "PlayerB", 33 ], [ "PlayerA", 31 ], [ "PlayerD", 1 ] ] }, { _id: "G2", playerId: [ [ "PlayerC", 66 ], [ "PlayerB", 14 ], [ "PlayerA", 10 ] ] } ]
与该查询等效的 SQL 是:
SELECT PLAYERID,GAMEID,SCORE FROM( SELECT ROW_NUMBER() OVER (PARTITION BY GAMEID ORDER BY SCORE DESC) AS GAMERANK, GAMEID,PLAYERID,SCORE FROM GAMESCORES ) AS T WHERE GAMERANK >= 2 ORDER BY GAMEID
根据n
的群组密钥计算$group
您还可以动态分配n
的值。 在此示例中,对gameId
字段使用了$cond
表达式。
db.gamescores.aggregate([ { $group: { _id: {"gameId": "$gameId"}, gamescores: { $bottomN: { output: "$score", n: { $cond: { if: {$eq: ["$gameId","G2"] }, then: 1, else: 3 } }, sortBy: { "score": -1 } } } } } ] )
示例管道:
使用
$group
按gameId
对结果进行分组。使用
output : "$score"
指定从$bottomN
输出的字段。如果
gameId
为G2
,则n
为 1,否则n
为 3。使用 sort by
{ "score": -1 }
按降序对结果进行排序。
操作返回以下结果:
[ { _id: { gameId: "G2" }, gamescores: [ 10 ] }, { _id: { gameId: "G1" }, gamescores: [ 33, 31, 1 ] } ]