モデルのクエリは質問に答えているか
モデルが書いた SQL を読むとは、信用する前に、クエリを質問と照らして確かめることです。クエリはエラーなしで動き、それでも別の質問に答えていることがあります。
モジュール 4 は結合とグループ化を教えました。このレッスンでは、モデルが質問のために書いた SQL を読み、モデルが一番よくする三つの誤りを探します。
このレッスンのゴール
メモ
モデルのクエリを読み、聞かれた質問に答えているかを言うことができる。
キーワード
| ことば | いみ | れい |
|---|---|---|
| 句 | WHERE のような、クエリの一部分 | |
| 条件 | 一部の行だけを残す確認 | returned = 0 |
| 重複 | 同じ値を二回以上数えること | |
| DISTINCT | それぞれの値を一回だけにする | COUNT(DISTINCT member_id) |
| 結合の列 | 二つの表を組み合わせるときの列 |
学生が入力するセルは、このプレビューでは空白です。
クエリより先に質問を読む
まず、質問が何を求めているかを書き出します。どの行を数えるのか。答えは数か、一覧か、一つの値か。同じものは一回だけ数えるのか。
次に、クエリを句ごとに読みます。FROM と JOIN は行がどこから来るかを言います。WHERE は一部の行を残し、SELECT は何を返すかを言います。
例: 質問の部分と句
| 質問の言葉 | クエリに要るもの | 句 |
|---|---|---|
| 3 月に | month = 3 | WHERE |
| 同じ人は一人と数える | DISTINCT member_id | SELECT |
| 何人 | COUNT | SELECT |
| 題名 | loans から books への book_id での結合 | FROM と JOIN |
学生が入力するセルは、このプレビューでは空白です。
モデルがよくする三つの誤り
一つ目は重複を数えることです。上田さんが二回借りると、COUNT(member_id) は上田さんを二回数えます。二つ目は条件を忘れることです。まだ貸し出し中のものだけでなく、すべての貸し出しを数えてしまいます。
三つ目は違う結合です。loans.id = books.id は、貸し出し 3 と本 3 を、どちらも番号が 3 だというだけで組み合わせます。結合には loans.book_id を使います。
例: 間違いと正しい形
| 質問 | 間違ったクエリの部分 | 正しいクエリの部分 |
|---|---|---|
| 3 月に借りた会員の人数 | COUNT(member_id) | COUNT(DISTINCT member_id) |
| まだ貸し出し中の本 | WHERE がない | WHERE returned = 0 |
| 会員 2 が借りた題名 | ON l.id = b.id | ON l.book_id = b.id |
学生が入力するセルは、このプレビューでは空白です。
よくある間違い
注意
エラーなしで動くクエリは質問に答えている、と考える人が多いです。エラーが示すのは、SQL が壊れていることだけです。違う結合や忘れた条件は、何事もなく動いて、間違った答えを返します。
助っ人 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 { ask, sqlOf } = require(\"./model\");' 'const questions = [' ' { q: \"3 月に本を借りた会員は、何人ですか(同じ人は一人と数える)。\", needs: [\"DISTINCT\", \"month = 3\"],' ' a: \"SELECT COUNT(member_id) FROM loans WHERE month = 3\",' ' b: \"SELECT COUNT(DISTINCT member_id) FROM loans WHERE month = 3\" },' ' { q: \"まだ貸し出し中の本は、何冊ですか。\", needs: [\"returned = 0\"],' ' a: \"SELECT COUNT(*) FROM loans\",' ' b: \"SELECT COUNT(*) FROM loans WHERE returned = 0\" },' ' { q: \"会員 2 が借りた本の題名は、何ですか。\", needs: [\"book_id\", \"member_id = 2\"],' ' a: \"SELECT b.title FROM loans l JOIN books b ON l.id = b.id WHERE l.member_id = 2\",' ' b: \"SELECT b.title FROM loans l JOIN books b ON l.book_id = b.id WHERE l.member_id = 2\" },' '];' > read.js"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
読むチェックリストを書く、その 2
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'function squash(sql) { return sql.toUpperCase().split(/\s+/).join(\" \").trim(); }' 'function review(sql, needs) {' ' const text = squash(sql);' ' const problems = needs.filter(function (n) { return text.indexOf(squash(n)) < 0; })' ' .map(function (n) { return \"ない: \" + n; });' ' if (/ON \w+\.ID = \w+\.ID\b/.test(text)) problems.push(\"id どうしの結合\");' ' return problems.length ? problems.join(\"、\") : \"よさそう\";' '}' >> read.js"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
読むチェックリストを書く、その 3
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'async function main() {' ' questions.forEach(function (x, i) {' ' console.log((i + 1) + \" \" + x.q);' ' console.log(\" A: \" + review(x.a, x.needs));' ' console.log(\" B: \" + review(x.b, x.needs));' ' });' ' const schema = \"loans(id, book_id, member_id, month, returned)\nmembers(id, name)\nloans.member_id -> members.id\";' ' const system = \"SQLite の SELECT 文を一つだけ答えてください。ほかには何も書かないでください。\";' ' const sample = \"SELECT COUNT(DISTINCT member_id) FROM loans WHERE month = 3;\";' ' const reply = await ask([{ role: \"system\", content: system },' ' { role: \"user\", content: schema + \"\n質問: \" + questions[0].q }], sample, 120);' ' const sql = sqlOf(reply.text);' ' console.log(\"モデルが書いたクエリ: \" + sql);' ' console.log(\" 読んで確かめた結果: \" + review(sql, questions[0].needs));' '}' 'main();' >> read.js"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
読むチェックリストを動かす
docker exec -w /home/node/project agent node read.jsここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
Run の出力
| 行 | 意味 |
|---|---|
| A: ない: DISTINCT | 質問 1 の候補 A は、重複を数える |
| A: ない: returned = 0 | 質問 2 の候補 A には、条件がない |
| A: ない: book_id、id どうしの結合 | 質問 3 の候補 A は、違う列で結合する |
| B: よさそう | どの候補 B も、読む確認を通る |
| モデルが書いたクエリ: ... | 質問 1 へのモデルのクエリを同じように読む。または記録済みサンプル |
学生が入力するセルは、このプレビューでは空白です。