mongodb 复杂聚合

最新推荐文章于 2023-06-03 12:31:32 发布

txxs

最新推荐文章于 2023-06-03 12:31:32 发布

阅读量817

点赞数

分类专栏： mongo 文章标签： mongo 聚合

本文链接：https://blog.csdn.net/maoyeqiu/article/details/48490445

版权

mongo 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

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

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

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

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

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 }
    }},
    { "$sort": { "_id": -1 } }
)

执行命令的结果：

{ "_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 }