sql – 合并复杂表

发布时间：2020-05-27 19:40:11 所属栏目：MsSql 来源：互联网

导读：我试图合并表,其中行对应于与“真实”事物的许多：1关系. 我正在写一个二十一点模拟器,将游戏历史存储在数据库中,每个运行生成一组新的表.表格更像是模板,因为每个游戏都有自己的3个可变表格(玩家,手和比赛).这里是布局,其中suff是用于当前运行的用户指定的后

我试图合并表,其中行对应于与“真实”事物的许多：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;

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!