最近项目用mongdb,作为数据库。要实现抽奖项目,按照收集卡片的多少来排名,显示前15名的用户中奖信息。
cllection 名称: wzl_app_winnerlist 每次收集一张卡片都会有一条记录。
使用自带的命令行客户端聚合语句:
db.getCollection('wzl_app_winnerlist').aggregate([
{$match:{'times':'1'}},
{$group:{'_id'
:{'times':'$times','user_id':'$user_id','user_account':'$user_account'},'count':{$sum:1}}},
{$sort:{'count':-1}},
{$limit:15},
{$project:{'_id':0,'times':'$_id.times','user_id':'$_id.user_id','user_account':'$_id.user_account','count':1}}
]);
其中times,为活动期次,user_id ,为用户id,user_accout,为用户名, count 为统计数量。
$match:过滤条件
$group:分组 按照_id 分组,并查询出数据。
$sort: 排序,按照count 降序排列。
$limit: 限制数量
$project: 聚合,0 不显示,1显示。
最后结果
[
{
"count": 31,
"times": "1",
"user_id": "20",
"user_account": "17812345678"
},
{
"count": 29,
"times": "1",
"user_id": "7",
"user_account": "18123456789"
},
{
"count": 23,
"times": "1",
"user_id": "1",
"user_account": "13524354057"
},
{
"count": 22,
"times": "1",
"user_id": "4",
"user_account": "13412345678"
},
{
"count": 20,
"times": "1",
"user_id": "11",
"user_account": "22222222222"
},
{
"count": 20,
"times": "1",
"user_id": "5",
"user_account": "15026994613"
},
{
"count": 17,
"times": "1",
"user_id": "16",
"user_account": "19193840921"
},
{
"count": 16,
"times": "1",
"user_id": "2",
"user_account": "13333333333"
},
{
"count": 16,
"times": "1",
"user_id": "10",
"user_account": "18412345678"
},
{
"count": 16,
"times": "1",
"user_id": "9",
"user_account": "18312345678"
},
{
"count": 15,
"times": "1",
"user_id": "13",
"user_account": "18012345678"
},
{
"count": 14,
"times": "1",
"user_id": "12",
"user_account": "33333333333"
},
{
"count": 14,
"times": "1",
"user_id": "19",
"user_account": "12345678909"
},
{
"count": 13,
"times": "1",
"user_id": "14",
"user_account": "18123456783"
},
{
"count": 13,
"times": "1",
"user_id": "17",
"user_account": "13234242343"
}
]
java driver 驱动聚合函数操作:
实现以上效果:
public static String queryWinners(HttpServletRequest request,HttpServletResponse response,JsonNode node,String sUserID) throws Exception {
String times = Util.getValue(node, "times") ;
String limitStr = Util.getValue(node, "limit") ;
int limit = -1 ;
if (limitStr!=null&&!"".equals(limitStr)) {
limit = Integer.parseInt(limitStr) ;
}else {
limit = 10 ;
}
if (times==null||"".equals(times)) {
return "{s:1,m:请求参数错误}";
}
//根据期次和用户id 查询 用户id 分组,limit 15 .
DBCollection collection = DBUtil.getCollection(Constant.WZL_APP_WINNERLIST);
DBObject matchBasicDBObjet = new BasicDBObject("$match",new BasicDBObject("times",times));
/* Group操作*/
DBObject groupFields = new BasicDBObject("_id", new BasicDBObject("times", "$times").append("user_id", "$user_id").append("user_account", "$user_account"));
groupFields.put("count", new BasicDBObject("$sum", 1));
DBObject group = new BasicDBObject("$group", groupFields);
// 排序操作
DBObject sortchBasicDBObjet = new BasicDBObject("$sort", new BasicDBObject("count",-1));
DBObject limitBasicDBObjet = new BasicDBObject("$limit",limit);
// project 操作
DBObject projectBasicDBObjet = new BasicDBObject("$project",
new BasicDBObject("_id",0).append("times", "$_id.times")
.append("user_id", "$_id.user_id").append("user_account", "$_id.user_account").append("count", 1));
List<DBObject> list = new ArrayList<DBObject>();
list.add(matchBasicDBObjet);
list.add(group);
list.add(sortchBasicDBObjet);
list.add(limitBasicDBObjet);
list.add(projectBasicDBObjet);
AggregationOutput output = collection.aggregate(list);
CommandResult result = output.getCommandResult();
//方案二
// JsonNode resultnode = JsonUtil.getJson(result.getString("result"));
// logger.info(JsonMapper.toJsonString(resultnode));
logger.info(result.getString("result"));
return result.getString("result");
}