获取唯一ID的整数和 [英] Get sum of integers for UNIQUE ids

问题描述

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

我已经准备了一个SQL提琴，但是很遗憾，它不起作用. /p>

我的(测试而非生产)代码在这里:

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 (...)-这将给我这个数字游戏2次.

请问有什么治疗方法吗?

在上述条件下两次使用id时，我需要检测".

解决方案

表布局

请勿使用char(7)存储时间戳.

准确地说，请勿使用char(7)存储任何内容.曾经.详细信息:
比较varchar和char

并且不要以 any 文本表示形式存储日期/时间数据.使用 timestamp或date .

如果您只对一年中的一周感兴趣，则可以存储integer(甚至是smallint)，通过 date_trunc() :

SELECT date_trunc('week', now())

和id应该更应该是int-或bigint(如果必须). varchar(32)效率很低.

并声明您的列completed NOT NULL！否则，您将不得不处理可能的NULL值.您的检查约束不涵盖了这一点. NULL不会违反约束.

查询/功能

假定yw的数据类型date和id的数据类型int:

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.

In a game using PostgreSQL 9.3 as backend I am trying to limit the number of games played by a user per week.

I have prepared an SQL Fiddle, but unfortunately it doesn't work.

My (test, not production) code is here:

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)
);

And here is a stored procedure with which I try to find the number of games played this week:

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;

With this condition:

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

I am trying to catch users who will try to cheat and join the game with a different player id but from the same IP-address.

But I am worried, that sometimes I will get doubled number of completed games - because in the above condition first the 1st part will match: id = _id and then the 2nd part id in (...) - and this will give me the number of games 2 times.

Is there please any cure for that?

I need to "detect" when an id is used twice in the above condition.

解决方案

Table layout

Do not use char(7) to store a timestamp.

To be precise, do not use char(7) to store anything. Ever. Details:
Compare varchar with char

And do not store date / time data in any text representation. Use timestamp or date.

If you are only interested in the week of the year, you could just store an integer (or even a smallint), which you get with with extract():

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

But I suggest to store a date which occupies 4 bytes, just like an integer, while char(7) occupies 11 bytes. You can extract the week with above function cheaply. Or use date_trunc():

SELECT date_trunc('week', now())

And id should much rather be int - or bigint if you must. varchar(32) is rather inefficient.

And declare your column completed NOT NULL! Or you'd have to deal with possible NULL values. Your check constraint does not cover that. NULL does not violate the constraint.

Query / function

Assuming data type date for yw and int for 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;

If last_ip was defined NOT NULL, you would not need _id as parameter at all. Just _last_ip.

这篇关于获取唯一ID的整数和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！