Skip to content

Instantly share code, notes, and snippets.

@d1ff
Created June 7, 2015 15:05
Show Gist options
  • Save d1ff/88eb11b429e730614dbb to your computer and use it in GitHub Desktop.
Save d1ff/88eb11b429e730614dbb to your computer and use it in GitHub Desktop.
mongo aggregation query
[
{
$match:
{
"last_request.2015_05_20": {$exists: true}
}
},
{
$project: {
"main_computer": 1,
"client_type": 1,
"showcase_code": 1,
"download_speed.main": { $ifNull : [ "$download_speed.main", [ 0 ] ] },
"download_speed.site": { $ifNull: ["$download_speed.site", [0] ] }
}
},
{
$unwind: "$download_speed.main"
},
{
$unwind: "$download_speed.site"
},
{
$group: {
"_id": "$showcase_code",
"main_speed_count": {
$sum: {
$cond: {
if : { $gt: ["$download_speed.main", 0] },
then: 1,
else: 0
}
}
},
"main_speed_sum": {
$sum: "$download_speed.main"
},
"site_speed_count": {
$sum: {
$cond: {
if : { $gt: ["$download_speed.site", 0] },
then: 1,
else: 0
}
}
},
"site_speed_sum": {
$sum: "$download_speed.site"
},
"total_main": {
$sum: "$main_computer"
},
"total_windows": {
$sum: {
$cond: {
if : {$eq: ["$client_type", "WIN"]},
then: 1,
else: 0
}
}
},
"total_android": {
$sum: {
$cond: {
if : { $eq: ["$client_type", "ANDRO"] },
then: 1,
else: 0
}
}
},
"total": {
$sum: 1
}
}
},
{
$project: {
"main_speed_count": 1,
"main_speed_sum": 1,
"site_speed_count": 1,
"site_speed_sum": 1,
"showcase_code": 1,
"total_main": 1,
"total_windows": 1,
"total_android": 1,
"total_windows2": {$subtract: ["$total_windows", "$total_main"] },
"total_main": 1,
"total_other": { $subtract: [{$subtract: ["$total", "$total_windows"]}, "$total_android"] },
"total": 1
}
},
{
$project: {
"avg_speed_main": {$cond: {
if: { $gt: ["$main_speed_count", 0] },
then: { $divide: ["$main_speed_sum", "$main_speed_count"] },
else: 0
} },
"avg_speed_site": {$cond: {
if: { $gt: ["$site_speed_count", 0] },
then: { $divide: ["$site_speed_sum", "$site_speed_count"] },
else: 0
} },
"showcase_code": 1,
"total_main": 1,
"total_windows": 1,
"total_android": 1,
"total_windows2": 1,
"total_main": 1,
"total_other": 1,
"total": 1
}
},
{
$sort: {
"_id": 1
}
}
]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment