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

# はじめに

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

<details><summary>**SQLite**</summary>
<div>

- SQLiteは、SQLのサブセットが使えるスタンドアローンなデータベース管理システムです。
    - Windows、MacOS、Android、iOSなどに対応しています。
- [公式サイト](https://www.sqlite.org/index.html)
- 参考: [SQLite3のためのSQLリファレンス (Qiita)](https://qiita.com/tetr4lab/items/691ceeb528d6144547c8)
- 参考: [UnityでSQLiteを使う ～ SQLiteUnityKit拡張ライブラリ (Qiita)](https://qiita.com/tetr4lab/items/0e8b8cf0f20049e46c7d)

</div>
</details>

<details><summary>**検証環境**</summary>
<div>この記事のコードは、以下の環境で検証いたしました。

- Windows 10
- [DB Browser for SQLite](https://sqlitebrowser.org/)
    - バージョン 3.12.1
    - x86_64-little_endian-llp64 向けビルド, x86_64 で動作中
    - Qt バージョン 5.12.8
    - SQLite バージョン 3.33.0.

</div>
</details>

# 「学習型じゃんけん」について
私が、新しく体験する言語や開発プラットフォームで、「Fizz Buzz」のような位置付けでよく使う題材です。

<details><summary>蛇足</summary>
<div>

```sql:Fizz&nbsp;Buzz&nbsp;with&nbsp;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;
```

</div>
</details>

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

### アルゴリズム
- 人間は、単純な選択を短時間に繰り返したときに、無意識に癖が出ます。
- 直近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 */);`で記録します。

# コード

```sql: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
;
```

<details><summary>**結果**</summary>
<div>

##### 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

</div>
</details>

```sql:人間の手を記録
INSERT INTO janken_log (hand) VALUES (/* 1~3 */);
```

```sql:次の手を決定
SELECT name FROM janken_decision;
```