An example tutorial for aggregating queries by day in mongodb
Preface
Recently, I encountered a problem when I was writing a project. I used mongodb to record the execution results of the use case, but I used the date format to record the time. Now there is a requirement to count the successful use cases and failed use cases every day in terms of days. When it comes to statistics, aggregate queries are definitely used, but if the time in date format is used as the basis for group, then there is no grouping. Because it is almost impossible to record the use case at the same time, after consulting the relevant documentation today, you can use the $dateToString command of mongodb to accomplish this requirement.
Source of problem
If we use the following data
/ * 1 * / {"_ id": ObjectId ("5d24c09651a456efbc231669"), "time": ISODate ("2019-07-08T10:12:35.125Z"), "result": "Pass"} / * 2 * / {"_ id": ObjectId ("5d24c09e51a456efbc23166a"), "time": ISODate ("2019-07-08T10:12:36.125Z") "result": "Pass"}. / * 10 * / {"_ id": ObjectId ("5d24c0d851a456efbc231672"), "time": ISODate ("2019-07-06T10:10:52.125Z"), "result": "Pass"} / * 11 * / {"_ id": ObjectId ("5d24c0e751a456efbc231673"), "time": ISODate ("2019-07-06T10:10:52.125Z"), "result": "Fail"}
My expected result is
{'_ id': '2019-07-06,' Pass': 1}
{'_ id': '2019-07-06,' Fail': 2}
{'_ id': '2019-07-07,' Pass': 2}
{'_ id': '2019-07-07,' Fail': 1}
{'_ id': '2019-07-08,' Pass': 2}
{'_ id': '2019-07-08,' Fail': 3}
If grouped according to the previous aggregation method, using $time, since each time is different, such aggregation is equivalent to no aggregation.
# coding:utf-8from pymongo import MongoClientclient = MongoClient (host= ['% slug% swatch% ("127.0.0.1", 27017)]) G_mongo = client ['test'] pipeline = [{' $group': {'_ id':'$time', 'count': {' $sum': 1},] for i in G_mongo ['test'] .percent (pipeline): print (I)
The results obtained
{'_ id': datetime.datetime (2019, 7, 6, 10, 10, 32, 125000), 'count': 1}
{'_ id': datetime.datetime (2019, 7, 7, 10, 10, 32, 125000), 'count': 1}
{'_ id': datetime.datetime (2019, 7, 8, 10, 11, 22, 125000), 'count': 1}
{'_ id': datetime.datetime (2019, 7, 6, 10, 10, 52, 125000), 'count': 1}
{'_ id': datetime.datetime (2019, 7, 8, 10, 11, 32, 125000), 'count': 1}
{'_ id': datetime.datetime (2019, 7, 8, 10, 12, 32, 125000), 'count': 1}
{'_ id': datetime.datetime (2019, 7, 7, 10, 11, 22, 125000), 'count': 1}
{'_ id': datetime.datetime (2019, 7, 8, 10, 12, 36, 125000), 'count': 1}
{'_ id': datetime.datetime (2019, 7, 8, 10, 12, 35, 125000), 'count': 1}
{'_ id': datetime.datetime (2019, 7, 7, 10, 10, 22, 125000), 'count': 1}
As you can see, no one is the same because of the time on $time, so if you group $time, each statistic is 1.
The solution of the problem
When grouping, there is a $dateToString instruction that converts the value of the date format into a string. For example, here, because the requirement is to be in days, I convert it to
The string format of% Y-%m-%d. The specific $grouop is as follows
{'$group': {'_ id': {"$dateToString": {'format':'%Y-%m-%d','date':'$time'}},' count': {'$sum': 1}
The documentation for $dateToString can be viewed at https://docs.mongodb.com/manual/reference/operator/aggregation/dateToString/ to briefly introduce a
{$dateToString: {date:, format:, timezone:, onNull:}}
It requires four parameters, only the date parameter is required, specify the data source, format is the converted format, timezone is the time zone, and onNull is the value returned if the date value does not exist.
# coding:utf-8from pymongo import MongoClientclient = MongoClient (host= ["127.0.0.1", 27017)) G_mongo = client ['test'] pipeline = [# {' $group': {'_ id':'$time', 'count': {' $sum': 1}, {'$group': {'_ id': {"$dateToString": {'format':'%Y-%m-%d' 'date':'$time'}},' count': {'$sum': 1}}] for i in G_mongo ['test'] .pipeline: print (I)
The result of the above code execution is as follows
{'_ id': '2019-07-06,' count': 2}
{'_ id': '2019-07-07,' count': 3}
{'_ id': '2019-07-08,' count': 5}
This looks good, but it's a little short of my goal, because it's not yet grouped according to the results of the use case execution, and then sorted in reverse order by day.
# coding:utf-8from pymongo import MongoClientclient = MongoClient (host= ["127.0.0.1", 27017)) G_mongo = client ['test'] pipeline = [# {' $group': {'_ id':'$time', 'count': {' $sum': 1}, {'$group': {'_ id': {'date': {"$dateToString": {' format':'%Y-%m-%d') 'date':'$time'}},' result':'$result'}, 'count': {' $sum': 1}, {'$sort': {"_ id.date":-1}}] for i in G_mongo ['test'] .requests (pipeline): print (I)
The results are as follows
{'_ id': {'date':' 2019-07-08, 'result':' Fail'}, 'count': 3}
{'_ id': {'date':' 2019-07-08, 'result':' Pass'}, 'count': 2}
{'_ id': {'date':' 2019-07-07, 'result':' Pass'}, 'count': 2}
{'_ id': {'date':' 2019-07-07, 'result':' Fail'}, 'count': 1}
{'_ id': {'date':' 2019-07-06, 'result':' Fail'}, 'count': 1}
{'_ id': {'date':' 2019-07-06, 'result':' Pass'}, 'count': 2}
To view the documentation, you can use the $dayOfMonth directive in addition to the $dateToString directive
Pipeline = [{'$group': {'_ id': {'date': {"$dayOfMonth": {' date':'$time'}}, 'result':'$result'},' count': {'$sum': 1}}, {'$sort': {"_ id.date":-1}},]
But this directive can only apply to a single month, if there will be an intersection in two months, such as the rendezvous on July 6 and June 6.
The result above is
{'_ id': {'date': 8,' result': 'Fail'},' count': 3}
{'_ id': {'date': 8,' result': 'Pass'},' count': 2}
{'_ id': {'date': 7,' result': 'Pass'},' count': 2}
{'_ id': {'date': 7,' result': 'Fail'},' count': 1}
{'_ id': {'date': 6,' result': 'Pass'},' count': 2}
{'_ id': {'date': 6,' result': 'Fail'},' count': 1}
Therefore, it is necessary to use various instructions flexibly according to the requirements.
Summary
The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.
