mongodb 复杂聚合

╰半橙微兮° 2022-08-05 11:26 76阅读 0赞

我这里有有个需求，首先根据日期进行索引，再按照event不同的值进行计数，查了很多资料才写出来，这里记录一下：

这是数据库中做记录的记录：

{ "_id" : "", "id" : "", "userId" : "", "age" : , "event" : 167,  "time" : ISODate("2015-09-02T03:20:25.114Z") }

按照日期索引，同时匹配不同的event：

执行命令的结果：

{ "_id" : { "date" : "2015-09-16", "event" : 104 }, "eventCount" : 1 }
    { "_id" : { "date" : "2015-09-16", "event" : 101 }, "eventCount" : 6 }
    { "_id" : { "date" : "2015-09-15", "event" : 104 }, "eventCount" : 44 }
    { "_id" : { "date" : "2015-09-15", "event" : 102 }, "eventCount" : 20 }
    { "_id" : { "date" : "2015-09-15", "event" : 101 }, "eventCount" : 11 }
    { "_id" : { "date" : "2015-09-14", "event" : 104 }, "eventCount" : 42 }
    { "_id" : { "date" : "2015-09-14", "event" : 103 }, "eventCount" : 3 }
    { "_id" : { "date" : "2015-09-14", "event" : 102 }, "eventCount" : 135 }
    { "_id" : { "date" : "2015-09-14", "event" : 101 }, "eventCount" : 79 }
    { "_id" : { "date" : "2015-09-13", "event" : 102 }, "eventCount" : 50 }
    { "_id" : { "date" : "2015-09-12", "event" : 104 }, "eventCount" : 6 }
    { "_id" : { "date" : "2015-09-12", "event" : 102 }, "eventCount" : 22 }
    { "_id" : { "date" : "2015-09-12", "event" : 101 }, "eventCount" : 3 }
    { "_id" : { "date" : "2015-09-11", "event" : 106 }, "eventCount" : 2 }
    { "_id" : { "date" : "2015-09-11", "event" : 104 }, "eventCount" : 23 }
    { "_id" : { "date" : "2015-09-11", "event" : 102 }, "eventCount" : 85 }
    { "_id" : { "date" : "2015-09-11", "event" : 101 }, "eventCount" : 67 }
    { "_id" : { "date" : "2015-09-10", "event" : 102 }, "eventCount" : 65 }
    { "_id" : { "date" : "2015-09-10", "event" : 101 }, "eventCount" : 45 }
    { "_id" : { "date" : "2015-09-09", "event" : 104 }, "eventCount" : 5 }

很明显这不是我想要的结果，这里边有很多冗余，再加一个group

db.logs.aggregate(
    	{
    		$project: {
    			event: 1,
    			date: {
    				$substr: ["$time", 0, 10]
    			}
    		}
    	},
    	{
    		$match: {
    			event: { $in: [101, 102, 103, 104,105,106,107,108] }
        }
    	},
        { "$group": {
            "_id": {
                "date": "$date",
                "event": "$event"
            },
            "eventCount": { "$sum": 1 }
        }},
        { "$group": {
            "_id": {
    		      "date": "$_id.date"
    		},
            "counts": { 
                "$push": { 
    				"event": "$_id.event",
                    "count": "$eventCount"
                },
            },
            "count": { "$sum": "$eventCount" }
        }},
        { "$sort": { "_id": -1 } }
    )

再看执行结果：

{ "_id" : { "date" : "2015-09-16" }, "counts" : [ { "event" : 101, "count" : 6 }, { "event" : 104, "count" : 1 } ], "count" : 7 }
    { "_id" : { "date" : "2015-09-15" }, "counts" : [ { "event" : 101, "count" : 11}, { "event" : 102, "count" : 20 }, { "event" : 104, "count" : 44 } ], "count" :75 }
    { "_id" : { "date" : "2015-09-14" }, "counts" : [ { "event" : 101, "count" : 79}, { "event" : 102, "count" : 135 }, { "event" : 104, "count" : 42 }, { "event": 103, "count" : 3 } ], "count" : 259 }
    { "_id" : { "date" : "2015-09-13" }, "counts" : [ { "event" : 102, "count" : 50} ], "count" : 50 }
    { "_id" : { "date" : "2015-09-12" }, "counts" : [ { "event" : 104, "count" : 6 }, { "event" : 102, "count" : 22 }, { "event" : 101, "count" : 3 } ], "count" : 31 }
    { "_id" : { "date" : "2015-09-11" }, "counts" : [ { "event" : 106, "count" : 2 }, { "event" : 104, "count" : 23 }, { "event" : 102, "count" : 85 }, { "event" :101, "count" : 67 } ], "count" : 177 }
    { "_id" : { "date" : "2015-09-10" }, "counts" : [ { "event" : 101, "count" : 45}, { "event" : 102, "count" : 65 } ], "count" : 110 }
    { "_id" : { "date" : "2015-09-09" }, "counts" : [ { "event" : 104, "count" : 5 }, { "event" : 101, "count" : 37 }, { "event" : 102, "count" : 22 } ], "count" :64 }
    { "_id" : { "date" : "2015-09-08" }, "counts" : [ { "event" : 102, "count" : 36}, { "event" : 101, "count" : 16 } ], "count" : 52 }
    { "_id" : { "date" : "2015-09-07" }, "counts" : [ { "event" : 106, "count" : 1 }, { "event" : 104, "count" : 10 }, { "event" : 101, "count" : 30 }, { "event" :102, "count" : 47 } ], "count" : 88 }
    { "_id" : { "date" : "2015-09-06" }, "counts" : [ { "event" : 104, "count" : 19
    }, { "event" : 103, "count" : 1 }, { "event" : 106, "count" : 2 }, { "event" : 101, "count" : 55 }, { "event" : 102, "count" : 77 } ], "count" : 154 }
    { "_id" : { "date" : "2015-09-05" }, "counts" : [ { "event" : 101, "count" : 2 }, { "event" : 104, "count" : 3 }, { "event" : 102, "count" : 24 } ], "count" : 29 }
    { "_id" : { "date" : "2015-09-04" }, "counts" : [ { "event" : 104, "count" : 5 }, { "event" : 102, "count" : 13 }, { "event" : 101, "count" : 13 } ], "count" :31 }
    { "_id" : { "date" : "2015-09-03" }, "counts" : [ { "event" : 101, "count" : 57}, { "event" : 104, "count" : 23 }, { "event" : 102, "count" : 53 }, { "event" :106, "count" : 1 } ], "count" : 134 }
    { "_id" : { "date" : "2015-09-02" }, "counts" : [ { "event" : 103, "count" : 11
    }, { "event" : 102, "count" : 118 }, { "event" : 104, "count" : 29 }, { "event": 106, "count" : 3 }, { "event" : 101, "count" : 72 } ], "count" : 233 }