Найти перекрывающиеся диапазоны дат в PostgreSQL

Question

Это правильно?

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 имеет имя игрока, название команды и даты, когда он вступил и покинул клуб.
Я хочу сделать функцию перечисления всех игроков, которые были в команде в определенные годы.
Вышеупомянутый запрос, похоже, не работает...

Answer 1

Почему бы не использовать между без даты часть вещи:

WHERE datefield BETWEEN '2009-10-10 00:00:00' AND '2009-10-11 00:00:00'

или что-то типа того?

Answer 2

В настоящее время принятый ответ не отвечает на вопрос. И это в принципе неправильно. a BETWEEN x AND y переводится как:

a >= x AND a <= y

Включая верхнюю границу, в то время как люди обычно должны исключать ее:

a >= x AND a < y

С датами вы можете легко настроить. Для 2009 года используйте "2009-12-31" в качестве верхней границы.
Но это не так просто с временными метками, которые допускают дробные цифры. Современные версии Postgres используют внутреннее 8-байтовое целое для хранения до 6 долей секунды (разрешение мкс). Зная это, мы все равно можем заставить его работать, но это не интуитивно понятно и зависит от деталей реализации. Плохая идея.

Более того, 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';

Но есть еще:

Если ссылочная целостность обеспечивается с помощью ограничений FK, таблица team Сам по себе просто шум в запросе и может быть удален.

Хотя один и тот же игрок может покинуть и присоединиться к той же команде, нам также необходимо сложить возможные дубликаты, например, с DISTINCT,

И нам, возможно, потребуется предусмотреть особый случай: игроки, которые никогда не уходили. Предполагая, что эти игроки имеют NULL в date_leave,

"Предполагается, что игрок, который не ушел, будет играть за команду по сей день".

Уточненный запрос:

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

Приоритет оператора работает против нас, AND связывает перед OR, Нам нужны скобки.

Связанный ответ с оптимизированным DISTINCT (если дубликаты распространены):

• Таблица "многие ко многим" - производительность плохая

Как правило, имена физических лиц не являются уникальными и используется суррогатный первичный ключ. Но, очевидно, name_player является первичным ключом player, Если все, что вам нужно, это имена игроков, нам не нужен стол player в запросе либо:

SELECT DISTINCT name_player 
FROM   contract
WHERE  name_team = ? 
AND    date_join  <  date '2010-01-01'
AND   (date_leave >= date '2009-01-01' OR date_leave IS NULL);

SQL OVERLAPS оператор

Руководство:

OVERLAPS автоматически принимает более раннее значение пары в качестве начала. Каждый период времени считается представляющим полуоткрытый интервал start <= time < end если start а также end равны, и в этом случае он представляет этот единственный момент времени.

Заботиться о потенциале NULL ценности, COALESCE кажется самым простым:

SELECT DISTINCT name_player 
FROM   contract
WHERE  name_team = ? 
AND    (date_join, COALESCE(date_leave, CURRENT_DATE)) OVERLAPS
       (date '2009-01-01', date '2010-01-01');  -- upper bound excluded

Тип диапазона с поддержкой индекса

В Postgres 9.2 или более поздней версии вы также можете работать с реальными типами диапазонов:

SELECT DISTINCT name_player 
FROM   contract
WHERE  name_team = ? 
AND    daterange(date_join, date_leave) &&
       daterange '[2009-01-01,2010-01-01)';  -- upper bound excluded

Типы диапазонов увеличивают накладные расходы и занимают больше места. 2 х date = 8 байт; 1 х daterange = 14 байт на диске или 17 байт в оперативной памяти. Но в сочетании с оператором перекрытия && запрос может быть поддержан индексом GiST.

Кроме того, нет необходимости в специальных значениях NULL. NULL означает "открытый диапазон" в типе диапазона - именно то, что нам нужно. Определение таблицы даже не нужно менять: мы можем создать тип диапазона на лету - и поддержать запрос с помощью соответствующего индекса выражения:

CREATE INDEX mv_stock_dr_idx ON mv_stock USING gist (daterange(date_join, date_leave));

Связанные с:

• Средняя таблица истории запасов