1日目では、IBM BluemixにNode-REDを導入し、WebSocketでデータを受信して、Ambientというグラフ表示サービスにデータを連携するところまで進みました。
2日目では、受信したデータをDBにとりあえず保存するのと、Webサイトを作るところまで行きました。
2.5日目で、Arduinoを実際に接続してみました。
今回、3日目では、とりあえず保存したデータを読める形にして、利用率を計算するところまで行きたいと思います。

Cloudantに保存したデータを使って統計を取るための設定
Viewの作成
CloudantからNode-REDでデータを取り出す
デフォルトのCloudantノードではviewを使えないぽい
APIを直接叩く
Bluemixの環境設定を読むための設定
取り出したデータを使って利用率を算出する（途中
- 検索クエリの設定
- 利用率の算出方法

##Cloudantに保存したデータを使って統計を取るための設定
###Viewの作成

Cloudantは、いわゆるno-SQLのデータベースで、jsonを投げればとりあえず何でも保存してくれます。そこからテーブルのように情報を整形して表示させるには、Viewを作る必要があるようです。Viewというのは、リレーショナルデータベースのViewと同じ概念だと思います。

BluemixでNode-REDを立ち上げた際には、かならずCloudant DBがセットでついてきます。まず、Bluemixのダッシュボードから、「LAUNCH」を押して、Cloudantの設定画面を開きます。

「Database」を押して、データベースの一覧を表示すると、2日目に適当に名前をつけたDatabase、「iot_data」が存在していることが分かります。

そしたら、Design Documents の右の「＋」マークをクリックして、「New　View」を選択します、

こんな感じで、functionを作りました、これもコードを置いておくのでよかったらコピペしてください。
見れば分かるとおり、payloadの中のchannelごとの情報を一つずつ調べ、channel番号とタイムスタンプの配列をKeyにして、Viewに追加しています。さくらIoT Platformの仕様では、1回の送信で16チャンネル分の情報まで送れるので、複数の値が保存されていることがあるので、普通に読もうとすると大変だったりします。そういった場合でも、forEachを使うことで、1つのDocument(Cloudantに保存されたjsonのかたまり）から複数の行をViewに追加することができます。これは便利。

function (doc) {
   doc.payload.channels.forEach(function(v) {
      emit([v.channel, doc.datetime], v.value);
  });
}

これで、下の緑のボタンを押すと、テーブルっぽくデータが見れるようになります。

さぁ、このViewをつかってNode-REDで・・・と行きたいのですが、思ったより簡単ではありませんでした。続きます。

##Cloudantからデータを取り出す
###デフォルトのCloudantノードではviewを使えないぽい

Node-REDの画面に戻り、cloudantの（検索のほうの）ノードを追加し、設定を開いてみます。

あれ・・・ Viewの検索ができない・・・。

いろいろいじってみたのですが、うまくいきませんでした。
そういうときのためのAPIです。直接叩いてデータをもらっちゃいましょう、

###APIを直接叩く

Cloudantの中身は、APIで簡単に読むことができます。今回作成したViewは、
https://xxx..（超長いURL）..xx-bluemix.cloudant.com/iot_data/_design/Views/_view/channels
にアクセスすることで取得できます。URLは、BluemixダッシュボードのNode-REDの環境変数から取得できます。

実際にSafariでアクセスしてみました。URLを入力すると、赤い背景で、URLにパスワードが含まれていますという警告画面が出ます。びっくりしますが、続行しても大丈夫です。

一旦このURLを直打ちして、結果を表示できるか試してみましょう。
Node-REDで inject→function→http request→debugとノードをつなげて、functionの中で、msg.urlに先ほどのURL, msg.methodにGETを指定します。
これもよかったら下記をコピペしてみてください。

[{"id":"f09115e4.71d7c","type":"inject","z":"396465eb.1d8e22","name":"実行","topic":"","payload":"true","payloadType":"bool","repeat":"","crontab":"","once":false,"x":109,"y":1409,"wires":[["96d60a9c.3ff388"]]},{"id":"96d60a9c.3ff388","type":"function","z":"396465eb.1d8e22","name":"API URL設定","func":"msg.url = \"https://もにゃもにゃ-bluemix:もにゃもにゃ-bluemix.cloudant.com/iot_data/_design/Views/_view/channels\"\nmsg.method = \"GET\";\nreturn msg;","outputs":1,"noerr":0,"x":257.14282417297363,"y":1407.5713195800781,"wires":[["d78c3247.f398b8"]]},{"id":"d78c3247.f398b8","type":"http request","z":"396465eb.1d8e22","name":"","method":"use","ret":"txt","url":"","tls":"","x":429.7142925262451,"y":1408.9996643066406,"wires":[["7dfd3438.b68a94"]]},{"id":"7dfd3438.b68a94","type":"debug","z":"396465eb.1d8e22","name":"","active":true,"console":"false","complete":"payload","x":597,"y":1408,"wires":[]}]

実行してみると、ちゃんと結果が返ってきていることが分かります。

###Bluemixの環境設定を読むための設定

このままURLを直打ちして使い続けるのもよいのですが、せっかく環境設定に入っているので、再利用しやすいように、環境設定を読めるようにします。
how do i get at my vcap variables from node-red?によれば、BluemixのNode-REDではデフォルトでは環境変数を参照できないので、 bluemix-settings.js を編集する必要があるとのこと。
なので、はたまたGitHubのレポジトリに戻ります。
下記のように、52行目にある、functionGlobalContent: { }, のカッコの中に、 process: process と記入し、commitします。
Node-REDが再起動するまで数分待ちます。

Node-REDに戻り、先ほど作成した、functionノードを開き、下記のようにコードを変えます。

bm_env = JSON.parse(context.global.process.env["VCAP_SERVICES"]);
cloudant_url= bm_env.cloudantNoSQLDB[0].credentials.url;
msg.url = cloudant_url + "/iot_data/_design/Views/_view/channels"
msg.method = "GET";
return msg;

もしくは、下記をインポートしてください。

[{"id":"a249c0d2.4e60d","type":"inject","z":"396465eb.1d8e22","name":"実行","topic":"","payload":"true","payloadType":"bool","repeat":"","crontab":"","once":false,"x":112,"y":1536,"wires":[["9360c52a.d3d39"]]},{"id":"9360c52a.d3d39","type":"function","z":"396465eb.1d8e22","name":"API URL設定","func":"bm_env = JSON.parse(context.global.process.env[\"VCAP_SERVICES\"]);\ncloudant_url= bm_env.cloudantNoSQLDB[0].credentials.url;\nmsg.url = cloudant_url + \"/iot_data/_design/Views/_view/channels\"\nmsg.method = \"GET\";\nreturn msg;","outputs":1,"noerr":0,"x":260.14282417297363,"y":1534.5713195800781,"wires":[["43805aab.5905ec"]]},{"id":"43805aab.5905ec","type":"http request","z":"396465eb.1d8e22","name":"","method":"use","ret":"txt","url":"","tls":"","x":432.7142925262451,"y":1535.9996643066406,"wires":[["d35ddabc.6da91"]]},{"id":"d35ddabc.6da91","type":"debug","z":"396465eb.1d8e22","name":"","active":true,"console":"false","complete":"payload","x":600,"y":1535,"wires":[]},{"id":"6c22d320.d18a3c","type":"comment","z":"396465eb.1d8e22","name":"Bluemixの環境設定を参照するバージョン","info":"","x":177,"y":1480,"wires":[]}]

先ほどと同じように実行して、debug画面に結果が表示されることを確認します。

##取り出したデータを使って利用率を算出する

まずはこちらをコピペしてみてください。

[{"id":"a249c0d2.4e60d","type":"inject","z":"396465eb.1d8e22","name":"実行","topic":"","payload":"true","payloadType":"bool","repeat":"","crontab":"","once":false,"x":112,"y":1536,"wires":[["9360c52a.d3d39"]]},{"id":"9360c52a.d3d39","type":"function","z":"396465eb.1d8e22","name":"API URL設定","func":"bm_env = JSON.parse(context.global.process.env[\"VCAP_SERVICES\"]);\ncloudant_url= bm_env.cloudantNoSQLDB[0].credentials.url;\ncloudant_view = \"/iot_data/_design/Views/_view/channels\";\nchannel_id=3;\n\nnow = new Date();\nprev_day = new Date();\nprev_day.setDate(now.getDate() - 1);\nstart_ms=new Date(prev_day.getFullYear(), prev_day.getMonth(), prev_day.getDate(),prev_day.getHours(),0);\nend_ms=new Date(now.getFullYear(), now.getMonth(), now.getDate(),now.getHours(),0);\n\nstart_time =start_ms.toString();\nend_time =end_ms.toString();\nmsg.url = cloudant_url +cloudant_view +\n        '?startkey=['+ channel_id + ',\"' + start_time + '\"]' +\n          '&endkey=['+ channel_id + ',\"' + end_time + '\"]';\nmsg.method = \"GET\";\nmsg.cl_stat = {\n    \"channel_id\":channel_id,\n    \"start_time\":start_time,\n    \"end_time\":end_time\n};\nreturn msg;","outputs":1,"noerr":0,"x":260.14282417297363,"y":1534.5713195800781,"wires":[["43805aab.5905ec"]]},{"id":"43805aab.5905ec","type":"http request","z":"396465eb.1d8e22","name":"","method":"use","ret":"txt","url":"","tls":"","x":432.7142925262451,"y":1535.9996643066406,"wires":[["1e1cf214.6462e6"]]},{"id":"22c110b7.273ce","type":"function","z":"396465eb.1d8e22","name":"統計情報の更新","func":"channel_id=msg.cl_stat.channel_id;\nstart_time=new Date(msg.cl_stat.start_time);\nend_time=new Date(msg.cl_stat.end_time);\n\n\nused_msec = 0;\nlast_closed = null;\n\nmsg.payload.rows.forEach(function( elm ) {\n    // keyに保存された日付をパース\n    dt = new Date(elm.key[1]);\n    \n    if(elm.value===0 && used_msec ===0 && last_closed ===null  ){\n        // ドアが開いた＝利用終了 かつ初エントリ\n        // 利用時間を計算して加算\n        // 初回なのでカウント開始時間（start_time）からの差\n        used_msec = dt - start_time;\n        \n        // 利用時間開始をリセット\n        last_closed = 0;\n        \n    } else if(elm.value===0 && last_closed !== 0 ){\n        // ドアが開いた＝利用終了\n        // 利用時間を計算して加算\n        used_msec += dt - last_closed;\n        \n        // 利用時間開始をリセット\n        last_closed = 0;\n        \n    } else if(elm.value==1 && !last_closed ) {\n        // ドアが閉まった＝利用開始 last_closedが0/null=連続していない１\n        last_closed = dt;\n    }\n    \n});\n\n// もし最後のエントリが１の場合（end_time 時点でドアが閉まっている場合\nif(last_closed){\n    // 利用時間を計算して加算\n    used_msec += end_time - last_closed;\n}\n\nmsg.payload = {\n    \"data_type\" : \"usage_stats\",\n    \"channel_id\" : channel_id,\n    \"start_time\":start_time.toString(),\n    \"end_time\":end_time.toString(),\n    \"used_msec\" : used_msec,\n    \"used_mins\" : used_msec / 1000 / 60,\n    \"usage\" : used_msec /(end_time-start_time)\n};\n\n\nreturn msg;","outputs":1,"noerr":0,"x":390,"y":1615,"wires":[["ce0ab3b.34cfad"]]},{"id":"1e1cf214.6462e6","type":"json","z":"396465eb.1d8e22","name":"","x":226.5,"y":1615,"wires":[["22c110b7.273ce"]]},{"id":"ce0ab3b.34cfad","type":"debug","z":"396465eb.1d8e22","name":"","active":true,"console":"false","complete":"payload","x":571,"y":1615,"wires":[]}]

Cloudant DBへのクエリを作るfunction部分と、その後、jsonパースして、利用率を算出するfunction部分、それぞれ説明します。

クエリの作成部分

bm_env = JSON.parse(context.global.process.env["VCAP_SERVICES"]);
cloudant_url= bm_env.cloudantNoSQLDB[0].credentials.url;
cloudant_view = "/iot_data/_design/Views/_view/channels";
channel_id=3;

now = new Date();
start_ms=new Date(now.getFullYear(), now.getMonth(), now.getDate(),now.getHours()-1,0);
end_ms=new Date(now.getFullYear(), now.getMonth(), now.getDate(),now.getHours(),0);

start_time =start_ms.toString();
end_time =end_ms.toString();
msg.url = cloudant_url +cloudant_view +
        '?startkey=['+ channel_id + ',"' + start_time + '"]' +
          '&endkey=['+ channel_id + ',"' + end_time + '"]';
msg.method = "GET";
msg.cl_stat = {
    "channel_id":channel_id,
    "start_time":start_time,
    "end_time":end_time
};
return msg;

cloudantのキーは、channelと時間の配列になっています。今回は、直近24時間で、どれだけ会議室が利用されていたか（ドアが閉まっていたか）をパーセントで計算しようと思います。
たとえば、今日が1月２日 0時(GMT)で、channel 0 のドアの利用率を計算したいすると、 start_key は [0, 2017-01-01T00:00:00.000Z] , end_key は [0,2017-01-02T00:00:00.000Z] になります。これを、先ほどのCloudant APIのURLの最後に、引数として設定して、クエリを実行します。

利用率の算出部分

ドアが閉まった時間を記録しておいて、ドアが開いたらその時間から逆算して、何分間ドアが閉まっていたかを計算するだけの簡単な仕組みです。

さくらIoT Platformから送られ、Cloudantに保存されているのは、こんなデータです。

ドア番号	日付	ステータス
A	1/1 8:00	closed
B	1/1 8:15	closed
B	1/1 8:45	open
A	1/1 9:00	open

ここから、ドアAは1時間利用されている、ドアBは30分利用されているといった感じで、利用時間を計算する必要があります。
もちろん、０時の時点でドアが閉まっていた場合も考慮しないと行けないです。ステータスがダブって記録された場合も一応考慮しておきます。
あと、今回は、算出対象期間中1回もドアが開閉しなかったケースは考えない(=利用されていなかった）ことにさせてください。

channel_id=msg.cl_stat.channel_id;
start_time=new Date(msg.cl_stat.start_time);
end_time=new Date(msg.cl_stat.end_time);


used_msec = 0;
last_closed = null;

msg.payload.rows.forEach(function( elm ) {
    // keyに保存された日付をパース
    dt = new Date(elm.key[1]);
    
    if(elm.value===0 && used_msec ===0 && last_closed ===null  ){
        // ドアが開いた＝利用終了 かつ初エントリ
        // 利用時間を計算して加算
        // 初回なのでカウント開始時間（start_time）からの差
        used_msec = dt - start_time;
        
        // 利用時間開始をリセット
        last_closed = 0;
        
    } else if(elm.value===0 && last_closed !== 0 ){
        // ドアが開いた＝利用終了
        // 利用時間を計算して加算
        used_msec += dt - last_closed;
        
        // 利用時間開始をリセット
        last_closed = 0;
        
    } else if(elm.value==1 && !last_closed ) {
        // ドアが閉まった＝利用開始 last_closedが0/null=連続していない１
        last_closed = dt;
    }
    
});

// もし最後のエントリが１の場合（end_time 時点でドアが閉まっている場合
if(last_closed){
    // 利用時間を計算して加算
    used_msec += end_time - last_closed;
}

msg.payload = {
    "data_type" : "usage_stats",
    "channel_id" : channel_id,
    "start_time":start_time.toString(),
    "end_time":end_time.toString(),
    "used_msec" : used_msec,
    "used_mins" : used_msec / 1000 / 60,
    "usage" : used_msec /(end_time-start_time)
};


return msg;

実際に実行してみると、無事計算されたようです。たぶん。

あとは、毎日早朝にこのスクリプトを実行するなり、この値をCloudantに保存し直すなり、煮たり焼いたりしていただければ。

そろそろ時間になりましたので、さくらIoT x node-RED on Bluemix で会議室の空き状況を表示させるまでの3日間、これで終わりにしておきます。

何かの参考になればなによりです。

##ご静聴？ありがとうございました。