Skip to content

Instantly share code, notes, and snippets.

@SharpMan
Created June 4, 2020 21:45
Show Gist options
  • Save SharpMan/cc5f902f323888ae2697149a66a8ae1a to your computer and use it in GitHub Desktop.
Save SharpMan/cc5f902f323888ae2697149a66a8ae1a to your computer and use it in GitHub Desktop.
//1-Count high speeds: Find the number of speeds > 100 in the data set.
db.agg_w_loopdata.find( { speed: { $gt: 100 } } ).count()
//2-Volume: Find the total volume for the station Foster NB for Sept 21, 2011.
db.agg_w_loopdata.aggregate([
{ $match:
{
"station.locationtext": "Foster NB",
starttime:
{
$gt: ISODate("2011-09-22T00:00:00-07:00"),
$lt: ISODate("2011-09-23T00:00:00-07:00")
}
}
},
{
$group:
{
_id: null,
totVolume: { $sum: "$volume" }
}
},
]);
//3-Single-Day Station Travel Times: Find travel time for station Foster NB for 5-minute intervals for Sept 22, 2011. Report travel time in seconds.
db.agg_w_loopdata.aggregate([
{ $match:
{
"station.locationtext": "Foster NB",
starttime:
{
$gt: ISODate("2011-09-22T00:00:00-07:00"),
$lt: ISODate("2011-09-23T00:00:00-07:00")
}
}
},
{ "$group": {
"_id": {
"$toDate": {
"$subtract": [
{ "$toLong": "$starttime" },
{ "$mod": [ { "$toLong": "$starttime" }, 1000 * 60 * 5 ] }
]
},
},
"speed": { "$avg": "$speed" }
}},
{ "$group": {
"_id": "_id",
"speed": { "$avg": "$speed" }
}},
{ $project: { avg_travel_time: { $multiply: [{$divide: [ 1.6, '$speed' ]}, 3600] } } },
]);
//4- Peak Period Travel Times: Find the average travel time for 7-9AM and 4-6PM on September 22, 2011 for station Foster NB. Report travel time in seconds.
db.agg_w_loopdata.aggregate([
{ $match:
{
"station.locationtext": "Foster NB",
$or:
[
{
starttime:
{
$gt: ISODate("2010-09-22T07:00:00-07:00"),
$lt: ISODate("2012-09-22T09:00:00-07:00")
}
},
{
starttime:
{
$gt: ISODate("2010-09-22T16:00:00-07:00"),
$lt: ISODate("2012-09-22T18:00:00-07:00")
}
}
]
}
},
{ "$group": {
"_id": {
"$toDate": {
"$subtract": [
{ "$toLong": "$starttime" },
{ "$mod": [ { "$toLong": "$starttime" }, 1000 * 60 * 60 * 2 ] }
]
},
},
"speed": { "$avg": "$speed" }
}},
{ "$group": {
"_id": "_id",
"speed": { "$avg": "$speed" }
}},
{ $project: { avg_travel_time: { $multiply: [{$divide: [ 1.6, '$speed' ]}, 3600] } } },
]);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment