我试图合并表,其中行对应于与“真实”事物的许多：1关系.
我正在写一个二十一点模拟器,将游戏历史存储在数据库中,每个运行生成一组新的表.表格更像是模板,因为每个游戏都有自己的3个可变表格(玩家,手和比赛).这里是布局,其中suff是用于当前运行的用户指定的后缀：
- cards
- id INTEGER PRIMARY KEY
- cardValue INTEGER NOT NULL
- suit INTEGER NOT NULL
- players_suff
- whichPlayer INTEGER PRIMARY KEY
- aiType TEXT NOT NULL
- hands_suff
- id BIGSERIAL PRIMARY KEY
- whichPlayer INTEGER REFERENCES players_suff(whichPlayer) *
- whichHand BIGINT NOT NULL
- thisCard INTEGER REFERENCES cards(id)
- matches_suff
- id BIGSERIAL PRIMARY KEY
- whichGame INTEGER NOT NULL
- dealersHand BIGINT NOT NULL
- whichPlayer INTEGER REFERENCES players_suff(whichPlayer)
- thisPlayersHand BIGINT NOT NULL **
- playerResult INTEGER NOT NULL --AKA who won
仅创建一张卡片表,因为它的值不变.
所以运行模拟器之后你可能有两次：
hands_firstrun players_firstrun matches_firstrun hands_secondrun players_secondrun matches_secondrun
如果您对这两个运行使用相同的AI参数(即,player_firstrun和players_secondrun完全相同),我希望能够组合这些表.问题是,我插入手的方式使这真的很混乱：哪个不能是一个BIGSERIAL,因为hands_suff行与“实际手”的关系是很多的：1. matches_suff以相同的方式处理,因为二十一点“游戏”实际上由一组游戏组成：每组玩家与经销商的一组对.所以对于3名玩家,你实际上每轮有3排.
目前我选择了表中最大的那个,并添加1,然后插入一只手的所有行.我担心这个“查询插入”会很慢,如果我合并了可能是任意大的2个表.
当我合并表时,我觉得我应该能够(完全在sql中)查询最大的值,其中Hand和whichGame一次然后使用它们组合表,为每个唯一的whichHand和表中的哪个Game合并,增加它们.
(我看到this question,但它不处理在2个不同的地方使用生成的ID).我正在使用Postgres,如果答案是具体的,它可以.
*可悲的是,postgres不允许使用参数化的表名,因此必须通过手动字符串替换完成.不是世界的尽头,因为程序不是面向Web的,除了我以外,没有人可能会打扰它,但是sql注入漏洞并没有让我开心.
** matches_suff(whichPlayersHand)原来是引用hands_suff(whichHand)而是foreign keys must reference unique values.HandHand并不是唯一的,因为手是由多行组成的,每行都是“保存”一个卡.要查询一个手,您可以选择所有这些行中具有相同值的其中Hand.我不能想到一个更优雅的方式来做到这一点,而不诉诸数组.
编辑：
这就是我现在所说的：
thomas=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------------+-------+--------
public | cards | table | thomas
public | hands_first | table | thomas
public | hands_second | table | thomas
public | matches_first | table | thomas
public | matches_second | table | thomas
public | players_first | table | thomas
public | players_second | table | thomas
(7 rows)
thomas=# SELECT * FROM hands_first
thomas-# \g
id | whichplayer | whichhand | thiscard
----+-------------+-----------+----------
1 | 0 | 0 | 6
2 | 0 | 0 | 63
3 | 0 | 0 | 41
4 | 1 | 1 | 76
5 | 1 | 1 | 23
6 | 0 | 2 | 51
7 | 0 | 2 | 29
8 | 0 | 2 | 2
9 | 0 | 2 | 92
10 | 0 | 2 | 6
11 | 1 | 3 | 101
12 | 1 | 3 | 8
(12 rows)
thomas=# SELECT * FROM hands_second
thomas-# \g
id | whichplayer | whichhand | thiscard
----+-------------+-----------+----------
1 | 0 | 0 | 78
2 | 0 | 0 | 38
3 | 1 | 1 | 24
4 | 1 | 1 | 18
5 | 1 | 1 | 95
6 | 1 | 1 | 40
7 | 0 | 2 | 13
8 | 0 | 2 | 84
9 | 0 | 2 | 41
10 | 1 | 3 | 29
11 | 1 | 3 | 34
12 | 1 | 3 | 56
13 | 1 | 3 | 52
thomas=# SELECT * FROM matches_first
thomas-# \g
id | whichgame | dealershand | whichplayer | thisplayershand | playerresult
----+-----------+-------------+-------------+-----------------+--------------
1 | 0 | 0 | 1 | 1 | 1
2 | 1 | 2 | 1 | 3 | 2
(2 rows)
thomas=# SELECT * FROM matches_second
thomas-# \g
id | whichgame | dealershand | whichplayer | thisplayershand | playerresult
----+-----------+-------------+-------------+-----------------+--------------
1 | 0 | 0 | 1 | 1 | 0
2 | 1 | 2 | 1 | 3 | 2
(2 rows)
我想把它们结合起来：
hands_combined table: id | whichplayer | whichhand | thiscard ----+-------------+-----------+---------- 1 | 0 | 0 | 6 --Seven of Spades 2 | 0 | 0 | 63 --Queen of Spades 3 | 0 | 0 | 41 --Three of Clubs 4 | 1 | 1 | 76 5 | 1 | 1 | 23 6 | 0 | 2 | 51 7 | 0 | 2 | 29 8 | 0 | 2 | 2 9 | 0 | 2 | 92 10 | 0 | 2 | 6 11 | 1 | 3 | 101 12 | 1 | 3 | 8 13 | 0 | 4 | 78 14 | 0 | 4 | 38 15 | 1 | 5 | 24 16 | 1 | 5 | 18 17 | 1 | 5 | 95 18 | 1 | 5 | 40 19 | 0 | 6 | 13 20 | 0 | 6 | 84 21 | 0 | 6 | 41 22 | 1 | 7 | 29 23 | 1 | 7 | 34 24 | 1 | 7 | 56 25 | 1 | 7 | 52 matches_combined table: id | whichgame | dealershand | whichplayer | thisplayershand | playerresult ----+-----------+-------------+-------------+-----------------+-------------- 1 | 0 | 0 | 1 | 1 | 1 2 | 1 | 2 | 1 | 3 | 2 3 | 2 | 4 | 1 | 5 | 0 4 | 3 | 6 | 1 | 7 | 2
“thiscard”的每个值代表范围[1..104] – 52张扑克牌的扑克牌,额外的位代表如果它正面朝上或面朝下.由于空间原因,我没有发布实际的表.
所以在第一场比赛中,球员0(也就是经销商)手中有(七个黑桃,空格之王,三个俱乐部).
解决方法
我认为你不是以使用Postgresql的方式使用,加上你的表格设计可能不适合你想要实现的.虽然很难理解你想要的解决方案实现,但我写了这个,这似乎解决了你想要的一切只使用一些表,还有返回记录集的功能,用于模拟你个人运行的需求.我使用枚举和复杂类型来说明你可能希望利用Postgresql的一些功能.
此外,我不知道什么参数化的表名称(我从来没有看到任何类似于任何RDBMS),但Postgresql确实允许一些完美的：记录集返回函数.
CREATE TYPE card_value AS ENUM ('1','2','3','4','5','6','7','8','9','10','J','Q','K');
CREATE TYPE card_suit AS ENUM ('Clubs','Diamonds','Hearts','Spades');
CREATE TYPE card AS (value card_value,suit card_suit,face_up bool);
CREATE TABLE runs (
run_id bigserial NOT NULL PRIMARY KEY,run_date timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE players (
run_id bigint NOT NULL REFERENCES runs,player_no int NOT NULL,-- 0 can be assumed as always the dealer
ai_type text NOT NULL,PRIMARY KEY (run_id,player_no)
);
CREATE TABLE matches (
run_id bigint NOT NULL REFERENCES runs,match_no int NOT NULL,match_no)
);
CREATE TABLE hands (
hand_id bigserial NOT NULL PRIMARY KEY,run_id bigint NOT NULL REFERENCES runs,hand_no int NOT NULL,UNIQUE (run_id,match_no,hand_no),FOREIGN KEY (run_id,match_no) REFERENCES matches,player_no) REFERENCES players
);
CREATE TABLE deals (
deal_id bigserial NOT NULL PRIMARY KEY,hand_id bigint NOT NULL REFERENCES hands,card card NOT NULL
);
CREATE OR REPLACE FUNCTION players(int) RETURNS SETOF players AS $$
SELECT * FROM players WHERE run_id = $1 ORDER BY player_no;
$$LANGUAGE sql;
CREATE OR REPLACE FUNCTION matches(int) RETURNS SETOF matches AS $$
SELECT * FROM matches WHERE run_id = $1 ORDER BY match_no;
$$LANGUAGE sql;
CREATE OR REPLACE FUNCTION hands(int) RETURNS SETOF hands AS $$
SELECT * FROM hands WHERE run_id = $1 ORDER BY match_no,hand_no;
$$LANGUAGE sql;
CREATE OR REPLACE FUNCTION hands(int,int) RETURNS SETOF hands AS $$
SELECT * FROM hands WHERE run_id = $1 AND match_no = $2 ORDER BY hand_no;
$$LANGUAGE sql;
CREATE OR REPLACE FUNCTION winner_player (int,int) RETURNS int AS $$
SELECT player_no
FROM hands
WHERE run_id = $1 AND match_no = $2
ORDER BY hand_no DESC
LIMIT 1
$$LANGUAGE sql;
CREATE OR REPLACE FUNCTION next_player_no (int) RETURNS int AS $$
SELECT CASE WHEN EXISTS (SELECT 1 FROM runs WHERE run_id = $1) THEN
COALESCE((SELECT MAX(player_no) FROM players WHERE run_id = $1),0) + 1 END
$$LANGUAGE sql;
CREATE OR REPLACE FUNCTION next_match_no (int) RETURNS int AS $$
SELECT CASE WHEN EXISTS (SELECT 1 FROM runs WHERE run_id = $1) THEN
COALESCE((SELECT MAX(match_no) FROM matches WHERE run_id = $1),0) + 1 END
$$LANGUAGE sql;
CREATE OR REPLACE FUNCTION next_hand_no (int) RETURNS int AS $$
SELECT CASE WHEN EXISTS (SELECT 1 FROM runs WHERE run_id = $1) THEN
COALESCE((SELECT MAX(hand_no) + 1 FROM hands WHERE run_id = $1),0) END
$$LANGUAGE sql;
CREATE OR REPLACE FUNCTION card_to_int (card) RETURNS int AS $$
SELECT ((SELECT enumsortorder::int-1 FROM pg_enum WHERE enumtypid = 'card_suit'::regtype AND enumlabel = ($1).suit::name) * 13 +
(SELECT enumsortorder::int-1 FROM pg_enum WHERE enumtypid = 'card_value'::regtype AND enumlabel = ($1).value::name) + 1) *
CASE WHEN ($1).face_up THEN 2 ELSE 1 END
$$LANGUAGE sql; -- SELECT card_to_int(('3','Spades',false))
CREATE OR REPLACE FUNCTION int_to_card (int) RETURNS card AS $$
SELECT ((SELECT enumlabel::card_value FROM pg_enum WHERE enumtypid = 'card_value'::regtype AND enumsortorder = ((($1-1)%13)+1)::real),(SELECT enumlabel::card_suit FROM pg_enum WHERE enumtypid = 'card_suit'::regtype AND enumsortorder = (((($1-1)/13)::int%4)+1)::real),$1 > (13*4))::card
$$LANGUAGE sql; -- SELECT i,int_to_card(i) FROM generate_series(1,13*4*2) i
CREATE OR REPLACE FUNCTION deal_cards(int,int,int[]) RETURNS TABLE (player_no int,hand_no int,card card) AS $$
WITH
hand AS (
INSERT INTO hands (run_id,player_no,hand_no)
VALUES ($1,$2,$3,next_hand_no($1))
RETURNING hand_id,mydeals AS (
INSERT INTO deals (hand_id,card)
SELECT hand_id,int_to_card(card_id)::card AS card
FROM hand,UNNEST($4) card_id
RETURNING hand_id,deal_id,card
)
SELECT h.player_no,h.hand_no,d.card
FROM hand h,mydeals d
$$LANGUAGE sql;
CREATE OR REPLACE FUNCTION deals(int) RETURNS TABLE (deal_id bigint,player_no int,card int) AS $$
SELECT d.deal_id,h.player_no,card_to_int(d.card)
FROM hands h
JOIN deals d ON (d.hand_id = h.hand_id)
WHERE h.run_id = $1
ORDER BY d.deal_id;
$$LANGUAGE sql;
INSERT INTO runs DEFAULT VALUES; -- Add first run
INSERT INTO players VALUES (1,'Dealer'); -- dealer always zero
INSERT INTO players VALUES (1,next_player_no(1),'Player 1');
INSERT INTO matches VALUES (1,next_match_no(1)); -- First match
SELECT * FROM deal_cards(1,1,ARRAY[6,63,41]);
SELECT * FROM deal_cards(1,ARRAY[76,23]);
SELECT * FROM deal_cards(1,ARRAY[51,29,2,92,6]);
SELECT * FROM deal_cards(1,ARRAY[101,8]);
INSERT INTO matches VALUES (1,next_match_no(1)); -- Second match
SELECT * FROM deal_cards(1,ARRAY[78,38]);
SELECT * FROM deal_cards(1,ARRAY[24,18,95,40]);
SELECT * FROM deal_cards(1,ARRAY[13,84,ARRAY[29,34,56,52]);
SELECT * FROM deals(1); -- This is the output you need (hands_combined table)
-- This view can be used to retrieve the list of all winning hands
CREATE OR REPLACE VIEW winning_hands AS
SELECT DISTINCT ON (run_id,match_no) *
FROM hands
ORDER BY run_id,hand_no DESC;
SELECT * FROM winning_hands;