阅读量:0
文章目录
在 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 { @Id private 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 { @Id private 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 } 