Слияние сложных таблиц

Ответ 1

Я думаю, что вы не используете PostgreSQL так, как планировали использовать, плюс дизайн вашей таблицы может оказаться неприемлемым для того, чего вы хотите достичь. Несмотря на то, что было сложно понять, чего вы хотите достичь, я написал это, что, кажется, решает все, что вы хотите, используя только несколько таблиц, и функции, возвращающие наборы записей для моделирования вашего требования для отдельных прогонов. Я использовал Enums и сложные типы, чтобы проиллюстрировать некоторые функции, которые вы, возможно, захотите использовать из-за возможностей PostgreSQL.

Кроме того, я не уверен, какие параметризованные имена таблиц (я никогда не видел ничего подобного в любой СУБД), но 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,
  PRIMARY KEY (run_id, match_no)
  );

CREATE TABLE hands (
  hand_id bigserial NOT NULL PRIMARY KEY,
  run_id bigint NOT NULL REFERENCES runs,
  match_no int NOT NULL,
  hand_no int NOT NULL,
  player_no int NOT NULL,
  UNIQUE (run_id, match_no, hand_no),
  FOREIGN KEY (run_id, match_no) REFERENCES matches,
  FOREIGN KEY (run_id, 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, int[]) RETURNS TABLE (player_no int, hand_no int, card card) AS $$
  WITH
    hand AS (
      INSERT INTO hands (run_id, match_no, player_no, hand_no)
      VALUES ($1, $2, $3, next_hand_no($1))
      RETURNING hand_id, player_no, hand_no),
    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, hand_no int, player_no int, card int) AS $$
  SELECT d.deal_id, h.hand_no, 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, 0, '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, 0, ARRAY[6, 63, 41]);
SELECT * FROM deal_cards(1, 1, 1, ARRAY[76, 23]);
SELECT * FROM deal_cards(1, 1, 0, ARRAY[51, 29, 2, 92, 6]);
SELECT * FROM deal_cards(1, 1, 1, ARRAY[101, 8]);

INSERT INTO matches VALUES (1, next_match_no(1)); -- Second match
SELECT * FROM deal_cards(1, 2, 0, ARRAY[78, 38]);
SELECT * FROM deal_cards(1, 2, 1, ARRAY[24, 18, 95, 40]);
SELECT * FROM deal_cards(1, 2, 0, ARRAY[13, 84, 41]);
SELECT * FROM deal_cards(1, 2, 1, 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, match_no, hand_no DESC;

SELECT * FROM winning_hands;

Ответ 2

Не использовал бы оператор UNION?

Для отношения рук:

SELECT * FROM hands_first
UNION ALL
SELECT * FROM hands_second

Для отношения совпадений:

SELECT * FROM matches_first
UNION ALL
SELECT * FROM matches_second

В качестве более долгосрочного решения я бы подумал о реструктуризации БД, поскольку он быстро станет неуправляемым с этой схемой. Почему бы не улучшить нормализацию, введя таблицу игр?

Иными словами, игры имеют много матчей, в матчах есть много игроков для каждой игры, и у игроков много рук для каждого матча.

Я бы рекомендовал рисовать UML для отношений сущностей на бумаге (http://dawgsquad.googlecode.com/hg/docs/database_images/Database_Model_Diagram(Title).png), а затем улучшить схему, чтобы ее можно было запросить используя обычные операторы SQL.

Надеюсь, что это поможет.

EDIT:

В этом случае вы можете использовать подзапрос в объединении обеих таблиц с функцией rownumber() PG для представления номера строки:

SELECT 
  row_number() AS id,
  whichplayer,
  whichhand,
  thiscard
FROM
(
  SELECT * FROM hands_first
  UNION ALL
  SELECT * FROM hands_second
);

Тот же принцип применим к таблице совпадений. Очевидно, что это не очень хорошо масштабируется даже для небольшого числа таблиц, поэтому приоритет будет нормализовать вашу схему.

Документы по некоторым функциям PG: http://www.postgresql.org/docs/current/interactive/functions-window.html

Ответ 3

чтобы создать новую таблицу со всеми строками из двух таблиц, выполните:

CREATE TABLE hands AS 
  select 1 as hand, id, whichplayer, whichhand, thiscard
  from hands_first
  union all
  select 2 as hand, id, whichplayer, whichhand, thiscard
  from hands_second

после этого, чтобы вставить данные нового matche, создайте последовательность с началом на текущий последний + 1

CREATE SEQUENCE matche START 3;

прежде чем вставить значение последовательности считывания и использовать его во вставках:

SELECT nextval('matche');

Ответ 4

Структура вашей базы данных невелика, и я точно знаю, что это не масштабируемый подход к созданию таблиц на лету. Есть недостатки производительности, создающие физические таблицы вместо использования существующей структуры. Я предлагаю вам реорганизовать вашу структуру db, если может.

Однако вы можете использовать оператор UNION для объединения ваших данных.