Difference between revisions of "AGGREGATE examples"
m |
m |
||
| Line 190: | Line 190: | ||
Some other useful aggregate functions to know are <code>$sum</code> and average: <code>$avg</code><br/> | Some other useful aggregate functions to know are <code>$sum</code> and average: <code>$avg</code><br/> | ||
Lets combine all we've learnt so far. | Lets combine all we've learnt so far. | ||
| − | <p class=strong>Order the continents in descending order by total GDP, | + | <p class=strong>Order the continents in descending order by total GDP, Include the average GDP for each country.</p> |
<pre class=def> | <pre class=def> | ||
pp.pprint(list( | pp.pprint(list( | ||
Revision as of 11:58, 17 July 2015
#ENCODING
import io
import sys
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-16')
#MONGO
from pymongo import MongoClient
client = MongoClient()
client.progzoo.authenticate('scott','tiger')
db = client['progzoo']
#PRETTY
import pprint
pp = pprint.PrettyPrinter(indent=4)
Introducing the aggregation framework
These examples introduce the aggregation framework and its operators. Again we will be using the collection world
$match Allows us to perform queries in a similar way to find()
Show all the details for France
pp.pprint(list(
db.world.aggregate([
{"$match":{"name":"France"}}
])
))
pp.pprint(list(db.world.aggregate([{"$match":{"name":"France"}}])))
$project Allows us to select what fields to display.
It can also has the ability to insert new fields and allows you to compare fields against each other without using $where
Show the name and population density of all Asian countries. (population/area)
Note that "density" is a new field, made from the result of dividing two existing fields, and that $divide is an aggregate function.
To avoid diving by 0 we do a $match to remove any countries with 0 area (Vatican City), then pipe these results through to $project
pp.pprint(list(
db.world.aggregate([
{"$match":{"area":{"$ne":0},"continent":"Asia"}},
{"$project":{
"_id":0,
"name":1,
"density": {"$divide": ["$population","$area"]}
}}
])
))
pp.pprint(list(db.world.aggregate([{"$match":{"area":{"$ne":0},"continent":"Asia"}},{"$project":{"_id":0,"name":1,"density":{"$divide":["$population","$area"]}}}])))
Because aggregate is a pipeline we can repeat a stage, and we aren't forced to use stages in a specific order.
Show the name of Asian countries with a density that's over 500 people per km2. (population/area)
pp.pprint(list(
db.world.aggregate([
{"$match":{"area":{"$ne":0},"continent":"Asia"}},
{"$project":{
"_id":0,
"name":1,
"density": {"$divide": ["$population","$area"]}
}},
{"$match":{"density":{"$gt":500}}}
])
))
pp.pprint(list(db.world.aggregate([{"$match":{"area":{"$ne":0},"continent":"Asia"}},{"$project":{"_id":0,"name":1,"density":{"$divide":["$population","$area"]}}},{"$match":{"density":{"$gt":500}}}])))
$sort Allows us to choose how the results are displayed, where 1 is ascending and -1 is descending.
Note that excluding $match is the same as {"$match":{}}
Show the name of all countries in descending order.
pp.pprint(list(
db.world.aggregate([
{"$project":{
"_id":0,
"name":1,
}},
{"$sort":{
"name":-1
}}
])
))
pp.pprint(list(db.world.aggregate([{"$project":{"_id":0,"name":1,}},{"$sort":{"name":-1}}])))
Grouping
Grouping allows us to use accumulator operations sum as $sum
All groups must have an _id. To see why this is useful imagine the following:
So far you've been using the collection world
As every country has a continent, it would make sense to have countries as a nested document inside continents: e.g:
[
{"name":"Africa",
"countries":[
{"name":"Algeria", "capital":"Algiers", ...},
{"name":"Angola", "capital":"Luanda", ...},
{"name":"Benin", "capital":"Porto-Novo",...}.
{...},
...
]},
{"name":"Asia",
"countries":[
{"name":"Afghanistan","capital":"Kabul", ...},
{"name":"Azerbaijan", "capital":"Baku", ...},
{"name":"Bahrain", "capital":"Manama",...},
{...},
...
]},
{...},
...
]
The world collection isn't like this however. We'll be using the following structure, which has a redundancy where continent is repeated for each country. Don't worry, you'll learn how to query nested documents later.
[
{"name":"Afghanistan","capital":"Kabul", "continent":"Asia", ...},
{"name":"Albania", "capital":"Tirana", "continent":"Europe, ...},
{"name":"Algeria", "capital":"Algiers","contiennt":"Africa",...},
{...},
...
]
What if we wanted to know what the largest and smallest GDPs in each continent are? We can use the _id to group by continent: "_id":"continent" If we wanted to find these values for any country regardless of continent, we could just group by "_id":"null" or "_id":None
$max and $min can be used to get the largest and smallest values in a group.
Get the smallest and largest GDPs of each continent.
pp.pprint(list(
db.world.aggregate([
{"$group":{
'_id':'$continent',
'min':{"$min":"$gdp"},
'max':{"$max":"$gdp"}
}},
{"$project":{
"_id":1,
"min":1,
"max":1
}},
])
))
pp.pprint(list(db.world.aggregate([{"$group":{'_id':'$continent','min':{"$min":"$gdp"},'max':{"$max":"$gdp"}}},{"$project":{"_id":1,"min":1,"max":1}},])))
A downside of grouping is that we lose the ability to find the $name of the country with the min/max value.
If we want to do this, a simple way is to combine a sort sort=[(<fieldname>, <direction>)] and a find_one(). Performance will be improved if these fields have previously been indexed.
Get the names and GDPs of the two countries with the smallest and largest GDPs.
It is possible that we will occasionally encounter null values in a data collection. This could be either where the field is simply not present in a certain document - MongoDB will simply ignore this document and not include it in the results set - or a document has a redundancy where the field is specified as null - which MongoDB will include. e.g: {"name": "Bob", "age": None} etc.
To deal with this we can use {<field>: {"$ne": None}} to prevent any null values from being included.
pp.pprint(
db.world.find_one({"gdp":{"$ne":None}},{"name":1,"gdp":1,"_id":0},sort=[("gdp", 1)])
)
pp.pprint(
db.world.find_one({},{"name":1,"gdp":1,"_id":0},sort=[("gdp", -1)])
)
pp.pprint(db.world.find_one({"gdp":{"$ne":None}},{"name":1,"gdp":1,"_id":0},sort=[("gdp", 1)])) pp.pprint(db.world.find_one({},{"name":1,"gdp":1,"_id":0},sort=[("gdp", -1)]))
Some other useful aggregate functions to know are $sum and average: $avg
Lets combine all we've learnt so far.
Order the continents in descending order by total GDP, Include the average GDP for each country.
pp.pprint(list(
db.world.aggregate([
{"$group":{
"_id":"$continent",
"Total GDP": {"$sum": "$gdp"},
"Average GDP": {"$avg": "$gdp"}
}},
{"$sort":{
"Total GDP":-1
}},
{"$project":{
"Area":"$_id",
"Total GDP": 1,
"Average GDP":1,
"_id":0
}}
])
))
pp.pprint(list(db.world.aggregate([{"$group":{"_id":"$continent","Total GDP":{"$sum":"$gdp"},"Average GDP":{"$avg":"$gdp"}}},{"$sort":{"Total GDP":-1}},{"$project":{"Area":"$_id","Total GDP":1,"Average GDP":1,"_id":0}}])))