MongoDB:聚合,按几个字段分组

时间:2022-02-25 16:59:11

I have a collection with documents, of that structure

我收集了一些文档,是那种结构的

{
  type: 'a',
  date: '2014-01-04'
},
{
  type: 'b',
  date: '2014-01-04'
},
{
  type: 'b',
  date: '2014-01-04
},
{
  type: 'c',
  date: '2014-01-03'
},
{
  type: 'a',
  date: '2014-01-03'
}

I want to aggregate that data by date and type (group by date and count by type):

我想按日期和类型聚合数据(按日期分组,按类型计数):

{
  date: '2014-01-04': {
    'a': 1,
    'b': 2
  },

  date: '2014-01'03': {
    'a': 1,
    'c': 1
  }
}

I have aggregate function, like this

我有聚合函数,像这样。

db.items.aggregate([
    {
        $match: { user: user },
    },
    {
        $group: { _id: {date: '$date'}, count: {$sum: 1}, services: {$push: '$type'}}
    }
], function (err, results) {

But doing that I still need to reduce results by services.

但是这样做,我仍然需要通过服务减少结果。

Can this be done with one aggregation query?

这能通过一个聚合查询完成吗?

2 个解决方案

#1


0  

By assuming you have fixed number of types, you can solve it as follows :

假设你有固定数量的类型,你可以解决如下问题:

db.collection.aggregate(
{$group : {_id : "$date", 
           a:{$sum:{$cond:[{$eq:['$type','a']},1,0]}},
           b:{$sum:{$cond:[{$eq:['$type','b']},1,0]}},
           c:{$sum:{$cond:[{$eq:['$type','c']},1,0]}}
}},
{$project : {_id : 0, date : "$_id", a: "$a", b : "$b", c : "$c"}}
)

#2


3  

You can of course group by more than one field:

当然,你可以在多个领域进行分组:

{ $group: { _id: { date: '$date', services: '$services' } }

But that is not what you want it seems. You can not every easily convert data to keys, unless you can do that all by hand. The following query would be an option:

但这似乎不是你想要的。你不可能很容易地将数据转换成键,除非你可以手工完成。以下查询将是一种选择:

db.test.aggregate( [
    { $group: {
        '_id' : { date: '$date' },
        a: { $sum: { 
            $cond: [ { $eq: [ '$type', 'a' ] }, 1, 0 ]  
        } },
        b: { $sum: { 
            $cond: [ { $eq: [ '$type', 'b' ] }, 1, 0 ]
        } },
        c: { $sum: { 
            $cond: [ { $eq: [ '$type', 'c' ] }, 1, 0 ] 
        } },
    } },
    { $project: {
        _id: 0,
        date: '$_id.date',
        a: '$a',
        b: '$b',
        c: '$c',
    } }
] );

You will need to manually add a line for each new type.

您将需要为每个新类型手动添加一行。

#1


0  

By assuming you have fixed number of types, you can solve it as follows :

假设你有固定数量的类型,你可以解决如下问题:

db.collection.aggregate(
{$group : {_id : "$date", 
           a:{$sum:{$cond:[{$eq:['$type','a']},1,0]}},
           b:{$sum:{$cond:[{$eq:['$type','b']},1,0]}},
           c:{$sum:{$cond:[{$eq:['$type','c']},1,0]}}
}},
{$project : {_id : 0, date : "$_id", a: "$a", b : "$b", c : "$c"}}
)

#2


3  

You can of course group by more than one field:

当然,你可以在多个领域进行分组:

{ $group: { _id: { date: '$date', services: '$services' } }

But that is not what you want it seems. You can not every easily convert data to keys, unless you can do that all by hand. The following query would be an option:

但这似乎不是你想要的。你不可能很容易地将数据转换成键,除非你可以手工完成。以下查询将是一种选择:

db.test.aggregate( [
    { $group: {
        '_id' : { date: '$date' },
        a: { $sum: { 
            $cond: [ { $eq: [ '$type', 'a' ] }, 1, 0 ]  
        } },
        b: { $sum: { 
            $cond: [ { $eq: [ '$type', 'b' ] }, 1, 0 ]
        } },
        c: { $sum: { 
            $cond: [ { $eq: [ '$type', 'c' ] }, 1, 0 ] 
        } },
    } },
    { $project: {
        _id: 0,
        date: '$_id.date',
        a: '$a',
        b: '$b',
        c: '$c',
    } }
] );

You will need to manually add a line for each new type.

您将需要为每个新类型手动添加一行。