Получить сумму целых чисел для уникальных идентификаторов

Question

В игре, использующей PostgreSQL 9.3 в качестве бэкэнда, я пытаюсь ограничить количество игр, в которые играет пользователь в неделю.

Я подготовил SQL-скрипку, но, к сожалению, она не работает.

Мой (тестовый, не производственный) код здесь:

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 а потом 2 часть id in (...) - и это даст мне количество игр в 2 раза.

Есть ли какое-нибудь лекарство от этого?

Мне нужно "обнаружить", когда id используется дважды в вышеуказанном условии.

Answer 1

Макет таблицы

Не использовать char(7) хранить метку времени.

Чтобы быть точным, не используйте char(7) хранить что угодно. Когда-либо. Подробности:
Сравните varchar с символом

И не храните данные даты / времени в любом текстовом представлении. использование timestamp или же date,

Если вас интересует только неделя года, вы можете просто сохранить integer (или даже smallint), который вы получаете с помощью extract ():

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

Но я предлагаю хранить date который занимает 4 байта, так же, как integer, в то время как char(7) занимает 11 байтов. Вы можете извлечь неделю с вышеупомянутой функцией дешево. Или использовать date_trunc():

SELECT date_trunc('week', now())

А также id должно быть int - или же bigint если вы должны. varchar(32) довольно неэффективно.

И объявите свою колонку completedNOT NULL! Или вам придется иметь дело с возможными значениями NULL. Ваше ограничение проверки не покрывает это. 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;

Тогда получите максимум из двух значений.

Но один пользователь может играть с более чем 1 IP, здесь это не рассматривается (скорее всего, вам нужно регистрировать каждый игровой IP, чтобы поймать эти ситуации)

Также обратите внимание, что это будет ОЧЕНЬ низкоэффективный код. Подзапрос будет выполняться в каждой соответствующей строке на этапе фильтрации.