如何通过两个单独的标准确保分组 [英] How to ensure grouping via two separate criteria

问题描述

Expanded from 如何对mongodb中的汇总值求平均值?

Expanded from How to average the summed up values in mongodb?

使用 MongoDB 2.4.8，

Using MongoDB 2.4.8,

我有以下记录

{
    "category" : "TOYS",
    "price" : 12,
    "status" : "online",
    "_id" : "35043"
}
{
    "category" : "TOYS",
    "price" : 13,
    "status" : "offline",
    "_id" : "35044"
}
{
    "category" : "TOYS",
    "price" : 22,
    "status" : "online",
    "_id" : "35045"
}
{
    "category" : "BOOKS",
    "price" : 13,
    "status" : "offline",
    "_id" : "35046"
}
{
    "category" : "BOOKS",
    "price" : 17,
    "status" : "online",
    "_id" : "35047"
}
{
    "category" : "TOYS",
    "price" : 19,
    "status" : "unavailable",
    "_id" : "35048"
}
{
    "category" : "BOOKS",
    "price" : 10,
    "status" : "unavailable",
    "_id" : "35049"
}
{
    "category" : "BOOKS",
    "price" : 17,
    "status" : "unavailable",
    "_id" : "35050"
}

我想查找状态为在线或离线且类别内总价超过 50 的所有类别的平均价格.

I want to find the average price of all categories whose status is online OR offline and total price within a category is more than 50.

离线玩具和在线玩具被视为两个不同的类别.

Toys offline and Toys online are considered two separate categories.

我修改了给出的答案.

db.items.aggregate([
    {$match:
        {    
            $or: [       
              {status:"online"},
              {status:"offline"}
            ]
        }
    },
    {$group :
        {
            _id: "$category",
            total_price: {$sum:"$price"},
        }
    },
    {$match:
        {           
            total_price:{$gt:50}
        }
    },
    {$group :
        {
            _id: "1",
            avg_price: {$avg:"$total_price"},
        }
    },
]);

但我相信这个查询我改编了同名的分组类别，这不是我要找的.

But I believe this query I adapted grouped categories of the same name together which is not what I am looking for.

推荐答案

如果 status 的唯一值是 online 和 offline，则可以删除初始的 $match 步骤.如果需要，使用 $in 会更合适 运算符，因为这些值可以在同一索引中找到(如果存在).

If online and offline are the only values for status, you can remove the initial $match step. If it is needed, it would be more appropriate to use the $in operator as these values could be found in the same index (if one existed).

我认为您缺少的唯一步骤是您可以按多个字段(即类别和状态)$group:

I think the only step you are missing is that you can $group by multiple fields (i.e. category and status):

db.items.aggregate(

    // If 'online' and 'offline' are the only possible status values, this may be unnecessary
    { $match: {
        'status' : { $in: [ 'online', 'offline' ] }
    }},

    // Group by category & status
    { $group: {
        _id: { category: "$category", status: "$status" },
       total_price: { $sum: "$price" },
    }},

    // Only find groups where total_price is > 50
    { $match: {
        total_price: { $gt:50 }
    }},

    // Find the average price for the group
    { $group : {
        _id: null,
        avg_price: {$avg:"$total_price"},
    }}

)

这篇关于如何通过两个单独的标准确保分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！