MongoDB 聚合查询 - 按时间分组统计

blog15623885110 · 2017-06-08 11:53 · 1852次阅读

DB search sql: 1.筛选出拥有openId的用户,对这些用户进行按年月日分组,并统计每天新增的用户数

db.user.aggregate(
   [
      { 
        $match: { "openId": { $exists: true } } 
      },
      {
        $group : {
           _id : { month: { $month: "$createDate" }, day: { $dayOfMonth: "$createDate" }, year: { $year: "$createDate" } },
           count: { $sum: 1 }
        }
      }
   ]
)

JAVA PROGRAM Warning : 实现依赖于 Morphia 1.3.2(最新版ORM框架) Mongo-Java-Driver 3.4.2(最新版驱动)

/**
 * Created by lenovo on 2017/6/7.
 */
public class TestCreateUserData {

    public static void main(String[] args) throws UnknownHostException {
        Morphia morphia = new Morphia();
        DatastoreImpl ds = new DatastoreImpl(morphia, new MongoClient("192.168.1.251"), "uap");


        Date date = new Date();
        Date date2 = new Date();
        date.setMonth(3);

        List<Group> groupIds = new ArrayList<>();
        groupIds.add(Group.grouping("day", new Accumulator("$dayOfMonth", new String("createDate"))));
        groupIds.add(Group.grouping("month", new Accumulator("$month", new String("createDate"))));
        groupIds.add(Group.grouping("year", new Accumulator("$year", new String("createDate"))));

        Query<User> query = ds.createQuery(User.class)
                .field("openId").exists()
                .field("createDate").greaterThanOrEq(date)
                .field("createDate").lessThanOrEq(date2);

        AggregationPipeline aggregationPipeline = ds
                .createAggregation(User.class)
                .match(query)
                .group(groupIds, Group.grouping("count", new Accumulator("$sum", new Integer(1))));

        Iterator<Result> iterator = aggregationPipeline.aggregate(Result.class);
        while (iterator.hasNext()) {
            Result result = iterator.next();
            System.out.println(result.getId());
            System.out.println(result.getCount());
        }
    }
}
class Result {

    @Id
    private GroupId id;
    @Property("count")
    private Integer count;

    public String getId() {
        return id.getYear() + "-" + id.getMonth() + "-" + id.getDay();
    }

    public void setId(GroupId id) {
        this.id = id;
    }

    public Integer getCount() {
        return count;
    }

    public void setCount(Integer count) {
        this.count = count;
    }
}

class GroupId {

    private String day;
    private String month;
    private String year;

    public String getDay() {
        return day;
    }

    public void setDay(String day) {
        this.day = day;
    }

    public String getMonth() {
        return month;
    }

    public void setMonth(String month) {
        this.month = month;
    }

    public String getYear() {
        return year;
    }

    public void setYear(String year) {
        this.year = year;
    }
}
收藏

给个赞!

登录后可以进行评论。没有账号?马上注册