Mongodb计数与多个组字段不同 [英] Mongodb count distinct with multiple group fields

问题描述

我有一个交易表，该表由员工的假期组成. 我需要在mongodb中遵循以下SQL方案的帮助.

I have transaction table which is populated by holidays taken by the employees. I would need help on following sql scenario in mongodb.

select employee,month,year,count(distinct (holiday_type) from 
transactions group by employee,month,year

我需要在mongodb中使用聚合，并像这样创建了mongo查询，这给了我错误的解决方案

I need to use aggregation in mongodb and was created mongo query like this and this gives me wrong solution

db.transactions.aggregate([
    { "$group": { 
        "_id": { 
            "Month": { "$month" : "$date" }, 
            "Year": { "$year" : "$date" },
            "employee" : "$employee",
            "holiday_type" : "$holiday_type"
        },
        "Count_of_Transactions" : { "$sum" : 1 }
     }}
 ]);

我对在mongodb中使用计数不同的逻辑感到困惑.任何建议都会有帮助

I am confused in using count distinct logic in mongodb. Any suggestion would be helpful

推荐答案

部分实现，但是您需要先获取"holiday_type"的"distinct"值，然后再

Part of the way there but you need to get the "distinct" values for "holiday_type" first, then you $group again:

db.transactions.aggregate([
    { "$group": { 
        "_id": { 
            "employee" : "$employee",
            "Month": { "$month" : "$date" }, 
            "Year": { "$year" : "$date" },
            "holiday_type" : "$holiday_type"
        },
     }},
     { "$group": {
         "_id": {
            "employee" : "$_id.employee",
            "Month": "$_id.Month",
            "Year": "$_id.Year"
         },
         "count": { "$sum": 1 }
     }}
 ], { "allowDiskUse": true }
 );

这是一般过程，因为SQL中的与众不同"本身就是一种分组操作.因此，这是两次 $group 操作，以便得到正确的结果.

That is the general process as "distinct" in SQL is kind of a grouping operation in itself. So it is a double $group operation in order to get your correct result.

这篇关于Mongodb计数与多个组字段不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！