【kintone】SQLで集計

AlaSQLというライブラリを使えば、kintoneのレコードをSQLで集計できる。

その一例として、本記事ではサンプルアプリのおすすめ機能体験パックで
部署ごとの平均休暇取得日数を集計する。

---

休暇申請アプリに、一覧名が 平均取得日数 のカスタマイズビューを追加して
「ページネーションを表示する」のチェックを外し、以下のHTMLを記述する。

<table id="average"></table>

JavaScript / CSSファイル は下図のように配置。

ライブラリは以下のサイトで入手できる。

• Cybozu CDN
• AlaSQL
• 魔法のCSS

sample.js は以下のコードを使う。

sample.js

(() => {
  "use strict";

  const client = new KintoneRestAPIClient();

  // 全てのレコードを取得する関数
  const fetchAllRecords = async (appId, fields) => {
    const resp = await client.record.getAllRecordsWithCursor({
      app: appId,
      fields: fields,
    });
    return resp;
  };

  // 平均取得日数一覧が表示されたときの処理
  kintone.events.on("app.record.index.show", async (event) => {
    if (event.viewName !== "平均取得日数") return;

    // カスタマイズビューが既に描画されているときの再描画を防止
    if (document.getElementById("average").children.length > 0) return;

    // 休暇申請アプリで全てのレコードの部署と申請内容を取得
    const mainRecords = await fetchAllRecords(kintone.app.getId(), ["部署", "申請内容"]);

    // 社員名簿アプリで全てのレコードの部署名を取得
    const staffRecords = await fetchAllRecords(kintone.app.getLookupTargetAppId("社員番号"),
    ["部署名"]);

    // 各部署の人数をカウント
    const departmentCounts = {};
    staffRecords.forEach((record) => {
      const dept = record.部署名.value;
      departmentCounts[dept] = (departmentCounts[dept] || 0) + 1;
    });

    // 申請内容テーブルから集計対象データ（部署、取得日の年、取得日数）を抽出
    const aggregated = [];
    mainRecords.forEach((record) => {
      const dept = record.部署.value;
      const tableRows = record.申請内容.value;

      tableRows.forEach((row) => {
        const getDate = row.value.取得日.value;
        const days = parseFloat(row.value.取得日数.value || "0");
        if (!getDate || isNaN(days)) return;
        const year = new Date(getDate).getFullYear();

        aggregated.push({
          department: dept,
          year: year,
          days: days,
        });
      });
    });

    // 部署および年ごとに合計取得日数を算出
    const summary = alasql(
      `SELECT department, year, SUM(days) AS totalDays FROM ? GROUP BY department, year`,
      [aggregated]
    );

    // データをDataTableで使える形に整形
    const finalData = summary.map((row) => {
    const count = departmentCounts[row.department];
    // 平均取得日数の表示形式
    const avg = count && count !== 0
      ? (row.totalDays / count).toFixed(2) // 合計取得日数÷部署人数の商を小数第二位まで表示
      : "-"; // 部署人数が0の場合はハイフンを表示（ゼロ除算を回避）

      return {
        部署: row.department,
        年: row.year,
        合計取得日数: row.totalDays,
        部署人数: count || 0,
        平均取得日数: avg,
      };
    });

    // DataTableでカスタマイズビューを描画
    $("#average").DataTable({
      data: finalData,
      columns: [
        { data: "部署", title: "部署" },
        { data: "年", title: "年" },
        { data: "合計取得日数", title: "合計取得日数" },
        { data: "部署人数", title: "部署人数" },
        { data: "平均取得日数", title: "平均取得日数" },
      ],
      paging: false,                    // ページネーションなし
      searching: true,                  // 検索ボックスあり
      info: false,                      // レコード数の表示なし
      order: [[1, "desc"], [0, "asc"]], // 年降順 → 部署昇順 にソート
    });
  });
})();