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}
])
两次分组：按照国家统计UV
先根据国家分组，然后查出每个国家下有多少个去重后的cookie
参考：https://www.coder.work/article/35662
db.getCollection('doc_page_view_log')
.aggregate([
{ $match: { "viewDate":{$gte:1623733217000}, "shopId":"302545389934906860"} },
{ $group: { _id: { countryName: "$countryName", cookie: "$cookie" } }},
{ $group: { _id: { countryName: "$_id.countryName" }, count: { $sum: 1 }} },
{ $sort: { "_id": -1 } }
])
每个页面的平均浏览页面：pv/uv
db.getCollection("doc_page_view_event").aggregate([ { "$match": { "shopId": "648107773959134989", "platform": 0, "date": { "$gte": "2022-01-01", "$lte": "2022-10-01" }, "event": "pageView" } }, { "$group": { "_id": "$url", "sumPv": { "$sum": 1 }, "cookieList": { "$addToSet": "$cookie" } } }, // { $project: { sumPv: 1 , cookieList:1, "cnt":{"$size":"$cookieList"}}}, // { $project: { sumPv: 1 , sumUv:{"$size":"$cookieList"}, avgPage:{$divide:["$sumPv", {"$size":"$cookieList"}]}}}, { $project: { avgPage:{$divide:["$sumPv", {"$size":"$cookieList"}]}}}, { "$sort": { "avgPage": 1 } }, { "$skip": 0 }, { "$limit": 10 } ])
分组后的总数：
db.getCollection('googleSearchTask').distinct("mainUserId",{"createTime" : {$gte:ISODate("2021-12-29 18:04:59.000Z")}})
先分组然后根据count倒序
db.getCollection('doc_product_traffic').aggregate([
{
"$match":{
"productType":3
}
},
{ $group: { _id: "$productId", coun: { $sum: 1 }} },
{$sort:{coun:-1}}
])
