mongodb group by

SQL-to-MongoDB2
目前在日常生活偶爾會使用 mongodb 做一些開發或存一些 log,除了基本的 json query 以外,偶爾需要用到如 mysql 的 group 功能,剛開始覺得好像沒有辦法直接用 query 做很困擾,實際做過一次複雜的 query 以後才發現 mongodb 的彈性實在是強大不少,除了語法不相容要重學以外。

舉一個實際的例子,有一個表存了 web 的 access log,然後想找到哪個頁面(page)最常被存取,按照頁面的存取次序排序,每一個人在一天存取同個頁面多次都只能算一次(不重複的工作階段 in GA),而且要排除特定 IP 不被計算(自己)。

在 SQL 裡面直覺寫出來大概會是這樣:

SELECT page, count(*) count FROM access_log 
WHERE IP != '192.168.1.1' 
GROUP BY page
ORDER BY count DESC
limit 10

雖然簡單,但是這個寫法沒有過濾掉,不重複計算同個人的重複瀏覽,所以直覺也是用子查詢才能解決這個問題。

SELECT page, count(*) count FROM 
  (
    SELECT * FROM access_log group by IP, page, date(ts)
  ) a
WHERE IP != '192.168.1.1' 
GROUP BY page
ORDER BY count
limit 10

只是這個 SQL 用了子查詢,或許在某一天在資料過多下,他的效能就會開始低落,追根究底就是我們要處理的資料是一層一層的,而這不是 SQL 擅長的事情,如果需求變更或許要再混用更多子查詢才能解決這個問題。而這種計算則是 mongodb 擅長的事情。

mongodb 的 group 對應的語法是 aggregate,而且有一個 Aggregation Pipeline 的 framework 讓資料處理起來更有威力。

就像 SQL 的 where => group => having => order 就像是 pipeline,只是被依照特定順序安排,你只能在他的框架下依照順序先過濾資料群組資料再過濾再排序,而 Aggregation Pipeline 則沒有限定順序,你可以任意組合這些工具,先 group 在 where 再 group 再 order 聽起來很理想吧?

對應 SQL 的 aggregate 指令為

WHERE,HAVING => $match
GROUP => $group
ORDER => $sort
SELECT => $project

實際寫起來如下,WHERE 一次 GROUP 兩次 ORDER 一次(ㄜ應該不可能 ORDER 很多次吧?)

db.getCollection('access_log').aggregate(
[{
    $match: {IP: {$ne: '192.168.1.1'}}
},
{
 $group: {_id: {page: '$page', ip: '$IP', year: {$year: '$ts'}, month: {$month: '$ts'}, day: {$dayOfMonth: '$ts'}}} 
},
{
 $group: {_id: '$_id.page', count: {$sum: 1}}
},
{
  $sort: {count: -1}
}
])

丟一些測資進去的結果如下

//input 
/* 1 */
{
    "_id" : ObjectId("550d29affbdeb601b2932c73"),
    "page" : "/",
    "IP" : "192.168.1.1",
    "ts" : ISODate("2015-01-01T13:22:20.201Z")
}

/* 2 */
{
    "_id" : ObjectId("550d2a2cfbdeb601b2932c74"),
    "page" : "/home",
    "IP" : "192.168.1.1",
    "ts" : ISODate("2015-01-02T13:23:20.201Z")
}

/* 3 */
{
    "_id" : ObjectId("550d2a33fbdeb601b2932c75"),
    "page" : "/home",
    "IP" : "192.168.1.2",
    "ts" : ISODate("2015-01-02T13:23:20.201Z")
}

/* 4 */
{
    "_id" : ObjectId("550d2a39fbdeb601b2932c76"),
    "page" : "/home",
    "IP" : "192.168.1.2",
    "ts" : ISODate("2015-01-02T13:23:20.201Z")
}

/* 5 */
{
    "_id" : ObjectId("550d2a40fbdeb601b2932c77"),
    "page" : "/a",
    "IP" : "192.168.1.3",
    "ts" : ISODate("2015-01-03T13:24:20.201Z")
}

/* 6 */
{
    "_id" : ObjectId("550d2a47fbdeb601b2932c78"),
    "page" : "/c",
    "IP" : "192.168.1.3",
    "ts" : ISODate("2015-01-02T13:23:20.201Z")
}

/* 7 */
{
    "_id" : ObjectId("550d2a58fbdeb601b2932c79"),
    "page" : "/c",
    "IP" : "192.168.1.3",
    "ts" : ISODate("2015-01-03T13:24:20.201Z")
}
//output
{
    "result" : [ 
        {
            "_id" : "/c",
            "count" : 2.0000000000000000
        }, 
        {
            "_id" : "/home",
            "count" : 1.0000000000000000
        }, 
        {
            "_id" : "/a",
            "count" : 1.0000000000000000
        }
    ],
    "ok" : 1.0000000000000000
}

SQL query 對應 mongodb 的表格 https://nosqlbooster.com/featuresForSQL

One comment on “mongodb group by

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *