得点集計システムの構築

点数を横長に保持するテーブル設計

CREATE TABLE scores(
  id TEXT PRIMARY KEY, name, 
  s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15
  -- 合計は計算で求めるのでカラムには含めない
);

CREATE TABLE students(sid PRIMARY KEY, name);
CREATE TABLE lectpts(sid, nlec, pts,
	FOREIGN KEY(sid) REFERENCES students(sid));

SELECT sid,sum(pts) FROM lectpts GROUP BY sid;
C110123,140
C110134,150
C110138,80
C110140,148
SELECT l.sid,s.name,sum(l.pts)
FROM lectpts l JOIN students s ON l.sid=s.sid  -- 別名定義: l=lectpts s=students
GROUP BY l.sid;
C110123|公益太郎|140
C110134|飯森花子|150
C110138|高見台一|80
C110140|緑智子|148

SELECT id,s1+s2+s3+s4+s5+s6+s7+s8+s9+s10+s11+s12+s13+s14+s15 FROM scores;

: 初期化
./score.sh -i

: 登録 → C110123の3回目の得点を10点に
./score.sh C110123 3 10

: 削除 → C110123の4回目の記録を削除
./score.sh -d C110123 4

: 集計表示
./score.sh -l
C110123|公益太郎|140
C110134|飯森花子|150
C110138|高見台一|80
C110140|緑智子|148

: 特定学生の点数一覧
./score.sh -l C110123
sid	 1  2  3  4  5  6  7  8  9 10 11 12 13 14 15
C110123	 5  5 10  0  5  0  0  5 10  5  5 10  4 10  6

: 再度初期化
./score.sh -i
./score.sh -l
: 空になる

CREATE TABLE students(sid PRIMARY KEY, name);
CREATE TABLE lectpts(
  sid, nlec, pts,
  UNIQUE(sid, nlec),				-- 制約1
  FOREIGN KEY(sid) REFERENCES students(sid)	-- 制約2
);

.schema sqlite_master
CREATE TABLE sqlite_master (
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);

: ヘッダ出力をONに
.head on

: 存在しない場合は何も出ない
SELECT * FROM sqlite_master WHERE name='students';

: 存在する場合は定義が出る
SELECT * FROM sqlite_master WHERE name='students';
type|name|tbl_name|rootpage|sql
table|students|students|2|CREATE TABLE students(sid PRIMARY KEY, name)

CREATE TABLE までの処理

#!/bin/sh
db=score.sq3
query() {
  sqlite3 -cmd 'PRAGMA foreign_keys=ON' $db "$@"
}


tbl=`query "SELECT name FROM sqlite_master WHERE name='students';"`
if [ -z "$tbl" ]; then		# $tbl が空文字列ならテーブル未定義なので作成
  query<<EOF
CREATE TABLE students(sid PRIMARY KEY, name);
CREATE TABLE lectpts(
  sid, nlec, pts,
  UNIQUE(sid, nlec),
  FOREIGN KEY(sid) REFERENCES students(sid)
);
EOF
  if [ -s students.csv ]; then		# students.csv ファイルが空でなければ
    query<<EOF			# CSVモードに切り替えてインポート
.mode csv
.import students.csv students
EOF
  fi
fi

CREATE TABLE IF NOT EXISTS テーブル定義...

CREATE TABLE IF NOT EXISTS students(sid PRIMARY KEY, name);
CREATE TABLE IF NOT EXISTS lectpts(
  sid, nlec, pts,
  UNIQUE(sid, nlec),
  FOREIGN KEY(sid) REFERENCES students(sid)
);

db=score.sq3
sqlite3 $db "REPLACE INTO lectpts VALUES('$1', $2, $3);"

query "DELETE FROM lectpts WHERE sid='$1' AND nlec=$2;"

SELECT l.sid,s.name,sum(l.pts)
FROM lectpts l JOIN students s ON l.sid=s.sid
GROUP BY l.sid;

version0/score.sh

#!/bin/sh
db=score.sq3
query() {
  sqlite3 -cmd 'PRAGMA foreign_keys=ON' $db "$@"
}
tbl=`query "SELECT name FROM sqlite_master WHERE name='students';"`
if [ -z "$tbl" ]; then		# $tbl が空文字列なら
  query<<EOF
CREATE TABLE students(sid PRIMARY KEY, name);
CREATE TABLE lectpts(
  sid, nlec, pts,
  UNIQUE(sid, nlec),
  FOREIGN KEY(sid) REFERENCES students(sid)
);
EOF
  if [ -s students.csv ]; then		# students.csv ファイルが空でなければ
    query<<EOF			# CSVモードに切り替えてインポート
.mode csv
.import students.csv students
EOF
  fi
fi
if [ x"$1" = x"-d" -a -n "$2" ]; then	# -d オプションなら削除
  query "DELETE FROM lectpts WHERE sid='$1' AND nlec=$2;"
elif [ x"$1" = x"-l" ]; then		# -l オプションなら集計出力
  query<<-EOF
	SELECT l.sid,s.name,sum(l.pts)
	FROM lectpts l JOIN students s ON l.sid=s.sid
	GROUP BY l.sid;
	EOF
elif [ -n "$3" ]; then			# 第3引数まで与えられたら登録
  sqlite3 $db "REPLACE INTO lectpts VALUES('$1', $2, $3);"
fi

./score.sh C110123 1 10
./score.sh C110138 1 5
./score.sh C110123 2 10
./score.sh C110138 2 5
./score.sh C110123 3 8
./score.sh C110138 3 10
./score.sh C110123 4 10
./score.sh C110123 5 8
./score.sh C110138 5 5

sid      1  2  3  4  5
C110138  5  5 10  0  5

sqlite3 -header -column score.sq3
SELECT * FROM lectpts WHERE sid='C110138';
sid         nlec        pts
----------  ----------  ----------
C110138     1           5
C110138     2           5
C110138     3           10
C110138     5           5

SELECT DISTINCT nlec FROM lectpts ORDER BY nlec;
nlec
----------
1
2
3
4
5

nlecs=`sqlite3 score.sq3 "SELECT DISTINCT nlec FROM lectpts ORDER BY nlec;"`

for n in $nlecs; do
  p=`sqlite3 score.sq3 "SELECT pts FROM lectpts WHERE sid='$id' AND nlec=$n";`
  printf ' %2d' ${p:-0}
done

  5  5 10  0  5

×

sqlite3 -header -column score.sq3
SELECT t1.nlec, t2.pts
FROM (SELECT DISTINCT nlec FROM lectpts) t1  -- 講義回のみ抽出して t1 とする
LEFT JOIN
     -- 該当学生のnlec,ptsを抽出して t2 とする
     (SELECT nlec, pts FROM lectpts WHERE sid='C110138') t2
     ON t1.nlec=t2.nlec
ORDER BY t1.nlec;
nlec        pts       
----------  ----------
1           5         
2           5         
3           10        
4                     
5           5         

SELECT t1.nlec, coalesce(t2.pts, 0)
FROM (SELECT DISTINCT nlec FROM lectpts) t1
LEFT JOIN
     (SELECT nlec, pts FROM lectpts WHERE sid='C110138') t2
     ON t1.nlec=t2.nlec
ORDER BY t1.nlec;
nlec        coalesce(t2.pts, 0)
----------  ----------
1           5         
2           5         
3           10        
4           0
5           5         

sqlite3 score.sq3 <<EOF | tr -d '\n'	# trで改行削除
SELECT printf(" %2d", coalesce(t2.pts, 0))
FROM (SELECT DISTINCT nlec FROM lectpts) t1
LEFT JOIN
     (SELECT nlec, pts FROM lectpts WHERE sid='C110138') t2
ON t1.nlec=t2.nlec
ORDER BY t1.nlec;
EOF

  5  5 10  0  5

group_concat(X)
group_concat(X, Y)

sqlite3 score.sq3 <<EOF
SELECT group_concat(printf("%2d", coalesce(t2.pts, 0)), ' ')
FROM (SELECT DISTINCT nlec FROM lectpts) t1
LEFT JOIN
     (SELECT nlec, pts FROM lectpts WHERE sid='C110138') t2
     ON t1.nlec=t2.nlec
ORDER BY t1.nlec;
EOF

DROP TABLE IF EXISTS テーブル...;

DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS lectpts;

score.sh

#!/bin/sh
db=score.sq3
query() {
  sqlite3 -cmd 'PRAGMA foreign_keys=ON' $db "$@"
}

create() {			# テーブルの作成と初期データ登録を行なう関数
  tbl=`query "SELECT name FROM sqlite_master WHERE name='students';"`
  if [ -z "$tbl" ]; then	# $tbl が空文字列なら
    query<<-EOF
	CREATE TABLE students(sid PRIMARY KEY, name);
	CREATE TABLE lectpts(
	  sid, nlec, pts,
	  UNIQUE(sid, nlec),
	  FOREIGN KEY(sid) REFERENCES students(sid)
	);
	EOF
    if [ -s students.csv ]; then	# students.csv ファイルが空でなければ
      query<<-EOF
	.mode csv
	.import students.csv students
	EOF
    fi
  fi
}

drop() {			# テーブル削除を行なう関数
  query<<-EOF
	-- 外部キー制約があるので子テーブルを先に削除
	DROP TABLE IF EXISTS lectpts;
	DROP TABLE IF EXISTS students;
	EOF
  create			# 再初期化
}

delete() {			# レコードの削除を行なう関数
  query "DELETE FROM lectpts WHERE sid='$1' AND nlec=$2;"
}

summary() {			# 集計表を出す関数
  query<<-EOF
	SELECT l.sid,s.name,sum(l.pts)
	FROM lectpts l JOIN students s ON l.sid=s.sid
	GROUP BY l.sid;
	EOF
}

list() {			# 指定した学生の得点リストを出す関数
  # ヘッダを出す
  printf "sid\t"
  query<<-EOF
	SELECT group_concat(printf('%2d', nlec), ' ')
	FROM (SELECT DISTINCT nlec FROM lectpts ORDER BY nlec);
	EOF
  # 続いて $1 に指定した学生の得点を横並びで出す
  printf "$1\t"
  query<<-EOF
	SELECT group_concat(printf('%2d', coalesce(b.pts,0)), ' ')
	FROM (SELECT DISTINCT nlec FROM lectpts ORDER BY nlec) a
	LEFT JOIN (SELECT nlec,pts FROM lectpts WHERE sid='$1') b
	ON a.nlec=b.nlec;
	EOF
}

while getopts ilsd i; do	# i l s d オプションが有効
  case "$i" in
    i)	job=drop ;;		# -i が指定されたら... 以下同様
    l)	job=list ;;
    s)	job=summary ;;
    d)	job=delete ;;
    \?)	exit 1 ;;		# 無効なオプションは異常終了
  esac
done
shift $((OPTIND-1))		# オプション解析の終わった引数をずらす

case $job in
  "")				# オプション指定なしの場合
    if [ -n "$3" ]; then	# 第3引数=点数 まで与えられたら登録処理
      query "REPLACE INTO lectpts VALUES('$1', $2, $3);"
    fi
    ;;
  *)
    $job "$@"
esac

sqlite3 score.sq3 'SELECT * FROM lectpts ORDER BY sid'
C110123|1|10
C110123|2|10
C110123|3|8
C110123|4|10
C110123|5|8
C110138|1|5
C110138|2|5
C110138|3|10
C110138|5|5
sqlite3 score.sq3 'SELECT * FROM students ORDER BY sid'
C110123|公益太郎
C110134|飯森花子
C110138|高見台一
C110140|緑智子

./score.sh -s
C110123|公益太郎|46
C110138|高見台一|25

./score.sh -l C110138
sid      1  2  3  4  5
C110138  5  5 10  0  5

while getopts iLlsd i; do	# i L l s d オプションが有効
  case "$i" in
    i)	job=drop ;;		# -i が指定されたら... 以下同様
    l)	job=list ;;
    L)	job=list2 ;;
    s)	job=summary ;;
    d)	job=delete ;;
    \?)	exit 1 ;;		# 無効なオプションは異常終了
  esac
done

list2() {
  # ヘッダを出す
  printf "氏名\t"
  query<<-EOF
	SELECT group_concat(printf('%2d', nlec), ' ')
	FROM (SELECT DISTINCT nlec FROM lectpts ORDER BY nlec);
	EOF
  # $1 に指定した学生の氏名を取得
  sname=`query "SELECT name FROM students WHERE sid='$1';"`
  printf "$sname\t"
  query<<-EOF
	SELECT group_concat(printf('%2d', coalesce(b.pts,0)), ' ')
	FROM (SELECT DISTINCT nlec FROM lectpts ORDER BY nlec) a
	LEFT JOIN (SELECT nlec,pts FROM lectpts WHERE sid='$1') b
	ON a.nlec=b.nlec;
	EOF
}

./score2.sh -L C110138
氏名     1  2  3  4  5
高見台一         5  5 10  0  5

氏名     	1  2  3  4  5
緑智子	8  9 10 10  7