학원 출석부 시트를 DB로 옮길 때, 저는 표를 5개로 나눕니다

출석부 시트, 어디서 꼬이나

학원 출석부는 보통 이렇게 생겼습니다. 왼쪽에 반과 학생, 오른쪽으로 날짜가 하루씩 늘어납니다.

반	학생	10/06	10/08	10/13	10/15
중2A	홍길동	O	O	지각	O
중2A	김해민	O	X	O
중2A	오태식	O	O	O	O
중2A	김해민␣				보강
중2B	장첸	X	O	X	o

눈으로 보기엔 좋습니다. 문제는 석 달쯤 지나면 생깁니다.

• 날짜가 열이라서 열이 100개를 넘어갑니다. “이번 달 결석 2회 이상” 같은 질문에 수식이 점점 길어집니다.
• 이름 뒤 공백 하나로 김해민과 김해민␣이 두 사람이 됩니다. 위 표 3행이 그렇습니다.
• 반을 옮기면 학생 줄을 지우거나 복사합니다. 옛 반 기록이 같이 사라지거나 두 군데 남습니다.
• 빈칸이 결석인지, 아직 안 적은 건지 알 수 없습니다.
• 소문자 o 하나가 출석인지 오타인지 사람이 판단해야 합니다.

시트가 나쁜 게 아니라, 시트 한 장이 화면과 장부 역할을 같이 하고 있어서 생기는 일입니다.

표 5개로 나누기

제가 학원 관리 앱을 만들면서 쓰는 출결 뼈대를 설명용으로 줄인 모양입니다. 실제 앱에는 숙제·시험 표가 더 붙지만, 출결은 이 다섯 개로 돌아갑니다.

표	한 줄의 뜻	왜 따로 두나
students	학생 1명	이름 오타·동명이인을 한 곳에서 정리
classes	반 1개	반 이름이 바뀌어도 id는 그대로
enrollments	누가 언제부터 언제까지 어느 반	반 이동해도 옛 기록이 살아 있음
sessions	반 × 날짜 = 수업 1회	휴강·보강일을 수업 단위로 관리
attendance	수업 × 학생 = 출결 1줄	합계·필터·학부모 화면이 전부 여기서 나옴

핵심은 attendance 한 줄이 “이 수업에 이 학생이 이 상태였다” 라는 사실 하나만 담는다는 점입니다. 날짜는 열이 아니라 sessions의 값이 됩니다.

1단계: 시트에서 먼저 세로로 펴기

DB로 가기 전에 시트 안에서 할 일이 있습니다. 가로형 출석부를 반 · 학생 · 날짜 · 상태 네 열로 펴는 겁니다. 이 모양만 돼도 피벗 테이블과 QUERY가 훨씬 쉬워지고, 나중에 DB로 옮길 때 그대로 넣을 수 있습니다.

// 가로형 출석부(날짜가 열) → 한 줄 = 한 학생·한 날짜 기록
// values: getValues() 결과. 1행 = ['반','학생', 날짜1, 날짜2, ...]
function unpivotAttendance(values) {
  var header = values[0];
  var out = [['class', 'student', 'date', 'status']];
  var allowed = { 'O': 'present', '지각': 'late', 'X': 'absent', '보강': 'makeup' };
  for (var r = 1; r < values.length; r++) {
    var cls = String(values[r][0]).trim();
    var student = String(values[r][1]).trim();
    if (!student) continue;
    for (var c = 2; c < header.length; c++) {
      var mark = String(values[r][c]).trim();
      if (mark === '') continue;               // 빈칸은 결석이 아니라 '기록 없음'
      var status = allowed[mark] || 'unknown:' + mark;
      var d = header[c] instanceof Date ? header[c] : new Date(header[c]);
      var date = Utilities.formatDate(d, 'Asia/Seoul', 'yyyy-MM-dd');
      out.push([cls, student, date, status]);
    }
  }
  return out;
}

function runUnpivot() {
  var ss = SpreadsheetApp.getActive();
  var values = ss.getSheetByName('출석부').getDataRange().getValues();
  var rows = unpivotAttendance(values);
  var sh = ss.getSheetByName('attendance_long') || ss.insertSheet('attendance_long');
  sh.clearContents();
  sh.getRange(1, 1, rows.length, 4).setValues(rows);
}

위 출석부 예시를 넣으면 16줄이 나옵니다(Utilities.formatDate만 같은 동작으로 바꿔 Node에서 실행했습니다). 눈여겨볼 결과 세 가지:

• 김해민␣은 trim() 덕분에 김해민으로 합쳐졌습니다. 10/15 보강이 원래 김해민 기록으로 들어갑니다.
• 빈칸은 줄을 만들지 않습니다. “결석”으로 바꿔 넣지 않는 게 일부러입니다.
• 장첸의 소문자 o는 unknown:o로 남았습니다. 조용히 출석 처리하지 않고, 사람이 고칠 수 있게 눈에 띄게 남깁니다.

2단계: D1(SQLite) 스키마

Cloudflare D1은 SQLite 기반이라 아래 SQL을 그대로 씁니다. 같은 파일을 로컬 SQLite와 wrangler d1 execute --local에 둘 다 넣어 봤습니다.

PRAGMA foreign_keys = ON;

CREATE TABLE students (
  id     INTEGER PRIMARY KEY AUTOINCREMENT,
  name   TEXT NOT NULL,
  phone4 TEXT,                       -- 동명이인 구분용 보호자 번호 뒤 4자리
  active INTEGER NOT NULL DEFAULT 1,
  UNIQUE (name, phone4)
);

CREATE TABLE classes (
  id     INTEGER PRIMARY KEY AUTOINCREMENT,
  code   TEXT NOT NULL UNIQUE,       -- '중2A'
  active INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE enrollments (           -- 누가 언제부터 언제까지 어느 반
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  student_id INTEGER NOT NULL REFERENCES students(id),
  class_id   INTEGER NOT NULL REFERENCES classes(id),
  started_on TEXT NOT NULL,          -- 'YYYY-MM-DD'
  ended_on   TEXT                    -- NULL = 지금 다니는 중
);

CREATE TABLE sessions (              -- 반 × 날짜 = 수업 1회
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  class_id     INTEGER NOT NULL REFERENCES classes(id),
  session_date TEXT NOT NULL,
  UNIQUE (class_id, session_date)
);

CREATE TABLE attendance (            -- 수업 × 학생 = 기록 1줄
  session_id INTEGER NOT NULL REFERENCES sessions(id),
  student_id INTEGER NOT NULL REFERENCES students(id),
  status     TEXT NOT NULL CHECK (status IN ('present','late','absent','makeup')),
  note       TEXT,
  PRIMARY KEY (session_id, student_id)
);

테스트 데이터는 학생 5명(홍길동이 두 명, 번호 뒤 4자리만 다름), 반 2개, 반별 수업 4회, 출결 20줄입니다. 장첸은 10/12부터 중2A에서 중2B로 옮긴 것으로 넣었습니다.

3단계: 실제로 돌려 본 질문 4개

시트에서 수식으로 고생하던 질문들이 쿼리 한 개씩이 됩니다. 결과는 SQLite와 D1 로컬이 똑같았습니다.

이번 달 결석 2회 이상인 학생

SELECT s.name, s.phone4, COUNT(*) AS absences
FROM attendance a
JOIN sessions se ON se.id = a.session_id
JOIN students s  ON s.id = a.student_id
WHERE a.status = 'absent'
  AND se.session_date BETWEEN '2026-10-01' AND '2026-10-31'
GROUP BY a.student_id
HAVING COUNT(*) >= 2
ORDER BY absences DESC, s.name;

name	phone4	absences
김해민	5678	2
장첸	1357	2

장첸은 결석 한 번은 중2A, 한 번은 중2B에서 했습니다. 반이 달라도 학생 기준으로 합쳐집니다. 시트에서는 두 반 시트를 따로 세야 하는 부분입니다.

반별 출석률(지각 포함)

SELECT c.code,
       COUNT(*) AS records,
       ROUND(100.0 * SUM(a.status IN ('present','late')) / COUNT(*), 1) AS attend_pct
FROM attendance a
JOIN sessions se ON se.id = a.session_id
JOIN classes c   ON c.id = se.class_id
GROUP BY c.code;

code	records	attend_pct
중2A	14	71.4
중2B	6	83.3

반을 옮긴 학생의 전체 기록

SELECT se.session_date, c.code, a.status
FROM attendance a
JOIN sessions se ON se.id = a.session_id
JOIN classes c   ON c.id = se.class_id
JOIN students s  ON s.id = a.student_id
WHERE s.name = '장첸'
ORDER BY se.session_date;

session_date	code	status
2026-10-06	중2A	absent
2026-10-08	중2A	present
2026-10-13	중2B	present
2026-10-15	중2B	absent

옛 반 기록이 그대로 남아 있습니다. 반 이동은 enrollments에서 옛 줄에 ended_on을 적고 새 줄을 하나 추가하는 게 전부입니다.

10월 15일 기준 반 명단

SELECT c.code, s.name
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN classes c  ON c.id = e.class_id
WHERE '2026-10-15' >= e.started_on
  AND (e.ended_on IS NULL OR '2026-10-15' <= e.ended_on)
ORDER BY c.code, s.name;

code	name
중2A	홍길동
중2A	김해민
중2A	오태식
중2B	홍길동
중2B	장첸

홍길동이 두 반에 다 있는데, 둘은 다른 학생입니다(번호 뒤 4자리가 다름). 이름만으로 구분했다면 여기서 꼬였을 겁니다.

틀린 입력은 들어가기 전에 막힌다

시트는 뭘 넣어도 받아 줍니다. 그게 편하면서도 무서운 점입니다. 이 스키마에 일부러 잘못된 값을 넣어 봤습니다.

정정은 지우고 다시 쓰지 않고 이렇게 합니다. 같은 수업·같은 학생 줄이 있으면 상태만 바꿉니다.

INSERT INTO attendance (session_id, student_id, status)
VALUES (3, 2, 'late')
ON CONFLICT (session_id, student_id) DO UPDATE SET status = excluded.status;

실행 뒤 김해민의 10/13 기록은 late, 전체 줄 수는 그대로 20줄이었습니다. 선생님 두 명이 같은 학생을 동시에 체크해도 줄이 두 개 생기지 않습니다.

자주 하는 실수

시트로 둘까, DB로 옮길까 (제 기준)

이런 상황이면	제 선택
반 몇 개, 입력하는 사람 1~2명	시트 유지. 대신 1단계처럼 세로형으로
선생님 여러 명이 동시에 출결 입력	DB 쪽으로 기움 (중복 방지가 필요)
학부모·학생 화면에서 같은 기록을 보여 줌	DB + 화면 분리
출결 문자·숙제·시험까지 한 기록으로 이어 붙임	DB

정답이라기보다 제가 직접 만들고 써 보면서 정한 선입니다. 시트가 맞는 학원도 많습니다.

같은 기록을 강사·학부모·학생 화면이 함께 쓰는 이유는 아직도 시트에 적고, 카톡에 또 복사해 붙이세요?에 따로 정리했습니다.

학원 출석부를 이렇게 정리하고 싶은데 어디서부터 손댈지 애매하다면, 지금 쓰는 시트 구조를 보내 주세요. 시트로 충분한지, 옮길 때가 됐는지부터 같이 봅니다.