MongoDBの集計($group, $match, $sum, $avgなど)

2022/11/27

MongoDBで集計を行う方法を確認します(aggregate)。「件数」「合計」「平均」「最大」「最小」を求める方法、集計時に特定条件を設定する方法など取り上げます。

前準備

動作確認用に、コレクションの作成、ドキュメントの追加を行います。

db.createCollection('test_collection')

db.test_collection.insert( { id:1, user_id:'aaa', point:21, created:ISODate("2020-12-01T00:00:00+09:00")} );
db.test_collection.insert( { id:2, user_id:'bbb', point:67, created:ISODate("2020-12-01T00:00:00+09:00")} );
db.test_collection.insert( { id:3, user_id:'ccc', point:34, created:ISODate("2020-12-01T00:00:00+09:00")} );
db.test_collection.insert( { id:4, user_id:'ddd', point:22, created:ISODate("2021-01-01T00:00:00+09:00")} );
db.test_collection.insert( { id:5, user_id:'ccc', point:62, created:ISODate("2021-01-01T00:00:00+09:00")} );
db.test_collection.insert( { id:7, user_id:'aaa', point:53, created:ISODate("2021-01-01T00:00:00+09:00")} );
db.test_collection.insert( { id:8, user_id:'aaa', point:12, created:ISODate("2021-01-01T00:00:00+09:00")} );
db.test_collection.insert( { id:9, user_id:'bbb', point:52, created:ISODate("2021-01-01T00:00:00+09:00")} );

以下のように、データが格納されています。

> db.test_collection.find();
{ "_id" : ObjectId("622d7bda3c53cfb65ce8029f"), "id" : 1, "user_id" : "aaa", "point" : 21, "created" : ISODate("2020-11-30T15:00:00Z") }
{ "_id" : ObjectId("622d7bda3c53cfb65ce802a0"), "id" : 2, "user_id" : "bbb", "point" : 67, "created" : ISODate("2020-11-30T15:00:00Z") }
{ "_id" : ObjectId("622d7bda3c53cfb65ce802a1"), "id" : 3, "user_id" : "ccc", "point" : 34, "created" : ISODate("2020-11-30T15:00:00Z") }
{ "_id" : ObjectId("622d7bda3c53cfb65ce802a2"), "id" : 4, "user_id" : "ddd", "point" : 22, "created" : ISODate("2020-12-31T15:00:00Z") }
{ "_id" : ObjectId("622d7bda3c53cfb65ce802a3"), "id" : 5, "user_id" : "ccc", "point" : 62, "created" : ISODate("2020-12-31T15:00:00Z") }
{ "_id" : ObjectId("622d7bda3c53cfb65ce802a4"), "id" : 7, "user_id" : "aaa", "point" : 53, "created" : ISODate("2020-12-31T15:00:00Z") }
{ "_id" : ObjectId("622d7bda3c53cfb65ce802a5"), "id" : 8, "user_id" : "aaa", "point" : 12, "created" : ISODate("2020-12-31T15:00:00Z") }
{ "_id" : ObjectId("622d7bda3c53cfb65ce802a6"), "id" : 9, "user_id" : "bbb", "point" : 52, "created" : ISODate("2020-12-31T15:00:00Z") }

プロパティを指定して集計( `$group` )

user_id でグループ化して、「件数」「合計」「平均」「最大」「最小」を算出するには、以下のように指定します。

db.test_collection.aggregate([
  { $group: { _id: "$user_id", count: { $sum: 1 }, sum: { $sum: "$point" }, avg: { $avg: "$point" }, max: { $max: "$point" }, min: { $min: "$point" }} }
])

$group の _id で、グループ化するプロパティを指定します。以下実行結果です。

> db.test_collection.aggregate([
...   { $group: { _id: "$user_id", count: { $sum: 1 }, sum: { $sum: "$point" }, avg: { $avg: "$point" }, max: { $max: "$point" }, min: { $min: "$point" }} }
... ])
{ "_id" : "bbb", "count" : 2, "sum" : 119, "avg" : 59.5, "max" : 67, "min" : 52 }
{ "_id" : "aaa", "count" : 3, "sum" : 86, "avg" : 28.666666666666668, "max" : 53, "min" : 12 }
{ "_id" : "ccc", "count" : 2, "sum" : 96, "avg" : 48, "max" : 62, "min" : 34 }
{ "_id" : "ddd", "count" : 1, "sum" : 22, "avg" : 22, "max" : 22, "min" : 22 }

集計時に条件追加( `$match` )

created が 2021/01/01以降 のドキュメントのみで集計したい場合、以下のように指定します。

db.test_collection.aggregate([
  { $match: { "created": { $gte: new ISODate("2021-01-01T00:00:00+09:00") } } },
  { $group: { _id: "$user_id", count: { $sum: 1 }, sum: { $sum: "$point" }, avg: { $avg: "$point" }, max: { $max: "$point" }, min: { $min: "$point" }} }
])

$match で条件指定しています。以下実行結果です。

> db.test_collection.aggregate([
...   { $match: { "created": { $gte: new ISODate("2021-01-01T00:00:00+09:00") } } },
...   { $group: { _id: "$user_id", count: { $sum: 1 }, sum: { $sum: "$point" }, avg: { $avg: "$point" }, max: { $max: "$point" }, min: { $min: "$point" }} }
... ])
{ "_id" : "ccc", "count" : 1, "sum" : 62, "avg" : 62, "max" : 62, "min" : 62 }
{ "_id" : "aaa", "count" : 2, "sum" : 65, "avg" : 32.5, "max" : 53, "min" : 12 }
{ "_id" : "bbb", "count" : 1, "sum" : 52, "avg" : 52, "max" : 52, "min" : 52 }
{ "_id" : "ddd", "count" : 1, "sum" : 22, "avg" : 22, "max" : 22, "min" : 22 }

集計結果をソート( `$sort` )

$sort でソートできます。

以下、件数でソート(昇順)した結果です。

> db.test_collection.aggregate([
...   { $group: { _id: "$user_id", count: { $sum: 1 }, sum: { $sum: "$point" }, avg: { $avg: "$point" }, max: { $max: "$point" }, min: { $min: "$point" }} }, 
...   { $sort: { count: 1 } }
... ])
{ "_id" : "ddd", "count" : 1, "sum" : 22, "avg" : 22, "max" : 22, "min" : 22 }
{ "_id" : "bbb", "count" : 2, "sum" : 119, "avg" : 59.5, "max" : 67, "min" : 52 }
{ "_id" : "ccc", "count" : 2, "sum" : 96, "avg" : 48, "max" : 62, "min" : 34 }
{ "_id" : "aaa", "count" : 3, "sum" : 86, "avg" : 28.666666666666668, "max" : 53, "min" : 12 }

以下、-1 を指定すると降順になります。

> db.test_collection.aggregate([
...   { $group: { _id: "$user_id", count: { $sum: 1 }, sum: { $sum: "$point" }, avg: { $avg: "$point" }, max: { $max: "$point" }, min: { $min: "$point" }} }, 
...   { $sort: { count: -1 } }
... ])
{ "_id" : "aaa", "count" : 3, "sum" : 86, "avg" : 28.666666666666668, "max" : 53, "min" : 12 }
{ "_id" : "bbb", "count" : 2, "sum" : 119, "avg" : 59.5, "max" : 67, "min" : 52 }
{ "_id" : "ccc", "count" : 2, "sum" : 96, "avg" : 48, "max" : 62, "min" : 34 }
{ "_id" : "ddd", "count" : 1, "sum" : 22, "avg" : 22, "max" : 22, "min" : 22 }