MongoDB – グループ、カウント、ソートの例
いくつかのMongoDBの例では、group by、count、sortクエリの実行方法を示します。
1.テストデータ
多くのレコードを含む `whois__range`コレクションです。
> db.whois__range.find(); { "__id" : 1, "country" : "us", "source" : "ARIN", "status" : "NEW", "createdDate" : ISODate("2016-05-03T08:52:32.434Z") }, { "__id" : 2, "country" : "us", "source" : "ARIN", "status" : "NEW", "createdDate" : ISODate("2016-05-03T09:52:32.434Z") }, { "__id" : 3, "country" : "cn", "source" : "APNIC", "status" : "NEW", "createdDate" : ISODate("2016-05-03T10:52:32.434Z") }, { "__id" : 4, "country" : "eu", "source" : "RIPE", "status" : "NEW", "createdDate" : ISODate("2016-05-03T10:52:32.434Z") }, {...}
P.S “Source” = RIPE、AFRINIC、KRNIC、LACNIC、APNIC、JPNIC、ARIN
2.グループとカウントの例
“ソース”でグループ化し、 “ソース”の総数を数えます。
> db.whois__range.aggregate([ {"$group" : {__id:"$source", count:{$sum:1}}} ]) { "__id" : "RIPE", "count" : 29270 } { "__id" : "AFRINIC", "count" : 1326 } { "__id" : "KRNIC", "count" : 105 } { "__id" : "LACNIC", "count" : 5889 } { "__id" : "APNIC", "count" : 6644 } { "__id" : "JPNIC", "count" : 167 } { "__id" : "ARIN", "count" : 25429 }
3.複数のIDによるグループ化の例
「ソース」と「ステータス」の2つのIDでグループ化します。
> db.whois__range.aggregate([ {"$group" : {__id:{source:"$source",status:"$status"}, count:{$sum:1}}}]) ]) { "__id" : { "source" : "RIPE", "status" : "NEW" }, "count" : 29260 } { "__id" : { "source" : "RIPE", "status" : "ERROR" }, "count" : 10 } { "__id" : { "source" : "LACNIC", "status" : "NEW" }, "count" : 5889 } { "__id" : { "source" : "KRNIC", "status" : "NEW" }, "count" : 105 } { "__id" : { "source" : "APNIC", "status" : "NEW" }, "count" : 6644 } { "__id" : { "source" : "AFRINIC", "status" : "NEW" }, "count" : 1326 } { "__id" : { "source" : "JPNIC", "status" : "NEW" }, "count" : 167 } { "__id" : { "source" : "ARIN", "status" : "NEW" }, "count" : 25420 } { "__id" : { "source" : "ARIN", "status" : "DONE" }, "count" : 9 }
3.グループ、カウント、ソートの例
3.1「ソース」と「ステータス」の2つのIDでグループ化し、レコードの総数を数え、「ソース」でソートします。
> db.whois__range.aggregate([ {"$group" : {__id:{source:"$source",status:"$status"}, count:{$sum:1}} }, {$sort:{"__id.source":1}}]) { "__id" : { "source" : "AFRINIC", "status" : "NEW" }, "count" : 1326 } { "__id" : { "source" : "APNIC", "status" : "NEW" }, "count" : 6644 } { "__id" : { "source" : "ARIN", "status" : "NEW" }, "count" : 25420 } { "__id" : { "source" : "ARIN", "status" : "DONE" }, "count" : 9 } { "__id" : { "source" : "JPNIC", "status" : "NEW" }, "count" : 167 } { "__id" : { "source" : "KRNIC", "status" : "NEW" }, "count" : 105 } { "__id" : { "source" : "LACNIC", "status" : "NEW" }, "count" : 5889 } { "__id" : { "source" : "RIPE", "status" : "NEW" }, "count" : 29260 } { "__id" : { "source" : "RIPE", "status" : "ERROR" }, "count" : 10 }
3.2降順で「カウント」でソートします。
> db.whois__range.aggregate([ {"$group" : {__id:{source:"$source",status:"$status"}, count:{$sum:1}} }, {$sort:{"count":-1}}]) { "__id" : { "source" : "RIPE", "status" : "NEW" }, "count" : 29260 } { "__id" : { "source" : "ARIN", "status" : "NEW" }, "count" : 25420 } { "__id" : { "source" : "APNIC", "status" : "NEW" }, "count" : 6644 } { "__id" : { "source" : "LACNIC", "status" : "NEW" }, "count" : 5889 } { "__id" : { "source" : "AFRINIC", "status" : "NEW" }, "count" : 1326 } { "__id" : { "source" : "JPNIC", "status" : "NEW" }, "count" : 167 } { "__id" : { "source" : "KRNIC", "status" : "NEW" }, "count" : 105 } { "__id" : { "source" : "RIPE", "status" : "ERROR" }, "count" : 10 } { "__id" : { "source" : "ARIN", "status" : "DONE" }, "count" : 9 }
完了しました。
参考文献
-
https://docs.mongodb.org/manual/aggregation/
[MongoDB集約
マニュアル]。
https://docs.mongodb.org/manual/reference/operator/aggregation/sort/
[MongoDB
ソートマニュアル]。リンク://mongodb/mongodb-aggregate-and-group-example/[MongoDB –
集計とグループの例]