kintoneのデータをSQL文で加工する

最近kintone漬け。
といっても、新しい機能を追加していくような楽しい作業ではなく、運用中のアプリの不具合対応（デバッグ）と細かい改良ばかりで、少し疲れてきた。

そんな中で、kintoneのデータの加工・集計をラクにやる方法はないものか？ と調べていたら、
kintone でSQLを使う
という記事を発見。
alasqlというJavascript用データベースを使うと、kintoneのデータをSQLで集計できるじゃん。これすごい。
SUM(Group By)で合計値を求めたり、複数のアプリのデータをJoinで組み合わせたりできる。
（逆に言うと、こんな簡単なことがkintone標準機能ではできなかったりするのだが…）

"kintone alasql"でググるとそこそこ記事が出てくるので、もしかしてkintoneガチ勢には当たり前の話？

早速、工数管理アプリに入っている工数明細をプロジェクト単位・個人単位でSUMする画面を作ってみたので、備忘のために記事に残しておく。

元データはこんな感じ。

Figure.1 集計元のkintoneアプリ

設計方針

データは、event.recordsではなくAPIで取得する（event.recordsは最大でも100件しかデータが取れないので、集計目的では使えない）。 APIは生で叩くのではなく、kintone-rest-api-clientを利用。（getAllRecordsWithCursorが最強。みんな使うといいと思う。）

データの表示にはjQuery DataTablesを利用する。DataTablesを使って多機能な一覧画面を作ろう！ – cybozu developer network とかが参考になる。

動作環境

アプリ設定画面の「JavaScript / CSSでカスタマイズ」にて以下URLを指定。

https://js.cybozu.com/jquery/3.4.0/jquery.min.js
https://js.cybozu.com/datatables/v1.10.19/js/jquery.dataTables.min.js
https://unpkg.com/@kintone/rest-api-client@latest/umd/KintoneRestAPIClient.min.js

https://js.cybozu.com/datatables/v1.10.19/css/jquery.dataTables.min.css

また、http://alasql.org/ から alasql.min.js をDLし、PC用のJavaScriptファイルにアップロードしておく（alasqlのzipをダウンロードして解凍し、distディレクトリから取り出せば良い）。

もう1点、DataTablesの見栄えをkintoneに近づけるため、DataTablesをkintoneライクにする魔法のコード - Qiitaからkintone-datatables.cssをDLし、PC用のCSSファイルにアップロードしておくとよい（作者の方に感謝）。

サンプルコード HTML

アプリに一覧「プロジェクト別集計」を作成しておく。
レコード一覧の表示形式を「カスタマイズ」にし、以下のHTMLを記述。

サンプルコード js

/*
 * project_summary.js
 * 工数管理アプリのデータをプロジェクト別・個人別に集計
 * 
 * required: jquery.dataTables.min.js
 * https://js.cybozu.com/datatables/v1.10.19/js/jquery.dataTables.min.js
 * 
 * required: kintone-rest-api-client
 * https://unpkg.com/@kintone/rest-api-client@latest/umd/KintoneRestAPIClient.min.js
 * 
 * requeired: alasql.min.js
 * http://alasql.org/
 */
(function() {
  "use strict";
  kintone.events.on(['app.record.index.show'], function(event){
    if(event.viewName !== 'プロジェクト別集計') return;

    // Initialize kintone-rest-api-client
    const client = new KintoneRestAPIClient();

    var myTable;

    var appId = kintone.app.getId();
    var query = kintone.app.getQuery();
    // APIでデータ取得
    client.record.getAllRecordsWithCursor({
      app: appId,
      query: query,
    }).then(function(response){

      // kintoneのrecords(json)を、alasqlで扱える配列に変換
      function convertToRows(records) {
        var rows = records.map(function(record){
          var keys = Object.keys(record);
          var row = {};
          keys.map(function(key){
            switch (key) {
              case '担当者氏名':
                // '担当者氏名'フィールドからname(=氏名)を取り出す
                row['担当者氏名'] = record[key].value[0]['name'];
                break;
              case '作業担当部署':
                // '作業担当部署'フィールドからname(=部署名)を取り出す
                row['作業担当部署'] = record[key].value[0]['name'];
                break;
              default:
                // 他のフィールドはvalueを取り出す。数値の場合はNumberに変換
                row[key] = record[key].type === 'NUMBER' ? Number(record[key].value) : record[key].value;
            }
          });
          return row;
        });
        return rows;
      }
      var recordSet = convertToRows(response);

      // sum([工数]) by alasql
      var sqlResult = alasql(
          "SELECT [プロジェクトコード], [作業担当部署], [担当者氏名], SUM([工数計_人月]) AS [工数計_人月] FROM ? GROUP BY [プロジェクトコード], [作業担当部署], [担当者氏名]", 
        [recordSet]
      );

      // 表に表示するカラム（alasqlの出力結果の項目名）
      var columns = [
        'プロジェクトコード',
        '作業担当部署',
        '担当者氏名',
        '工数計_人月',
      ];

      // 表のタイトルを好きにいじれるよう、別変数で定義。このサンプルではあまり意味がない。
      var titles = {
        プロジェクトコード: 'プロジェクトコード',
        作業担当部署: '作業担当部署',
        担当者氏名: '担当者氏名',
        工数計_人月: '工数計',
      };

      // DataTables初期化
      myTable = $('#myTable').DataTable({
        // DataTableの設定値はお好みで。
        destroy: true,
        pageLength: 100,
        lengthChange: false,
        searching: false,
        info: false,
        data: sqlResult.map(function(record){
          return columns.reduce(function(data, column){
            data[column] = record[column];
            return data;
          }, {
            レコード番号: record['レコード番号']
          });
        }),
        columns: columns.map(function(column){
          return {
            title: this[column],
            data: column
          }
        }, titles)
      });
    });
  });
})();

動作イメージ

サンプルの動作結果はこんな感じ。

Figure.2 カスタマイズ結果

画像にモザイクがかかりまくっていて何がなんだか分からないが、結果はとても良好。パフォーマンスも今のところ申し分ない。

今回はSUMしか使っていないが、そのうちJoinも使ってみたい。