MongoDB の Aggregation Framework を使って PV と UU を同時に集計する

データ

> db.userlog.find({}, {"_id": 0});
{ "user_id" : 1, "path" : "mypage", "timestamp" : ISODate("2013-06-12T15:00:00Z") }
{ "user_id" : 2, "path" : "mypage", "timestamp" : ISODate("2013-06-12T15:00:00Z") }
{ "user_id" : 3, "path" : "mypage", "timestamp" : ISODate("2013-06-12T15:00:00Z") }
{ "user_id" : 4, "path" : "mypage", "timestamp" : ISODate("2013-06-12T15:00:00Z") }
{ "user_id" : 5, "path" : "mypage", "timestamp" : ISODate("2013-06-12T15:00:00Z") }
{ "user_id" : 4, "path" : "home", "timestamp" : ISODate("2013-06-12T16:00:00Z") }
{ "user_id" : 2, "path" : "home", "timestamp" : ISODate("2013-06-12T16:00:00Z") }
{ "user_id" : 4, "path" : "home", "timestamp" : ISODate("2013-06-11T16:00:00Z") }
{ "user_id" : 1, "path" : "login", "timestamp" : ISODate("2013-06-10T16:00:00Z") }
{ "user_id" : 2, "path" : "login", "timestamp" : ISODate("2013-06-10T16:00:00Z") }
{ "user_id" : 3, "path" : "login", "timestamp" : ISODate("2013-06-11T16:00:00Z") }
{ "user_id" : 3, "path" : "login", "timestamp" : ISODate("2013-06-11T17:00:00Z") }
{ "user_id" : 3, "path" : "login", "timestamp" : ISODate("2013-06-11T18:00:00Z") }
{ "user_id" : 4, "path" : "login", "timestamp" : ISODate("2013-06-11T16:00:00Z") }
{ "user_id" : 2, "path" : "mypage", "timestamp" : ISODate("2013-06-11T15:00:00Z") }
{ "user_id" : 2, "path" : "mypage", "timestamp" : ISODate("2013-06-11T16:00:00Z") }

特定日時のPVとUUを集計

Pipeline

db.userlog.aggregate(
    {
        $match: {
            "timestamp" : {
                "$gte" : ISODate("2013-06-12T00:00:00+09:00"), 
                "$lt" : ISODate("2013-06-13T00:00:00+09:00")
            }
        }
    },
    {
        $group: {
            "_id": {
                "path": "$path",
                "user_id": "$user_id"
            },
            "count" : {
                "$sum" : 1
            }
        }
    },
    {
        $group: {
            "_id": {
                "path": "$_id.path"
            },
            "pv": {
                "$sum": "$count"
            }, 
            "uu": {
                "$sum": 1
            }
        }
    },
    {
        $project: {
            "_id": 0,
            "path": "$_id.path", 
            "pv": 1, 
            "uu": 1
        }
    }    
);

結果

{
        "result" : [
                {
                        "pv" : 1,
                        "uu" : 1,
                        "path" : "home"
                },
                {
                        "pv" : 4,
                        "uu" : 2,
                        "path" : "login"
                },
                {
                        "pv" : 2,
                        "uu" : 1,
                        "path" : "mypage"
                }
        ],
        "ok" : 1
}

日別のPVとUUを集計

但し MongoDB 2.4.4 では日付がUTCになってしまう。

[#SERVER-6310] Timezone support in date operators at query time - MongoDB

Pipeline

db.userlog.aggregate(
    {
        $group: {
            "_id": {
                "year": {
                    "$year": "$timestamp"
                },
                "month": {
                    "$month": "$timestamp"
                },
                "day": {
                    "$dayOfMonth": "$timestamp"
                },
                "path": "$path",
                "user_id": "$user_id"
            },
            "count" : {
                "$sum" : 1
            }
        }
    },
    {
        $group: {
            "_id": {
                "year": "$_id.year",
                "month": "$_id.month",
                "day": "$_id.day",
                "path": "$_id.path"
            },
            "pv": {
                "$sum": "$count"
            }, 
            "uu": {
                "$sum": 1
            }
        }
    },
    {
        $project: {
            "_id": 0,
            "year": "$_id.year", 
            "month": "$_id.month", 
            "day": "$_id.day", 
            "path": "$_id.path", 
            "pv": 1, 
            "uu": 1
        }
    }    
);

結果

{ 
        "result" : [
                {
                        "pv" : 2,
                        "uu" : 1,
                        "year" : 2013,
                        "month" : 6,
                        "day" : 11,
                        "path" : "mypage"
                },
                {
                        "pv" : 2,
                        "uu" : 2,
                        "year" : 2013,
                        "month" : 6,
                        "day" : 12,
                        "path" : "home"
                },
                {
                        "pv" : 1,
                        "uu" : 1,
                        "year" : 2013,
                        "month" : 6,
                        "day" : 11,
                        "path" : "home"
                },
                {
                        "pv" : 5,
                        "uu" : 5,
                        "year" : 2013,
                        "month" : 6,
                        "day" : 12,
                        "path" : "mypage"
                },
                {
                        "pv" : 2,
                        "uu" : 2,
                        "year" : 2013,
                        "month" : 6,
                        "day" : 10,
                        "path" : "login"
                },
                {
                        "pv" : 4,
                        "uu" : 2,
                        "year" : 2013,
                        "month" : 6,
                        "day" : 11,
                        "path" : "login"
                }
        ],
        "ok" : 1
}