SQL学習型じゃんけん (SQLite3)

はじめに

• SQLite3を少し囓った素人が、SQLで「学習型じゃんけん」を作ってみました。
  • 「こうした方がすっきりする」といった突っ込みは大歓迎です。
  • 「UI付けてみた」とかもご自由にどうぞ。
• 標準SQLは意識していない(知らない)ので、SQLite3に依存していると思います。
  • 「こうしたら標準になる」という突っ込みも歓迎します。

**SQLite**

• SQLiteは、SQLのサブセットが使えるスタンドアローンなデータベース管理システムです。
  • Windows、MacOS、Android、iOSなどに対応しています。
• 公式サイト
• 参考: SQLite3のためのSQLリファレンス (Qiita)
• 参考: UnityでSQLiteを使う ～ SQLiteUnityKit拡張ライブラリ (Qiita)

**検証環境**

この記事のコードは、以下の環境で検証いたしました。

• Windows 10
• DB Browser for SQLite
  • バージョン 3.12.1
  • x86_64-little_endian-llp64 向けビルド, x86_64 で動作中
  • Qt バージョン 5.12.8
  • SQLite バージョン 3.33.0.

「学習型じゃんけん」について

私が、新しく体験する言語や開発プラットフォームで、「Fizz Buzz」のような位置付けでよく使う題材です。

蛇足

Fizz Buzz with SQLite3

WITH RECURSIVE tmp (num) AS (
	SELECT 1
	UNION ALL
	SELECT num + 1 FROM tmp WHERE num < 20
)
SELECT 
	CASE
		WHEN num % 15 = 0 THEN 'FIZZBUZZ'
		WHEN num % 5 = 0 THEN 'BUZZ'
		WHEN num % 3 = 0 THEN 'FIZZ'
		ELSE num
	END as 'F/B'
FROM tmp;

概要

• プログラムがじゃんけんの相手をします。
• 人間の次の手を予測して、それに勝てる手を出してきます。

アルゴリズム

• 人間は、単純な選択を短時間に繰り返したときに、無意識に癖が出ます。
• 直近4回の手の並びの出現頻度を記録して、直近3回の手の並びから最も頻度の高い次の手を予測とします。

設計

DBの構成

• 手の呼び名 janken_names
  • 手の内部表現を外部表現にする変換テーブルです。
    • 手は、「1:ぐー、2:ちょき、3:ぱー」と内部表現します。
  • 内部表現と外部表現の対を1対1で持ちます。
• 手の記録 janken_log
  • 手とシリアル番号を記録するテーブルです。
    • シリアル番号が再利用されないように、INTEGER PRIMARY KEY AUTOINCREMENTにします。
  • 初期化時に無作為値で埋めます。
  • 挿入時にトリガーjanken_loggingで頻度を更新し、古い記録を消します。
• 手のパターンの出現頻度 janken_frq
  • 4回の手の順列と頻度のテーブルです。
  • 初期化時に全順列を算出し、頻度を小さめの無作為値で埋めます。
• 手の決定 janken_decision
  • 次にコンピューターが出す手を返すビューです。
  • 記録と頻度表から次の相手の手を予測し、それに勝てる手を算出します。

使い方

• コンピュータ側の次の手はSELECT name FROM janken_decision;で表示されます。
• 人間側の手をINSERT INTO janken_log (hand) VALUES (/* 1~3 */);で記録します。

コード

DBの構築と初期化

-- 手の呼び名
DROP TABLE IF EXISTS janken_names;
CREATE TABLE IF NOT EXISTS janken_names (
	hand INTEGER UNIQUE NOT NULL CHECK (hand >= 1 AND hand <= 3), 
	name TEXT UNIQUE NOT NULL
);
INSERT INTO janken_names VALUES
	(1, 'Goo'),
	(2, 'Choki'),
	(3, 'Par')
;

-- 相手の手のログ
DROP TABLE IF EXISTS janken_log;
CREATE TABLE IF NOT EXISTS janken_log (
	number INTEGER PRIMARY KEY AUTOINCREMENT,
	hand INTEGER NOT NULL CHECK (hand >= 1 AND hand <= 3)
);
INSERT INTO janken_log (hand) VALUES 
	(abs(random() % 3) + 1),
	(abs(random() % 3) + 1),
	(abs(random() % 3) + 1),
	(abs(random() % 3) + 1),
	(abs(random() % 3) + 1)
;

-- 相手の手の頻度
DROP TABLE IF EXISTS janken_frq;
CREATE TABLE IF NOT EXISTS janken_frq (
	hand1 INTEGER NOT NULL CHECK (hand1 >= 1 AND hand1 <= 3), 
	hand2 INTEGER NOT NULL CHECK (hand2 >= 1 AND hand2 <= 3), 
	hand3 INTEGER NOT NULL CHECK (hand3 >= 1 AND hand3 <= 3), 
	hand4 INTEGER NOT NULL CHECK (hand4 >= 1 AND hand4 <= 3), 
	frequency INTEGER DEFAULT (abs(random() % 3))
);
WITH RECURSIVE tmp (h1, h2, h3, h4) AS (
	SELECT 1, 1, 1, 1
	UNION ALL
	SELECT 
		iif(h1 < 3, h1 + 1, 1), 
		iif(h1 >= 3, iif(h2 < 3, h2 + 1, 1), h2), 
		iif(h1 >= 3 AND h2 >= 3, iif(h3 < 3, h3 + 1, 1), h3), 
		iif(h1 >= 3 AND h2 >= 3 AND h3 >= 3, h4 + 1, h4) 
	FROM tmp WHERE h1 < 3 or h2 < 3 or h3 < 3 or h4 < 3
)
INSERT INTO janken_frq (hand1, hand2, hand3, hand4) SELECT * FROM tmp;

-- 相手の手を記録したときの頻度記録処理
DROP TRIGGER IF EXISTS janken_logging;
CREATE TRIGGER IF NOT EXISTS janken_logging AFTER INSERT ON janken_log BEGIN
	UPDATE janken_frq SET frequency = frequency + 1 
	FROM (
		SELECT 
			   max(hand) OVER (ORDER BY number DESC ROWS CURRENT ROW)AS h1,
			   max(hand) OVER (ORDER BY number DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS h2,
			   max(hand) OVER (ORDER BY number DESC ROWS BETWEEN 2 FOLLOWING AND 2 FOLLOWING) AS h3,
			   max(hand) OVER (ORDER BY number DESC ROWS BETWEEN 3 FOLLOWING AND 3 FOLLOWING) AS h4
		FROM janken_log LIMIT 1
	)
	WHERE hand1 = h1 AND hand2 = h2 AND hand3 = h3 AND hand4 = h4;
	DELETE FROM janken_log WHERE number NOT IN (
		SELECT number FROM janken_log ORDER BY number DESC LIMIT 5
	);
END;

-- 相手の手を予測してそれに勝つ自分の手を決める
DROP VIEW IF EXISTS janken_decision;
CREATE VIEW IF NOT EXISTS janken_decision AS 
	SELECT name, hand FROM (
		SELECT iif(janken_frq.hand1 <= 1, 3, hand1 - 1) AS hand FROM janken_frq, (
			SELECT 
				max(hand) OVER (ORDER BY number DESC ROWS CURRENT ROW) AS h2,
				max(hand) OVER (ORDER BY number DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS h3,
				max(hand) OVER (ORDER BY number DESC ROWS BETWEEN 2 FOLLOWING AND 2 FOLLOWING) AS h4
			FROM janken_log LIMIT 1
		)
		WHERE hand2 = h2 AND hand3 = h3 AND hand4 = h4
		ORDER BY frequency DESC
		LIMIT 1
	) NATURAL JOIN janken_names
;

**結果**

janken_hands

hand	name
1	Goo
2	Choki
3	Par

janken_log

number	hand
1	1
2	3
3	3
4	2
5	2

janken_frq

hand1	hand2	hand3	hand4	frequency
1	1	1	1	2
2	1	1	1	2
3	1	1	1	0
1	2	1	1	2
2	2	1	1	1
3	2	1	1	1
1	3	1	1	2
2	3	1	1	0
3	3	1	1	0
1	1	2	1	1
2	1	2	1	0
3	1	2	1	2
1	2	2	1	1
2	2	2	1	2
3	2	2	1	0
1	3	2	1	0
2	3	2	1	2
3	3	2	1	1
1	1	3	1	2
2	1	3	1	1
3	1	3	1	2
1	2	3	1	0
2	2	3	1	1
3	2	3	1	1
1	3	3	1	1
2	3	3	1	2
3	3	3	1	0
1	1	1	2	0
2	1	1	2	0
3	1	1	2	0
1	2	1	2	0
2	2	1	2	1
3	2	1	2	1
1	3	1	2	1
2	3	1	2	1
3	3	1	2	0
1	1	2	2	2
2	1	2	2	0
3	1	2	2	0
1	2	2	2	0
2	2	2	2	0
3	2	2	2	1
1	3	2	2	0
2	3	2	2	1
3	3	2	2	1
1	1	3	2	0
2	1	3	2	0
3	1	3	2	0
1	2	3	2	2
2	2	3	2	1
3	2	3	2	0
1	3	3	2	0
2	3	3	2	2
3	3	3	2	0
1	1	1	3	2
2	1	1	3	0
3	1	1	3	2
1	2	1	3	0
2	2	1	3	0
3	2	1	3	1
1	3	1	3	1
2	3	1	3	1
3	3	1	3	0
1	1	2	3	2
2	1	2	3	0
3	1	2	3	1
1	2	2	3	2
2	2	2	3	2
3	2	2	3	2
1	3	2	3	2
2	3	2	3	0
3	3	2	3	0
1	1	3	3	2
2	1	3	3	1
3	1	3	3	0
1	2	3	3	0
2	2	3	3	2
3	2	3	3	0
1	3	3	3	2
2	3	3	3	0
3	3	3	3	1

janken_decision

name	hand
Par	3

人間の手を記録

INSERT INTO janken_log (hand) VALUES (/* 1~3 */);

次の手を決定

SELECT name FROM janken_decision;