MongoDB语法

MongoDB客户端：
1，NoSQL Manager for MongoDB
2，Robomongo(推荐，好多教程上的语句可以直接使用)
不同的客户端执行语句有差异

 参考：https://docs.mongodb.com/manual/reference/operator/aggregation/count/

一，NoSQL Manager for MongoDB客户端语句

1，id查询使用ObjectId

2，时间查询使用ISODate

{
  "_id" : ObjectId("5b498fb070bb993b335ab1c0"),
  "_class" : "com.onloon.custom.data.model.importdata.DetectiveTask",
  "keywords" : "pear",
  "status" : 0,
  "userId" : "645a828883714b679dc5d004e93dc192",
  "serverLevel" : "1",
  "createDate" : "20180714",
  "createTime" : ISODate("2018-07-14T05:52:48.584Z"),
  "updateTime" : ISODate("2018-07-14T05:52:48.584Z"),
  "countries" : "Russia&&United Kingdom"
}

{"userId":"645a828883714b679dc5d004e93dc192", "_id" : ObjectId("5b498fb070bb993b335ab1c0"),"createTime" : {$gte : ISODate("2018-07-14T05:52:48.584Z")}}

　　

二，Robomongo客户端

1，SUM

按照productType统计pv的总和：

{
"_id" : ObjectId("598bb71370bb9933cf6fa196"),
"_class" : "com.onloon.website.analytics.core.entity.ProductTrafficBean",
"productId" : "83277ed6be2f4e46b6fbdc6866416e94",
"platform" : 0,
"shopId" : "151623511167744942",
"pv" : NumberLong(3),
"uv" : NumberLong(1),
"uvTime" : NumberLong(252186),
"msgProductCount" : 2,
"date" : "2017-08-10",
"productType" : "1",
"cookie" : ""
}

db.getCollection('doc_product_traffic').aggregate([{$group : {_id : "$productType", num_tutorial : {$sum : "$pv"}}}])

按照pageType统计总数：

{
"_id" : ObjectId("5a7d975c01c56936f40d1f27"),
"_class" : "com.onloon.website.analytics.core.entity.PageDirectBean",
"pageId" : "aa123a",
"pageType" : 2,
"redirectUrl" : "www.baidu.com"
}

db.getCollection('pageDirectBean').aggregate([{$group : {_id : "$pageType", num_tutorial : {$sum : 1}}}])

 2，COUNT

{“_ id”：ObjectId（“54936 ... dd0c”），“last_name”：“smith”，“first_name”：“mike”} 
{“_ id”：ObjectId（“54936 ... dd0d”），“last_name”：“smith “，”first_name“：”william“} 
{”_ id“：ObjectId（”54936 ... dd0e“），”last_name“：”smith“，”first_name“：”william“} 
{”_ id“：ObjectId（”54936 ... dd0f“），”last_name“：”smith“，”first_name“：”mark“}

> db.collection.find（{“last_name”：“smith”}）.count（）;> db.collection.distinct（“first_name”，{“last_name”：“smith”}）.length;

 独立站点总IP数:

{
    "_id" : ObjectId("595dada070bb99301b45695a"),
    "_class" : "com.onloon.website.analytics.core.entity.PageViewLogBean",
    "domain" : "baidu.com",
    "url" : "dsds",
    "pageTitle" : "商品详情",
    "pageId" : "3232",
    "cookie" : "aba7981f-332a-421a-8a56-82095ddad5dd",
    "viewDate" : NumberLong(1499311520843),
    "viewFrom" : "0",
    "shopId" : "3232",
    "ip" : "223.93.170.250",
    "platform" : 0,
    "date" : "2017-07-06"
}

db.getCollection('doc_page_view_log').aggregate([{$match:{viewDate:{$lt:1532016000000}}},{$group: {_id: '$ip'} },{$count:"ipcount"}])

侦探次数:

{
    "_id" : ObjectId("5acf4e0170bb9918956ce28d"),
    "_class" : "com.onloon.custom.data.model.importdata.DetectiveTask",
    "keywords" : "phone",
    "status" : 0,
    "userId" : "83fcb7323c9a47de98403be7cedb9433",
    "serverLevel" : "1",
    "createDate" : "20180412",
    "createTime" : ISODate("2018-04-12T12:16:01.885Z"),
    "updateTime" : ISODate("2018-04-12T12:16:01.885Z")
}

db.getCollection('detectiveTask').count({"createTime" : {$lte : ISODate("2018-07-20T00:00:00.584Z")}})

分组查询公共方法：

/**
	 * 处理group时使用流式操作，务必注意顺序
	 * params 查询条件
	 * page  分页信息
	 * sortKey 排序值
	 * direction 升序还是降序
	 * filedsNameList 自定义返回的字段
	 * groupKey 需要去重的字段
	 */
	protected Aggregation getGeneralAggregation(Map<String, Object> params,
												Long pageNumber,
												Long pageSize,
												String sortKey,
												Sort.Direction direction,
												List<String> filedsNameList,
												String groupKey) {
		List<AggregationOperation> aggOptions = new ArrayList<>();
		{ // 添加查询条件
			List<Criteria> criteriaList = new ArrayList<>();
			if (params != null) {
				log.debug("查询参数：" + params);
				for (Map.Entry<String, Object> entry : params.entrySet()) {
					if (entry.getKey() != null && entry.getValue() != null) {
						switch (entry.getValue().getClass().getName()) {
							case "java.util.ArrayList":
							case "java.util.LinkedList":
								criteriaList.add(Criteria.where(entry.getKey()).in(entry.getValue()));
								break;
							default:
								criteriaList.add(Criteria.where(entry.getKey()).is(entry.getValue()));
								break;
						}
					}
				}
			}
			aggOptions.add(Aggregation.match(new Criteria().andOperator(criteriaList.toArray(new Criteria[criteriaList.size()]))));
		}
		{ // 分组
			if (!StringUtils.isEmpty(groupKey)) {
				aggOptions.add(new GroupOperation(Aggregation.fields(groupKey)).first("$$ROOT")
						.as("data"));
			}
		}
		{ // 排序
//			sortKey = "data." + (sortKey == null ? "id" : sortKey);
//			List<Sort.Order> orders = new ArrayList<Sort.Order>();
//			orders.add(new Sort.Order(direction, sortKey));
//			Sort sort = Sort.by(orders);
			Sort sort = new Sort(direction, "data."+sortKey);
			aggOptions.add(Aggregation.sort(sort));
		}
		{ // 分页
			if (pageNumber != null) {
				aggOptions.add(Aggregation.skip((int) (pageNumber > 0 ? (pageNumber - 1) * pageSize : 0)));
				aggOptions.add(Aggregation.limit(pageSize));
			}
		}
		{ // 添加返回field
			if (CollectionUtils.isNotEmpty(filedsNameList)) {
				filedsNameList = filedsNameList.stream()
						.map(str -> "data." + str)
						.collect(Collectors.toList());
				aggOptions.add(Aggregation.project(filedsNameList.toArray(new String[filedsNameList.size()])));
			}
		}
		Aggregation aggregation = Aggregation.newAggregation(aggOptions);
		return aggregation;
	}



Aggregation generalAggregation = getGeneralAggregation(params, Long.valueOf(page.toString()), Long.valueOf(size.toString()), "createTime", Sort.Direction.DESC, Arrays.asList("normalKeywords"), "normalKeywords");
		AggregationResults<String> searchTask = mongoTemplate.aggregate(generalAggregation, "searchTask", String.class);

　　

按照日期分组查询：

按照用户分组：
db.getCollection('dnbSearchLog').aggregate([
	{ $match: { "createdTime" : {$gte : ISODate("2020-03-15 09:18:39.689Z")} } },
    { $group: { _id: "$userId", count: { $sum: 1 }} },
    { $sort: { "_id": -1 } }
])


按照日期分组：
db.getCollection('dnbSearchLog').aggregate([
    { $match: { "createdTime" : {$gte : ISODate("2020-03-15 09:18:39.689Z")} } },
    {
        $project: {
            quantity: 1, createdTime: { $dateToString: { format: "%Y-%m-%d", date: "$createdTime" } },
        }
    },
    { $group: { _id: "$createdTime", count: { $sum: 1 }} },
    { $sort: { "_id": -1 } }
])

分组求和之后再取最大值

db.getCollection('BaseSocialPageMonitor').aggregate([
        {
            "$match":{
                "platformType":1,
                "staticTime":{
                    $gte:ISODate("2018-07-14T05:52:48.584Z"),
                    $lt:ISODate("2020-07-20T05:52:48.584Z")
                },
                "postTotal":{$gte:1}
            }
        },
        {
            "$group":{
                "_id":"$platformAccountId",
                "sumPost":{"$sum":"$postTotal"}
            }
        },
        {
             "$sort":{
                 sumPost:-1    
             }    
        },
        {$limit:1}
    ])

　　

 先分组然后根据count倒序

db.getCollection('doc_product_traffic').aggregate([
    {
            "$match":{
                "productType":3 
            }
    },
    { $group: { _id: "$productId", coun: { $sum: 1 }} },
    {$sort:{coun:-1}}
])