Edited at

kintone でSQLを使う

More than 1 year has 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)サブクエリー