蒙哥独特的聚集 [英] Mongo Distinct Aggegation
问题描述
我正在尝试使用聚合框架在mongo中执行组计数,但结果并不完全符合预期.
考虑下面的收藏
$people->insert(array("user_id" => "1", "day" => "Monday", 'age' => 18));
$people->insert(array("user_id" => "3", "day" => "Monday", 'age' => 24));
$people->insert(array("user_id" => "1", "day" => "Monday", 'age' => 18));
$people->insert(array("user_id" => "1", "day" => "Monday", 'age' => 18));
$people->insert(array("user_id" => "2", "day" => "Monday", 'age' => 25));
$people->insert(array("user_id" => "4", "day" => "Monday", 'age' => 33));
$people->insert(array("user_id" => "1", "day" => "Tuesday", 'age' => 18));
$people->insert(array("user_id" => "2", "day" => "Tuesday", 'age' => 25));
$people->insert(array("user_id" => "1", "day" => "Wednesday", 'age' => 18));
$people->insert(array("user_id" => "2", "day" => "Thursday", 'age' => 25));
$people->insert(array("user_id" => "1", "day" => "Friday", 'age' => 18));
我使用下面的查询尝试计算一周中每一天的不同条目(user_id)的数量.
$query = array(
array(
'$project' => array(
'user_id' =>1,
'day' =>1,
),
),
array(
'$group' => array(
'_id' => array(
'user_id' => '$user_id',
'day' => '$day'),
'count' => array('$sum' => 1),
)
));
因此,对于上面的集合,结果应为
Monday = 3 Tues = 2, Wed = 1, Thur = 1 and Friday = 1
,但是它不会将所有DISTINCT users_id的总数在一天之内分组,而是每天为我提供每个现有user_id的总数.
结果(不完整)
[result] => Array
(
[0] => Array
(
[_id] => Array
(
[user_id] => 1
[day] => Friday
)
[count] => 1
)
[1] => Array
(
[_id] => Array
(
[user_id] => 1
[day] => Wednesday
)
[count] => 1
)
[2] => Array
(
[_id] => Array
(
[user_id] => 2
[day] => Tuesday
)
[count] => 1
)
... ... ...
有人可以帮我过滤每日总计,以便只包含每天不同的总计
我查看了 $ unwind ,但无法进行真的让我明白了. `
如果我正确地理解了这个问题,那么您要解决的是
totals of all DISTINCT users_id under a day
或者据我了解:每天唯一用户ID的计数.
为此,您可以使用已经拥有的组并减少计数,以便只有唯一的_id.user_id
和_id.day
值:
'$group' => array(
'_id' => array(
'user_id' => '$user_id',
'day' => '$day'
)
)
然后通过管道将其传递到另一个计算每天文档数量的$group
语句,因为每个唯一的user_id
/day
组合都只有一个:
'$group' => array(
'_id' => '$_id.day',
'count' => array('$sum' => 1)
)
I'm trying to use the aggregation framework to perform group counts in mongo but the results are not exactly as expected.
Consider the collection bellow
$people->insert(array("user_id" => "1", "day" => "Monday", 'age' => 18));
$people->insert(array("user_id" => "3", "day" => "Monday", 'age' => 24));
$people->insert(array("user_id" => "1", "day" => "Monday", 'age' => 18));
$people->insert(array("user_id" => "1", "day" => "Monday", 'age' => 18));
$people->insert(array("user_id" => "2", "day" => "Monday", 'age' => 25));
$people->insert(array("user_id" => "4", "day" => "Monday", 'age' => 33));
$people->insert(array("user_id" => "1", "day" => "Tuesday", 'age' => 18));
$people->insert(array("user_id" => "2", "day" => "Tuesday", 'age' => 25));
$people->insert(array("user_id" => "1", "day" => "Wednesday", 'age' => 18));
$people->insert(array("user_id" => "2", "day" => "Thursday", 'age' => 25));
$people->insert(array("user_id" => "1", "day" => "Friday", 'age' => 18));
I use the query below try count the number of distinct entries (user_id) for each day of the week.
$query = array(
array(
'$project' => array(
'user_id' =>1,
'day' =>1,
),
),
array(
'$group' => array(
'_id' => array(
'user_id' => '$user_id',
'day' => '$day'),
'count' => array('$sum' => 1),
)
));
So for the collection above the results should be
Monday = 3 Tues = 2, Wed = 1, Thur = 1 and Friday = 1
but it does not group the totals of all DISTINCT users_id under a day, and instead for each day it gives me a total for each existing user_id.`
Results (not complete)
[result] => Array
(
[0] => Array
(
[_id] => Array
(
[user_id] => 1
[day] => Friday
)
[count] => 1
)
[1] => Array
(
[_id] => Array
(
[user_id] => 1
[day] => Wednesday
)
[count] => 1
)
[2] => Array
(
[_id] => Array
(
[user_id] => 2
[day] => Tuesday
)
[count] => 1
)
... ... ...
Can someone help me to filter the daily totals so that it only includes distinct totals per day
I have looked at the $unwind but couldn't really get my head around it. `
If I'm understanding the question right, what you're trying to get at is
totals of all DISTINCT users_id under a day
Or as I understand it: Count of unique user_ids per day.
For that, you could take the group you already have and cut out the count so that you just have a unique _id.user_id
and _id.day
value:
'$group' => array(
'_id' => array(
'user_id' => '$user_id',
'day' => '$day'
)
)
Then pipe that to another $group
statement that counts the number of documents per day, since there is exactly one for every unique user_id
/day
combination:
'$group' => array(
'_id' => '$_id.day',
'count' => array('$sum' => 1)
)
这篇关于蒙哥独特的聚集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!