LoginSignup
1
1

More than 3 years have passed since last update.

@tetr4lab(テトララボ)

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

Last updated at Posted at 2020-11-19

はじめに

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

SQLite


検証環境
この記事のコードは、以下の環境で検証いたしました。
  • 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;
1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1