Mongo 不同的聚合

2022-01-20 00:00:00 mongodb aggregation-framework distinct php

我正在尝试使用聚合框架在 mongo 中执行组计数，但结果并不完全符合预期.

I'm trying to use the aggregation framework to perform group counts in mongo but the results are not exactly as expected.

考虑下面的集合

 $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)的数量.

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

但它不会将所有 DISTINCT users_id 的总数归为一天，而是每天为我提供每个现有 user_id 的总数.`

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.`

结果(不完整)

     [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

我查看了 $unwind 但不能真的让我头疼.`

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

或者据我了解:每天唯一用户 ID 的计数.

Or as I understand it: Count of unique user_ids per day.

为此，您可以使用已有的组并删除计数，以便您只有一个唯一的 _id.user_id 和 _id.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'
            )
        )

然后将其传送到另一个 $group 语句，该语句计算每天的文档数量，因为每个唯一的 user_id/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)
        )

相关文章