Arhn - архитектура программирования

Получить сумму целых чисел для УНИКАЛЬНЫХ идентификаторов

В игре, использующей 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-адреса.

Но меня беспокоит, что иногда я получу удвоенное количество пройденных игр - потому что в вышеприведенном условии сначала совпадет 1-я часть: id = _id а потом 2-я часть id in (...) - и это даст мне количество игр в 2 раза.

Пожалуйста, есть ли какое-нибудь лекарство от этого?

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

postgresql join database-design plpgsql postgresql-9.3
17.06.2014

Ответы:


1

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

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

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

И не храните данные о дате/времени в любом текстовом представлении. Используйте 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) довольно неэффективен.

И объявите свой столбец completed NOT 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.

17.06.2014
  • Спасибо - я знаю, что мне нужно было использовать метку времени, а не строку, но я принял это неправильное решение 4 года назад, теперь не могу изменить базу данных... И почему вы предлагаете добавить NOT NULL к completed? Разве default 0 недостаточно? 17.06.2014
  • @AlexanderFarber: если вы хотите, чтобы столбец никогда не был равен NULL, определите его NOT NULL. Стандартного значения недостаточно — таким образом можно ввести NULL. Также помогает хранение и планирование запросов. 18.06.2014

    • 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-адрес игры, чтобы отслеживать такие ситуации).

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

    17.06.2014
    Новые материалы
    Коллекции публикаций по глубокому обучению
    Последние пару месяцев я создавал коллекции последних академических публикаций по различным подполям глубокого обучения в моем блоге https://amundtveit.com - эта публикация дает обзор 25..
    Представляем: Pepita
    Фреймворк JavaScript с открытым исходным кодом Я знаю, что недостатка в фреймворках JavaScript нет. Но я просто не мог остановиться. Я хотел написать что-то сам, со своими собственными..
    Советы по коду Laravel #2
    1-) Найти // You can specify the columns you need // in when you use the find method on a model User::find(‘id’, [‘email’,’name’]); // You can increment or decrement // a field in..
    Работа с временными рядами спутниковых изображений, часть 3 (аналитика данных)
    Анализ временных рядов спутниковых изображений для данных наблюдений за большой Землей (arXiv) Автор: Рольф Симоэс , Жильберто Камара , Жильберто Кейрос , Фелипе Соуза , Педро Р. Андраде ,..
    3 способа решить квадратное уравнение (3-й мой любимый) -
    1. Методом факторизации — 2. Используя квадратичную формулу — 3. Заполнив квадрат — Давайте поймем это, решив это простое уравнение: Мы пытаемся сделать LHS,..
    Создание VR-миров с A-Frame
    Виртуальная реальность (и дополненная реальность) стали главными модными терминами в образовательных технологиях. С недорогими VR-гарнитурами, такими как Google Cardboard , и использованием..
    Демистификация рекурсии
    КОДЕКС Демистификация рекурсии Упрощенная концепция ошеломляющей О чем весь этот шум? Рекурсия, кажется, единственная тема, от которой у каждого начинающего студента-информатика..