获取唯一ids的整数和

Question

在一个使用PostgreSQL 9.3作为后端的游戏中，我试图限制用户每周玩游戏的数量。

我已经准备好了SQL Fiddle，但不幸的是它不起作用。

我的(测试，而不是生产)代码在这里：

create table pref_users (
        id varchar(32) primary key,
        last_ip inet
);

create table pref_match (
        id varchar(32) references pref_users on delete cascade,
        completed integer default 0 check (completed >= 0),
        yw char(7) default to_char(current_timestamp, 'IYYY-IW'),
        primary key(id, yw)
);

下面是一个存储过程，我试着找出本周玩的游戏数量：

create or replace function pref_get_user_info(
    IN _id varchar,
    IN _last_ip inet,
    OUT games_this_week integer
) as $BODY$
        begin
            select sum(completed)
            into games_this_week
            from pref_match where
            (id = _id or
            id in (select id from pref_users where last_ip=_last_ip)) and
            yw=to_char(current_timestamp, 'IYYY-IW');
        end;
$BODY$ language plpgsql;

在这种情况下：

(id = _id or
 id in (select id from pref_users where last_ip=_last_ip))

我试图捕捉的用户，谁会试图欺骗和加入游戏与一个不同的玩家id，但从相同的IP地址。

但我担心，有时我会得到两倍数量的完成游戏--因为在上述情况下，第一部分将匹配：id = _id，然后是第二部分id in (...) --这将给我两次游戏次数。

能治好吗？

在上述情况下，当id被两次使用时，我需要“检测”。

Answer 1

表格布局

不要使用char(7)存储时间戳。

准确地说，不要使用char(7)来存储任何东西。永远不会。详细信息：

比较varchar和char

也不要将日期/时间数据存储在任何文本表示中。使用date。

如果您只对一年中的一周感兴趣，您只需存储一个integer (甚至smallint)，您就可以在萃取物()中获得它。

SELECT extract(week FROM now())::int;

但是我建议存储一个date，它占用4个字节，就像一个integer，而char(7)占用11个字节。您可以便宜提取具有上述功能的一周。或者使用date_trunc()

SELECT date_trunc('week', now())

如果必须的话，id更应该是int --或者bigint。varchar(32)相当低效。

并声明您的列completed NOT NULL！否则您将不得不处理可能的空值。您的check约束不包括这一点。NULL不违反约束。

查询/功能

假设数据类型为date用于yw，int为id

CREATE OR REPLACE FUNCTION pref_get_user_info(_id int, _last_ip inet
            ,OUT games_this_week int) AS
$func$
DECLARE
   _this_monday date := date_trunc('week', now())::date;
BEGIN

   SELECT sum(completed)::int
   INTO   games_this_week
   FROM   pref_users u
   JOIN   pref_match m USING (id)
   WHERE (u.id = _id OR u.last_ip = _last_ip)
   AND    m.yw BETWEEN _this_monday
                   AND _this_monday + 6;  -- "sargable"

END
$func$ LANGUAGE plpgsql;

如果last_ip被定义为NOT NULL，则根本不需要将_id作为参数。只有_last_ip。

Answer 2

虽然我不知道plpgsql是否支持它，但我还是尝试了某种类型的透视表。

begin
    select 
    SUM
    (
        CASE WHEN pref_match.id IN (select id from pref_users where last_ip=_last_ip)
        THEN completed
    ) AS ip_matches,
    SUM
    (
        CASE WHEN pref_match.id = _id
        THEN completed
    ) AS id_matches,
    into games_this_week
    from pref_match
    and yw=to_char(current_timestamp, 'IYYY-IW');
end;

然后得到两个值的最大值。

但是一个用户可以在一个以上的IP上玩游戏，这里没有涉及到这个问题(很可能你需要记录每一个游戏的IP来捕捉这些位置)。

另外，请注意，这将是非常低性能的代码。子查询将在筛选阶段在每个匹配的行上运行。