自己做网站制作,南阳网站运营招聘信息,宝安中心医院皮肤科,长春seo全网营销文章目录 1. 构造测试数据1. 示例 12. 示例23. 示例34. 示例45. 示例5 2. 构造测试数据1. 示例12. 示例23. 示例3 在 MongoDB 中#xff0c;$group 是聚合管道中的一个阶段#xff0c;用于根据指定的条件对文档进行分组。
{$group: {_id: expression, // 分组的依据… 文章目录 1. 构造测试数据1. 示例 12. 示例23. 示例34. 示例45. 示例5 2. 构造测试数据1. 示例12. 示例23. 示例3 在 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():参数的顺序为聚合管道的定义、输入类型、输出类型AggregationResultsAggregationResult results mongoTemplate.aggregate(aggregation, Sales.class, AggregationResult.class);ListAggregationResult mappedResults results.getMappedResults();mappedResults.forEach(System.out::println);//AggregationResult(idxyz)//AggregationResult(idjkl)//AggregationResult(iddef)//AggregationResult(idabc)
}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():参数的顺序为聚合管道的定义、输入类型、输出类型AggregationResultsAggregationResult results mongoTemplate.aggregate(aggregation, Sales.class, AggregationResult.class);ListAggregationResult mappedResults results.getMappedResults();mappedResults.forEach(System.out::println);//AggregationResult(idxyz, totalSaleAmount150)//AggregationResult(idjkl, totalSaleAmount20)//AggregationResult(iddef, totalSaleAmount112.5)//AggregationResult(idabc, totalSaleAmount170)
}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 100SpringBoot 整合 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():参数的顺序为聚合管道的定义、输入类型、输出类型AggregationResultsAggregationResult results mongoTemplate.aggregate(aggregation, Sales.class, AggregationResult.class);ListAggregationResult mappedResults results.getMappedResults();mappedResults.forEach(System.out::println);//AggregationResult(idxyz, totalSaleAmount150)//AggregationResult(iddef, totalSaleAmount112.5)//AggregationResult(idabc, totalSaleAmount170)
}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 DESCSpringBoot 整合 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);AggregationResultsAggregationResult results mongoTemplate.aggregate(aggregation, Sales.class, AggregationResult.class);ListAggregationResult mappedResults results.getMappedResults();mappedResults.forEach(System.out::println);//AggregationResult(id2014-04-04, totalSaleAmount200, averageQuantity15.0, count2)//AggregationResult(id2014-03-15, totalSaleAmount50, averageQuantity10.0, count1)//AggregationResult(id2014-03-01, totalSaleAmount40, averageQuantity1.5, count2)
}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 salesSpringBoot 整合 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);AggregationResultsAggregationResult results mongoTemplate.aggregate(aggregation, Sales.class, AggregationResult.class);ListAggregationResult mappedResults results.getMappedResults();mappedResults.forEach(System.out::println);//AggregationResult(idnull, totalSaleAmount452.5, averageQuantity7.875, count8)
}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);AggregationResultsAggregationResult results mongoTemplate.aggregate(aggregation, Order.class, AggregationResult.class);ListAggregationResult mappedResults results.getMappedResults();mappedResults.forEach(System.out::println);//AggregationResult(id3.0, totalAmount300)//AggregationResult(id2.0, totalAmount450)//AggregationResult(id1.0, totalAmount250)
}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);AggregationResultsAggregationResult results mongoTemplate.aggregate(aggregation, Order.class, AggregationResult.class);ListAggregationResult mappedResults results.getMappedResults();mappedResults.forEach(System.out::println);//AggregationResult(id3.0, count1, averageAmount300)//AggregationResult(id2.0, count2, averageAmount225)//AggregationResult(id1.0, count2, averageAmount125)
}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
}
