[SQL] PostgreSQL의 날짜 범위를 중복 찾기

PostgreSQL의 날짜 범위를 중복 찾기

이 올바른지?

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)<= ?

내 테이블 계약 선수 이름, 팀 이름을 가지고 있으며, 날짜 그는 입사 클럽을 떠날 때. 나는 특정 년 만에 팀에 있던 모든 선수를 나열하는 기능을 만들고 싶어. 위의 쿼리는 작동하지 않는 것 ...

해결법

1. ==============================

   1.왜 날짜 부분 일없이 사이에 사용하지 :

   왜 날짜 부분 일없이 사이에 사용하지 :

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

   또는 그런 뭔가?

2. ==============================

   2.현재 허용 대답은 질문에 대답하지 않습니다. 그리고 그것은 원칙적으로 잘못된 것입니다. X 및 Y로 변환 BETWEEN A :

   현재 허용 대답은 질문에 대답하지 않습니다. 그리고 그것은 원칙적으로 잘못된 것입니다. X 및 Y로 변환 BETWEEN A :

   a >= x AND a <= y

   사람들은 일반적으로 그것을 제외해야하지만, 상위 경계를 포함 :

   a >= x AND a < y

   날짜하면 쉽게 조정할 수 있습니다. 상단 테두리와 2009 년 사용 '2009-12-31'하십시오. 그러나 소수 자릿수를 허용 타임 스탬프와 간단하지 않습니다. 현대 포스트 그레스 버전은 6 분수 초 (μs의 해상도)까지 저장할 내부적으로 8 바이트 정수를 사용합니다. 이 알면 우리는 여전히 작동 할 수 있지만 직관적 아니다 및 구현 세부 사항에 따라 달라집니다. 나쁜 생각.

   또한, x와 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 제약 조건이 적용되는 경우, 테이블 팀 자체는 소음 쿼리 및 제거 할 수 있습니다.

   같은 선수가 떠나 같은 팀에 복귀 할 수 있지만, 우리는 또한 DISTINCT와 예를 들어, 가능한 중복을 접을 필요가있다.

   그리고 우리는 특별한 경우를 위해 제공해야 할 수 있습니다 떠난 적이 플레이어있다. 그 선수가 date_leave에 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);
   

   연산자 우선 순위는 OR 전에 우리를 대적 작동과 결합한다. 우리는 괄호가 필요합니다.

   (중복 일반적인 경우) DISTINCT 최적화와 관련 대답 :

   일반적으로, 자연인의 이름이 고유하지 않습니다 및 대리 기본 키가 사용됩니다. 그러나, 분명히, name_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);
   

   수동 :

   잠재적 인 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
   

   포스트 그레스 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 × 8 바이트; 디스크에 1 × DATERANGE = 14 바이트 또는 17 바이트의 RAM. 그러나 오버랩 연산자 && 쿼리와 함께 요지 인덱스를 지원 할 수있다.

   또한, 특별한 경우의 NULL 값을 필요가 없습니다. NULL 수단 다양한 유형의 "개방 범위"- 우리가 필요 정확히. 우리는 즉시 범위 유형을 만들 수 있습니다 -와 일치하는 표현 인덱스 쿼리를 지원 : 테이블 정의도 변경하지 않습니다

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

   관련 :

from https://stackoverflow.com/questions/4480715/find-overlapping-date-ranges-in-postgresql by cc-by-sa and MIT license