51
Write a MongoDB query to find the average score for each restaurant.
一个数组的平均值
这里我遇到的第一个 aggregate
1db.restaurants.aggregate([{
2 $unwind: "$grades"
3 },
4 {
5 $group: {
6 _id: "$name",
7 avgScore: {
8 $avg: "$grades.score"
9 }
10 }
11 }
12])
这里gpt给出的多种求平均值的方法
在 MongoDB 中，要求数组的平均值可以使用 聚合框架 来处理。MongoDB 的聚合框架提供了强大的数据操作功能，包括计算数组中元素的平均值。可以通过以下几种方式来实现：
假设我们有以下文档集合：
1{
2 "_id": 1,
3 "scores": [90, 80, 70, 85]
4},
5{
6 "_id": 2,
7 "scores": [60, 75, 85, 90]
8}
方法 1：使用 $avg 操作符
MongoDB 的聚合框架允许在管道中使用 $avg 操作符来计算某个数组的平均值。为了计算数组 scores 的平均值，我们需要先将数组元素展开（使用 $unwind），然后计算平均值。
聚合管道示例：
1db.collection.aggregate([
2 {
3 $project: {
4 averageScore: { $avg: "$scores" }
5 }
6 }
7])
解释：
$project：用于指定输出的字段，在这里我们指定了一个新字段averageScore。$avg：用于计算指定字段的平均值。在这里，我们应用$avg操作符来计算scores数组的平均值。
方法 2：使用 $unwind 与 $group 来求平均值
如果你想更细粒度地处理数组中的元素，可以使用 $unwind 来展平数组，然后使用 $group 来求数组的平均值。
聚合管道示例：
1db.collection.aggregate([
2 { $unwind: "$scores" },
3 { $group: { _id: null, averageScore: { $avg: "$scores" } } }
4])
解释：
$unwind：将scores数组中的每个元素展平为单独的文档。每个文档将包含一个数组元素。$group：将所有展平后的文档组合在一起，并使用$avg来计算这些文档中scores字段的平均值。
这个方法适用于当你希望对数组中的所有元素进行处理并计算平均值的情况。
方法 3：直接计算字段的平均值
如果数组字段本身已经包含了所有的数值，且你只想计算这个数组字段的平均值，而不是数组中的每个元素，可以直接在 aggregate 阶段中计算平均值：
示例：
1db.collection.aggregate([
2 {
3 $project: {
4 averageScore: { $avg: "$scores" }
5 }
6 }
7])
总结：
$avg用于计算一个数值字段的平均值，可以直接在聚合管道中对数组字段进行操作。$unwind+$group适用于需要将数组元素分开处理，并求所有元素的平均值。
根据需求选择适合的方法，通常情况下，直接使用 $avg 对数组字段求平均值最为简单和高效。
52
Write a MongoDB query to find the highest score for each restaurant.
这里用到了 $group阶段的$max操作
1db.restaurants.aggregate([{
2 $unwind: "$grades"
3 },
4 {
5 $group: {
6 _id: "$name",
7 highest_score: {
8 $max: "$grades.score"
9 }
10 }
11 }
12])
53
Write a MongoDB query to find the lowest score for each restaurant.
和上一个题目一样，用$min
1db.restaurants.aggregate([{
2 $unwind: "$grades"
3 },
4 {
5 $group: {
6 _id: "$name",
7 lowest_score: {
8 $min: "$grades.score"
9 }
10 }
11 }
12])
54
Write a MongoDB query to find the count of restaurants in each borough.
用到了count
1db.restaurants.aggregate([{
2 $group: {
3 _id: "$borough",
4 count: {
5 $sum: 1
6 }
7 }
8}])
55
Write a MongoDB query to find the count of restaurants for each cuisine.
只是用到了$sum
1db.restaurants.aggregate([{
2 $group: {
3 _id: "$cuisine",
4 count: {
5 $sum: 1
6 }
7 }
8}])
56 如何按两个值进行分组
1db.restaurants.aggregate([{
2 $group: {
3 _id: {
4 cuisine: "$cuisine",
5 borough: "$borough"
6 },
7 count: {
8 $sum: 1
9 }
10 }
11}])
57 多阶段配合
Write a MongoDB query to find the count of restaurants that received a grade of ‘A’ for each cuisine.
多阶段
- unwind
- match
- group
1db.restaurants.aggregate([
2 {
3 $unwind: "$grades"
4 },
5 {
6 $match: { "grades.grade": "A" }
7 },
8 {
9 $group: {
10 _id: "$cuisine",
11count: { $sum: 1 }
12 }
13 }
14])
58 同上
Write a MongoDB query to find the count of restaurants that received a grade of ‘A’ for each borough.
1db.restaurants.aggregate([
2 {
3 $unwind: "$grades"
4 },
5 {
6 $match: { "grades.grade": "A" }
7 },
8 {
9 $group: {
10 _id: "$borough",
11count: { $sum: 1 }
12 }
13 }
14])
59
Write a MongoDB query to find the count of restaurants that received a grade of ‘A’ for each cuisine and borough.
聚合确实能达到
这里没有unwind
1db.restaurants.aggregate([
2 {
3 $match: { "grades.grade": "A" }
4 },
5 {
6 $group: {
7 _id: { cuisine: "$cuisine", borough: "$borough" },
8count: { $sum: 1 }
9 }
10 },
11 {
12 $sort: { count: -1 }
13 }
14]);
60
Write a MongoDB query to find the number of restaurants that have been graded in each month of the year.
问一年中，每个月分评级的restaurants分别有多少个,那显然要$group
1db.restaurants.aggregate([
2 {
3 $unwind: "$grades"
4 },
5 {
6 $project: {
7month: { $month: { $toDate: "$grades.date" } },
8year: { $year: { $toDate: "$grades.date" } }
9 }
10 },
11 {
12 $group: {
13 _id: { month: "$month", year: "$year" },
14count: { $sum: 1 }
15 }
16 },
17 {
18 $sort: {
19 "_id.year": 1,
20 "_id.month": 1
21 }
22 }
23]);
61-66 平均分,最高低分
上面求过了
67
Write a MongoDB query to find the name and address of the restaurants that received a grade of ‘A’ on a specific date
查询一个array里满足特定条件的。
1db.restaurants.find(
2 {
3 "grades": {
4 "$elemMatch": {
5 "date": {
6 "$eq": ISODate("2013-07-22T00:00:00Z")
7 },
8 "grade": {
9 "$eq": "A"
10 }
11 }
12 }
13 },
14 {
15 "name": 1,
16 "address": 1,
17 "_id": 0
18 }
19)
68
Write a MongoDB query to find the name and address of the restaurants that received a grade of ‘B’ or ‘C’ on a specific date.
1db.restaurants.find(
2 {
3 "grades": {
4 $elemMatch: {
5 "date": ISODate("2013-04-05"),
6 "grade": { $in: [ "B", "C" ] }
7 }
8 }
9 },
10 {
11 "name": 1,
12 "address": 1
13 }
14)
69
Write a MongoDB query to find the name and address of the restaurants that have at least one ‘A’ grade and one ‘B’ grade.
啊！ 查询数组里： 至少含有一个A，且至少含有一个B ,\exits A in arr \and \exits B in arr
70
Write a MongoDB query to find the name and address of the restaurants that have at least one ‘A’ grade and no ‘B’ grades.
one A no B.两个条件同时成立
no b -> not exits b in Arr -> {$not : { "grades.score" : 'A'}
和我想的差不多
1db.restaurants.find({
2 $and: [
3{ "grades.grade": "A" },
4{ "grades.grade": { $not: { $eq: "B" } } }
5 ]
6},
7{ name: 1, address: 1, _id: 0 })
71
Write a MongoDB query to find the name ,address and grades of the restaurants that have at least one ‘A’ grade and no ‘C’ grades.
同上
1db.restaurants.find({
2 $and: [
3{ "grades.grade": "A" },
4{ "grades.grade": { $not: { $eq: "C" } } }
5 ]
6},
7{ name: 1, address: 1, "grades.grade":1, _id: 0 })
72
Write a MongoDB query to find the name, address, and grades of the restaurants that have at least one ‘A’ grade, no ‘B’ grades, and no ‘C’ grades.
同上
1db.restaurants.find({
2 $and: [
3{ "grades.grade": "A" },
4{ "grades.grade": { $not: { $eq: "B" } } },
5{ "grades.grade": { $not: { $eq: "C" } } }
6 ]
7},
8{ name: 1, address: 1, "grades.grade":1, _id: 0 })
73
Write a MongoDB query to find the name and address of the restaurants that have the word ‘coffee’ in their name
正则
1db.restaurants.find({ name: { $regex: /coffee/i } }, { name: 1, address: 1 })
74
Write a MongoDB query to find the name and address of the restaurants that have a zipcode that starts with ‘10’.
这里我觉得官方给的答案不对。
75
Write a MongoDB query to find the name and address of the restaurants that have a cuisine that starts with the letter ‘B’.
1db.restaurants.find(
2 { "cuisine": { $regex: /^B/ } },
3 { "name": 1,
4 "address": 1,
5 "cuisine" : 1,
6 "_id": 0 }
7)
76 77 正则
没有什么好写的
78
找最大值，那就需要排序
1db.restaurants.aggregate([
2 {$unwind: "$grades"},
3 {$group: {
4 _id: "$restaurant_id",
5avgScore: {$avg: "$grades.score"}
6 }},
7 {$sort: {avgScore: -1}},
8 {$limit: 1},
9 {$project: {_id: 1, avgScore: 1}}
10])
79
找最大的值A的那些restaurants的id，用到聚合
1db.restaurants.aggregate([
2 {$unwind: "$grades"},
3 {$match: {"grades.grade": "A"}},
4 {$group: {
5 _id: "$restaurant_id",
6count: {$sum: 1}
7 }},
8 {$sort: {count: -1}},
9 {$group: {
10 _id: "$count",
11restaurants: {$push: "$_id"}
12 }},
13 {$sort: {_id: -1}},
14 {$limit: 1},
15 {$project: {restaurants: 1}}
16])
80
Write a MongoDB query to find the cuisine type that is most likely to receive a ‘C’ grade.
哪个区域的C最多,用到了聚合
1db.restaurants.aggregate([
2 {$unwind: "$grades"},
3 {$match: {"grades.grade": "C"}},
4 {$group: {_id: "$cuisine", count: {$sum: 1}}},
5 {$sort: {count: -1}}
6])
81
Write a MongoDB query to find the restaurant that has the highest average score for thecuisine “Turkish”.
聚合。
82
Write a MongoDB query to find the restaurants that achieved the highest total score.
连续的聚合,聚合就是一种管道。
1db.restaurants.aggregate([
2{ $unwind: "$grades" },
3{ $group: {
4 _id: "$name",
5totalScore: { $sum: "$grades.score" }
6 }},
7{ $sort: { totalScore: -1 } },
8{ $group: {
9 _id: "$totalScore",
10restaurants: { $push: "$_id" }
11 }},
12{ $sort: { _id: -1 } },
13{ $limit: 1 },
14{ $unwind: "$restaurants" },
15{ $group: {
16 _id: "$_id",
17restaurants: { $push: "$restaurants" }
18 }}
19])