MongoDB中的一個分組統計的查詢（Group ... Distinct)

資料很簡單，如下：

> db.t2.find();{ "country" : "china", "province" : "sh", "userid" : "a" }{  "country" : "china", "province" : "sh", "userid" : "b" }{  "country" : "china", "province" : "sh", "userid" : "a" }{  "country" : "china", "province" : "sh", "userid" : "c" }{  "country" : "china", "province" : "bj", "userid" : "da" }{  "country" : "china", "province" : "bj", "userid" : "fa" }

需求是統計出每個country/province下的userid的數量（同一個userid只統計一次）

過程如下。

首先試著這樣來統計：

> db.t2.aggregate([ { $group: {"_id": { "country" : "$country", "prov": "$province"} , "number":{$sum:1}} } ])

但是這樣的結果是錯誤的：

{ "_id" : { "country" : "china", "prov" : "bj" }, "number" : 2 }{ "_id" : { "country" : "china", "prov" : "sh" }, "number" : 4 }

原因是，這樣來統計不能區分userid相同的情況 （上面的資料中sh有兩個 userid = a)

為瞭解決這個問題，首先執行一個group，其id 是 country, province, userid三個field：

> db.t2.aggregate([ { $group: {"_id": { "country" : "$country", "province": "$province" , "uid" : "$userid" } } } ])

結果為

{ "_id" : { "country" : "china", "province" : "bj", "uid" : "fa" } }{ "_id" : { "country" : "china", "province" : "bj", "uid" : "da" } }{ "_id" : { "country" : "china", "province" : "sh", "uid" : "c" } }{ "_id" : { "country" : "china", "province" : "sh", "uid" : "b" } }{ "_id" : { "country" : "china", "province" : "sh", "uid" : "a" } }

可以看出，這步的目的是把相同的userid只剩下一個。

然後第二步，再第一步的結果之上再執行統計：

>db.t2.aggregate([         { $group: {"_id": { "country" : "$country", "province": "$province" , "uid" : "$userid" } } } ,{ $group: {"_id": { "country" : "$_id.country", "province": "$_id.province"  }, count : { $sum : 1 }  } }])

這回就對了：

{ "_id" : { "country" : "china", "province" : "sh" }, "count" : 3 }{ "_id" : { "country" : "china", "province" : "bj" }, "count" : 2 }

為了讓結果好看點，加入一個$project操作符，把_id分開：

>db.t2.aggregate([ { $group: {"_id": { "country" : "$country", "province": "$province" , "uid" : "$userid" } } } ,{ $group: {"_id": { "country" : "$_id.country", "province": "$_id.province"  }, count: { $sum : 1 }  } },{ $project : {"_id": 0, "country" : "$_id.country", "province" : "$_id.province", "count" : 1}}])

{ "count" : 3, "country" : "china", "province" : "sh" }{ "count" : 2, "country" : "china", "province" : "bj" }