kintone でSQLを使う

ブラウザ上の JavaScript 内 SQL がそれなりに動いたので、kintone でも試してみた。
**結果、用途によれば、kintone でも SQL が使えそうだ！**

※[alasql データベース覚書](http://qiita.com/rex0220/items/d1030b293c1021d28750) 参照

こんな SQL で集計できました。

```
alasql(
"SELECT cust.[会社名], cust.[顧客ランク], SUM(orders.[受注金額]) as [受注金額] \
FROM ? AS cust JOIN ? AS orders ON cust.[会社名] = orders.[顧客名] \
GROUP BY cust.[会社名], cust.[顧客ランク] \
ORDER BY cust.[会社名]", [obj.rs1, obj.rs2]);
```

# 集計元アプリ

とりあえず、顧客と受注金額を集計します。


![2017-02-14_21h04_38.png](https://qiita-image-store.s3.amazonaws.com/0/100572/fead128b-a2d5-0a82-e2ab-475c96d710f7.png)


# 集計結果

集計は、グリッド表示にしました。
ぱっと見、集計されていそうです。

![2017-02-14_21h09_02.png](https://qiita-image-store.s3.amazonaws.com/0/100572/66fd9026-9c31-d1ef-2777-6db892fbdb1d.png)


# 必要ライブラリ

- [alasql.min.js](http://alasql.org/) : JavaScript で SQL が使えます。
- [handsontable](https://handsontable.com/?_ga=1.73131861.714310381.1484906350) : グリッド表示用

# 集計コード

処理概要

- 顧客情報アプリと案件アプリのレコードを取得
- json 構造を簡単化
- SQL で結合(Join)と集計（Group By）

**SQL で、普通にJoin, Group By が使えるのがすばらしい！**

```
////////////////////////////////////////////////////////////////////////////
// kintone JavaScript: Summary of kintone records by alasql.
//  2017.02.14 by rex0220
////////////////////////////////////////////////////////////////////////////
(function() {
    "use strict";

    // 一覧表示
    kintone.events.on("app.record.index.show", function(event) {
        if (event.viewId !== 5332577) return event;

        var obj = {};
        var custApp = 433;

        // 顧客情報取得
        fetchRecords(custApp, '', ['会社名', '顧客ランク']).then(function(records) {
            obj.rs1 = convertToRows(records);
            console.log('rs1', JSON.stringify(obj.rs1));

            // 注文情報取得
            return fetchRecords(event.appId, '', ['顧客名', '受注金額']);

        }).then(function(records) {
            obj.rs2 = convertToRows(records);
            console.log('rs2', JSON.stringify(obj.rs2));

            // 顧客情報と注文情報を集計
            var result1 = 
                alasql(
                "SELECT cust.[会社名], cust.[顧客ランク], SUM(orders.[受注金額]) as [受注金額] \
                FROM ? AS cust JOIN ? AS orders ON cust.[会社名] = orders.[顧客名] \
                GROUP BY cust.[会社名], cust.[顧客ランク] \
                ORDER BY cust.[会社名]", [obj.rs1, obj.rs2]);

            console.log('result3', JSON.stringify(result1));

            // grid 表示
            var grid = document.getElementById('xp-grid');
            new Handsontable(grid, { 
                data: result1, 
                colWidths: [150, 100, 200],
                colHeaders: ['会社名', '顧客ランク', '受注金額'], 
                columns: [
                    { data: '会社名' },
                    { data: '顧客ランク' },
                    { data: '受注金額', type: 'numeric', format: '0,0' },
                ],
                readOnly: true 
            });

        });

        return event;
    });


    // get records
    function fetchRecords(appId, opt_query, opt_fields, opt_offset, opt_limit, opt_records) {
        var query = opt_query || '';
        var offset = opt_offset || 0;
        var limit = opt_limit || 500;
        var allRecords = opt_records || [];
        var params = {app: appId, query: query + ' limit ' + limit + ' offset ' + offset };
        if (opt_fields) params.fields = opt_fields;
        return kintone.api(kintone.api.url('/k/v1/records', true), 'GET', params).then(function(resp) {
            allRecords = allRecords.concat(resp.records);
            if (resp.records.length === limit) {
                return fetchRecords(appId, query, opt_fields, offset + limit, limit, allRecords);
            }
            return allRecords;
        });
    }

    // records convert to table rows 
    function convertToRows(records) {
        var rows = records.map(function(record){
            var keys = Object.keys(record);
            var row = {};
            keys.map(function(key){
                row[key] = record[key].type === 'NUMBER' ? Number(record[key].value) : record[key].value;
            });
            return row;
        });
        return rows;
    }

})();
```

# 参考

[Alasql JavaScript SQL Database Library: User Manual](http://www.slideshare.net/AndreyGershun/alasql-manual-141220-1?next_slideshow=1)

# あとがき

対象レコード数が少なければ、問題なく使えそうです。
あとは、レコード件数が多い場合にどの程度のパフォーマンスになるか検証要です。

[kintone でSQLを使う（その２）パフォーマンス検証](http://qiita.com/rex0220/items/4db795a920d75c9e4643) に続く
[kintone でSQLを使う（その３）サブクエリー](http://qiita.com/rex0220/items/0c0516d6f08bd47c1a1d)