答えの分かる場所でクエリを動かす
SQL を動かして確かめるとは、手で答えを求めた小さなデータで、モデルのクエリを試すことです。読めば見つかる誤りもありますが、見た目は正しい誤りは、動かして初めて見つかります。
モジュール 43 は、人が書いたクエリを結果の行で確かめました。このレッスンはモデルの候補を確かめ、データが正しいものと間違ったものを見分けられるかも問います。
このレッスンのゴール
メモ
答えの分かっているデータで動かして、モデルのクエリを確かめることができる。
キーワード
| ことば | いみ |
|---|---|
| 分かっている答え | 手で求めた結果 |
| 候補 | 答えとして出された一つのクエリ |
| 結果の行 | クエリが返すもの |
| フィクスチャ | 確かめるために取っておく、小さな決まったデータ |
| 見分ける | 正しいものと間違ったもので、違う結果を出す |
学生が入力するセルは、このプレビューでは空白です。
分かっている答えと行を比べる
小さなフィクスチャで、答えを手で求めます。貸し出し九件なら、紙の上で数えられます。次に、候補を一つずつ動かし、その行を自分の答えと比べます。
判定は三つです。合格は、行が一致したという意味です。行が違うは、動いたけれど別のものを返したという意味です。エラーは、SQLite が受け付けなかったという意味です。
例: 図書館の分かっている答え
| 質問 | 手で求めたしかた | 分かっている答え |
|---|---|---|
| 3 月に借りた会員の人数 | 貸し出し 1 から 4: 上田、上田、佐藤、高橋 | 3 |
| まだ貸し出し中の本 | 貸し出し 3、5、6、8、9 が returned = 0 | 5 |
| 会員 2 が借りた題名 | 貸し出し 3、6、8: 本 3、4、5 | 雨の地図、静かなエンジン、七つのやかん |
学生が入力するセルは、このプレビューでは空白です。
順は質問が求めるときだけ大事
「どの題名か」には、どんな順の題名の一覧でも答えられます。両方の一覧を並べ替えたコピーで比べれば、順が違っても合格します。
質問が「新しいものから」と言うなら、順も答えの一部です。そのときは、行を来たとおりの順で比べます。
データが正誤を見分けられること
5 月の貸し出しのうち、まだ貸し出し中のものは何件かを聞くとします。5 月の貸し出しは二件とも貸し出し中なので、returned の条件がないクエリも 2 を返します。
このフィクスチャでは、二つを見分けられません。5 月の返却済みの貸し出しのように、二つを分ける行を一つ足し、もう一度確かめます。
よくある間違い
注意
テスト用のデータで合格した候補は正しい、と考える人が多いです。正しいと言えるのは、そのデータについてだけです。ありがちな間違ったクエリでフィクスチャが別の答えを出すと確かめてから、合格を信用しましょう。
助っ人 model.js を書く、その 1
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'const base = process.env.AI_URL || \"http://ai:4000\";' '// 応答のトークン数。なければ null。' 'function count(data, name) {' ' return data.usage && typeof data.usage[name] === \"number\" ? data.usage[name] : null;' '}' '// 応答がクエリを囲みで包んでいたら、内側だけを残す。' 'function sqlOf(text) {' ' const parts = text.split(String.fromCharCode(96).repeat(3));' ' const inner = (parts.length > 2 ? parts[1] : text).trim();' ' return inner.toLowerCase().startsWith(\"sql\") ? inner.slice(3).trim() : inner;' '}' > model.js"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
助っ人 model.js を書く、その 2
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'async function ask(messages, sample, max) {' ' try {' ' const res = await fetch(base + \"/v1/chat/completions\", {' ' method: \"POST\",' ' headers: { \"content-type\": \"application/json\", authorization: \"Bearer lab\" },' ' body: JSON.stringify({ model: \"lab\", messages: messages, max_tokens: max || 100 }),' ' signal: AbortSignal.timeout(30000),' ' });' ' if (res.ok === false) throw new Error(\"status \" + res.status);' ' const data = await res.json();' ' const text = data.choices[0].message.content;' ' return { text, inTokens: count(data, \"prompt_tokens\"), outTokens: count(data, \"completion_tokens\"), recorded: false };' ' } catch (error) {' ' console.log(\"記録済みサンプル: モデルが答えませんでした (\" + error.message + \")。\");' ' return { text: sample, inTokens: null, outTokens: null, recorded: true };' ' }' '}' 'module.exports = { ask, sqlOf };' >> model.js"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
小さな図書館のデータを書く、その 1
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'const { DatabaseSync } = require(\"node:sqlite\");' '// 作り話の図書館: 本が六冊、会員が四人、貸し出しが九件。' 'const BOOKS = [[1, \"塩の道\", 2019], [2, \"ガラスの港\", 2021], [3, \"雨の地図\", 2015],' ' [4, \"静かなエンジン\", 2023], [5, \"七つのやかん\", 2018], [6, \"正午の北\", 2022]];' 'const MEMBERS = [[1, \"上田\"], [2, \"佐藤\"], [3, \"高橋\"], [4, \"中村\"]];' '// id, book_id, member_id, month, returned(1 は返却済み、0 は貸し出し中)' 'const LOANS = [[1, 1, 1, 3, 1], [2, 2, 1, 3, 1], [3, 3, 2, 3, 0], [4, 1, 3, 3, 1], [5, 1, 1, 4, 0],' ' [6, 4, 2, 4, 0], [7, 2, 3, 4, 1], [8, 5, 2, 5, 0], [9, 2, 4, 5, 0]];' > library.js"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
小さな図書館のデータを書く、その 2
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'function put(db, table, rows) {' ' const marks = rows[0].map(function () { return \"?\"; }).join(\", \");' ' const insert = db.prepare(\"INSERT INTO \" + table + \" VALUES (\" + marks + \")\");' ' rows.forEach(function (row) { insert.run(...row); });' '}' 'function open(file) {' ' const db = new DatabaseSync(file || \":memory:\");' ' db.exec(\"CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, year INTEGER)\");' ' db.exec(\"CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT)\");' ' db.exec(\"CREATE TABLE loans (id INTEGER PRIMARY KEY, book_id INTEGER REFERENCES books(id), \"' ' + \"member_id INTEGER REFERENCES members(id), month INTEGER, returned INTEGER)\");' ' put(db, \"books\", BOOKS);' ' put(db, \"members\", MEMBERS);' ' put(db, \"loans\", LOANS);' ' return db;' '}' 'module.exports = { open };' >> library.js"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
確かめるスクリプトを書く、その 1
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'const { ask, sqlOf } = require(\"./model\");' 'const { open } = require(\"./library\");' 'const db = open();' 'function rowsOf(sql) {' ' try {' ' return db.prepare(sql).all().map(function (r) { return JSON.stringify(Object.values(r)); }).sort();' ' } catch (error) { return null; }' '}' 'function check(sql, want) {' ' const got = rowsOf(sql);' ' if (got === null) return \"エラー\";' ' const wanted = want.map(function (r) { return JSON.stringify(r); }).sort();' ' return got.join(\"|\") === wanted.join(\"|\") ? \"合格\" : \"行が違う \" + got.join(\" \");' '}' > check.js"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
確かめるスクリプトを書く、その 2
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'const L = \"SELECT COUNT(*) FROM loans WHERE \";' 'const T = \"SELECT b.title FROM loans l JOIN books b ON \";' 'const cases = [' ' { want: [[3]], sql: [\"SELECT COUNT(member_id) FROM loans WHERE month = 3\",' ' \"SELECT COUNT(DISTINCT member_id) FROM loans WHERE month = 3\",' ' \"SELECT COUNT(DISTINCT member) FROM loans WHERE month = 3\"] },' ' { want: [[5]], sql: [\"SELECT COUNT(*) FROM loans\", L + \"returned = 0\", L + \"returned = 1\"] },' ' { want: [[\"雨の地図\"], [\"静かなエンジン\"], [\"七つのやかん\"]],' ' sql: [T + \"l.id = b.id WHERE l.member_id = 2\", T + \"l.book_id = b.id WHERE l.member_id = 2\",' ' T + \"l.book_id = b.id WHERE l.member_id = 2 ORDER BY b.title DESC\"] },' ' { want: [[2]], sql: [L + \"month = 5 AND returned = 0\", L + \"month = 5\"] },' '];' >> check.js"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
確かめるスクリプトを書く、その 3
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'async function main() {' ' cases.forEach(function (c, i) {' ' c.sql.forEach(function (sql, j) { console.log((i + 1) + \"abc\"[j] + \": \" + check(sql, c.want)); });' ' });' ' db.exec(\"UPDATE loans SET returned = 1 WHERE id = 8\");' ' console.log(\"貸し出し 8 が戻った後、質問 4 の答えは 1:\");' ' cases[3].sql.forEach(function (sql, j) { console.log(\"4\" + \"ab\"[j] + \": \" + check(sql, [[1]])); });' ' db.exec(\"UPDATE loans SET returned = 0 WHERE id = 8\");' ' await live();' '}' >> check.js"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
確かめるスクリプトを書く、その 4
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'async function live() {' ' const q = \"loans(id, book_id, member_id, month, returned)\n質問: 3 月に本を借りた会員は、何人ですか(同じ人は一人と数える)。\";' ' const sample = \"SELECT COUNT(DISTINCT member_id) FROM loans WHERE month = 3;\";' ' const reply = await ask([{ role: \"system\", content: \"SQLite の SELECT 文を一つだけ答えてください。ほかには何も書かないでください。\" },' ' { role: \"user\", content: q }], sample, 120);' ' const sql = sqlOf(reply.text);' ' console.log(\"モデルが書いたクエリ: \" + sql);' ' console.log(\" 答えの分かるデータで確かめた結果: \" + check(sql, [[3]]));' '}' 'main();' >> check.js"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
すべての候補をデータで動かす
docker exec -w /home/node/project agent node --experimental-sqlite --no-warnings check.jsここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
Run の出力
| 行 | 意味 |
|---|---|
| 1a: 行が違う [4] | 上田さんを二回数えた |
| 1c: エラー | member という列はない |
| 2c: 行が違う [4] | 戻った本を数えた |
| 3a: 行が違う ["正午の北"] ["雨の地図"] | id どうしの結合 |
| 3c: 合格 | 順が違っても合格する |
| 4a: 合格 と 4b: 合格 | データが二つを見分けられない |
| 貸し出し 8 が戻った後の 4b: 行が違う [2] | 新しい一行で二つを見分けられる |
学生が入力するセルは、このプレビューでは空白です。