将每场比赛3名球员的成绩保存到PostgreSQL中

I'm trying to save scores of card game matches (always with 3 players) into PotsgreSQL 8.4.9

我正试图将数十场纸牌游戏比赛(总是有3名玩家)保存到PotsgreSQL 8.4.9中

I have almost everything there (s. below please) just 2 minor parts are missing.

我几乎所有的东西(请在下面)只缺少2个小部分。

1. As first I'm trying to create a PL/pgSQL procedure for saving scores

首先,我正在尝试创建一个PL / pgSQL程序来保存分数

2. And then I need a join select statement to retrieve match date, player names, gender, avatars, scores and if a player has quit the game prematurely

然后我需要一个连接选择语句来检索比赛日期,球员姓名,性别,头像,分数以及球员是否过早退出比赛

I've created 3 SQL tables:

我创建了3个SQL表:

    create table pref_users (
            uid varchar(32) primary key,
            first_name varchar(64),
            female boolean,
            avatar varchar(128)
    }

    create table pref_games {
            gid serial,
            rounds integer not null,
            finished timestamp default current_timestamp
    }

    create table pref_scores (
            uid varchar(32) references pref_users,
            gid serial references pref_games,  /* XXX serial ok here? */
            money integer not null,
            quit boolean
    );

Here is my PL/pgSQL procedure where I need some help please:

这是我的PL / pgSQL程序,我需要一些帮助:

    create or replace function pref_insert_scores(
        _uid0 varchar, _money0 integer, _quit0 boolean,
        _uid1 varchar, _money1 integer, _quit1 boolean,
        _uid2 varchar, _money2 integer, _quit2 boolean,
        _rounds integer) returns void as $BODY$
            begin

            insert into pref_games (rounds) values (_rounds);

            -- XXX how do I get the _gid of this new game?

            insert into pref_scores (uid, gid, money, quit)
                values(_uid0, _gid, _money0, _quit0);

            insert into pref_scores (uid, gid, money, quit)
                values(_uid1, _gid, _money1, _quit1);

            insert into pref_scores (uid, gid, money, quit)
                values(_uid2, _gid, _money2, _quit2);
            end;
    $BODY$ language plpgsql;

And then I need some help to join the scores with first_name, female, avatar data from the first table - so that I can display a list of played games for the last 7 days in a table at a web page:

然后我需要一些帮助来加入第一张表中的first_name,女性,头像数据的分数 - 这样我就可以在网页的表格中显示最近7天玩过的游戏列表:

01.12.2011  Alice $10        Bob $20 Charlie -$30  17 rounds
01.12.2011  Alice $0 (quit)  Bob $20 Charlie -$20  5  rounds

UPDATE:

With mu is too short's help I have now my tables filled with data, but still can't figure out how to list all games performed by a player - together with his 2 opponents and their scores.

由于亩太短的帮助我现在我的桌子充满了数据,但仍然无法弄清楚如何列出玩家所执行的所有游戏 - 以及他的2个对手和他们的分数。

I have a table containing all games played:

我有一张包含所有比赛的桌子:

# select * from pref_games limit 5;
 gid | rounds |          finished
-----+--------+----------------------------
  1 |     10 | 2011-10-26 14:10:35.46725
  2 |     12 | 2011-10-26 14:34:13.440868
  3 |     12 | 2011-10-26 14:34:39.279883
  4 |     14 | 2011-10-26 14:35:25.895376
  5 |     14 | 2011-10-26 14:36:56.765978

Then here I have all 3 players (and their scores) who participated in game #3:

然后我在这里参加了第3场比赛的所有3名球员(以及他们的分数):

# select * from pref_scores where gid=3;
         uid           | gid | money | quit
-----------------------+-----+-------+------
 OK515337846127        |   3 |   -37 | f
 OK40798070412         |   3 |   -75 | f
 MR2871175175044094219 |   3 |   112 | f

And these are all games played by the player with uid = DE9411:

这些都是玩家用uid = DE9411玩的游戏:

# select * from pref_scores where id='DE9411';
  uid   | gid | money | quit
--------+-----+-------+------
 DE9411 |  43 |    64 | f
 DE9411 | 159 |  -110 | f
 DE9411 | 224 |    66 | f
 DE9411 | 297 |   -36 | f
 DE9411 | 385 |    29 | f
 DE9411 | 479 |   -40 | f
 DE9411 | 631 |   -14 | f
 DE9411 | 699 |   352 | f
 DE9411 | 784 |   -15 | f
 DE9411 | 835 |   242 | f

But how do I list the 2 other players and their scores in the above result set?

但是如何在上述结果集中列出其他2名球员及其得分?

4 个解决方案

#1

You don't want serial in pref_scores, just int:

你不想在pref_scores中使用serial,只需要int:

create table pref_scores (
        uid varchar(32) references pref_users,
        gid int references pref_games,  /* XXX serial ok here? */
        money integer not null,
        quit boolean
);

You want to use INSERT ... RETURNING ... INTO:

你想使用INSERT ... RETURNING ... INTO:

create or replace function pref_insert_scores(
    _uid0 varchar, _money0 integer, _quit0 boolean,
    _uid1 varchar, _money1 integer, _quit1 boolean,
    _uid2 varchar, _money2 integer, _quit2 boolean,
    _rounds integer) returns void as $BODY$
declare
    _gid int;
begin
    insert into pref_games (rounds) values (_rounds) returning gid into _gid;
    -- etc...

#2

This query should work for grabbing the list you need with the current schema. However, with so many joins, performance might not be so great. Since you know you always have 3 users, depending on the expected size of this table, you might want to test performance for having a single game table with the user's information de-normalized.

此查询应该适用于使用当前架构获取所需的列表。但是,如此多的联接,性能可能不会那么大。由于您知道您总共有3个用户,因此根据此表的预期大小,您可能希望测试具有单个游戏表的性能,其中用户的信息被非规范化。

select g.finished, u1.uid u1, s1.money m1, s1.quit q1,  u2.uid u2, s2.money m2, s2.quit
q2, u3.uid u3, s3.money m3, s3.quit q3, g.rounds
from pref_games g
inner join pref_scores s1 on s1.gid = g.gid 
inner join pref_scores s2 on s2.gid = g.gid and s2.uid > s1.uid
inner join pref_scores s3 on s3.gid = g.gid and s3.uid > s2.uid
inner join pref_users u1 on s1.uid = u1.uid
inner join pref_users u2 on s2.uid = u2.uid
inner join pref_users u3 on s3.uid = u3.uid
and g.finished > CURRENT_DATE - interval '1 week'

#3

In the line:

在线:

        finished timestamp default current_timestamp

I would strongly suggest using

我强烈建议使用

        finished timestamptz default current_timestamp

as this allows for saner date/time arithmetic with respect to daylight saving changes as the actual date/time is stored in GMT - it is also better suited for internationalisation.

因为实际日期/时间存储在GMT中,因此这允许关于夏令时变化的更准确的日期/时间算术 - 它也更适合国际化。

From the pg9.1.1 manual '8.5. Date/Time Types':

从pg9.1.1手册'8.5。日期/时间类型':

[...]

Note: The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior. (Releases prior to 7.3 treated it as timestamp with time zone.) timestamptz is accepted as an abbreviation for timestamp with time zone; this is a PostgreSQL extension.

注意:SQL标准要求只写时间戳等同于没有时区的时间戳,PostgreSQL会尊重这种行为。 (7.3之前的版本将其视为带时区的时间戳。)timestamptz被接受为带时区的时间戳的缩写;这是一个PostgreSQL扩展。

[...]

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the timezone zone.

对于带时区的时间戳,内部存储的值始终为UTC(通用协调时间,传统上称为格林威治标准时间,GMT)。具有指定显式时区的输入值将使用该时区的相应偏移量转换为UTC。如果输入字符串中未指定时区,则假定它位于系统时区参数指示的时区中,并使用时区区域的偏移量转换为UTC。

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.3).

输出带有时区值的时间戳时,它始终从UTC转换为当前时区区域,并在该区域中显示为本地时间。要查看另一个时区的时间,请更改时区或使用AT TIME ZONE构造(参见第9.9.3节)。

[...]

#4

        gid serial references pref_games,  /* XXX serial ok here? */

Here you should use 'int' rather than 'serial' - as you need to reference a very specific record in pref_games.

在这里你应该使用'int'而不是'serial' - 因为你需要在pref_games中引用一个非常具体的记录。