Davomiyligi bo'yicha MongoDB guruhi

Chiziqli diagramma ma'lumotlarini ko'rsatish uchun yig'ish so'rovim bor.

U quyidagicha:

[
{
    "$match": {
        "Category": {
            "$in": ["Mobile"]
        },
        "StartTime": {
            "$gte": {"$date": "2014-01-29T00:00:00.000Z"},
            "$lt": {"$date": "2014-09-29T00:00:00.000Z"}
        }
    }
},
{
    "$group": {
        "_id": {
            "Category": "$Category", 
            "Country":"$Country",
            "City":"$City",
            "day": {
                "day":{"$dayOfMonth": "$StartTime"},
                "month":{"$month":"$month"},
                "year":{"$year":"$year"}
            }
        },
        "UniqueVisits": {
            "$sum": 1
        },
        "Date": {
            "$first": "$StartTime"
        }
    }
},
{
    "$project": {
        "_id": "$_id.Category",
        "Header": {
            "$concat": [{"$substr": [{"$month": "$Date"}, 0,2]},
                "/",
                {"$substr": [{"$dayOfMonth": "$Date"}, 0, 2]},
                "/",
                {"$substr": [{"$year": "$Date"}, 0, 4]}]
        },
        "Name": {
            "$concat": [
                {
                    "$ifNull": ["$_id.Country","notset"]
                },
                "~",
                {
                    "$ifNull": ["$_id.City","notset"]
                }
            ]
        },
        "UniqueVisits": "$UniqueVisits",            
    }
}
]

Bu yaxshi ishlaydi. Xuddi shunday, menda $hour, $week, $oy yordamida guruhlash orqali soat, hafta va oy bo'yicha chiziqli diagramma bor.

Endi men 0-12 va 12-24 gacha bo'lgan soat davomiyligi va hatto Ertalab (6-12), Peshindan keyin (12-18), Kechqurun (18-24), Tungi (0-) gacha chiziqli diagrammani AM/PM tomonidan qo'shmoqchiman. 6)

Yuqoridagi muddatlar uchun guruhlashni qanday amalga oshirishim mumkinligi haqida har qanday yordam. Oldindan rahmat.

Masalan :

Ma'lumotlar:

{ "_id" : 1, "Date" : ISODate("2014-10-02T19:44:09Z") }
{ "_id" : 2, "Date" : ISODate("2014-10-02T20:44:09Z") }
{ "_id" : 3, "Date" : ISODate("2014-09-03T20:44:09Z") }
{ "_id" : 4, "Date" : ISODate("2014-09-02T20:44:09Z") }
{ "_id" : 5, "Date" : ISODate("2014-09-03T00:00:00Z") }
{ "_id" : 6, "Date" : ISODate("2014-09-03T07:00:00Z") }
{ "_id" : 7, "Date" : ISODate("2014-09-03T14:00:00Z") }
{ "_id" : 8, "Date" : ISODate("2014-10-02T20:47:09Z") }

Har kuni guruhlash uchun AM/PM uchun erishmoqchi bo'lgan narsam quyidagicha:

{ "_id" : "PM", "Count" : 3, "Date" : ISODate("2014-10-02T19:44:09Z") }
{ "_id" : "AM", "Count" : 1, "Date" : ISODate("2014-09-03T00:00:00Z") }
{ "_id" : "AM", "Count" : 4, "Date" : ISODate("2014-09-03T00:00:00Z") }

Ertalab, tushdan keyin, kechqurun, tun uchun esa quyidagicha:

{ "_id" : "Evening", "Count" : 3, "Date" : ISODate("2014-10-02T19:44:09Z") }
{ "_id" : "Evening", "Count" : 1, "Date" : ISODate("2014-09-02T20:44:09Z") }
{ "_id" : "Night", "Count" : 1, "Date" : ISODate("2014-09-03T00:00:00Z") }
{ "_id" : "Morning", "Count" : 1, "Date" : ISODate("2014-09-03T07:00:00Z") }
{ "_id" : "Afternoon", "Count" : 1, "Date" : ISODate("2014-09-03T14:00:00Z") }
{ "_id" : "Evening", "Count" : 1, "Date" : ISODate("2014-09-03T20:44:09Z") }

java mongodb aggregation-framework
person Ninad    schedule 04.11.2014    source manba

Javoblar (1)


arrow_upward
1
arrow_downward

Ushbu qismlarni $group._id ning to'g'ri joyiga qo'yish maqsadingizga erishishi mumkin.

ampm : {
    $cond : {
        "if" : {
            $lt : [ {
                $hour : "$StartTime"
            }, 12 ]
        },
        "then" : "AM", 
        "else" : "PM"
    }
},
segment : {
    $let : {
        "vars" : {
            h : {
                $hour : "$StartTime"
            }
        },
        "in" : {
            $cond : {
                "if" : {
                    $lt: [ "$$h", 6 ]
                },
                "then" : "Night",
                "else" : {
                    $cond : {
                        "if" : {
                            $lt : [ "$$h", 12 ]
                        },
                        "then" : "Morning",
                        "else" : {
                            $cond : {
                                "if" : {
                                    $lt : [ "$$h", 18 ]
                                },
                                "then" : "Afternoon",
                                "else" : "Evening"
                            }
                        }
                    }
                }
            }
        }
    }
}

Tushunish osonroq bo'lishi uchun to'liq matnni shu yerga qo'yaman

[
{
    "$match": {
        "Category": {
            "$in": ["Mobile"]
        },
        "StartTime": {
            "$gte": {"$date": "2014-01-29T00:00:00.000Z"},
            "$lt": {"$date": "2014-09-29T00:00:00.000Z"}
        }
    }
},
{
    "$group": {
        "_id": {
            "Category": "$Category", 
            "Country":"$Country",
            "City":"$City",
            "day": {
                "day":{"$dayOfMonth": "$StartTime"},
                "month":{"$month":"$month"},
                "year":{"$year":"$year"},

                // add this part to group by for each day of each month of each year
                ampm : {
                    $cond : {
                        "if" : {
                            $lt : [ {
                                $hour : "$StartTime"
                            }, 12 ]
                        },
                        "then" : "AM", 
                        "else" : "PM"
                    }
                },
                segment : {
                    $let : {
                        "vars" : {
                            h : {
                                $hour : "$StartTime"
                            }
                        },
                        "in" : {
                            $cond : {
                                "if" : {
                                    $lt: [ "$$h", 6 ]
                                },
                                "then" : "Night",
                                "else" : {
                                    $cond : {
                                        "if" : {
                                            $lt : [ "$$h", 12 ]
                                        },
                                        "then" : "Morning",
                                        "else" : {
                                            $cond : {
                                                "if" : {
                                                    $lt : [ "$$h", 18 ]
                                                },
                                                "then" : "Afternoon",
                                                "else" : "Evening"
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }


            }
        },
        "UniqueVisits": {
            "$sum": 1
        },
        "Date": {
            "$first": "$StartTime"
        }
    }
},
{
    "$project": {
        "_id": "$_id.Category",
        "Header": {
            "$concat": [{"$substr": [{"$month": "$Date"}, 0,2]},
                "/",
                {"$substr": [{"$dayOfMonth": "$Date"}, 0, 2]},
                "/",
                {"$substr": [{"$year": "$Date"}, 0, 4]}]
        },
        "Name": {
            "$concat": [
                {
                    "$ifNull": ["$_id.Country","notset"]
                },
                "~",
                {
                    "$ifNull": ["$_id.City","notset"]
                }
            ]
        },
        "UniqueVisits": "$UniqueVisits",            
    }
}
]
person Wizard    schedule 05.11.2014
comment
Menimcha, u har yilning har oyining har kuni uchun AM/PM uchun emas, balki AM/PM uchun guruhlanadi, to'g'rimi? Ikki guruhli quvur liniyasi yagona variantmi? - person Ninad; 05.11.2014
comment
@Ninad, men bu qismlarni siz bergan $group._id (siz $group._id.day ichiga kiritishingiz mumkin) ga kiritishni aytdim, keyin u har yilning har oyining har kuni uchun ishlaydi. Agar siz butun $group._id ni almashtirsangiz, u boshqa variantlarni e'tiborsiz qoldirmasdan har bir kun uchun guruhlanadi. Men qilgan ishingni takrorlamadim. - person Wizard; 05.11.2014
comment
@Ninad, men javobni yangiladim. Ehtimol, bu yo'lni tushunish osonroqdir. - person Wizard; 05.11.2014
comment
@Ninad, ikkinchi so'rovingizdan $group._id.day ichidagi "Hour": {"$hour": "$Date"}, ni olib tashlang, shunda siz xohlagan narsangizga erishasiz. - person Wizard; 05.11.2014