ジム・スタジオの「稼働率」を、予約と会員管理のデータから出す。枠・トレーナー・会員の3つの定義、休講・取り消し・無断欠席・体験の入れ方と、同じデータで数字が変わる例

// ジム・スタジオの「稼働率」を出すための最小のテーブル（node:sqlite）
import { DatabaseSync } from 'node:sqlite';
export function open(file = ':memory:') {
  const db = new DatabaseSync(file);
  db.exec(`
  CREATE TABLE IF NOT EXISTS slots (          -- クラス・パーソナルの枠
    id INTEGER PRIMARY KEY,
    starts_at TEXT NOT NULL,                  -- 日本時間 'YYYY-MM-DD HH:MM'
    minutes INTEGER NOT NULL,
    kind TEXT NOT NULL CHECK (kind IN ('class','personal')),
    trainer TEXT NOT NULL,
    capacity INTEGER NOT NULL CHECK (capacity > 0),
    status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open','cancelled'))  -- cancelled＝休講
  );
  CREATE TABLE IF NOT EXISTS bookings (       -- 予約1件＝1行
    id INTEGER PRIMARY KEY,
    slot_id INTEGER NOT NULL REFERENCES slots(id),
    member_id TEXT NOT NULL,
    is_trial INTEGER NOT NULL DEFAULT 0,      -- 体験の予約
    status TEXT NOT NULL CHECK (status IN ('booked','cancelled','late_cancel','no_show','attended'))
    -- cancelled＝期限内の取り消し / late_cancel＝期限を過ぎた取り消し / no_show＝無断欠席 / attended＝来館
  );
  CREATE TABLE IF NOT EXISTS shifts (         -- トレーナーの勤務
    trainer TEXT NOT NULL, starts_at TEXT NOT NULL, minutes INTEGER NOT NULL
  );
  CREATE TABLE IF NOT EXISTS members (        -- 在籍の期間
    member_id TEXT PRIMARY KEY, joined_on TEXT NOT NULL, left_on TEXT  -- left_on は退会日＝その日から在籍しない日（在籍中は NULL）
  );`);
  return db;
}

// 稼働率の計算。分母と分子に「何を入れ、何を外すか」をSQLに書いておく
// 期間は [from, to)（to の日は含まない）。日時は日本時間の文字列のまま比べる
export function slotUtilization(db, from, to, kind = 'class') {
  return db.prepare(`
    SELECT
      COUNT(*)                                                   AS slots,          -- 休講を除いた枠の数
      SUM(s.capacity)                                            AS seats,          -- 定員の合計（分母）
      SUM(b.reserved)                                            AS reserved,       -- 予約（期限内の取り消しを除く。体験は除く）
      SUM(b.attended)                                            AS attended,       -- 来館（体験は除く）
      SUM(b.trial_attended)                                      AS trial_attended, -- 体験の来館（別に数える）
      ROUND(100.0 * SUM(b.reserved + b.trial_reserved) / SUM(s.capacity), 1) AS booked_pct,   -- 予約の稼働率（体験を含む）
      ROUND(100.0 * SUM(b.attended + b.trial_attended) / SUM(s.capacity), 1) AS attended_pct, -- 来館の稼働率（体験を含む）
      ROUND(100.0 * SUM(b.no_show) / NULLIF(SUM(b.reserved), 0), 1)          AS no_show_pct   -- 会員の予約のうち無断欠席
    FROM slots s
    JOIN (
      SELECT slot_id,
        SUM(is_trial = 0 AND status IN ('booked','late_cancel','no_show','attended')) AS reserved,
        SUM(is_trial = 1 AND status IN ('booked','late_cancel','no_show','attended')) AS trial_reserved,
        SUM(is_trial = 0 AND status = 'attended') AS attended,
        SUM(is_trial = 1 AND status = 'attended') AS trial_attended,
        SUM(is_trial = 0 AND status = 'no_show')  AS no_show
      FROM bookings GROUP BY slot_id
      UNION ALL                                     -- 予約が1件もない枠も、分母には入れる
      SELECT id, 0, 0, 0, 0, 0 FROM slots WHERE id NOT IN (SELECT slot_id FROM bookings)
    ) b ON b.slot_id = s.id
    WHERE s.status = 'open' AND s.kind = ? AND s.starts_at >= ? AND s.starts_at < ?`).get(kind, from, to);
}

// トレーナーの稼働率：勤務の時間のうち、パーソナルで来館があった枠の時間の割合
export function trainerUtilization(db, from, to) {
  return db.prepare(`
    SELECT w.trainer, w.work_min,
      COALESCE(p.used_min, 0) AS used_min,
      ROUND(100.0 * COALESCE(p.used_min, 0) / w.work_min, 1) AS pct
    FROM (SELECT trainer, SUM(minutes) AS work_min FROM shifts
          WHERE starts_at >= ? AND starts_at < ? GROUP BY trainer) w
    LEFT JOIN (SELECT s.trainer, SUM(s.minutes) AS used_min FROM slots s
          WHERE s.kind = 'personal' AND s.status = 'open' AND s.starts_at >= ? AND s.starts_at < ?
            AND EXISTS (SELECT 1 FROM bookings b WHERE b.slot_id = s.id AND b.status = 'attended')
          GROUP BY s.trainer) p ON p.trainer = w.trainer
    ORDER BY w.trainer`).all(from, to, from, to);
}

// 会員の利用率：期間の最終日に在籍していた会員のうち、期間内に1回以上来館した人の割合
export function memberActiveRate(db, from, to) {
  return db.prepare(`
    SELECT COUNT(*) AS members,
      SUM(EXISTS (SELECT 1 FROM bookings b JOIN slots s ON s.id = b.slot_id
                  WHERE b.member_id = m.member_id AND b.status = 'attended' AND b.is_trial = 0
                    AND s.starts_at >= ? AND s.starts_at < ?)) AS active,
      ROUND(100.0 * SUM(EXISTS (SELECT 1 FROM bookings b JOIN slots s ON s.id = b.slot_id
                  WHERE b.member_id = m.member_id AND b.status = 'attended' AND b.is_trial = 0
                    AND s.starts_at >= ? AND s.starts_at < ?)) / COUNT(*), 1) AS pct
    FROM members m
    WHERE m.joined_on < ? AND (m.left_on IS NULL OR m.left_on >= ?)`).get(from, to, from, to, to, to);
}

function setup() {
  const db = open();
  const slot = db.prepare('INSERT INTO slots (id, starts_at, minutes, kind, trainer, capacity, status) VALUES (?, ?, ?, ?, ?, ?, ?)');
  const book = db.prepare('INSERT INTO bookings (slot_id, member_id, is_trial, status) VALUES (?, ?, ?, ?)');
  slot.run(1, '2026-11-02 19:00', 60, 'class', 'A', 10, 'open');
  slot.run(2, '2026-11-03 19:00', 60, 'class', 'A', 10, 'cancelled'); // 休講
  slot.run(3, '2026-11-04 07:00', 60, 'class', 'B', 10, 'open');      // 予約0件
  for (const [i, s] of ['attended', 'attended', 'attended', 'no_show', 'late_cancel', 'cancelled', 'cancelled'].entries()) book.run(1, `m${i}`, 0, s);
  book.run(1, 't1', 1, 'attended');   // 体験
  for (let i = 0; i < 8; i++) book.run(2, `x${i}`, 0, 'booked');      // 休講の枠の予約
  return db;
}

test('期限内の取り消しは予約に数えず、期限後の取り消しと無断欠席は予約に数える', () => {
  const r = slotUtilization(setup(), '2026-11-02', '2026-11-09');
  assert.equal(r.reserved, 5);         // 来館3＋無断欠席1＋期限後の取り消し1
  assert.equal(r.attended, 3);
  assert.equal(r.booked_pct, 30);      // (会員5＋体験1) / 20
  assert.equal(r.attended_pct, 20);    // (来館3＋体験1) / 20
  assert.equal(r.no_show_pct, 20);     // 1 / 5
});

$ node --test --test-reporter=spec metrics.test.mjs
✔ 休講の枠は分母から外し、予約0件の枠は分母に入れる
✔ 期限内の取り消しは予約に数えず、期限後の取り消しと無断欠席は予約に数える
✔ 体験は会員の数字と分けて数える
✔ 期間の終わりの日は含まない
✔ トレーナーの稼働率は、来館のあったパーソナルの時間 ÷ 勤務の時間
✔ 会員の利用率は、期間の最終日に在籍していた会員が分母
ℹ tests 6
ℹ pass 6
ℹ fail 0

$ node demo.mjs
この記事の定義 [Object: null prototype] {
  slots: 27,
  seats: 324,
  reserved: 165,
  attended: 144,
  trial_attended: 9,
  booked_pct: 54,
  attended_pct: 47.2,
  no_show_pct: 4.8
}
期限内の取り消しも予約に数えた場合 63.3
休講の枠も分母に入れた場合 52.1

予約の稼働率（%）  　　　月　　　火　　　水　　　木　　　金　　　土　　　日
07時                 50   50   50   50   42   50   33
10時                 42   42   25   42   33   33   42
19時                 92   83    休  100   92   50   50
21時                 67   67   67   67   42   50   50