MongoDB – グループ、カウント、ソートの例

getdocs

7年前

いくつかの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 }

完了しました。