Это верно?
SELECT * FROM contract JOIN team USING (name_team) JOIN player USING(name_player) WHERE name_team = ? AND DATE_PART('YEAR',date_join)>= ? AND DATE_PART('YEAR',date_leave)<= ? У моего настольного contract есть имя игрока, название команды и даты, когда он присоединился и покинул клуб.
Я хочу создать функцию, в которой перечислены все игроки, которые были в команде в определенные годы.
Вышеприведенный запрос, похоже, не работает …
Почему бы не использовать между элементами даты:
WHERE datefield BETWEEN '2009-10-10 00:00:00' AND '2009-10-11 00:00:00'
или что-то типа того?
Ответ, предлагающий a BETWEEN x AND y получил много оборотов и был принят уже около 2 лет. Но это не только не отвечает на вопрос, но и в принципе неверно .
a BETWEEN x AND y переводит:
a >= x AND a <= y
Включая верхнюю границу, в то время как люди обычно должны ее исключать :
a >= x AND a < y
С датами вы можете легко настроить. В 2009 году использовать «2009-12-31» в качестве верхней границы.
Но это не так просто с метками времени, которые допускают дробные цифры. Современные версии Postgres используют 8-байтовое целое внутри для хранения до 6 дробных секунд (разрешение μs). Зная это, мы все равно можем заставить его работать, но это неинтуитивно и зависит от деталей реализации. Плохая идея.
Более того, в этом конкретном случае a BETWEEN x AND y бесполезно находить диапазоны, перекрывающиеся с другим диапазоном. Нам нужно:
b >= x AND a < y
И игроки, которые никогда не уходили, пока не считаются.
Предполагая, что 2009 , я буду перефразировать вопрос, не изменяя его значение:
«Найдите всех игроков данной команды, которые присоединились до 2010 года и не уезжали до 2009 года».
SELECT p.* FROM team t JOIN contract c USING (name_team) JOIN player p USING (name_player) WHERE t.name_team = ? AND c.date_join < date '2010-01-01' AND c.date_leave >= date '2009-01-01';
Приоритет операторов работает против нас, AND связывается перед OR . Нам нужны скобки.
Если ссылочная целостность не нарушена, команда таблицы сама по себе является шумом в этом запросе и может быть удалена.
В то время как один и тот же игрок может уйти и воссоединиться с той же командой, нам также необходимо сбрасывать возможные дубликаты, например, с DISTINCT .
И нам может потребоваться предоставить специальный случай: игроки, которые никогда не уходили. Предположим, что у этих игроков есть date_leave IS NULL .
«Предполагается, что игрок, который, как известно, не ушел, будет играть за команду по сей день».
Приобретение этого оптимизированного запроса:
SELECT DISTINCT p.* FROM contract c JOIN player p USING (name_player) WHERE c.name_team = ? AND c.date_join < date '2010-01-01' AND (c.date_leave >= date '2009-01-01' OR c.date_leave IS NULL);
Связанный ответ с оптимизированным DISTINCT (если дубликаты являются общими):
Как правило, имена не уникальны, а первичный ключ суррогата используется для физических лиц. Но, очевидно, name_player является основным ключом player . Если вам нужны только имена игроков, нам не нужен player таблицы в запросе:
SELECT DISTINCT c.name_player FROM contract c WHERE c.name_team = ? AND c.date_join < date '2010-01-01' AND (c.date_leave >= date '2009-01-01' OR c.date_leave IS NULL);
Мы могли бы также использовать оператор SQL OVERLAPS :
OVERLAPSавтоматически берет предыдущее значение пары в качестве начала. Каждый период времени считается периодом полуоткрытого интервалаstart <= time < end, если толькоstartиendне равны, и в этом случае он представляет одноразовый момент.
Но нам нужно позаботиться о потенциальных значениях NULL . Самый простой с COALESCE :
SELECT DISTINCT c.name_player FROM contract c WHERE t.name_team = ? AND (c.date_join, COALESCE(c.date_leave, current_date)) OVERLAPS ('2009-01-01'::date, '2009-12-31'::date)
В Postgres 9.2 или новее вы также можете работать с реальными типами диапазонов в сочетании с оператором перекрытия && который может поддерживаться индексом GiST. Пример: