mongoDb 内嵌文档数据统计

导读

本文档主要介绍mongodb内嵌文档时的统计查询，其涉及统计内容如下，（下述内容暂以统计产品的uv为主题，方便阅读）：
-  统计某产品的uv总数
-  统计某产品在某时段内的总数
-  统计某产品按渠道的总数
-  统计某产品按平台的总数
-  统计某产品按日期的总数
-  统计某产品按日期 ，渠道 ，平台 的总数
-  统计所有产品的uv总数 ，按日期 ，渠道 ，平台等的总数

提示

• mongo version : 4.2.9
• $dateToString ： 该函数存在8小时的时差问题，需要使用 timezone: “+08:00” 参数进行时差补全；

测试数据：

{"name":"p1","uv":[
{"channel":"c1","os":"o1","time":ISODate("2020-08-01 00:00:00")},
{"channel":"c1","os":"o1","time":ISODate("2020-08-02 00:00:00")}
,{"channel":"c2","os":"o2","time":ISODate("2020-08-01 00:00:00")},
{"channel":"c2","os":"o2","time":ISODate("2020-08-02 00:00:00")}
,{"channel":"c1","os":"o2","time":ISODate("2020-08-01 00:00:00")},
{"channel":"c2","os":"o1","time":ISODate("2020-08-01 00:00:00")}]}

{"name":"p2","uv":[
{"channel":"c1","os":"o1","time":ISODate("2020-08-01 00:00:00")},
{"channel":"c1","os":"o1","time":ISODate("2020-08-02 00:00:00")}
,{"channel":"c2","os":"o2","time":ISODate("2020-08-01 00:00:00")},
{"channel":"c2","os":"o2","time":ISODate("2020-08-02 00:00:00")}
,{"channel":"c1","os":"o2","time":ISODate("2020-08-01 00:00:00")},
{"channel":"c2","os":"o1","time":ISODate("2020-08-01 00:00:00")}]}

1. 统计p1的uv总数

aggregate(
[
	{"$match":{"name": "p1"}}
	,{"$project" :{"name": 1 ,"count": {"$size": "$uv"}}}
])

2. 统计按日期分类下p1的uv总数

aggregate(
[
	{"$match":{"name":"p1"}}
	,{"$unwind": "$uv"}
	,{"$project": {"name":1 ,"date":{"$dateToString": { "format": "%Y-%m-%d", "date": "$uv.time", timezone: "+08:00"}} }}
	,{"$group": {"_id":{"date": "$date" ,"name": "$name"},"count": {"$sum": 1}}}
	,{"$project": {"_id":0 ,"name": "$_id.name" ,"date":"$_id.date" ,"count": "$count"}}
	,{"$sort":{"date": -1}}
])

2.1 步骤解析：

1. 条件过滤，定位到指定的p1数据
2. 拆分内嵌文档
3. 提取有效字段，并格式化日期为目标格式
4. 分组统计
5. 整理有效字段
6. 排序处理

3. 统计按渠道分类下p1的uv总数

aggregate(
[
	{"$match":{"name":"p1"}}
	,{"$unwind": "$uv"}
	,{"$group": {"_id":{"channel": "$uv.channel" ,"name": "$name"},"count": {"$sum": 1}}}
	,{"$project": {"_id":0 ,"name": "$_id.name" ,"channel":"$_id.channel" ,"count": "$count"}}
])

4. 统计按平台分类下p1的uv总数

aggregate(
[
	{"$match":{"name":"p1"}}
	,{"$unwind": "$uv"}
	,{"$group": {"_id":{"os": "$uv.os" ,"name": "$name"},"count": {"$sum": 1}}}
	,{"$project": {"_id":0 ,"name": "$_id.name" ,"os":"$_id.os" ,"count": "$count"}}
])

5. 统计按日期、平台、渠道下p1的uv总数

aggregate(
[
	{"$match":{"name":"p1"}}
	,{"$unwind": "$uv"}
	,{"$project": {"name":1 ,"date":{"$dateToString": { "format": "%Y-%m-%d", "date": "$uv.time", timezone: "+08:00"}} ,"os": "$uv.os" ,"channel": "$uv.channel" }}
	,{"$group": {"_id":{"date": "$date" ,"name": "$name" ,"channel": "$channel" ,"os": "$os"},"count": {"$sum": 1}}}
	,{"$project": {"_id":0 ,"name": "$_id.name" ,"date":"$_id.date","channel": "$_id.channel" ,"os": "$_id.os" ,"count": "$count"}}
	,{"$sort":{"date": -1}}
])

6. 统计在某一时段内p1的uv总数

aggregate( 
[ 
	{"$match":{"name": "p1"}} 
	,{"$unwind" :"$uv"} 
	,{"$match": {"uv.time" :{"$gt": ISODate("2020-08-01 00:00:00")}}} 
	,{"$group": {"_id": "$name" ,"count": {"$sum":1}}} 
])

7 ，统计不同名称的总数 ，某一时段总数 ，按日期/平台/渠道分类的总数

=> 提取1-6中的p1筛选条件即可 

7.1 示例，不同名称的总数：

aggregate(
[
	{"$project" :{"name": 1 ,"count": {"$size": "$uv"}}}
])

=> 某一时段的总数

aggregate( 
[ 
	{"$unwind" :"$uv"} 
	,{"$match": {"uv.time" :{"$gt": ISODate("2020-08-01 00:00:00")}}} 
	,{"$group": {"_id": "$name" ,"count": {"$sum":1}}} 
])

=> 按日期/平台/渠道分类的总数

aggregate(
[
	{"$unwind": "$uv"}
	,{"$project": {"name":1 ,"date":{"$dateToString": { "format": "%Y-%m-%d", "date": "$uv.time", timezone: "+08:00"}} ,"os": "$uv.os" ,"channel": "$uv.channel" }}
	,{"$group": {"_id":{"date": "$date" ,"name": "$name" ,"channel": "$channel" ,"os": "$os"},"count": {"$sum": 1}}}
	,{"$project": {"_id":0 ,"name": "$_id.name" ,"date":"$_id.date","channel": "$_id.channel" ,"os": "$_id.os" ,"count": "$count"}}
	,{"$sort":{"date": -1}}
])