Help us understand the problem. What is going on with this article?

kintone でSQLを使う

More than 3 years have passed since last update.

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

alasql データベース覚書 参照

こんな 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

集計結果

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

2017-02-14_21h09_02.png

必要ライブラリ

集計コード

処理概要

  • 顧客情報アプリと案件アプリのレコードを取得
  • 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

あとがき

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

kintone でSQLを使う(その2)パフォーマンス検証 に続く
kintone でSQLを使う(その3)サブクエリー

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした