result = db.zip.aggregate([
{'$group' : {'_id' : '$state', 'total_pop' : {'$sum' : '$pop'}}},
{'$limit': 5}
])
for record in result:
print(record)
출력 fields - '_id' : '$field' --> SQL에서 GROUP BY field. - total_pop : a calculated field that contains the total population of each state. - $sum operator : Add the population field (pop) for each state. SQL과 비교해보자. - SQL: SELECT state, SUM(pop) FROM zip GROUP BY state - mongodb aggregate: db.zip.aggregate([ {'$group' : {'_id' : '$state', 'total_pop' : {'$sum' : '$pop'}}} ])
# total_pop 만 출력하되 5개만 출력하기
result = db.zip.aggregate([
{'$group' : {'_id' : '$state', 'total_pop' : {'$sum' : '$pop'} } },
{'$project' : {'_id' : 0, 'total_pop' : 1} },
{'$limit' : 5 }
])
for record in result:
print(record)
result = db.zip.aggregate([
{'$match' : {'pop' : {'$gte' : 100000}}},
{'$limit' : 5 }
])
for record in result:
print(record)
출력 fields - 전체 필드 SQL과 비교해보자. - SQL: SELECT * FROM zip WHERE pop >= 100000 - mongodb aggregate: db.zip.aggregate([ {'$match' : {'pop' : {'$gte' : 100000}}} ])
result = db.zip.aggregate([
{'$group' : {'_id' : '$state', 'total_pop' : {'$sum' : '$pop'}}},
{'$match' : {'total_pop' : {'$gte' : 10*1000*1000}}}
])
for record in result:
print(record)
- SQL1: SELECT state, SUM(pop) AS total_pop FROM zip GROUP BY state - SQL2: SELECT * FROM zip WHERE pop >= 100000 - SQL1 + SQL2: SELECT state, SUM(pop) AS total_pop FROM zip HAVING pop >= 10000000 - mongodb aggregate: result = db.zip.aggregate([ {'$group' : {'_id' : '$state', 'total_pop' : {'$sum' : '$pop'}}}, {'$match' : {'total_pop' : {'$gte' : 10*1000*1000}}} ])
result = db.zip.aggregate([
{'$group' : {'_id' : '$state', 'state_pop' : {'$sum' : '$pop'}}},
{'$match' : {'state_pop' : {'$gte' : 10*1000*1000}}},
{'$project' : { '_id' : 0, 'state_pop': 1}}
])
for record in result:
print(record)
* $limit (LIMIT)
</pre>
result = db.zip.aggregate([
{'$group' : {'_id' : '$state', 'total_pop' : {'$sum' : '$pop'}}},
{'$match' : {'total_pop' : {'$gte' : 10 * 1000 * 1000}}},
{'$sort' : {'total_pop' : -1}}
])
for record in result:
print(record)
result = db.zip.aggregate([
{'$group' : {'_id' : '$state', 'total_pop' : {'$sum' : '$pop'}}},
{'$match' : {'total_pop' : {'$gte' : 10 * 1000 * 1000}}},
{'$sort' : {'total_pop' : -1}},
{'$limit' : 5}
])
for record in result:
print(record)
result = db.zip.aggregate([
{'$group' : {'_id' : '$state', 'state_pop' : {'$sum' : '$pop'}}},
{'$match' : {'state_pop' : {'$gte' : 10*1000*1000}}},
{'$sort' : { 'state_pop' : -1 }},
{'$project' : { '_id' : 0, 'state_pop': 1}},
{'$limit' : 3}
])
for record in result:
print(record)