tweeeetyのぶろぐ的めも

アウトプットが少なかったダメな自分をアウトプット<br>\(^o^)/

php×mongodbで配列のn番目の要素(index)について集計したいときメモ(find*cursorでゴリゴリ、groupby、aggregate*unwind)

はじめに

mongodbでのデータ集計で
配列のn番目の要素に関して集計したかったときのメモ

特にjsというよりはphp

この記事のサンプルのメインはjavascriptです。
実際はMongoDB PHP ドライバからMongoへアクセスする必要があったので
javascriptで試す

phpに置き換えてバッチとか作成
って流れでやったのでその所感もつづっておきます。

とはいえ文書だと説明しずらいのでまずは事例から

集計したい事例

集計したいデータ

データは以前の記事で1億件つっこんでみたデータと同じものにしました
ただしドキュメント数は100分の1(100万件)ほど。

どんなデータか

ゲームやらなんやらでポイントGETするたびに1ドキュメントinsert
insertされるのはポイントの総計と内訳
内訳は、[GETしたポイント + ボーナスポイント]の配列
ボーナスポイントはGETしたポイント×0.05※消費税みたいな

実際のデータやら構造はこんなん
# mongod --version
db version v2.4.8
Wed May  7 15:44:07.756 git version: a350fc38922fbda2cec8d5dd842237b904eafc14

# mongo
> use sample02
switched to db sample

> db.actlog.find().count()
1266991

> db.actlog.find().sort({"info.ts": -1}).limit(5)
{ "_id" : ObjectId("53620e4fc6e3dd05e7a0e7a4"), "info" : { "uid" : "id08", "ts" : "2014-04-04 09:26:09", "hn" : "sv2" }, "val" : { "total" : 51800952, "add" : [  22,  1 ] } }
{ "_id" : ObjectId("53620e4fc6e3dd05e7a0e7a3"), "info" : { "uid" : "id08", "ts" : "2014-04-04 09:26:08", "hn" : "sv1" }, "val" : { "total" : 51800929, "add" : [  76,  3 ] } }
{ "_id" : ObjectId("53620e4fc6e3dd05e7a0e7a1"), "info" : { "uid" : "id08", "ts" : "2014-04-04 09:26:06", "hn" : "sv2" }, "val" : { "total" : 51800846, "add" : [  99,  4 ] } }
{ "_id" : ObjectId("53620e4fc6e3dd05e7a0e7a2"), "info" : { "uid" : "id08", "ts" : "2014-04-04 09:26:06", "hn" : "sv1" }, "val" : { "total" : 51800850, "add" : [  4,  0 ] } }
{ "_id" : ObjectId("53620e4fc6e3dd05e7a0e79f"), "info" : { "uid" : "id08", "ts" : "2014-04-04 09:26:04", "hn" : "sv2" }, "val" : { "total" : 51800648, "add" : [  39,  1 ] } }

> db.actlog.find().sort({"info.ts": 1}).limit(5)
{ "_id" : ObjectId("53620e2fc6e3dd05e78d9276"), "info" : { "uid" : "id01", "ts" : "2014-03-24 00:00:00", "hn" : "sv2" }, "val" : { "total" : 32, "add" : [  31,  1 ] } }
{ "_id" : ObjectId("53620e2fc6e3dd05e78d9277"), "info" : { "uid" : "id01", "ts" : "2014-03-24 00:00:00", "hn" : "sv2" }, "val" : { "total" : 96, "add" : [  61,  3 ] } }
{ "_id" : ObjectId("53620e30c6e3dd05e78d965e"), "info" : { "uid" : "id02", "ts" : "2014-03-24 00:00:00", "hn" : "sv1" }, "val" : { "total" : 79, "add" : [  76,  3 ] } }
{ "_id" : ObjectId("53620e30c6e3dd05e78d965f"), "info" : { "uid" : "id02", "ts" : "2014-03-24 00:00:00", "hn" : "sv2" }, "val" : { "total" : 110, "add" : [  30,  1 ] } }
{ "_id" : ObjectId("53620e30c6e3dd05e78d9660"), "info" : { "uid" : "id02", "ts" : "2014-03-24 00:00:00", "hn" : "sv1" }, "val" : { "total" : 195, "add" : [  81,  4 ] } }

> db.actlog.getIndexes()
[
        {
                "v" : 1,
                "key" : {
                        "_id" : 1
                },
                "ns" : "sample02.actlog",
                "name" : "_id_"
        }
]

> db.actlog.distinct('info.uid');
[ "id01", "id02", "id05", "id06", "id08" ]

> db.actlog.distinct('info.uid', {"info.ts": /2014-03-25.*/});
[ "id02", "id08" ]

ってことで、まとめるとこのデータはこんな感じになってます

  • sampleデータベースのaclogコレクション
  • aclogコレクションの総ドキュメント数は1,266,991件(約100万件)
  • 2014-03-24~2014-04-04のデータ
  • indexは特に貼ってない
  • ログの対象のid(人)は、id01、id02、id05、id06、id08の5つ
  • 2014-03-25中に限定すると対象idはid02、id08の2つ

  • 補足

    この記事を書いた時点ではMongoDB 2.6がリリース済みなので そっちでやったほうが良いかのかもしれませんが 業務で使っているのが2.4なので同じバージョンで行っています

  • 2.6についてはこちらを参考にさせて頂いてます http://qiita.com/syokenz/items/a0be8a8bc8b79d471ccf

こんな集計がしたい例

このデータにおいて、こんな感じの集計要望があったとします

2014年3月25日に各ユーザが獲得した、ユーザごとのボーナスポイントの総和が知りたい

今回だとval.addに対してindex=1のデータだけを合算すればいいじゃん
って話しになります

しかし、配列のn番目ってどーやってアクセスするの?
ってことで結構悩みました(><)
※そうならないように設計でカバーが一番良いんですが、
※こうなってしまってるデータってことで。。。

これを実現する集計としてこんな3パターンでやってみました

  1. findで絞ってからのcursorをゴリゴリまわして自前集計
  2. group関数を使って集計
  3. aggregateを使って集計

また、最後に

  • aggregateの補足
  • 実行時間を比較

も加えておきました

1. findで絞ってからのcursorをゴリゴリまわして自前集計

findでドキュメントを絞って cursorをforでまわしてゴリゴリ自分で集計する感じです

jsで
  • ソース
  • 結果
# mongo sample02 mongo_script_sum_up_array_normal.js
MongoDB shell version: 2.4.8
connecting to: sample02
"target documents count: 172867"
{ "id02" : 176516, "id08" : 177516 }
php
  • ソース
  • 結果
# php mongo_script_sum_up_array_normal.php
array(2) {
  'id02' =>
  double(176516)
  'id08' =>
  double(177516)
}
  • 所感
    件数多めのドキュメントを集計するようなバッチとしてはきついですね。あたりまえですが。 ちょこっとした集計であれば手っ取りはやいかも?

2. group関数を使って集計

mongodbのgroup関数を使います

jsで
  • ソースで
  • 結果
# mongo sample02 mongo_script_sum_up_array_groupby.js
MongoDB shell version: 2.4.8
connecting to: sample02
[
        {
                "info.uid" : "id02",
                "point" : 176516
        },
        {
                "info.uid" : "id08",
                "point" : 177516
        }
]
php
  • ソース
  • 結果
# php mongo_script_sum_up_array_groupby.php
array(4) {
  'retval' =>
  array(2) {
    [0] =>
    array(2) {
      'info.uid' =>
      string(4) "id02"
      'count' =>
      double(176516)
    }
    [1] =>
    array(2) {
      'info.uid' =>
      string(4) "id08"
      'count' =>
      double(177516)
    }
  }
  'count' =>
  double(172867)
  'keys' =>
  int(2)
  'ok' =>
  double(1)
}
  • 所感
    こちらはphpに置き換える際に jsのcallbackを文字列で書かなければいけないので複雑になると結構だるいです。。
    また下記の点が気になります

    group関数は、実行中、全てのJavaScriptスレッドがブロックされてしまう
    こちらの記事より抜粋

2. aggregateを使って集計

mongodbのaggregate関数を使います

jsで
  • ソース
  • 結果
# mongo sample02 mongo_script_sum_up_array_aggregate.js
MongoDB shell version: 2.4.8
connecting to: sample02
{
        "result" : [
                {
                        "_id" : "id02",
                        "sum" : 176516
                },
                {
                        "_id" : "id08",
                        "sum" : 177516
                }
        ],
        "ok" : 1
}
php
  • ソース
  • 結果
#php mongo_script_sum_up_array_aggregate.php
array(2) {
  'result' =>
  array(2) {
    [0] =>
    array(2) {
      '_id' =>
      string(4) "id02"
      'sum' =>
      double(176516)
    }
    [1] =>
    array(2) {
      '_id' =>
      string(4) "id08"
      'sum' =>
      double(177516)
    }
  }
  'ok' =>
  double(1)
}
  • 所感
    最初、aggregateで配列のn番目へのアクセスでかなり悩みましたが
    unwindを使うことで最初、または、最後への要素はアクセスできました。
    また、めちゃめちゃ早いです!!

aggregateの補足

自分のためにもaggregateの補足をメモっておきます

8行目
array( '$match' => array( "info.ts" => new MongoRegex( "/$date.*/" ) ) ),  

絞込み条件を指定するwhere句です

9行目
array( '$project' => array( "user_id"=> '$info.uid', "values"=>'$val.add'  ) ),  

projectで集計用のフィールド再定義をしてます(別名つけてるだけ)
projectは他にも集計用フィールドの削除や追加もできます

10行目

array( '$unwind' => '$values' ),
最初は使い方がわかりにくかったんですが、unwindを使うことで配列を分解できます

具体的には
こんな感じのドキュメントから

{ 
  "_id" : ObjectId("53620e41c6e3dd05e798861a"), 
  "info" : { "uid" : "id08", "ts" : "2014-03-29 01:07:39", "hn" : "sv2" }, 
  "val" : { "total" : 22966082, "add" : [  43,  2 ] } 
}

こんな感じのドキュメントに分解されます

[
  {
          "_id" : ObjectId("53620e41c6e3dd05e798861a"),
          "user_id" : "id08",
          "values" : 43,
          "ts" : "2014-03-29 01:07:39"
  },
  {
          "_id" : ObjectId("53620e41c6e3dd05e798861a"),
          "user_id" : "id08",
          "values" : 2,
          "ts" : "2014-03-29 01:07:39"
  }
]

_idが同じで配列について分解したドキュメントが2つできました

11~16行目
array(
        '$group' => array (
                '_id' => array( "user_id"  => '$user_id', 'oid'=>'$_id'),
                'values' => array( '$last' => '$values' )
        )
),

unwindで分解したドキュメントをuser_idと_idでグルーピングして
valuesとして最後の要素(上の例で言うvalues=2)を取得してます

17~22行目
array(
        '$group' => array (
                '_id' => '$_id.user_id',
                'sum' => array( '$sum' => '$values' )
        )
)

最後はuser_idについてグルーピングしてvaluesについてsumしてます

実行時間を比較

上記のスクリプトの実行時間を比較してみました
3回程度ですが。。。w

timeコマンドの結果
スクリプト timeコマンド結果
mongo_script_sum_up_array_normal.js 4.12s user 0.09s system 75% cpu 5.560 total
4.22s user 0.09s system 77% cpu 5.576 total
3.85s user 0.16s system 74% cpu 5.363 total
mongo_script_sum_up_array_groupby.js 0.06s user 0.02s system 1% cpu 5.630 total
0.06s user 0.02s system 1% cpu 6.023 total
0.07s user 0.01s system 1% cpu 6.669 total
mongo_script_sum_up_array_aggregate.js 0.07s user 0.01s system 3% cpu 2.830 total
0.07s user 0.01s system 3% cpu 2.761 total
0.07s user 0.01s system 2% cpu 2.871 total
mongo_script_sum_up_array_normal.php 1.29s user 0.05s system 37% cpu 3.545 total
1.27s user 0.04s system 37% cpu 3.485 total
1.28s user 0.04s system 37% cpu 3.577 total
mongo_script_sum_up_array_groupby.php 0.02s user 0.01s system 0% cpu 5.440 total
0.02s user 0.01s system 0% cpu 5.436 total
0.02s user 0.01s system 0% cpu 5.558 total
mongo_script_sum_up_array_aggregate.php 0.02s user 0.01s system 1% cpu 2.606 total
0.02s user 0.01s system 1% cpu 2.553 total
0.02s user 0.01s system 1% cpu 2.691 total

jsでもphpでもaggregateが圧倒的に早いです。
normalはjsもphpも自前でループ集計ですが、phpのほうが言語としての速度が速い感じでしょうか。

結果まとめ
1回目 2回目 3回目 平均
normal.js 5.56 5.576 5.363 5.499666667
groupby.js 5.63 6.023 6.669 6.107333333
aggregate.js 2.83 2.761 2.871 2.820666667
normal.php 3.3545 3.485 3.577 3.472166667
groupby.php 5.44 5.436 5.558 5.478
aggregate.php 2.606 2.553 2.691 2.616666667

まとめ

  • unwindを使うことで配列の最初、または、最後の要素へアクセスできる
    • 配列のn番目へのアクセスはどなたか教えてください(><)
  • aggregateめっちゃ早っ
  • ゴリゴリ自前でまわすときはphpとかのが良いかも

関連する書籍