MongoDB - 聚合阶段 $group 的使用

在 MongoDB 中，$group 是聚合管道中的一个阶段，用于根据指定的条件对文档进行分组。

{$group: {_id: <expression>,  // 分组的依据，可以是字段名或表达式<field1>: { <accumulator1> : <expression1> },  // 对字段进行聚合操作<field2>: { <accumulator2> : <expression2> },...}
}

_id 字段必填，指定了分组的键。如果指定的 _id 值为空值或任何其他常量值，$group 阶段将返回聚合所有输入文档值的单个文档。

<field1> 是要输出的字段名，可以是现有字段或新的计算字段。
<accumulator1> 是聚合操作符，用于对分组内的文档进行计算。
<expression1> 是要应用于每个分组的表达式，用于计算聚合操作的结果。

以下是一些常用的聚合操作符：

$sum：计算指定字段的总和。
$avg：计算指定字段的平均值。
$min：计算指定字段的最小值。
$max：计算指定字段的最大值。
$push：将指定字段的值添加到数组中。
$addToSet：将指定字段的唯一值添加到集合中。
$first：返回每个分组中指定字段的第一个值。
$last：返回每个分组中指定字段的最后一个值。

1. 构造测试数据

db.sales.drop()db.sales.insertMany([
{"_id": 1,"item": "abc","price": Decimal128("10"),"quantity": Int32("2"),"date": "2014-03-01"
},
{"_id": 2,"item": "jkl","price": Decimal128("20"),"quantity": Int32("1"),"date": "2014-03-01"
},
{"_id": 3,"item": "xyz","price": Decimal128("5"),"quantity": Int32("10"),"date": "2014-03-15"
},
{"_id": 4,"item": "xyz","price": Decimal128("5"),"quantity": Int32("20"),"date": "2014-04-04"
},
{"_id": 5,"item": "abc","price": Decimal128("10"),"quantity": Int32("10"),"date": "2014-04-04"
},
{"_id": 6,"item": "def","price": Decimal128("7.5"),"quantity": Int32("5"),"date": "2015-06-04"
},
{"_id": 7,"item": "def","price": Decimal128("7.5"),"quantity": Int32("10"),"date": "2015-09-10"
},
{"_id": 8,"item": "abc","price": Decimal128("10"),"quantity": Int32("5"),"date": "2016-02-06"
}
]) 

@Data
@AllArgsConstructor
@NoArgsConstructor
@Document(collection = "sales")
public class Sales {private ObjectId id;private String item;private Decimal128 price;private int quantity;private Date date;
}

1. 示例 1

按 item 字段对文档进行分组

db.sales.aggregate([{ $group: { _id: "$item" } }
])

{ "_id" : "abc" }
{ "_id" : "jkl" }
{ "_id" : "def" }
{ "_id" : "xyz" }

SpringBoot 整合 MongoDB实现上述操作：

@Data
@Document(collection = "sales")
public class Sales {@Idprivate String id;private String item;private Decimal128 price;private int quantity;private Date date;
}@Data
public class AggregationResult {private String id;
}

@Test
public void aggregateTest() {GroupOperation groupOperation = Aggregation.group("item");Aggregation aggregation = Aggregation.newAggregation(groupOperation);// aggregate():参数的顺序为聚合管道的定义、输入类型、输出类型AggregationResults<AggregationResult> results= mongoTemplate.aggregate(aggregation, Sales.class, AggregationResult.class);List<AggregationResult> mappedResults = results.getMappedResults();mappedResults.forEach(System.out::println);//AggregationResult(id=xyz)//AggregationResult(id=jkl)//AggregationResult(id=def)//AggregationResult(id=abc)
}

2. 示例2

$group 阶段按 item 对文档进行分组，并计算返回每个一项的总销售额totalSaleAmount

db.sales.aggregate([// First Stage{$group :{_id : "$item",totalSaleAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } }}}])

// 1
{"_id": "xyz","totalSaleAmount": Decimal128("150")
}// 2
{"_id": "jkl","totalSaleAmount": Decimal128("20")
}// 3
{"_id": "def","totalSaleAmount": Decimal128("112.5")
}// 4
{"_id": "abc","totalSaleAmount": Decimal128("170")
}

SpringBoot 整合 MongoDB实现上述操作：

@Data
@Document(collection = "sales")
public class Sales {@Idprivate String id;private String item;private Decimal128 price;private int quantity;private Date date;
}@Data
public class AggregationResult {private String id;private Decimal128 totalSaleAmount;
}

@Test
public void aggregateTest() {GroupOperation groupOperation = Aggregation.group("item").sum(ArithmeticOperators.Multiply.valueOf("price").multiplyBy("quantity")).as("totalSaleAmount");Aggregation aggregation = Aggregation.newAggregation(groupOperation);// aggregate():参数的顺序为聚合管道的定义、输入类型、输出类型AggregationResults<AggregationResult> results= mongoTemplate.aggregate(aggregation, Sales.class, AggregationResult.class);List<AggregationResult> mappedResults = results.getMappedResults();mappedResults.forEach(System.out::println);//AggregationResult(id=xyz, totalSaleAmount=150)//AggregationResult(id=jkl, totalSaleAmount=20)//AggregationResult(id=def, totalSaleAmount=112.5)//AggregationResult(id=abc, totalSaleAmount=170)
}

3. 示例3

第一个阶段：

$group 阶段按 item 对文档进行分组，以检索非重复的项值。此阶段返回每一项的 totalSaleAmount。

第二个阶段：

$match 阶段会对生成的文档进行筛选，从而只返回 totalSaleAmount 大于或等于 100 的项目。

db.sales.aggregate([// First Stage{$group :{_id : "$item",totalSaleAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } }}},// Second Stage{$match: { "totalSaleAmount": { $gte: 100 } }}])

// 1
{"_id": "xyz","totalSaleAmount": Decimal128("150")
}// 2
{"_id": "def","totalSaleAmount": Decimal128("112.5")
}// 3
{"_id": "abc","totalSaleAmount": Decimal128("170")
}

这个聚合操作相当于以下 SQL 语句：

SELECT item,Sum(( price * quantity )) AS totalSaleAmount
FROM   sales
GROUP  BY item
HAVING totalSaleAmount >= 100

SpringBoot 整合 MongoDB实现上述操作：

@Data
public class AggregationResult {private String id;private Decimal128 totalSaleAmount;
}

@Test
public void aggregateTest() {// 第一个阶段GroupOperation groupOperation = Aggregation.group("item").sum(ArithmeticOperators.Multiply.valueOf("price").multiplyBy("quantity")).as("totalSaleAmount");// 第二个阶段MatchOperation matchOperation = Aggregation.match(Criteria.where("totalSaleAmount").gte(100));Aggregation aggregation = Aggregation.newAggregation(groupOperation,matchOperation);// aggregate():参数的顺序为聚合管道的定义、输入类型、输出类型AggregationResults<AggregationResult> results= mongoTemplate.aggregate(aggregation, Sales.class, AggregationResult.class);List<AggregationResult> mappedResults = results.getMappedResults();mappedResults.forEach(System.out::println);//AggregationResult(id=xyz, totalSaleAmount=150)//AggregationResult(id=def, totalSaleAmount=112.5)//AggregationResult(id=abc, totalSaleAmount=170)
}

4. 示例4

计算 2014 年每一天的总销售额、平均销售数量和销售数量：

第一个阶段：

$match 阶段会对这些文档进行筛选，仅将从 2014 年开始的文档传递到下一阶段。

第二个阶段：

$group 阶段按日期对文档分组，并计算每组文档的总销售金额、平均数量和总数。

第三个阶段：

$sort 阶段按每个组的总销售金额对结果进行降序排序。

db.sales.aggregate([// First Stage{$match : { "date": { $gte: "2014-01-01", $lt: "2015-01-01" } }},// Second Stage{$group : {_id : "$date",totalSaleAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } },averageQuantity: { $avg: "$quantity" },count: { $sum: 1 }}},// Third Stage{$sort : { totalSaleAmount: -1 }}])

// 1
{"_id": "2014-04-04","totalSaleAmount": Decimal128("200"),"averageQuantity": 15,"count": 2
}// 2
{"_id": "2014-03-15","totalSaleAmount": Decimal128("50"),"averageQuantity": 10,"count": 1
}// 3
{"_id": "2014-03-01","totalSaleAmount": Decimal128("40"),"averageQuantity": 1.5,"count": 2
}

这个聚合操作相当于以下 SQL 语句：

SELECT date,Sum(( price * quantity )) AS totalSaleAmount,Avg(quantity)             AS averageQuantity,Count(*)                  AS Count
FROM   sales
WHERE  date >= '01/01/2014' AND date < '01/01/2015'
GROUP  BY date
ORDER  BY totalSaleAmount DESC

SpringBoot 整合 MongoDB实现上述操作：

@Data
public class AggregationResult {private String id;private Decimal128 totalSaleAmount;private Double averageQuantity;private Integer count;
}

@Test
public void aggregateTest() {// 第一个阶段MatchOperation matchOperation = Aggregation.match(Criteria.where("date").gte("2014-01-01").lte("2015-01-01"));// 第二个阶段GroupOperation groupOperation = Aggregation.group("date").sum(ArithmeticOperators.Multiply.valueOf("price").multiplyBy("quantity")).as("totalSaleAmount").avg("quantity").as("averageQuantity").count().as("count");// 第三个阶段SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.DESC, "totalSaleAmount"));// 组合上面的3个阶段Aggregation aggregation = Aggregation.newAggregation(matchOperation,groupOperation,sortOperation);AggregationResults<AggregationResult> results= mongoTemplate.aggregate(aggregation, Sales.class, AggregationResult.class);List<AggregationResult> mappedResults = results.getMappedResults();mappedResults.forEach(System.out::println);//AggregationResult(id=2014-04-04, totalSaleAmount=200, averageQuantity=15.0, count=2)//AggregationResult(id=2014-03-15, totalSaleAmount=50, averageQuantity=10.0, count=1)//AggregationResult(id=2014-03-01, totalSaleAmount=40, averageQuantity=1.5, count=2)
}

5. 示例5

聚合操作指定了 null 的 _id 组，计算集合中所有文档的总销售额、平均数量和计数。

db.sales.aggregate([{$group : {_id : null,totalSaleAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } },averageQuantity: { $avg: "$quantity" },count: { $sum: 1 }}}])

{"_id" : null,"totalSaleAmount" : Decimal128("452.5"),"averageQuantity" : 7.875,"count" : 8
}

这个聚合操作相当于以下 SQL 语句：

SELECT Sum(price * quantity) AS totalSaleAmount,Avg(quantity)         AS averageQuantity,Count(*)              AS Count
FROM   sales

SpringBoot 整合 MongoDB实现上述操作：

@Data
public class AggregationResult {private String id;private Decimal128 totalSaleAmount;private Double averageQuantity;private Integer count;
}

@Test
public void aggregateTest() {GroupOperation groupOperation = Aggregation.group().sum(ArithmeticOperators.Multiply.valueOf("price").multiplyBy("quantity")).as("totalSaleAmount").avg("quantity").as("averageQuantity").count().as("count");// 组合上面的3个阶段Aggregation aggregation = Aggregation.newAggregation(groupOperation);AggregationResults<AggregationResult> results= mongoTemplate.aggregate(aggregation, Sales.class, AggregationResult.class);List<AggregationResult> mappedResults = results.getMappedResults();mappedResults.forEach(System.out::println);//AggregationResult(id=null, totalSaleAmount=452.5, averageQuantity=7.875, count=8)
}

2. 构造测试数据

db.oredrs.insertMany([{ orderId: 1, customerId: 1, amount: 100 },{ orderId: 2, customerId: 2, amount: 200 },{ orderId: 3, customerId: 1, amount: 150 },{ orderId: 4, customerId: 3, amount: 300 },{ orderId: 5, customerId: 2, amount: 250 }
])

@Data
@Document(collection = "orders")
public class Order {private int orderId;private int customerId;private double amount;
}

1. 示例1

计算每个客户的订单总额：group阶段根据 customerId 字段对订单文档进行分组，然后使用 sum 操作符计算每个客户的订单总额。

db.orders.aggregate([{$group: {_id: "$customerId",totalAmount: { $sum: "$amount" }}}
])

// 1
{"_id": 3,"totalAmount": 600
}// 2
{"_id": 2,"totalAmount": 900
}// 3
{"_id": 1,"totalAmount": 500
}

SpringBoot 整合 MongoDB实现上述操作：

@Data
public class AggregationResult {private String id;private Integer totalAmount;
}

@Test
public void aggregateTest() {GroupOperation groupOperation = Aggregation.group("customerId").sum("amount").as("totalAmount");// 组合上面的3个阶段Aggregation aggregation = Aggregation.newAggregation(groupOperation);AggregationResults<AggregationResult> results= mongoTemplate.aggregate(aggregation, Order.class, AggregationResult.class);List<AggregationResult> mappedResults = results.getMappedResults();mappedResults.forEach(System.out::println);//AggregationResult(id=3.0, totalAmount=300)//AggregationResult(id=2.0, totalAmount=450)//AggregationResult(id=1.0, totalAmount=250)
}

2. 示例2

计算每个客户的订单数量和平均订单金额：group阶段根据 customerId 字段对订单文档进行分组，然后使用 $sum 操作符计算每个客户的订单数量，并使用 $avg 操作符计算每个客户的平均订单金额。

db.orders.aggregate([{$group: {_id: "$customerId",count: { $sum: 1 },averageAmount: { $avg: "$amount" }}}
])

// 1
{"_id": 3,"count": 1,"averageAmount": 300
}// 2
{"_id": 2,"count": 2,"averageAmount": 225
}// 3
{"_id": 1,"count": 2,"averageAmount": 125
}

SpringBoot 整合 MongoDB实现上述操作：

@Data
public class AggregationResult {private String id;private Integer count;private Integer averageAmount;
}

@Test
public void aggregateTest() {GroupOperation groupOperation = Aggregation.group("customerId").count().as("count").avg("amount").as("averageAmount");// 组合上面的3个阶段Aggregation aggregation = Aggregation.newAggregation(groupOperation);AggregationResults<AggregationResult> results= mongoTemplate.aggregate(aggregation, Order.class, AggregationResult.class);List<AggregationResult> mappedResults = results.getMappedResults();mappedResults.forEach(System.out::println);//AggregationResult(id=3.0, count=1, averageAmount=300)//AggregationResult(id=2.0, count=2, averageAmount=225)//AggregationResult(id=1.0, count=2, averageAmount=125)
}

3. 示例3

按照订单金额范围统计订单数量：group阶段使用 $switch 操作符根据订单金额对订单文档进行分组。根据订单金额是否小于200，将订单分为"小额订单"和"大额订单"两个组。然后使用$sum操作符计算每个组的订单数量。

db.orders.aggregate([{$group: {_id: {$switch: {branches: [{ case: { $lt: ["$amount", 200] }, then: "小额订单" },{ case: { $gte: ["$amount", 200] }, then: "大额订单" }],default: "其他"}},count: { $sum: 1 }}}
])

// 1
{"_id": "大额订单","count": 3
}// 2
{"_id": "小额订单","count": 2
}