SQL: найти команды, содержащие список пользователей

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

Для этого я создал три таблицы базы данных.

 Team    User    TeamUsers
 t_id    u_id    t_id, u_id

Если пользователи 1, 4, 5 и 7 ранее были вместе в команде, я хотел бы знать идентификатор команды этой команды. Как будет выглядеть SQL для этого?

Я экспериментировал с чем-то вроде этого:

SELECT t_id FROM teamusers WHERE u_id IN (users[1], ..., users[i])
GROUP BY t_id HAVING COUNT(t_id) = users[].length;

Массив users программно заполняется значениями. Это кажется правильным? Есть ли другой способ, который лучше?


postgresql sql relational-division
person Pablo Jomer    schedule 14.04.2013    source источник
comment
Как вы справляетесь с изменениями с течением времени? Откуда мы знаем, что пользователи были членами команды одновременно? Создает ли каждое изменение участников автоматически новую команду (другую t_id)?   -  person Erwin Brandstetter    schedule 14.04.2013
comment
Да, изменение пользователей создает новую команду. Это работает как команда SC2, если вы играли в эту игру? Если вы играете с кем-то, что создает команду.   -  person Pablo Jomer    schedule 14.04.2013

Ответы (1)


arrow_upward
1
arrow_downward

Это классический случай относительного деления. Мы собрали целый арсенал запросов по этому тесно связанному вопросу:
Как фильтровать результаты SQL в отношении "многие-сквозные"

Один из способов получить все команды, в которых есть все данные игроки, — это то, что у вас уже есть. Должен быть одним из самых эффективных запросов.

Для более длинных массивов unnest() + JOIN работает лучше:

SELECT t_id
FROM  (SELECT unnest($users) AS u_id) u
JOIN   team_users USING (u_id)
GROUP  BY 1
HAVING count(*) = array_length($users, 1);

Но для небольшой группы игроков это вряд ли имеет значение.
Для многократного использования вместо построения запроса в приложении можно создать табличную функцию:

CREATE OR REPLACE FUNCTION f_get_teams(_users int[])
  RETURNS SETOF int AS
$func$

SELECT t_id
FROM  (SELECT unnest($1) AS u_id) u
JOIN   team_users USING (u_id)
GROUP  BY 1
HAVING count(*) = array_length($1, 1);

$func$ LANGUAGE SQL STRICT;

Вызов:

SELECT * FROM f_get_teams('{1,4,5,7}'::int[]);
person Erwin Brandstetter    schedule 14.04.2013